From: | "Carlo Stonebanks" <stonec(dot)register(at)sympatico(dot)ca> |
---|---|
To: | pgsql-performance(at)postgresql(dot)org |
Subject: | Is Vacuum/analyze destroying my performance? |
Date: | 2006-12-04 05:16:22 |
Message-ID: | el0av7$1a6l$1@news.hub.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
I have always been frustrated by the wildly erratic performance of our
postgresql 8 server. We run aprogram that does heavy data importing via a
heuristics-based import program. Sometime records being imported would just
fly by, sometimes they would crawl. The import program imports records from
a flat table and uses heuristics to normalise and dedupe. This is done via a
sequence of updates and inserts bracketed by a start-end transaction.
At a certain checkpoint representing about 1,000,000 rows read and imported,
I ran a vacuum/analyze on all of the tables in the target schema. To my
horror, performance reduced to less than TEN percent of what it was befor
the vacuum/analyze. I thought that turning autovacuum off and doing my own
vacuuming would improve performance, but it seems to be killing it.
I have since turned autovacuum on and am tearing my hair out wathcing the
imported records crawl by. I have tried vacuuming the entire DB as well as
rebuilding indexes. Nothing. Any ideas what could have happened? What is the
right thing to do?
Carlo
From | Date | Subject | |
---|---|---|---|
Next Message | Greg Smith | 2006-12-04 05:44:47 | Re: Bad iostat numbers |
Previous Message | Fayza Sultan | 2006-12-03 22:37:37 | Re: Enabling constraint_exclusion does not avoid scanning all child partitions |