From: Chris Browne <cbbrowne@acm.org>
Subject: Re: Need to run CLUSTER to keep performance
Date: Thu, 08 Nov 2007 10:47:44 -0500
Organization: cbbrowne Computing Inc
Lines: 43
Message-ID: <60ir4civrz.fsf@dba2.int.libertyrms.com>
References: <4732E6B4.1030907@usit.uio.no> <4732EF07.3060807@enterprisedb.com>
	<47332200.9050705@usit.uio.no>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
User-Agent: Gnus/5.1008 (Gnus v5.10.8) XEmacs/21.4.20 (linux)
Cancel-Lock: sha1:2ujjnr5UU713ZvYwoeS32dglqNw=
To: pgsql-performance@postgresql.org

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...