SSDs, power loss protection and fsync latency

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;drfor 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 slowuse an enterprise SSD if possible, if not run tests to understand fsync and fdatasync latencyUpdates:I am not surprised that Tanel Poder has a great blog post on this topicInnoDB, O_DIRECT and O_DIRECT_NO_FSYNCWhen 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 fileDo the batch of in-place (16kb) page writesCall fsync once per database file that was written by step 2When 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.The following table uses results while running the Insert Benchmark for InnoDB to compute the ratio of fsyncs per write using the SHOW GLOBAL STATUS counters:Innodb_data_fsyncs / Innodb_data_writesAnd 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. 5.7.44  8.0.44.01046  .00729  O_DIRECT.00172  .00053  O_DIRECT_NO_FSYNCPower loss protectionI 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 fioI 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 writesO_DIRECT with an fsync per write, 16kb writesO_DIRECT with an fdatasync per write, 16kb writesO_DIRECT without fsync, 2M writesO_DIRECT with an fsync per write, 2M writesO_DIRECT with an fdatasync per write, 2M writesResults from all tests are here. I did the test on several servers:dell32a 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.gcpa 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.hetzan 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. ser7a 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.socket2a 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.Results: overviewThis table lists fsync and fdatasync latency per server: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 SSDssync latency after 2M writes is sometimes much larger than after 16kb writesfor 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.--- Sync latency in microseconds for sync after 16kb writesdell    hetz    ser7    socket2891.1   12.4    2974.2  1.6     fsync447.4    9.8    2783.2  0.7     fdatasyncgcplocal devices           hyperdiskext-4   xfs             ext-4   xfs56.2    39.5            738.1   635.0   fsync28.1    29.0             46.8    46.0   fdatasync--- Sync latency in microseconds for sync after 2M writesdell    hetz    ser7    socket2980.1   58.2    5396.8  139.1   fsync449.7   10.8    3508.2    2.2   fdatasyncgcplocal devices           hyperdiskext-4   xfs             ext-4   xfs1020.4  916.8           821.2   778.9   fsync 832.4  809.7            63.6    51.2   fdatasyncResults: dellSummary: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 expectedLegend:w/s - writes/sMB/s - MB written/ssync - latency per sync (fsync or fdatasync)16 KB writesw/s     MB/s    sync    test43400   646.6   0.0     no-sync43500   648.5   0.0     no-sync-1083    16.1    891.1   fsync1085    16.2    889.2   fsync-2100    31.3    447.4   fdatasync2095    31.2    448.6   fdatasync2 MB writesw/s     MB/s    sync    test2617    4992.5  0.0     no-sync2360    4502.3  0.0     no-sync-727     1388.5  980.1   fsync753     1436.2  942.5   fsync-1204    2297.4  449.7   fdatasync1208    2306.0  446.9   fdatasyncResults: gcpSummaryLocal 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.Legend:w/s - writes/sMB/s - MB written/ssync - latency per sync (fsync or fdatasync)--- ext-4 and local devices16 KB writesw/s     MB/s    sync    test10100   150.7   0.0     no-sync10300   153.5   0.0     no-sync-6555    97.3    56.2    fsync6607    98.2    55.1    fsync-8189    122.1   28.1    fdatasync8157    121.1   28.2    fdatasync2 MB writesw/s     MB/s    sync    test390     744.8   0.0     no-sync390     744.8   0.0     no-sync-388     741.0   1020.4  fsync388     741.0   1012.7  fsync-390     744.8   832.4   fdatasync390     744.8   869.6   fdatasync--- xfs and local devices16 KB writesw/s     MB/s    sync    test9866    146.9   0.0     no-sync9730    145.0   0.0     no-sync-7421    110.6   39.5    fsync7537    112.5   38.3    fsync-8100    121.1   29.0    fdatasync8117    121.1   28.8    fdatasync2 MB writesw/s     MB/s    sync    test390     744.8   0.0     no-sync390     744.8   0.0     no-sync-389     743.9   916.8   fsync389     743.9   919.1   fsync-390     744.8   809.7   fdatasync390     744.8   806.5   fdatasync--- ext-4 and Hyperdisk Balanced16 KB writesw/s     MB/s    sync    test2093    31.2    0.0     no-sync2068    30.8    0.0     no-sync-804     12.0    738.1   fsync798     11.9    740.6   fsync-1963    29.3    46.8    fdatasync1922    28.6    49.0    fdatasync2 MB writesw/s     MB/s    sync    test348     663.8   0.0     no-sync367     701.0   0.0     no-sync-278     531.2   821.2   fsync271     517.8   814.1   fsync-358     683.8   63.6    fdatasync345     659.0   64.5    fdatasync--- xfs and Hyperdisk Balanced16 KB writesw/s     MB/s    sync    test2033    30.3    0.0     no-sync2004    29.9    0.0     no-sync-870     13.0    635.0   fsync858     12.8    645.0   fsync-1787    26.6    46.0    fdatasync1727    25.7    49.6    fdatasync2 MB writesw/s     MB/s    sync    test343     655.2   0.0     no-sync343     655.2   0.0     no-sync-267     511.2   778.9   fsync268     511.2   774.7   fsync-347     661.8   51.2    fdatasync336     642.8   54.4    fdatasyncResults: hetzSummarythis has an enterprise SSD with excellent (low) sync latencyLegend:w/s - writes/sMB/s - MB written/ssync - latency per sync (fsync or fdatasync)16 KB writesw/s     MB/s    sync    test37700   561.7   0.0     no-sync37500   558.9   0.0     no-sync-25200   374.8   12.4    fsync25100   374.8   12.4    fsync-27600   411.0   0.0     fdatasync27200   404.4   9.8     fdatasync2 MB writesw/s     MB/s    sync    test1833    3497.1  0.0     no-sync1922    3667.8  0.0     no-sync-1393    2656.9  58.2    fsync1355    2585.4  59.6    fsync-1892    3610.6  10.8    fdatasync1922    3665.9  10.8    fdatasyncResults: ser7Summary:this has a consumer SSD with high sync latencyresults had much variance (see the 2MB results below) and results at higher concurrency. This is a great SSD, but not for my use case.Legend:w/s - writes/sMB/s - MB written/ssync - latency per sync (fsync or fdatasync)16 KB writesw/s     MB/s    sync    test34000   506.4   0.0     no-sync40200   598.9   0.0     no-sync-325     5.0     2974.2  fsync333     5.1     2867.3  fsync-331     5.1     2783.2  fdatasync330     5.0     2796.1  fdatasync2 MB writesw/s     MB/s    sync    test362     691.4   0.0     no-sync364     695.2   0.0     no-sync-67      128.7   10828.3 fsync114     218.4   5396.8  fsync-141     268.9   3864.0  fdatasync192     368.1   3508.2  fdatasyncResults: socket2Summary:this has an enterprise SSD with excellent (low) sync latency after small writes, but fsync latency after 2MB writes is much largerLegend:w/s - writes/sMB/s - MB written/ssync - latency per sync (fsync or fdatasync)16 KB writesw/s     MB/s    sync    test49500   737.2   0.0     no-sync49300   734.3   0.0     no-sync-44500   662.8   1.6     fsync45400   676.2   1.5     fsync-46700   696.2   0.7     fdatasync45200   674.2   0.7     fdatasync2 MB writesw/s     MB/s    sync    test707     1350.4  0.0     no-sync708     1350.4  0.0     no-sync-703     1342.8  139.1   fsync703     1342.8  122.5   fsync-707     1350.4  2.2     fdatasync707     1350.4  2.1     fdatasync