Re: General performance/load issue

Hi Robert,

Thanks for your help as well.
You're right about checkpoints, it's running pretty good at start then
encounter heavy i/os.

I've changed theses settings and also reduced work_mem a little and
reduced effective_cache_size btw.

LOG: parameter "work_mem" changed to "96MB"
LOG: parameter "effective_cache_size" changed to "24GB"
LOG: parameter "checkpoint_segments" changed to "40"
LOG: parameter "checkpoint_timeout" changed to "5min"

Apparently, it's been running fine since I made the first changes
recommenced by Tomas. Let's wait for a couple of hours again to confirm
this.

Gaëtan

Le 24/11/11 16:39, « Robert Treat » <rob(at)xzilla(dot)net> a écrit :

>On Thu, Nov 24, 2011 at 9:09 AM, Tomas Vondra <tv(at)fuzzy(dot)cz> wrote:
>> On 24 Listopad 2011, 14:51, Gaëtan Allart wrote:
>>> Hello everyone,
>>>
>>> I'm having some troubles with a Postgresql server.
>>> We're using PG has a database backend for a very big website (lots of
>>>data
>>> and much traffic).
>>>
>>> The issue : server suddenly (1H after restart) becomes slow (queries
>>>not
>>> responding), load rises (>20 instead of 1), iowait rises (20 to 70%)
>>>
>>> Version : 9.0.5
>>> Server : Dual Xeon X5650 (24 cores total)
>>> Memory : 48 GB
>>> Disks : SSD
>>>
>>>
>>> Top when overloaded :
>>
>> Top is not the most useful tool here, I guess. Use "iotop" (will show
>>you
>> which processes are doing the I/O) and tools like vmstat / iostat.
>>
>>> Postgresql.conf :
>>>
>>> max_connections = 50
>>> shared_buffers = 12G
>>> temp_buffers = 40MB
>>> work_mem = 128MB
>>> maintenance_work_mem = 256MB
>>> max_files_per_process = 8192
>>> checkpoint_segments = 256
>>> checkpoint_timeout = 30min
>>> checkpoint_completion_target = 0.9
>>
>> Fine. Let's see the options that look suspicious.
>>
>
>I think you missed some suspicious settings... I'd recommend setting
>shared buffers to 8gb, and I'd likely reduce checkpoint segements to
>30 and set the checkpoint timeout back to 5 minutes. Everything about
>the way this server is configured (including those vm settings) is
>pushing it towards delaying the WAL/Buffer/Checkpoint as long as
>possible, which matches with the idea of good performance initial
>followed by a period of poor performance and heavy i/o.
>
>On a side note, I'd guess your work_mem is probably too high. 50
>(connections) x 128 (mb work mem) x 2 (sorts per query) = 12GB RAM,
>which is 25% of total ram on the box. That doesn't necessarily mean
>game over, but it seem like it wouldn't be that hard to get thrashing
>being set up that way. YMMV.
>
>Robert Treat
>conjecture: xzilla.net
>consulting: omniti.com