MySQL regressions: update-nonindex vs InnoDB

I started to look at CPU overheads in MyRocks and upstream InnoDB. While I am happy to file bugs for MyRocks as they are likely to be fixed, I am not sure how much energy I want to put into proper bug reports for upstream InnoDB. So I will just write blog posts about them for now.

I created flamegraphs while running sysbench with cached databases (more likely to be CPU bound) and the problem here occurs on an 8-core PN53 where sysbench was run with 1 thread. Here I use perf record -e cycles to collect data for flamegraphs and then I focus on the percentage of samples in a given function (and its callees) as a proxy for CPU overhead.

The flamegraphs are here.

The workload here is the update-nonindex microbenchmark. The throughput for a release relative to MySQL 5.6.51 is -> (QPS for $version) / (QPS for 5.6.51). The results below show that 8.0.37 gets about 62% of the QPS relative to 5.6.51.

• 0.86 in 5.7.44
• 0.79 in 8.0.11
• 0.67 in 8.0.28
• 0.62 in 8.0.37
  

From the numbers above there will soon be a release that only gets 50% of the QPS relative to 5.6.51. From the flamegraphs there is time spent in purge, networking and processing update statements. I focus on the time spent processing update statements. The percentage of samples (a proxy for CPU time) in mysqld_stmt_execute and its callees is:

• 32.77% in 5.6.51 from ~17% in mysql_update and ~12% from trans_commit_stmt
• 65.89% in 5.7.44 from ~39% in Sql_cmd_update::execute and ~22% from trans_commit_stmt
• 67.30% in 8.0.28 from ~38% in Sql_cmd_dml::execute and ~23% from trans_commit_stmt
• 67.97% in 8.0.37 from ~40% in Sql_cmd_dml::execute and ~21% from trans_commit_stmt