DimitriK's (dim) Weblog

MySQL Performance: PFS and Checksums impact on OLTP_RW Benchmark with MySQL 5.7

This article is the follow-up on discussion started around MySQL 5.7 results on OLTP_RW Benchmark.. -- the point was about the impact of PERFORMANCE_SCHEMA (PFS) enabled, and InnoDB checksums on MySQL 5.7 performance within this OLTP_RW workload.

As promised, here are the results:

Just in case if the legend naming in graphs is not obvious :

• PS-off : "performance_schema=off" was used
• PS-def : "performance_schema=on" was used (default PFS instrumentation)
• chksum0 : "innodb_checksums=0" was used
• chksum1 : "innodb_checksums=0, innodb_checksum_algorithm=crc32" was used

• the impact of checksums is near zero when OLTP_RW workload is running on MySQL 5.7 with a less safe config (trx_commit=2)
• the checksum impact is bigger on trx_commit=1 (but not growing more since double_write is also enabled)
• something similar goes with PFS impact as well..
• however this gives me an expectation that once we'll fix the slowdown issues due trx_commit=1, we'll lower as well PFS impact here too (like it was already happened in the past)..
• so far, the checksum impact here is around 0-2%, while PFS impact is around 3%-7% (mostly bigger on higher concurrency load)
• interesting that "combined" impact of both (checksums + PFS=on) is not really much different from just PFS=on
• NOTE : if you're really worry a lot about CPU cycles used by PFS instrumentation -- since MySQL 5.7 you can compile MySQL binary yourself with various PFS compile flags to completely disable some kind of instrumentations you're sure you'll never use (for ex. mutexes/rw-locks, etc.) -- I'm not doing so, but you can ;-))
• NOTE : from the past experience I've also observed that compiling MySQL binaries without "-fno-omit-frame-pointer" option may give you additional 5%-10% speed-up !! (however, you'll not be able anymore to get a proper stack trace from a running process or in case of crash..) -- I'm not doing so, but you can ;-))
• but well, what is important here is that with or without PFS/checksums enabled MySQL 5.7 is still reaching over 40K TPS performance level on this workload, and it's still way bigger than I've ever observed on MySQL until now ;-))
• and our challenge here is to bring trx_commit=1 to the same level as trx_commit=2, and largely reduce the impact of double_write.. (but this is another story, stay tuned ;-))

As usual, any comments are welcome! And Thank You for using MySQL! ;-))

Rgds,
-Dimitri