Обсуждение: More Autovacuum questions

Chris Hoover escribió:
> Another question.
>
> When autovacuum sleeps, does it release the lock it has on the table?

What do you mean sleep?  There are two sleeps: the one caused by
cost-based vacuum delay (lasts for some milliseconds, happens every
handful of pages vacuumed; no locks released here) and the big sleep
that happens after one worker finishes and the next one starts (all
locks are released).

When one autovac worker is processing a table, no other worker touches
it.  So if you have a table that's 64 GB, it won't be analyzed until
the currently running vacuum finishes.

> What we would like to have happen is for vacuum to work for a while, sleep,
> and while it is sleeping run an analyze on the table.  We need this due to
> how quickly our data is changing.  Currently we are running analyze every 5
> minutes against the table of question and get a very serious performance hit
> when vacuum runs due to the index stats getting out of date.  As soon as we
> rerun analyze after the vacuum happiness returns.

This is worst case for Postgres vacuuming, I think.

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

Chris Hoover wrote:
> Another question.
>
> When autovacuum sleeps, does it release the lock it has on the table?
>
> What we would like to have happen is for vacuum to work for a while,
> sleep, and while it is sleeping run an analyze on the table.  We need
> this due to how quickly our data is changing.  Currently we are running
> analyze every 5 minutes against the table of question and get a very
> serious performance hit when vacuum runs due to the index stats getting
> out of date.  As soon as we rerun analyze after the vacuum happiness
> returns.
>
> The pg_total_relation_size for the table in question is 64GB, and as
> stated above is a very heavily used and modifed table.

As Alvaro already said this is a case where autovacuum still isn't
great.  Perhaps what you should do here is disable auto-vacumming of the
table during busy periods and and only have it vacuum at off peak times?

Matthew T. O'Connor escribió:

> As Alvaro already said this is a case where autovacuum still isn't
> great.

While I have your attention ;-) do you have any ideas on how to improve
this?  I don't see anything that looks like a solution for this case.

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

Alvaro Herrera wrote:
> Matthew T. O'Connor escribió:
>
>> As Alvaro already said this is a case where autovacuum still isn't
>> great.
>>
>
> While I have your attention ;-) do you have any ideas on how to improve
> this?  I don't see anything that looks like a solution for this case.

No good ideas, but a few random thoughts.
1) Can we make vacuum not block analyze?  That way we can continue to
analyze a table while it's being vacuumed.
1b) If not, can we make auto-analyze bump auto-vacuum?
2) Maintenance windows.  They wouldn't solve this problem, but they
might mitigate it some.