Tom Lane wrote:
> Alvaro Herrera <[EMAIL PROTECTED]> writes:
> > Tom Lane wrote:
> >> 1. Grab the AutovacSchedule LWLock exclusively.
> >> 2. Check to see if another worker is currently processing
> >> that table; if so drop LWLock and go to next list entry.
> >> 3. Recompute whether table needs va
Tom Lane <[EMAIL PROTECTED]> wrote:
> Who said anything about external schedulers? I remind you that this is
> AUTOvacuum. If you want to implement manual scheduling you can still
> use plain 'ol vacuum commands.
I think we can split autovacuum into two (or more?) functions:
task gatherers and
ITAGAKI Takahiro <[EMAIL PROTECTED]> writes:
> Tom Lane <[EMAIL PROTECTED]> wrote:
>> In any case, I still haven't seen a good case made why a global work
>> queue will provide better behavior than each worker keeping a local
>> queue.
> If we have some external vacuum schedulers, we need to see a
Tom Lane <[EMAIL PROTECTED]> wrote:
> In any case, I still haven't seen a good case made why a global work
> queue will provide better behavior than each worker keeping a local
> queue. The need for small "hot" tables to be visited more often than
> big tables suggests to me that a global queue w
Galy Lee <[EMAIL PROTECTED]> writes:
> We can use the fix-size share memory to maintain such a queue. The
> maximum task size is the number of all tables. So the size of the queue
> can be the same with max_fsm_relations which is usually larger than the
> numbers of tables and indexes in the cluste
Hi, Alvaro
Alvaro Herrera wrote:
> keep such a task list in shared memory, because we aren't able to grow
> that memory after postmaster start.
We can use the fix-size share memory to maintain such a queue. The
maximum task size is the number of all tables. So the size of the queue
can be the sa
Alvaro Herrera <[EMAIL PROTECTED]> writes:
> Tom Lane wrote:
>> 1. Grab the AutovacSchedule LWLock exclusively.
>> 2. Check to see if another worker is currently processing
>> that table; if so drop LWLock and go to next list entry.
>> 3. Recompute whether table needs vacuuming; if not,
>> drop LWL
Tom Lane wrote:
> I suggest that maybe we don't need exposed TODO lists at all. Rather
> the workers could have internal TODO lists that are priority-sorted
> in some way, and expose only their current table OID in shared memory.
> Then the algorithm for processing each table in your list is
>
>
Alvaro Herrera wrote:
> worker to-do list
> -
>
> When each worker starts, it determines which tables to process in the
> usual fashion: get pg_autovacuum and pgstat data and compute the
> equations.
>
> The worker then takes a "snapshot" of what's currently going on in the
> dat
Galy Lee wrote:
>
> Alvaro Herrera wrote:
> >worker to-do list
> >-
> >It removes from its to-do list the tables being processed. Finally, it
> >writes the list to disk.
>
> I am worrying about the worker-to-do-list in your proposal. I think
> worker isn't suitable to maintain an
Galy Lee <[EMAIL PROTECTED]> writes:
> I am worrying about the worker-to-do-list in your proposal. I think
> worker isn't suitable to maintain any vacuum task list; instead
> it is better to maintain a unified vacuum task queue on autovacuum share
> memory.
Shared memory is fixed-size.
Alvaro Herrera wrote:
>worker to-do list
>-
>It removes from its to-do list the tables being processed. Finally, it
>writes the list to disk.
I am worrying about the worker-to-do-list in your proposal. I think
worker isn't suitable to maintain any vacuum task list; instead
it is
Tom Lane wrote:
"Matthew T. O'Connor" writes:
It's not clear to me why a worker cares that there is a new worker,
since the new worker is going to ignore all the tables that are already
claimed by all worker todo lists.
That seems wrong to me, since it means that new workers will ignore
tabl
"Matthew T. O'Connor" writes:
> Does a new worker really care about the PID of other workers or what
> table they are currently working on?
As written, it needs the PIDs so it can read in the other workers' todo
lists (which are in files named by PID).
> It's not clear to me why a worker cares
My initial reaction is that this looks good to me, but still a few
comments below.
Alvaro Herrera wrote:
Here is a low-level, very detailed description of the implementation of
the autovacuum ideas we have so far.
launcher's dealing with databases
-
[ Snip ]
Here is a low-level, very detailed description of the implementation of
the autovacuum ideas we have so far.
launcher's dealing with databases
-
We'll add a new member "nexttime" to the autovac_dbase struct, which
will be the time_t of the next time a worker needs
Tom Lane wrote:
> Saving the array is
> expensive both in runtime and code complexity, and I don't believe we
> can trust it later --- at least not without even more expensive-and-
> complex measures, such as WAL-logging every such save :-(
I don’t understand well the things you are worrying about
On Feb 26, 2007, at 12:49 PM, Alvaro Herrera wrote:
Jim C. Nasby wrote:
That's why I'm thinking it would be best to keep the maximum size of
stuff for the second worker small. It probably also makes sense to
tie
it to time and not size, since the key factor is that you want it
to hit
the
On Tue, Feb 27, 2007 at 12:12:22PM -0500, Matthew T. O'Connor wrote:
> Jim C. Nasby wrote:
> >On Tue, Feb 27, 2007 at 12:00:41AM -0300, Alvaro Herrera wrote:
> >>Jim C. Nasby wrote:
> >>
> >>>The advantage to keying this to autovac_naptime is that it means we
> >>>don't need another GUC, but after
Jim C. Nasby wrote:
On Tue, Feb 27, 2007 at 12:00:41AM -0300, Alvaro Herrera wrote:
Jim C. Nasby wrote:
The advantage to keying this to autovac_naptime is that it means we
don't need another GUC, but after I suggested that before I realized
that's probably not the best idea. For example, I've
On Tue, Feb 27, 2007 at 12:00:41AM -0300, Alvaro Herrera wrote:
> Jim C. Nasby wrote:
>
> > The advantage to keying this to autovac_naptime is that it means we
> > don't need another GUC, but after I suggested that before I realized
> > that's probably not the best idea. For example, I've seen clu
On Tue, Feb 27, 2007 at 12:54:28AM -0500, Matthew T. O'Connor wrote:
> Jim C. Nasby wrote:
> >On Mon, Feb 26, 2007 at 10:18:36PM -0500, Matthew T. O'Connor wrote:
> >>Jim C. Nasby wrote:
> >>Here is a worst case example: A DB with 6 tables all of which are highly
> >>active and will need to be vac
On Tue, Feb 27, 2007 at 01:26:00AM -0500, Matthew T. O'Connor wrote:
> Tom Lane wrote:
> >"Matthew T. O'Connor" writes:
> >>I'm not sure what you are saying here, are you now saying that partial
> >>vacuum won't work for autovac? Or are you saying that saving state as
> >>Jim is describing abov
Tom Lane wrote:
"Matthew T. O'Connor" writes:
I'm not sure what you are saying here, are you now saying that partial
vacuum won't work for autovac? Or are you saying that saving state as
Jim is describing above won't work?
I'm saying that I don't like the idea of trying to "stop on a dime"
"Matthew T. O'Connor" writes:
> I'm not sure what you are saying here, are you now saying that partial
> vacuum won't work for autovac? Or are you saying that saving state as
> Jim is describing above won't work?
I'm saying that I don't like the idea of trying to "stop on a dime" by
saving the
Tom Lane wrote:
"Jim C. Nasby" <[EMAIL PROTECTED]> writes:
The proposal to save enough state to be able to resume a vacuum at
pretty much any point in it's cycle might work; we'd have to benchmark
it. With the default maintenance_work_mem of 128M it would mean writing
out 64M of state every min
Jim C. Nasby wrote:
On Mon, Feb 26, 2007 at 10:18:36PM -0500, Matthew T. O'Connor wrote:
Jim C. Nasby wrote:
Here is a worst case example: A DB with 6 tables all of which are highly
active and will need to be vacuumed constantly. While this is totally
hypothetical, it is how I envision things
On Tue, Feb 27, 2007 at 12:37:42AM -0500, Tom Lane wrote:
> "Jim C. Nasby" <[EMAIL PROTECTED]> writes:
> > The proposal to save enough state to be able to resume a vacuum at
> > pretty much any point in it's cycle might work; we'd have to benchmark
> > it. With the default maintenance_work_mem of
On Mon, Feb 26, 2007 at 10:18:36PM -0500, Matthew T. O'Connor wrote:
> Jim C. Nasby wrote:
> >On Mon, Feb 26, 2007 at 06:23:22PM -0500, Matthew T. O'Connor wrote:
> >>I'm not sure how pg_class.relpages is maintained but what happens to a
> >>bloated table? For example, a 100 row table that is con
"Jim C. Nasby" <[EMAIL PROTECTED]> writes:
> The proposal to save enough state to be able to resume a vacuum at
> pretty much any point in it's cycle might work; we'd have to benchmark
> it. With the default maintenance_work_mem of 128M it would mean writing
> out 64M of state every minute on aver
On Mon, Feb 26, 2007 at 10:48:49PM -0500, Tom Lane wrote:
> "Matthew T. O'Connor" writes:
> > That does sounds simpler. Is chunk-at-a-time a realistic option for 8.3?
>
> It seems fairly trivial to me to have a scheme where you do one
> fill-workmem-and-scan-indexes cycle per invocation, and stor
On Tue, Feb 27, 2007 at 12:00:41AM -0300, Alvaro Herrera wrote:
> Jim C. Nasby wrote:
>
> > The advantage to keying this to autovac_naptime is that it means we
> > don't need another GUC, but after I suggested that before I realized
> > that's probably not the best idea. For example, I've seen clu
Tom Lane wrote:
"Matthew T. O'Connor" writes:
That does sounds simpler. Is chunk-at-a-time a realistic option for 8.3?
It seems fairly trivial to me to have a scheme where you do one
fill-workmem-and-scan-indexes cycle per invocation, and store the
next-heap-page-to-scan in some handy place (
Tom Lane wrote:
"Matthew T. O'Connor" writes:
Tom Lane wrote:
I'm inclined to propose an even simpler algorithm in which every worker
acts alike;
That is what I'm proposing except for one difference, when you catch up
to an older worker, exit.
No, that's a bad idea, because it means that
"Matthew T. O'Connor" writes:
> That does sounds simpler. Is chunk-at-a-time a realistic option for 8.3?
It seems fairly trivial to me to have a scheme where you do one
fill-workmem-and-scan-indexes cycle per invocation, and store the
next-heap-page-to-scan in some handy place (new pg_class colum
"Matthew T. O'Connor" writes:
> Tom Lane wrote:
>> I'm inclined to propose an even simpler algorithm in which every worker
>> acts alike;
> That is what I'm proposing except for one difference, when you catch up
> to an older worker, exit.
No, that's a bad idea, because it means that any large
Tom Lane wrote:
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.
Tom Lane wrote:
"Jim C. Nasby" <[EMAIL PROTECTED]> writes:
The real problem is trying to set that up in such a fashion that keeps
hot tables frequently vacuumed;
Are we assuming that no single worker instance will vacuum a given table
more than once? (That's not a necessary assumption, certai
Alvaro Herrera <[EMAIL PROTECTED]> 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 negle
Jim C. Nasby wrote:
On Mon, Feb 26, 2007 at 06:23:22PM -0500, Matthew T. O'Connor wrote:
I'm not sure how pg_class.relpages is maintained but what happens to a
bloated table? For example, a 100 row table that is constantly updated
and hasn't been vacuumed in a while (say the admin disabled aut
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, and I don't see
> where that behavior will emerge from your proposal; whereas it's fairly
> easy to make it happen if non-first workers pay attention
[ oh, I forgot to respond to this: ]
"Jim C. Nasby" <[EMAIL PROTECTED]> writes:
> Isn't there a special lock acquired on a relation by vacuum? Can't we
> just check for that?
I think you're thinking that ConditionalLockRelation solves the problem,
but it does not, because it will fail if someone
"Jim C. Nasby" <[EMAIL PROTECTED]> writes:
> On Mon, Feb 26, 2007 at 09:22:42PM -0500, Tom Lane wrote:
>> I'm not liking any of these very much, as they seem critically dependent
>> on impossible-to-tune parameters. I think it'd be better to design this
>> around having the first worker explicitly
Jim C. Nasby wrote:
> The advantage to keying this to autovac_naptime is that it means we
> don't need another GUC, but after I suggested that before I realized
> that's probably not the best idea. For example, I've seen clusters that
> are running dozens-hundreds of databases; in that environment
On Mon, Feb 26, 2007 at 06:23:22PM -0500, Matthew T. O'Connor wrote:
> Alvaro Herrera wrote:
> >Matthew T. O'Connor wrote:
> >>How can you determine what tables can be vacuumed within
> >>autovacuum_naptime?
> >
> >My assumption is that
> >pg_class.relpages * vacuum_cost_page_miss * vacuum_cost_de
On Mon, Feb 26, 2007 at 08:11:44PM -0300, Alvaro Herrera wrote:
> Matthew T. O'Connor wrote:
> > Alvaro Herrera wrote:
>
> > >The second mode is the "hot table worker" mode, enabled when the worker
> > >detects that there's already a worker in the database. In this mode,
> > >the worker is limite
On Mon, Feb 26, 2007 at 09:22:42PM -0500, Tom Lane wrote:
> Alvaro Herrera <[EMAIL PROTECTED]> writes:
> > Matthew T. O'Connor wrote:
> >> I'm not sure it's a good idea to tie this to the vacuum cost delay
> >> settings either, so let me as you this, how is this better than just
> >> allowing the
Tom Lane wrote:
Alvaro Herrera <[EMAIL PROTECTED]> writes:
Matthew T. O'Connor wrote:
I'm not sure it's a good idea to tie this to the vacuum cost delay
settings either, so let me as you this, how is this better than just
allowing the admin to set a new GUC variable like
autovacuum_hot_table_
Alvaro Herrera <[EMAIL PROTECTED]> writes:
> Matthew T. O'Connor wrote:
>> I'm not sure it's a good idea to tie this to the vacuum cost delay
>> settings either, so let me as you this, how is this better than just
>> allowing the admin to set a new GUC variable like
>> autovacuum_hot_table_size_
Alvaro Herrera wrote:
Matthew T. O'Connor wrote:
I'm not sure how pg_class.relpages is maintained but what happens to a
bloated table? For example, a 100 row table that is constantly updated
and hasn't been vacuumed in a while (say the admin disabled autovacuum
for a while), now that small 10
Matthew T. O'Connor wrote:
> Alvaro Herrera wrote:
> >Matthew T. O'Connor wrote:
> >>How can you determine what tables can be vacuumed within
> >>autovacuum_naptime?
> >
> >My assumption is that
> >pg_class.relpages * vacuum_cost_page_miss * vacuum_cost_delay = time to
> >vacuum
> >
> >This is of
Alvaro Herrera wrote:
Matthew T. O'Connor wrote:
How can you determine what tables can be vacuumed within
autovacuum_naptime?
My assumption is that
pg_class.relpages * vacuum_cost_page_miss * vacuum_cost_delay = time to vacuum
This is of course not the reality, because the delay is not how lo
Matthew T. O'Connor wrote:
> Alvaro Herrera wrote:
> >The second mode is the "hot table worker" mode, enabled when the worker
> >detects that there's already a worker in the database. In this mode,
> >the worker is limited to those tables that can be vacuumed in less than
> >autovacuum_naptime, s
Alvaro Herrera wrote:
Jim C. Nasby wrote:
That's why I'm thinking it would be best to keep the maximum size of
stuff for the second worker small. It probably also makes sense to tie
it to time and not size, since the key factor is that you want it to hit
the high-update tables every X number of
Jim C. Nasby wrote:
> That's why I'm thinking it would be best to keep the maximum size of
> stuff for the second worker small. It probably also makes sense to tie
> it to time and not size, since the key factor is that you want it to hit
> the high-update tables every X number of seconds.
>
> If
On Fri, Feb 23, 2007 at 01:22:17PM -0300, Alvaro Herrera wrote:
> Jim C. Nasby wrote:
> > On Thu, Feb 22, 2007 at 10:32:44PM -0500, Matthew T. O'Connor wrote:
>
> > > I'm not sure this is a great idea, but I don't see how this would result
> > > in large numbers of workers working in one database
Jim C. Nasby wrote:
> On Thu, Feb 22, 2007 at 10:32:44PM -0500, Matthew T. O'Connor wrote:
> > I'm not sure this is a great idea, but I don't see how this would result
> > in large numbers of workers working in one database. If workers work
> > on tables in size order, and exit as soon as they
On Thu, Feb 22, 2007 at 10:32:44PM -0500, Matthew T. O'Connor wrote:
> Jim C. Nasby wrote:
> >On Thu, Feb 22, 2007 at 09:32:57AM -0500, Matthew T. O'Connor wrote:
> >
> >>So the heuristic would be:
> >>* Launcher fires off workers into a database at a given interval
> >>(perhaps configurable?)
>
Jim C. Nasby wrote:
On Thu, Feb 22, 2007 at 09:32:57AM -0500, Matthew T. O'Connor wrote:
So the heuristic would be:
* Launcher fires off workers into a database at a given interval
(perhaps configurable?)
* Each worker works on tables in size order.
* If a worker ever catches up to an older
> > > vacuum should be a process with the least amount of voodoo.
> > > If we can just have vacuum_delay and vacuum_threshold, where
> > > threshold allows an arbitrary setting of how much bandwidth we
will
> > > allot to the process, then that is a beyond wonderful thing.
> > >
> > > It is ea
On Thu, Feb 22, 2007 at 09:35:45AM +0100, Zeugswetter Andreas ADI SD wrote:
>
> > vacuum should be a process with the least amount of voodoo.
> > If we can just have vacuum_delay and vacuum_threshold, where
> > threshold allows an arbitrary setting of how much bandwidth
> > we will allot to the
On Thu, Feb 22, 2007 at 09:32:57AM -0500, Matthew T. O'Connor wrote:
> Jim C. Nasby wrote:
> >On Wed, Feb 21, 2007 at 05:40:53PM -0500, Matthew T. O'Connor wrote:
> >
> >>My Proposal: If we require admins to identify hot tables tables, then:
> >>1) Launcher fires-off a worker1 into database X.
Jim C. Nasby wrote:
On Wed, Feb 21, 2007 at 05:40:53PM -0500, Matthew T. O'Connor wrote:
My Proposal: If we require admins to identify hot tables tables, then:
1) Launcher fires-off a worker1 into database X.
2) worker1 deals with "hot" tables first, then regular tables.
3) Launcher continu
On Wed, Feb 21, 2007 at 05:40:53PM -0500, Matthew T. O'Connor wrote:
> My Proposal: If we require admins to identify hot tables tables, then:
> 1) Launcher fires-off a worker1 into database X.
> 2) worker1 deals with "hot" tables first, then regular tables.
> 3) Launcher continues to launch worke
> vacuum should be a process with the least amount of voodoo.
> If we can just have vacuum_delay and vacuum_threshold, where
> threshold allows an arbitrary setting of how much bandwidth
> we will allot to the process, then that is a beyond wonderful thing.
>
> It is easy to determine how much
> Ron Mayer expressed the thought that we're complicating needlessly the
> UI for vacuum_delay, naptime, etc. He proposes that instead of having
> cost_delay etc, we have a mbytes_per_second parameter of some sort.
> This strikes me a good idea, but I think we could make that after this
> proposa
"Alvaro Herrera" <[EMAIL PROTECTED]> writes:
> Greg Stark and Matthew O'Connor say that we're misdirected in having
> more than one worker per tablespace. I say we're not :-)
I did say that. But your comment about using a high cost_delay was fairly
convincing too. It would be a simpler design
Alvaro Herrera wrote:
Ok, scratch that :-) Another round of braindumping below.
I still think this is solution in search of a problem. The main problem
we have right now is that hot tables can be starved from vacuum. Most
of this proposal doesn't touch that. I would like to see that probl
Ok, scratch that :-) Another round of braindumping below.
Launcher starts one worker in each database. This worker is not going
to do vacuum work, just report how much vacuum effort is needed in the
database. "Vacuum effort" is measured as the total number of pages in
need of vacuum, being the
I'm wondering if we can do one better...
Since what we really care about is I/O responsiveness for the rest of
the system, could we just time how long I/O calls take to complete? I
know that gettimeofday can have a non-trivial overhead, but do we care
that much about it in the case of autovac?
On
Gregory Stark wrote:
If we could have autovacuum interrupt a vacuum in mid-sweep, perform a cycle
of vacuums on smaller tables, then resume, that problem would go away. That
sounds too difficult though, but perhaps we could do something nearly as good.
I think to make vacuum has this interrupt
> One option that I've heard before is to have vacuum after a single iteration
> (ie, after it fills maintenance_work_mem and does the index cleanup and the
> second heap pass), remember where it was and pick up from that point next
> time.
>From my experience this is not acceptable... I have tabl
Alvaro Herrera wrote:
>
> Once autovacuum_naptime... autovacuum_max_workers...
> How does this sound?
The knobs exposed on autovacuum feel kinda tangential to
what I think I'd really want to control.
IMHO "vacuum_mbytes_per_second" would be quite a bit more
intuitive than cost_delay, naptime, et
Alvaro Herrera <[EMAIL PROTECTED]> writes:
> Each worker, including the initial one, starts vacuuming tables
> according to pgstat data. They recheck the pgstat data after finishing
> each table, so that a table vacuumed by another worker is not processed
> twice (maybe problematic: a table with h
[EMAIL PROTECTED] (Alvaro Herrera) writes:
> When there is a single worker processing a database, it does not recheck
> pgstat data after each table. This is to prevent a high-update-rate
> table from starving the vacuuming of other databases.
This case is important; I don't think that having mul
Alvaro Herrera wrote:
Matthew T. O'Connor wrote:
On first blush, I'm not sure I like this as it doesn't directly attack
the table starvation problem, and I think it could be a net loss of speed.
VACUUM is I/O bound, as such, just sending multiple vacuum commands at a
DB isn't going to make th
Matthew T. O'Connor wrote:
> Alvaro Herrera wrote:
> >After staring at my previous notes for autovac scheduling, it has become
> >clear that this basics of it is not really going to work as specified.
> >So here is a more realistic plan:
>
> [Snip Detailed Description]
>
> >How does this sound?
>
In an ideal world I think you want precisely one vacuum process running per
tablespace on the assumption that each tablespace represents a distinct
physical device.
The cases where we currently find ourselves wanting more are where small
tables are due for vacuuming more frequently than the time
Alvaro Herrera wrote:
After staring at my previous notes for autovac scheduling, it has become
clear that this basics of it is not really going to work as specified.
So here is a more realistic plan:
[Snip Detailed Description]
How does this sound?
On first blush, I'm not sure I like this a
After staring at my previous notes for autovac scheduling, it has become
clear that this basics of it is not really going to work as specified.
So here is a more realistic plan:
First, we introduce an autovacuum_max_workers parameter, to limit the
total amount of workers that can be running at any
80 matches
Mail list logo
