On Fri, 2007-10-12 at 13:51 -0400, Tom Lane wrote:
> Simon Riggs <[EMAIL PROTECTED]> writes:
> > Can you explain further what you meant by "don't disable manual
> > cancels".
>
> I meant that pg_cancel_backend() should still work on autovac workers,
> contrary to Alvaro's suggestion that autovac w
Simon Riggs <[EMAIL PROTECTED]> writes:
> Can you explain further what you meant by "don't disable manual
> cancels".
I meant that pg_cancel_backend() should still work on autovac workers,
contrary to Alvaro's suggestion that autovac workers should sometimes
ignore SIGINT.
Basically the implement
On Fri, 2007-10-12 at 12:42 -0400, Tom Lane wrote:
> Simon Riggs <[EMAIL PROTECTED]> writes:
> > On Fri, 2007-10-12 at 11:26 -0400, Tom Lane wrote:
> >> Why not SIGINT?
>
> > I must be missing something. How would I tell the difference between
> > manual and automatic cancels if we use SIGINT for
Simon Riggs <[EMAIL PROTECTED]> writes:
> On Fri, 2007-10-12 at 11:26 -0400, Tom Lane wrote:
>> Why not SIGINT?
> I must be missing something. How would I tell the difference between
> manual and automatic cancels if we use SIGINT for both cases?
Why do you need to? I thought the plan was that D
On Fri, 2007-10-12 at 11:26 -0400, Tom Lane wrote:
> Simon Riggs <[EMAIL PROTECTED]> writes:
> > That seemed more complex when I thought about that, but if we just use
> > SIGUSR2 for automatic cancels then this would be very simple.
>
> Why not SIGINT?
I must be missing something. How would I te
Simon Riggs <[EMAIL PROTECTED]> writes:
> That seemed more complex when I thought about that, but if we just use
> SIGUSR2 for automatic cancels then this would be very simple.
Why not SIGINT?
regards, tom lane
---(end of broadcast)
On Fri, 2007-10-12 at 10:19 -0400, Tom Lane wrote:
> Simon Riggs <[EMAIL PROTECTED]> writes:
> > I think the best way to handle this is to have two limits.
> > First limit attempts to autovacuum, but can be cancelled.
> > When we hit second limit, sometime later, then autovacuum cannot be
> > cance
Simon Riggs <[EMAIL PROTECTED]> writes:
> I think the best way to handle this is to have two limits.
> First limit attempts to autovacuum, but can be cancelled.
> When we hit second limit, sometime later, then autovacuum cannot be
> cancelled.
This seems like uselessly complex overdesign.
Remembe
Simon Riggs schreef:
On Fri, 2007-10-12 at 11:44 +0200, Michael Paesold wrote:
Simon Riggs wrote:
On Fri, 2007-10-12 at 01:24 -0400, Alvaro Herrera wrote:
Yes, I think it is easy to mark the "is for xid wraparound" bit in the
WorkerInfo struct and have the cancel work only if it
On Fri, 2007-10-12 at 11:44 +0200, Michael Paesold wrote:
> Simon Riggs wrote:
> > On Fri, 2007-10-12 at 01:24 -0400, Alvaro Herrera wrote:
> >> Yes, I think it is easy to mark the "is for xid wraparound" bit in the
> >> WorkerInfo struct and have the cancel work only if it's off.
> >>
> >> However
Simon Riggs wrote:
On Fri, 2007-10-12 at 01:24 -0400, Alvaro Herrera wrote:
Yes, I think it is easy to mark the "is for xid wraparound" bit in the
WorkerInfo struct and have the cancel work only if it's off.
However, what I think should happen is that the signal handler for
SIGINT in a worker f
Simon Riggs wrote:
I think the best way to handle this is to have two limits.
First limit attempts to autovacuum, but can be cancelled.
When we hit second limit, sometime later, then autovacuum cannot be
cancelled.
That would give us a breathing space if we need it.
Sounds quite reasonable.
Simon Riggs schreef:
On Fri, 2007-10-12 at 07:17 +0100, Simon Riggs wrote:
On Fri, 2007-10-12 at 01:24 -0400, Alvaro Herrera wrote:
Michael Paesold escribió:
Simon Riggs wrote:
Hmm, I am not sure we are there, yet. Autovacuum does take extra care to
vacuum tables neari
On Fri, 2007-10-12 at 07:17 +0100, Simon Riggs wrote:
> On Fri, 2007-10-12 at 01:24 -0400, Alvaro Herrera wrote:
> > Michael Paesold escribió:
> > > Simon Riggs wrote:
> >
> > > Hmm, I am not sure we are there, yet. Autovacuum does take extra care to
> > > vacuum tables nearing xid wrap-around, r
On Fri, 2007-10-12 at 01:24 -0400, Alvaro Herrera wrote:
> Michael Paesold escribió:
> > Simon Riggs wrote:
>
> > Hmm, I am not sure we are there, yet. Autovacuum does take extra care to
> > vacuum tables nearing xid wrap-around, right? It even does so when
> > autovacuum is disabled in the conf
Michael Paesold escribió:
> Simon Riggs wrote:
> Hmm, I am not sure we are there, yet. Autovacuum does take extra care to
> vacuum tables nearing xid wrap-around, right? It even does so when
> autovacuum is disabled in the configuration.
>
> So in case a vacuum is needed for that very reason, th
On Thu, 2007-10-11 at 21:59 +0200, Michael Paesold wrote:
> So in case a vacuum is needed for that very reason, the vacuum should *not*
> be canceled, of course. So we don't really need the information, whether
> the AV worker is doing VACUUM or ANALYZE, but whether it is critical
> against xid
Simon Riggs wrote:
After some thought, you and Michael have persuaded me that there is
cause to do this for VACUUM as well, but just autovacuum, I think. That
also makes the patch simpler, since we don't need to delve inside the av
worker to see what it is doing.
Alvaro: That means we can just s
"Gregory Stark" <[EMAIL PROTECTED]> writes:
> "Alvaro Herrera" <[EMAIL PROTECTED]> writes:
>
>> Hmm, it looks like the race condition Heikki mentioned is the culprit.
>> We need a way to stop future analyzes from starting. Back to the
>> drawing board ...
>
> A crazy idea I just had -- what if yo
Simon Riggs wrote:
OK, I've got this working now. It successfully handles this test case,
which trips up on an auto ANALYZE every time I run it.
...
I notice when we cancel an AV worker it always says "cancelling
autovacuum of table", even when its just an ANALYZE. Wasn't important
before but n
On Wed, 2007-10-10 at 11:04 +0200, Michael Paesold wrote:
> Simon Riggs wrote:
> > OK, I've got this working now. It successfully handles this test case,
> > which trips up on an auto ANALYZE every time I run it.
> ...
> > I notice when we cancel an AV worker it always says "cancelling
> > autovacu
Heikki,
Thanks for your comments, we do need some review on the expected
behaviour.
On Wed, 2007-10-10 at 11:17 +0100, Heikki Linnakangas wrote:
> Simon Riggs wrote:
> > My thoughts are that it doesn't need to. Typically we create objects and
> > then fill them. It isn't that frequent that we wou
Simon Riggs wrote:
> My thoughts are that it doesn't need to. Typically we create objects and
> then fill them. It isn't that frequent that we would load data, then
> delete or update more than 20% of it, then attempt other DDL.
One scenario that comes to mind is a table that's used in OLTP fashio
Simon Riggs escribió:
> OK, I've got this working now. It successfully handles this test case,
> which trips up on an auto ANALYZE every time I run it.
OK, nice, send your patch. I'm a bit disconnected these days so I'm not
sure when, but I'll commit mine shortly.
--
Alvaro Herrera
On Thu, 2007-10-04 at 17:33 -0400, Alvaro Herrera wrote:
> Simon Riggs escribió:
>
> > Seems like we don't need to mess with the deadlock checker itself.
> >
> > We can rely on the process at the head of the lock wait queue to sort
> > this out for us. So all we need do is look at the isAutovacuu
On Thu, 2007-10-04 at 17:33 -0400, Alvaro Herrera wrote:
> Simon Riggs escribió:
>
> > Seems like we don't need to mess with the deadlock checker itself.
> >
> > We can rely on the process at the head of the lock wait queue to sort
> > this out for us. So all we need do is look at the isAutovacuu
Simon Riggs escribió:
> Seems like we don't need to mess with the deadlock checker itself.
>
> We can rely on the process at the head of the lock wait queue to sort
> this out for us. So all we need do is look at the isAutovacuum flag on
> the process that is holding the lock we're waiting on. If
On Thu, 2007-10-04 at 19:40 +0100, Simon Riggs wrote:
> On Thu, 2007-10-04 at 14:33 -0400, Alvaro Herrera wrote:
> > Tom Lane escribió:
> > > Alvaro Herrera <[EMAIL PROTECTED]> writes:
> > > > Gregory Stark escribi:
> > > >> A crazy idea I just had -- what if you roll this into the deadlock
> > >
Alvaro Herrera <[EMAIL PROTECTED]> writes:
> I am totally uninclined to mess with this stuff. I am barely aware of
> what exactly is it doing; I don't have the slightest idea how to modify
> it to cancel autovacs. Furthermore it sounds very much like a layering
> violation (what is deadlock.c doi
On Thu, 2007-10-04 at 14:33 -0400, Alvaro Herrera wrote:
> Tom Lane escribió:
> > Alvaro Herrera <[EMAIL PROTECTED]> writes:
> > > Gregory Stark escribi:
> > >> A crazy idea I just had -- what if you roll this into the deadlock
> > >> check? So
> > >> after waiting on the lock for 1s it wakes up,
Tom Lane escribió:
> Alvaro Herrera <[EMAIL PROTECTED]> writes:
> > Gregory Stark escribi�:
> >> A crazy idea I just had -- what if you roll this into the deadlock check?
> >> So
> >> after waiting on the lock for 1s it wakes up, finds that the holder it's
> >> waiting on is an autovacuum process
Alvaro Herrera <[EMAIL PROTECTED]> writes:
> Gregory Stark escribió:
>> A crazy idea I just had -- what if you roll this into the deadlock check? So
>> after waiting on the lock for 1s it wakes up, finds that the holder it's
>> waiting on is an autovacuum process and cancels it instead of finding n
Gregory Stark escribió:
> "Alvaro Herrera" <[EMAIL PROTECTED]> writes:
>
> > Hmm, it looks like the race condition Heikki mentioned is the culprit.
> > We need a way to stop future analyzes from starting. Back to the
> > drawing board ...
>
> A crazy idea I just had -- what if you roll this into
Gregory Stark wrote:
> "Alvaro Herrera" <[EMAIL PROTECTED]> writes:
>
>> Hmm, it looks like the race condition Heikki mentioned is the culprit.
>> We need a way to stop future analyzes from starting. Back to the
>> drawing board ...
>
> A crazy idea I just had -- what if you roll this into the d
On Thu, 2007-10-04 at 16:07 +0100, Gregory Stark wrote:
> "Alvaro Herrera" <[EMAIL PROTECTED]> writes:
>
> > Hmm, it looks like the race condition Heikki mentioned is the culprit.
> > We need a way to stop future analyzes from starting. Back to the
> > drawing board ...
>
> A crazy idea I just h
"Alvaro Herrera" <[EMAIL PROTECTED]> writes:
> Hmm, it looks like the race condition Heikki mentioned is the culprit.
> We need a way to stop future analyzes from starting. Back to the
> drawing board ...
A crazy idea I just had -- what if you roll this into the deadlock check? So
after waiting
On Thu, 2007-10-04 at 10:43 -0400, Tom Lane wrote:
> Simon Riggs <[EMAIL PROTECTED]> writes:
> > I'd also like to see vacuum_delay_point() do a test against
> > CountActiveBackends() to see if anything else is running. If there all
> > non-autovac processes are idle or waiting, then we should skip
Simon Riggs <[EMAIL PROTECTED]> writes:
> I'd also like to see vacuum_delay_point() do a test against
> CountActiveBackends() to see if anything else is running. If there all
> non-autovac processes are idle or waiting, then we should skip the delay
> point, this time only. That way a VACUUM can go
On Tue, 2007-10-02 at 11:17 -0400, Tom Lane wrote:
> In any case, this would still only fix things for pg_restore, and I
> remain concerned that people will gripe about autovacuum blocking
> locks. The idea of kicking autovac off tables remains probably more
> interesting in the long run.
Yes, s
Guillaume Smet escribió:
> Alvaro,
>
> On 10/4/07, Alvaro Herrera <[EMAIL PROTECTED]> wrote:
> > I came up with the following patch. What this does is cancel any
> > ANALYZE started by autovacuum, at the top of ALTER TABLE.
>
> It doesn't seem to work for me. I still have my ALTER TABLEs waiting
Alvaro,
On 10/4/07, Alvaro Herrera <[EMAIL PROTECTED]> wrote:
> I came up with the following patch. What this does is cancel any
> ANALYZE started by autovacuum, at the top of ALTER TABLE.
It doesn't seem to work for me. I still have my ALTER TABLEs waiting:
\_ postgres: postgres cityvox [local]
Alvaro Herrera wrote:
> I came up with the following patch. What this does is cancel any
> ANALYZE started by autovacuum, at the top of ALTER TABLE.
There's a small race condition, autoanalyze could start between the
calls autovac_cancel_analyze and relation_open.
And it doesn't solve the proble
I came up with the following patch. What this does is cancel any
ANALYZE started by autovacuum, at the top of ALTER TABLE.
There is a new function relation_openrv_cav(). This is the same as
relation_openrv, except that it will also cancel analyzes. I'm still
wondering if I should merge the two
Alvaro Herrera escribió:
> I think this is doable. We would need to add a phase 0 to ALTER TABLE
> processing, which grabs a less strong (than AccessExclusive) lock on the
> table, then goes over the list of commands and determine if at least one
> of them requires exclusive access to the table (
Tom Lane escribió:
> >> It might be possible to solve this if we reduce the strength of the lock
> >> used for ALTER TABLE. We'd have to go through all the commands
> >> potentially issued by a pg_dump script and see if they could all be made
> >> to run concurrently with autovac, which is a bit
Alvaro Herrera <[EMAIL PROTECTED]> writes:
> Tom Lane escribió:
>> It might be possible to solve this if we reduce the strength of the lock
>> used for ALTER TABLE. We'd have to go through all the commands
>> potentially issued by a pg_dump script and see if they could all be made
>> to run concur
Tom Lane escribió:
> I wrote:
> > Actually, if you wanted a low-hanging solution to that, it would
> > probably be to revert this 8.2 patch:
> > http://archives.postgresql.org/pgsql-committers/2006-09/msg00284.php
>
> Uh ... never mind, that won't help at all. Reducing the lock taken for
> ANALYZ
I wrote:
> Actually, if you wanted a low-hanging solution to that, it would
> probably be to revert this 8.2 patch:
> http://archives.postgresql.org/pgsql-committers/2006-09/msg00284.php
Uh ... never mind, that won't help at all. Reducing the lock taken for
ANALYZE could allow CREATE INDEX (Share
Tom Lane wrote:
In the worst case autovac could be starved out for a long time.
I don't have any immediate good idea about how to fix that, but
the worst consequences could be avoided if we disable the cancellation
ability when running an anti-wraparound vacuum. Further down the road
(*not* 8.3)
Tom Lane wrote:
Alvaro Herrera <[EMAIL PROTECTED]> writes:
How about getting ShareUpdateExclusiveLock on manual analyze and plain
AccessShareLock on autovacuum-induced analyze?
Wouldn't fix the original problem because those two lock types don't
conflict; hence might as well keep the behavior
Alvaro Herrera <[EMAIL PROTECTED]> writes:
> How about getting ShareUpdateExclusiveLock on manual analyze and plain
> AccessShareLock on autovacuum-induced analyze?
Wouldn't fix the original problem because those two lock types don't
conflict; hence might as well keep the behavior simple.
Tom Lane escribió:
> [ on further thought ]
>
> "Matthew T. O'Connor" <[EMAIL PROTECTED]> writes:
> >> ... solving the problem
> >> for analyze is a nice piece of low-hanging fruit that solves an
> >> immediate problem that has been reported.
>
> Actually, if you wanted a low-hanging solution t
[ on further thought ]
"Matthew T. O'Connor" <[EMAIL PROTECTED]> writes:
>> ... solving the problem
>> for analyze is a nice piece of low-hanging fruit that solves an
>> immediate problem that has been reported.
Actually, if you wanted a low-hanging solution to that, it would
probably be to rev
"Matthew T. O'Connor" <[EMAIL PROTECTED]> writes:
> I don't know much about what is involved in crafting these solutions,
> but it seems we're close to beta and probably don't want to make drastic
> changes to anything. As such it seems to me that solving the problem
> for analyze is a nice pie
Alvaro Herrera <[EMAIL PROTECTED]> writes:
> So you say we should make any job that needs an exclusive lock on a
> table to be able to cancel a running autovac job?
I think we're going to be seeing complaints of this form until we do that.
The only reason this particular discussion is about pg_res
Tom Lane wrote:
If you insist on crafting a solution that only fixes this problem for
pg_restore's narrow usage, you'll be back revisiting it before beta1
has been out a month.
I don't know much about what is involved in crafting these solutions,
but it seems we're close to beta and probably
Tom Lane escribió:
> Simon Riggs <[EMAIL PROTECTED]> writes:
> > We should not allow VACUUM to be concurrent with either CREATE INDEX or
> > ANALYZE, but then thats not the problem here anyway.
>
> I can't believe anyone is short-sighted enough to think that.
>
> The problem here is that autovac
Simon Riggs <[EMAIL PROTECTED]> writes:
> We should not allow VACUUM to be concurrent with either CREATE INDEX or
> ANALYZE, but then thats not the problem here anyway.
I can't believe anyone is short-sighted enough to think that.
The problem here is that autovac takes locks that block foreground
On Mon, 2007-10-01 at 16:50 -0400, Alvaro Herrera wrote:
> > Can CREATE INDEX and ANALYZE be made to run concurrently?
>
> I don't see why not (except for the fact that both try to update
> reltuples and relpages AFAIR, so we would need to be careful about
> that).
This seems like the most desir
Matthew T. O'Connor escribió:
> Tom Lane wrote:
>> Alvaro Herrera <[EMAIL PROTECTED]> writes:
>>
>>> This is an interesting idea, but I think it's attacking the wrong
>>> problem. To me, the problem here is that an ANALYZE should not block
>>> CREATE INDEX or certain forms of ALTER TABLE.
>>
>>
Tom Lane wrote:
Alvaro Herrera <[EMAIL PROTECTED]> writes:
This is an interesting idea, but I think it's attacking the wrong
problem. To me, the problem here is that an ANALYZE should not block
CREATE INDEX or certain forms of ALTER TABLE.
I doubt that that will work; in particular I'
Alvaro Herrera <[EMAIL PROTECTED]> writes:
> This is an interesting idea, but I think it's attacking the wrong
> problem. To me, the problem here is that an ANALYZE should not block
> CREATE INDEX or certain forms of ALTER TABLE.
I doubt that that will work; in particular I'm pretty dubious that
Heikki Linnakangas escribió:
> In my opinion, CREATE INDEX shouldn't need to wait for autovacuum to
> finish, regardless of who issued it. This is like priority inversion;
> the autovacuum is not urgent, and runs slowly to avoid disturbing
> others. But if it keeps the higher priority CREATE INDEX
Gregory Stark wrote:
> "Stefan Kaltenbrunner" <[EMAIL PROTECTED]> writes:
>
>> some additional datapoints:
>>
>> autovacuum on, delay 20: 8h 40min
>> autovacuum on, delay 0: 4h 23min
>
>
> I realize this isn't directly addressing the problem but perhaps part of the
> solution would be to start a
Gregory Stark <[EMAIL PROTECTED]> writes:
> I realize this isn't directly addressing the problem but perhaps part of the
> solution would be to start advocating the use of pg_restore -1 ? That would
> solve the problem for the narrow case of pg_restore.
Well, that would do as a quick workaround, a
"Stefan Kaltenbrunner" <[EMAIL PROTECTED]> writes:
> some additional datapoints:
>
> autovacuum on, delay 20: 8h 40min
> autovacuum on, delay 0: 4h 23min
I realize this isn't directly addressing the problem but perhaps part of the
solution would be to start advocating the use of pg_restore -1 ?
On 10/1/07, Guillaume Smet <[EMAIL PROTECTED]> wrote:
> * 8.3devel freshly compiled *
> - autovacuum off: 14m39
> - autovacuum on, delay 0: 15m32
> - autovacuum on, delay 20: 51m37 (the box is idle during a large
> amount of this time) - default configuration of 8.3devel
I made a few more tests w
Guillaume Smet wrote:
> On 9/22/07, Tom Lane <[EMAIL PROTECTED]> wrote:
>> Please try that experiment with all three configurations on both
>> versions:
>> * autovacuum off
>> * autovacuum on, autovacuum_vacuum_cost_delay = 0
>> * autovacuum on, autovacuum_vacuum_cost_delay
On 9/22/07, Tom Lane <[EMAIL PROTECTED]> wrote:
> Please try that experiment with all three configurations on both
> versions:
> * autovacuum off
> * autovacuum on, autovacuum_vacuum_cost_delay = 0
> * autovacuum on, autovacuum_vacuum_cost_delay = 20
I've finally found some
Tom Lane wrote:
> "Guillaume Smet" <[EMAIL PROTECTED]> writes:
>> So a total of: 16 minutes for 8.2 compared to 53 minutes for 8.3 to
>> have the database in the same state.
>
> Please try that experiment with all three configurations on both
> versions:
> * autovacuum off
> * autovacu
On 9/22/07, Tom Lane <[EMAIL PROTECTED]> wrote:
> "Guillaume Smet" <[EMAIL PROTECTED]> writes:
> Please try that experiment with all three configurations on both
> versions:
> * autovacuum off
> * autovacuum on, autovacuum_vacuum_cost_delay = 0
> * autovacuum on, autovacuum_
"Guillaume Smet" <[EMAIL PROTECTED]> writes:
> So a total of: 16 minutes for 8.2 compared to 53 minutes for 8.3 to
> have the database in the same state.
Please try that experiment with all three configurations on both
versions:
* autovacuum off
* autovacuum on, autovacuum_vacuum_c
On 9/19/07, Decibel! <[EMAIL PROTECTED]> wrote:
> You missed my point... what we'd want to happen is for the analyze to
> take place while that table had a good chance of still being in memory.
It seems to be a bit too intrusive for 8.3 at this point.
> It might be worth looking into creating a d
On Sep 19, 2007, at 2:08 AM, Guillaume Smet wrote:
On 9/19/07, Decibel! <[EMAIL PROTECTED]> wrote:
Odd... I'd expect it to actually be beneficial to run analyze on a
table
at roughly the same time as PK building, because you'd make better
use
of cache.
Sure if your database fits entirely i
On 9/19/07, Decibel! <[EMAIL PROTECTED]> wrote:
> Odd... I'd expect it to actually be beneficial to run analyze on a table
> at roughly the same time as PK building, because you'd make better use
> of cache.
Sure if your database fits entirely in RAM (otherwise if two big
tables are analyzed while
On Tue, Sep 18, 2007 at 06:51:04PM +0200, Guillaume Smet wrote:
> Hi all,
>
> As we will soon enter beta, I decided to give a try to 8.3devel. The
> first step is of course to load a dump from an existing database.
>
> The dump is a text dump of 1.6 GB (database is approximately 4 GB).
>
> The r
76 matches
Mail list logo
