Bug #81680 A proposal to make disk IO much faster under SSD
Submitted: 1 Jun 2016 13:33 Modified: 1 Jun 2016 17:59
Reporter: Yang Sun Email Updates:
Status: Verified Impact on me:
Category:MySQL Server: InnoDB storage engine Severity:S4 (Feature request)
Version:5.7 OS:Any (CentOS Linux release 7.2.1511 (Core) )
Assigned to: CPU Architecture:Any
Tags: directio, performance, SSD

[1 Jun 2016 13:33] Yang Sun
For a local SSD, I think a much better way is: directIO for all heavy files and don’t invoke fsync. But I searched the mysql doc, there is no such option.
PS: without fsync, data maybe lost during a power failure due to internal cache of SSD. However that’s not a problem for industry level SSD(there is a capacitor ensuring data persistence). 

The performance of mysql under SSD is not as good as expected. 
My test result is one "insert + commit" operation costs 3~4 ms. The mainly cost is commit operation which trigger binary log sync.

The disk IO pattern of mysql:
     Innodb data files: one direct write for each insert/commit, fsync once per second;
     Innodb log files: one buffered write for each insert/commit, fsync once per second;
     Binary log files: one buffered write for each insert/commit, fsync for each commit;
Am I missing something or there is a reason for that?

How to repeat:
A local SSD

[myslq configuration]
innodb_flush_method = O_DIRECT
innodb_flush_log_at_trx_commit = 2
sync_binlog = 1
binlog_format = statement
default-storage-engine = innodb
innodb_data_file_path = ibdata1:10M:autoextend

10 columns, each column is a 128bytes random string.

A python client based on unix sock;
Insert a row, then commit it; 
After last commit succeed, insert a new row and commit;
Repeated in that way....

And client and mysql is running on the same machine.

Suggested fix:
DirectIO and not invoke fsync.
[1 Jun 2016 17:59] MySQL Verification Team
This is a feature request that could be considered.