Is regular vacuuming with autovacuum needed?

Hi all,

I have an database server that is experiencing some lock contention
and deadlock. It's infrequent, maybe once every two months, but
time-consuming to deal with.

The issue was that a routine VACUUM process (vacuumdb -az, called
nightly via cron) was locking a table and wasn't completing. This
server is also running autovacuum. This wasn't the source of the
deadlock, but I'm wondering if regular vacuuming is necessary or even
desirable with autovacuum running. Is there any reason for me to not
disable the vacuum cron job and just tweak autovacuum parameters (or
even just to leave the defaults)?

Details:
PostgreSQL version 8.4.4
Default vacuum/autovacuum parameters in postgresql.conf:
#vacuum_cost_delay = 0ms
#vacuum_cost_page_hit = 1
#vacuum_cost_page_miss = 10
#vacuum_cost_page_dirty = 20
#vacuum_cost_limit = 200
....
#autovacuum = on
#log_autovacuum_min_duration = -1
#autovacuum_max_workers = 3
#autovacuum_naptime = 1min
#autovacuum_vacuum_threshold = 50
#autovacuum_analyze_threshold = 50
#autovacuum_vacuum_scale_factor = 0.2
#autovacuum_analyze_scale_factor = 0.1
#autovacuum_freeze_max_age = 200000000
#autovacuum_vacuum_cost_delay = 20ms
#autovacuum_vacuum_cost_limit = -1
....
#vacuum_freeze_min_age = 50000000
#vacuum_freeze_table_age = 150000000

Cheers,
Peter

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: Is regular vacuuming with autovacuum needed?

On Mon, Aug 16, 2010 at 12:08 PM, Peter Koczan wrote:
> Hi all,
>
> I have an database server that is experiencing some lock contention
> and deadlock. It's infrequent, maybe once every two months, but
> time-consuming to deal with.
>
> The issue was that a routine VACUUM process (vacuumdb -az, called
> nightly via cron) was locking a table and wasn't completing. This
> server is also running autovacuum. This wasn't the source of the
> deadlock, but I'm wondering if regular vacuuming is necessary or even
> desirable with autovacuum running. Is there any reason for me to not
> disable the vacuum cron job and just tweak autovacuum parameters (or
> even just to leave the defaults)?

If autovac is properly configured, very few, if any, PostgreSQL
databases need routine vacuuming jobs. However, other than sleep
states making it run slower, autovacuum is no different than a regular
old vacuum. Are you sure this wasn't a vacuum full, which is almost
never a desired operation to be regularly scheduled?
--
To understand recursion, one must first understand recursion.

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: Is regular vacuuming with autovacuum needed?

Peter Koczan writes:
> The issue was that a routine VACUUM process (vacuumdb -az, called
> nightly via cron) was locking a table and wasn't completing. This
> server is also running autovacuum. This wasn't the source of the
> deadlock, but I'm wondering if regular vacuuming is necessary or even
> desirable with autovacuum running. Is there any reason for me to not
> disable the vacuum cron job and just tweak autovacuum parameters (or
> even just to leave the defaults)?

On versions where autovacuum is on by default, I would certainly
recommend trying to use only autovacuum. cron-driven vacuum still
has some uses but they are corner cases.

regards, tom lane

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: Is regular vacuuming with autovacuum needed?

Tom Lane wrote:
> On versions where autovacuum is on by default, I would certainly
> recommend trying to use only autovacuum. cron-driven vacuum still
> has some uses but they are corner cases.
>

Corner cases implies something a bit more rare than I'd consider the
case here. Consider a server where you know you have a large table that
ends up with 5% dead rows each day. This will cause autovacuum to kick
in to clean up about every 4 days, at the defaults where
autovacuum_vacuum_scale_factor = 0.2.

When it does finally get to that table, it's going to have a fairly
large amount of work to do. If that happens during peak load time on
your server, you may find that a painful shock.

In that situation, it's completely reasonable to manually vacuum that
table each day during a known slow period, late at night for example.
Then it will never get to where it's so bloated that a hefty autovacuum
kicks in at an unpredictable time.

The other alternative here is to just tune autovacuum so it runs really
slowly, so it won't kill responsiveness during any peak period. While
in theory that's the right thing to do, this is much harder to get
working well than what I just described.

--
Greg Smith 2ndQuadrant US Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com www.2ndQuadrant.us


--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: Is regular vacuuming with autovacuum needed?

Greg Smith writes:
> Tom Lane wrote:
>> On versions where autovacuum is on by default, I would certainly
>> recommend trying to use only autovacuum. cron-driven vacuum still
>> has some uses but they are corner cases.

> Corner cases implies something a bit more rare than I'd consider the
> case here.

Well, it certainly has some uses, but I still think it's good advice to
first see if autovac alone will keep you happy.

> The other alternative here is to just tune autovacuum so it runs really
> slowly, so it won't kill responsiveness during any peak period. While
> in theory that's the right thing to do, this is much harder to get
> working well than what I just described.

But you really have to do that *anyway*, if you're not going to turn
autovac off.

I think the case where you want to combine cron-driven vacuum with
autovac is where, having made sure autovac is dialed down enough to not
present performance issues, you find that it can't keep up with the
required vacuuming. Then you need to do some not-hobbled vacuuming
during your maintenance windows. Eventually probably autovac will have
some understanding of maintenance windows built-in, but it doesn't yet.

regards, tom lane

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: Is regular vacuuming with autovacuum needed?

