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
>
