Low-concurrency performance for updates and the Heap engine: MySQL 5.7 vs previous releases

The low-concurrency performance regression evaluation continues after evaluating range queries. This post covers updates with the Heap engine. The Heap engine is used as the base case and the next post has results for InnoDB. The database has 64,000 rows but all updates were for the same row. Tests were done for updates to an indexed and then an unindexed column at 1, 4 and 32 threads. Tests were also repeated with the binlog disabled and then enabled. There are regressions at 1, 4 and 32 threads. The summary is:

• Response time at 1 thread for 5.7.5 is between 1.55X and 1.67X worse than 5.0.85 
• Response time at 32 threads for 5.7.5 is between 1.19X and 1.49X worse than 5.0.85 
• In all cases it is worse in 5.7 than in 5.6
• Regressions at 4 and 32 threads are much worse when the binlog is enabled

All of the graphs graphs use normalized response time. For each tested version that is the time at X threads divided by the time at X threads for MySQL 5.0.85 where X is 1, 4 and 32.

update unindexed, binlog off

This section has results for updates to an unindexed column with the binlog disabled. Newer releases are faster at 4 and 32 threads. There is a gradual regression at 1 thread and the response time for MySQL 5.7.5 is 1.55X worse than for 5.0.85.

update unindexed, binlog on

This section has results for updates to an unindexed column with the binlog enabled. Newer releases are faster at 4 and 32 threads. There is a gradual regression at 1 thread and the response time for MySQL 5.7.5 is 1.67X worse than for 5.0.85. The single-thread regression here is worse than with the binlog disabled, so there is some regression from the binlog code.

update indexed, binlog off

This section has results for updates to an indexed column with the binlog disabled. Newer releases are faster at 4 and 32 threads. There is a gradual regression at 1 thread and the response time for MySQL 5.7.5 is 1.56X worse than for 5.0.85.

update indexed, binlog on

This section has results for updates to an indexed column with the binlog enabled. Newer releases are faster at 4 and 32 threads. There is a gradual regression at 1 thread and the response time for MySQL 5.7.5 is 1.67X worse than for 5.0.85. The single-thread regression here is worse than with the binlog disabled, so there is some regression from the binlog code.

command lines

Tests used mysqlslap as described in previous posts. The command lines for updates to the non-indexed and indexed columns are:
mysqlslap --concurrency=$t -h127.0.0.1 --create-schema=test1 --number-of-queries=$(( $t * 100000 )) --create=${ddl} --query="update foo set l = l + 2 where i=100"

mysqlslap --concurrency=$t -h127.0.0.1 --create-schema=test1 --number-of-queries=$(( $t * 100000 )) --create=${ddl} --query="update foo set k = k + 2 where i=100"