MySQL binlog log optimization and ideas

Tag: Bring EDM patch host get format configuration Ges blank

After the database has been installed, for the Binlog log parameter setting, there are some parameters that can be adjusted to meet the business requirements or maximize performance. MySQL logs mainly affect the performance of Io, this time the main concern Binlog log.

Check the binary log related parameters

Mysql> Show variables like '%binlog% ';
+-----------------------------------------+----------------------+
| variable_name | Value |
+-----------------------------------------+----------------------+
| Binlog_cache_size | 32768 |
| Binlog_checksum | CRC32 |
| Binlog_direct_non_transactional_updates | OFF |
| Binlog_format | STATEMENT |
| Binlog_max_flush_queue_time | 0 |
| Binlog_order_commits | On |
| Binlog_row_image | Full |
| binlog_rows_query_log_events | OFF |
| Binlog_stmt_cache_size | 32768 |
| Innodb_api_enable_binlog | OFF |
| Innodb_locks_unsafe_for_binlog | OFF |
| Max_binlog_cache_size | 18446744073709547520 |
| Max_binlog_size | 1073741824 |
| Max_binlog_stmt_cache_size | 18446744073709547520 |
| Sync_binlog | 0 |
+-----------------------------------------+----------------------+

Where the Innodb_locks_unsafe_for_binlog parameter is a binlog-related parameter that is specific to the InnoDB storage engine, which is turned off by default

Binlog_cache_size: The default size is 37268 or 32K. The cache size that holds the 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).

Note that each client can allocate a set size of binlogcache space. If the reader friend's system often appears multi-statement transactions in China, you can try to increase the size of the value to achieve more performance;

We can judge the usage of binlog_cache_size by the following two state variables:

1) binlog_cache_use:the number of transactions that used the temporary binary log cache.

----The number of transactions using the binary log cache

2) binlog_cache_disk_use:the number of transactions that used the binary log cache and that exceeded the value of Binlog_c Ache_size and used a temporary file to store changes from the transaction.

----Use the binary log cache and the value reaches the value set by the Binlog_cache_size, storing the number of transactions from transaction changes with temporary files

==

Let's look at the configuration file:

[@hostname ~]# grep ' Binlog '/data/mydata/my3306/my3306.cnf
Binlog_cache_size = 64M
Binlog_format = Mixed
Max_binlog_cache_size = 128M
Max_binlog_size = 200M
Sync_binlog = 0

[@hostname ~]#

Let's see if configuration 64M is enough? To view the following operations:

Mysql> Show status like ' binlog_% ';
+-----------------------+-----------+
| variable_name | Value |
+-----------------------+-----------+
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 120402264 |
+-----------------------+-----------+
2 rows in Set (0.00 sec)

The running condition Binlog_cache_use indicates how many times Binlog_cache memory is used, binlog_cache_disk_use indicates how many times Binlog_cache temporary files were used.

Binlog_cache_disk_use is now equal to 0, which means that the memory cache is sufficient and never needs to be used to temporary files. If there is no long_transaction, I think 64M is a big one.

---

Max_binlog_cache_size: The default value is 18446744073709547520, this value is large enough for us to use. This parameter corresponds to Binlog_cache_size, which 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:1073741824=1G Binlog maximum value, generally set to 512M or 1G, generally 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, and not only does it affect the performance loss of Binlog to MySQL, but it 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 past 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 can be as much as 5 times times or more.

==========

Slow Query Log related parameters and suggestions for use
--
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 if the system has turned on the Slowquerylog function, and the Long_query_time parameter tells us the query that the Slowquery record of the current system setup has been executing for more than a long time.

In the MySQL version of Mysqlab release, Slowquerylog can set the shortest slow query time of 1 seconds, which may not be able to fully meet our requirements in some cases, if you want to further reduce the time limit for slow queries, You can break this limit by using the Microslow-patch provided by Percona (pieces become Mslpatch).

Mslpatch not only reduces the slow query time to the millisecond level, but also filters the recorded SQL by some specific rules, such as logging only the slowquery that involves a table, and so on.

In view of the space problem, here does not introduce Mslpatch to us to bring the more detailed function and use, everybody please refer to the official introduction (http://www.mysqlperformanceblog.com/2008/04/20/ updated-msl-microslow-patch-installation-walk-through/)

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.

From MySQL performance tuning and architecture design

MySQL binlog log optimization and ideas