From: Steven Jones <steven.jones1201@outlook.com>
To: ik@postgresql-consulting.com <ik@postgresql-consulting.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: Very slow checkpoints
Date: Wed, 18 Mar 2015 11:58:45 +0000
Message-ID: <COL129-W41B57411604205109F5AC592000@phx.gbl> (raw)
In-Reply-To: <CAG95seW33nJtZmu+8XDia3kM6zm-aNyQYn3NbSvsm_f7WK-VoQ@mail.gmail.com>
References: <COL129-W88EAA235AC3967FF7F5D8792000@phx.gbl>
<CAG95seW33nJtZmu+8XDia3kM6zm-aNyQYn3NbSvsm_f7WK-VoQ@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
Hi Ilya,
Thank you for the response.
Yes BBU is on the controller; 1024Mb. It is a HP P410i controller, with write caching turned on the controller; off on disk level.
2x 15k SAS SFF for WAL and 12x 10k SAS SFF for DB
We have tried longer settings for checkpoint_timeout, but not 1hr; so we will try that as well.
We don't at this stage have any graphs, but we will set it up over the next 24hrs at least.
Regards,
Steve
----------------------------------------
> From: ilya.kosmodemiansky@postgresql-consulting.com
> Date: Wed, 18 Mar 2015 12:42:43 +0100
> Subject: Re: [PERFORM] Very slow checkpoints
> To: steven.jones1201@outlook.com
> CC: pgsql-performance@postgresql.org
>
> Hi Steven,
>
> On Wed, Mar 18, 2015 at 12:21 PM, Steven Jones
> <steven.jones1201@outlook.com> wrote:
>> - system is HP blade; 128Gb RAM, 2x 8-core, 12x 10k RPM RAID1+0 (database)
>
> Have you BBU on your controller? And how your controller configured, I
> mean cache mode, io mode, disk write cache mode. You have 15K SAS
> (which form factor?) under WAL and 10K SAS under database, am I
> correct?
>
>> Full iostat/iotop, configuration, checkpoint stats, etc. are pasted below
>> for completeness. Highlights are:
>> checkpoint_segments=512
>> shared_buffers=16GB
>> checkpoint_timeout=15min
>> checkpoint_completion_target=0.1
>
> It looks like your checkpoint settings are a bit strange besides of
> everything else. If you chose high value for checkpoint_segments, your
> aim is to avoid checkpoints by timeout (or vice verse). If you have
> checkpoint_segments=512, your checkpoint_timeout should be about
> 60min. And anyway - checkpoint_completion_target=0.9 or 0.7 in order
> to spread disk load between checkpoints.
>
>
>
>>
>> sysctl settings for dirty pages
>>
>> vm.dirty_background_bytes = 0
>> vm.dirty_background_ratio = 5
>> vm.dirty_bytes = 0
>> vm.dirty_expire_centisecs = 3000
>> vm.dirty_ratio = 10
>> vm.dirty_writeback_centisecs = 500
>
> Values for this settings are really dependent of RAID (and BBU size).
>
> And about further problem description: have you any graphical
> representation of your % disc utilization?
>
> Best regards,
> Ilya
>
> --
> Ilya Kosmodemiansky,
>
> PostgreSQL-Consulting.com
> tel. +14084142500
> cell. +4915144336040
> ik@postgresql-consulting.com
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: steven.jones1201@outlook.com, ik@postgresql-consulting.com
Subject: Re: Very slow checkpoints
In-Reply-To: <COL129-W41B57411604205109F5AC592000@phx.gbl>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox