Bug #81630 MySQL 5.7 is 37-45% slower then 5.6 when using binlogging due to defaults change
Submitted: 28 May 2016 5:46 Modified: 31 Aug 2016 6:30
Reporter: Roel Van de Paar (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Replication Severity:S5 (Performance)
Version:5.7.12 OS:Any
Assigned to: CPU Architecture:Any
Tags: performance

[28 May 2016 5:46] Roel Van de Paar
Description:
# 4 Threads
5.6.30: transactions: 7483 (149.60 per sec.)
5.7.12: transactions: 4689 (93.71 per sec.)  — A 37.36% decrease

# 100 Threads
5.6.30. transactions: 20216 (398.89 per sec.)
5.7.12. transactions: 11097 (218.43 per sec.) — A 45.24% decrease

# 150 Threads
5.6.30. transactions: 11852 (233.01 per sec.)
5.7.12. transactions: 6606 (129.80 per sec.) — A 44.29% decrease

The culprit is the change of --sync_binlog from 0 to 1. 

Even if both 5.6 and 5.7 have sync_binlog set to 0, there is still a 11% performance drop:

# 4 Threads
5.6.30: transactions: 7472 (149.38 per sec.)
5.7.12: transactions: 6594 (131.86 per sec.)  — A 11.73% decrease

Continuing to use 4 threads (not tested with higher number of threads), there is a performance increase of 19.29% when both 5.6 and 5.7 are set to sync_binlog=1, but this benefit disappears with sync_binlog=100 (13.83% decrease) and a performance decrease is even visible with binary logs turned off completely (11.76% decrease).

How to repeat:
* Use following mysqld options: –no-defaults –log-bin=mysql-bin –server-id=2
* Use Sysbench version 0.5
* Prepare: sysbench --test=/usr/share/doc/sysbench/tests/db/parallel_prepare.lua --oltp-auto-inc=off --mysql-engine-trx=yes --mysql-table-engine=innodb --oltp_table_size=1000000 --oltp_tables_count=1 --mysql-db=test --mysql-user=root --db-driver=mysql --mysql-socket=/path_to_socket_file/your_socket_file.sock prepare
* Run: sysbench --report-interval=10 --oltp-auto-inc=off --max-time=50 --max-requests=0 --mysql-engine-trx=yes --test=/usr/share/doc/sysbench/tests/db/oltp.lua --init-rng=on --oltp_index_updates=10 --oltp_non_index_updates=10 --oltp_distinct_ranges=15 --oltp_order_ranges=15 --oltp_tables_count=1 --num-threads=4 --oltp_table_size=1000000 --mysql-db=test --mysql-user=root --db-driver=mysql --mysql-socket=/path_to_socket_file/your_socket_file.sock run

Suggested fix:
[29 Aug 2016 14:20] Sinisa Milivojevic
Hi Roel,

Thank you very much for your report. You have provided us with info regarding slowing down of the server operations, depending on the number of threads and versions. 

The speed is expressed in number of transaction. In order for us to repeat, we would have to have your data and exactly the transactions that you were running.

Many thanks in advance.
[30 Aug 2016 4:17] Roel Van de Paar
Hi Sinisa,

Thanks. The requested information is already there (sysbench prepare generates the data before the actual test).
[30 Aug 2016 13:23] Sinisa Milivojevic
Roel,

I  suppose that binary log is in the RBR format and not in SBR format. However, I do require your confirmation.

Also, I guess that those LUA tests are part of the sysbench ??? 

I will have to transfer the testing to someone more familiar with sysbench.
[30 Aug 2016 18:29] Sinisa Milivojevic
Hi Roel,

Regarding a change in the --sync-binlog setting, that brings the expected results.

Regarding the difference reported on pure RW performance between 5.7 and 5.6 (even with binary log switched off) -- unfortunately this is a known problem, we got this regression in 5.7 (and some others) and it's still remain unfixed.. -- this is not just for "single thread", but for many "low load" cases (1, 4, even 8 concurrent user sessions).. 

In order to fix the problem we have added two new WorkLog entries.

Hence, this bug is verified.
[31 Aug 2016 6:30] Roel Van de Paar
Thanks. Binlog format is default.