Message-ID: <46799C45.7080805@metacarta.com>
Date: Wed, 20 Jun 2007 17:29:41 -0400
From: Karl Wright <kwright@metacarta.com>
User-Agent: Mozilla Thunderbird 1.0.2 (Windows/20050317)
MIME-Version: 1.0
To: Shaun Thomas <sthomas@leapfrogonline.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Performance query about large tables, lots of concurrent
 access
References: <4676DD57.7070304@metacarta.com>
	<20070620175300.GN30369@alvh.no-ip.org>
	<46796A08.8080601@metacarta.com>
	<200706201425.28557.sthomas@leapfrogonline.com>
In-Reply-To: <200706201425.28557.sthomas@leapfrogonline.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Shaun Thomas wrote:
> 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.
> 

A nice try, but I had just completed a VACUUM on this database three 
hours prior to starting the VACUUM that I gave up on after 27 hours.  So 
I don't see how much more frequently I could do it.  (The one I did 
earlier finished in six hours - but to accomplish that I had to shut 
down EVERYTHING else that machine was doing.)

Karl


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