Re: PostgreSQL performance issues

Поиск
Список
Период
Сортировка
От Willo van der Merwe
Тема Re: PostgreSQL performance issues
Дата
Msg-id 44F57CB2.4030808@studentvillage.co.za
обсуждение исходный текст
Ответ на Re: PostgreSQL performance issues  ("Luke Lonergan" <LLonergan@greenplum.com>)
Список pgsql-performance
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.
>>
>>
>>
>
>
>


В списке pgsql-performance по дате отправления:

Предыдущее
От: Alex Hayward
Дата:
Сообщение: Re: PostgreSQL performance issues
Следующее
От: Willo van der Merwe
Дата:
Сообщение: Re: PostgreSQL performance issues