Re: General performance/load issue

Hello Robert,

I'm having around 30 active connections (average).
As far as disk layout is concerned, ever thing's on the same disk (raid 1
with 2 SSDs).

Gaëtan

Le 26/11/11 15:25, « Robert Treat » <rob(at)xzilla(dot)net> a écrit :

>On Fri, Nov 25, 2011 at 6:48 PM, Gaëtan Allart <gaetan(at)nexylan(dot)com> wrote:
>> Here are the latest checkpoint logs :
>>
>> LOG: checkpoint complete: wrote 842 buffers (0.1%); 0 transaction log
>> file(s) added, 0 removed, 0 recycled; write=168.970 s, sync=0.005 s,
>> total=168.977 s
>> LOG: checkpoint starting: time
>> LOG: checkpoint complete: wrote 318 buffers (0.0%); 0 transaction log
>> file(s) added, 0 removed, 2 recycled; write=63.818 s, sync=0.006 s,
>> total=63.825 s
>> LOG: checkpoint starting: time
>> LOG: checkpoint complete: wrote 744 buffers (0.1%); 0 transaction log
>> file(s) added, 0 removed, 0 recycled; write=147.035 s, sync=0.006 s,
>> total=147.043 s
>> LOG: checkpoint starting: time
>> LOG: checkpoint complete: wrote 108 buffers (0.0%); 0 transaction log
>> file(s) added, 0 removed, 3 recycled; write=35.410 s, sync=14.921 s,
>> total=54.811 s
>> LOG: checkpoint starting: time
>> LOG: checkpoint complete: wrote 393 buffers (0.0%); 0 transaction log
>> file(s) added, 0 removed, 0 recycled; write=88.835 s, sync=43.210 s,
>> total=135.728 s
>> LOG: checkpoint starting: time
>> LOG: checkpoint complete: wrote 914 buffers (0.1%); 0 transaction log
>> file(s) added, 0 removed, 1 recycled; write=148.162 s, sync=14.249 s,
>> total=170.481 s
>> LOG: checkpoint starting: time
>> LOG: checkpoint complete: wrote 202 buffers (0.0%); 0 transaction log
>> file(s) added, 0 removed, 1 recycled; write=53.152 s, sync=0.004 s,
>> total=53.159 s
>> LOG: checkpoint starting: time
>> LOG: checkpoint complete: wrote 897 buffers (0.1%); 0 transaction log
>> file(s) added, 0 removed, 1 recycled; write=42.414 s, sync=1.175 s,
>> total=58.957 s
>> LOG: checkpoint starting: shutdown immediate
>> LOG: checkpoint complete: wrote 666 buffers (0.1%); 0 transaction log
>> file(s) added, 0 removed, 1 recycled; write=0.027 s, sync=1.600 s,
>> total=1.630 s
>> LOG: checkpoint starting: time
>> LOG: checkpoint complete: wrote 627 buffers (0.1%); 0 transaction log
>> file(s) added, 0 removed, 1 recycled; write=125.856 s, sync=0.006 s,
>> total=125.864 s
>> LOG: checkpoint starting: time
>>
>>
>> And pg_stat_bgwriter :
>>
>> postgres=# select *,now() from pg_stat_bgwriter;
>> checkpoints_timed | checkpoints_req | buffers_checkpoint |
>>buffers_clean
>> | maxwritten_clean | buffers_backend | buffers_alloc | now
>>
>>
>>-------------------+-----------------+--------------------+--------------
>>-+
>>
>>------------------+-----------------+---------------+--------------------
>>--
>> ---------
>> 388 | 13 | 494948 | 4306591
>> | 13555 | 7458743 | 7835244602 | 2011-11-26
>> 00:43:47.232924+01
>> (1 row)
>>
>> postgres=# select *,now() from pg_stat_bgwriter;
>> checkpoints_timed | checkpoints_req | buffers_checkpoint |
>>buffers_clean
>> | maxwritten_clean | buffers_backend | buffers_alloc | now
>>
>>
>>-------------------+-----------------+--------------------+--------------
>>-+
>>
>>------------------+-----------------+---------------+--------------------
>>--
>> ---------
>> 389 | 13 | 501802 | 4352198
>> | 13809 | 7469220 | 7839778941 | 2011-11-26
>> 00:49:00.680779+01
>> (1 row)
>>
>> Processes that were writing were "SELECT" queries against database.
>>
>
>What was the filesystem involved? What is the underlying disk layout
>(you said it's SSD's, but how what type, how many, and in what
>configuration? Also how is Postgres set up on top of the disks (all of
>$PGDATA and OS on one volume? Split up?)
>
>Also, how many active connections do you typically have? Can you
>reduce your sort mem to something like 4MB, and set log_temp_files to
>0?
>
>
>Robert Treat
>conjecture: xzilla.net
>consulting: omniti.com