From: "Carlo Stonebanks" <stonec.register@sympatico.ca>
Subject: Re: Is Vacuum/analyze destroying my performance?
Date: Mon, 4 Dec 2006 00:49:45 -0500
Organization: Hub.Org Networking Services
Lines: 38
Message-ID: <el0ctq$1g1i$1@news.hub.org>
References: <el0av7$1a6l$1@news.hub.org>
To: pgsql-performance@postgresql.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