SSDs, power loss protection and fsync latency

Summary

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:InnoDB, 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...