Bug #75962 Innodb calls fsync when freeing a LRU page to fulfill page request
Submitted: 19 Feb 2015 3:14 Modified: 3 Mar 2015 20:46
Reporter: Tianzhou Chen Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:5.6.24, 5.7.6 OS:Any
Assigned to: CPU Architecture:Any
Tags: fsync, innodb, performance
Triage: Needs Triage: D5 (Feature request)

[19 Feb 2015 3:14] Tianzhou Chen
Description:
When storage engine requests a page from the buffer pool and the buffer pool has to free a LRU page to fulfill this request, flushing the buffer to OS should be enough, fsync is not needed.

Looking at the mysql55 code, there is no fsync in this case:
http://bazaar.launchpad.net/~mysql/mysql-server/5.5/view/4736/storage/innobase/buf/buf0flu...

In mysql56, the fsync is introduced at this revision:
http://bazaar.launchpad.net/~mysql/mysql-server/5.6/revision/3402.50.80

How to repeat:
N/A

Suggested fix:
According to the revision history introducing the fsync, it's used to address issue caused by simulated AIO, thus we should only apply the fix to simulated AIO. Mysql normally uses native IO nowadays.
[19 Feb 2015 3:16] Tianzhou Chen
fix typo
[27 Feb 2015 19:05] Sveta Smirnova
Thank you for the report.

Do you mean buf_flush_sync_datafiles call? Looks like it has been removed in latest versions:

[sveta@delly mysql-git]$ git branch
  mysql-5.5
* mysql-5.6
  mysql-trunk
[sveta@delly mysql-git]$ grep -R buf_flush_sync_datafiles storage/innobase/
[sveta@delly mysql-git]$ 

Which exact versionof 5.6 do you use?
[3 Mar 2015 6:07] Tianzhou Chen
I mean:

buf_flush_page(buf_pool, bpage, BUF_FLUSH_SINGLE_PAGE, true);

which is called from: buf_flush_single_page_from_LRU

The last parameter is "true" which does a fsync.

This should be avoided and as I put in the initial bug report. This changed is introduced to fix an issue in AIO, so at least, we should only apply this change to AIO.
[3 Mar 2015 20:46] Sveta Smirnova
Thank you for the feedback.

Verified as described using code review. Since buf_flush_page has assertion ut_ad(!sync || flush_type == BUF_FLUSH_SINGLE_PAGE); this can be considered as feature request only and verified only to bring attention of InnoDB Team for possible refactoring.