From: Bill Moran <wmoran@collaborativefusion.com>
To: Edoardo Ceccarelli <eddy@axa.it>
Cc: pgsql-performance@postgresql.org
Cc: pgsql-admin@postgresql.org
Subject: Re: [PERFORM] autovacuum on a -mostly- r/o table
Date: Wed, 27 Sep 2006 12:14:54 -0400
Message-ID: <20060927121454.36b9fc6c.wmoran@collaborativefusion.com> (raw)
In-Reply-To: <451AA1FE.9050704@axa.it>
References: <451AA1FE.9050704@axa.it>
In response to Edoardo Ceccarelli <eddy@axa.it>:
> Hello,
>
> we are running a 7.3 postgres db with only a big table (avg
> 500.000records) and 7 indexes for a search engine.
> we have 2 of this databases and we can switch from one to another.
> Last week we decided to give a try to 8.1 on one of them and everything
> went fine, db is faster (about 2 or 3 times in our case) and the server
> load is higher - which should mean that faster response time is achieved
> by taking a better use of the server.
>
> We also activated the autovacuum feature to give it a try and that's
> were our problems started.
> I left the standard autovacuum configuration just to wait and see, pg
> decided to start a vacuum on the table just midday when users were
> launching search queries on the table and server load reached a very
> high value so that in a couple of minutes the db was unusable
>
> With pg7.3 we use to vacuum the db night time, mostly because the insert
> and updates in this table is made in a batch way: a single task that
> puts 100.000 records in the db in 10/20minutes, so the best time to
> actually vacuum the db would be after this batch.
>
> 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.
--
Bill Moran
Collaborative Fusion Inc.
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: wmoran@collaborativefusion.com, eddy@axa.it, pgsql-admin@postgresql.org
Subject: Re: [PERFORM] autovacuum on a -mostly- r/o table
In-Reply-To: <20060927121454.36b9fc6c.wmoran@collaborativefusion.com>
* 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