MySQL (21) ------------ cache parameter optimization, mysql ------------

MySQL (21) ------------ cache parameter optimization, mysql ------------

Databases are IO-intensive applications. Their primary responsibilities are data management and storage. We know that the time for reading a database from the memory is microseconds, while reading an IO from a common hard disk is in milliseconds. The difference between the two is three orders of magnitude. Therefore, to optimize the database, the first step is to optimize I/O and convert disk I/O to memory I/O as much as possible.This article first looks at the MySQL database IO-related parameters (Cache parameters) to see which parameters can be used for IO Optimization:

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, that is, the result set for executing an SQL statement. Therefore, it can only be used for select statements. When the Query Cache function is enabled, after MySQL receives a select statement request, if this statement meets the Query Cache requirements (not explicitly stated that Query Cache is not allowed, or you have explicitly stated that you need to use Query Cache), MySQL will directly HASH the received select statement in string mode based on the preset hash algorithm, then, go to the Query Cache to check whether the data has been cached. That is to say, if the select request is already in the cache, the data will be directly returned, thus omitting all subsequent steps (such as SQL statement parsing, optimizer optimization and Data Request to the storage engine), greatly improving performance.

Of course, Query Cache also has a fatal defect, that is, when the data in a table has any changes, all the select statements that reference the table in the Query Cache will become invalid. Therefore, when our data changes frequently, the use of Query Cache may not be worth the candle.

The use of Query Cache requires the combination of multiple parameters. The most important among them is query_cache_size and query_cache_type. The former is used to Cache the memory size of the ResultSet, and the latter is used in the scenario where Query Cache is used. From past experiences, if it is not a MySQL database used to cache basically unchanged data, query_cache_size is generally 256 MB, which is a suitable size. Of course, this can be adjusted by calculating the Query Cache hit rate (Qcache_hits/(Qcache_hits + Qcache_inserts) * 100. Query_cache_type can be set to 0 (OFF), 1 (ON), or 2 (DEMOND), indicating that query cache is not used at all, except explicitly requiring no query cache (using SQL _no_cache) query cache is used for all other select statements. query cache (SQL _cache) is used only for display requirements ).

Binlog_cache_size (global)

Binlog Cache is used to enable the binary log (binlog) record function. It is a memory area designed by MySQL to improve the binlog record efficiency and temporarily Cache binlog data in a short time.

