Mysql innodb Learning (2)

Mysql innodb (2) a previous article briefly introduced the role of innodbbufferpool_size. Next I will share with you the role of parameters related to innodb log files. The value www.2cto.com innodblogfile_size defines the size of the log file. The innodb log file is used to save redo logs. A transaction's changes to data or indexes usually correspond to random locations in the tablespace. Therefore, when these changes are refreshed to the disk, they will cause random I/O, random I/O is usually more expensive than sequential I/O because random I/O requires more overhead to locate the specified position. Innodb uses logs to convert random I/O to sequential I/O. As long as the log file is secure, the transaction is permanent, although these changes have not been written to the data file, if a server or server is powered off, innodb can use log files to restore and commit transactions. However, the log files are of a certain size. Therefore, you must write the changes recorded in the log files to the data files. innodb operates the log files cyclically, that is, when the log file is full, the pointer will be moved to the start of the file and re-written, but it will not overwrite the logs that have not been written to the data file, this is the only transaction persistence record. Innodb uses a background thread to refresh records in logs to data files. This thread can combine write operations to convert them into sequential I/O to improve efficiency. Innodb can set multiple log files, and the total size of log files is the total size of all log files. innodb processes these log files as one log file, when a log file is full, subsequent records are written to the next log file. When all log files are full, innodb writes data from the beginning.

The default log file in mysql is 2 5 m files, which is far from enough when the pressure is high. How can we modify the log file size? Before restarting the mysql service, delete the log file, modify the log file size in the configuration file, and then perform the restart operation, mysql will recreate a log file of the specified size. The log files of innodb are also stored in the disk, so the write speed is relatively slow. innodb uses the log cache to increase the write speed. Innodb will first write all the logs to the log cache, And then refresh these logs to the log files on the disk through a background thread. So when did innodb refresh the cache to the log file? Innodb has a configuration parameter to control this behavior. innodbflushlogattrx_commit can be set to three different values: 0 does not perform any operation when the transaction is committed, the background thread of innodb performs the refresh operation once per second. Every commit of transaction 1, innodb refreshes the data in the log cache to the log file (this is the default setting of innodb in mysql, it is also the safest setting.) 2. innodb refresh the data in the log cache to the disk for every commit of a transaction, but do not perform the flush operation (that is, only write to the cache of the operating system). The difference between this value and 0 is, this operation will not lose any transactions in case of service crash and other exceptions, but there is another problem: how does innodb refresh the log cache to the log file? This question will be shared with you in the next article.