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.
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)
When you use a transactional storage engine (assuming InnoDB), when you change a record, the change is not immediately written to the data file.
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.
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.
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
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:
Change innodb_log_file_size in my.cnf
Stop mysql server
Delete the old log by executing the command rm -f /var/lib/mysql/ib_logfile*
Start the mysql server. It should take longer than before because you need to create a new transaction log.