Thanks, Scott. I would think that we would reach some sort of steady state, yet the tables appear to continue to grow. Looking at the running processes from the server status in pgAdmin, I can see that one table has been under autovacuum for 2.5 hours (vacuum/analyze). I just ran it manually and it took < 7 seconds. For reference, the table has 48000 rows These are the results: INFO: vacuuming "public.tblksaura"INFO: scanned index "tblksaura_kstestssysid_key" to remove 2087069 row versions DETAIL: CPU 0.00s/0.08u sec elapsed 0.09 sec.INFO: scanned index "tblksaura_pkey" to remove 2087069 row versions DETAIL: CPU 0.01s/0.08u sec elapsed 0.09 sec.INFO: scanned index "tblksaura_idx_time" to remove 2087069 row versions DETAIL: CPU 0.03s/0.44u sec elapsed 0.50 sec.INFO: scanned index "tblksaura_idx_kstestssysid" to remove 2087069 row versions DETAIL: CPU 0.01s/0.31u sec elapsed 0.33 sec.INFO: "tblksaura": removed 2087069 row versions in 112510 pages DETAIL: CPU 0.20s/0.40u sec elapsed 0.63 sec. INFO: index "tblksaura_kstestssysid_key" now contains 48166 row versions in 822 pages DETAIL: 152098 index row versions were removed. 24 index pages have been deleted, 24 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: index "tblksaura_pkey" now contains 48170 row versions in 824 pages DETAIL: 147284 index row versions were removed. 25 index pages have been deleted, 24 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: index "tblksaura_idx_time" now contains 48257 row versions in 9495 pages DETAIL: 715673 index row versions were removed. 6669 index pages have been deleted, 4738 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec.INFO: index "tblksaura_idx_kstestssysid" now contains 48257 row versions in 5083 pages DETAIL: 715953 index row versions were removed. 256 index pages have been deleted, 101 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: "tblksaura": found 38128 removable, 47802 nonremovable row versions in 142382 out of 146932 pages DETAIL: 28 dead row versions cannot be removed yet. There were 614222 unused item pointers. 0 pages are entirely empty. CPU 0.88s/1.86u sec elapsed 2.81 sec. INFO: vacuuming "pg_toast.pg_toast_92876"INFO: scanned index "pg_toast_92876_index" to remove 6103 row versions DETAIL: CPU 0.00s/0.00u sec elapsed 0.00 sec.INFO: "pg_toast_92876": removed 6103 row versions in 675 pages DETAIL: CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: index "pg_toast_92876_index" now contains 17163 row versions in 956 pages DETAIL: 6103 index row versions were removed. 568 index pages have been deleted, 550 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: "pg_toast_92876": found 4465 removable, 16949 nonremovable row versions in 1288 out of 5200 pages DETAIL: 0 dead row versions cannot be removed yet. There were 45124 unused item pointers. 0 pages are entirely empty. CPU 0.01s/0.02u sec elapsed 0.03 sec.INFO: analyzing "public.tblksaura"INFO: "tblksaura": scanned 30000 of 146932 pages, containing 9832 live rows and 72 dead rows; 9832 rows in sample, 48155 estimated total rowsTotal query runtime: 6937 ms. Any suggestions on how to better tune autovacuum, or alternatively do you recommend just running a vacuum analyze as a pgagent scheduled task? > -----Original Message----- > From: Scott Marlowe [mailto:scott.marlowe@xxxxxxxxx] > Sent: Monday, January 04, 2010 6:50 PM > To: Benjamin Krajmalnik > Cc: pgsql-admin@xxxxxxxxxxxxxx > Subject: Re: Finetuning Autovacuum > > On Mon, Jan 4, 2010 at 6:38 PM, Benjamin Krajmalnik <kraj@xxxxxxxxxxx> > wrote: > > PostgreSQL 8.4/FreeBSD 7.2 amd64 > > > > I have a database which has 3 tables which get a very high level of > > activity (about 40 thousand updates per minute). > > That's quite a lot. Even if you do get autovac / vacuum aggressive > enough, you're gonna have a lot of dead (but available for writing) > rows all the time to provide the space for the new rows to go into. > > > The tables are getting quite bloated, since autovacuum is apparently > not > > optimally configured (it is using the default settings). > > Yeah, it's not setup for something quite this aggressive by default. > > > Anything I do must be such that it will not cause deadlocks, since > the > > effects can be catastrophic with the amount of data being pumped > through the > > system. > > > > Initially, I had scheduled tasks through pgagent running a vacuum > analyze > > every 15 minutes, but other posts I have read here have stated this > could > > cause deadlocks, and mentioned running autovacuum is preferable > > Autovacuum is just a daemon that calls vacuum (regular) for you, so if > regular vacuum could cause deadlocks then so could autovacuum. In my > experience plain vacuum does not cause locks, or deadlocks, that get > in the way of very much. The only experience I have with vacuum > getting in the way is with slony thrown in the mix on a machine > running execute on ddl on the slony nodes. Which is a pretty odd > combo and turning off autovac during slony maintenance fixed me right > up. > > You should set up a test and see how it runs. > > > Also, are rows "vacuumed" in the indices made available without > having to > > reindex? The reason I am asking is because the indices seem to be > bloating > > to a much higher factor than the data table. > > yes, both indexes and tables have their free space made available by > regular vacuum. -- Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
