MySQL 5.6: an incomplete guide to avoiding performance regressions

MySQL 5.6: an incomplete guide to avoiding performance regressions: I spent more time looking at the performance regressions in MySQL 5.6 for single-threaded read-only workloads and I can get 5.6 closer to 5.1 by disabling a few options when building MySQL 5.6 -- optimizer tracing & profiling. This is my summary of things to do if you want to avoid some of the performance regressions I encountered while doing update-only and read-only performance tests for single-threaded and high-concurrency workloads:

• Disable optimizer tracing --  use -DOPTIMIZER_TRACE=0 with cmake
• Disable profiling -- use -DENABLED_PROFILING=0 with cmake
• Disable performance schema -- use -DWITH_PERFSCHEMA_STORAGE_ENGINE=0 with cmake. See bug 68413 and many blog posts from me.
• Set metadata_locks_hash_instances=256 -- see bug 68487
• Set innodb_checksum_algorithm=CRC32 -- but read the manual first! This isn't a new problem. This is a faster checksum algorithm not available in older versions of InnoDB.
• Get the fix for bug 68588 to avoid stalls on sleep during fsync. This isn't a new problem. There is more work in progress to fix new regressions in InnoDB. This is my placeholder for the problem in general.
• Set innodb_io_capacity and innodb_lru_scan_depth to values that are large enough given the IO capacity of your server. This wasn't as important in the past when InnoDB did furious flushing. This is very important in 5.6. More details are here.

I repeated tests for single-thread read-only sysbench after disabling optimizer tracing and profiling as described above and this reduced the 5.6 regression. I think the remaining overhead is in the optimizer.

• 11916 QPS -- orig5163
• 11795 QPS -- fb5163
• 11286 QPS -- orig5610, no perf schema, optimizer tracing, profiling
• 10875 QPS -- orig5610, no perf schema

DIGITAL JUICE