Bug #71169 add a trx_commit=3 mode, like Oracle's async commit mode
Submitted: 18 Dec 2013 10:03 Modified: 24 Dec 2013 7:09
Reporter: Fangxin Flou (OCA) Email Updates:
Status: Verified Impact on me:
Category:MySQL Server: InnoDB storage engine Severity:S4 (Feature request)
Version:All Version OS:Any
Assigned to: CPU Architecture:Any
Tags: innodb, performance

[18 Dec 2013 10:03] Fangxin Flou
if we set trx_commit to 2, we will get a much higher performance, but we could lost 1 second data, if we set trx_commit to 1, the performance is not good enough. So I will suggest to add a new trx_commit mode.

How it works:

We start a dedicate thread for log flush work, it will check flushed lsn with the current lsn, if flushed lsn less than current lsn, then perform a log flush, else wait on an os event.

if use commit a transaction, it will check whether the dedicated log flush thread is working, if not, just set the os event to wake up the dedicate thread.

We could get about 50% tps improve compared to trx_commit=1, but with less data loss.

How to repeat:

Suggested fix:
Please check the attacked file.
[19 Dec 2013 14:02] Fangxin Flou
After one day's testing, I will make the following change.

1, trx_commit=0, add a log_async write call, but did not flush to disk.
2, add trx_commit=3 mode, write and flush the log in an async call.
[19 Dec 2013 14:08] MySQL Verification Team
This is a proper feature request.
[21 Dec 2013 14:06] Fangxin Flou
In which version the async commit will be applied?
[24 Dec 2013 7:09] Fangxin Flou
In the same way, we could implement async binlog flush, to maximumly protect the binlog data lose.