|
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@xxxxxxxxx]
Hey, this is quite the *opposite*. The function find files in the postgres database folder that are not used by the database. To use it : * connect to the database you want to analyse ( **mandatory** ). * create the function (execute function definition) * Execute `SELECT * FROM find_useless_postgres_file('your_database_name')`
and so can be removed. To be extra sure, you should use oid2name programme to check that the useless files are really useless. FROM find_useless_postgres_file('your_database_name') ) TO 'path_to_you_database_folder/potential_useless.txt' now you've got a file with a list of potential erroneous files. * Then use oid2name `$su postgres $while read i; do oid2name -f "$i" -i -S -q -d your_database_name; done < potential_useless.txt Nothing should show, meaning that every potential erroneous file
If you feel unconvinced, you can manually try oid2name on some
* Now delete all the files in `potential_useless.txt`. It could be wiser to not delete the files but rename those (for instance, adding `.potentially_useless` as a postfix) so if it breaks something, you have an easy way to revert everything. Anyway, use *-*extra extra*-* caution if you delete. Cheers, Rémi-C 2016-03-02 15:38 GMT+01:00 Johnny Morano <johnny.morano@xxxxxxxxx>: Hi Remi! This SQL function you have provided, seems to return all valid files, is that correct?
In my case, it returned all my ‘base/’ files. Is that normal? If yes, maybe you rename the function to ‘find_useful_postgres_files’ ;-) Could you explain in steps how to use this function to make a cleanup of bloated data?
(like in an example with commands and example output, if possible of course) Thanks! Mit besten Grüßen / With best 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 Registered at: LG Salzburg | Company number: FN 315081 f | VAT-ID: ATU64439405 Managing Director: Christian Bamberger Follow us on:
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. This message and any attachments have been scanned for viruses prior leaving PAY.ON; however,
PAY.ON does not guarantee the security of this message and will not be responsible for any damages arising as a result of any virus being passed on or arising from any alteration of this message by a third party. PAY.ON may monitor e-mails sent to and from
PAY.ON. From:
pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx]
On Behalf Of Rémi Cura Would gladly do it, but still this "wiki cooloff" stuff, can't create a page Cheers, Rémi-C 2016-02-29 20:44 GMT+01:00 Alvaro Herrera <alvherre@xxxxxxxxxxxxxxx>: Rémi Cura wrote: |
