This has results to measure the impact of calling fsync (or fdatasync) per-write for files opened with O_DIRECT. My goal is to document the impact of the innodb_flush_method option.
The primary point of this post is to document the claim:
For an SSD without power loss protection, writes are fast but fsync is slow.
The secondary point of this post is to provide yet another example where context matters when reporting performance problems. This post is motivated by results that look bad when run on a server with slow fsync but look OK otherwise.
tl;dr
- for my mini PCs I will switch from the Samsung 990 Pro to the Crucial T500 to get lower fsync latency. Both are nice devices but the T500 is better for my use case.
- with a consumer SSD writes are fast but fsync is often slow
- use an enterprise SSD if possible, if not run tests to understand fsync and fdatasync latency
- I am not surprised that Tanel Poder has a great blog post on this topic
InnoDB, O_DIRECT and O_DIRECT_NO_FSYNC
When innodb_flush_method is set to O_DIRECT there are calls to fsync after each batch of writes. While I don't know the source like I used to, I did browse it for this blog post and then I looked at SHOW GLOBAL STATUS counters. I think that InnoDB does the following with it set to O_DIRECT:
- Do one large write to the doublewrite buffer, call fsync on that file
- Do the batch of in-place (16kb) page writes
- Call fsync once per database file that was written by step 2
When set to O_DIRECT_NO_FSYNC then the frequency of calls to fsync are greatly reduced and are only done in cases where important filesystem metadata needs to be updated, such as after extending a file. The reference manual is misleading WRT the following sentence. I don't think that InnoDB ever does an fsync after each write. It can do an fsync after each batch of writes:
O_DIRECT_NO_FSYNC: InnoDB uses O_DIRECT during flushing I/O, but skips the fsync() system call after each write operation.
Many years ago it was risky to use O_DIRECT_NO_FSYNC on some filesystems because the feature as implemented (either upstream or in forks) didn't do fsync for cases where it was needed (see comment about metadata above). I experienced problems from this and I only have myself to blame. But the feature has been enhanced to do the right thing. And if the #whynotpostgres crowd wants to snark about MySQL not caring about data, lets not forget that InnoDB had per-page checksums long before Postgres -- those checksums made web-scale life much easier when using less than stellar hardware.
Innodb_data_fsyncs / Innodb_data_writes
And from this table a few things are clear. First, there isn't an fsync per write with O_DIRECT but there might be an fsync per batch of writes as explained above. Second, the rate of fsyncs is greatly reduced by using O_DIRECT_NO_FSYNC.
.00172 .00053 O_DIRECT_NO_FSYNC
Power loss protection
I am far from an expert on this topic, but most SSDs have a write-buffer that makes small writes fast. And one way to achieve speed is to buffer those writes in RAM on the SSD while waiting for enough data to be written to an extent. But that speed means there is a risk of data loss if a server loses power. Some SSDs, especially those marketed as enterprise SSDs, have a feature called power loss protection that make data loss unlikely. Other SSDs, lets call them consumer SSDs, don't have that feature while some of the consumer SSDs claim to make a best effort to flush writes from the write buffer on power loss.
One solution to avoiding risk is to only buy enterprise SSDs. But they are more expensive, less common, and many are larger (22120 rather than 2280) because more room is needed for the capacitor or other HW that provides the power loss protection. Note that power loss protection is often abbreviated as PLP.
For devices without power loss protection it is often true that writes are fast but fsync is slow. When fsync is slow then calling fsync more frequently in InnoDB will hurt performance.
Results from fio
I used this fio script to measure performance for writes for files opened with O_DIRECT. The test was run twice configuration for 5 minutes per run followed by a 5 minute sleep. This was repeated for 1, 2, 4, 8, 16 and 32 fio jobs but I only share results here for 1 job. The configurations tested were:
- O_DIRECT without fsync, 16kb writes
- O_DIRECT with an fsync per write, 16kb writes
- O_DIRECT with an fdatasync per write, 16kb writes
- O_DIRECT without fsync, 2M writes
- O_DIRECT with an fsync per write, 2M writes
- O_DIRECT with an fdatasync per write, 2M writes
- dell32
- a large server I have at home. The SSD is a Crucial T500 2TB using ext-4 with discard enabled and Ubuntu 24.04. This is a consumer SSD. While the web claims it has PLP via capacitors the fsync latency for it was almost 1 millisecond.
- gcp
- a c3d-standard-30-lssd from the Google cloud with 2 local NVMe devices using SW RAID 0 and 1TB of Hyperdisk Balanced storage configured for 50,000 IOPs and 800MB/s of throughput. The OS is Ubuntu 24.04 and I repeated tests for both ext-4 and xfs, both with discard enabled. I was not able to determine the brand of the local NVMe devices.
- hetz
- an ax162-s from Hetzner with 2 local NVME devices using SW RAID 1. Via udiskctl status I learned the devices are Intel D7-P5520 (now Solidigm). These are datacenter SSDs and the web claims they have power loss protection. The OS is Ubuntu 24.04 and the drives use ext-4 without discard enabled.
- ser7
- a mini-PC I have at home. The SSD is a Samsung 990 Pro using ext-4 with discard enabled and Ubuntu 24.04. This is a consumer SSD, the web claims it does not have PLP and fsync latency is several milliseconds.
- socket2
- a 2-socket server I have at home. The SSD is a Samsung PM-9a3. This is an enterprise SSD with power loss protection. The OS is Ubuntu 24.04 and the drives use ext-4 with discard enabled.
- for servers with consumer SSDs (dell, ser7) the latency is much larger on the ser7 that uses a Samsung 990 Pro than on the dell that uses a Crucial T500. This is to be expected given that the T500 has PLP while the 990 Pro does not.
- sync latency is much lower on servers with enterprise SSDs
- sync latency after 2M writes is sometimes much larger than after 16kb writes
- for the Google server with Hyperdisk Balanced storage the fdatasync latency was good but fsync latency was high. While with the local NVMe devices the latencies were larger than for enterprise SSDs but much smaller than for consumer SSDs.
- Write throughput drops dramatically when there is an fsync or fdatasync per write because sync latency is large.
- This servers uses a consumer SSD so high sync latency is expected
- w/s - writes/s
- MB/s - MB written/s
- sync - latency per sync (fsync or fdatasync)
- Local NVMe devices have lower sync latency and more throughput with and without a sync per write at low concurrency (1 fio job).
- At higher concurrency (32 fio jobs), the Hyperdisk Balanced setup provides similar throughput to local NVMe and would do even better had I paid more to get more IOPs and throughput. Results don't have nice formatting but are here for xfs on the local and Hyperdisk Balanced devices.
- fsync latency is ~2X larger than fdatasync on the local devices and closer to 15X larger on the Hyperdisk Balanced setup. That difference is interesting. I wonder what the results are for Hyperdisk Extreme.
- w/s - writes/s
- MB/s - MB written/s
- sync - latency per sync (fsync or fdatasync)
- this has an enterprise SSD with excellent (low) sync latency
- w/s - writes/s
- MB/s - MB written/s
- sync - latency per sync (fsync or fdatasync)
- this has a consumer SSD with high sync latency
- results had much variance (see the 2MB results below) and results at higher concurrency. This is a great SSD, but not for my use case.
- w/s - writes/s
- MB/s - MB written/s
- sync - latency per sync (fsync or fdatasync)
- this has an enterprise SSD with excellent (low) sync latency after small writes, but fsync latency after 2MB writes is much larger
- w/s - writes/s
- MB/s - MB written/s
- sync - latency per sync (fsync or fdatasync)
