MySQL Bugs: #81630: MySQL 5.7 is 37-45% slower then 5.6 when using binlogging due to defaults change

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	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

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:

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.

Hi Sinisa,

Thanks. The requested information is already there (sysbench prepare generates the data before the actual test).

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.

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.

Thanks. Binlog format is default.