[PERFORM] test post
please ignore if this goes through. They've been bouncing and I'm trying to find out why. -m ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[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
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]
Re: [PERFORM] Free PostgreSQL Training, Philadelphia, Oct 30
On Wed, Oct 13, 2004 at 12:21:27PM -0400, Aaron Mulder wrote: All, My company (Chariot Solutions) is sponsoring a day of free PostgreSQL training by Bruce Momjian (one of the core PostgreSQL developers). The day is split into 2 sessions (plus a QA session): * Mastering PostgreSQL Administration * PostgreSQL Performance Tuning Registration is required, and space is limited. The location is Malvern, PA (suburb of Philadelphia) and it's on Saturday Oct 30. For more information or to register, see http://chariotsolutions.com/postgresql.jsp I'm up in New York City and would be taking the train down to Philly. Is anyone coming from Philly or New York that would be able to give me a lift to/from the train station? Sounds like a great event. Cheers, -m ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
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
