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 ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
