From: Matthew O'Connor <matthew@zeut.net>
To: Carlo Stonebanks <stonec.register@sympatico.ca>
Cc: pgsql-performance@postgresql.org
Subject: Re: Is Vacuum/analyze destroying my performance?
Date: Mon, 04 Dec 2006 09:35:44 -0500
Message-ID: <45743240.7050302@zeut.net> (raw)
In-Reply-To: <el0ctq$1g1i$1@news.hub.org>
References: <el0av7$1a6l$1@news.hub.org>
<el0ctq$1g1i$1@news.hub.org>
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
>
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: matthew@zeut.net, stonec.register@sympatico.ca
Subject: Re: Is Vacuum/analyze destroying my performance?
In-Reply-To: <45743240.7050302@zeut.net>
* 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