Message-ID: <45743240.7050302@zeut.net>
Date: Mon, 04 Dec 2006 09:35:44 -0500
From: Matthew O'Connor <matthew@zeut.net>
User-Agent: Thunderbird 2.0a1 (Windows/20060724)
MIME-Version: 1.0
To: Carlo Stonebanks <stonec.register@sympatico.ca>
CC: pgsql-performance@postgresql.org
Subject: Re: Is Vacuum/analyze destroying my performance?
References: <el0av7$1a6l$1@news.hub.org> <el0ctq$1g1i$1@news.hub.org>
In-Reply-To: <el0ctq$1g1i$1@news.hub.org>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Just a wild guess, but the performance problem sounds like maybe as your 
data changes, eventually the planner moves some query from an index scan 
to a sequential scan, do you have any details on what queries are taking 
so long when things are running slow?  You can turn on the GUC var 
"log_min_duration_statement" and see what queries are slow and then 
manually check them with an explain analyze, that might help.

Matt


Carlo Stonebanks wrote:
> 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 
> 
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>