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
53 matches
Mail list logo
