MySQL detailed (+)------------cache parameter optimization

The database is an IO-intensive application whose primary responsibility is to manage and store the data. And we know that the time to read a database from memory is the microsecond level, while reading an IO from a common hard disk is at the millisecond level, which differs by 3 orders of magnitude. Therefore, to optimize the database, the first step is to optimize the IO, as far as possible to convert disk IO to memory io. This article first from the MySQL database IO related parameters (cache parameters) to see 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 ResultSet in MySQL, which is a result set executed by an SQL statement, so only for SELECT statements. When we open the Query cache feature, MySQL receives a request for a SELECT statement, if the statement satisfies the query cache requirement (not explicitly stating that the query cache is not allowed, or if you have explicitly declared that you need to use query cache), MySQL will hash the received SELECT statement as a string directly based on a pre-set 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 will return the data directly, omitting all subsequent steps (such as parsing of SQL statements, optimizer optimizations, and requesting data from the storage engine), which greatly improves performance.

Of course, the query cache also has a fatal flaw, that is, when there is any change in the data of a table, it will cause all the SELECT statements referencing the table to fail in cached data in Query cache. So, when our data changes very frequently, using the query Cache may not be worth the candle.

The use of query cache requires multiple parameter mates, the most critical of which is query_cache_size and Query_cache_type, which sets the memory size used to cache ResultSet, which sets the scenario under which to use query cache. In the past experience, if the MySQL database is not used to cache basic unchanging data, Query_cache_size General 256MB is a more appropriate size. Of course, this can be adjusted by calculating the hit ratio (qcache_hits/(qcache_hits+qcache_inserts) *100) of the query cache. The Query_cache_type can be set to 0 (OFF), 1 (on), or 2 (Demond), which means that the query cache is not used at all, except explicitly requiring the use of the query cache (using the Sql_no_ cache), use query cache for all SELECT, and use query cache (using Sql_cache) only for display requirements.

binlog_cache_size (Global)

The Binlog cache is used in environments where the binary log (Binlog) logging feature is turned on, and is a memory area that MySQL has designed to improve the efficiency of Binlog recording for a short period of time to temporarily cache Binlog data.

In general, if there are no large transactions in our database, and the writes are not particularly frequent, 2MB~4MB is a suitable choice. But if our database large transaction, write volume is large, can be adjusted with the appropriate height binlog_cache_size. At the same time, we can use Binlog_cache_use and binlog_cache_disk_use to analyze whether the set of binlog_cache_size is sufficient, whether there is a large number of Binlog_ The cache was cached with temporary files (binlog_cache_disk_use) due to insufficient memory size.

key_buffer_size (Global)

Key Buffer is probably the most familiar mysql cache parameter, 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 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.

Also, when we use MyISAM storage, there is one important point to note, because the MyISAM engine's nature limits him to caching only the index blocks in memory, not the table database blocks. So, our SQL has to make sure that the filters are in the index as much as possible so that the cache helps us improve query efficiency.

bulk_insert_buffer_size (thread)

Like key_buffer_size, this parameter is also used only for the use of the MyISAM storage engine to cache the temporary cache write data when bulk data is inserted. This memory area is used to cache bulk structure data to help bulk write data files when we write to statements using several of the following data:

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 is probably the most critical parameter that affects our performance, which is used to set the size of the memory area used to cache InnoDB indexes and blocks, similar to the MyISAM storage engine's The Key_buffer_size parameter, of course, may be more like Oracle's db_cache_size. Simply put, when we manipulate a InnoDB table, all the data returned or any index block used in the data process will go through this memory area.

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

We can calculate cache hit ratio 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 according to the hit ratio to optimize.

innodb_additional_mem_pool_size (Global)

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

It's easy to know if this parameter size is enough, because when it's too small, MySQL logs the Warning information into the database's error log, and you know it's time to adjust the parameter size.

innodb_log_buffer_size (Global)

