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

Re: Autovacuum and stats_row_level

From: Chander Ganesan <chander(at)otg-nc(dot)com>
To: d(dot)wall(at)computer(dot)org
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Autovacuum and stats_row_level
Date: 2008-01-30 03:14:32
Message-ID: 479FEB98.1010502@otg-nc.com (view raw or flat)
Thread:
Lists: pgsql-general
David Wall wrote:
> Noted that to use autovacuum we need to turn on stats_row_level (along 
> with stats_start_collector that is on by default).  Since 
> stats_row_level is off by default, I wonder what sort of overhead is 
> incurred since it sounds like it could add up if it's storing 
> additional stats information on every row update, though it's not 
> clear to me just what it means to have row level stats.
There is overhead. I think it is somewhat related to your query load  
However, I do know that there is some rate limiting logic in place to 
prevent the backends from sending too many messages to the stats collector.
>
> We currently use cron to run vacuum and analyze daily in the early 
> morning.  It's  never been an issue, but the databases are getting 
> bigger and we wonder if autovacuum is a good addition to our operations.
AVD doesn't pay attention to the load on your server, and I assume you 
cron that stuff when things are at a lull.  In that case, you might be 
worse off.    Also, if your load is such that AVD wouldn't kick off 
anyways during the day, cron would give you better results than AVD 
(though you could tune AVD to balance this out)

The benefit is that AVD, properly adjusted, will keep the number of dead 
tuples from getting "too bad".  Suppose you had a system that did lots 
of updates and deletes throughout the day, with load increasing daily.  
A nightly vacuum might allow your tables to continue to grow (albeit 
more slowly, since more dead tuples would accumulate before a nightly 
vacuum), whereas a well tuned AVD would prevent that (by performing 
periodic vacuums throughout the day). 
>
> Are the default values for autovacuum generally good enough for most 
> deployments?  The various parameters are a bit complicated to 
> understand, so tweaking them is something we're naturally concerned 
> about doing.
To really understand that one would have to know about your specific 
deployment, and how you use your tables.  The reason AVD is so 
customizable is because different environments have different needs.  I 
would leave them at the defaults to start with, but keep in mind that 
they might not be aggressive enough - or perhaps too aggressive - for 
your particular needs.  Once you have some stats, you can look at the 
statistics and figure out an optimum value for how often AVD should 
process a table (based on load, statistics, table sizes, etc.)
>
> Will autovacuum running allow regular vacuum and analyze commands to 
> run faster?  Can it replace them entirely, or do we still need to run 
> them from time to time?
You shouldn't need to run them if you have AVD enabled.  It won't speed 
them up...it will just periodically run them when the number of changed 
rows is significant, or dead tuples is significant...thus allowing you 
to not have to lose sleep over vacuum operations occurring.
>
> Can autovacuum be configured to run in a backup server that is in 
> "recovery" mode handling pg_standby WAL file updates?
>
No.  Though vacuums on the master would in effect also occur on the 
standby node.  So as long as you do vacuums on the master you'll be okay.

-- 
Chander Ganesan
Open Technology Group, Inc.
One Copley Parkway, Suite 210
Morrisville, NC  27560
Phone: 877-258-8987/919-463-0999
http://www.otg-nc.com
Ask me about our Expert PostgreSQL & PostGIS Training



-- 
Chander Ganesan
Open Technology Group, Inc.
One Copley Parkway, Suite 210
Morrisville, NC  27560
Phone: 877-258-8987/919-463-0999
http://www.otg-nc.com


In response to

Responses

pgsql-general by date

Next:From: Vishal AroraDate: 2008-01-30 04:15:44
Subject: Re: Backup
Previous:From: Joshua D. DrakeDate: 2008-01-30 03:08:31
Subject: Re: OT - pg perl DBI question

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