Subject: Re: commit so slow program looks frozen
From: Scott Marlowe <smarlowe@g2switchworks.com>
To: Carlo Stonebanks <stonec.register@sympatico.ca>
Cc: pgsql-performance@postgresql.org
In-Reply-To: <ehog68$19d3$1@news.hub.org>
References: <eho0sj$2eht$1@news.hub.org>
	<20061025181910.GW26407@alvh.no-ip.org>  <ehog68$19d3$1@news.hub.org>
Content-Type: text/plain
Content-Transfer-Encoding: 7bit
Message-Id: <1161813892.8706.2023.camel@state.g2switchworks.com>
Mime-Version: 1.0
Date: Wed, 25 Oct 2006 17:04:52 -0500

On Wed, 2006-10-25 at 15:07, Carlo Stonebanks wrote:
> > 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... ;-)

You should try a google search on strace and NT or windows or XP...  I
was surprised how many various implementations of it I found.

> 
> > 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!)

I can just see the postgresql group getting together at the next
O'Reilley's conference and creating that band.  And it will all be your
fault.

A context switch storm is when your machine spends more time trying to
figure out what to do than actually doing anything.  The CPU spends most
it's time switching between programs than running them.


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

Seeing as PostgreSQL runs one thread / process per connection, it's
pretty unlikely that the problem here is one "hungry" thread.  Do all
four CPUs show busy, or just one?  Do you have a way of measuring how
much time is spent waiting on I/O on a windows machine like top / vmstat
does in unix?

Is it possible your machine is going into a swap storm?  i.e. you've
used all physical memory somehow and it's swapping out?  If your current
configuration is too aggresive on sort / work mem then it can happen
with only a few connections.  

Note that if you have an import process that needs a big chunk of
memory, you can set just that one connection to use a large setting and
leave the default smaller.