MySQL Bugs: #68658: buf_flush_single_page_from_LRU causes stalls on fil

Bug #68658	buf_flush_single_page_from_LRU causes stalls on fil_flush
Submitted:	12 Mar 2013 23:16	Modified:	12 Apr 2013 16:23
Reporter:	Mark Callaghan	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S5 (Performance)
Version:	5.6.10	OS:	Any
Assigned to:	Inaam Rana	CPU Architecture:	Any

Description:
Back in the day, threads running user transactions (foreground threads) within innodb could all do bulk flushing of dirty pages from the LRU list when there weren't enough clean pages at the LRU end.  See buf_flush_free_margin().

With 5.6 the page cleaner thread is expected to do most of the work and only when it falls behind will foreground threads call buf_flush_single_page_from_LRU which won't flush a large number of pages (perhaps 1) but will do an fsync for all tablespaces via calls to fil_flush. Given that this thread should just be flushing pages for a single tablespace it shouldn't have to sync all tablespaces. Alas, it calls buf_flush_sync_datafiles to do just that -- which calls fil_flush_file_spaces.

How to repeat:
read the source
run update-intensive benchmarks

Suggested fix:
Provide an option to only sync one tablespace.

edit title

This is fixed due to another bug fix which is currently pushed to 5.6.12.
Oracle bug number 16477781 , which states:

In cases where threads are forced to do single page flushing, "fsync()" would
be triggered for all data files. This fix allows for synchronous single page
flushing.

5.6$ bzr log -r 4955
------------------------------------------------------------
revno: 4955
committer: Inaam Rana <inaam.rana@oracle.com>
branch nick: mysql-5.6
timestamp: Mon 2013-03-25 17:27:44 -0400
message:
  Bug#16477781 SINGLE PAGE FLUSHING CAUSES FSYNC() OF ALL DATAFILES
  
  Approved by: Kevin Lewis rb://2174
  
  In cases where threads are forced to do single page flushing we call
  buf_flush_sync_datafiles() after queueing the IO asynchronously.
  buf_flush_sync_datafiles() wakes up IO helper threads, then waits for
  all the IO requests to be serviced and then trigger an fsync() on all
  data files.  The minimal we need is that the IO request for the given
  page has been serviced and the data file that it belongs to have been
  synced to the disk.
  
  * Explicitly pass 'sync' value to buf_flush_page(). The new scheme is:
    -- All batches happen asynchronously (same as before)
    -- Single page flushes to find a victim are synchronous
    -- Single page flushes for export are asynchronous
  * Flush only the datafile that is touched during the single page flushing
  * Change handling of single page flush dblwr slot to IO completion
  routine i.e., now it will happen in IO helper thread in case of async
  request
  * Remove polling from dblwr buffer. Introduce two separate events,
  one for batch flushing and one for single page flushing