MySQL binlog parameter settings

1.mysql There are many system variables can be set, system variable settings, will cause the system to run different state. So MySQL provides two sets of commands to view the system settings and the running state, respectively.

1. System settings:

SHOW [GLOBAL | SESSION] VARIABLES [Like_or_where]
SHOW VARIABLES shows the values of the MySQL system VARIABLES.
2. Operation Status:
SHOW [GLOBAL | SESSION] STATUS [Like_or_where]
SHOW status provides server status information.

Note: Show XXX may show a lot of content, similar to Linux under too much content, often need grep to filter, then MySQL also consider this, using like words can be filtered.

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.

2.Binlog related parameters and optimization strategy.

Binlog_cache_size

Binlog_cache_disk_use

binlog_cache_use

Max_binlog_cache_size

Max_binlog_size

Sync_binlog

"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.

Binlog_cache_disk_use: Indicates the number of times a temporary file was used by the cache binary log because of insufficient memory designed by US Binlog_cache_size

Binlog_cache_use: Indicates the number of times cached with Binlog_cache_size

When the corresponding Binlog_cache_disk_use value is relatively large, we can consider the appropriate height binlog_cache_size corresponding value

Show global status like ' bin% ';

The above statement we can get the current database Binlog_cache_size usage situation

mysql> Show status like ' binlog_% ';
+-----------------------+-----------+
| Variable_name        | Value    |
+-----------------------+-----------+
| Binlog_cache_disk_use | 0        |
| Binlog_cache_use    | 120402264 |
+-----------------------+-----------+

"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.

MySQL binlog parameter settings