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
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, right? It even
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
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.
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 workers
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 implementation
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 tell the
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
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
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
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
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, what I think
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.
Remember
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
cancelled.
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 both cases?
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 you roll this into
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
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
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, the
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 fashion
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 would
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
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
autovacuum of
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 isAutovacuum flag on
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 isAutovacuum flag on
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 problem
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]
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:
\_
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,
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 at
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 the
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 the
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 had -- what if
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 deadlock
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 the deadlock
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 no
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 and cancels it
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, finds that the
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 doing
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
check? So
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 it
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 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 (I
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
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
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*
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
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
ANALYZE could
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 concurrently
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 time
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 = 20
I've
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 with
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 ? That
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, as
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 advocating the use
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
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 you
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'm
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.
I doubt that that
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 desirable
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
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 takes
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
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
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 piece of
[ 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 revert this
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 to that, it would
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 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
* autovacuum on,
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
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,
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,
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 we
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 in
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 restore is far slower than with 8.2. From time to time ALTER TABLE
queries
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 restore
77 matches
Mail list logo
