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
