MySQL Bugs: #81561: MySQL 5.7 is 11% to 37% slower then 5.6 when using 4 threads

Bug #81561	MySQL 5.7 is 11% to 37% slower then 5.6 when using 4 threads
Submitted:	24 May 2016 7:22	Modified:	28 May 2016 5:46
Reporter:	Roel Van de Paar	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	5.7.12	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Test was performed on i7 server with SSD installed and using latest sysbench:

5.7.12: transactions: 7610 (152.14 per sec.)
5.6.30: transactions: 4794 (95.83 per sec.)  --- 37% less

Even without any options there is an 11+% drop in performance:

5.7.12: transactions: 7059 (141.09 per sec.)
5.6.30: transactions: 7954 (158.95 per sec.)

How to repeat:
* Use following mysqld options:
  --no-defaults --log-bin=mysql-bin --server-id=2 --log-slave-updates=true --gtid-mode=on --enforce-gtid-consistency=true --binlog_format=ROW --default_storage_engine=InnoDB --innodb_autoinc_lock_mode=2

* 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

Credits for this bug: Ramesh, Vadim, Krunal, Roel

if you specify same value for --sync-binlog in both servers,  does it even things out?  Also,  the first stats say 5.7 is faster?

Woops, I tagged the numbers the wrong way around. Will log a new bug with clearer details.

Shane, yes you're right

"Prior to MySQL 5.7.7, the default value of sync_binlog was 0, which configures no synchronizing to disk—in this case, the server relies on the operating system to flush the binary log's contents from time to time as for any other file. MySQL 5.7.7 and later use a default value of 1, which is the safest choice, but as noted above can impact performance."

https://dev.mysql.com/doc/refman/5.7/en/replication-options-binary-log.html#sysvar_sync_bi...

"which is the safest choice": why was this defaults change made?

New version http://bugs.mysql.com/bug.php?id=81630