0

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!

2
  • What was the test? Did you also vary io_capacity and other settings?
    – Rick James
    Jan 6, 2021 at 8:13
  • Thanks @James. I left the io_capacity always as default. The test I run: github.com/Percona-Lab/tpcc-mysql.
    – Tim He
    Jan 6, 2021 at 10:03

1 Answer 1

1

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.
1
  • Thanks very much for the meticulous answer!
    – Tim He
    Jan 7, 2021 at 2:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.