Skip site navigation (1) Skip section navigation (2)

Re: commit so slow program looks frozen

From: "Carlo Stonebanks" <stonec(dot)register(at)sympatico(dot)ca>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: commit so slow program looks frozen
Date: 2006-10-25 20:07:38
Message-ID: ehog68$19d3$ (view raw, whole thread or download thread mbox)
Lists: pgsql-performance
> 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 

> 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.


In response to


pgsql-performance by date

Next:From: Luke LonerganDate: 2006-10-25 20:13:25
Subject: Re: Best COPY Performance
Previous:From: Markus SchaberDate: 2006-10-25 18:58:50
Subject: Re: Best COPY Performance

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group