From: Shaun Thomas <sthomas@leapfrogonline.com>
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
Message-ID: <200706201425.28557.sthomas@leapfrogonline.com> (raw)
In-Reply-To: <46796A08.8080601@metacarta.com>
References: <4676DD57.7070304@metacarta.com>
<20070620175300.GN30369@alvh.no-ip.org>
<46796A08.8080601@metacarta.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
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: sthomas@leapfrogonline.com
Subject: Re: Performance query about large tables, lots of concurrent access
In-Reply-To: <200706201425.28557.sthomas@leapfrogonline.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