From: Edoardo Ceccarelli <eddy@axa.it>
To: Bill Moran <wmoran@collaborativefusion.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: autovacuum on a -mostly- r/o table
Date: Wed, 27 Sep 2006 18:40:22 +0200
Message-ID: <451AA976.8090403@axa.it> (raw)
In-Reply-To: <20060927121454.36b9fc6c.wmoran@collaborativefusion.com>
References: <451AA1FE.9050704@axa.it>
<20060927121454.36b9fc6c.wmoran@collaborativefusion.com>
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...
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: eddy@axa.it, wmoran@collaborativefusion.com
Subject: Re: autovacuum on a -mostly- r/o table
In-Reply-To: <451AA976.8090403@axa.it>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox