From: Shaun Thomas <sthomas@leapfrogonline.com>
Organization: Leapfrog Online
To: pgsql-performance@postgresql.org
Subject: Re: Performance query about large tables, lots of concurrent access
Date: Wed, 20 Jun 2007 14:25:28 -0500
User-Agent: KMail/1.9.6
References: <4676DD57.7070304@metacarta.com>
	<20070620175300.GN30369@alvh.no-ip.org>
	<46796A08.8080601@metacarta.com>
In-Reply-To: <46796A08.8080601@metacarta.com>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200706201425.28557.sthomas@leapfrogonline.com>

On Wednesday 20 June 2007 12:55:20 pm Karl Wright wrote:

> I am afraid that I did answer this.  My largest tables
> are the ones continually being updated.  The smaller
> ones are updated only infrequently. 

You know, it actually sounds like you're getting whacked by the same 
problem that got us a while back.  It sounds like you weren't vacuuming 
frequently enough initially, and then tried vacuuming later, only after 
you noticed performance degrade.

Unfortunately what that means, is for several weeks or months, Postgres 
has not been reusing rows on your (admittedly) active and large tables; 
it just appends at the end, and lets old rows slowly bloat that table 
larger and larger.  Indexes too, will suffer from dead pages.  As 
frightening/sickening as this sounds, you may need to dump/restore the 
really huge table, or vacuum-full to put it on a crash diet, and then 
maintain a strict daily or bi-daily vacuum schedule to keep it under 
control.

The reason I think this: even with several 200M row tables, vacuums 
shouldn't take over 24 hours.  Ever.  Do a vacuum verbose and see just 
how many pages it's trying to reclaim.  I'm willing to wager it's 
several orders of magnitude higher than the max_fsm_pages setting 
you've stuck in your config file.

You'll also want to see which rows in your 250M+ table are actually 
active, and shunt the stable rows to another (warehouse) table maybe 
available only via view or table partition.  I mean, your most active 
table is also the largest?  Seems a bit backward, to me.

-- 

Shaun Thomas
Database Administrator

Leapfrog Online 
807 Greenwood Street 
Evanston, IL 60201 
Tel. 847-440-8253
Fax. 847-570-5750
www.leapfrogonline.com