Skip site navigation (1) Skip section navigation (2)

Re: autovacuum next steps, take 2

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Alvaro Herrera <alvherre(at)commandprompt(dot)com>
Cc: "Matthew T(dot) O'Connor" <matthew(at)zeut(dot)net>, "Jim C(dot) Nasby" <jim(at)nasby(dot)net>, Hackers <pgsql-hackers(at)postgresql(dot)org>, Gregory Stark <stark(at)enterprisedb(dot)com>
Subject: Re: autovacuum next steps, take 2
Date: 2007-02-27 03:26:40
Message-ID: 10180.1172546800@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackers
Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
> Tom Lane wrote:
>> I think an absolute minimum requirement for a sane design is that no two
>> workers ever try to vacuum the same table concurrently,

> FWIW, I've always considered this to be a very important and obvious
> issue, and I think I've neglected mentioning it (maybe I did too few
> times).  But I think this is pretty easy to do, just have each worker
> advertise the current table it's working on in shared memory, and add a
> recheck loop on the table-pick algorithm (with appropriate grabs of the
> autovacuum lwlock), to make sure no one starts to vacuum the same table
> you're going to process, at the same time.

Well, any of these proposals need that at the bottom level, to prevent
race conditions.  But I'd prefer a design that wasn't positively
encouraging multiple workers to try to pick the same table concurrently.
Not only is that wasteful, but it makes it harder to predict what is the
real behavior that emerges after race conditions are backed off from.

BTW, to what extent might this whole problem be simplified if we adopt
chunk-at-a-time vacuuming (compare current discussion with Galy Lee)?
If the unit of work has a reasonable upper bound regardless of table
size, maybe the problem of big tables starving small ones goes away.

			regards, tom lane

In response to

Responses

pgsql-hackers by date

Next:From: Matthew T. O'ConnorDate: 2007-02-27 03:26:49
Subject: Re: autovacuum next steps, take 2
Previous:From: Rusty ConoverDate: 2007-02-27 03:24:20
Subject: Expanding DELETE/UPDATE returning

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group