From: "Carlo Stonebanks" <stonec.register@sympatico.ca>
Subject: Re: commit so slow program looks frozen
Date: Thu, 26 Oct 2006 10:43:50 -0400
Organization: Hub.Org Networking Services
Lines: 41
Message-ID: <ehqhhk$1vhm$1@news.hub.org>
References: <eho0sj$2eht$1@news.hub.org>
	<20061025181910.GW26407@alvh.no-ip.org>
	<ehog68$19d3$1@news.hub.org>
	<1161813892.8706.2023.camel@state.g2switchworks.com>
To: pgsql-performance@postgresql.org

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

Finally, a chance for me to wear my black leather pants.

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

Is thatl likely on a new 4 CPU server that has no clients connected and that 
is only running four (admittedly heavy) TCL data load scripts?

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

Before optimising the queries, all four CPU's were pinned to max performance 
(that's why I only run four imports at a time). After opimisation, all four 
CPU's are busy, but usage is spikey (which looks more normal), but all are 
obviously busy. I have this feeling that when an import app freezes, one CPU 
goes idle while the others stay busy - I will confirm that with the next 
import operation.

I suspect that the server has the Xeon processors that were of a generation 
which PostgreSQL had a problem with - should a postgresql process be able to 
distrivute its processing load across CPU's? (i.e. When I see one CPU at 
100% while all others are idle?)

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

Total memory usage is below the max available. Each postgresql process takes 
up 500MB, there are four running and I have 4GB of RAM.

Carlo