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
[PERFORM] Vacuum takes a really long time, vacuum full required
Hi Folks, This is my _4th_ time trying to post this, me and the mailing list software are fighting. I think it's because of the attachments so I'll just put links to them instead. All apologies if this gets duplicated. 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. I would love suggestions on what I can do either inside my application, or from a dba point of view to keep the database maintained without having to inflict downtime. This is for 'Netdisco' -- an open source network management software by the way. I'ld like to fix this for everyone who uses it. Sys Info : $ uname -a FreeBSD .ucsc.edu 4.10-STABLE FreeBSD 4.10-STABLE #0: Mon Aug 16 14:56:19 PDT 2004 [EMAIL PROTECTED]:/usr/src/sys/compile/ i386 $ pg_config --version PostgreSQL 7.3.2 $ cat postgresql.conf max_connections = 32 shared_buffers = 3900 # 30Mb - Bsd current kernel limit max_fsm_relations = 1000# min 10, fsm is free space map, ~40 bytes max_fsm_pages = 1 # min 1000, fsm is free space map, ~6 bytes max_locks_per_transaction = 64 # min 10 wal_buffers = 8 # min 4, typically 8KB each The log of the vacuum and the db schema could not be attached, so they are at : http://netdisco.net/db_vacuum.txt http://netdisco.net/pg_all.input Thanks for any help! -m ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
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]
