« MySQL Performance: Scalability on OLTP_RW Benchmark with MySQL 5.7 | Main | My slides about MySQL Performance from #PerconaLive Apr.2016 US »

Tuesday, 16 February, 2016

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

OLTP_RW 1M x8-tables MySQL 5.7 (config: trx_commit=2 double_write=0) :

OLTP_RW 1M x8-tables MySQL 5.7 (config: trx_commit=1 double_write=0) :

OLTP_RW 1M x8-tables MySQL 5.7 (config: trx_commit=1 double_write=1) :

  • 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! ;-))


Posted by Dimitri at 19:22
Categories: MySQL
blog comments powered by Disqus
Note: if you don't see any "comment" dialog above, try to access this page with another web browser, or google for known issues on your browser and DISQUS..