Concurrent VACUUM and ANALYZE

Jonah H. Harris escribi�:

Currently, one cannot perform a concurrent VACUUM and ANALYZE. This
is a significant problem for tables which are not only large and have
designated cost-delays, but which are also heavily inserted into and
deleted from. After performing a quick cursory investigation on this,
it doesn't appear to be difficult to change. Other than the
ShareUpdateExclusiveLock, is there anything technically preventing us
from performing both concurrently?

This means that VACUUM ANALYZE must grab both locks ... is there a
gotcha here?

The main problem I see with this idea is that the dead and total tuple
count computed by ANALYZE would be immediately out of date, and if it
happens to finish after VACUUM then it'll overwrite the values the
latter just wrote, which are more correct. Not sure how serious a
problem this is.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

"Jonah H. Harris" <jonah.harris@gmail.com> writes:

Because we wouldn't want multiple ANALYZEs running on the same table,
changing the lock back to an AccessShareLock doesn't sound like a
solution.

It flat will not work. We used to do it that way, and it didn't
(search for "tuple concurrently updated" in the archives).

However, what are the thoughts around creating another,
more-specific lock? Perhaps something like ShareUpdateAnalysisLock?

The general overhead involved in a whole new lock type is high enough
that I would resist taking this path. (It's certainly a lot more than
adding an entry to one enum someplace --- offhand I can name docs and
grammar as important issues. And no you don't get to have a hidden lock
type that no one can see.)

Also, as Alvaro points out, it's far from clear that concurrent VACUUM
and ANALYZE is as safe as you think --- they both want to write the same
fields in pg_class.

regards, tom lane

On Mon, Jul 21, 2008 at 6:15 PM, Alvaro Herrera
<alvherre@commandprompt.com> wrote:

This means that VACUUM ANALYZE must grab both locks ... is there a
gotcha here?

Agreed.

The main problem I see with this idea is that the dead and total tuple
count computed by ANALYZE would be immediately out of date, and if it
happens to finish after VACUUM then it'll overwrite the values the
latter just wrote, which are more correct. Not sure how serious a
problem this is.

Agreed, but in the worst case, it's no different than running ANALYZE
immediately following a VACUUM.

-Jonah

On Mon, Jul 21, 2008 at 7:59 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

"Jonah H. Harris" <jonah.harris@gmail.com> writes:

Because we wouldn't want multiple ANALYZEs running on the same table,
changing the lock back to an AccessShareLock doesn't sound like a
solution.

It flat will not work. We used to do it that way, and it didn't
(search for "tuple concurrently updated" in the archives).

Agreed.

However, what are the thoughts around creating another,
more-specific lock? Perhaps something like ShareUpdateAnalysisLock?

The general overhead involved in a whole new lock type is high enough
that I would resist taking this path. (It's certainly a lot more than
adding an entry to one enum someplace --- offhand I can name docs and
grammar as important issues. And no you don't get to have a hidden lock
type that no one can see.)

Any other suggestions?

Also, as Alvaro points out, it's far from clear that concurrent VACUUM
and ANALYZE is as safe as you think --- they both want to write the same
fields in pg_class.

AFAICS, Alvaro didn't say that at all. At worst, if ANALYZE completed
after VACUUM, its stats wouldn't be as good as those set by VACUUM.
But, as I said in response to Alvaro, that's no different than running
ANALYZE immediately following VACUUM.

The case I'm looking at is a large table which requires a lazy vacuum,
and a zero vacuum cost delay would cause too much I/O. Yet, this
table has enough insert/delete activity during a vacuum, that it
requires a fairly frequent analysis to maintain proper plans. I
patched as mentioned above and didn't run across any unexpected
issues; the only one expected was that mentioned by Alvaro.

-Jonah

"Jonah H. Harris" <jonah.harris@gmail.com> writes:

The case I'm looking at is a large table which requires a lazy vacuum,
and a zero vacuum cost delay would cause too much I/O. Yet, this
table has enough insert/delete activity during a vacuum, that it
requires a fairly frequent analysis to maintain proper plans. I
patched as mentioned above and didn't run across any unexpected
issues; the only one expected was that mentioned by Alvaro.

I don't find this a compelling argument, at least not without proof that
the various vacuum-improvement projects already on the radar screen
(DSM-driven vacuum, etc) aren't going to fix your problem.

regards, tom lane

On Mon, Jul 21, 2008 at 10:19 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

"Jonah H. Harris" <jonah.harris@gmail.com> writes:

