Re: General performance/load issue

From: Robert Treat <rob(at)xzilla(dot)net>
To: Gaëtan Allart <gaetan(at)nexylan(dot)com>
Cc: Cédric Villemain <cedric(dot)villemain(dot)debian(at)gmail(dot)com>, Tomas Vondra <tv(at)fuzzy(dot)cz>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Re: General performance/load issue
Date: 2011-11-26 14:25:00
Message-ID: CAJSLCQ2bO4rf2EUyb+12GZf3peW5M4c9r14Hd12o-mgx5te88w@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Gaëtan Allart 2011-11-26 16:47:03 Re: General performance/load issue
Previous Message Tomas Vondra 2011-11-26 14:12:05 Re: General performance/load issue