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

MySQL: REDO Logs and UNDO Logs

Most of the DBAs like me, haven't cared about the them..! But, they are the Unsung heroes of MySQL..!!

Yes, UNDO and REDO logs are the 2 different kind of logs; each with it's own purpose and you shouldn't get confused between them ..!!

As the name indicates, REDO logs are for Re-doing things..! 

They hold the transactions which were already executed, but not committed to the disks..! So, the REDO log records every transaction, holds until it gets committed and if needed, it will be used for crash recovery.

By default, there will be 2 REDO logs, named, ib_logfile0 and iblogfile1. 

As of version 5.6 or later, we can have up to 100 REDO logs using the System Variable  innodb_log_files_in_group . 

Since, these logs are overwritten as soon as all the files are full, it is advised to have more files or files with larger size. By default, each log file will be 5 MB and the size can be set using the system variable  innodb_log_file_size. 

The maximum allowed combined size of all the files is 512 GB(innodb_log_file_size * innodb_log_files_in_group <= 512 GB).

The REDO log files location can be set using the variable innodb_log_group_home_dir , if not specified, default directory will be used.

Let's talk about UNDO logs..:

This log stores copy of data that is being modified by any current transaction. So that, at the same time if any other transaction queries for the original data, this log will serve the purpose..!! 

These logs are also called Rollback Segments.

In the earlier versions, these logs were invisible as, UNDO logs were the part of System table space, i.e., ibdata1 and ibdata2. But, as of 5.6 and later, you can create multiple and separate UNDO log files(table space files).

Use variable innodb_undo_tablespaces  to set the number of table space files. Max value is 126 and minimum 0. 

The Undo logs set by the variable innodb_undo_logs will be divided between the set table spaces. Maximum you can create 128 undo logs and it is the default value.

The path of the UNDO log table space can be set by the variable innodb_undo_directory.

Linux: Solution for 'Not Able to Access Remote Server'

If you're playing with the Linux servers, one of the bothering issue is that you're not able to access other server from the current server

Or, MySQL replication or other service will not work even if you're able to ping from one server to the other or vice versa

Or, you may not be able to access the Tomcat service through your browser

Or, any other such connectivity issue, here is the solution that I used:

Just reset all the Firewall(Iptables) rules and you're done!


First get into SUDO and check the applied rules. It should be looking like below:

[root@myhost ]# iptables -L
Chain INPUT (policy ACCEPT)
target     prot opt source               destination
ACCEPT     all  --  anywhere             anywhere            state RELATED,ESTABLISHED
ACCEPT     icmp --  anywhere             anywhere
ACCEPT     all  --  anywhere             anywhere
ACCEPT     tcp  --  anywhere             anywhere            state NEW tcp dpt:ssh
REJECT     all  --  anywhere             anywhere            reject-with icmp-host-prohibited
ACCEPT     tcp  --  anywhere             anywhere            tcp dpt:opsession-prxy

Chain FORWARD (policy ACCEPT)
target     prot opt source               destination
REJECT     all  --  anywhere             anywhere            reject-with icmp-host-prohibited

Chain OUTPUT (policy ACCEPT)

target     prot opt source               destination



In my case, the highlighted line is the culprit! It just says to REJECT all incoming connections !

Either you can modify the rules by removing this line, or just like me, remove all the rules !!

Here is how to remove/flush  the firewall rules:



[root@myhost ]# iptables --flush


Yes, that's it!