On Mon, Aug 16, 2010 at 4:01 PM, Tom Lane wrote:
> Greg Smith writes:
>> The other alternative here is to just tune autovacuum so it runs really
>> slowly, so it won't kill responsiveness during any peak period. =A0While
>> in theory that's the right thing to do, this is much harder to get
>> working well than what I just described.
>
> But you really have to do that *anyway*, if you're not going to turn
> autovac off.
>
> I think the case where you want to combine cron-driven vacuum with
> autovac is where, having made sure autovac is dialed down enough to not
> present performance issues, you find that it can't keep up with the
> required vacuuming. =A0Then you need to do some not-hobbled vacuuming
> during your maintenance windows. =A0Eventually probably autovac will have
> some understanding of maintenance windows built-in, but it doesn't yet.

If the global values aren't vacuuming that table enough, then it seems
apropos to change the autovacuum_vacuum_threshold value to some lower
value for that table.

(And it seems to me that if autovac never kicks in until 10% of a
table's "dead," that's not nearly aggressive enough, possibly even
with the global value! Given the 8.4 visibility map changes,
shouldn't autovac be a tad more aggressive, when it should be spending
little time on non-dead material? If the visibility map is doing its
job, then the global threshold can be set pretty low, no?)

I'm not quite sure what to think maintenance windows would look
like... I see them having at least two distinct forms:
a) A maintenance that is particularly for vacuuming, where factors
would get dialed down to encourage vacuuming tables that mayn't have
been hit lately;
b) A maintenance where it is expected that Things Are Being Changed,
where it might be pretty well necessary to shut off autovac so it
doesn't interfere with DDL work.
--=20
http://linuxfinances.info/info/postgresql.html

--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: Is regular vacuuming with autovacuum needed?

On Mon, Aug 16, 2010 at 1:34 PM, Scott Marlowe wr=
ote:
> If autovac is properly configured, very few, if any, PostgreSQL
> databases need routine vacuuming jobs. =A0However, other than sleep
> states making it run slower, autovacuum is no different than a regular
> old vacuum. =A0Are you sure this wasn't a vacuum full, which is almost
> never a desired operation to be regularly scheduled?

I'm sure it wasn't a full vacuum. I almost never do those and when I
do, I have to schedule downtime.

I think another process got hung up somewhere and couldn't release its
lock on the table in question, and there were several other processes
waiting. It's possible that it was just a symptom of a larger problem
at the time. I didn't have time to do a thorough analysis (and the
problem state is lost now), and what was cause vs. effect is probably
immaterial at this point.

Peter

--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: Is regular vacuuming with autovacuum needed?

On Mon, Aug 16, 2010 at 3:01 PM, Tom Lane wrote:
> Greg Smith writes:
>> Tom Lane wrote:
>>> On versions where autovacuum is on by default, I would certainly
>>> recommend trying to use only autovacuum. =A0cron-driven vacuum still
>>> has some uses but they are corner cases.
>
>> Corner cases implies something a bit more rare than I'd consider the
>> case here.
>
> Well, it certainly has some uses, but I still think it's good advice to
> first see if autovac alone will keep you happy.
>
>> The other alternative here is to just tune autovacuum so it runs really
>> slowly, so it won't kill responsiveness during any peak period. =A0While
>> in theory that's the right thing to do, this is much harder to get
>> working well than what I just described.
>
> But you really have to do that *anyway*, if you're not going to turn
> autovac off.
>
> I think the case where you want to combine cron-driven vacuum with
> autovac is where, having made sure autovac is dialed down enough to not
> present performance issues, you find that it can't keep up with the
> required vacuuming. =A0Then you need to do some not-hobbled vacuuming
> during your maintenance windows. =A0Eventually probably autovac will have
> some understanding of maintenance windows built-in, but it doesn't yet.

For this application (and most of my databases), I'm fairly certain
that autovacuum will work fine on its own. I'm going to disable the
cron-vacuuming and try running with autovacuum alone.

Thanks for the help,
Peter

--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: Is regular vacuuming with autovacuum needed?

On Mon, Aug 16, 2010 at 2:47 PM, Peter Koczan wrote:
> On Mon, Aug 16, 2010 at 1:34 PM, Scott Marlowe =
wrote:
>> If autovac is properly configured, very few, if any, PostgreSQL
>> databases need routine vacuuming jobs. =A0However, other than sleep
>> states making it run slower, autovacuum is no different than a regular
>> old vacuum. =A0Are you sure this wasn't a vacuum full, which is almost
>> never a desired operation to be regularly scheduled?
>
> I'm sure it wasn't a full vacuum. I almost never do those and when I
> do, I have to schedule downtime.
>
> I think another process got hung up somewhere and couldn't release its
> lock on the table in question, and there were several other processes
> waiting. It's possible that it was just a symptom of a larger problem
> at the time. I didn't have time to do a thorough analysis (and the
> problem state is lost now), and what was cause vs. effect is probably
> immaterial at this point.

OK then. It's important to understand that regular vacuum and
autovacuum perform the same functions and the only difference is the
default nap time. If you had a problem with vacuum, you can expect it
to crop up with autovacuum eventually too.

Note that I have had priority inversion issues with autovacuum, slony,
and the application where I work that meant I had to turn off
autovacuum to perform maintenance operations with slony. You might be
seeing the same kind of thing.


--=20
To understand recursion, one must first understand recursion.

--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin