Thread: Concurrent VACUUM and ANALYZE
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? 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. However, what are the thoughts around creating another, more-specific lock? Perhaps something like ShareUpdateAnalysisLock? Any other suggestions? -Jonah
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.comPostgreSQL 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