Re: [HACKERS] First steps with 8.3 and autovacuum launcher

2007-10-12 Thread Simon Riggs
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

Re: [HACKERS] First steps with 8.3 and autovacuum launcher

2007-10-12 Thread Tom Lane
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

Re: [HACKERS] First steps with 8.3 and autovacuum launcher

2007-10-12 Thread Simon Riggs
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

Re: [HACKERS] First steps with 8.3 and autovacuum launcher

2007-10-12 Thread Tom Lane
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

Re: [HACKERS] First steps with 8.3 and autovacuum launcher

2007-10-12 Thread Simon Riggs
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

Re: [HACKERS] First steps with 8.3 and autovacuum launcher

2007-10-12 Thread Tom Lane
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)

Re: [HACKERS] First steps with 8.3 and autovacuum launcher

2007-10-12 Thread Simon Riggs
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

Re: [HACKERS] First steps with 8.3 and autovacuum launcher

2007-10-12 Thread Tom Lane
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

Re: [HACKERS] First steps with 8.3 and autovacuum launcher

2007-10-12 Thread Deblauwe Gino
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

Re: [HACKERS] First steps with 8.3 and autovacuum launcher

2007-10-12 Thread Simon Riggs
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

Re: [HACKERS] First steps with 8.3 and autovacuum launcher

2007-10-12 Thread Michael Paesold
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

Re: [HACKERS] First steps with 8.3 and autovacuum launcher

2007-10-12 Thread Michael Paesold
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.

Re: [HACKERS] First steps with 8.3 and autovacuum launcher

2007-10-12 Thread Deblauwe Gino
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

Re: [HACKERS] First steps with 8.3 and autovacuum launcher

2007-10-11 Thread Simon Riggs
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

Re: [HACKERS] First steps with 8.3 and autovacuum launcher

2007-10-11 Thread Simon Riggs
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

Re: [HACKERS] First steps with 8.3 and autovacuum launcher

2007-10-11 Thread Alvaro Herrera
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

Re: [HACKERS] First steps with 8.3 and autovacuum launcher

2007-10-11 Thread Simon Riggs
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

Re: [HACKERS] First steps with 8.3 and autovacuum launcher

2007-10-11 Thread Michael Paesold
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

Re: [HACKERS] First steps with 8.3 and autovacuum launcher

2007-10-11 Thread Gregory Stark
"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

Re: [HACKERS] First steps with 8.3 and autovacuum launcher

2007-10-10 Thread Michael Paesold
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

Re: [HACKERS] First steps with 8.3 and autovacuum launcher

2007-10-10 Thread Simon Riggs
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

Re: [HACKERS] First steps with 8.3 and autovacuum launcher

2007-10-10 Thread Simon Riggs
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

Re: [HACKERS] First steps with 8.3 and autovacuum launcher

2007-10-10 Thread Heikki Linnakangas
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

Re: [HACKERS] First steps with 8.3 and autovacuum launcher

2007-10-09 Thread Alvaro Herrera
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

Re: [HACKERS] First steps with 8.3 and autovacuum launcher

2007-10-09 Thread Simon Riggs
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

Re: [HACKERS] First steps with 8.3 and autovacuum launcher

2007-10-05 Thread Simon Riggs
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

Re: [HACKERS] First steps with 8.3 and autovacuum launcher

2007-10-04 Thread Alvaro Herrera
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

Re: [HACKERS] First steps with 8.3 and autovacuum launcher

2007-10-04 Thread Simon Riggs
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 > > >

Re: [HACKERS] First steps with 8.3 and autovacuum launcher

2007-10-04 Thread Tom Lane
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

Re: [HACKERS] First steps with 8.3 and autovacuum launcher

2007-10-04 Thread Simon Riggs
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,

Re: [HACKERS] First steps with 8.3 and autovacuum launcher

2007-10-04 Thread Alvaro Herrera
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

Re: [HACKERS] First steps with 8.3 and autovacuum launcher

2007-10-04 Thread Tom Lane
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

Re: [HACKERS] First steps with 8.3 and autovacuum launcher

2007-10-04 Thread Alvaro Herrera
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

Re: [HACKERS] First steps with 8.3 and autovacuum launcher

2007-10-04 Thread Heikki Linnakangas
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

Re: [HACKERS] First steps with 8.3 and autovacuum launcher

2007-10-04 Thread Simon Riggs
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

Re: [HACKERS] First steps with 8.3 and autovacuum launcher

2007-10-04 Thread Gregory Stark
"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

Re: [HACKERS] First steps with 8.3 and autovacuum launcher

2007-10-04 Thread Simon Riggs
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

Re: [HACKERS] First steps with 8.3 and autovacuum launcher

2007-10-04 Thread Tom Lane
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

Re: [HACKERS] First steps with 8.3 and autovacuum launcher

