Re: General performance/load issue

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