From: Chris Browne <cbbrowne@acm.org>
To: pgsql-performance@postgresql.org
Subject: Re: Need to run CLUSTER to keep performance
Date: Thu, 08 Nov 2007 10:47:44 -0500
Message-ID: <60ir4civrz.fsf@dba2.int.libertyrms.com> (raw)
References: <4732E6B4.1030907@usit.uio.no>
<4732EF07.3060807@enterprisedb.com>
<47332200.9050705@usit.uio.no>
r.m.guerrero@usit.uio.no (Rafael Martinez) writes:
> Heikki Linnakangas wrote:
>> On a small table like that you could run VACUUM every few minutes
>> without much impact on performance. That should keep the table size in
>> check.
>>
>
> Ok, we run VACUUM ANALYZE only one time a day, every night. But we would
> espect the performance to get ok again after running vacuum, and it
> doesn't. Only CLUSTER helps.
You have characterized the shape of the problem Right There.
If you only VACUUM that table once a day, then it has a whole day to
get cluttered with dead tuples, which increases its size to encompass
651 pages, and NOTHING ever allows it to shrink back to a small size.
Plain VACUUM (or VACUUM ANALYZE) does not attempt to shrink table
sizes. Only VACUUM FULL and CLUSTER do that.
Here are some options to "parameterize" your choices:
- If you vacuum the table often enough that only 10% of the table
consists of dead tuples, then you can expect the table to perpetually
have 10% of dead space.
- If you vacuum the table seldom enough that 90% of the table may be
expected to consist of dead tuples, then you can expect this table to
consistently have 90% of its space be "dead."
It sounds like this particular table needs to be vacuumed quite a bit
more frequently than once a day.
On our systems, we have certain tables where tuples get killed off so
frequently that we find it worthwhile to vacuum those tables once
every two to three minutes. If we didn't, we'd see application
performance bog down until it forced us to CLUSTER or VACUUM FULL the
table.
--
"cbbrowne","@","acm.org"
http://linuxfinances.info/info/linux.html
"How much more helpful could I be than to provide you with the
appropriate e-mail address? I could engrave it on a clue-by-four and
deliver it to you in Chicago, I suppose." -- Seen on Slashdot...
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: cbbrowne@acm.org
Subject: Re: Need to run CLUSTER to keep performance
In-Reply-To: <60ir4civrz.fsf@dba2.int.libertyrms.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