MySQL Log settings optimization

Preface

After the installation of MySQL, there must be some tuning of MySQL's various parameter options. Although the MySQL system is highly scalable, it can run efficiently in a very sufficient hardware resource environment, and can operate well in very few resource environments, but it is always helpful to have as many hardware resources as possible to improve MySQL performance. In this section we mainly analyze the MySQL log (mainly binlog) on the impact of the system performance, and according to the relevant characteristics of the log to draw the corresponding optimization ideas.

performance impact of log generation

The direct performance loss due to log logging is the most expensive IO resource in the database system.

In the previous chapter on MySQL physical architecture, we have learned that MySQL logs include error log (errorlog), update log (updatelog), binary log (Binlog), query log (querylog), Slow query log (slowquerylog), and so on. Of course, the update log is an older version of MySQL, and is now replaced by binary logs.

By default, the system simply turns on the error log, shutting down all other logs to minimize IO loss to improve system performance. However, in practical scenarios where it is generally slightly more important, it is necessary to open the binary log at least, as this is the basis for the incremental backup of many MySQL storage engines and the basic condition for MySQL to replicate. Sometimes for further performance optimizations, locating a slower SQL statement, many systems also turn on the slow query log to record SQL statements that execute more than a certain value (set by US).

In general, there are few systems in the production system that open the query log. Since the query log is opened, each query executed in MySQL will be recorded in the log, resulting in a larger IO burden on the system, and the actual benefit is not very large. Generally only in the development test environment, in order to locate some features specific use of the SQL statements, only in a short period of time to open the log to do the corresponding analysis. So, in the MySQL system, the MySQL logs (which do not include the respective storage engine's own logs) that have an impact on performance are mostly binlog.

Binlog related parameters and optimization strategies

We first look at the relevant parameters of binlog, and we can get the relevant parameters about Binlog by executing the following command. Of course, it also shows the "Innodb_locks_unsafe_for_binlog" InnoDB storage Engine specific parameters related to Binlog:

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 that accommodates binary log SQL statements during a transaction. The binary log cache is the memory allocated for each client by the server that supports the transactional storage engine and the server has the binary log enabled (-log-bin option), and note that the Binlogcache space of the set size can be assigned to each of the clients. If the reader friend's system often appears in the multi-statement transaction of China, you can try to increase the size of the value to achieve more performance. Of course, we can use the following two state variables of MySQL to determine the current state 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 a multi-statement transaction, if the max_binlog_cache_size is not large enough, the system may report "Multi-statementtransactionrequiredmorethan" Max_binlog_cache _size ' bytesofstorage ' error.

"Max_binlog_size": Binlog log maximum value, generally set to 512M or 1G, but not more than 1G. This size does not strictly control the size of the binlog, especially when the arrival of Binlog near the tail and encountered a large transaction, the system in order to ensure the integrity of the transaction, it is impossible to switch the log action, only the transaction of all SQL records into the current log, until the end of the transaction. This is a bit different from Oracle's redo logs, because Oracle's redo logs record changes in the physical location of the data files, and it also records information about redo and undo, so it is not critical for Oracle to have the same transaction in one log. What MySQL is recording in Binlog is the database logical change information, which is called the event, which is actually a query statement that brings the database changes to DML.

"Sync_binlog": This parameter is critical to the MySQL system, which not only affects the performance loss of Binlog to MySQL, but also affects the integrity of the data in MySQL. The descriptions for the various settings for the "Sync_binlog" parameter are as follows:

Sync_binlog=0, when the transaction commits, MySQL does not do fsync such as the disk synchronization instructions to refresh Binlog_cache information to disk, and let filesystem decide when to synchronize, or cache full after the synchronization to disk.

Sync_binlog=n, after every n transaction commits, MySQL will perform a disk synchronization instruction such as Fsync to force the data in the Binlog_cache to disk.

In MySQL, the system default setting is Sync_binlog=0, that is, do not make any mandatory disk refresh instructions, the performance is the best, but the risk is also the largest. As soon as the system crash, all binlog information in the Binlog_cache is lost. When set to "1", it is the safest and most performance-depleting setting. Because when set to 1, even if the system is crash, a transaction that is not completed in Binlog_cache is lost at most, without any substantial impact on the actual data. From previous experience and related tests, for systems with high concurrency transactions, the system write performance gap of "Sync_binlog" set to 0 and set to 1 could be as much as 5 times times or more.

As we all know, MySQL replication (Replication), is actually by the master side of the Binlog through the use of IO thread through the network to replicate to the slave side, and then through the SQL thread resolution Binlog in the log and then applied to the database to achieve. Therefore, the size of the binlog can have a direct effect on the IO thread and the network between the Msater and slave ends.

There is no way to change the amount of binlog in MySQL, so long as our query changes the data in the database, the event corresponding to the query must be recorded in Binlog. Is there any way we can optimize the replication? Of course not, in the MySQL replication environment, there are actually 8 parameters that allow us to control the DB or table that needs to be copied or omitted to be copied, respectively:

BINLOG_DO_DB: Set which database (Schema) need to record Binlog;

BINLOG_IGNORE_DB: Set which database (Schema) do not record Binlog;

REPLICATE_DO_DB: Set the Database (Schema) that needs to be copied, separated by commas (",") for multiple DB;

REPLICATE_IGNORE_DB: Set the Database (Schema) that can be ignored;

Replicate_do_table: Sets the Table to be copied;

Replicate_ignore_table: Set the Table that can be ignored;

Replicate_wild_do_table: Functions with replicate_do_table, but can be set with wildcard characters;

Replicate_wild_ignore_table: function with replicate_ignore_table, can be set with wildcard character;

Through the above eight parameters, we can be very convenient according to the actual requirements, control from the master side to the slave end of the binlog as little as possible, thereby reducing the master end to slave end of the network traffic, reduce IO thread io volume, It can reduce the number of SQL thread parsing and application SQL, and finally achieve the problem of improving the data delay on slave.

In fact, the first two of the eight parameters above are set on the master side, and the next six parameters are set on the slave side. Although the previous two parameters and the following six parameters do not have a very direct relationship with the function, but for the optimization of MySQL replication can be a similar function. Of course there are certain differences, the main differences are as follows:

If the first two parameters are set on the master side, it will not only reduce the amount of IO generated by the Binlog record on the master side, but also allow the IO thread on the master side to reduce the binlog read, and the binlog of the IO thread passed to the slave side will naturally be less. The advantage of this is that you can reduce network IO, reduce the IO volume of the slave-side IO thread, and reduce the workload of the SQL threads on the slave side, thus optimizing replication performance in the most significant way. Of course, there are some drawbacks to setting up the master side, because MySQL determines whether an event needs to be copied or not based on the data changed by the query that generated the event

The DB that is located, but is based on the default schema where the query is executed, that is, the DB specified at the time we log in or run "usedatabase". The IO thread will read the event to the IO thread of slave only if the current default DB is fully aligned with the DB set in the configuration. So if you change the data of a table in the DB that needs to be copied in a system where the default db is not the same as the DB that needs to be copied, the event is not copied to slave. This causes the data in the slave end to be inconsistent with the master data. Similarly, if the data in the schema that does not need to be copied is changed in the default schema, it is copied to the slave end, and when the slave side does not have the schema, it causes replication to stop.

In the case of the slave end of the six parameters, in the performance optimization may be slightly less than the master side, because either the need or not need to copy the event is read by the IO thread to the slave side, so that not only increase the network IO volume, It also adds Relaylog writes to the IO thread on the slave side. However, you can still reduce the amount of slave SQL thread's log application on the slave side. Although performance is slightly inferior, but on the slave side set the replication filtering mechanism, can ensure that the default schema of the problem caused by the slave and master data inconsistent or replication error.

Slow Query Log related parameters and suggestions for use

Let's take a look at the configuration of the relevant parameters of Slowquerylog. In some cases, we need to open the slow query log, which is slowquerylog, in order to locate the system's query statements that are more efficient than the underground. We can view the settings of the system slow query log as follows:

Mysql> Show variables like ' log_slow% '; +------------------+-------+| variable_name | Value |+------------------+-------+| log_slow_queries | On |+------------------+-------+row in Set (0.00 sec) mysql> Show variables like ' long_query% '; +-----------------+--- ----+| variable_name | Value |+-----------------+-------+| Long_query_time | 1 |+-----------------+-------+row in Set (0.01 sec)

Open Slowquerylog function on the overall effect of the system performance is not binlog so large, after all, slowquerylog data volume is small, the resulting IO loss is smaller, but the system needs to calculate the execution time of each query, so the consumption will always have some, It is mainly CPU-related consumption. If everyone's system is rich enough CPU resources, you can not care about this a little bit of loss, after all, he may bring us a greater performance optimization of the harvest. However, if our CPU resources are also very tense, it is also possible to turn off the function most of the time, but only intermittently open the Slowquerylog function to locate the possible slow query.

Other MySQL logs because of the use of little (querylog) or little performance impact, we are not too much analysis here, as for each storage engine related logs, we left behind the "Common Storage Engine Optimization" section to do the corresponding analysis.