Innodb_log_file_size: Set the MySQL redo log size

What is the InnoDB transaction log

Have you ever used undo or redo functionality in a text editor and imagine the editor's actions in that scenario? I'm sure you should have used it. Do you believe it? transactional databases have the same functionality. May not be exactly the same, but the principle is the same.
Just as you are always able to undo the importance of several steps when editing text, redo and undo functions are as important as transactional data. Why, then? There are two main reasons:
1. Rollback TRANSACTION (that is undo)
2. Put the committed transaction back in case of a database crash (that is redo)

Undo

When you are using the transaction storage engine (assuming it is InnoDB), when you change a record, the change is not immediately written to the data file.
First, they are written to a specific file on a hard disk called the transaction log. At the same time, they also change the memory –innodb buffer pool. The new InnoDB page now contains the changed records called Dirty data.
The original unchanged page that was copied to the special area on the hard disk is called a rollback segment.
If someone interrupts a transaction using rollback before committing, the undo action occurs – your record has been restored to its original state.
Since the change has not yet been written to the data file, this operation is fairly simple –innodb only need to extract the old page from the rollback segment, erase the dirty page from memory, and mark the transaction in the transaction log as rolled back.
So you see, the data file has never been changed since you have canceled all the changes before you perform a random write to flush the dirty data to the hard disk.

Redo

When you submit the transaction and then InnoDB confirm your submission, the change is ready to be written to the actual data file.
Now you think they're going to be written to the hard drive's data file, which is actually not the case. Why? Because it's very inefficient. Instead, the changes are written only to the transaction log (because it is sequential, fast, called redo logging), and the changed records are still –innodb the buffer pool's dirty pages in the log, and are refreshed to the hard disk at a certain time.
This time MySQL crashed!
Guess what MySQL would do?
If MySQL (actually InnoDB) does not redo the log, it simply retains the dirty page in memory – All transactions that have not been flushed to the hard disk have been committed will be permanently lost.
Fortunately, the changes are always written to the transaction log,
So what InnoDB need to do is find the last checkpoint in the redo log (the location where the data has been synchronized to the hard disk), and then redo the transactions that were not synchronized to the hard drive.

Log size

One thing you might want to know is how to properly set the size of the innodb_log_file_size. The rules are simple:
* Small log files make writing slower and crash-recover faster
* Large log files make writing faster, crash recovery slower
Because the transaction log is equivalent to a write buffer, and the small log file is quickly filled, it needs to be refreshed frequently to the hard drive, which slows down. If a large number of writes are generated, MySQL may not be able to refresh the data fast enough, so write performance will be reduced.
Large log files, on the other hand, give you enough space to use before the refresh operation occurs. In turn allows InnoDB to populate more pages.
For crash recovery – a large redo log means that more data needs to be read before the server is started, and more changes need to be done, which is why the crash recovery is slow.

Redo Log Size

Finally, let's talk about how to find the correct size of the redo log.
Luckily, you don't have to work out the right size, here's a rule of thumb:
During server busy times, check that the total size of the redo log is sufficient to write to 1-2 hours.
How do you know how much InnoDB writes, here's a method:

Mysql> Pager grep seq
Mysql> Show engine InnoDB status\g Select Sleep (60); Show Engine InnoDB Status\g
Log Sequence Number 1777308180429
...
Log Sequence Number 1777354541591

Mysql> Nopager
Mysql> Select (1777354541591-1777308180429) *60/1024/1024;
+--------------------------------------------+
| (1777354541591-1777308180429) *60/1024/1024 |
+--------------------------------------------+
| 2652.80696869 |
+--------------------------------------------+
1 row in Set (0.00 sec)
In this 60s sampling case, InnoDB writes 2.6GB of data per hour. So if Innodb_log_files_in_group doesn't change (the default is 2, the minimum number of InnoDB repeats), and then set the Innodb_log_file_size to 2560M, then you actually have two log files plus 5GB, It's enough for you to write two hours of data.

Change Redo log size

The ease with which the

changes the innodb_log_file_size and how much you can set depends on the version of MySQL you are currently using.
Specifically, if you're using a version earlier than 5.6, you can't just change the variable and expect the server to restart automatically.
OK, here's the steps:
1. Change Innodb_log_file_size
2 in my.cnf to stop MySQL server
3. Deletes the old log, and executes the command rm-f/var/lib/mysql/ib_ Logfile*
4. Start the MySQL server – it should take longer than the previous time because a new transaction log needs to be created.
Finally, it is important to note that some versions of MySQL (such as 5.6.2) limit the redo log size to 4GB. So when you set Innodb_log_file_size to 2G or more, please check the MySQL version of this limitation.