If you’re reading consumer SSD reviews and using them to estimate SSD performance under database workloads, you’d better stop. Databases are not your typical consumer applications and they do not use IO in the same way.
Let’s look, for example, at this excellent AnandTech review of Samsung 960 Pro – a consumer NVMe device that I happen to have in my test lab.
The summary table is actually great, showing the performance both at Queue Depth 1 (single threaded) as well as Queue Depth 32 – a pretty heavy concurrent load.
Even at QD1 we see 50K (4K) writes per second, which should be enough for pretty serious database workloads.
In reality, though, you might be in for some disappointing surprises. While “normal” buffered IO is indeed quite fast, this drive really hates fsync() calls, with a single thread fsync() latency of 3.5ms or roughly 300 fsync/sec. That’s just slightly more than your old school spinning drive.
To achieve Durability—the letter “D” of ACID—databases tend to rely on a write ahead log (WAL) which is sequentially written. The WAL must be synced to disk on every transaction commit using fsync() or similar measures, such as opening file with O_SYNC flag. These tend to have similar performance implications.
Other database operations use fsync() too, but writing WAL is where it usually hurts the most.
In a fully durable configuration MySQL tends to be impacted even more by poor fsync() performance. It may need to perform as many as three fsync operations per transaction commit. Group commit reduces the impact on throughput but transaction latency will still be severely impacted
Want more bad news? If the fsync() performance is phenomenal on your consumer SSD it indeed might be too good to be true. Over the years, some consumer SSDs “faked” fsync and accepted possible data loss in the case of power failure. This might not be a big deal if you only use them for testing but it is a showstopper for any real use.
Want to know more about your drive’s fsync() performance? You can use these sysbench commands:
|
1 |
sysbench fileio --time=60 --file-num=1 --file-extra-flags= --file-total-size=4096 --file-block-size=4096 --file-fsync-all=on --file-test-mode=rndwr --file-fsync-freq=0 --file-fsync-end=0 --threads=1 --percentile=99 prepare<br><br>sysbench fileio --time=60 --file-num=1 --file-extra-flags= --file-total-size=4096 --file-block-size=4096 --file-fsync-all=on --file-test-mode=rndwr --file-fsync-freq=0 --file-fsync-end=0 --threads=1 --percentile=99 run | grep "avg:" |
You can also use ioping as described in this blog post
I wish that manufacturers’ tech specifications described fsync latency, along with a clear statement as to whether the drive guarantees no loss of data on power failure. Likewise, I wish folk doing storage reviews could include these in their research.
Interested in fsync() performance for variety of devices? Yves Trudeau wrote an excellent blog post about fsync() performance on various storage devices a few months ago.
Principal Support Escalation Specialist Sveta Smirnova presents Troubleshooting MySQL Concurrency Issues with Load Testing Tools.
You can download a three part series of eBooks by Principal Consultant Alexander Rubin and me on MySQL Performance.
Databases are not your typical consumer applications. They are designed to deliver optimal performance, flexibility, reliability and scalability, all the while being equipped to handle heavy workloads, with an eye toward reducing cost. Ensuring your database satisfactorily meets your needs involves several considerations, from choosing the right database to early database architecture decisions. Download the following Percona white papers for in-depth analyses and recommendations on those topics and more:
