MySQL DBA tutorial: MySQL performance Optimization cache parameter optimization _mysql

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) of the perspective of IO optimization:

One, 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 the 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).

Ii. binlog_cache_size (Global)
The 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 there is no big business in our database, and the writing 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 it.

Iii. key_buffer_size (Global)
Key buffer is probably one of the most familiar MySQL cache parameters, especially when MySQL has not replaced 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, but 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.

Iv. 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:

Copy Code code as follows:

Insert ... select ...

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

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

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

Vi. 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 the time 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.

Vii. 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 into the INNOFB log Buffer, when meeting Innodb_flush_log_trx_commit The log is not written to a file (or synchronized to a disk) until the corresponding condition set by the parameter (or the log buffer is full). 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 will be 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.

Viii. innodb_max_dirty_pages_pct (Global)
This parameter differs from the parameters above, and is not used to set a parameter for caching the memory size of some data, but to control the dirty Page in the InnoDB Buffer Pool that can be written without writing to the data file Proportions (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 by the write operation.

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's performance.

optimization Example:
According to Mcsrainbow Friend's request, here is a list of the recommended values for the relevant parameters based on past experience:

1.query_cache_type: If you use the InnoDB storage engine all, it is recommended that 0, if you use the MyISAM storage engine, it is recommended to 2, while explicitly controlling in the SQL statement is yo you gquery cache
2.query_cache_size: According to the hit rate (qcache_hits/(qcache_hits+qcache_inserts) *100) to adjust, generally do not recommend too big, 256MB may have been almost, Large configuration-type static data can be adjusted appropriately
3.binlog_cache_size: The general Environment 2MB~4MB is a suitable choice, the transaction is large and writes frequently the database environment may adjust the size suitably, but does not recommend surpasses the 32MB
4.key_buffer_size: If you do not use the MyISAM storage engine, 16MB is sufficient to cache some system table information, and so on. If you use the MyISAM storage engine, if memory allows, put all indexes in memory as much as possible, simply "the bigger the better"
5.bulk_insert_buffer_size: If the regular need to use a batch Insert special statement (above) to insert data, you can appropriately adjust the parameter to 16MB~32MB, not recommended to continue to increase, someone 8MB
6.innodb_buffer_pool_size: If you do not use the InnoDB storage engine, you can not adjust this parameter, if you need to use, in the case of memory permitting, as much as possible to store all the InnoDB data files as memory, but also "the bigger the better"
7.innodb_additional_mem_pool_size: General database Recommendations to adjust to 8MB~16MB, if the table is particularly large, you can adjust to 32MB, can be based on the information in the error log to determine whether the need to increase
8.innodb_log_buffer_size: The default is 1MB, the system such as frequent systems can be appropriately increased to 4MB~8MB. Of course, as mentioned above, this parameter is actually related to another flush parameter. Generally do not recommend more than 32MB
9.innodb_max_dirty_pages_pct: According to previous experience, restart recovery data if more than 1GB, startup speed will be slow, almost difficult to accept, so the recommendation is not more than 1gb/innodb_buffer_pool_size (GB) * 100 this value. Of course, if you can tolerate a long startup time, and want to minimize memory to disk flush, you can adjust this value to 90, but do not recommend more than 90
Note: The above range of values is just a few of the best experience I've ever gotten from a database scenario. Does not necessarily apply to all scenarios, so in the actual optimization process, we still need to adjust their own analysis, but also welcome to contact me through Mail and communication optimization or architectural technology, Explore and learn from each other.