mysql log buffer_MySQL: Innodb Log Buffer and REDO Logs

Actually, the definition of sys variable innodb_log_buffer_size was confusing me very much.

What actually this buffer is meant for?

Is it a part of Innodb buffer pool?

And after extensive googling(!) I got a clear picture of it.

Here's what I got:

No doubt, REDO logs are the unsung heroes. But, how exactly it's generated?

When any DML request comes in, the storage engine reads out the corresponding page from the disk into the memory(innodb buffer pool).

So, there are now 2 copies of this page: one is in  disk and the other in memory. The page in the memory, which is not yet flushed to disk is called Dirty Page.

For this dirty page, an redo log will be generated locally, in the mini transaction buffer. Which is very internal thing and not of our concern to worry!

This log is then transferred to the global redo log buffer. And, this redo log buffer size is decided by the variable innodb_log_buffer_size ! This is a separate memory taken from the host.

So, the redo logs in this buffer is written or flushed to redo log files on disk(iblog files) as soon as the buffer is full or a commit happens.

If we're performing long transactions, it's recommended to increase the size of this buffer to avoid the unnecessary I/Os.

The redo logs will be flushed to the disk before the corresponding dirty pages are flushed.

For a clear insight on how InnoDB works, go through the below link:

https://blogs.oracle.com/mysqlinnodb/entry/redo_logging_in_innodb