Is there a reason _not_ to vacuum continuously?
От | Matt Clark |
---|---|
Тема | Is there a reason _not_ to vacuum continuously? |
Дата | |
Msg-id | LFEIJBEOKGPDHCEMDGNFCECFCDAA.matt@ymogen.net обсуждение исходный текст |
Ответы |
Re: Is there a reason _not_ to vacuum continuously?
Re: Is there a reason _not_ to vacuum continuously? |
Список | pgsql-performance |
I'm running a load of stress scripts against my staging environment to simulate user interactions, and watching the various boxen as time goes by. I noticed that the CPU utilisation on the DB server (PG 7.2.3, RH7.3, Dual PII 550MHz, 1GB RAM, 1GB database on disk, Single 10k SCSI drive) was increasing over time, and manually launched a vacuum analyze verbose. A typical output from the VAV is: NOTICE: --Relation mobilepm-- NOTICE: Index mobilepm_ownerid_idx: Pages 1103; Tuples 32052: Deleted 46012. CPU 0.15s/0.66u sec elapsed 14.82 sec. NOTICE: Index mobilepm_id_idx: Pages 1113; Tuples 32143: Deleted 46012. CPU 0.33s/1.08u sec elapsed 45.89 sec. NOTICE: Index mobilepm_ownerid_status_idx: Pages 1423; Tuples 32319: Deleted 46 012. CPU 0.52s/1.05u sec elapsed 54.59 sec. NOTICE: Index mobilepm_number_idx: Pages 1141; Tuples 32413: Deleted 46012. CPU 0.26s/0.61u sec elapsed 16.13 sec. NOTICE: Removed 46012 tuples in 2548 pages. CPU 0.88s/0.79u sec elapsed 75.57 sec. NOTICE: Pages 3188: Changed 10, Empty 0; Tup 32007: Vac 46012, Keep 11, UnUsed 0. Total CPU 2.56s/4.25u sec elapsed 216.50 sec. NOTICE: --Relation pg_toast_112846940-- NOTICE: Pages 0: Changed 0, Empty 0; Tup 0: Vac 0, Keep 0, UnUsed 0. Total CPU 0.00s/0.00u sec elapsed 0.00 sec. NOTICE: Analyzing mobilepm So you can see that some tables are seeing a hell of a lot of updates. That's life, and yes, I do need all those indexes :-) Now I see no drop in performance while the VAV is running, the CPU utilisation gradually drops from 80% to 30% on the DB server, and life in general improves. On the live server (PG 7.2.3, RH7.3, Quad Xeon 700Mhz 1MB cache, 4Gb RAM, 256MB write-back RAID10 over 4 10K disks) I vacuum analyze daily, and vacuum analyze a couple of key tables every 15 minutes, but my question is... *** THE QUESTION(S) *** Is there any reason for me not to run continuous sequential vacuum analyzes? At least for the 6 tables that see a lot of updates? I hear 10% of tuples updated as a good time to vac-an, but does my typical count of 3 indexes per table affect that? Cheers Matt Postscript: I may have answered my own question while writing this mail. Under the current stress test load about 10% of the key tables' tuples are updated between sequential vacuum-analyzes, so the received wisdom on intervals suggests '0' in my case anyway...
В списке pgsql-performance по дате отправления: