MySQL Bugs: #66865: Performance degradation in MySQL 5.6

Bug #66865	Performance degradation in MySQL 5.6
Submitted:	19 Sep 2012 3:18	Modified:	25 Jan 2017 14:20
Reporter:	Vadim TKACHENKO	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.6	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
In MySQL 5.6 I observe a severe performance degradation.

It is workload described there http://www.mysqlperformanceblog.com/2012/09/10/adaptive-flushing-in-mysql-5-6-cont/

but now I am using small buffer pool size, i.e. 25GB (against 250GB of data),
do it gives an intensive IO load.

With MySQL 5.6 I am getting about 70-80 transactions per second,
while even in MySQL 5.5 with smaller log file sizes I have about 350 transaction per second.

The benchmark is run on very fast PCIe SSD that is able to handle 200.000 IOPs

How to repeat:
related config for MySQL 5.6

innodb_file_per_table = true
innodb_data_file_path = ibdata1:100M:autoextend
innodb_flush_method = O_DIRECT
innodb_log_buffer_size = 256M

innodb_flush_log_at_trx_commit = 1
innodb_buffer_pool_size = 25G
innodb_buffer_pool_instances=8

innodb_log_file_size = 4G ( I tried 8G also)
innodb_log_files_in_group = 2
innodb_read_io_threads = 16
innodb_write_io_threads = 16
innodb_io_capacity = 20000
innodb_max_io_capacity = 40000
innodb_buffer_pool_instances=1 (I tried 8 also)

and I tried innodb_adaptive_flushing=OFF, 
but nothing helped.

For MySQL 5.5 I use:

innodb_file_per_table = true
innodb_data_file_path = ibdata1:100M:autoextend
innodb_flush_method = O_DIRECT
innodb_log_buffer_size = 256M

innodb_flush_log_at_trx_commit = 1
innodb_buffer_pool_size = 25G

innodb_log_file_size = 2000M
innodb_log_files_in_group = 2
innodb_read_io_threads = 16
innodb_write_io_threads = 16
innodb_io_capacity = 20000
innodb_buffer_pool_instances=8

Hi Vadim,

thank you for reporting this issue! 
we're trying to reproduce it on our servers, and I'll be back to you when will need more details..

Rgds,
-Dimitri

Our teams worked hard and got it fixed in 5.7. More progress could be expected in 8.* if the timeframe will allow..

in 5.7 we implemented MT flushing + improved age-aware logic for pages, so the issue was gone.. but, patches are so large and complex that those will not be backported it to 5.6 , so the fix here is to upgrade to 5.7.