mysql : understanding innodb log operations

Question

I am trying to understand these 3 variables when it comes to writing changes to the binary log in the innodb storage engine

sync_binlog

innodb_flush_log_at_trx_commit

innodb_flush_log_at_timeout

After reading the documentation several times over here is what my understanding of these variables are

sync_binlog

0  -> Operating system decides when to write log to disk(whenever it has free time)
1  -> after every commit changes written to disk(safest)
N  -> after N commits changes written to disk

innodb_flush_log_at_trx_commit

0 -> written and flushed to disk once per second
1 -> written and flushed after every commit(safest)
2 -> written after every commit and flushed to disk once per second

innodb_flush_log_at_timeout

after N seconds of delay logs are written to file

So as an experiment here is what i want to acheive

1)No transactions should be written to log until 5 transactions have been commited

2)After 5 transaction's have been commited i want innodb to wait for 10 seconds before these transactions are written to log

So i have set the variable's as follows

SET @@GLOBAL.sync_binlog=5;                          #Don't write anything until 5 commits have been done?
SET @@GLOBAL.innodb_flush_log_at_trx_commit=0;       #Maybe? because logs are written every second regardless if they are commited or not?
SET @@GLOBAL.innodb_flush_log_at_timeout=10;         #After 5 commit's wait for 10 seconds before writing the transaction's to disk?

As you can see i have put question marks everywhere because i have no idea what i'm doing. And these settings don't work. After every commit i see the changes updated in the log instantly

Basically i have an test table

CREATE TABLE Test(TestCol INT);

I issue 5 transaction's[auto commit is on] as 5 insert statements

INSERT INTO Test VALUES(1);
INSERT INTO Test VALUES(2);
INSERT INTO Test VALUES(3);
INSERT INTO Test VALUES(4);
INSERT INTO Test VALUES(5);

What i want is if I insert values let's say only 1 to 4 then I don't want to see any changes in the log file but as soon as i insert 5 as well which makes a total of 5 transctions then i want to make innodb wait for 10 seconds before writing those transactions to log so i can have enough time to open my file explorer and run

mysqlbinlog --verbose --base64-output=DECODE-ROWS binlog.000001

to see the changes in my log file

But even if i insert just one value and open my log file i see that insert instantly in there.

Is my objective even possible? Can someone explain me how these variables work and if possible an alternative solution to my problem?

Any clarity would be greatly appreciated

Answer 1

The sync_binlog only controls when the file is synced to disk. The transactions are still written to the logical file immediately when you commit a transaction, they're just buffered by the OS. If you read that same file with a program (e.g. mysqlbinlog), this reads all the content, including what is in the OS buffer. The OS merges the buffer with the content on disk automatically.

I don't know of any way to make MySQL delay writing to the binary log on commit. There's a binlog buffer, but it's for accumulating changes before the transaction is committed. Once you commit, it should write to the binlog immediately, because other sessions may also want to write to the binlog, and the commits should be written in the order of commit time.

Likewise, writes to the innodb redo log are continuous. The flushing options you name only control when the file is synced to disk. In the meantime, writes to the log file are still happening, but may be buffered by the OS. The OS may choose to flush the buffered I/O before innodb forces it to sync.

I don't think you can do what you are trying to do. I don't know why you would want to, since it would spoil durability. Suppose you could make InnoDB not save transactions to the redo log until 5 transactions have committed. If you commit 4 of those transactions and have a sudden crash, then you would lose those 4 transactions.

---

Re your comment:

https://bugs.mysql.com/bug.php?id=69309 has more explanation:

Write and flush the logs every N seconds. innodb_flush_log_at_timeout was introduced in MySQL 5.6.6. It allows the timeout period between flushes to be increased in order to reduce flushing and avoid impacting performance of binary log group commit. Prior to MySQL 5.6.6, flushing frequency was once per second. The default setting for innodb_flush_log_at_timeout is also once per second.

The bug log claims this explanation will be added to the documentation, but I guess it never made it in.

It sounds like this option is meant to allow for reduced durability if your rate of transactions is so high that syncing the log file is a bottleneck. See https://dev.mysql.com/worklog/task/?id=5223

In my opinion, if your rate of transactions is so high that it can't keep up with fsync once per second, then you need to think about how to redesign your architecture. Even if you extend the intervals between fsyncs, that creates a risk for your data, and you can't keep extending that forever. You need a better strategy for managing a growing transaction rate. Usually this means splitting the database into "shards" so that each transaction writes to one of many servers.