Re: Performance query about large tables, lots of concurrent access

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