Re: CPU load high

Hello.

I have a web-server with php 5.2 connected to postgres 8.0 backend. Most
of the queries the users are doing are SELECTs (100-150 in a second for
100 concurrent users), with a 5-10 INSERTs/UPDATEs at the same time. There
is also a demon running in the background doing some work once every
100ms. The problem is that after the number of concurrent users rises to
100, CPU becomes almost 100% loaded. How do I find out what's hogging the
CPU?

'top' shows demon using 8% cpu on top, and some amount of postgres
processes each using 2% cpu with some apache processes occassionally
rising with 2% cpu also. Often the writer process is at the top using 10%
cpu.

And the second question is that over time demon and writer processes use
more and more shared memory - is it normal?

Thanks in advance.

"Max Zorloff" <zorloff(at)gmail(dot)com> writes:
> ... The problem is that after the number of concurrent users rises to
> 100, CPU becomes almost 100% loaded. How do I find out what's hogging the
> CPU?

> 'top' shows demon using 8% cpu on top, and some amount of postgres
> processes each using 2% cpu with some apache processes occassionally
> rising with 2% cpu also. Often the writer process is at the top using 10%
> cpu.

IOW there's nothing particular hogging the CPU? Maybe you need more
hardware than you've got, or maybe you could fix it by trying to
optimize your most common queries. It doesn't sound like there'll be
any quick single-point fix though.

> And the second question is that over time demon and writer processes use
> more and more shared memory - is it normal?

This is probably an artifact. Many versions of "top" report a process
as having used as many pages of shared memory as it's actually touched
in its lifetime. So if you have lots of shared buffers, then any one
Postgres process will show growth of reported memory usage as it
randomly happens to access one buffer or another, eventually maxing out
at whatever you've got the PG shared memory segment size set to.

regards, tom lane

Hi Max,

To find out what is causing the big load you could also try to use 'ATOP'
which can be found at http://www.atcomputing.nl/atop. This tool shows more
(accurate) information than the regular TOP.

There are also some kernel patches available which, when applied to your
kernel, even show more information which might come in handy.

Good Luck,

Patrick Lindeman

> Hello.
>
> I have a web-server with php 5.2 connected to postgres 8.0 backend. Most
> of the queries the users are doing are SELECTs (100-150 in a second for
> 100 concurrent users), with a 5-10 INSERTs/UPDATEs at the same time. There
> is also a demon running in the background doing some work once every
> 100ms. The problem is that after the number of concurrent users rises to
> 100, CPU becomes almost 100% loaded. How do I find out what's hogging the
> CPU?
>
> 'top' shows demon using 8% cpu on top, and some amount of postgres
> processes each using 2% cpu with some apache processes occassionally
> rising with 2% cpu also. Often the writer process is at the top using 10%
> cpu.
>
> And the second question is that over time demon and writer processes use
> more and more shared memory - is it normal?
>
> Thanks in advance.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>

Hi Max,

To find out what is causing the big load you could also try to use 'ATOP'
which can be found at http://www.atcomputing.nl/atop. This tool shows more
(accurate) information than the regular TOP.

There are also some kernel patches available which, when applied to your
kernel, even show more information which might come in handy.

Good Luck,

-
Patrick Lindeman

> Hello.
>
> I have a web-server with php 5.2 connected to postgres 8.0 backend. Most
> of the queries the users are doing are SELECTs (100-150 in a second for
> 100 concurrent users), with a 5-10 INSERTs/UPDATEs at the same time. There
> is also a demon running in the background doing some work once every
> 100ms. The problem is that after the number of concurrent users rises to
> 100, CPU becomes almost 100% loaded. How do I find out what's hogging the
> CPU?
>
> 'top' shows demon using 8% cpu on top, and some amount of postgres
> processes each using 2% cpu with some apache processes occassionally
> rising with 2% cpu also. Often the writer process is at the top using 10%
> cpu.
>
> And the second question is that over time demon and writer processes use
> more and more shared memory - is it normal?
>
> Thanks in advance.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>

On 23.08.2007 11:04, Max Zorloff wrote:
> When one postgres process waits for lock to release does it use any cpu?
> And also, when apache waits for query to finish, does it use cpu?

No, but are you sure what you see is not i/o wait? What values does top
display in the %wa columns in the CPU rows? What does iostat -dm 1 say
say under load?

--
Regards,
Hannes Dorbath

On Thu, 23 Aug 2007 08:29:03 +0400, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> "Max Zorloff" <zorloff(at)gmail(dot)com> writes:
>> ... The problem is that after the number of concurrent users rises to
>> 100, CPU becomes almost 100% loaded. How do I find out what's hogging
>> the
>> CPU?
>
>> 'top' shows demon using 8% cpu on top, and some amount of postgres
>> processes each using 2% cpu with some apache processes occassionally
>> rising with 2% cpu also. Often the writer process is at the top using
>> 10%
>> cpu.
>
> IOW there's nothing particular hogging the CPU? Maybe you need more
> hardware than you've got, or maybe you could fix it by trying to
> optimize your most common queries. It doesn't sound like there'll be
> any quick single-point fix though.

There's no one big process chugging everything yes, but all these 2%
postgres
processes look like they're having their hand in overall cpu consumption.
I looked through every query and they all use indexes and whats more,
return
1-20 rows at most. Yes, I think there won't be any fix, but I wanted to
know,
are there some tools or techinques for finding where the problem lies?

I've looked into query time statistics - they all grow with cpu usage but
it
doesn't really mean anything - cpu usage grows, queries get slower.

When one postgres process waits for lock to release does it use any cpu?
And also, when apache waits for query to finish, does it use cpu?

On Thu, 23 Aug 2007 12:24:32 +0400, Hannes Dorbath
<light(at)theendofthetunnel(dot)de> wrote:

> On 23.08.2007 11:04, Max Zorloff wrote:
>> When one postgres process waits for lock to release does it use any cpu?
>> And also, when apache waits for query to finish, does it use cpu?
>
> No, but are you sure what you see is not i/o wait? What values does top
> display in the %wa columns in the CPU rows? What does iostat -dm 1 say
> say under load?
>
>

Well, vmstat 1 says this on 64 users (last column is the same wa) :

procs -----------memory---------- ---swap-- -----io---- --system--
----cpu----
r b swpd free buff cache si so bi bo in cs us sy
id wa
1 0 12336 289880 331224 3473804 0 4 8 259 1 0 31 13
54 3
13 0 12336 288012 331224 3473872 0 0 0 288 1054 3237 59 17
24 0
3 0 12336 284044 331224 3473872 0 0 0 480 908 3922 71 18
11 0
4 0 12336 291500 331224 3473872 0 0 0 248 654 2913 63 13
23 0
6 0 12336 297220 331224 3473940 0 0 0 240 678 3232 44 12
44 0
6 0 12336 304312 331224 3473940 0 0 0 1708 1166 3303 50 17
17 16
9 0 12336 304080 331224 3473940 0 0 0 480 779 4856 61 13
25 0
10 0 12336 309172 331224 3474008 0 0 0 304 697 3094 62 16
21 0
2 0 12336 308180 331224 3474008 0 0 0 272 681 3370 56 12
32 0
0 0 12336 307684 331224 3474076 0 0 0 112 689 3212 44 11
44 0
0 1 12336 312280 331224 3474076 0 0 0 1472 863 3121 51 13
29 7
7 0 12336 310544 331224 3474076 0 0 0 916 1023 3383 59 14
18 9
3 0 12336 309428 331224 3474076 0 0 0 224 731 2974 55 14
30 0
6 0 12336 306444 331224 3474144 0 0 0 392 796 3513 60 14
25 0