Date: Thu, 19 Jul 2007 13:03:26 -0400
From: Bill Moran <wmoran@collaborativefusion.com>
To: arnaulist@andromeiberica.com
Cc: PostgreSQL Performance <pgsql-performance@postgresql.org>
Subject: Re: Is it possible to know where is the "deadlock"
Message-Id: <20070719130326.356037e4.wmoran@collaborativefusion.com>
In-Reply-To: <469F9500.7060402@andromeiberica.com>
References: <469F9500.7060402@andromeiberica.com>
Organization: Collaborative Fusion
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

In response to Arnau <arnaulist@andromeiberica.com>:

> Hi all,
> 
>    I have a serious problem with a server. This server holds severals 
> DB, the problem is thet the CPU's spend most of the time waiting:
> 
> Cpu0: 4.0% us, 2.3% sy, 0.0% ni, 61.5% id, 32.1% wa, 0.0% hi, 0.0% si
> Cpu1: 2.3% us, 0.3% sy, 0.0% ni, 84.1% id, 13.3% wa, 0.0% hi, 0.0% si
> Cpu2: 1.3% us, 0.3% sy, 0.0% ni, 68.6% id, 29.8% wa, 0.0% hi, 0.0% si
> Cpu3: 4.6% us, 3.3% sy, 0.0% ni,  2.6% id, 88.4% wa, 0.3% hi, 0.7% si
> 
>      The iostat -c says about 8% of time waiting for IO. I'm afraid this 
> is due to locks between concurrent queries, is there anyway to have more 
> info about?

This looks perfectly normal for a medium-load server.

Although you don't state your problem (you state what you think is a
symptom, and call it the problem) I'm guessing you have queries that
are executing slower than you would like?   If that's the case, I would
suggest investigating the slow queries directly.  Check for indexes and
ensure your vacuum/analyze schedule is acceptable.  If you get
stumped, post details of the queries here asking for help.

Another thing that (I'm guessing) may be confusing you is if this 
system has multiple CPUs, each query can only execute on a single
CPU.  So a single query at full throttle on a 8-way system will
only use 12.5% max.

If you have reason to believe that locks are an issue, the pg_locks
view can help you prove/disprove that theory:
http://www.postgresql.org/docs/8.2/interactive/view-pg-locks.html

If none of those are the case, then please describe the actual problem
you are having.

HTH.

-- 
Bill Moran
Collaborative Fusion Inc.
http://people.collaborativefusion.com/~wmoran/

wmoran@collaborativefusion.com
Phone: 412-422-3463x4023