From: Chris Browne <cbbrowne@acm.org>
To: pgsql-performance@postgresql.org
Subject: Re: When to do a vacuum for highly active table
Date: Tue, 06 Sep 2005 08:48:56 -0400
Message-ID: <607jdumkef.fsf@dba2.int.libertyrms.com> (raw)
References: <20050830211356.48138.qmail@web31606.mail.mud.yahoo.com>
<20050830212545.5472718E718@linux.finestmedia.tv>
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
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: When to do a vacuum for highly active table
In-Reply-To: <607jdumkef.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