pg.ddx.io pgsql-hackers@postgresql.org mailing list archive
help / color / mirror / Atom feedPreventing duplicate vacuums?
10+ messages / 6 participants
[nested] [flat]
* Preventing duplicate vacuums?
@ 2004-02-05 20:37 Josh Berkus <josh@agliodbs.com>
2004-02-05 21:07 ` Re: Preventing duplicate vacuums? Tom Lane <tgl@sss.pgh.pa.us>
2004-02-05 21:26 ` Re: Preventing duplicate vacuums? Rod Taylor <pg@rbt.ca>
0 siblings, 2 replies; 10+ messages in thread
From: Josh Berkus @ 2004-02-05 20:37 UTC (permalink / raw)
To: pgsql-hackers
Folks,
Just occurred to me that we have no code to prevent a user from running two
simultaneos lazy vacuums on the same table. I can't think of any
circumstance why running two vacuums would be desirable behavior; how
difficult would it be to make this an exception?
This becomes a more crucial issue now since the introduction of vacuum_delay
makes overlapping vacuums more probable.
--
-Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Preventing duplicate vacuums?
2004-02-05 20:37 Preventing duplicate vacuums? Josh Berkus <josh@agliodbs.com>
@ 2004-02-05 21:07 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-02-05 21:51 ` Re: Preventing duplicate vacuums? Josh Berkus <josh@agliodbs.com>
1 sibling, 1 reply; 10+ messages in thread
From: Tom Lane @ 2004-02-05 21:07 UTC (permalink / raw)
To: josh@agliodbs.com; +Cc: pgsql-hackers
Josh Berkus <josh@agliodbs.com> writes:
> Just occurred to me that we have no code to prevent a user from running two
> simultaneos lazy vacuums on the same table.
Yes we do: there's a lock.
regards, tom lane
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Preventing duplicate vacuums?
2004-02-05 20:37 Preventing duplicate vacuums? Josh Berkus <josh@agliodbs.com>
2004-02-05 21:07 ` Re: Preventing duplicate vacuums? Tom Lane <tgl@sss.pgh.pa.us>
@ 2004-02-05 21:51 ` Josh Berkus <josh@agliodbs.com>
2004-02-06 22:32 ` Re: Preventing duplicate vacuums? Robert Treat <xzilla@users.sourceforge.net>
0 siblings, 1 reply; 10+ messages in thread
From: Josh Berkus @ 2004-02-05 21:51 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-hackers
Tom,
> Yes we do: there's a lock.
Sorry, bad test. Forget I said anything.
Personally, I would like to have the 2nd vacuum error out instead of blocking.
However, I'll bet that a lot of people won't agree with me.
--
-Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Preventing duplicate vacuums?
2004-02-05 20:37 Preventing duplicate vacuums? Josh Berkus <josh@agliodbs.com>
2004-02-05 21:07 ` Re: Preventing duplicate vacuums? Tom Lane <tgl@sss.pgh.pa.us>
2004-02-05 21:51 ` Re: Preventing duplicate vacuums? Josh Berkus <josh@agliodbs.com>
@ 2004-02-06 22:32 ` Robert Treat <xzilla@users.sourceforge.net>
2004-02-07 05:07 ` Re: Preventing duplicate vacuums? Thomas Swan <tswan@idigx.com>
2004-02-07 07:07 ` Re: Preventing duplicate vacuums? Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 10+ messages in thread
From: Robert Treat @ 2004-02-06 22:32 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-hackers
On Thu, 2004-02-05 at 16:51, Josh Berkus wrote:
> Tom,
>
> > Yes we do: there's a lock.
>
> Sorry, bad test. Forget I said anything.
>
> Personally, I would like to have the 2nd vacuum error out instead of blocking.
> However, I'll bet that a lot of people won't agree with me.
>
Don't know if I would agree for sure, but i the second vacuum could see
that it is being blocked by the current vacuum, exiting out would be a
bonus, since in most scenarios you don't need to run that second vacuum
so it just ends up wasting resources (or clogging other things up with
it lock)
Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Preventing duplicate vacuums?
2004-02-05 20:37 Preventing duplicate vacuums? Josh Berkus <josh@agliodbs.com>
2004-02-05 21:07 ` Re: Preventing duplicate vacuums? Tom Lane <tgl@sss.pgh.pa.us>
2004-02-05 21:51 ` Re: Preventing duplicate vacuums? Josh Berkus <josh@agliodbs.com>
2004-02-06 22:32 ` Re: Preventing duplicate vacuums? Robert Treat <xzilla@users.sourceforge.net>
@ 2004-02-07 05:07 ` Thomas Swan <tswan@idigx.com>
2004-02-07 05:43 ` Re: Preventing duplicate vacuums? Joshua D. Drake <jd@commandprompt.com>
1 sibling, 1 reply; 10+ messages in thread
From: Thomas Swan @ 2004-02-07 05:07 UTC (permalink / raw)
To: Robert Treat <xzilla@users.sourceforge.net>; +Cc: Josh Berkus <josh@agliodbs.com>; Tom Lane <tgl@sss.pgh.pa.us>; pgsql-hackers
Robert Treat wrote:
>On Thu, 2004-02-05 at 16:51, Josh Berkus wrote:
>
>
>>Tom,
>>
>>
>>
>>>Yes we do: there's a lock.
>>>
>>>
>>Sorry, bad test. Forget I said anything.
>>
>>Personally, I would like to have the 2nd vacuum error out instead of blocking.
>>However, I'll bet that a lot of people won't agree with me.
>>
>>
>>
>
>Don't know if I would agree for sure, but i the second vacuum could see
>that it is being blocked by the current vacuum, exiting out would be a
>bonus, since in most scenarios you don't need to run that second vacuum
>so it just ends up wasting resources (or clogging other things up with
>it lock)
>
>
>
What about a situation where someone would have lazy vacuums cron'd and
it takes longer to complete the vacuum than the interval between
vacuums. You could wind up with an ever increasing queue of vacuums.
Erroring out with a "vacuum already in progress" might be useful.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Preventing duplicate vacuums?
2004-02-05 20:37 Preventing duplicate vacuums? Josh Berkus <josh@agliodbs.com>
2004-02-05 21:07 ` Re: Preventing duplicate vacuums? Tom Lane <tgl@sss.pgh.pa.us>
2004-02-05 21:51 ` Re: Preventing duplicate vacuums? Josh Berkus <josh@agliodbs.com>
2004-02-06 22:32 ` Re: Preventing duplicate vacuums? Robert Treat <xzilla@users.sourceforge.net>
2004-02-07 05:07 ` Re: Preventing duplicate vacuums? Thomas Swan <tswan@idigx.com>
@ 2004-02-07 05:43 ` Joshua D. Drake <jd@commandprompt.com>
0 siblings, 0 replies; 10+ messages in thread
From: Joshua D. Drake @ 2004-02-07 05:43 UTC (permalink / raw)
To: Thomas Swan <tswan@idigx.com>; +Cc: Robert Treat <xzilla@users.sourceforge.net>; Josh Berkus <josh@agliodbs.com>; Tom Lane <tgl@sss.pgh.pa.us>; pgsql-hackers
> What about a situation where someone would have lazy vacuums cron'd and
> it takes longer to complete the vacuum than the interval between
> vacuums. You could wind up with an ever increasing queue of vacuums.
>
> Erroring out with a "vacuum already in progress" might be useful.
I have seen this many times with customers as their traffic on the
database grows. A simple check would be of great, great use.
Sincerely,
Joshua D. Drake
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
--
Co-Founder
Command Prompt, Inc.
The wheel's spinning but the hamster's dead
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Preventing duplicate vacuums?
2004-02-05 20:37 Preventing duplicate vacuums? Josh Berkus <josh@agliodbs.com>
2004-02-05 21:07 ` Re: Preventing duplicate vacuums? Tom Lane <tgl@sss.pgh.pa.us>
2004-02-05 21:51 ` Re: Preventing duplicate vacuums? Josh Berkus <josh@agliodbs.com>
2004-02-06 22:32 ` Re: Preventing duplicate vacuums? Robert Treat <xzilla@users.sourceforge.net>
@ 2004-02-07 07:07 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-02-09 18:55 ` Re: Preventing duplicate vacuums? Robert Treat <xzilla@users.sourceforge.net>
1 sibling, 1 reply; 10+ messages in thread
From: Tom Lane @ 2004-02-07 07:07 UTC (permalink / raw)
To: Robert Treat <xzilla@users.sourceforge.net>; +Cc: Josh Berkus <josh@agliodbs.com>; pgsql-hackers
Robert Treat <xzilla@users.sourceforge.net> writes:
> Don't know if I would agree for sure, but i the second vacuum could see
> that it is being blocked by the current vacuum, exiting out would be a
> bonus, since in most scenarios you don't need to run that second vacuum
> so it just ends up wasting resources (or clogging other things up with
> it lock)
This would be reasonable if we could do it, but the present lock manager
doesn't provide any way to tell what sort of lock is blocking you.
There are some cases in which it isn't obvious anyway. For instance,
suppose an ALTER TABLE (which wants an exclusive lock) is queued up
waiting for the currently-running VACUUM. An incoming new VACUUM
request will queue behind the ALTER. Which lock would you say is
blocking it ... and does an honest answer to that question jibe with
your preference about whether the second VACUUM should give up?
A chintzy way out would be for VACUUM to just exit if it can't
immediately acquire lock, regardless of the cause. This wouldn't be
too useful for VACUUM FULL, but plain VACUUM is not blocked by very many
common operations other than another VACUUM, so most of the time it
would do what you want. I could possibly be talked into supporting an
option to do that.
regards, tom lane
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Preventing duplicate vacuums?
2004-02-05 20:37 Preventing duplicate vacuums? Josh Berkus <josh@agliodbs.com>
2004-02-05 21:07 ` Re: Preventing duplicate vacuums? Tom Lane <tgl@sss.pgh.pa.us>
2004-02-05 21:51 ` Re: Preventing duplicate vacuums? Josh Berkus <josh@agliodbs.com>
2004-02-06 22:32 ` Re: Preventing duplicate vacuums? Robert Treat <xzilla@users.sourceforge.net>
2004-02-07 07:07 ` Re: Preventing duplicate vacuums? Tom Lane <tgl@sss.pgh.pa.us>
@ 2004-02-09 18:55 ` Robert Treat <xzilla@users.sourceforge.net>
0 siblings, 0 replies; 10+ messages in thread
From: Robert Treat @ 2004-02-09 18:55 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Josh Berkus <josh@agliodbs.com>; pgsql-hackers
On Sat, 2004-02-07 at 02:07, Tom Lane wrote:
> Robert Treat <xzilla@users.sourceforge.net> writes:
> > Don't know if I would agree for sure, but i the second vacuum could see
> > that it is being blocked by the current vacuum, exiting out would be a
> > bonus, since in most scenarios you don't need to run that second vacuum
> > so it just ends up wasting resources (or clogging other things up with
> > it lock)
>
> This would be reasonable if we could do it, but the present lock manager
> doesn't provide any way to tell what sort of lock is blocking you.
> There are some cases in which it isn't obvious anyway. For instance,
> suppose an ALTER TABLE (which wants an exclusive lock) is queued up
> waiting for the currently-running VACUUM. An incoming new VACUUM
> request will queue behind the ALTER. Which lock would you say is
> blocking it ... and does an honest answer to that question jibe with
> your preference about whether the second VACUUM should give up?
>
ISTM that both sides have trouble, since you could just as easily have
vacuum queued up behind an alter we your second vacuum comes in...
> A chintzy way out would be for VACUUM to just exit if it can't
> immediately acquire lock, regardless of the cause. This wouldn't be
> too useful for VACUUM FULL, but plain VACUUM is not blocked by very many
> common operations other than another VACUUM, so most of the time it
> would do what you want. I could possibly be talked into supporting an
> option to do that.
>
This seems pretty useful to me. I thought about doing things like
setting statement_timeout to some low number but that would generally
cause the vacuum to timeout as well. Looking through postgresql.conf
nothing else seems to apply... ISTR people asking for a general
"lock_timeout" param that would cancel queries if they wait for a lock
longer than x milliseconds... this seems like very similar
functionality...
Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Preventing duplicate vacuums?
2004-02-05 20:37 Preventing duplicate vacuums? Josh Berkus <josh@agliodbs.com>
@ 2004-02-05 21:26 ` Rod Taylor <pg@rbt.ca>
2004-02-05 21:41 ` Re: Preventing duplicate vacuums? Josh Berkus <josh@agliodbs.com>
1 sibling, 1 reply; 10+ messages in thread
From: Rod Taylor @ 2004-02-05 21:26 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-hackers
On Thu, 2004-02-05 at 15:37, Josh Berkus wrote:
> Folks,
>
> Just occurred to me that we have no code to prevent a user from running two
> simultaneos lazy vacuums on the same table. I can't think of any
> circumstance why running two vacuums would be desirable behavior; how
> difficult would it be to make this an exception?
You have a 8 billion row table with some very high turn over tuples
(lots of updates to a few thousand rows). A partial or targeted vacuum
would be best, failing that you kick them off fairly frequently,
especially if IO isn't really an issue.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Preventing duplicate vacuums?
2004-02-05 20:37 Preventing duplicate vacuums? Josh Berkus <josh@agliodbs.com>
2004-02-05 21:26 ` Re: Preventing duplicate vacuums? Rod Taylor <pg@rbt.ca>
@ 2004-02-05 21:41 ` Josh Berkus <josh@agliodbs.com>
0 siblings, 0 replies; 10+ messages in thread
From: Josh Berkus @ 2004-02-05 21:41 UTC (permalink / raw)
To: Rod Taylor <pg@rbt.ca>; +Cc: pgsql-hackers
Rod,
> You have a 8 billion row table with some very high turn over tuples
> (lots of updates to a few thousand rows). A partial or targeted vacuum
> would be best, failing that you kick them off fairly frequently,
> especially if IO isn't really an issue.
Yes, but we don't have partial or targeted vacuums yet. When tablespaces is
finished, presumably the lock would be per tablespace.
--
-Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2004-02-09 18:55 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-02-05 20:37 Preventing duplicate vacuums? Josh Berkus <josh@agliodbs.com>
2004-02-05 21:07 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-02-05 21:51 ` Josh Berkus <josh@agliodbs.com>
2004-02-06 22:32 ` Robert Treat <xzilla@users.sourceforge.net>
2004-02-07 05:07 ` Thomas Swan <tswan@idigx.com>
2004-02-07 05:43 ` Joshua D. Drake <jd@commandprompt.com>
2004-02-07 07:07 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-02-09 18:55 ` Robert Treat <xzilla@users.sourceforge.net>
2004-02-05 21:26 ` Rod Taylor <pg@rbt.ca>
2004-02-05 21:41 ` Josh Berkus <josh@agliodbs.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox