From: Carlo Stonebanks <stonec.register@sympatico.ca>
To: pgsql-performance@postgresql.org
Subject: Is Vacuum/analyze destroying my performance?
Date: Mon, 4 Dec 2006 00:16:22 -0500
Message-ID: <el0av7$1a6l$1@news.hub.org> (raw)
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
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: stonec.register@sympatico.ca
Subject: Re: Is Vacuum/analyze destroying my performance?
In-Reply-To: <el0av7$1a6l$1@news.hub.org>
* 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