Optimization of caching parameters for MySQL database performance optimization

In peacetime is asked the most questions about the MySQL database performance optimization issues, so recently intended to write a MySQL database performance optimization of the series of articles, I hope that the junior high level MySQL DBA and other interested in MySQL performance of friends to help.

Databases are IO-intensive applications whose primary responsibility is data management and storage. And we know that the time to read a database from memory is a microsecond level, while reading an IO from a common hard disk is at a millisecond level, which is 3 orders of magnitude. Therefore, to optimize the database, the first step is to optimize the IO, as far as possible to the disk IO into memory io. This article first from the MySQL database IO correlation parameters (caching parameters) from the perspective of what parameters can be used for IO optimization:

Query_cache_size/query_cache_type (Global)

Query cache acts on the entire MySQL Instance, primarily to cache the ResultSet in MySQL, which is the result set executed by an SQL statement, so it is only for SELECT statements. When we turn on the query cache feature, MySQL receives a request for a SELECT statement that satisfies query cache requirements (not explicitly stating that query cache is not allowed, or explicitly stating that you need to use query cache). MySQL will hash the received SELECT statement directly against the predefined hash algorithm and then go directly to the query cache to find out if it is already cached. That is, if it is already in the cache, the select request returns the data directly, omitting all subsequent steps (such as parsing of SQL statements, optimizer optimizations, and requesting data to the storage engine), which greatly improves performance.

Of course, Query Cache also has a fatal flaw, that is, when any change in the data of a table, will cause all the SELECT statements that reference the table in the query cache data is invalid. So, when our data changes very frequently, using query Cache may not outweigh the gains.

Query cache use requires a number of parameters, of which the most critical is query_cache_size and Query_cache_type, the former set to cache the ResultSet memory size, the latter set under what scene use Query cache. In the past experience, if it is not used to cache basic unchanging data of the MySQL database, query_cache_size General 256MB is a relatively appropriate size. Of course, this can be adjusted by calculating the hit ratio of the query cache (qcache_hits/(qcache_hits+qcache_inserts) *100). Query_cache_type can be set to 0 (off), 1 (on), or 2 (Demond), indicating that the query cache is not used at all, except for explicit requirements not to use query cache (using Sql_no_cache) All select uses query cache, and query cache is used only for display requests (using Sql_cache).

binlog_cache_size (Global)

Binlog cache is for environments where the binary log (Binlog) logging feature is turned on, a memory area designed to temporarily cache Binlog data for a short period of time by MySQL to improve the efficiency of Binlog records.

Generally speaking, if our database does not have any big business, write is not very frequent, 2MB~4MB is a suitable choice. However, if we have a large number of database transactions, the written volume is relatively large, can be adjusted with appropriate high binlog_cache_size. At the same time, we can through Binlog_cache_use and binlog_cache_disk_use to analyze the set of binlog_cache_size is sufficient, whether there is a large number of binlog_cache due to insufficient memory size and use of temporary files ( Binlog_cache_disk_use) to cache.

key_buffer_size (Global)

Key buffer is probably one of the most familiar MySQL cache parameters, especially when MySQL does not replace the default storage engine, many friends may find that the default MySQL configuration file set the largest memory parameter is this parameter. The key_buffer_size parameter is used to set the size of the memory area used to cache the index files in the MyISAM storage engine. If we have enough memory, this cache area is best able to store all the indexes of all our MyISAM engine tables to maximize performance.

In addition, when we use MyISAM storage, there is an important point to note, because the MyISAM engine's characteristics limit the fact that he only caches the index block into memory and does not cache the table database block. So, our SQL has to make sure that the filter conditions are in the index as much as possible so that caching can help us improve query efficiency.

bulk_insert_buffer_size (thread)

As with key_buffer_size, this parameter is used only for the use of the MyISAM storage engine, which caches the temporary caching of data when it is used to bulk insert data. When we write a statement using the following data, this memory area is used to cache the bulk structure data to help bulk write to the data file:

Insert ... select ...

