Обсуждение: Autovacuum to-do list
Hackers, This is a list of things people mentioned as interesting to do for vacuum/autovacuum, during the last autovacuum discussion thread. Some of them are wishful thinking, others are doable. Neil, Gavin: both of you mentioned that you didn't like autovacuum as a long term solution. May I ask you if you have more suggestions for this list? * Enable autovacuum by default. Get some field experience with it first, so the worst bugs are covered. (Has anybody tested it?) * Add per-table vacuum delay parameters. * Integrate the FSM with autovacuum. Maybe invent the concept of a "dead space map". This could be a bitmap per heap segment, where we keep a bit set for eachpage in the segment that has dead tuples. * Make the decision taking about what database to vacuum be smarter. Right now, we only consider what database was least recently vacuumed. We could have a per-database counter of dead tuplesin pgstats; we have to figure out a way to use that and not cause starvation for less-used databases. * Make XID wraparound issues be determined on a per-table basis. * Implement some sort of maintenance window where vacuum policy would be more aggresive. Maybe the reverse: specify somehours at which vacuum should not run at all. One alternative for this is to suggest changing autovacuum parameters froma script to be run by cron or pgAgent. * Implement partial vacuums. A partial vacuum would scan only a portion of the table looking for dead tuples, then stop. Or maybe not partial, but insteadvacuum a portion, then stop, release locks, sleep for a while, then continue with the rest. * Have autovacuum daemons per-database or per-tablespace. * Use REINDEX instead of updating the index for each tuple, if the dead tuple count is too high. * Stop a running VACUUM if the system load is too high. -- Alvaro Herrera (<alvherre[a]alvh.no-ip.org>) "La verdad no siempre es bonita, pero el hambre de ella sí"
On Wed, 27 Jul 2005, Alvaro Herrera wrote: > Hackers, > > This is a list of things people mentioned as interesting to do for > vacuum/autovacuum, during the last autovacuum discussion thread. Some > of them are wishful thinking, others are doable. > > Neil, Gavin: both of you mentioned that you didn't like autovacuum as a > long term solution. May I ask you if you have more suggestions for this > list? My argument against autovacuum integration was more about the fact that we shouldn't automate vacuuming in the backend until the cost of vacuum reduces significantly. The fact is that it has been integrated so I think the following might be worth considering: * Change how we track the need to vacuum I don't really think the use of row level stats are the bestindicator for a need to vacuum. I wonder if we could keep acounterfor each segment (ie, every 1 GB file backing a heap orindex) or file which indicates the number of blocks whichhave beendirtied since the last vacuum. The user would specify a thresholdof X blocks and once we reached that we wouldinvoke vacuum. This means we can do without row level stats, I think, at theexpense of having to maintain state in shared memory for eachtableand index (or segment). I'm not sure if that is a goodtrade off or not but it reminds me of counting cards in blackjack--and good things always come of that ;-). That's all I can think of at the moment. Thanks, Gavin
On Thu, Jul 28, 2005 at 11:42:22AM +1000, Gavin Sherry wrote: > On Wed, 27 Jul 2005, Alvaro Herrera wrote: > > * Change how we track the need to vacuum > > I don't really think the use of row level stats are the best > indicator for a need to vacuum. I wonder if we could keep a > counter for each segment (ie, every 1 GB file backing a heap or > index) or file which indicates the number of blocks which have been > dirtied since the last vacuum. The user would specify a threshold > of X blocks and once we reached that we would invoke vacuum. Interesting approach. I don't think it's too difficult to implement. We just need a new stat message indicating that block N was dirtied because of new or deleted tuples (hint bit changes need not apply) -- Alvaro Herrera (<alvherre[a]alvh.no-ip.org>) "I love the Postgres community. It's all about doing things _properly_. :-)" (David Garamond)
All the items you mentioned look like 8.2 issues to me. But here are some thoughts. Alvaro Herrera wrote: >* Enable autovacuum by default. > > Get some field experience with it first, so the worst bugs are covered. > (Has anybody tested it?) > > I have done some testing and it seems to be working ok. I am planning on doing some more in depth tests this week. >* Stop a running VACUUM if the system load is too high. > What if vacuum used a vacuum delay that was equal to the vacuum delay GUC settings * the system load. Or something more sophisticated but this would have the effect of having vacuum automatically throttle down when the system gets busy and throttle back up when the system gets quiet. We would probably set some min / max values but it sounds interesting, thoughts?
On 2005-08-01, "Matthew T. O'Connor" <matthew@zeut.net> wrote: >>* Stop a running VACUUM if the system load is too high. > > What if vacuum used a vacuum delay that was equal to the vacuum delay > GUC settings * the system load. Or something more sophisticated but > this would have the effect of having vacuum automatically throttle down > when the system gets busy and throttle back up when the system gets > quiet. We would probably set some min / max values but it sounds > interesting, thoughts? I'd be very concerned about feedback loop stability; insufficient vacuuming can increase the system load, causing vacuum to get further behind... -- Andrew, Supernews http://www.supernews.com - individual and corporate NNTP services
Andrew - Supernews wrote: >On 2005-08-01, "Matthew T. O'Connor" <matthew@zeut.net> wrote: > > >>>* Stop a running VACUUM if the system load is too high. >>> >>> >>What if vacuum used a vacuum delay that was equal to the vacuum delay >>GUC settings * the system load. Or something more sophisticated but >>this would have the effect of having vacuum automatically throttle down >>when the system gets busy and throttle back up when the system gets >>quiet. We would probably set some min / max values but it sounds >>interesting, thoughts? >> >> > >I'd be very concerned about feedback loop stability; insufficient >vacuuming can increase the system load, causing vacuum to get further >behind... > Right which is why we would need to enforce some max value so that vacuuming will never be totally squeezed out.
On Mon, Aug 01, 2005 at 10:22:14PM -0400, Matthew T. O'Connor wrote: [..snipped..] > > Right which is why we would need to enforce some max value so that > vacuuming will never be totally squeezed out. > greetings, I'm a linux guy, so please forgive my ignorance, but is it even possible to determine load average on win32? regards, J
On Mon, Aug 01, 2005 at 10:35:00PM -0400, Jeff MacDonald wrote:
> On Mon, Aug 01, 2005 at 10:22:14PM -0400, Matthew T. O'Connor wrote:
> [..snipped..]
> > Right which is why we would need to enforce some max value so that
> > vacuuming will never be totally squeezed out.
> I'm a linux guy, so please forgive my ignorance, but is it even possible to
> determine load average on win32?
Oooo... That's a lot of win32 ignorance to ignore... :-)
Push control-alt-delete and look under "Performance". I believe
Windows may even keep *more* information that Linux. It's all a
question of figuring out what the Win32 API calls are to get what you
are looking for. Most concepts found in one system are also found in
the other. I would assume yes before no.
Cheers,
mark
--
mark@mielke.cc / markm@ncf.ca / markm@nortel.com __________________________
. . _ ._ . . .__ . . ._. .__ . . . .__ | Neighbourhood Coder
|\/| |_| |_| |/ |_ |\/| | |_ | |/ |_ |
| | | | | \ | \ |__ . | | .|. |__ |__ | \ |__ | Ottawa, Ontario, Canada
One ring to rule them all, one ring to find them, one ring to bring them all and in the darkness
bindthem...
http://mark.mielke.cc/
> -----Original Message----- > From: pgsql-hackers-owner@postgresql.org > [mailto:pgsql-hackers-owner@postgresql.org] On Behalf Of > mark@mark.mielke.cc > Sent: 02 August 2005 05:55 > To: Jeff MacDonald > Cc: Matthew T. O'Connor; andrew@supernews.com; > pgsql-hackers@postgresql.org > Subject: Re: [HACKERS] Autovacuum to-do list > > On Mon, Aug 01, 2005 at 10:35:00PM -0400, Jeff MacDonald wrote: > > On Mon, Aug 01, 2005 at 10:22:14PM -0400, Matthew T. O'Connor wrote: > > [..snipped..] > > > Right which is why we would need to enforce some max > value so that > > > vacuuming will never be totally squeezed out. > > I'm a linux guy, so please forgive my ignorance, but is it > even possible to > > determine load average on win32? > > Oooo... That's a lot of win32 ignorance to ignore... :-) > > Push control-alt-delete and look under "Performance". I believe > Windows may even keep *more* information that Linux. It's all a > question of figuring out what the Win32 API calls are to get what you > are looking for. Most concepts found in one system are also found in > the other. I would assume yes before no. Yes - Win32 has Performance Counters which are an extensible set of monitored objects, and a rich API to access them - see http://msdn.microsoft.com/library/default.asp?url=/library/en-us/perfmon /base/performance_monitoring.asp for details. The perfmon program (Start -> Run -> perfmon -> OK) gives a GUI interface to play with in which you can monitor any installed counter. Regards, Dave.
On Tue, Aug 02, 2005 at 08:25:00AM +0100, Dave Page wrote: [..snipped..] > > > > Oooo... That's a lot of win32 ignorance to ignore... :-) > > > > Push control-alt-delete and look under "Performance". I believe > > Windows may even keep *more* information that Linux. It's all a > > question of figuring out what the Win32 API calls are to get what you > > are looking for. Most concepts found in one system are also found in > > the other. I would assume yes before no. > > Yes - Win32 has Performance Counters which are an extensible set of > monitored objects, and a rich API to access them - see > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/perfmon > /base/performance_monitoring.asp for details. The perfmon program (Start > -> Run -> perfmon -> OK) gives a GUI interface to play with in which you > can monitor any installed counter. > > Regards, Dave. ok, ok.. you can tell I haven't seriously used win32 since the "win 98" days (heck, haven't written any kind of windows application since around thetime of win 3.11 or so).. I told you I was ignorant of these things! :) I don't have a win32 machine to try either of the above with, so I'll take the words of the more experienced as fact :) regards, J