The most frequently asked question is about  MySQL database performance optimization, so I recently planned to write a series of articles on MySQL database performance optimization . I hope that those who are interested in the junior and intermediate MySQL DBAs and other friends who are interested in MySQL performance optimization will have Helped.

This is the first article of the  MySQL database performance optimization topic  series: MySQL database performance optimization cache parameter optimization

The database is an IO-intensive application, and its main responsibility is data management and storage. And we know that the time to read a database from memory is at the level of microseconds, while reading an IO from an ordinary hard disk is at the level of milliseconds. The difference between the two is 3 orders of magnitude. Therefore, to optimize the database, the first step that needs to be optimized is IO, as much as possible to convert disk IO into memory IO. This article first looks at which parameters can be used to optimize IO from the perspective of MySQL database IO related parameters (cache parameters) :

• query_cache_size/query_cache_type (global)

  Query cache acts on the entire MySQL Instance and is mainly used to cache the ResultSet in MySQL, which is the result set of a SQL statement execution, so it can only be used for select statements. When we turn on the Query Cache function, after MySQL receives a request for a select statement, if the statement meets the requirements of Query Cache (not explicitly stated that Query Cache is not allowed, or that the use of Query Cache has been explicitly stated), MySQL It will directly hash the received select statement as a string according to the preset HASH algorithm, and then directly check whether it has been cached in the Query Cache. In other words, if it is already in the cache, the select request will directly return the data, thereby omitting all subsequent steps (such as SQL statement parsing, optimizer optimization, and requesting data from the storage engine, etc.), which greatly improves performance.

  Of course, Query Cache also has a fatal flaw, that is, any change in the data of a table will cause all select statements that reference the table to invalidate the cached data in the Query Cache. Therefore, when our data changes very frequently, the use of Query Cache may outweigh the gains.

  The use of Query Cache requires the cooperation of multiple parameters. The most important of which is query_cache_size and query_cache_type. The former sets the memory size for caching the ResultSet, and the latter sets the context in which Query Cache is used. In the past experience, if it is not a MySQL database for caching basically unchanged data, query_cache_size is generally 256MB is a more appropriate size. Of course, this can be adjusted by calculating the hit rate of Query Cache (Qcache_hits/(Qcache_hits+Qcache_inserts)*100)). The query_cache_type can be set to 0 (OFF), 1 (ON) or 2 (DEMOND), respectively, which means that the query cache is not used at all. All the selects except the explicit request not to use the query cache (using sql_no_cache) use the query cache. Use query cache (using sql_cache) only if required for display.

• binlog_cache_size (global)

  Binlog Cache is used in the environment where the binary log (binlog) recording function is turned on. It is a memory area designed by MySQL to improve the efficiency of binlog recording and temporarily cache binlog data in a short time.

  Generally speaking, if there are no big transactions in our database and writes are not particularly frequent, 2MB to 4MB is a suitable choice. But if our database has a lot of big transactions and writes a lot, we can increase binlog_cache_size appropriately. At the same time, we can use binlog_cache_use and binlog_cache_disk_use to analyze whether the set binlog_cache_size is sufficient, and whether a large amount of binlog_cache is cached using temporary files (binlog_cache_disk_use) due to insufficient memory size.

• key_buffer_size (global)

  Key Buffer may be the most familiar MySQL cache parameter, especially when MySQL does not change the default storage engine, many friends may find that this parameter is the largest memory parameter set in the default MySQL configuration file. The key_buffer_size parameter is used to set the size of the memory area used to cache index files in the MyISAM storage engine. If we have enough memory, this cache area should be able to store all the indexes of all our MyISAM engine tables to improve performance as much as possible.

  In addition, when we use MyISAM storage, there is an extremely important point to note. Due to the characteristics of the MyISAM engine, it only caches index blocks into memory, not table database blocks. Therefore, our SQL must make the filter conditions in the index as much as possible, so that the cache can help us improve query efficiency.

• bulk_insert_buffer_size (thread)

  Like key_buffer_size, this parameter also only applies to the MyISAM storage engine, which is used to temporarily cache write data when inserting data in batches. When we use the following types of data write statements, this memory area will be used to cache the data of the batch structure to help batch write data files:

  insert… select…
  insert… values ​​(…) ,(…),(…)…
  load data infile… into… (non-empty table)

