Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id 056B49FB335 for ; Wed, 27 Sep 2006 13:40:06 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 57862-03 for ; Wed, 27 Sep 2006 16:40:01 +0000 (UTC) X-Greylist: delayed 00:31:48.222657 by SQLgrey- Received: from www.axa.it (www.axa.it [194.242.232.5]) by postgresql.org (Postfix) with ESMTP id 524719FB2DF for ; Wed, 27 Sep 2006 13:40:01 -0300 (ADT) Received: from localhost (localhost.localdomain [127.0.0.1]) by localhost.axa.it (Postfix) with ESMTP id 59279A50442; Wed, 27 Sep 2006 18:40:00 +0200 (CEST) Received: from www.axa.it ([127.0.0.1]) by localhost (www.axa.it [127.0.0.1]) (amavisd-new, port 10024) with LMTP id 12366-01-8; Wed, 27 Sep 2006 18:39:58 +0200 (CEST) Received: from [192.168.0.71] (adsl-ull-84-199.48-151.net24.it [151.48.199.84]) by www.axa.it (Postfix) with ESMTP id 3F2D8A5043E; Wed, 27 Sep 2006 18:39:58 +0200 (CEST) Message-ID: <451AA976.8090403@axa.it> Date: Wed, 27 Sep 2006 18:40:22 +0200 From: Edoardo Ceccarelli User-Agent: Thunderbird 1.5.0.7 (Windows/20060909) MIME-Version: 1.0 To: Bill Moran Cc: pgsql-performance@postgresql.org Subject: Re: autovacuum on a -mostly- r/o table References: <451AA1FE.9050704@axa.it> <20060927121454.36b9fc6c.wmoran@collaborativefusion.com> In-Reply-To: <20060927121454.36b9fc6c.wmoran@collaborativefusion.com> Content-Type: multipart/alternative; boundary="------------080508090605060708010205" X-Virus-Scanned: amavisd-new at axa.it X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.497 tagged_above=0 required=5 tests=HTML_40_50, HTML_MESSAGE X-Spam-Level: X-Archive-Number: 200609/363 X-Sequence-Number: 21049 This is a multi-part message in MIME format. --------------080508090605060708010205 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Bill Moran wrote: > In response to Edoardo Ceccarelli : >> >> I have read that autovacuum cannot check to see pg load before launching >> vacuum but is there any patch about it? that would sort out the problem >> in a good and simple way. >> Otherwise, which kind of set of parameters I should put in autovacuum >> configuration? I am stuck because in our case the table gets mostly read >> and if I set up things as to vacuum the table after a specific amount of >> insert/updates, I cannot foresee whether this could happen during >> daytime when server is under high load. >> How can I configure the vacuum to run after the daily batch insert/update? >> > > It doesn't sound as if your setup is a good match for autovacuum. You > might be better off going back to the cron vacuums. That's the > beauty of Postgres -- it gives you the choice. > > If you want to continue with autovac, you may want to experiment with > vacuum_cost_delay and associated parameters, which can lessen the > impact of vacuuming. > > The db is constantly monitored during high peak so that we can switch to a backup pg7.3 database that is being vacuumed every night. This is giving me the opportunity to try it so I tried this: vacuum_cost_delay = 200 vacuum_cost_page_hit = 5 vacuum_cost_page_miss = 10 vacuum_cost_page_dirty = 20 vacuum_cost_limit = 100 I know these values affect the normal vacuum process but apparently this means setting #autovacuum_vacuum_cost_delay = -1 # default vacuum cost delay for # autovac, -1 means use # vacuum_cost_delay and #autovacuum_vacuum_cost_limit = -1 # default vacuum cost limit for # autovac, -1 means use # vacuum_cost_limit for the rest of them I am currently trying the deafults: #autovacuum_naptime = 60 # time between autovacuum runs, in secs #autovacuum_vacuum_threshold = 1000 # min # of tuple updates before vacuum #autovacuum_analyze_threshold = 500 # min # of tuple updates before analyze #autovacuum_vacuum_scale_factor = 0.4 # fraction of rel size before vacuum #autovacuum_analyze_scale_factor = 0.2 # fraction of rel size before analyze Does anybody know which process is actually AUTO-vacuum-ing the db? So that I can check when is running... --------------080508090605060708010205 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit

Bill Moran wrote: 
In response to Edoardo Ceccarelli <eddy@axa.it>:

I have read that autovacuum cannot check to see pg load before launching 
vacuum but is there any patch about it? that would sort out the problem 
in a good and simple way.
Otherwise, which kind of set of parameters I should put in autovacuum 
configuration? I am stuck because in our case the table gets mostly read 
and if I set up things as to vacuum the table after a specific amount of 
insert/updates, I cannot foresee whether this could happen during 
daytime when server is under high load.
How can I configure the vacuum to run after the daily batch insert/update?
    

It doesn't sound as if your setup is a good match for autovacuum.  You
might be better off going back to the cron vacuums.  That's the
beauty of Postgres -- it gives you the choice.

If you want to continue with autovac, you may want to experiment with
vacuum_cost_delay and associated parameters, which can lessen the
impact of vacuuming.
The db is constantly monitored during high peak so that we can switch to a backup pg7.3 database that is being vacuumed every night.
This is giving me the opportunity to try it so I tried this:

vacuum_cost_delay = 200
vacuum_cost_page_hit = 5
vacuum_cost_page_miss = 10
vacuum_cost_page_dirty = 20
vacuum_cost_limit = 100

I know these values affect the normal vacuum process but apparently this means setting
#autovacuum_vacuum_cost_delay = -1      # default vacuum cost delay for
                                        # autovac, -1 means use
                                        # vacuum_cost_delay

and
#autovacuum_vacuum_cost_limit = -1      # default vacuum cost limit for
                                        # autovac, -1 means use
                                        # vacuum_cost_limit


for the rest of them I am currently trying the deafults:

#autovacuum_naptime = 60                # time between autovacuum runs, in secs
#autovacuum_vacuum_threshold = 1000     # min # of tuple updates before vacuum
#autovacuum_analyze_threshold = 500     # min # of tuple updates before analyze
#autovacuum_vacuum_scale_factor = 0.4   # fraction of rel size before vacuum
#autovacuum_analyze_scale_factor = 0.2  # fraction of rel size before analyze

Does anybody know which process is actually AUTO-vacuum-ing the db?
So that I can check when is running...




--------------080508090605060708010205--