In general, if there are no large transactions in our database, writing is not very frequent, 2 MB ~ 4 MB is a suitable choice. However, if there are many large transactions in our database and the write volume is large, you can increase the binlog_cache_size accordingly. At the same time, we can use binlog_cache_use and binlog_cache_disk_use to analyze whether the configured binlog_cache_size is sufficient and whether a large number of binlog_cache files are cached due to insufficient memory size (binlog_cache_disk_use.

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 may find that, the maximum memory parameter set in the default MySQL configuration file is this parameter. The key_buffer_size parameter is used to set the memory size of the index file used to cache the MyISAM storage engine. If we have enough memory, it is best to store all the indexes of all our MyISAM engine tables in this cache area to maximize performance.

In addition, when using MyISAM storage, we need to note that due to the features of MyISAM engine, it only caches index blocks to the memory, instead of caching table database blocks. Therefore, we must try our best to make all the filter conditions in the index, so that the cache can help us improve the query efficiency.

Bulk_insert_buffer_size (thread)

Like key_buffer_size, this parameter only applies to the MyISAM storage engine, which is used to cache data written in temporary cache when data is inserted in batches. When we use the following data writing statements, we will use this memory area to cache batch structured data 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, it may be more like db_cache_size of Oracle. To put it simply, when we operate an InnoDB table, all the data returned or any index Block Used in the de-data process will go through this memory area.

Like the key_buffer_size for the MyISAM engine, innodb_buffer_pool_size sets the size of the memory area with the largest requirements for the InnoDB Storage engine, which is directly related to the performance of the InnoDB Storage engine. Therefore, if we have enough memory, you can set this parameter to a sufficient number to add as many InnoDB indexes and data as possible to the cache until all of them exist.

We can calculate the cache hit rate by using (bytes-innodb_buffer_pool_read_readests)/Innodb_buffer_pool_read_requests * 100%, and adjust the innodb_buffer_pool_size parameter based on the hit rate to optimize it.

Innodb_additional_mem_pool_size (global)

We may not adjust this parameter too much at ordinary times. Many people use the default value, and many people may not be too familiar with this parameter. Innodb_additional_mem_pool_size sets the InnoDB Storage engine to store data dictionary information and the memory size of some internal data structures. Therefore, when there are many database objects in one MySQL Instance, it is necessary to adjust the size of this parameter to ensure that all data can be stored in the memory to improve access efficiency.

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

Innodb_log_buffer_size (global)

This is the buffer used by the transaction logs of the InnoDB Storage engine. Similar to Binlog Buffer, InnoDB writes information to Innofb Log Buffer to improve performance when writing transaction logs, when the corresponding conditions set by the innodb_flush_log_trx_commit parameter are met (or the log buffer is full), the log will be written to a file (or synchronized to the disk. You can use the innodb_log_buffer_size parameter to set the maximum memory space that can be used.
Note: The innodb_flush_log_trx_commit parameter has a critical impact on the InnoDB Log write performance. This parameter can be set to 0, 1, and 2. The explanation is as follows:

0: Data in the log buffer is written to the log file at a frequency every second, and the file system is synchronized to the disk at the same time, however, the commit of each transaction does not trigger any refresh from log buffer to log file or from the file system to disk;
1: Data in the log buffer will be written to the log file during each transaction commit, and synchronization from the file system to the disk will also be triggered;
2: transaction commit triggers refresh from log buffer to log file, but does not trigger Disk file System to disk synchronization. In addition, a file system is synchronized to the disk every second.

In addition, the MySQL documentation also mentions that the mechanism for synchronizing these settings once per second may not completely ensure that synchronization will occur every second very accurately, but also depends on the process scheduling problem. In fact, whether InnoDB can really meet the meaning of the value set by this parameter indicates that normal Recovery is still restricted by file systems and disks in different operating systems, sometimes, if the Disk Synchronization is not completed, mysqld is also notified that the disk synchronization has been completed.

Innodb_max_dirty_pages_pct (global)

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

However, if the proportion value is too large, it may take a long time to restart the database after Crash, 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, a large proportion value may also cause excessive flush operations after reaching the upper limit of the proportion setting, resulting in high performance fluctuations.

The above parameters are the main parameters designed for MySQL to reduce Disk Physical IO, and play a crucial role in MySQL performance. -EOF-according to the requirements of mcsrainbow friends, the recommended values of parameters obtained based on previous experience are listed here:

• Query_cache_type: If innodb Storage engine is used, it is recommended that it be 0. If MyISAM storage engine is used, it is recommended to be 2, and explicitly control whether your gquery cache is yo in SQL statements.
• Query_cache_size: adjusted based on the hit rate (Qcache_hits/(Qcache_hits + Qcache_inserts) * 100). Generally, it is not recommended that it be too large. The size of MB may be almost the same. Large configuration static data can be
• Binlog_cache_size: generally 2 MB ~ 4 MB is an appropriate choice. The database environment with large transactions and frequent writes can be appropriately increased, but it is not recommended to exceed 32 MB.
• Key_buffer_size: If the MyISAM storage engine is not used, 16 Mb is sufficient to cache some system table information. If you use the MyISAM storage engine, put all the indexes into the memory whenever the memory permits. Simply put, the larger the memory, the better"
• Bulk_insert_buffer_size: If you need to insert data in batches using special statements (described above), you can increase the value of this parameter to 16 MB ~ 32 MB. We do not recommend that you continue to increase the size of a person by 8 MB.
• Innodb_buffer_pool_size: if you do not use the InnoDB Storage engine, you do not need to adjust this parameter. If you need to use it, store all InnoDB data files as much as possible in the memory as allowed, in the same way, "the bigger the better"
• Innodb_additional_mem_pool_size: it is recommended to adjust the size of a general database to 8 Mb ~ 16 MB. If the table is too large, it can be adjusted to 32 MB. You can determine whether to increase according to the error log information.
• Innodb_log_buffer_size: The default value is 1 MB. The system size can be increased to 4 MB for frequent systems ~ 8 MB. Of course, as described above, this parameter is actually related to another flush parameter. Generally, it is not recommended to exceed 32 MB.
• Innodb_max_dirty_pages_pct) * The value of 100. Of course, if you can tolerate a long start time and want to minimize the memory to disk flush, You can adjust this value to 90, but it is not recommended to exceed 90.

Note: The above value range is only applicable to some of the experience gained from the previous database scenarios, and is not necessarily applicable to all scenarios, therefore, in the actual optimization process, you need to constantly adjust and analyze your own data. You are also welcome to contact me via Mail at any time to discuss optimization or architecture technologies and to discuss and learn from each other.

Copyright Disclaimer: you are welcome to reprint it. I hope you can add the original article address while reprinting it. Thank you for your cooperation.