InnoDB WAL learning and InnoDBWAL Learning

InnoDB WAL learning and InnoDBWAL Learning

I wrote a blog post titled bad MySQL process writing habits (http://www.cnblogs.com/wingsless/p/5041838.html ). This blog post emphasizes that you do not need to submit transactions cyclically. Try to submit SQL statements that can be put together in the same transaction as much as possible, which will be much faster. The redo issue is mentioned in the blog. Therefore, we will give a brief introduction to some InnoDB and Redo tasks based on the sentiment of preparing new employee training materials recently.

InnoDB memory includes redo log buffer, and redo log file on the disk. redo is used to restore data after a crash to ensure data persistence.

In general, the most ACID-compliant redo method should be like this: when a transaction is committed, the redo buffer content in the memory is written into the file and flushed back to the disk (flush, this action is to flush the data cached on the disk back to the file ). In this case, the data block in the buffer pool is modified to a dirty page, but is not written back to the disk. Instead, it is written back slowly in the master thread loop, in this way, the log writing and data file writing are not synchronized, and there will be a certain time difference between the two. This method is called the pre-write log (WAL ). Once the system goes down, data in the memory will be lost immediately. When you start the database next time, data that has not been written back to the data file can be recovered from the redo log.

Therefore, the process in the previous blog post will write a log when each transaction is committed, which will lead to a lot of disk IO, so the efficiency is very low, and a single transaction commit will only cause one IO, therefore, the efficiency is greatly improved. However, InnoDB also supports another mode, which is controlled by the innodb_flush_log_at_trx_commit parameter. The default value is 1, which indicates the method mentioned above. redo logs are written every time a transaction is committed.

In a test, I still kept the Stored Procedure submitting transactions cyclically, But I adjusted the parameter to 2, which significantly improved the efficiency, slightly slower than submitting a single transaction. The parameter 2 indicates that log files are also written each time a transaction is committed, but logs are not flushed back to the disk by calling the fsync function (Disk flushing function, instead, the fsync function is scheduled once a second. Therefore, it will also bring about a lot of efficiency improvements. The problem is obvious. If a machine goes down, it will lose about one second of data.

Of course, this parameter can be adjusted to 0, which means that no operation is performed when the transaction is committed. The redo buffer data is written into the log and flushed back to the disk every second. This method seems much faster, but it is the least ACID compliant practice.

Of course, disk flushing involves some other parameters, which will not be discussed in this article. I will write it down if I have any learning experience in the future.

In fact, if you do not care about the loss of data in one second (sometimes there are a lot of data in one second), you can set this parameter to 2, but it is best to set it to 1. When writing data to a database, the program can submit data in batches at a very fast speed. This is a matter of programming, and it is no longer under discussion.