Re: Finetuning Autovacuum

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(dot)marlowe(at)gmail(dot)com]
> Sent: Monday, January 04, 2010 6:50 PM
> To: Benjamin Krajmalnik
> Cc: pgsql-admin(at)postgresql(dot)org
> Subject: Re: [ADMIN] Finetuning Autovacuum
>
> On Mon, Jan 4, 2010 at 6:38 PM, Benjamin Krajmalnik <kraj(at)illumen(dot)com>
> 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.