Poor delete performance AFTER vacuum analyze
От | Jeremy M. Guthrie |
---|---|
Тема | Poor delete performance AFTER vacuum analyze |
Дата | |
Msg-id | 200307191622.46709.jeremy.guthrie@berbee.com обсуждение исходный текст |
Ответы |
Re: Poor delete performance AFTER vacuum analyze
Re: Poor delete performance AFTER vacuum analyze |
Список | pgsql-performance |
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Hello, I have an application I wrote that uses two tables heavily. The first table is a queue table, the second table is an archive table. The queue table is constantly taking on new records at the rate of 10/second. My software pulls the logs from the queue table, processes them, moves them to the archive table, and then deletes the processed entries from the queue table. Symptoms: My system will run great after a full vacuum(as I would expect). It will run all day long taking only 3-5 seconds to run and deal with approximately 100megs of new data each day. However, the instant the system finishes only a 'vacuum analyze', the whole thing slows WAY down to where each run can take 10-15 minutes. ie. After a full analyze vacuum, my software will load, process, move, and delete in under 3-4 seconds. After a analyze vacuum(notice: not full), it can load, process and move data in 3-5 seconds but the delete takes 10-15 minutes! I submit the delete as one transaction to clear out the records processed. Trunactae won't work because other records are coming in while I process data. Mind you the archive table is 15 million records while the temporary table is maybe 10-20,000 records. Now I just rewrote a portion of my application to change its behavior. What it did before was that it would pile through a 10 gig archive table, processed logs, etc... in about 3 minutes but I did not delete in the same way because everything is already in one table. My software has to run every five minutes so the three minute runtime is getting close for process overlap(yuck). Recap The old system didn't delete records but plowed through the 10 gig db and takes 3 1/2 minutes to do its job. The new system flies through the smaller queue table(100-200k) but it dies after conducting a non-full vacuum. Is the planner just that much better at analyzing a full then an regular analyze or is there something else I'm missing? The Box: The DB is a dual P4 2.4ghz Xeon w/ 1.5 gig of RAM. IBM 335 w/ 36gig mirrored. kernel.shmmax = 1342177280 shared_buffers = 115200 # 2*max_connections, min 16 sort_mem = 65536 # min 32 vacuum_mem = 196608 # min 1024 fsync = false - -- Jeremy M. Guthrie Systems Engineer Berbee 5520 Research Park Dr. Madison, WI 53711 Phone: 608-298-1061 Berbee...Decade 1. 1993-2003 -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.1 (GNU/Linux) iD8DBQE/GbajqtjaBHGZBeURAkKiAJ9zaqQISD47XycRcSgDKbNeuqqaKQCfcgim yCdaycBg4+99Epd7EuAAxsE= =9xlS -----END PGP SIGNATURE-----
В списке pgsql-performance по дате отправления: