From: Karl Wright <kwright@metacarta.com>
To: Shaun Thomas <sthomas@leapfrogonline.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Performance query about large tables, lots of concurrent access
Date: Wed, 20 Jun 2007 17:29:41 -0400
Message-ID: <46799C45.7080805@metacarta.com> (raw)
In-Reply-To: <200706201425.28557.sthomas@leapfrogonline.com>
References: <4676DD57.7070304@metacarta.com>
<20070620175300.GN30369@alvh.no-ip.org>
<46796A08.8080601@metacarta.com>
<200706201425.28557.sthomas@leapfrogonline.com>
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.
>
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: kwright@metacarta.com, sthomas@leapfrogonline.com
Subject: Re: Performance query about large tables, lots of concurrent access
In-Reply-To: <46799C45.7080805@metacarta.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