From: Willo van der Merwe <willo@studentvillage.co.za>
To: Luke Lonergan <LLonergan@greenplum.com>
Cc: Merlin Moncure <mmoncure@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: PostgreSQL performance issues
Date: Wed, 30 Aug 2006 13:55:30 +0200
Message-ID: <44F57CB2.4030808@studentvillage.co.za> (raw)
In-Reply-To: <3E37B936B592014B978C4415F90D662D04392D2B@MI8NYCMAIL06.Mi8.com>
References: <3E37B936B592014B978C4415F90D662D04392D2B@MI8NYCMAIL06.Mi8.com>
That's exactly what I'm experiencing.
Everything was fine until yesterday, when we noticed a considerable site
slow-down. Graphs showed the server suddenly spiking to a load of 67. At
first I thought somebody executed a ran-away query, so I restarted
postgres, but after it came back up, it climbed back up to this load.
In the meanwhile I've applied some table level optimizations and the
postgres.conf optimizatrions ... nothing
Here's the vmstat output, since reboot last night
[root@srv1 ~]# vmstat -a
procs -----------memory---------- ---swap-- -----io---- --system--
----cpu----
r b swpd free inact active si so bi bo in cs us sy
id wa
27 0 0 595312 248100 2962764 0 0 8 31 105 7 63
2 35 0
[root@srv1 ~]# vmstat -d
disk- ------------reads------------ ------------writes-----------
-----IO------
total merged sectors ms total merged sectors ms
cur sec
ram0 0 0 0 0 0 0 0 0
0 0
ram1 0 0 0 0 0 0 0 0
0 0
ram2 0 0 0 0 0 0 0 0
0 0
ram3 0 0 0 0 0 0 0 0
0 0
ram4 0 0 0 0 0 0 0 0
0 0
ram5 0 0 0 0 0 0 0 0
0 0
ram6 0 0 0 0 0 0 0 0
0 0
ram7 0 0 0 0 0 0 0 0
0 0
ram8 0 0 0 0 0 0 0 0
0 0
ram9 0 0 0 0 0 0 0 0
0 0
ram10 0 0 0 0 0 0 0 0
0 0
ram11 0 0 0 0 0 0 0 0
0 0
ram12 0 0 0 0 0 0 0 0
0 0
ram13 0 0 0 0 0 0 0 0
0 0
ram14 0 0 0 0 0 0 0 0
0 0
ram15 0 0 0 0 0 0 0 0
0 0
sda 197959 38959 4129737 952923 777438 1315162 16839981
39809324 0 2791
fd0 0 0 0 0 0 0 0 0
0 0
md0 0 0 0 0 0 0 0 0
0 0
Luke Lonergan wrote:
> Interesting - in this quick snapshot there is no I/O happening at all.
> What happens when you track the activity for a longer period of time?
>
> How about just capturing vmstat during a period when the queries are
> slow?
>
> Has the load average been this high forever or are you experiencing a
> growth in workload? 463 processes all doing CPU work will take 100x as
> long as one query on a 4 CPU box, have you worked through how long you
> should expect the queries to take?
>
> - Luke
>
>
>> -----Original Message-----
>> From: Willo van der Merwe [mailto:willo@studentvillage.co.za]
>> Sent: Wednesday, August 30, 2006 4:35 AM
>> To: Luke Lonergan
>> Cc: Merlin Moncure; pgsql-performance@postgresql.org
>> Subject: Re: [PERFORM] PostgreSQL performance issues
>>
>> Luke Lonergan wrote:
>>
>>>> Currently the load looks like this:
>>>> Cpu0 : 96.8% us, 1.9% sy, 0.0% ni, 0.3% id, 0.0% wa,
>>>>
>> 0.0% hi,
>>
>>>> 1.0% si
>>>> Cpu1 : 97.8% us, 1.6% sy, 0.0% ni, 0.3% id, 0.0% wa,
>>>>
>> 0.0% hi,
>>
>>>> 0.3% si
>>>> Cpu2 : 96.8% us, 2.6% sy, 0.0% ni, 0.3% id, 0.0% wa,
>>>>
>> 0.0% hi,
>>
>>>> 0.3% si
>>>> Cpu3 : 96.2% us, 3.2% sy, 0.0% ni, 0.3% id, 0.0% wa,
>>>>
>> 0.0% hi,
>>
>>>> 0.3% si
>>>>
>>>>
>>> All four CPUs are hammered busy - check "top" and look for runaway
>>> processes.
>>>
>>> - Luke
>>>
>>>
>>>
>>>
>> Yes, the first 463 process are all postgres. In the meanwhile
>> I've done:
>> Dropped max_connections from 500 to 250 and Upped
>> shared_buffers = 50000
>>
>> Without any apparent effect.
>>
>>
>>
>
>
>
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: willo@studentvillage.co.za, LLonergan@greenplum.com, mmoncure@gmail.com
Subject: Re: PostgreSQL performance issues
In-Reply-To: <44F57CB2.4030808@studentvillage.co.za>
* 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