Обсуждение: Finetuning Autovacuum

On Mon, Jan 4, 2010 at 6:38 PM, Benjamin Krajmalnik <kraj@illumen.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.

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
for2.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:
6937ms. 

Any suggestions on how to better tune autovacuum, or alternatively do you recommend just running a vacuum analyze as a
pgagentscheduled task? 


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

On Mon, Jan 4, 2010 at 7:10 PM, Benjamin Krajmalnik <kraj@illumen.com> wrote:
> Thanks, Scott.
> I would think that we would reach some sort of steady state, yet the tables appear to continue to grow.

Then it's likely you're blowing out your free space map.

> Looking at the running processes from the server status in pgAdmin, I can see that one table has been under
autovacuumfor 2.5 hours (vacuum/analyze). 

That's autovacuum_vacuum_cost_delay biting you there.  The default
cost delay for regular vacuums is 0 (i.e. no cost delay) while the
default for autovac is something like 20ms, which is pretty high.

Do me a favor, run vacuum verbose on the db as a superuser and post
the last 20 or so lines back...  That'll tell us if you've blown out
the fsm.  Note that since you have recently fixed things up it'll
likely still look good.  Run it each night and email the last 20 lines
to yourself just to see how things are holding up.

Scott Marlowe escribió:
> On Mon, Jan 4, 2010 at 6:38 PM, Benjamin Krajmalnik <kraj@illumen.com> wrote:

> > 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.

FWIW there are some smarts in the deadlock detection code that prefer to
kill autovacuum if it is blocking some other process (even if it doesn't
cause a deadlock -- just blocking a user process is enough).  This does
not occur with user invoked vacuum, so there indeed can be a difference
here.  Note that this means that if you have an operation somewhere that
wants to get a lock that conflicts with vacuum all the time, it could be
causing autovacuum to get killed and thus never completing, leading to
catastrophic bloat.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Alvaro, since you are the autovacuum guru :)
What setting do you recommend I make to the autovacuum to see if I can get it to work better, and assuming I cannot get
itto complete in a reasonable time, I will run vacuum manually on the 3 tables so I can kill it if I see a deadlock
issue.

Obviously I prefer to use autovacuum, but I have to vacuum the files because when they bloat the performance gets
affectedsignificantly, resulting in a snowball effect. 


> -----Original Message-----
> From: Alvaro Herrera [mailto:alvherre@commandprompt.com]
> Sent: Monday, January 04, 2010 8:40 PM
> To: Scott Marlowe
> Cc: Benjamin Krajmalnik; pgsql-admin@postgresql.org
> Subject: Re: [ADMIN] Finetuning Autovacuum
>
> Scott Marlowe escribió:
> > On Mon, Jan 4, 2010 at 6:38 PM, Benjamin Krajmalnik
> <kraj@illumen.com> wrote:
>
> > > 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.
>
> FWIW there are some smarts in the deadlock detection code that prefer
> to
> kill autovacuum if it is blocking some other process (even if it
> doesn't
> cause a deadlock -- just blocking a user process is enough).  This does
> not occur with user invoked vacuum, so there indeed can be a difference
> here.  Note that this means that if you have an operation somewhere
> that
> wants to get a lock that conflicts with vacuum all the time, it could
> be
> causing autovacuum to get killed and thus never completing, leading to
> catastrophic bloat.
>
> --
> Alvaro Herrera
> http://www.CommandPrompt.com/
> The PostgreSQL Company - Command Prompt, Inc.

Alvaro, Scott - thanks for your replies and the direction you pointed me
into.

The underlying problem was that the cost limit was too low, so the
autovacuum process would run forever and not be able to do anything.  I
reduced the cost delay and increased the cost limit form the default of
200 to 10000, which is slightly more than the longest time I saw when
running manually.  Autovacuum now works as expected - the tables, which
have a very high rate of updates and some inserts (very few) are not
growing anymore.  The run time for the scheduled background processes
(which perform some background aggregation) have gone down slightly
(about 10%) as well.  Since autovacuum will prevent a deadlock, it is
definitely preferable to me over manually vacuuming.

Once again, thanks!

On Mon, 2010-01-04 at 22:00 -0700, Benjamin Krajmalnik wrote:
> Alvaro, Scott - thanks for your replies and the direction you pointed me
> into.
>
> The underlying problem was that the cost limit was too low, so the
> autovacuum process would run forever and not be able to do anything.  I
> reduced the cost delay and increased the cost limit form the default of
> 200 to 10000, which is slightly more than the longest time I saw when
> running manually.  Autovacuum now works as expected - the tables, which
> have a very high rate of updates and some inserts (very few) are not
> growing anymore.  The run time for the scheduled background processes
> (which perform some background aggregation) have gone down slightly
> (about 10%) as well.  Since autovacuum will prevent a deadlock, it is
> definitely preferable to me over manually vacuuming.
>
> Once again, thanks!

As an aside - are what percentage of your updates HOT updates?  If it's
low, and you can change your indexing (not having an index on the
column(s) that is updated), or set the fill factor if needed in such a
way that you can get into doing a larger number of HOT updates - that
will be very beneficial in for your usage pattern.

--
Brad Nicholson  416-673-4106
Database Administrator, Afilias Canada Corp.