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"
Date: Thu, 19 Jul 2007 13:03:26 -0400
Message-ID: <20070719130326.356037e4.wmoran@collaborativefusion.com> (raw)
In-Reply-To: <469F9500.7060402@andromeiberica.com>
References: <469F9500.7060402@andromeiberica.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: wmoran@collaborativefusion.com, arnaulist@andromeiberica.com
Subject: Re: Is it possible to know where is the "deadlock"
In-Reply-To: <20070719130326.356037e4.wmoran@collaborativefusion.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox