MySQL Bugs: #68481: InnoDB LRU flushing for MySQL 5.6 needs work

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

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

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

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

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

Related bug 71988.

Related bug 74637