pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPGSQL, checkpoints, and file system syncs
9+ messages / 5 participants
[nested] [flat]
* PGSQL, checkpoints, and file system syncs
@ 2014-04-03 00:38 Reza Taheri <rtaheri@vmware.com>
2014-04-08 14:00 ` Re: PGSQL, checkpoints, and file system syncs Shaun Thomas <sthomas@optionshouse.com>
0 siblings, 1 reply; 9+ messages in thread
From: Reza Taheri @ 2014-04-03 00:38 UTC (permalink / raw)
To: pgsql-performance
Hello PGSQL performance community,
You might remember that I pinged you in July 2012 to introduce the TPC-V benchmark, and to ask for a feature similar to clustered indexes. I am now back with more data, and a question about checkpoints. As far as the plans for the benchmark, we are hoping to release a benchmarking kit for multi-VM servers this year (and of course one can always simply configure it to run on one database)
Anyways, I am facing a situation that is affecting performance when checkpoints end. This becomes a big problem when you have many VMs sharing the same underlying storage, but I have reduced the problem to a single VM/single database here to make it easier to discuss.
Complete config info is in the attached files. Briefly, it is a 6-vCPU VM with 91G of memory, and 70GB in PGSQL shared buffers. The host has 512GB of memory and 4 sockets of Westmere (E7-4870) processors with HT enabled.
The data tablespace is on an ext4 file system on a (virtual) disk which is striped on 16 SSD drives in RAID 0. This is obviously overkill for the load we are putting on this VM, but in the usual config, the 16 SSDs are shared by 24 VMs. Log is on an ext3 file system on 4 spinning drives in RAID 1.
We are running PGSQL version 9.2 on RHEL 6.4, and here are some parameters of interest (postgresql.conf in the attachment):
checkpoint_segments = 1200
checkpoint_timeout = 360s
checkpoint_completion_target = 0.8
wal_sync_method = open_datasync
wal_buffers = 16MB
wal_writer_delay = 10ms
effective_io_concurrency = 10
effective_cache_size = 1024MB
When running tests, I noticed that when a checkpoint completes, we have a big burst of writes to the data disk. The log disk has a very steady write rate that is not affected by checkpoints except for the known phenomenon of more bytes in each log write when a new checkpoint period starts. In a multi-VM config with all VMs sharing the same data disks, when these write bursts happen, all VMs take a hit.
So I set out to see what causes this write burst. After playing around with PGSQL parameters and observing its behavior, it appears that the bursts aren't produced by the database engine; they are produced by the file system. I suspect PGSQL has to issue a sync(2)/fsync(2)/sync_file_range(2) system call at the completion of the checkpoint to ensure that all blocks are flushed to disk before creating a checkpoint marker. To test this, I ran a loop to call sync(8) once a second.
The pdf labeled "Chart 280" has the throughput, data disk activity, and checkpoint start/completion timestamps for the baseline case. You can see that the checkpoint completion, the write burst, and the throughput dip all occur at the same time, so much so that it is hard to see the checkpoint completion line under the graph of writes. It looks like the file system does a mini flush every 30 seconds. The pdf labeled "Chart 274" is the case with sync commands running in the background. You can see that everything is more smooth.
Is there something I can set in the PGSQL parameters or in the file system parameters to force a steady flow of writes to disk rather than waiting for a sync system call? Mounting with "commit=1" did not make a difference.
Thanks,
Reza
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Attachments:
checkpoints.tgz (application/x-compressed, 421.8K) download
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: PGSQL, checkpoints, and file system syncs
2014-04-03 00:38 PGSQL, checkpoints, and file system syncs Reza Taheri <rtaheri@vmware.com>
@ 2014-04-08 14:00 ` Shaun Thomas <sthomas@optionshouse.com>
2014-04-09 16:42 ` Re: PGSQL, checkpoints, and file system syncs Bruce Momjian <bruce@momjian.us>
0 siblings, 1 reply; 9+ messages in thread
From: Shaun Thomas @ 2014-04-08 14:00 UTC (permalink / raw)
To: Reza Taheri <rtaheri@vmware.com>; pgsql-performance
> Is there something I can set in the PGSQL parameters or in the file system
> parameters to force a steady flow of writes to disk rather than waiting for
> a sync system call? Mounting with "commit=1" did not make a difference.
The PostgreSQL devs actually had a long talk with the Linux kernel devs over this exact issue, actually. While we wait for the results of that to bear some fruit, I'd recommend using the dirty_background_bytes and dirty_bytes settings both on the VM side, and on the host server. To avoid excessive flushes, you want to avoid having more dirty memory than the system can handle in one gulp.
The dirty_bytes setting will begin flushing disks synchronously when the amount of dirty memory reaches this amount. While dirty_background_bytes will flush in the background when the amount of dirty memory hits the specified limit. It's the background flushing that will prevent your current problems, and it should be set at the same level as the amount of write cache your system has available.
So if you are on a 1GB RAID card, set it to 1GB. Once you have 1GB of dirty memory (from a checkpoint or whatever), Linux will begin flushing.
This is a pretty well-known issue on Linux systems with large amounts of RAM. Most VM servers fit that profile, so I'm not surprised it's hurting you.
--
Shaun Thomas
OptionsHouse | 141 W. Jackson Blvd | Suite 400 | Chicago IL, 60604
312-676-8870
sthomas@optionshouse.com
______________________________________________
See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: PGSQL, checkpoints, and file system syncs
2014-04-03 00:38 PGSQL, checkpoints, and file system syncs Reza Taheri <rtaheri@vmware.com>
2014-04-08 14:00 ` Re: PGSQL, checkpoints, and file system syncs Shaun Thomas <sthomas@optionshouse.com>
@ 2014-04-09 16:42 ` Bruce Momjian <bruce@momjian.us>
0 siblings, 0 replies; 9+ messages in thread
From: Bruce Momjian @ 2014-04-09 16:42 UTC (permalink / raw)
To: Shaun Thomas <sthomas@optionshouse.com>; +Cc: Reza Taheri <rtaheri@vmware.com>; pgsql-performance
On Tue, Apr 8, 2014 at 02:00:05PM +0000, Shaun Thomas wrote:
> So if you are on a 1GB RAID card, set it to 1GB. Once you have 1GB
> of dirty memory (from a checkpoint or whatever), Linux will begin
> flushing.
>
> This is a pretty well-known issue on Linux systems with large amounts
> of RAM. Most VM servers fit that profile, so I'm not surprised it's
> hurting you.
Agreed. The dirty kernel defaults Linux defaults are too high for
systems with large amounts of memory. See sysclt -a | grep dirty for a
list.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ Everyone has their own god. +
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 9+ messages in thread
* PGSQL, checkpoints, and file system syncs
@ 2014-04-03 17:39 Reza Taheri <rtaheri@vmware.com>
2014-04-03 18:01 ` Re: PGSQL, checkpoints, and file system syncs Heikki Linnakangas <hlinnakangas@vmware.com>
0 siblings, 1 reply; 9+ messages in thread
From: Reza Taheri @ 2014-04-03 17:39 UTC (permalink / raw)
To: pgsql-performance
Hello PGSQL performance community,
You might remember that I pinged you in July 2012 to introduce the TPC-V benchmark. I am now back with more data, and a question about checkpoints. As far as the plans for the benchmark, we are hoping to release a benchmarking kit for multi-VM servers this year (and of course one can always simply configure it to run on one database)
I am now dealing with a situation of performance dips when checkpoints complete. To simplify the discussion, I have reproduced the problem on a single VM/single database.
Complete config info is in the attached files. Briefly, it is a 6-vCPU VM with 91G of memory, and 70GB in PGSQL shared buffers. The host has 512GB of memory and 4 sockets of Westmere (E7-4870) processors with HT enabled.
The data tablespace is on an ext4 file system on a (virtual) disk which is striped on 16 SSD drives in RAID 0. This is obviously overkill for the load we are putting on this one VM, but in the usual benchmarking config, the 16 SSDs are shared by 24 VMs. Log is on an ext3 file system on 4 spinning drives in RAID 1.
We are running PGSQL version 9.2 on RHEL 6.4; here are some parameters of interest (postgresql.conf is in the attachment):
checkpoint_segments = 1200
checkpoint_timeout = 360s
checkpoint_completion_target = 0.8
wal_sync_method = open_datasync
wal_buffers = 16MB
wal_writer_delay = 10ms
effective_io_concurrency = 10
effective_cache_size = 1024MB
When running tests, I noticed that when a checkpoint completes, we have a big burst of writes to the data disk. The log disk has a very steady write rate that is not affected by checkpoints except for the known phenomenon of more bytes in each log write when a new checkpoint period starts. In a multi-VM config with all VMs sharing the same data disks, when these write bursts happen, all VMs take a hit.
So I set out to see what causes this write burst. After playing around with PGSQL parameters and observing its behavior, it appears that the bursts aren't produced by the database engine; they are produced by the file system. I suspect PGSQL has to issue a sync(2)/fsync(2)/sync_file_range(2) system call at the completion of the checkpoint to ensure that all blocks are flushed to disk before creating a checkpoint marker. To test this, I ran a loop to call sync(8) once a second.
The graphs in file "run280.mht" have the throughput, data disk activity, and checkpoint start/completion timestamps for the baseline case. You can see that the checkpoint completion, the write burst, and the throughput dip all occur at the same time, so much so that it is hard to see the checkpoint completion line under the graph of writes. It looks like the file system does a mini flush every 30 seconds. The file "run274.mht" is the case with sync commands running in the background. You can see that everything is more smooth.
Is there something I can set in the PGSQL parameters or in the file system parameters to force a steady flow of writes to disk rather than waiting for a sync system call? Mounting with "commit=1" did not make a difference.
Thanks,
Reza
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Attachments:
checkpoints.tgz (application/x-compressed, 108.6K) download
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: PGSQL, checkpoints, and file system syncs
2014-04-03 17:39 PGSQL, checkpoints, and file system syncs Reza Taheri <rtaheri@vmware.com>
@ 2014-04-03 18:01 ` Heikki Linnakangas <hlinnakangas@vmware.com>
2014-04-03 18:11 ` Re: PGSQL, checkpoints, and file system syncs Reza Taheri <rtaheri@vmware.com>
2014-04-09 16:51 ` Re: PGSQL, checkpoints, and file system syncs Bruce Momjian <bruce@momjian.us>
0 siblings, 2 replies; 9+ messages in thread
From: Heikki Linnakangas @ 2014-04-03 18:01 UTC (permalink / raw)
To: Reza Taheri <rtaheri@vmware.com>; +Cc: pgsql-performance
On 04/03/2014 08:39 PM, Reza Taheri wrote:
> Hello PGSQL performance community,
> You might remember that I pinged you in July 2012 to introduce the TPC-V benchmark. I am now back with more data, and a question about checkpoints. As far as the plans for the benchmark, we are hoping to release a benchmarking kit for multi-VM servers this year (and of course one can always simply configure it to run on one database)
>
> I am now dealing with a situation of performance dips when checkpoints complete. To simplify the discussion, I have reproduced the problem on a single VM/single database.
>
> Complete config info is in the attached files. Briefly, it is a 6-vCPU VM with 91G of memory, and 70GB in PGSQL shared buffers. The host has 512GB of memory and 4 sockets of Westmere (E7-4870) processors with HT enabled.
>
> The data tablespace is on an ext4 file system on a (virtual) disk which is striped on 16 SSD drives in RAID 0. This is obviously overkill for the load we are putting on this one VM, but in the usual benchmarking config, the 16 SSDs are shared by 24 VMs. Log is on an ext3 file system on 4 spinning drives in RAID 1.
>
> We are running PGSQL version 9.2 on RHEL 6.4; here are some parameters of interest (postgresql.conf is in the attachment):
> checkpoint_segments = 1200
> checkpoint_timeout = 360s
> checkpoint_completion_target = 0.8
> wal_sync_method = open_datasync
> wal_buffers = 16MB
> wal_writer_delay = 10ms
> effective_io_concurrency = 10
> effective_cache_size = 1024MB
>
> When running tests, I noticed that when a checkpoint completes, we have a big burst of writes to the data disk. The log disk has a very steady write rate that is not affected by checkpoints except for the known phenomenon of more bytes in each log write when a new checkpoint period starts. In a multi-VM config with all VMs sharing the same data disks, when these write bursts happen, all VMs take a hit.
>
> So I set out to see what causes this write burst. After playing around with PGSQL parameters and observing its behavior, it appears that the bursts aren't produced by the database engine; they are produced by the file system. I suspect PGSQL has to issue a sync(2)/fsync(2)/sync_file_range(2) system call at the completion of the checkpoint to ensure that all blocks are flushed to disk before creating a checkpoint marker. To test this, I ran a loop to call sync(8) once a second.
>
> The graphs in file "run280.mht" have the throughput, data disk activity, and checkpoint start/completion timestamps for the baseline case. You can see that the checkpoint completion, the write burst, and the throughput dip all occur at the same time, so much so that it is hard to see the checkpoint completion line under the graph of writes. It looks like the file system does a mini flush every 30 seconds. The file "run274.mht" is the case with sync commands running in the background. You can see that everything is more smooth.
>
> Is there something I can set in the PGSQL parameters or in the file system parameters to force a steady flow of writes to disk rather than waiting for a sync system call? Mounting with "commit=1" did not make a difference.
Try setting the vm.dirty_bytes sysctl. Something like 256MB might be a
good starting point.
This comes up fairly often, see e.g.:
http://www.postgresql.org/message-id/flat/27C32FD4-0142-44FE-8488-9F366DC75966@mr-paradox.net
- Heikki
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: PGSQL, checkpoints, and file system syncs
2014-04-03 17:39 PGSQL, checkpoints, and file system syncs Reza Taheri <rtaheri@vmware.com>
2014-04-03 18:01 ` Re: PGSQL, checkpoints, and file system syncs Heikki Linnakangas <hlinnakangas@vmware.com>
@ 2014-04-03 18:11 ` Reza Taheri <rtaheri@vmware.com>
2014-04-04 06:11 ` Re: PGSQL, checkpoints, and file system syncs Ilya Kosmodemiansky <ilya.kosmodemiansky@postgresql-consulting.com>
1 sibling, 1 reply; 9+ messages in thread
From: Reza Taheri @ 2014-04-03 18:11 UTC (permalink / raw)
To: Heikki Linnakangas <hlinnakangas@vmware.com>; +Cc: pgsql-performance
> Try setting the vm.dirty_bytes sysctl. Something like 256MB might be a good
> starting point.
>
> This comes up fairly often, see e.g.:
> http://www.postgresql.org/message-id/flat/27C32FD4-0142-44FE-8488-
> 9F366DC75966@mr-paradox.net
>
> - Heikki
Thanks, Heikki. That sounds like my problem alright. I will play with these parameters right away, and will report back.
Cheers,
Reza
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: PGSQL, checkpoints, and file system syncs
2014-04-03 17:39 PGSQL, checkpoints, and file system syncs Reza Taheri <rtaheri@vmware.com>
2014-04-03 18:01 ` Re: PGSQL, checkpoints, and file system syncs Heikki Linnakangas <hlinnakangas@vmware.com>
2014-04-03 18:11 ` Re: PGSQL, checkpoints, and file system syncs Reza Taheri <rtaheri@vmware.com>
@ 2014-04-04 06:11 ` Ilya Kosmodemiansky <ilya.kosmodemiansky@postgresql-consulting.com>
2014-04-04 20:27 ` Re: PGSQL, checkpoints, and file system syncs Reza Taheri <rtaheri@vmware.com>
0 siblings, 1 reply; 9+ messages in thread
From: Ilya Kosmodemiansky @ 2014-04-04 06:11 UTC (permalink / raw)
To: Reza Taheri <rtaheri@vmware.com>; +Cc: Heikki Linnakangas <hlinnakangas@vmware.com>; pgsql-performance
Hi Reza,
vm.dirty_bytes indeed makes sense, but just in case: how exactly is
your ext4 mount? Particularly, have you disabled barrier?
Ilya
On Thu, Apr 3, 2014 at 8:11 PM, Reza Taheri <rtaheri@vmware.com> wrote:
>> Try setting the vm.dirty_bytes sysctl. Something like 256MB might be a good
>> starting point.
--
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: PGSQL, checkpoints, and file system syncs
2014-04-03 17:39 PGSQL, checkpoints, and file system syncs Reza Taheri <rtaheri@vmware.com>
2014-04-03 18:01 ` Re: PGSQL, checkpoints, and file system syncs Heikki Linnakangas <hlinnakangas@vmware.com>
2014-04-03 18:11 ` Re: PGSQL, checkpoints, and file system syncs Reza Taheri <rtaheri@vmware.com>
2014-04-04 06:11 ` Re: PGSQL, checkpoints, and file system syncs Ilya Kosmodemiansky <ilya.kosmodemiansky@postgresql-consulting.com>
@ 2014-04-04 20:27 ` Reza Taheri <rtaheri@vmware.com>
0 siblings, 0 replies; 9+ messages in thread
From: Reza Taheri @ 2014-04-04 20:27 UTC (permalink / raw)
To: ik@postgresql-consulting.com <ik@postgresql-consulting.com>; +Cc: Heikki Linnakangas <hlinnakangas@vmware.com>; pgsql-performance
Hi Ilya,
I mount the esx4 file system with: nofail,noatime,nodiratime,nobarrier,commit=1
The esxt3 file system for the log is mounted with: nofail,noatime,nodiratime,data=writeback
Attached is a tar file with charts from last night's runs. File 0_dirty_background_bytes.htm has the default with dirty_background_bytes=0 and dirty_background_ratio=10 (it's a warm-up run, so it starts with low throughput and a high read rate). I still see a little bit of burstiness with dirty_background_bytes=50MB, but no burstiness with dirty_background_ratio from 10MB down to even 1MB.
Thanks everyone for the advice,
Reza
> -----Original Message-----
> From: Ilya Kosmodemiansky [mailto:ilya.kosmodemiansky@postgresql-
> consulting.com]
> Sent: Thursday, April 03, 2014 11:12 PM
> To: Reza Taheri
> Cc: Heikki Linnakangas; pgsql-performance@postgresql.org
> Subject: Re: [PERFORM] PGSQL, checkpoints, and file system syncs
>
> Hi Reza,
>
> vm.dirty_bytes indeed makes sense, but just in case: how exactly is your
> ext4 mount? Particularly, have you disabled barrier?
>
> Ilya
>
> On Thu, Apr 3, 2014 at 8:11 PM, Reza Taheri <rtaheri@vmware.com> wrote:
> >> Try setting the vm.dirty_bytes sysctl. Something like 256MB might be
> >> a good starting point.
>
>
>
> --
> 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
Attachments:
dirty_background_bytes.tgz (application/x-compressed, 106.9K) download
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: PGSQL, checkpoints, and file system syncs
2014-04-03 17:39 PGSQL, checkpoints, and file system syncs Reza Taheri <rtaheri@vmware.com>
2014-04-03 18:01 ` Re: PGSQL, checkpoints, and file system syncs Heikki Linnakangas <hlinnakangas@vmware.com>
@ 2014-04-09 16:51 ` Bruce Momjian <bruce@momjian.us>
1 sibling, 0 replies; 9+ messages in thread
From: Bruce Momjian @ 2014-04-09 16:51 UTC (permalink / raw)
To: Heikki Linnakangas <hlinnakangas@vmware.com>; +Cc: Reza Taheri <rtaheri@vmware.com>; pgsql-performance
On Thu, Apr 3, 2014 at 09:01:08PM +0300, Heikki Linnakangas wrote:
> >Is there something I can set in the PGSQL parameters or in the file
> >system parameters to force a steady flow of writes to disk rather
> >than waiting for a sync system call? Mounting with "commit=1" did not
> >make a difference.
>
> Try setting the vm.dirty_bytes sysctl. Something like 256MB might be a
> good starting point.
>
> This comes up fairly often, see e.g.:
> http://www.postgresql.org/message-id/flat/27C32FD4-0142-44FE-8488-9F36
> 6DC75966@mr-paradox.net
Uh, should he set vm.dirty_bytes or vm.dirty_background_bytes? It is my
understanding that vm.dirty_background_bytes starts the I/O while still
accepting writes, while vm.dirty_bytes stops accepting writes during the
I/O, which isn't optimal. See:
https://www.kernel.org/doc/Documentation/sysctl/vm.txt
dirty_bytes
Contains the amount of dirty memory at which a process generating disk
writes will itself start writeback.
dirty_background_bytes
Contains the amount of dirty memory at which the background kernel
flusher threads will start writeback.
I think we want the flusher to be active, not necessarly the writing
process.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ Everyone has their own god. +
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2014-04-09 16:51 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2014-04-03 00:38 PGSQL, checkpoints, and file system syncs Reza Taheri <rtaheri@vmware.com>
2014-04-08 14:00 ` Shaun Thomas <sthomas@optionshouse.com>
2014-04-09 16:42 ` Bruce Momjian <bruce@momjian.us>
2014-04-03 17:39 PGSQL, checkpoints, and file system syncs Reza Taheri <rtaheri@vmware.com>
2014-04-03 18:01 ` Heikki Linnakangas <hlinnakangas@vmware.com>
2014-04-03 18:11 ` Reza Taheri <rtaheri@vmware.com>
2014-04-04 06:11 ` Ilya Kosmodemiansky <ilya.kosmodemiansky@postgresql-consulting.com>
2014-04-04 20:27 ` Reza Taheri <rtaheri@vmware.com>
2014-04-09 16:51 ` Bruce Momjian <bruce@momjian.us>
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