Shulou(Shulou.com)06/01 Report--

Today, we mainly study the setting of parameters related to mysql's redo log (transaction log), which mainly involves three parameters: one: innodb_log_file_size: this parameter determines the size of the mysql transaction log file (ib_logfile0). The setting is too small: when a log file is full, innodb will automatically switch to another log file and trigger a database Checkpoint, which will cause a small batch refresh of dirty pages in the innodb cache, which will significantly reduce the performance of innodb. Because log switching is more frequent, it directly leads to more BUFFER FLUSH, and because log switching cannot BUFFER FLUSH and BUFFER cannot write down, resulting in no extra buffer to write redo, then the whole MYSQL lives in HANG. Another situation is that if there is a large transaction and all log files are full and not finished, the log cannot be switched (because instance recovery still needs to be done). Can't be copied in a loop) so mysql lives in hang. The rotation frequency of the log file can be determined according to the file modification time. The rotation frequency is too frequent, indicating that the log file is too small. The setting is too large: the checkpoint is reduced after a large setting, and the performance of redo log is greatly improved because the redo log is a sequential Imax O. But if there is an unexpected problem with the database, such as an unexpected downtime, then you need to replay the log and recover the committed transaction (that is, roll forward in the instance recovery, using redo to recover the data in the buffer cache from the change). If the log is very large, it will take a long time to recover. Even to the point where we can't accept it. If there are a large number of writes to the Innodb datasheet, then choosing the appropriate innodb_log_file_size value is important to improve MySQL performance, how to set the appropriate innodb_log_file_size size? In general, the full size of the log file should be large enough to hold the active content of the server for an hour. The details are as follows: I often set it to 64-512MB, first calculate the amount of redo written in one minute during peak business periods, and then evaluate the amount of redo written in one hour; MariaDB [(none)] > pager grep Log # # after using page, the command executed only shows PAGER set to 'grep Log' MariaDB [(none)] > show engine innodb status\ G select sleep (60) at the beginning of Log; show engine innodb status\ G; Log sequence number 45780590533 Log flushed up to 1149269980141row in set (0.00 sec)

1 row in set (1 min 0.00 sec)

Log sequence number 4578062739081 Log flushed up to 1149270019005 1 row in set (0.00 sec) MariaDB [(none)] > nopager PAGER set to stdout MariaDB [(none)] > select (4578062739081-4578059050533) / 1024 as MB; +-+ | MB | +-+ | 3.51767349 | +-+ 1 row in set (0.00 sec) Note Log sequence number, which is the total number of bytes written to the transaction log. So now you can see how many MB logs are written per minute (the technique here applies to all versions of MySQL, and in version 5.0 and later, you can see the value of Innodb_os_log_written from the output of SHOW GLOBAL STATUS). It is calculated that there are 3.5m log writes per minute. According to the rule of thumb. Usually we set the redo log size to be large enough to hold 1 hour of log writes. Log writes per hour = 3.5m * 60log 210m, because there are two log redo log files ib_logfile0 and ib_logfile1 by default. Each redo log file in the log group is of the same size and is written in a circular manner. The innodb storage engine first writes the redo log file 0, and when it reaches the end of the file, it switches to redo log 1 and checkpoint. In this cycle. So we can set up innodb_log_file_size=110M roughly. Note: prior to the innodb1.2.x version, the total size of the redo log file must not be greater than or equal to 4G, while the 1.2.x version extended this limit to 521G. Second: innodb_log_files_in_group this parameter controls the number of log files. The default value is 2. The mysql transaction log file is overwritten in a loop. It is important to note that innodb_log_files_in_group is a static variable that needs to be changed and restarted in a "clean" way, otherwise mysql will not start. That is to say, if you want to change the original 2 to 3, then you need to close the mysql service, delete the original ib_logfile0 and ib_logfile1 files, and then start mysql, otherwise the error report is as follows: restart mysql when you directly modify my.cnf to change the parameter to 3, and report an error. The innodb engine failed to mount 110124 14:06:23 InnoDB: Log file. / ib_logfile2 did not exist: new to be created 110124 14:06:23 [ERROR] Plugin 'InnoDB' init function returned error. 110124 14:06:23 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed. Three: innodb_log_buffer_size this parameter ensures that there is a large enough log buffer to hold dirty data before it is written to the log file. For smaller innodb_buffer_pool_size, it is recommended to set it to the same size. However, for larger innodb_buffer_pool_size, this setting is not recommended. There is a potential problem, that is, when mysql hangs, it takes a long time to recover data, resulting in a lot of downtime. When we adjust the size of innodb_buffer_pool_size, innodb_log_buffer_size and innodb_log_file_size should also be adjusted accordingly. Four: innodb_log_group_home_dir before the transaction is committed and written to the tablespace disk file, the transaction log is stored in the InnoDB redo log file. These logs are located in the directory set by the innodb_log_group_home_dir parameter, which is usually set to the same as the innodb_data_home_dir variable. For best performance, it is recommended to separate innodb_data_home_dir and innodb_log_group_home_dir into separate physical storage arrays, which ensures that IO resources do not conflict and facilitates the server to handle a large number of highly concurrent connections. Summary: the parameters related to the transaction log of mysql are basically introduced. The parameters that affect the performance of log refresh innodb_flush_log_at_trx_commit analyzes the meaning of innodb_flush_log_at_trx_commit=N in detail: innodb_flush_log_at_trx_commit=0. Every time commit, the transaction log is written into innodb log buffer, and then every second Log Thread will refresh the transaction log from innodb log buffer to ib_ogfile (that is, to disk). When innodb_flush_log_at_trx_commit is set to 0memmysqld process, the crash will result in the loss of all transaction data in the previous second. This is because every time commit, the transaction log is only written into innodb log buffer, and then the transaction log in innodb log buffer is flushed to disk for permanent storage every second. So when the mysqld process crashes, innodb log buffer may not refresh the log for one second, but in this case, MySQL performance is the best. Innodb_flush_log_at_trx_commit=2, every time commit, the transaction log is written to innodb log buffer and then to os cache, that is, every time commit, the transaction log is written to os cache, and then flushed from os cache to ib_logfile (that is, flushed to disk) every second. When innodb_flush_log_at_trx_commit is set to 2, only if the operating system crashes or the system is powered off, all transaction data can be lost in the last second. Because every time commit, the transaction log has already entered the os cache, so mysqld crashes and the transaction log will not be lost. When innodb_flush_log_at_trx_commit is set to 1, this is the safest setting, and the efficiency is the least due to frequent io operations. at this time, no matter it is mysqld or the operating system crashes, the data will not be lost, this is because every time commit, the transaction log is flushed to disk for permanent storage.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.