MySQL Bugs: #71169: add a trx_commit=3 mode, like Oracle's async commit mode

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:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S4 (Feature request)
Version:	All Version	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	innodb, performance

Description:
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:
N/A

Suggested fix:
Please check the attacked file.

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.

This is a proper feature request.

In which version the async commit will be applied?

In the same way, we could implement async binlog flush, to maximumly protect the binlog data lose.