Insert ... values (...), (...), (...) ...

Load Data infile ... (Non-empty table)

innodb_buffer_pool_size (Global)

When we use the InnoDB storage engine, the Innodb_buffer_pool_size parameter may be one of the most critical parameters affecting our performance, which he uses to set the size of the memory area used to cache InnoDB indexes and blocks of data, similar to the MyISAM storage engine Key_buffer_size parameters, of course, may be more like Oracle's db_cache_size. In short, when we operate a InnoDB table, all the data returned or any index block used in the data process will go through the memory area.

As with Key_buffer_size for the MyISAM engine, Innodb_buffer_pool_size sets the size of the largest chunk of memory that the InnoDB storage engine needs, directly related to the performance of the InnoDB storage engine, So if we have enough memory, we can set this parameter to be enough to hit, and put as many InnoDB indexes and data as possible into the cache area, until all.

We can compute the cache hit rate by (innodb_buffer_pool_read_requests–innodb_buffer_pool_reads)/innodb_buffer_pool_read_requests * 100% , and adjusts the innodb_buffer_pool_size parameter size according to the hit rate to optimize.

innodb_additional_mem_pool_size (Global)

This parameter we usually adjust is not too much, many people use the default value, probably many people are not too familiar with the role of this parameter. Innodb_additional_mem_pool_size set the InnoDB storage engine to hold data dictionary information and some internal data structure of the memory space size, so when we have a MySQL instance database objects very much, It is necessary to adjust the size of this parameter appropriately to ensure that all data is stored in memory for increased access efficiency.

The size of this parameter is still relatively easy to know, because when it is too small, MySQL will record Warning information to the database error log, this time you know the size of the adjustment of this parameter.

innodb_log_buffer_size (Global)

This is the buffer used by the transaction log of the InnoDB storage engine. Similar to the Binlog Buffer,innodb in writing transaction log, in order to improve performance, it is also first write information to INNOFB log Buffer, when the corresponding conditions set by the Innodb_flush_log_trx_commit parameters are met ( or log buffer is full), the log is not written to a file (or synchronized to disk). You can set the maximum memory space that can be used by the Innodb_log_buffer_size parameter.

Note: The Innodb_flush_log_trx_commit parameter has a very critical effect on the write performance of the InnoDB log. This parameter can be set to 0,1,2 and interpreted as follows:

The data in 0:log buffer will be written to log file at once per second, while simultaneous file system to disk synchronization, but the commit of each transaction does not trigger any log buffer to log file refresh or file system to disk refresh operation;

1: The data in log buffer will be written to log file every time the transaction is submitted, and the synchronization of file system to disk will also be triggered;

2: Transaction commit triggers the refresh of log buffer to log file, but does not trigger synchronization of disk file system to disk. In addition, there is one file system to disk synchronization operation per second.

In addition, the MySQL documentation mentions that the mechanism for synchronizing each second of these settings may not fully ensure that very accurate synchronization occurs every second, and also depends on the process scheduling problem. In fact, InnoDB can really satisfy the value that this parameter sets represents the normal Recovery is still limited by the file system under different OS and the disk itself, and may sometimes tell Mysqld that the disk synchronization has been completed without actually completing the disk synchronization.

innodb_max_dirty_pages_pct (Global)

This parameter is different from the parameters above, and is not used to set a parameter for caching the memory size of some data, but rather to control the ratio of the dirty Page in the InnoDB Buffer Pool that can not be written to the data file ( Dirty data that has been repaired but has not yet been written to the data file from memory. The larger the scale, the lower the write operation from memory to disk, so that the disk IO can be reduced to a certain extent.

However, if this scale is too large, the time to reboot after the database Crash may be long, because a large amount of transaction data needs to be recovered from the log file and written to the data file. At the same time, too large proportional value may also cause the flush operation "too fierce" after reaching the proportional set limit, resulting in a great performance fluctuation.

The above parameters are the main parameters in MySQL designed to reduce disk physical IO, and are critical to MySQL performance.

(Responsible editor: Lu Guang)