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.
