Re: bloated postgres data folder, clean up

Hi,

So, I still think the SQL function isnt really working well.

Here’s what I did:

/data/postgres # psql

postgres=# \copy ( select * from find_useless_postgres_file('live') ) to /tmp/useless_files.csv delimiter ';' csv header;

postgres=# \q

/data/postgres # wc -l /tmp/useless_files.csv

7422 /tmp/useless_files.csv

# filter out the .## files, e.g.:

# 48175847.37;/base/16398/48175847.37;/data/postgres/base/16398/48175847.37;1047420928

# 48175847.36;/base/16398/48175847.36;/data/postgres/base/16398/48175847.36;1073741824

# 48175847.35;/base/16398/48175847.35;/data/postgres/base/16398/48175847.35;1073741824

# 48175847.34;/base/16398/48175847.34;/data/postgres/base/16398/48175847.34;1073741824

# 48175847.33;/base/16398/48175847.33;/data/postgres/base/16398/48175847.33;1073741824

# 48175847.32;/base/16398/48175847.32;/data/postgres/base/16398/48175847.32;1073741824

# 48175847.31;/base/16398/48175847.31;/data/postgres/base/16398/48175847.31;1073741824

# 48175847.30;/base/16398/48175847.30;/data/postgres/base/16398/48175847.30;1073741824

#

# because oid2name doesn't like them, gives error:

# /data/postgres # oid2name -f 48175847.30 -i -S -q -d live

# oid2name: query failed: ERROR:  invalid input syntax for type oid: "48175847.30"

# LINE 11:   (pg_catalog.pg_relation_filenode(c.oid) IN ('48175847.30')...

#                                                        ^

#

# oid2name: query was: SELECT pg_catalog.pg_relation_filenode(c.oid) as "Filenode", relname as "Table Name"

# FROM pg_catalog.pg_class c

#      LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace

#      LEFT JOIN pg_catalog.pg_database d ON d.datname = pg_catalog.current_database(),

#      pg_catalog.pg_tablespace t

# WHERE relkind IN ('r', 'm', 'i', 'S', 't') AND

#            t.oid = CASE

#                   WHEN reltablespace <> 0 THEN reltablespace

#                   ELSE dattablespace

#            END AND

#   (pg_catalog.pg_relation_filenode(c.oid) IN ('48175847.30'))

# ORDER BY relname

/data/postgres # cut -d ';' -f1 /tmp/useless_files.csv | cut -d'.' -f1 | sort -n | uniq >/tmp/potential_useless_oids.csv

/data/postgres # wc -l /tmp/potential_useless_oids.csv

1017 /tmp/potential_useless_oids.csv

# get a list of all used oids, instead of examing one by one

/data/postgres # oid2name -i -S -q -d live | awk '{print $1}' | sort -n | uniq >/tmp/used_oids.csv

/data/postgres # wc -l /tmp/used_oids.csv

940 /tmp/used_oids.csv

/data/postgres # while read i; do grep $i /tmp/used_oids.csv >/dev/null || (echo "$i" >>/tmp/not_in_use_oids.csv); done < /tmp/potential_useless_oids.csv

/data/postgres # wc -l /tmp/not_in_use_oids.csv

168 /tmp/not_in_use_oids.csv

/data/postgres # egrep -v 'fsm|vm' /tmp/not_in_use_oids.csv |wc -l

1

/data/postgres # egrep -v 'fsm|vm' /tmp/not_in_use_oids.csv      

file_name

# The CSV header only

So, no bloated files.

But, using the function from https://wiki.postgresql.org/wiki/Show_database_bloat I get:

live=# select tbloat,wasted_space from table_bloat order by wasted_space desc limit 25;

tbloat │ wasted_space

────────┼──────────────

    1.0 │ 9976 kB

    1.2 │ 98 GB

    1.0 │ 97 MB

    1.4 │ 96 kB

    1.2 │ 920 kB

    1.2 │ 88 kB

    1.1 │ 88 kB

    2.0 │ 8192 bytes

    0.0 │ 8192 bytes

    1.3 │ 8192 bytes

    2.0 │ 8192 bytes

    1.3 │ 8192 bytes

    1.5 │ 8192 bytes

    1.5 │ 8192 bytes

    2.0 │ 8192 bytes

    1.1 │ 8192 bytes

    1.0 │ 8192 bytes

    1.1 │ 8192 bytes

    1.3 │ 8192 bytes

    1.5 │ 8192 bytes

    1.1 │ 80 kB

    1.0 │ 7584 kB

    1.6 │ 71 MB

    1.0 │ 704 kB

    1.1 │ 6968 kB

(25 rows)

So actually, quite a lot of bloated data J

What am I doing wrong?

Mit freundlichen Grüßen / With kind regards,

Johnny Morano

____________________________________________________

Johnny Morano  |  Principal Systems Engineer

PAY.ON GmbH  |  AN ACI WORLDWIDE COMPANY  |  WWW.PAYON.COM

Jakob-Haringer-Str. 1  |  5020 Salzburg  |  Austria

This email message and any attachments may contain confidential, proprietary or non-public information. This information is intended solely for the designated recipient(s). If an addressing or transmission error has misdirected this email, please notify the sender immediately and destroy this email. Any review, dissemination, use or reliance upon this information by unintended recipients is prohibited. Any opinions expressed in this email are those of the author personally.

From: Rémi Cura [mailto:remi.cura@gmail.com]
Sent: Mittwoch, 2. März 2016 17:49
To: Johnny Morano
Cc: Alvaro Herrera; PostgreSQL General
Subject: Re: [GENERAL] bloated postgres data folder, clean up