Re: commit so slow program looks frozen

> You may try to figure out what's the process doing (the backend
> obviously, not the frontend (Tcl) process) by attaching to it with
> strace.

It's so sad when us poor Windows guys get helpful hints from people assume
that we're smart enough to run *NIX... ;-)

> Maybe it's swamped by a context switch storm (but in that case, probably
> the other processes would be affected as well).

What is a context switch storm? (and what a great name for a heavy metal
rock band!)

Interestingly enough, last night (after the original post) I watched three
of the processes slow down, one after the other - and then stall for so long
that I had assumed they had frozen. They were all stalled on a message that
I had put in the script that indicated they had never returned from a
commit. I have looked into this, and I believe the commits are actually
going through.

The remaining 4th process continued to run, and actually picked up speed as
the CPU gave its cycles over. The Windows task manager shows the postgresql
processes that (I assume) are associated with the stalled processes as
consuming zero CPU time.

Sometimes I have seen all of the apps slow down and momentarrily freeze at
the same time... but then continue. I have autovacuum off, although
stats_row_level and stats_start_collector remain on (I thought these were
only relevant if autovacuum was on).

I have seen the apps slow down (and perhaps stall) when specifical tables
have vacuum/analyze running, and that makes sense. I did notice that on one
occasion a "frozen" app came back to life after I shut down EMS PostgreSQL
manager in another session. Maybe a coincidence, or maybe an indication that
the apps are straining resources... on a box with two twin-core XEONs and
4GB of memory? Mind you, the config file is confgiured for the database
loading phase weare in now - with lots of resources devoted to a few
connections.

> I wouldn't expect it to be stuck on locks, because if it's only on
> commit, then it probably has all the locks it needs. But try to see if
> you can find something not granted in pg_locks that it may be stuck on.

Looking at the pgadmin server status pages, no locks or transactions are
pending when this happens.

Carlo