2007-10-04 Thread Simon Riggs
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

Re: [HACKERS] First steps with 8.3 and autovacuum launcher

2007-10-04 Thread Alvaro Herrera
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

Re: [HACKERS] First steps with 8.3 and autovacuum launcher

2007-10-04 Thread Guillaume Smet
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]

Re: [HACKERS] First steps with 8.3 and autovacuum launcher

2007-10-04 Thread Heikki Linnakangas
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

Re: [HACKERS] First steps with 8.3 and autovacuum launcher

2007-10-03 Thread Alvaro Herrera
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

Re: [HACKERS] First steps with 8.3 and autovacuum launcher

2007-10-03 Thread Alvaro Herrera
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 (

Re: [HACKERS] First steps with 8.3 and autovacuum launcher

2007-10-03 Thread Alvaro Herrera
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

Re: [HACKERS] First steps with 8.3 and autovacuum launcher

2007-10-02 Thread Tom Lane
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

Re: [HACKERS] First steps with 8.3 and autovacuum launcher

2007-10-02 Thread Alvaro Herrera
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

Re: [HACKERS] First steps with 8.3 and autovacuum launcher

2007-10-02 Thread Tom Lane
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

Re: [HACKERS] First steps with 8.3 and autovacuum launcher

2007-10-02 Thread Michael Paesold
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)

Re: [HACKERS] First steps with 8.3 and autovacuum launcher

2007-10-02 Thread Michael Paesold
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

Re: [HACKERS] First steps with 8.3 and autovacuum launcher

2007-10-01 Thread Tom Lane
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.

Re: [HACKERS] First steps with 8.3 and autovacuum launcher

2007-10-01 Thread Alvaro Herrera
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

Re: [HACKERS] First steps with 8.3 and autovacuum launcher

2007-10-01 Thread Tom Lane
[ 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

Re: [HACKERS] First steps with 8.3 and autovacuum launcher

2007-10-01 Thread Tom Lane
"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

Re: [HACKERS] First steps with 8.3 and autovacuum launcher

2007-10-01 Thread Tom Lane
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

Re: [HACKERS] First steps with 8.3 and autovacuum launcher

2007-10-01 Thread Matthew T. O'Connor
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

Re: [HACKERS] First steps with 8.3 and autovacuum launcher

2007-10-01 Thread Alvaro Herrera
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

Re: [HACKERS] First steps with 8.3 and autovacuum launcher

2007-10-01 Thread Tom Lane
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

Re: [HACKERS] First steps with 8.3 and autovacuum launcher

2007-10-01 Thread Simon Riggs
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

Re: [HACKERS] First steps with 8.3 and autovacuum launcher

2007-10-01 Thread Alvaro Herrera
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. >> >>

Re: [HACKERS] First steps with 8.3 and autovacuum launcher

2007-10-01 Thread Matthew T. O'Connor
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'

Re: [HACKERS] First steps with 8.3 and autovacuum launcher

2007-10-01 Thread Tom Lane
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

Re: [HACKERS] First steps with 8.3 and autovacuum launcher

2007-10-01 Thread Alvaro Herrera
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

Re: [HACKERS] First steps with 8.3 and autovacuum launcher

2007-10-01 Thread Heikki Linnakangas
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

Re: [HACKERS] First steps with 8.3 and autovacuum launcher

2007-10-01 Thread Tom Lane
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

Re: [HACKERS] First steps with 8.3 and autovacuum launcher

2007-10-01 Thread Gregory Stark
"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 ?

Re: [HACKERS] First steps with 8.3 and autovacuum launcher

2007-10-01 Thread Guillaume Smet
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

Re: [HACKERS] First steps with 8.3 and autovacuum launcher

2007-10-01 Thread Stefan Kaltenbrunner
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

Re: [HACKERS] First steps with 8.3 and autovacuum launcher

2007-10-01 Thread Guillaume Smet
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

Re: [HACKERS] First steps with 8.3 and autovacuum launcher

2007-09-30 Thread Stefan Kaltenbrunner
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

Re: [HACKERS] First steps with 8.3 and autovacuum launcher

2007-09-22 Thread Guillaume Smet
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_

Re: [HACKERS] First steps with 8.3 and autovacuum launcher

2007-09-22 Thread Tom Lane
"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

Re: [HACKERS] First steps with 8.3 and autovacuum launcher

2007-09-22 Thread Guillaume Smet
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

Re: [HACKERS] First steps with 8.3 and autovacuum launcher

2007-09-19 Thread Decibel!
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

Re: [HACKERS] First steps with 8.3 and autovacuum launcher

2007-09-19 Thread Guillaume Smet
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

Re: [HACKERS] First steps with 8.3 and autovacuum launcher

2007-09-18 Thread Decibel!
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