MySQL Log Settings Optimization

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'% BINLOG %';+ ------------------------------ + ------------ + | Variable_name | value | + ---------------------------------- + ------------ +
| Binlog_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.

 

As we all know, MySQL replication actually copies the BINLOG of the master end to the slave end through the network using the IO thread, then, the BINLOG logs are parsed by the SQL thread and then applied to the database. Therefore, the size of BINLOG has a direct impact on the I/O thread and the network between the msater and slave.

The generation of BINLOG in MySQL cannot be changed. As long as our query changes the data in the database, the event corresponding to the query must be recorded in the BINLOG. Is there no way to optimize replication? Of course not. In the MySQL replication environment, there are actually eight parameters that allow us to control the DB or table that needs to be copied or ignored but not copied, respectively:

Binlog_do_db: set which databases (Schemas) need to record BINLOG;

Binlog_ignore_db: set which databases (schema) do not record BINLOG;

Replicate_do_db: Specifies the database to be copied (schema). Multiple databases are separated by commas;

Replicate_ignore_db: Set the database that can be ignored (schema );

Replicate_do_table: Specifies the table to be copied;

Replicate_ignore_table: sets the table that can be ignored;

Replicate_wild_do_table: The function is the same as replicate_do_table, but can be set with wildcards;

Replicate_wild_ignore_table: this function is the same as replicate_ignore_table and can be set with wildcards;

 

With the above eight parameters, we can easily control as few binlogs from the master end to the slave end as possible according to actual needs, thus reducing the network traffic from the master end to the slave end, this reduces the I/O workload of I/O threads, reduces the number of SQL thread parsing and application SQL, and ultimately improves the data latency on slave.

In fact, the first two of the above eight parameters are set on the master side, while the last six parameters are set on the slave side. Although the first two parameters and the last six parameters have no direct functional relationship, similar functions can be enabled for optimizing MySQL replication. Of course there are also some differences, the main differences are as follows:

If you set the first two parameters on the master end, it will not only reduce the IO volume caused by BINLOG records on the master end, but also reduce the read volume of BINLOG by the IO thread on the master end, the amount of binlogs passed to the IO thread on the slave side is naturally small. This can reduce network I/O, reduce the I/O workload of the slave I/O thread, reduce the workload of the slave SQL thread, and optimize the replication performance to the maximum extent. Of course, the settings on the master end also have some drawbacks, because MySQL's judgment on whether to copy an event is not based on the data changed by the query that generates the event

 

The dB is based on the default schema of the query execution time, that is, the database specified when we log on or run the database specified in "usedatabase. The IO thread will read the event to the slave Io thread only when the default dB exactly matches the DB set in the configuration. Therefore, if the data of a table in the database to be copied is changed when the default database and the dB to be copied are different in the system, this event will not be copied to the slave, which will cause inconsistency between the data on the slave end and the data on the master node. Similarly, if the data in the schema that does not need to be copied is changed under the default schema, the data will be copied to the slave end. When the slave end does not have the schema, this will cause a replication error and stop.

If you set the following six parameters on the slave end, the performance optimization may be slightly inferior to that on the master end, because events that need or do not need to be copied are read by the IO thread to the slave end, this not only increases the network I/O volume, it also adds the relaylog write volume to the IO thread on the slave end. However, it can still reduce the log usage of slave SQL threads on the slave end. Although the performance is slightly inferior, setting the replication filtering mechanism on the slave end ensures that the data inconsistency between the slave and the master node or the replication error does not occur due to the default schema problem.

 

Slow query log parameters and usage suggestions

Let's take a look at the parameter configuration of slowquerylog. In some cases, in order to locate the query statements with lower efficiency in the system, we need to open the slow query log, that is, slowquerylog. You can view the system slow query log settings as follows:

 mysql> show variables like  '  log_slow %   ' ;  + ---------------- + ------- + | variable_name | value | + ------------------ + ------- + | log_slow_queries | on | + ------------------ + ------- +  1  row  in  set ( 0.00   Sec) mysql  show variables like  '  long_query %   ' ;  + --------------- + ------- + | variable_name | value | + --------------- + ------- + | long_query_time |  1  | + ----------------- + ------- +  1  row  in  set ( 0.01  Sec) 

 

The "log_slow_queries" parameter shows whether the slowquerylog function has been enabled. The "long_query_time" parameter shows how long the slowquery record has been executed. In MySQL released by mysqlab, slowquerylog can set the shortest slow query time to 1 second, which may not fully meet our requirements in some cases, if you want to further reduce the time limit for slow queries, you can use the microslow-patch provided by percona to break through this restriction. Mslpatch not only reduces the slow query time to the millisecond level, but also filters the recorded SQL statements by some specific rules, such as only recording the slowquery of a table and other additional functions. Taking into account the length problem, here will not introduce mslpatch for us to bring more detailed functions and use, please refer to the official introduction (http://www.mysqlperformanceblog.com/2008/04/20/updated-msl-microslow-patch-installation-walk-through)

The overall impact of the slowquerylog function on system performance is not as large as the BINLOG function. After all, the slowquerylog data volume is small, resulting in a small Io loss. However, the system needs to calculate the execution time of each query, so there will always be some consumption, mainly the CPU consumption. If your system is rich enough CPU resources, you don't have to care about this loss. After all, it may bring us more performance optimization gains. However, if our CPU resources are tight, we can disable this function most of the time. Instead, we only need to enable the slowquerylog function intermittently to locate possible slow queries.

Other MySQL logs are rarely used (querylog) or have little impact on performance, so we will not analyze them too much here. As for the logs related to various storage engines, we will leave the "common storage engine optimization" section behind for corresponding analysis.

 

Transferred from MySQL performance tuning and Architecture Design