Why Consumer SSD Reviews are Useless for Database Performance Use Case

Why Consumer SSD Reviews are Useless for Database Performance Use Case

PREVIOUS POST
NEXT POST

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.

Anandtech Table reviewing consumer SSD performance

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.

Why is fsync() performance critical for databases?

To achieve Durabilitythe letter “D” of ACIDdatabases 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:

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.

Other Technical Resources

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.

You May Also Like

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:

PREVIOUS POST
NEXT POST

Share this post

Comments (6)

  • Mark Callaghan Reply

    I have consumer SSDs in my home perf test servers. Usually I run with fsync disabled and was surprised to learn how slow fsync was with tests a few months back.

    I asked nicely and fio (https://github.com/axboe/fio/blob/master/HOWTO) was enhanced to report fsync latency separately from write latency.

    I hope that sysbench oltp is updated to do the same — report commit latency separately.

    July 20, 2018 at 11:17 am
  • Nils Reply

    A disk that doesn’t guarantee that data is actually written after fsync() is defective, any review site that claims otherwise is very generous with the manufacturer. The cherry on top of this marketing BS sundae is disks claiming “power loss protection”. That should be a given, otherwise one might as well sell it as “write-only memory” or a hardware null device.

    July 20, 2018 at 1:15 pm
  • GeorgeL Reply

    Hi Peter thanks for sharing

    with

    –file-fsync-end=0

    I get

    invalid option: –file-fsync-end=0 –threads=1

    with sysbench 1.0.15 (using bundled LuaJIT 2.1.0-beta2)

    for me that should be –file-fsync-end=on ?

    sysbench fileio help
    sysbench 1.0.15 (using bundled LuaJIT 2.1.0-beta2)

    fileio options:
    –file-num=N number of files to create [128]
    –file-block-size=N block size to use in all IO operations [16384]
    –file-total-size=SIZE total size of files to create [2G]
    –file-test-mode=STRING test mode {seqwr, seqrewr, seqrd, rndrd, rndwr, rndrw}
    –file-io-mode=STRING file operations mode {sync,async,mmap} [sync]
    –file-async-backlog=N number of asynchronous operatons to queue per thread [128]
    –file-extra-flags=[LIST,…] list of additional flags to use to open files {sync,dsync,direct} []
    –file-fsync-freq=N do fsync() after this number of requests (0 – don’t use fsync()) [100]
    –file-fsync-all[=on|off] do fsync() after each write operation [off]
    –file-fsync-end[=on|off] do fsync() at the end of test [on]
    –file-fsync-mode=STRING which method to use for synchronization {fsync, fdatasync} [fsync]
    –file-merged-requests=N merge at most this number of IO requests if possible (0 – don’t merge) [0]
    –file-rw-ratio=N reads/writes ratio for combined test [1.5]

    July 20, 2018 at 4:34 pm
  • GeorgeL Reply

    nevermind had to move threads parameter to beginning of command

    sysbench fileio –threads=1 –time=30 –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 –percentile=99 run

    July 20, 2018 at 4:36 pm
  • bruno Reply

    I’m curious, every sql statement opens a transaction (including select), does it means that a database is limited to roughly 300 queries(fsync) per second?

    July 26, 2018 at 4:47 am
    • Nils Reply

      Since there are usually no disk writes happening selects aren’t constrained by fsync() performance in most cases.

      August 13, 2018 at 2:52 pm

Leave a Reply