From: tv@fuzzy.cz
To: Tobias Brox <tobias@nordicbet.com>
Cc: Gábor Farkas <gabor@nekomancer.net>
Cc: pgsql-performance@postgresql.org
Subject: Re: autovacuum: recommended?
Date: Fri, 16 Nov 2007 14:38:23 +0100 (CET)
Message-ID: <61575.212.24.144.68.1195220303.squirrel@mail.fuzzy.cz> (raw)
In-Reply-To: <20071116111355.GB1195@mail.nordicbet.com>
References: <473D659B.1070008@nekomancer.net>
<20071116111355.GB1195@mail.nordicbet.com>
> That being said, we have some huge tables in our database and pretty
> much traffic, and got quite some performance problems when the
> autovacuum kicked in and started vacuuming those huge tables, so we're
> currently running without. Autovacuum can be tuned to not touch those
> tables, but we've chosen to leave it off.
We had some performance problems with the autovacuum on large and
frequently modified tables too - but after a little bit of playing with
the parameters the overall performance is much better than it was before
the autovacuuming.
The table was quite huge (say 20k of products along with detailed
descriptions etc.) and was completely updated and about 12x each day, i.e.
it qrew to about 12x the original size (and 11/12 of the rows were dead).
This caused a serious slowdown of the application each day, as the
database had to scan 12x more data.
We set up autovacuuming with the default parameters, but it interfered
with the usual traffic - we had to play a little with the parameters
(increase the delays, decrease the duration or something like that) and
now it runs much better than before. No nightly vacuuming, no serious
performance degradation during the day, etc.
So yes - autovacuuming is recommended, but in some cases the default
parameters have to be tuned a little bit.
tomas
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: tv@fuzzy.cz, tobias@nordicbet.com, gabor@nekomancer.net
Subject: Re: autovacuum: recommended?
In-Reply-To: <61575.212.24.144.68.1195220303.squirrel@mail.fuzzy.cz>
* 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