Ouch hurts my eyes :) Can you see something like table_len,
dead_tuple_percent, free_percent order by dead_tuple_percent desc
limit 10 or something like that maybe?
Sorry about the pain. Didn't know what you needed to see.
Ordering by dead_tuple_percent:
db.production=> select table_name, table_len, dead_tuple_percent,
free_percent from temp_tuplestats order by dead_tuple_percent desc
limit 10;
table_name | table_len | dead_tuple_percent | free_percent
-------------------------------------+-----------+--------------------+--------------
scheduler_info | 8192 | 43.95 | 46
inserter_maintenance_logs | 16384 | 25.13 | 9
merchants | 8192 | 24.19 | 64
scheduler_in_progress | 32768 | 16.47 | 75
guilds_hosts | 8192 | 13.28 | 67
work_types | 8192 | 12.18 | 78
production_printer_maintenance_logs | 16384 | 11.18 | 11
guilds_work_types | 8192 | 10.94 | 71
config | 8192 | 10.47 | 83
work_in_progress | 131072 | 8.47 | 85
(10 rows)
These are our smallest, and in terms of performance, least significant
tables. Except for work_in_progress, they play little part in overall
system performace. work_in_progress gets dozens of insertions and
deletions per second, and as many queries.
Ordering by table size, because I had the questions of where the bloat
is, in terms of disk space used (since I brought up before that the
physical size of the database is growing at about 50% per quarter):
db.production=> select table_name, table_len, dead_tuple_percent, free_percent from temp_tuplestats order by table_len desc limit 10;
table_name | table_len | dead_tuple_percent | free_percent
--------------------------------------------+-------------+--------------------+--------------
documents | 28510109696 | 1.05 | 21
document_address | 23458062336 | 2.14 | 10
latest_document_address_links | 4953735168 | 3.71 | 21
documents_ps_page | 4927676416 | 1.19 | 6
injectd_log | 4233355264 | 0.74 | 17
ps_page | 3544350720 | 0.81 | 4
temp_bak_documents_invoice_amount_for_near | 3358351360 | 0 | 0
statements | 1832091648 | 4.4 | 2
documents_old_addresses | 1612947456 | 0 | 1
cron_logs | 791240704 | 0 | 1
(10 rows)
Am I seeing in the above queries evidence that my bloat is mostly in
free space, and not in dead tuples?
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
