Its nice to know the evolution of autovacuum and i understand that the suggestion/requirement of "autovacuum at lean hours only" was defeating the whole idea. regds --rajesh kumar mallah. On Fri, Feb 13, 2009 at 11:07 PM, Chris Browne <cbbrowne@xxxxxxx> wrote: > mallah.rajesh@xxxxxxxxx (Rajesh Kumar Mallah) writes: >> why is it not a good idea to give end users control over when they >> want to run it ? > > It's not a particularly good idea to give end users things that they > are likely then to *immediately* use to shoot themselves in the foot. > > Turning off vacuuming "all day" is the sort of thing that is indeed > pretty certain to hurt you when you imagined it was going to help you. > > In particular, if you shut off autovac all day, heavily updated tables > with certain sorts of (pretty common!) update patterns are certain to > "bloat up," to the point that you'll need to do CLUSTER/VACUUM FULL on > them. > > In effect, the practical effect of "autovacuum at lean hours only" is > more reasonably described as "cancel autovacuum and revert to the > elder policy of requiring users to do manual vacuuming." > > It's worth looking at how autovacuum has been evolving over time... > > - When it was introduced, 8.0-ish (maybe it was 8.1 when it became > "official"), it was pretty primitive. > > Autovac was a single process, where you had three controls over > behaviour: > > - You could run autovac, or not; > > - You could exclude specific tables from being processed by autovac > > - There is a capability to vacuum less aggressively by using > delays to reduce autovac I/O usage > > - In 8.3, it was enhanced to add the notion of having multiple vacuum > workers > > There was discussion about having one of those workers restrict > itself to small tables, so that you'd never have the scenario where > the workers were all busy and a small table that needed vacuuming > was left unvacuumed for a long time. It appears that didn't happen, > which seems unfortunate, but that's life... > > You should look at all the "knobs" that *are* offered before deciding > a policy that may be harmful to performance. As things stand now, > there are a couple of ways I could see tuning this: > > - You might check on the GUC variables autovacuum_vacuum_cost_delay > and autovacuum_vacuum_cost_limit, which would allow you to restrict > the I/O cost. > > This might allow you to run autovacuum all the time without > adversely affecting performance. > > - You might come up with a list of the *LARGE* tables that you don't > want vacuumed during the day, and set up a cron job that adds/drops > them from the pg_autovacuum table at the appropriate times. > > This is definitely going to be more finicky, and requires a great > deal more awareness of the tables being updated by your > applications. It makes "autovacuum" a whole lot less "automatic." > > There are known enhancements coming up: > > - In 8.4, there is a capability for VACUUM to only process the > portions of the table known to have been altered. > > That ought to be a better answer than *any* of the fiddling > suggested, to date. Right now, a VACUUM on "public.my_huge_table", > a table 18GB in size, will walk through the entire table, even > though there were only a handful of pages where tuples were > invalidated. > > This is almost certainly the single best improvement possible to > resolve your issue; it seems likely to *directly* address the > problem, and has the considerable merit of not requiring much if > any configuration/reconfiguration/scheduling. > -- > (reverse (concatenate 'string "gro.mca" "@" "enworbbc")) > http://linuxdatabases.info/info/ > "what would we do without C? we would have PASAL, BASI, OBOL, and > Ommon Lisp." -- #Erik > > -- > Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance > -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
