Quantcast

Is it possible to know where is the "deadlock"

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Is it possible to know where is the "deadlock"

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

Thanks all
--
Arnau

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Is it possible to know where is the "deadlock"

Bill Moran-2
In response to Arnau <[hidden email]>:

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

[hidden email]
Phone: 412-422-3463x4023

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

               http://archives.postgresql.org
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Is it possible to know where is the "deadlock"

kometen
In reply to this post by Arnau
>      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?

I do believe that if you told what OS you're running, what pg-version
you're running, what type of sql-statements you perform the list can
provide some help.

--
regards
Claus

When lenity and cruelty play for a kingdom,
the gentlest gamester is the soonest winner.

Shakespeare

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq
Loading...