From: Carlo Stonebanks <stonec.register@sympatico.ca>
To: pgsql-performance@postgresql.org
Subject: Re: Is Vacuum/analyze destroying my performance?
Date: Mon, 4 Dec 2006 00:49:45 -0500
Message-ID: <el0ctq$1g1i$1@news.hub.org> (raw)
References: <el0av7$1a6l$1@news.hub.org>
Update on this issue, I "solved" my problem by doing the following:
1) Stopped the import, and did a checkpoint backup on my import target
schema
2) Dropped the import target schema
3) Restored a backup from a previous checkpoint when the tables were much
smaller
4) Performed a VACUUM/ANALYZE on all of the tables in the import target
schema in that smaller state
5) Dropped the import target schema again
6) Restored the checkpoint backup of the larger data set referred to in step
1
7) Rstarted the import from where it left off
The result: the import is flying again, with 10-20 times the performance.
The import runs as 4 different TCL scripts in parallel, importing difernt
segments of the table. The problem that I have when the import runs at this
speed is that I hve to constantly watch for lock-ups. Previously I had
reported that when these multiple processes are running at high speed,
PostgreSQL occasionally freezes one or more of the processes by never
retutning from a COMMIT. I look at the target tables, and it seems that the
commit has gone through.
This used to be a disaster because Ithought I had to restart every frozen
proess by killing the script and restarting at the last imported row.
Now I have found a way to un-freeze the program: I find the frozen process
via PgAdmin III and send a CANCEL. To my surprise, the import continues as i
nothing happened. Still incredibly inconvenient and laborious, but at least
it's a little less tedious.
Could these two problems - the weird slowdowns after a VACUUM/ANALYZE and
the frequent lockups when the import process is running quickly - be
related?
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: <el0ctq$1g1i$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