Re: [PERFORM] Vacuum takes a really long time, vacuum full required
On Tue, Oct 19, 2004 at 11:40:17AM -0400, Rod Taylor wrote: Whatever the case, the database still slows down to a halt after a month or so, and I have to go in and shut everything down and do a VACUUM FULL by hand. One index (of many many) takes 2000 seconds to vacuum. The whole process takes a few hours. Do a REINDEX on that table instead, and regular vacuum more frequently. $ pg_config --version PostgreSQL 7.3.2 7.4.x deals with index growth a little better 7.3 and older did. I did a REINDEX of the database. The results are pretty insane, the db went from 16GB to 381MB. Needless to say things are running a lot faster. I will now take Tom's well-given advice and upgrade to 7.4. But at least now I have something to tell my users who are not able to do a DB upgrade for whatever reason. Thanks for all your help folks! -m Before: # du -h pgsql 135Kpgsql/global 128Mpgsql/pg_xlog 80Mpgsql/pg_clog 3.6Mpgsql/base/1 3.6Mpgsql/base/16975 1.0Kpgsql/base/16976/pgsql_tmp 16Gpgsql/base/16976 16Gpgsql/base 16Gpgsql After Reindex: # du /data/pgsql/ 131K/data/pgsql/global 128M/data/pgsql/pg_xlog 81M/data/pgsql/pg_clog 3.6M/data/pgsql/base/1 3.6M/data/pgsql/base/16975 1.0K/data/pgsql/base/16976/pgsql_tmp 268M/data/pgsql/base/16976 275M/data/pgsql/base 484M/data/pgsql/ After Vacuum: # du /data/pgsql/ 131K/data/pgsql/global 144M/data/pgsql/pg_xlog 81M/data/pgsql/pg_clog 3.6M/data/pgsql/base/1 3.6M/data/pgsql/base/16975 1.0K/data/pgsql/base/16976/pgsql_tmp 149M/data/pgsql/base/16976 156M/data/pgsql/base 381M/data/pgsql/ netdisco= select relname, relpages from pg_class order by relpages desc; Before: relname | relpages -+-- idx_node_switch_port_active | 590714 idx_node_switch_port| 574344 idx_node_switch | 482202 idx_node_mac| 106059 idx_node_mac_active |99842 After: relname | relpages -+-- node_ip |13829 node| 9560 device_port | 2124 node_ip_pkey| 1354 idx_node_ip_ip | 1017 idx_node_ip_mac_active | 846 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Vacuum takes a really long time, vacuum full required
Whatever the case, the database still slows down to a halt after a month or so, and I have to go in and shut everything down and do a VACUUM FULL by hand. One index (of many many) takes 2000 seconds to vacuum. The whole process takes a few hours. Do a REINDEX on that table instead, and regular vacuum more frequently. $ pg_config --version PostgreSQL 7.3.2 7.4.x deals with index growth a little better 7.3 and older did. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] Vacuum takes a really long time, vacuum full required
Max Baker [EMAIL PROTECTED] writes: I've been having problems maintaining the speed of the database in the long run. VACUUMs of the main tables happen a few times a day after maybe 50,000 or less rows are added and deleted (say 6 times a day). I have a whole lot (probably too much) indexing going on to try to speed things up. Whatever the case, the database still slows down to a halt after a month or so, and I have to go in and shut everything down and do a VACUUM FULL by hand. One index (of many many) takes 2000 seconds to vacuum. The whole process takes a few hours. The first and foremost recommendation is to increase your FSM settings; you seem to be using the defaults, which are pegged for a database size of not more than about 100Mb. Second is to update to PG 7.4. I think you are probably suffering from index bloat to some extent, and 7.4 should help. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] Vacuum takes a really long time, vacuum full required
Hi Rod, On Tue, Oct 19, 2004 at 11:40:17AM -0400, Rod Taylor wrote: Whatever the case, the database still slows down to a halt after a month or so, and I have to go in and shut everything down and do a VACUUM FULL by hand. One index (of many many) takes 2000 seconds to vacuum. The whole process takes a few hours. Do a REINDEX on that table instead, and regular vacuum more frequently. Great, this is exactly what I think it needs. Meanwhile, I was checking out http://www.postgresql.org/docs/7.3/static/sql-reindex.html Which suggests I might be able to do a drop/add on each index with the database 'live'. However, the DROP INDEX command was taking an awfully long time to complete and it hung my app in the mean time. Does anyone know if the DROP INDEX causes an exclusive lock, or is it just a lengthy process? $ pg_config --version PostgreSQL 7.3.2 7.4.x deals with index growth a little better 7.3 and older did. Will do. Meanwhile I'm stuck supporting older 7.x versions, so I'm still looking for a solution for them. Thanks! -m ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
