Bug #68481 InnoDB LRU flushing for MySQL 5.6 needs work
Submitted: 25 Feb 2013 2:58 Modified: 25 Feb 2013 3:36
Reporter: Mark Callaghan Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:5.6.10 OS:Any
Assigned to: Assigned Account CPU Architecture:Any

[25 Feb 2013 2:58] Mark Callaghan
Description:
see http://mysqlha.blogspot.com/2013/02/mysql-56-io-bound-update-only-workloads.html

From reading launchpad source, there have been many changes to this code that are not in 5.6.10 but I think the problem remains.
1) furious flushing isn't done any more (furious means that sleep is skipped when a lot of work was done)
2) innodb_io_capacity isn't used to determine how many LRU tail page flushes to do

How to repeat:
read my wiki post

Suggested fix:
1) bring back furious flushing
2) sleep for at most 100ms rather than 1s -- too much can change in one second
3) use innodb_io_capacity to determine how many LRU tail pages to flush
[25 Feb 2013 3:34] Mark Callaghan
This is the common call stack when foreground threads get stuck. It isn't efficient to have a foreground thread do fil_flush_file_spaces after flushing 1 page. It is better to amortize that over several page flushes.

select,os_thread_sleep,fil_flush,fil_flush_file_spaces,buf_flush_sync_datafiles,buf_flush_single_page_from_LRU,buf_LRU_get_free_block,buf_page_init_for_read,buf_read_page_low,buf_read_page,buf_page_get_gen,btr_cur_search_to_nth_level,btr_pcur_open_with_no_init_func,row_search_for_mysql,ha_innobase::index_read,handler::read_range_first,handler::multi_range_read_next,QUICK_RANGE_SELECT::get_next,rr_quick,mysql_update,mysql_execute_command,mysql_parse,mysql_parse,dispatch_command,do_handle_one_connection,handle_one_connection,start_thread,clone
[28 Sep 2013 9:49] Laurynas Biveinis
+1 on returning furious flush list flushing, and we've seen good results in allowing page cleaner to violate innodb_io_capacity and innodb_lru_scan_depth when needed because of the checkpoint age or empty free lists.
[11 Nov 2013 13:48] James Day
Related Bug #70500 "Page cleaner should perform LRU flushing regardless of server activity" reporting what are believed to be "Sysbench performance instabilities".

James Day, MySQL Senior Principal Support Engineer, Oracle
[10 Mar 2014 12:53] Laurynas Biveinis
Related bug 71988.
[4 Nov 2014 11:47] Laurynas Biveinis
Related bug 74637