MySQL Performance : 8.0 GA on IO-bound Sysbench OLTP with Optane -vs- SSD

MySQL Performance on IO-bound workloads is still extremely depending on the underlaying storage layer (thus is directly depending on your Storage Performance).. Indeed, flash storage is definitively changing the game, but even with flash there is, as usual, "flash and flash" -- all storage vendors are improving their stuff constantly, so every time you have something new to discover and to learn ;-)) During all my MySQL 8.0 GA tests I was very pleasantly surprised by IO performance delivered by Intel Optane SSD. However, what the storage device can deliver alone on pure IO tests is not at all the same to what you could observe when it's used by MySQL -- unfortunately, in the past I've observed many cases when with a device claimed to be x2 times faster we were even not observing 10% gain.. But MySQL 8.0 is probably the most best placed MySQL version today to re-visit all this IO-bound story (there are many "under-hood" changes in the code helping to use your storage more efficiently) -- well, we're definitively yet very far from "perfect" ;-)) -- but again, as usual -- "everything is relative".. And for the "relative" point for this article I'll propose you to get a look on 2 different flash drives from Intel :

yes, both devices are from Intel, so it's only Intel -vs- Intel (e.g. no blame for "competition" post ;-))

so far, if you'll look on their specs, both drives are looking pretty good for avg. IO-bound workloads, except that Optane drive is claimed to be x5 times faster in all categories (and it's particularly impressive with its low IO latency -- see 1M IO-bound QPS with MySQL 8.0 -- so, can confirm at least for latency ;-)) -- however, will the x5 claim still be valid in different conditions / workloads ?

For my testing I'll use the same config as before (the same Skylake server as well), and the key config points are :
  • InnoDB page size : 16K
  • trx_commit : 1 (flush REDO on every commit)
  • single flash drive (just EXT4 on top of single drive, no RAID, no LVM, etc..)
  • InnoDB Buffer Pool (BP) : 128G / 64G / 32G (will vary along with test scenarios)

(no double write, no binlog for the moment -- these ones will be covered later, patience ;-))..

So far, let's start first with so called "in-memory" Sysbench OLTP_RW workload :
  • test workload : Sysbench OLTP_RW uniform
  • concurrent users : 1, 2, 4, .. 1024
  • data volume : 8 tables of 10M rows each (~20GB)
  • InnoDB Buffer Pool (BP) : 128GB
  • so, all the data will be fully cached in BP, thus NO any IO reads (the reason to be so called "in-memory")
  • however, there will be definitively IO writes (as there are writes in OLTP_RW ;-))
  • and in our case, if all is going well, there will be only 2 types of writes :
    • 1) dirty pages flushing
    • 2) REDO log writes
  • the 1) is going in background, and as soon as your flushing is fast enough -- your performance is not impacted
  • the 2) are pure sequential writes only mixed with periodic fsync(), and generally as soon as you use a good flash drive for your REDO files -- you're fine ;-))

Ok, what about results ?

Sysbench OLTP_RW 10Mx8-tables BP = 128GB : Intel SSD -vs- Intel Optane

Comments :
  • on the left side of the graph you can see TPS level of MySQL 8.0 running on SSD, and on the right side -- the same but on Optane
  • concurrent users level is progressing from 1 to 2, 4, 8, .. 1024
  • and from TPS results you can see that up to 16 users there is nearly no TPS difference between SSD and Optane
  • then Optane is slightly better on 64usr level, and definitively better on higher load
  • but you don't see any sign of expected x5 difference, right ?
  • and if you'll stop here, you could just say "all this Optane story is b*shit !", Dimitri, WFT ?.. ;-))

Indeed, on such a small data volume and having all the active data set cached in BP you'll not see much difference with a faster storage.. -- however, what will happen if your data will grow ?..

Read more... (9 min remaining to read)



TEL/電話+86 13764045638
QQ 47079569