- จำนวน record ที่เพิ่มมากขึ้น
- ขนาดของ index ที่มากขึ้น
- ขนาดของ dead rows ที่มากขึ้น
- ค่า free space map ดูว่าจะไม่พอแล้ว ในเวอร์ชั่นที่ต่ำกว่า 8.4
- มันสายไปแล้วถ้าคุณลืมเปิด autovacumm
หลาย ๆ คน อาจต้องยอมทำ VACUUM FULL แต่ปัญหาก็ตามมาคือ ระบบผมหยุดไม่ได้!
ส่ิงที่ต้องรีบดำเนินการโดยด่วนคือ
- เพิ่ม free space map
- เปิด auto vacuum
- จากเดิมที่ใช้ DELETE TABLE tablename; ก็เปลี่ยนเป็น TRUNCATE TABLE tablename;
อ้างอิงจากอันนี้ http://gkoenig.wordpress.com/2009/11/24/postgres-and-bloated-table/
postgres and bloated tableNovember 24, 2009 in open source, PostgreSQLFrom time to time there are news/messages about bloated tables in postgres and a thereby decreased performance of the database.The postgres-wiki contains a view (extracted from a script of the bucardo project) to check for bloat in your database hereFor a quick reference you can check your table/index sizes regularly and check the no. of tuples to assume where bloat comes in.e.g. check size of your tables and indexes:SELECT relname AS table_name,pg_size_pretty(pg_relation_size(oid)) AS table_size,pg_size_pretty(pg_total_relation_size(oid)) AS total_sizeFROM pg_classWHERE relkind in ('r','i')ORDER BY pg_relation_size(oid) DESC;check no. of tuplesSELECT relname, relkind, reltuples, relpagesFROM pg_classORDER BY relpages DESC;If you’re facing such a situation you have to check your (auto)vacuum settings and if it’s working correctly.Starting with Postgres 8.3.x the autovacuum daemon works reliable for most cases and should be enabled. On some workloads a manual vacuum is needed anyhow. In combination with setting the (auto)vacuum parameters you have to consider the Free Space Map (FSM) parameter.Open your postgresql.conf and check it out (every parameter has a very good comment there):* enable autovacuum and log every run > 1 sec.autovacuum = onlog_autovacuum_min_duration = 1000fine tune when autovacuum will check the tables. The number is the fraction of the table size which needs modified data to run an autovacuum.Apart from the bloated-table/index stuff, for the query planner it is very important to have up-to-date statistics. If you need an analyze more often, shrink the number to e.g. autovacuum_analyze_scale_factor = 0.02 and the tables will be autoanalyzed more oftenautovacuum_vacuum_scale_factor = 0.2autovacuum_analyze_scale_factor = 0.1prevent autovacuum from running too oftenautovacuum_naptime = 1min #time between two runsAnd don’t forget to check your postgresql.log file for any hints the database system provides.Regarding bloated tables I detected something like:WARNING: relation "public.table_one" contains more than "max_fsm_pages" pages with useful free spaceHINT: Consider using VACUUM FULL on this relation or increasing the configuration parameter "max_fsm_pages"A “normal” vacuum wouldn’t help in this case because it doesn’t release the pages to the os, they will only be marked as free in the FSM (Free Space Map).If your FSM isn’t big enough your tables will be bloated even with running autovacuum and perhaps manual vacuum, because the pages with no longer needed data cannot be marked as free and therefore never be overwritten.One solution would be to run avacuum full verbose analyze table_nameThis “defragment”-command marks unused pages as “can be overwritten” and moves pages from the bottom into this marked pages. => The table size will shrink! But be aware: vacuum full locks the table exclusive. Consider running this command in maintenance windows or during night.You should also keep in mind to “defragment” bloated indexes withREINDEX INDEX indexname # recreate index explicitlyREINDEX TABLE tablename # recreate all indexes for this table