This is the buffer used by the transaction log of the InnoDB storage engine. Similar to Binlog Buffer,innodb when writing transaction logs, in order to improve performance, the information is first written to the INNOFB log Buffer, when it satisfies the Innodb_flush_log_trx_commit The log is written to a file (or to a disk) after 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 impact on the write performance of InnoDB log. This parameter can be set to 0,1,2, as explained below:

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

In addition, the MySQL documentation mentions that the mechanism of synchronizing each second in these settings may not completely ensure that a very accurate synchronization occurs every second and depends on the process scheduling problem. In fact, whether InnoDB can really meet the value set by this parameter means normal Recovery is still under the limitations of the file system and the disk itself in the different OS, there may be times when the disk synchronization is not actually complete and will tell Mysqld that the disk synchronization has been completed.

innodb_max_dirty_pages_pct (Global)

This parameter differs from the parameters above, and is not used to set a parameter that caches the memory size of a particular data, but rather to control the dirty Page that can be used in InnoDB buffer Pool without writing 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 value, the smaller the write operation from memory to disk, so that the disk IO of the write operation can be reduced to some extent.

However, if the scale value is too large, the time to restart after the database Crash may be very long because there will be a large amount of transactional data that needs to be recovered from the log file to be written to the data file. At the same time, excessive proportional values may also cause the flush operation to be "too strong" after reaching the proportional set cap, resulting in a high performance fluctuation.

The above parameters are the main parameters in MySQL designed to reduce the physical IO of disk, which plays a vital role in MySQL performance. -eof-according to Mcsrainbow Friend's request, here is a list of recommended values for the relevant parameters based on previous experience:

• Query_cache_type: If all of the InnoDB storage engines are used, it is recommended that 0, if using the MyISAM storage engine, is recommended to 2, while explicitly controlling in the SQL statement is yo you gquery cache
• Query_cache_size: According to the hit Ratio (qcache_hits/(qcache_hits+qcache_inserts) *100) to adjust, generally not recommended too large, 256MB may have been almost, Large configuration-type static data can be adjusted to the appropriate size
• Binlog_cache_size: The general Environment 2MB~4MB is a suitable choice, the transaction is large and writes frequently the database environment can be suitably large, but does not recommend more than 32MB
• 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, put all the indexes into memory whenever possible, and simply say, "Bigger is better."
• Bulk_insert_buffer_size: If you frequently need to use the BULK INSERT special statement (described above) to insert the data, you can adjust the parameter to 16MB~32MB, do not recommend to continue to increase, someone 8MB
• Innodb_buffer_pool_size: If you do not use the InnoDB storage engine, you can not adjust this parameter, if necessary, in memory, if possible to store all the InnoDB data files as in memory, but also the "bigger the better"
• Innodb_additional_mem_pool_size: General database recommended adjustment to 8MB~16MB, if the table is particularly large, can be adjusted to 32MB, you can determine whether you need to increase the information in error log
• Innodb_log_buffer_size: The default is 1MB, as the system with frequent systems can be increased appropriately to 4MB~8MB. Of course, as described above, this parameter is actually related to the additional flush parameter. Generally not recommended more than 32MB
• innodb_max_dirty_pages_pct: According to previous experience, restarting the recovered data if it is more than 1GB, the boot speed will be relatively slow, almost unacceptable, so it is less than 1gb/innodb_buffer_pool_size (GB) * 100 this value. Of course, if you can tolerate a long boot time and want to minimize memory to disk flush, you can adjust this value to 90, but not more than 90

Note: The above value range is just my experience based on the previous experience of some of the optimization of the database scenario, and does not necessarily apply to all scenarios, so in the actual optimization process, we also need to constantly adjust the analysis, but also welcome you at any time through the Mail contact me to communicate with the optimization or architecture technology, Explore Mutual learning together.

Copyright NOTICE: Welcome to reprint, hope to reprint the same time add the original address, thank you for your cooperation, learning happy!

MySQL detailed (+)------------cache parameter optimization