From: Chris Browne <cbbrowne@acm.org>
Subject: Re: When to do a vacuum for highly active table
Date: Tue, 06 Sep 2005 08:48:56 -0400
Organization: cbbrowne Computing Inc
Lines: 56
Message-ID: <607jdumkef.fsf@dba2.int.libertyrms.com>
References: <20050830211356.48138.qmail@web31606.mail.mud.yahoo.com>
	<20050830212545.5472718E718@linux.finestmedia.tv>
Mime-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
User-Agent: Gnus/5.1007 (Gnus v5.10.7) XEmacs/21.4.17 (Jumbo Shrimp, linux)
Cancel-Lock: sha1:R5hLhPUDRk4tUidSsuC5SR0/t/Y=
To: pgsql-performance@postgresql.org

rigmor.ukuhe@finestmedia.ee ("Rigmor Ukuhe") writes:

>> -----Original Message-----
>> From: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-
>> owner@postgresql.org] On Behalf Of Markus Benne
>> Sent: Wednesday, August 31, 2005 12:14 AM
>> To: pgsql-performance@postgresql.org
>> Subject: [PERFORM] When to do a vacuum for highly active table
>> 
>> We have a highly active table that has virtually all
>> entries updated every 5 minutes.  Typical size of the
>> table is 50,000 entries, and entries have grown fat.
>> 
>> We are currently vaccuming hourly, and towards the end
>> of the hour we are seeing degradation, when compared
>> to the top of the hour.
>> 
>> Vaccum is slowly killing our system, as it is starting
>> to take up to 10 minutes, and load at the time of
>> vacuum is 6+ on a Linux box.  During the vacuum,
>> overall system is goin unresponsive, then comes back
>> once vacuum completes.
>
> Play with vacuum_cost_delay option. In our case it made BIG difference
> (going from very heavy hitting to almost unnoticed vacuuming.)

That helps only if the ONLY problem you're having is from the direct
I/O of the vacuum.

If part of the problem is that the table is so large that it takes 4h
for VACUUM to complete, thereby leaving a transaction open for 4h,
thereby causing other degradations, then vacuum_cost_delay will have a
NEGATIVE impact, as it will mean that the vacuum on that table will
take even /more/ than 4h.  :-(

For the above scenario, it is almost certain that the solution comes
in two pieces:

1.  VACUUM FULL / CLUSTER to bring the size down.

    The table has grown "fat," and no number of repetitions of "plain
    vacuum" will fix this.

2.  Do "plain vacuum" on the table VASTLY more frequently, probably
    every 5 minutes, possibly more often than that.

    By doing this, you prevent things from getting so bad again.

By the way, in this sort of situation, _ANY_ transaction that runs
more than about 5 minutes represents a serious enemy to performance,
as it will tend to cause the "hot" table to "get fatter."
-- 
(reverse (concatenate 'string "gro.gultn" "@" "enworbbc"))
http://www.ntlug.org/~cbbrowne/linux.html
TECO Madness: a moment of regret, a lifetime of convenience.
-- Kent Pitman