• innodb_buffer_pool_size (global)

  When we use the InnoDB storage engine, the innodb_buffer_pool_size parameter may be the most critical parameter that affects our performance. It is used to set the size of the memory area used to cache InnoDB indexes and data blocks, similar to the key_buffer_size parameter of the MyISAM storage engine. Of course, it may be more like Oracle's db_cache_size. Simply put, when we operate an InnoDB table, all the returned data or any index block used in the process of data removal will go through this memory area.

  Like key_buffer_size for the MyISAM engine, innodb_buffer_pool_size sets the size of the memory area that the InnoDB storage engine needs the most, which is directly related to the performance of the InnoDB storage engine. So if we have enough memory, we can set this parameter to a sufficient size. As many InnoDB indexes and data are put into this cache area, up to all.

  We can calculate the cache hit rate by (Innodb_buffer_pool_read_requests – Innodb_buffer_pool_reads)/Innodb_buffer_pool_read_requests * 100%, and adjust the innodb_buffer_pool_size parameter size for optimization based on the hit rate.

• innodb_additional_mem_pool_size (global)

  We may not usually adjust this parameter too much. Many people use the default value. Maybe many people are not too familiar with the function of this parameter. innodb_additional_mem_pool_size sets the size of the memory space used by the InnoDB storage engine to store data dictionary information and some internal data structures, so when we have a lot of database objects in a MySQL Instance, we need to adjust the size of this parameter appropriately to ensure that all data is Can be stored in memory to improve access efficiency.

  It is easy to know whether the size of this parameter is sufficient, because when it is too small, MySQL will record Warning information in the error log of the database. At this time, you know it is time to adjust the size of this parameter.

• innodb_log_buffer_size (global)

  This is the buffer used by the InnoDB storage engine's transaction log. Similar to Binlog Buffer, when InnoDB writes the transaction log, in order to improve performance, it also writes the information to the Innofb Log Buffer first, and only after the corresponding conditions set by the innodb_flush_log_trx_commit parameter (or the log buffer is full) Logs are written to files (or synchronized to disk). The maximum memory space that can be used can be set through the innodb_log_buffer_size parameter.
  Note: The innodb_flush_log_trx_commit parameter has a very critical impact on the write performance of InnoDB Log. This parameter can be set to 0, 1, 2, and the explanation is as follows:

  0: The data in the log buffer will be written to the log file at a frequency of once per second, and the file system will be synchronized to the disk at the same time, but the commit of each transaction will not trigger any log buffer to log file. Refresh or file system to disk refresh operation;
  1: The data in the log buffer will be written to the log file every time a transaction is committed, and it will also trigger the synchronization of the file system to the disk;
  2: Transaction commit will trigger the log The flush from buffer to log file does not trigger the synchronization of the disk file system to the disk. In addition, there will be a file system to disk synchronization operation every second.

  In addition, the MySQL documentation also mentions that the synchronization once per second mechanism in these settings may not completely ensure that synchronization will occur every second at a very accurate rate. It also depends on process scheduling issues. In fact, whether InnoDB can really meet the meaning of the value set by this parameter. Normal Recovery is still limited by the file system under different OSs and the disk itself. It may sometimes tell mysqld that it has been synchronized without actually completing the disk synchronization. Disk synchronization is completed.

• innodb_max_dirty_pages_pct (global)

  This parameter is different from the above parameters. It is not a parameter used to set the memory size used to cache certain data, but to control the proportion of Dirty Pages in the InnoDB Buffer Pool that do not need to be written to the data file ( Dirty data that has been repaired but has not been written to the data file from memory). The larger this ratio is, the write operations from memory to disk will be relatively reduced, so disk IO for write operations can be reduced to a certain extent.

  However, if this ratio is too large, the restart time after the database crash may be very long, because a large amount of transaction data needs to be recovered from the log file and written into the data file. At the same time, an excessively large ratio value may also cause the “excessive” flush operation after reaching the upper limit of the ratio setting, resulting in large performance fluctuations.

Note: The above value range is just some optimization experience values ​​obtained based on the database scenarios I encountered in the past, and may not be applicable to all scenarios, so in the actual optimization process, you need to continuously adjust and analyze by yourself, and welcome You can contact me at any time through Mail to communicate about optimization or architecture technology, and discuss and learn from each other together.

译文：