MySQL binlog parameter settings

1.mysql has many system variables, can be set, system variable settings, different systems will cause the execution state.

Therefore, MySQL provides two sets of commands to view the system settings and execution status, respectively.

1. System settings:

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

Note: Show XXX may display very much content. Like Linux content too much, often need grep to filter, then MySQL also consider this, use like words can filter.

After the MySQL installation is complete. There must be some tuning of MySQL's various parameter options.

Although the MySQL system is very scalable, it can be executed efficiently in a very sufficient hardware resource environment, and can be executed very well in very few resource environments. However, it is always helpful to have as much hardware resources as possible to improve MySQL performance. In this section we mainly analyze the impact of MySQL logs (mainly binlog) on system performance. And according to the relevant characteristics of the log to obtain 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 section on MySQL physical architecture, we have learned that MySQL logs contain error logs (errorlog) and update logs (Updatelog). Binary log (Binlog). Query log (querylog), slow query log (slowquerylog), and so on. Of course. The update log is the old version number of MySQL. It is now replaced by binary logs.

By default, the system only opens the error log. All other logs are closed 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. Since this is the basis for an incremental backup of MySQL's very many storage engines, it is also the basic condition for MySQL to replicate. Sometimes for further performance optimizations, locate the SQL statement that is running slower. Very many systems also turn on the slow query log to record SQL statements that run longer than a certain number (which we set ourselves).

Under normal circumstances, very few systems in the production system will open the query log. Since the query log is opened, each query running 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 of the test environment, in order to locate some features detailed use of which SQL statements, 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 contain the logs of each storage engine) that affect 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 to each client by the server that supports the transactional storage engine and the server has binary logging enabled (-log-bin option), noting that each client is able to allocate binlogcache space of the set size. Suppose that the reader friend's system often appears in the multi-statement transaction of China, can try to add the value of the size, in order to achieve more performance.

Of course, we can infer the state of the current binlog_cache_size by the following two state variables in MySQL: Binlog_cache_use and Binlog_cache_disk_use.

Binlog_cache_disk_use: Indicates the number of temporary files used by the cache binary log due to insufficient memory designed by our 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 the 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 run a multi-statement transaction. If the max_binlog_cache_size hypothesis 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.

The size does not strictly control the size of the binlog. Especially when arriving at Binlog is closer to the tail and encountering a larger transaction. System in order to guarantee the integrity of the transaction. It is not possible to switch the log, only to record all of the transaction's SQL into the current log until the transaction ends. This is a bit different from Oracle's redo logs, as Oracle's redo logs record changes in the physical location of the data files, and the information associated with redo and undo is recorded at the same time, so it is not critical for Oracle to have the same transaction in one log.

And MySQL is recorded in the Binlog is the database logic change information, MySQL called the event. is actually a query statement such as DML that brings database changes.

"Sync_binlog": This is important for the MySQL system, which not only affects the performance loss of Binlog to MySQL, but also affects the integrity of the data in MySQL.

For a description of the various settings for the "Sync_binlog" parameter, such as the following:

Sync_binlog=0 when the transaction is committed. MySQL does not do fsync such as the disk synchronization instructions to refresh the information in the Binlog_cache to disk, and let filesystem decide when to do synchronization. or the cache is full before syncing to disk.

Sync_binlog=n. After each n transaction commit, 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 do whatever mandatory disk refresh instructions. The performance at this time is the best, but the risk is also the biggest.

As soon as the system crash. All binlog information in the Binlog_cache will be lost. And when set to "1", it is the most secure, but the most performance loss of the settings. Because when set to 1. Even if the system is crash. There is also a maximum loss of a transaction that is not completed in Binlog_cache, which has no substantive impact on the actual data. From past experience and related tests. For systems with high concurrent transactions, "Sync_binlog" is set to 0 and set to 1 the performance gap written by the system can be as much as 5 times or many others.

Copyright notice: This article Bo Master original articles, blogs, without consent may not be reproduced.

MySQL binlog parameter settings