Re: PG 8.3 and server load - Mailing list pgsql-performance

Phoenix Kiula wrote:
> Thanks, but swap is not changing, there is no idle transaction, and
> number of connections are 28/29.
>
> Here are some command line stamps...any other ideas?
>
>
>
> [MYSITE] ~ > date && vmstat
> Wed Aug 19 10:00:37 CDT 2009
> procs -----------memory---------- ---swap-- -----io---- --system-- ----cpu----
>  r  b   swpd   free   buff  cache   si   so    bi    bo   in    cs us sy id wa
>  3  1  20920  25736  60172 7594988    0    0    74   153    0     3 10  5 74 12
>
> [MYSITE] ~ > date && vmstat
> Wed Aug 19 10:00:40 CDT 2009
> procs -----------memory---------- ---swap-- -----io---- --system-- ----cpu----
>  r  b   swpd   free   buff  cache   si   so    bi    bo   in    cs us sy id wa
>  0  1  20920  34696  60124 7593996    0    0    74   153    0     3 10  5 74 12
>
> [MYSITE] ~ > ps ax|grep postgres
> 25302 ?        Ss     0:00 postgres: logger process
> 25352 ?        Ss     0:07 postgres: writer process
> 25353 ?        Ss     4:21 postgres: stats collector process
> 23483 ?        Ds     0:00 postgres: snipurl_snipurl snipurl
> 127.0.0.1(51622) UPDATE
> 23485 pts/12   S+     0:00 grep postgres
>
> [MYSITE] ~ > date && vmstat
> Wed Aug 19 10:00:55 CDT 2009
> procs -----------memory---------- ---swap-- -----io---- --system-- ----cpu----
>  r  b   swpd   free   buff  cache   si   so    bi    bo   in    cs us sy id wa
>  0  0  20920  49464  60272 7597748    0    0    74   153    0     3 10  5 74 12
>
> [MYSITE] ~ > ps ax|grep http|wc --lines
> 28
>
> [MYSITE] ~ > ps ax|grep http|wc --lines
> 29
>
> [MYSITE] ~ > ps ax|grep postgres
> 25302 ?        Ss     0:00 postgres: logger process
> 25352 ?        Ss     0:07 postgres: writer process
> 25353 ?        Ss     4:21 postgres: stats collector process
> 24718 pts/12   S+     0:00 grep postgres
>
> [MYSITE] ~ > date && vmstat
> Wed Aug 19 10:01:23 CDT 2009
> procs -----------memory---------- ---swap-- -----io---- --system-- ----cpu----
>  r  b   swpd   free   buff  cache   si   so    bi    bo   in    cs us sy id wa
>  0  0  20920 106376  59220 7531016    0    0    74   153    0     3 10  5 74 12
>
>
>
>
> On Wed, Aug 19, 2009 at 10:01 PM, Andy Colson<andy@squeakycode.net> wrote:
>> Phoenix Kiula wrote:
>>> I'm on a CentOS 5 OS 64 bit, latest kernel and all of that.
>>> PG version is 8.3.7, compiled as 64bit.
>>> The memory is 8GB.
>>> It's a 2 x Dual Core Intel 5310.
>>> Hard disks are Raid 1, SCSI 15 rpm.
>>>
>>> The server is running just one website. So there's Apache 2.2.11,
>>> MySQL (for some small tasks, almost negligible).
>>>
>>> And then there's PG, which in the "top" command shows up as the main
>>> beast.
>>>
>>> My server load is going to 64, 63, 65, and so on.
>>>
>>> Where should I start debugging? What should I see? TOP command does
>>> not yield anything meaningful. I mean, even if it shows that postgres
>>> user for "postmaster" and nobody user for "httpd" (apache) are the
>>> main resource hogs, what should I start with in terms of debugging?
>>>
>> 1) check if you are using swap space.  Use free and make sure swap/used is a
>> small number.  Check vmstat and see if swpd is moving up and down.  (Posting
>> a handful of lines from vmstat might help us).
>>
>> 2) check 'ps ax|grep postgres' and make sure nothing says "idle in
>> transaction"
>>
>> 3) I had a web box where the number of apache clients was set very high, and
>> the box was brought to its knees by the sheer number of connections.  check
>> "ps ax|grep http|wc --lines" and make sure its not too big. (perhaps less
>> than 100)
>>
>> -Andy
>>
>>

the first line of vmstat is an average since bootup.  Kinda useless.
run it as:  'vmstat 4'

it will print a line every 4 seconds, which will be a summary of
everything that happened in the last 4 seconds.

since boot, you've written out an average of 153 blocks (the bo column).
  Thats very small, so your not io bound.

but... you have average 74% idle cpu.  So your not cpu bound either?

Ahh?  I'm not sure what that means.  Maybe I'm reading something wrong?

-Andy