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@xxxxxxxxxx> a écrit : >On Fri, Nov 25, 2011 at 6:48 PM, Gaëtan Allart <gaetan@xxxxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
