MySQL – Why HDD is More Sensitive to innodb_fsync_threshold Than SSD

configurationMySQLperformancessd

What am I doing:
I want to do some experiments to find some interesting facts in DBMS when changing configurations above different physical storage media.

Disk:
Intel P45 SSD vs. SATA HDD 7200 RPM.

Workload:
TPC-C

Performance measurement:
Throughput

Standarized Result when changing innodb_fsync_threshold.
Every data point is repeated 10 times.
Result

Thanks for the help!

Best Answer

Might a hardware perspective explain the differences?

The steps of disk I/O are something like:

  1. buffering (for writes)
  2. send data to the device
  3. "Seek" the desired cylinder on the device
  4. Wait for the read/write head to be over the sector that needs read/written
  5. perform the I/O
  6. buffering (for reads)

Steps 3 and 4 are the dominant time consumers for HDD. For SDD there is no "head" to move nor platter spinning, so both of these steps take no time.

More to the point, 3 and 4 take a variable amount of time, based on, well, the phase of the moon. And a good controller, etc, can play games that optimize disk activity.

Most benchmarks have "real life" flaws.

  • They ignore interference from other threads -- that may be fighting for access to the same drive.
  • They pound on the resource to find the "max" that can be handled. While this is an interesting metric, in real life if the server is at the max, it is about to keel over. A butterfly flapping its wings can cause it to fall off the cliff.

Some optimizations:

  • RAID can cache things -- read and/or write -- to smooth out activity.
  • RAID can "lie" about when action is finished. This is because, for example, a "battery backed write cache" can safely hold a write in ram on the controller while returning "successful write" to the user. This may lead to a puzzling inflection point in the graph -- the device will suddenly slow down when the write cache is full.
Related Question