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

In this article, I'll give you some advice on how to set the innodb_log_file_size parameter for MySQL.

Like other database management systems, MySQL implements data persistence through logging (as long as the InnoDB storage engine is used). This ensures that when a transaction is committed, its related data will not be lost in the event of a crash or a power outage on the server.

MySQL's InnoDB storage engine uses a specified size of Redo log space (a circular data structure). The space of Redo log is adjusted by the innodb_log_file_size and innodb_log_files_in_group (default 2) parameters. Multiply these two parameters to get the total available Redo log space. Although it doesn't technically care whether you adjust the Redo log space through innodb_log_file_size or innodb_log_files_in_group, most of the time you adjust it through innodb_log_file_size.

Setting the appropriate Redo log space for the InnoDB engine is important for writing sensitive workloads. However, there is a trade-off to be made. The more Redo space you configure, the better InnoDB can optimize write operations; however, increasing Redo space also means longer recovery time in the event of an accident such as a crash or power outage.

With regard to recovery time, it is difficult to predict how long it will take to crash for a specified innodb_log_file_size value-it depends on factors such as hardware capabilities, MySQL version, and workload. However, in general, we can estimate the recovery time of Redo log per 1GB in about 5 minutes. If recovery time is important to your environment, I suggest you do some simulation tests to simulate a system crash under a normal workload (after preheating) to assess a more accurate recovery time.

Although recovery time can be used as a reference factor to limit innodb_log_file_size, there are other ways to see if the parameter setting is "reasonable" (especially if you have installed PMM: Percona Monitoring and Management)

Check Percona Monitoring and Management's "MySQL InnoDB Metrics" dashboard if you see the following image:

The Uncheckpointed Bytes in the figure is very close to Max Checkpoint Age, so you can almost be sure that the current innodb_log_file_size value has limited system performance to some extent because it is too small. Increasing this value can significantly improve the system performance.

And if you see something similar to the following:

In this figure, Uncheckpointed Bytes is much smaller than Max Checkpoint Age, and in this case, there will be no significant performance improvement by adding innodb_log_file_size.

Note: many MySQL settings are interrelated, and although a particular Redo log space may be sufficient for a smaller InnoDB Buffer Pool value, a larger InnoDB Buffer Pool value still expects a larger Redo log space for better performance.

Another thing to remember: the recovery time we talked about before depends on the Uncheckpointed Bytes rather than the total Redo log space. If you do not observe an increase in recovery time after adding innodb_log_file_size, it may be that the previous configuration is sufficient for your current workload, and your added space is not fully utilized.

Another way to observe innodb_log_file_size is the usage of Redo log space:

This picture shows the total amount of data written to the log file per hour and the value of innodb_log_file_size. In the image above, we have 2 gigabytes of Redo log space, but more than 12 gigabytes of data are written to the log file every hour. This means that the Redo space rotates about every ten minutes.

On the other hand, InnoDB flushes every dirty page in innodb buffer pool to disk every time it rotates Redo log space. The less this operation occurs, the better the InnoDB will perform (and the less wear and tear on the SSD hard drive). I would like to see the frequency of this operation at least once in 15 minutes, of course, the less the better.

With regard to the usage of Redo space, if PMM is not installed, you can also observe the number of writes per hour (MB) with the following command:

Grep $(mysql-uuser-paired passwdd'-e "show engine innodb status\ G" | grep "Log sequence number" | awk'{print $4}'); sleep 60; baked $(mysql-uuser-paired passwdd'-e "show engine innodb status\ G" | grep "Log sequence number" | awk'{print $4}'); let "res= ($bMobila) * 60bp 1024ap1024"; echo $res

Summary:

Setting the right innodb_log_file_file_size is important to balance performance and recovery time. But keep in mind that the recovery time in your scenario cannot be accurately estimated due to various factors. I hope the points discussed in this article will help you set up a more reasonable innodb_log_file_file_size.

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.