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
