INNODB_LOG_FILE_SIZE: change the size of the MySQL redo log files

by ·

What is the InnoDB log?

Have you ever used the undo or redo function in a text editor, imagine the operation of the editor in that scenario? I am sure you should have used it. Do you believe it? Transactional databases have the same function. It may not be exactly the same, but the principle is the same.

MySQL Server

Just as you always have the ability to undo the importance of several steps when editing text, the redo and undo functions are just as important for transaction data. Why? There are two main reasons:

  • Rollback the transaction data (that is undo)
  • Replay the committed transmission in case of database crash (that is redo)

Undo

When you use a transactional storage engine (assuming 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 changed the memory-InnoDB buffer pool. The new InnoDB page now contains the changed records called dirty data.

The original unaltered page copied to a special area on the hard disk is called the rollback segment. Since the changes have not been written to the data file, this operation is quite simple, InnoDB only needs to extract the old pages from the rollback segment, erase the dirty pages from memory, and mark in the transaction log that the transaction has been rolled back. So you see, the data file has never been changed because you have canceled all the changes before performing random write operations to refresh the dirty data to the hard disk.

Redo

When you commit a transaction, then InnoDB confirms your commit and the changes are ready to be written to the actual data file.

Now you think they will be written to the data file of the hard disk immediately, which is not the case in fact. Why? This is very inefficient. Instead, the changes are only written to the transaction log (because it is written sequentially, the speed will be very fast, called redo log records), and the record of the changes is still in the log-dirty pages of the InnoDB buffer pool, after a certain amount of time, it will refresh to the hard disk.

At this time MySQL crashed! Guess what MySQL will do?
If MySQL (actually InnoDB) does not have a redo log, it simply keeps dirty pages in memory-all committed transactions that have not been flushed to the hard disk will be permanently lost. Fortunately, all changes are always written to the transaction log, so what InnoDB needs to do is to find the last checkpoint (the location of the synchronized data to the hard disk) in the redo log, and then redo the transaction that has not been synchronized to the hard disk.

Log size

One thing you may want to know is how to set the size of innodb_log_file_size correctly. The rules are simple:

  • Small log files make writing slower and crash recovery faster
  • Large log files make writing faster and crash recovery slower

Since the transaction log is equivalent to a write buffer, and the small log file will be filled quickly, at this time, it needs to be refreshed to the hard disk frequently, and the speed is slow. If a large number of write operations occur, MySQL may not be able to refresh the data fast enough, so the write performance will be reduced.

Large log files, on the other hand, give you enough space to use before the refresh operation takes place. This allows InnoDB to fill more pages.

For crash recovery, a large redo log means that more data needs to be read before the server starts, and more changes need to be redone, which is why crash recovery is slow.

Redo log size

Fortunately, you don’t have to work hard to figure out the correct size, here is a rule of thumb:
During the busy period of the server, check whether the total size of the redo log is enough to write for 1-2 hours. How do you know how much InnoDB writes, there is a method below:

[pastacode lang=”markup” message=”” highlight=”” provider=”manual”]

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)

[/pastacode]

In this 60s sampling situation, InnoDB writes 2.6GB of data per hour. So if innodb_log_files_in_group has not changed (the default is 2, which is the smallest number of InnoDB repeated logs), and then set innodb_log_file_size to 2560M, then you actually have 5GB of two log files combined, enough for you to write two hours of data.

Change redo log size

The difficulty of changing innodb_log_file_size and how big it can be set depending on the MySQL version you are using. Here are the steps:

  1. Change innodb_log_file_size in my.cnf
  2. Stop mysql server
  3. Delete the old log by executing the command rm -f /var/lib/mysql/ib_logfile*
  4. Start the mysql server. It should take longer than before because you need to create a new transaction log.

Tags: