MySQL Log settings optimization

Objective

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.

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 chapters, we have learned that MySQL logs include error logs (errorlog), binary logs (Binlog), query Logs (querylog), slow query logs (Slowquerylog), and so on.

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                       | 32768                | | | binlog_direct_non_transactional_updates | OFF                  | | Binlog_format                           | MIXED                | | binlog_stmt_cache_size                  | 32768                | | innodb_locks_unsafe_for_binlog          | OFF                  | | max_binlog_cache_size                   | 18446744073709547520 | | max_binlog_size                         | 1073741824           | | max_ binlog_stmt_cache_size |              18446744073709547520 | | Sync_binlog                             | 0                    |+-------------------------------- ---------+----------------------+

MySQL Log settings optimization