pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedMassive I/O spikes during checkpoint
10+ messages / 4 participants
[nested] [flat]
* Massive I/O spikes during checkpoint
@ 2012-07-10 05:39 David Kerr <dmk@mr-paradox.net>
2012-07-10 05:51 ` Re: Massive I/O spikes during checkpoint Maxim Boguk <maxim.boguk@gmail.com>
2012-07-10 05:52 ` Re: Massive I/O spikes during checkpoint Jeff Janes <jeff.janes@gmail.com>
0 siblings, 2 replies; 10+ messages in thread
From: David Kerr @ 2012-07-10 05:39 UTC (permalink / raw)
To: pgsql-performance
Howdy!
I'm trying to figure out why checkpointing it completely pegging my I/O under moderate to high write load,
I'm on PG9.1.1, RHEL 6.2 x64
checkpoint_completion_target = 0.7
checkpoint_timeout = 10m
Jul 10 00:32:30 perf01 postgres[52619]: [1895-1] user=,db= LOG: checkpoint starting: time
[...]
Jul 10 00:36:47 perf01 postgres[52619]: [1896-1] user=,db= LOG: checkpoint complete: wrote 119454 buffers (11.4%); 0 transaction log file(s) added, 0 removed
Watching my I/O with: iostat -t -d -x dm-2 5
Which is my $PGDATA mount point (ext4).
I get the following:
Date r/s w/s rsec/s wsec/s await svctm %util
[...]
07/10/12 00:35:36 0 69.8 0 2233.6 0.63 0.07 0.46
07/10/12 00:35:41 1.2 810 99.2 22200 4.13 0.05 4.02
07/10/12 00:35:46 0 111.6 0 5422.4 1.82 0.08 0.9
07/10/12 00:35:51 0 299.2 0 5670.4 1.27 0.04 1.24
07/10/12 00:35:56 0.8 176.6 41.6 3654.4 2.16 0.07 1.32
07/10/12 00:36:01 0 364.8 0 6670.4 1.1 0.04 1.62
07/10/12 00:36:06 0.8 334.6 12.8 5953.6 1.18 0.05 1.64
07/10/12 00:36:11 0 118.6 0 6948.8 1.82 0.07 0.82
07/10/12 00:36:16 0 8274.6 0 148764.8 10.55 0.07 61.18
07/10/12 00:36:21 0.2 8577.4 3.2 161806.4 16.68 0.12 99.62
07/10/12 00:36:26 0.8 9244.6 12.8 167841.6 15.01 0.11 99.82
07/10/12 00:36:31 0.8 9434.2 44.8 208156.8 16.22 0.11 99.7
07/10/12 00:36:36 0 9582.8 0 202508.8 14.84 0.1 99.72
07/10/12 00:36:41 0 9830.2 0 175326.4 14.42 0.1 99.5
07/10/12 00:36:46 0 8208.6 0 149372.8 17.82 0.12 99.64
07/10/12 00:36:51 3 1438.4 102.4 26748.8 8.49 0.12 18
07/10/12 00:36:56 0.6 2004.6 9.6 27400 1.25 0.03 5.74
07/10/12 00:37:01 0.6 1723 9.6 23758.4 1.85 0.03 5.08
07/10/12 00:37:06 0.4 181.2 35.2 2928 1.49 0.06 1.06
The ramp up is barely using any I/O, but then just before the checkpoint ends I get a
flood of I/O all at once.
I thought that the idea of checkpoint_completion_target was that we try to finish writing
out the data throughout the entire checkpoint (leaving some room to spare, in my case 30%
of the total estimated checkpoint time)
But what appears to be happening is that all of the data is being written out at the end of the checkpoint.
This happens at every checkpoint while the system is under load.
I get the feeling that this isn't the correct behavior and i've done something wrong.
Also, I didn't see this sort of behavior in PG 8.3, however unfortunately, I don't have data to back that
statement up.
Any suggestions. I'm willing and able to profile, or whatever.
Thanks
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Massive I/O spikes during checkpoint
2012-07-10 05:39 Massive I/O spikes during checkpoint David Kerr <dmk@mr-paradox.net>
@ 2012-07-10 05:51 ` Maxim Boguk <maxim.boguk@gmail.com>
2012-07-10 06:03 ` Re: Massive I/O spikes during checkpoint David Kerr <dmk@mr-paradox.net>
1 sibling, 1 reply; 10+ messages in thread
From: Maxim Boguk @ 2012-07-10 05:51 UTC (permalink / raw)
To: David Kerr <dmk@mr-paradox.net>; +Cc: pgsql-performance
>
>
>
> But what appears to be happening is that all of the data is being written
> out at the end of the checkpoint.
>
> This happens at every checkpoint while the system is under load.
>
> I get the feeling that this isn't the correct behavior and i've done
> something wrong.
>
>
>
It's not an actual checkpoints.
It's is a fsync after checkpoint which create write spikes hurting server.
You should set sysctl vm.dirty_background_bytes and vm.dirty_bytes to
reasonable low values
(for 512MB raid controller with cache I would suggest to sometning like
vm.dirty_background_bytes = 33554432
vm.dirty_bytes = 268435456
32MB and 256MB respectively)
If youre server doesn't have raid with BBU cache - then you should tune
these values to much lower values.
Please read http://blog.2ndquadrant.com/tuning_linux_for_low_postgresq/
and related posts.
--
Maxim Boguk
Senior Postgresql DBA.
http://www.postgresql-consulting.com/
Phone RU: +7 910 405 4718
Phone AU: +61 45 218 5678
Skype: maxim.boguk
Jabber: maxim.boguk@gmail.com
МойКруг: http://mboguk.moikrug.ru/
"People problems are solved with people.
If people cannot solve the problem, try technology.
People will then wish they'd listened at the first stage."
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Massive I/O spikes during checkpoint
2012-07-10 05:39 Massive I/O spikes during checkpoint David Kerr <dmk@mr-paradox.net>
2012-07-10 05:51 ` Re: Massive I/O spikes during checkpoint Maxim Boguk <maxim.boguk@gmail.com>
@ 2012-07-10 06:03 ` David Kerr <dmk@mr-paradox.net>
2012-07-10 06:14 ` Re: Massive I/O spikes during checkpoint Maxim Boguk <maxim.boguk@gmail.com>
0 siblings, 1 reply; 10+ messages in thread
From: David Kerr @ 2012-07-10 06:03 UTC (permalink / raw)
To: Maxim Boguk <maxim.boguk@gmail.com>; +Cc: pgsql-performance
On Jul 9, 2012, at 10:51 PM, Maxim Boguk wrote:
>
>
> But what appears to be happening is that all of the data is being written out at the end of the checkpoint.
>
> This happens at every checkpoint while the system is under load.
>
> I get the feeling that this isn't the correct behavior and i've done something wrong.
>
>
>
> It's not an actual checkpoints.
> It's is a fsync after checkpoint which create write spikes hurting server.
> You should set sysctl vm.dirty_background_bytes and vm.dirty_bytes to reasonable low values
So use bla_bytes instead of bla_ratio?
> (for 512MB raid controller with cache I would suggest to sometning like
> vm.dirty_background_bytes = 33554432
> vm.dirty_bytes = 268435456
> 32MB and 256MB respectively)
I'll take a look.
>
> If youre server doesn't have raid with BBU cache - then you should tune these values to much lower values.
>
> Please read http://blog.2ndquadrant.com/tuning_linux_for_low_postgresq/
> and related posts.
yeah, I saw that I guess I didn't put 2+2 together. thanks.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Massive I/O spikes during checkpoint
2012-07-10 05:39 Massive I/O spikes during checkpoint David Kerr <dmk@mr-paradox.net>
2012-07-10 05:51 ` Re: Massive I/O spikes during checkpoint Maxim Boguk <maxim.boguk@gmail.com>
2012-07-10 06:03 ` Re: Massive I/O spikes during checkpoint David Kerr <dmk@mr-paradox.net>
@ 2012-07-10 06:14 ` Maxim Boguk <maxim.boguk@gmail.com>
2012-07-10 06:17 ` Re: Massive I/O spikes during checkpoint David Kerr <dmk@mr-paradox.net>
2012-07-10 12:44 ` Re: Massive I/O spikes during checkpoint Andres Freund <andres@2ndquadrant.com>
0 siblings, 2 replies; 10+ messages in thread
From: Maxim Boguk @ 2012-07-10 06:14 UTC (permalink / raw)
To: David Kerr <dmk@mr-paradox.net>; +Cc: pgsql-performance
On Tue, Jul 10, 2012 at 4:03 PM, David Kerr <dmk@mr-paradox.net> wrote:
>
> On Jul 9, 2012, at 10:51 PM, Maxim Boguk wrote:
>
>
>>
>> But what appears to be happening is that all of the data is being written
>> out at the end of the checkpoint.
>>
>> This happens at every checkpoint while the system is under load.
>>
>> I get the feeling that this isn't the correct behavior and i've done
>> something wrong.
>>
>>
>>
> It's not an actual checkpoints.
> It's is a fsync after checkpoint which create write spikes hurting server.
>
> You should set sysctl vm.dirty_background_bytes and vm.dirty_bytes to
> reasonable low values
>
>
> So use bla_bytes instead of bla_ratio?
>
>
Yes because on 256GB server
echo 10 > /proc/sys/vm/dirty_ratio
is equivalent to 26Gb dirty_bytes
and
echo 5 >/proc/sys/vm/dirty_background_ratio
is equivalent to 13Gb dirty_background_bytes
It is really huge values.
So kernel doesn't start write any pages out in background before it has at
least 13Gb dirty pages in kernel memory.
And at end of the checkpoint kernel trying flush all dirty pages to disk.
Even echo 1 >/proc/sys/vm/dirty_background_ratio is too high value for
contemporary server.
That is why *_bytes controls added to kernel.
--
Maxim Boguk
Senior Postgresql DBA
http://www.postgresql-consulting.com/
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Massive I/O spikes during checkpoint
2012-07-10 05:39 Massive I/O spikes during checkpoint David Kerr <dmk@mr-paradox.net>
2012-07-10 05:51 ` Re: Massive I/O spikes during checkpoint Maxim Boguk <maxim.boguk@gmail.com>
2012-07-10 06:03 ` Re: Massive I/O spikes during checkpoint David Kerr <dmk@mr-paradox.net>
2012-07-10 06:14 ` Re: Massive I/O spikes during checkpoint Maxim Boguk <maxim.boguk@gmail.com>
@ 2012-07-10 06:17 ` David Kerr <dmk@mr-paradox.net>
1 sibling, 0 replies; 10+ messages in thread
From: David Kerr @ 2012-07-10 06:17 UTC (permalink / raw)
To: Maxim Boguk <maxim.boguk@gmail.com>; +Cc: pgsql-performance
On 7/9/2012 11:14 PM, Maxim Boguk wrote:
>
>
> On Tue, Jul 10, 2012 at 4:03 PM, David Kerr <dmk@mr-paradox.net
> <mailto:dmk@mr-paradox.net>> wrote:
>
>
> On Jul 9, 2012, at 10:51 PM, Maxim Boguk wrote:
>
>>
>>
>> But what appears to be happening is that all of the data is
>> being written out at the end of the checkpoint.
>>
>> This happens at every checkpoint while the system is under load.
>>
>> I get the feeling that this isn't the correct behavior and
>> i've done something wrong.
>>
>>
>>
>> It's not an actual checkpoints.
>> It's is a fsync after checkpoint which create write spikes hurting
>> server.
>> You should set sysctl vm.dirty_background_bytes and vm.dirty_bytes
>> to reasonable low values
>
> So use bla_bytes instead of bla_ratio?
>
>
> Yes because on 256GB server
> echo 10 > /proc/sys/vm/dirty_ratio
> is equivalent to 26Gb dirty_bytes
>
> and
> echo 5 >/proc/sys/vm/dirty_background_ratio
> is equivalent to 13Gb dirty_background_bytes
>
> It is really huge values.
<sigh> yeah, I never bothered to think that through.
> So kernel doesn't start write any pages out in background before it has
> at least 13Gb dirty pages in kernel memory.
> And at end of the checkpoint kernel trying flush all dirty pages to disk.
>
> Even echo 1 >/proc/sys/vm/dirty_background_ratio is too high value for
> contemporary server.
> That is why *_bytes controls added to kernel.
Awesome, Thanks.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Massive I/O spikes during checkpoint
2012-07-10 05:39 Massive I/O spikes during checkpoint David Kerr <dmk@mr-paradox.net>
2012-07-10 05:51 ` Re: Massive I/O spikes during checkpoint Maxim Boguk <maxim.boguk@gmail.com>
2012-07-10 06:03 ` Re: Massive I/O spikes during checkpoint David Kerr <dmk@mr-paradox.net>
2012-07-10 06:14 ` Re: Massive I/O spikes during checkpoint Maxim Boguk <maxim.boguk@gmail.com>
@ 2012-07-10 12:44 ` Andres Freund <andres@2ndquadrant.com>
2012-07-10 13:36 ` Re: Massive I/O spikes during checkpoint Jeff Janes <jeff.janes@gmail.com>
1 sibling, 1 reply; 10+ messages in thread
From: Andres Freund @ 2012-07-10 12:44 UTC (permalink / raw)
To: pgsql-performance; +Cc: Maxim Boguk <maxim.boguk@gmail.com>; David Kerr <dmk@mr-paradox.net>
On Tuesday, July 10, 2012 08:14:00 AM Maxim Boguk wrote:
> On Tue, Jul 10, 2012 at 4:03 PM, David Kerr <dmk@mr-paradox.net> wrote:
> > On Jul 9, 2012, at 10:51 PM, Maxim Boguk wrote:
> >> But what appears to be happening is that all of the data is being
> >> written out at the end of the checkpoint.
> >>
> >> This happens at every checkpoint while the system is under load.
> >>
> >> I get the feeling that this isn't the correct behavior and i've done
> >> something wrong.
> >
> > It's not an actual checkpoints.
> > It's is a fsync after checkpoint which create write spikes hurting
> > server.
> >
> > You should set sysctl vm.dirty_background_bytes and vm.dirty_bytes to
> > reasonable low values
> >
> >
> > So use bla_bytes instead of bla_ratio?
>
> Yes because on 256GB server
> echo 10 > /proc/sys/vm/dirty_ratio
> is equivalent to 26Gb dirty_bytes
>
> and
> echo 5 >/proc/sys/vm/dirty_background_ratio
> is equivalent to 13Gb dirty_background_bytes
>
> It is really huge values.
>
> So kernel doesn't start write any pages out in background before it has at
> least 13Gb dirty pages in kernel memory.
> And at end of the checkpoint kernel trying flush all dirty pages to disk.
Thast not entirely true. The kernel will also writeout pages which haven't
been written to for dirty_expire_centisecs.
But yes, adjusting dirty_* is definitely a good idea.
Andres
--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Massive I/O spikes during checkpoint
2012-07-10 05:39 Massive I/O spikes during checkpoint David Kerr <dmk@mr-paradox.net>
2012-07-10 05:51 ` Re: Massive I/O spikes during checkpoint Maxim Boguk <maxim.boguk@gmail.com>
2012-07-10 06:03 ` Re: Massive I/O spikes during checkpoint David Kerr <dmk@mr-paradox.net>
2012-07-10 06:14 ` Re: Massive I/O spikes during checkpoint Maxim Boguk <maxim.boguk@gmail.com>
2012-07-10 12:44 ` Re: Massive I/O spikes during checkpoint Andres Freund <andres@2ndquadrant.com>
@ 2012-07-10 13:36 ` Jeff Janes <jeff.janes@gmail.com>
2012-07-10 13:41 ` Re: Massive I/O spikes during checkpoint Andres Freund <andres@2ndquadrant.com>
0 siblings, 1 reply; 10+ messages in thread
From: Jeff Janes @ 2012-07-10 13:36 UTC (permalink / raw)
To: Andres Freund <andres@2ndquadrant.com>; +Cc: pgsql-performance@postgresql.org, Maxim Boguk <maxim.boguk@gmail.com>; David Kerr <dmk@mr-paradox.net>
On Tue, Jul 10, 2012 at 5:44 AM, Andres Freund <andres@2ndquadrant.com> wrote:
> On Tuesday, July 10, 2012 08:14:00 AM Maxim Boguk wrote:
>>
>> So kernel doesn't start write any pages out in background before it has at
>> least 13Gb dirty pages in kernel memory.
>> And at end of the checkpoint kernel trying flush all dirty pages to disk.
> Thast not entirely true. The kernel will also writeout pages which haven't
> been written to for dirty_expire_centisecs.
There seems to be many situations in which it totally fails to do that.
Although I've never been able to categorize just what those situations are.
Cheers,
Jeff
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Massive I/O spikes during checkpoint
2012-07-10 05:39 Massive I/O spikes during checkpoint David Kerr <dmk@mr-paradox.net>
2012-07-10 05:51 ` Re: Massive I/O spikes during checkpoint Maxim Boguk <maxim.boguk@gmail.com>
2012-07-10 06:03 ` Re: Massive I/O spikes during checkpoint David Kerr <dmk@mr-paradox.net>
2012-07-10 06:14 ` Re: Massive I/O spikes during checkpoint Maxim Boguk <maxim.boguk@gmail.com>
2012-07-10 12:44 ` Re: Massive I/O spikes during checkpoint Andres Freund <andres@2ndquadrant.com>
2012-07-10 13:36 ` Re: Massive I/O spikes during checkpoint Jeff Janes <jeff.janes@gmail.com>
@ 2012-07-10 13:41 ` Andres Freund <andres@2ndquadrant.com>
0 siblings, 0 replies; 10+ messages in thread
From: Andres Freund @ 2012-07-10 13:41 UTC (permalink / raw)
To: Jeff Janes <jeff.janes@gmail.com>; +Cc: pgsql-performance@postgresql.org, Maxim Boguk <maxim.boguk@gmail.com>; David Kerr <dmk@mr-paradox.net>
On Tuesday, July 10, 2012 03:36:35 PM Jeff Janes wrote:
> On Tue, Jul 10, 2012 at 5:44 AM, Andres Freund <andres@2ndquadrant.com>
wrote:
> > On Tuesday, July 10, 2012 08:14:00 AM Maxim Boguk wrote:
> >> So kernel doesn't start write any pages out in background before it has
> >> at least 13Gb dirty pages in kernel memory.
> >> And at end of the checkpoint kernel trying flush all dirty pages to
> >> disk.
> >
> > Thast not entirely true. The kernel will also writeout pages which
> > haven't been written to for dirty_expire_centisecs.
>
> There seems to be many situations in which it totally fails to do that.
Totally as in diry pages sitting around without any io activity? Or just not
agressive enough?
Currently its a bit hard to speculate about all without specifying the kernel
because there have been massive rewrites of all that stuff in several kernels
in the last two years...
Andres
--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Massive I/O spikes during checkpoint
2012-07-10 05:39 Massive I/O spikes during checkpoint David Kerr <dmk@mr-paradox.net>
@ 2012-07-10 05:52 ` Jeff Janes <jeff.janes@gmail.com>
2012-07-10 05:59 ` Re: Massive I/O spikes during checkpoint David Kerr <dmk@mr-paradox.net>
1 sibling, 1 reply; 10+ messages in thread
From: Jeff Janes @ 2012-07-10 05:52 UTC (permalink / raw)
To: David Kerr <dmk@mr-paradox.net>; +Cc: pgsql-performance
On Mon, Jul 9, 2012 at 10:39 PM, David Kerr <dmk@mr-paradox.net> wrote:
>
> I thought that the idea of checkpoint_completion_target was that we try to
> finish writing
> out the data throughout the entire checkpoint (leaving some room to spare,
> in my case 30%
> of the total estimated checkpoint time)
>
> But what appears to be happening is that all of the data is being written
> out at the end of the checkpoint.
Postgres is writing data out to the kernel throughout the checkpoint.
But the kernel is just buffering it up dirty, until the end of the
checkpoint when the fsyncs start landing like bombs.
>
> This happens at every checkpoint while the system is under load.
>
> I get the feeling that this isn't the correct behavior and i've done
> something wrong.
>
> Also, I didn't see this sort of behavior in PG 8.3, however unfortunately, I
> don't have data to back that
> statement up.
Did you have less RAM back when you were running PG 8.3?
> Any suggestions. I'm willing and able to profile, or whatever.
Who much RAM do you have? What are your settings for /proc/sys/vm/dirty_* ?
Cheers,
Jeff
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Massive I/O spikes during checkpoint
2012-07-10 05:39 Massive I/O spikes during checkpoint David Kerr <dmk@mr-paradox.net>
2012-07-10 05:52 ` Re: Massive I/O spikes during checkpoint Jeff Janes <jeff.janes@gmail.com>
@ 2012-07-10 05:59 ` David Kerr <dmk@mr-paradox.net>
0 siblings, 0 replies; 10+ messages in thread
From: David Kerr @ 2012-07-10 05:59 UTC (permalink / raw)
To: Jeff Janes <jeff.janes@gmail.com>; +Cc: pgsql-performance
On Jul 9, 2012, at 10:52 PM, Jeff Janes wrote:
> On Mon, Jul 9, 2012 at 10:39 PM, David Kerr <dmk@mr-paradox.net> wrote:
>>
>> I thought that the idea of checkpoint_completion_target was that we try to
>> finish writing
>> out the data throughout the entire checkpoint (leaving some room to spare,
>> in my case 30%
>> of the total estimated checkpoint time)
>>
>> But what appears to be happening is that all of the data is being written
>> out at the end of the checkpoint.
>
> Postgres is writing data out to the kernel throughout the checkpoint.
> But the kernel is just buffering it up dirty, until the end of the
> checkpoint when the fsyncs start landing like bombs.
Ahh. duh!
I guess i assumed that the point of spreading the checkpoint I/O was
spreading the syncs out.
>
>>
>> This happens at every checkpoint while the system is under load.
>>
>> I get the feeling that this isn't the correct behavior and i've done
>> something wrong.
>>
>> Also, I didn't see this sort of behavior in PG 8.3, however unfortunately, I
>> don't have data to back that
>> statement up.
>
> Did you have less RAM back when you were running PG 8.3?
nope. I was on RHEL 5.5 back then though.
>
>> Any suggestions. I'm willing and able to profile, or whatever.
>
> Who much RAM do you have? What are your settings for /proc/sys/vm/dirty_* ?
256G
and I've been running with this for a while now, but I think that's the default in RHEL 6+
echo 10 > /proc/sys/vm/dirty_ratio
echo 5 >/proc/sys/vm/dirty_background_ratio
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2012-07-10 13:41 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-07-10 05:39 Massive I/O spikes during checkpoint David Kerr <dmk@mr-paradox.net>
2012-07-10 05:51 ` Maxim Boguk <maxim.boguk@gmail.com>
2012-07-10 06:03 ` David Kerr <dmk@mr-paradox.net>
2012-07-10 06:14 ` Maxim Boguk <maxim.boguk@gmail.com>
2012-07-10 06:17 ` David Kerr <dmk@mr-paradox.net>
2012-07-10 12:44 ` Andres Freund <andres@2ndquadrant.com>
2012-07-10 13:36 ` Jeff Janes <jeff.janes@gmail.com>
2012-07-10 13:41 ` Andres Freund <andres@2ndquadrant.com>
2012-07-10 05:52 ` Jeff Janes <jeff.janes@gmail.com>
2012-07-10 05:59 ` David Kerr <dmk@mr-paradox.net>
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