The case I'm looking at is a large table which requires a lazy vacuum,
and a zero vacuum cost delay would cause too much I/O. Yet, this
table has enough insert/delete activity during a vacuum, that it
requires a fairly frequent analysis to maintain proper plans. I
patched as mentioned above and didn't run across any unexpected
issues; the only one expected was that mentioned by Alvaro.

I don't find this a compelling argument, at least not without proof that
the various vacuum-improvement projects already on the radar screen
(DSM-driven vacuum, etc) aren't going to fix your problem.

Is DSM going to be in 8.4? The last I had heard, DSM+related
improvements weren't close to being guaranteed for this release. If
it doesn't make it, waiting another year and a half for something
easily fixed would be fairly unacceptable. Should I provide a patch
in the event that DSM doesn't make it?

-Jonah

Jonah H. Harris wrote:

On Mon, Jul 21, 2008 at 10:19 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

I don't find this a compelling argument, at least not without proof that
the various vacuum-improvement projects already on the radar screen
(DSM-driven vacuum, etc) aren't going to fix your problem.

Is DSM going to be in 8.4? The last I had heard, DSM+related
improvements weren't close to being guaranteed for this release. If
it doesn't make it, waiting another year and a half for something
easily fixed would be fairly unacceptable. Should I provide a patch
in the event that DSM doesn't make it?

Can't hurt to submit a patch. Also, could you do something to help
mitigate the worse case, something like don't update the stats in
pg_class if the analyze finishes after a vacuum has finished since the
current analyze started?

Matt

On Tue, Jul 22, 2008 at 12:31 AM, Matthew T. O'Connor <matthew@zeut.net> wrote:

Is DSM going to be in 8.4? The last I had heard, DSM+related
improvements weren't close to being guaranteed for this release. If
it doesn't make it, waiting another year and a half for something
easily fixed would be fairly unacceptable. Should I provide a patch
in the event that DSM doesn't make it?

Can't hurt to submit a patch. Also, could you do something to help mitigate
the worse case, something like don't update the stats in pg_class if the
analyze finishes after a vacuum has finished since the current analyze
started?

Yeah, I was thinking about that. It should actually be very easy to do that.

-Jonah

Jonah H. Harris wrote:

On Mon, Jul 21, 2008 at 10:19 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

"Jonah H. Harris" <jonah.harris@gmail.com> writes:

The case I'm looking at is a large table which requires a lazy vacuum,
and a zero vacuum cost delay would cause too much I/O. Yet, this
table has enough insert/delete activity during a vacuum, that it
requires a fairly frequent analysis to maintain proper plans. I
patched as mentioned above and didn't run across any unexpected
issues; the only one expected was that mentioned by Alvaro.

I don't find this a compelling argument, at least not without proof that
the various vacuum-improvement projects already on the radar screen
(DSM-driven vacuum, etc) aren't going to fix your problem.

Is DSM going to be in 8.4? The last I had heard, DSM+related
improvements weren't close to being guaranteed for this release. If
it doesn't make it, waiting another year and a half for something
easily fixed would be fairly unacceptable. Should I provide a patch
in the event that DSM doesn't make it?

For the immediate term, would it make sense for the ANALYZE to give up
and simply return if a VACUUM was in progress?

At least that way a client that sees performance degrade quickly between
vacuums can run the occasional preventative analyze without blocking
completely on auto-vacuums.

Jeroen

On Mon, 2008-07-21 at 17:50 -0400, Jonah H. Harris wrote:

Currently, one cannot perform a concurrent VACUUM and ANALYZE. This
is a significant problem for tables which are not only large and have
designated cost-delays, but which are also heavily inserted into and
deleted from.

I suspect this is not the root problem, but one solution to it.

If the stats need such frequent updating, then the code that handles the
stats probably needs extension/improvement to avoid such sensitivities.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support

On Thu, Jul 24, 2008 at 9:06 AM, Simon Riggs <simon@2ndquadrant.com> wrote:

I suspect this is not the root problem, but one solution to it.

Agreed. It is not the root problem. However, until DSM is fully
implemented and working, not having the ability to gather statistics
during long vacuums is problematic. Of course, you can try and
optimize vacuum by minimizing relation sizes using partitioning, but
that doesn't work in all cases.

If the stats need such frequent updating, then the code that handles the
stats probably needs extension/improvement to avoid such sensitivities.

Agreed, the optimizer's sensitivity to statistics in certain query
conditions is the root problem.

--
Jonah H. Harris, Senior DBA
myYearbook.com