MySQL Log Settings Optimization

Linux community (www.linuxidc.com) is a professional Linux System Portal website that publishes the latest Linux information in real time, including Linux, Ubuntu, Fedora, RedHat, Red Flag Linux, Linux tutorial, Linux certification, SUSE

Linux community (www.linuxidc.com) is a professional Linux System Portal website that publishes the latest Linux information in real time, including Linux, Ubuntu, Fedora, RedHat, Red Flag Linux, Linux tutorial, Linux certification, SUSE

Preface

After installing MySQL, you must optimize and adjust various MySQL Parameter options. Although the MySQL system is highly scalable, it can run efficiently in an environment with sufficient hardware resources or in a few resource environments. However, as many hardware resources as possible will always help improve MySQL performance. In this section, we mainly analyze the impact of MySQL logs (mainly Binlog) on system performance, and draw the corresponding optimization ideas based on the characteristics of logs.

Performance impact of logs

The direct performance loss caused by log records is the most expensive IO resource in the database system.

In the previous chapter about MySQL physical architecture, we have learned that MySQL logs include error logs (ErrorLog), update logs (UpdateLog), binary logs (Binlog ), query log and SlowQueryLog. Of course, the update log is only available in the old version of MySQL and has been replaced by binary logs.

By default, the system only opens error logs and closes all other logs to minimize IO consumption and improve system performance. However, in actual application scenarios that are a little more important, you must at least enable the binary log, because this is the basis for many MySQL storage engines to perform Incremental backup and is also the basic condition for MySQL to implement replication. Sometimes, to further optimize the performance, locate slow SQL statements. Many systems also open slow query logs to record SQL statements whose execution time exceeds a specific value (set by ourselves.

Generally, query logs are rarely opened in the production system. Because after the Query log is opened, every Query executed in MySQL is recorded in the log, this system will bring a large IO burden, but the actual benefits are not very large. Generally, this log can be opened for analysis in a short period of time only when some functions use SQL statements in the development and testing environment. Therefore, in the MySQL system, MySQL logs that affect the performance (excluding the logs of each storage engine) are mainly binlogs.

Binlog parameters and Optimization Strategies

First, let's take a look at the Binlog parameters. You can obtain the Binlog parameters by executing the following command. Of course, the innodb_locks_unsafe_for_binlog parameter unique to the Innodb Storage engine is also displayed:

Mysql> show variables like + ------------------------------ + ------------ + | Variable_name | Value | + ------------------------------ + ------------ +
| Bin log_cache_size | 1048576 |
| Innodb_locks_unsafe_for_binlog | OFF |
| Max_binlog_cache_size | 4294967295 |
| Max_binlog_size | 1073741824 |
| Sync_binlog | 0 |
+ -------------------------------- + ------------ +

"Binlog_cache_size": the cache size of the binary log SQL statement during the transaction process. The binary log cache is the memory allocated to each client on the premise that the server supports the transaction storage engine and the server enables the binary log (-log-bin option). Note, is that each Client can allocate binlogcache space of the set size. If the reader's friend's system often shows the trend of Multi-statement transactions, you can try to increase the value to achieve better performance. Of course, we can use the following two state variables of MySQL to determine the current status of binlog_cache_size: Binlog_cache_use and Binlog_cache_disk_use.

"Max_binlog_cache_size": corresponds to "binlog_cache_size", but represents the maximum cache memory size that binlog can use. When we execute Multi-statement transactions, if max_binlog_cache_size is not large enough, the system may report the error "Multi-statementtransactionrequiredmorethan 'max _ binlog_cache_size 'bytesofstorage.

"Max_binlog_size": maximum value of Binlog logs. Generally, it is set to m or 1G, but cannot exceed 1G. This size does not strictly control the Binlog size. Especially when a large transaction arrives near the end of the Binlog, the system ensures the integrity of the transaction, it is impossible to switch logs. You can only record all SQL statements of the transaction into the current log until the transaction ends. This is a little different from the Redo log of Oracle, because the Redo log of Oracle records changes in the physical location of the data file and records the Redo and Undo information at the same time, therefore, whether a transaction is in a log is not critical to Oracle. MySQL records database logic changes in the Binlog. MySQL calls this Event as a Query statement such as DML that brings database changes.

"Sync_binlog": this parameter is crucial for the MySQL system. It not only affects the performance loss caused by Binlog on MySQL, but also affects the data integrity in MySQL. The settings of the "sync_binlog" parameter are described as follows:

Sync_binlog = 0. After the transaction is committed, MySQL does not perform Disk Synchronization commands such as fsync to refresh the information in binlog_cache to the disk, and Filesystem determines when to synchronize the data, or, after the cache is full, it is synchronized to the disk.

Sync_binlog = n. After each n transaction commits, MySQL runs a Disk Synchronization command such as fsync to forcibly write data in binlog_cache to the disk.

In MySQL, sync_binlog is set to 0 by default, that is, no mandatory disk refresh command is performed. At this time, the performance is the best, but the risk is also the biggest. Because once the system Crash, all binlog information in binlog_cache will be lost. When it is set to "1", it is the safest but the biggest loss of performance. When set to 1, even if the system Crash is used, a transaction not completed in binlog_cache can be lost at most, without any material impact on the actual data. From past experience and related tests, for high-concurrency transaction systems, the system write performance gap between "sync_binlog" and "1" may be as high as 5 times or more.