MySQL is cached everywhere. When I read the source code of MySQL, I will analyze in detail how the cache is used. This part mainly optimizes various explicit caches:
- Query cache optimization
- Result Set Cache
- Sort cache
- join connection cache
- Table Cache and table structure definition cache Cache
- Table Scan cache buffer
- MyISAM index cache buffer
- Log Cache
- Reading mechanism
- Delay tables and temporary tables
1. Query cache optimization
Query cache not only caches the query statement structure, but also caches the query results. For a period of time, if it is the same SQL, the results will be read directly from the cache to improve the efficiency of finding data. However, when the data in the cache is inconsistent with the data in the hard disk, the cache will fail.
mysql> show variables like '%query_cache%'; +------------------------------+---------+ | Variable_name | Value | +------------------------------+---------+ | have_query_cache | YES | | query_cache_limit | 1048576 | | query_cache_min_res_unit | 4096 | | query_cache_size | 1048576 | | query_cache_type | OFF | | query_cache_wlock_invalidate | OFF | +------------------------------+---------+
have_query_cacheWhether query cache is supported.
query_cache_limitIf the result set size of a select statement exceeds the value of querycachelimit, the result set will not be added to the query cache.
query_cache_min_res_unitThe query cache applies for memory space based on blocks, and the block size applied for each time is the set value. 4K is a very reasonable value and does not need to be modified.
query_cache_sizeQuery the size of the cache.
query_cache_typeThe type of query cache is 0 (OFF), 1 (ON), and 2 (DEMOND). OFF means that the query cache is closed. ON means that the query is always searched in the query cache first, unless the sql_no_cache option is included in the select statement. DEMOND means that caching is not applicable unless the sql_cache option is included in the select statement.
query_cache_wlock_invalidate This parameter is used to set the relationship between the row-level exclusive lock and the query cache. The default is 0 (OFF), which means that all query caches of the table are still valid while applying the row-level exclusive. If set to 1 (ON), it means that all query caches of the table are invalidated while the row-level exclusive lock is locked.
View the hit rate of query cache
mysql> show status like 'Qcache%'; +-------------------------+---------+ | Variable_name | Value | +-------------------------+---------+ | Qcache_free_blocks | 1 | | Qcache_free_memory | 1031360 | | Qcache_hits | 0 | | Qcache_inserts | 0 | | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 0 | | Qcache_queries_in_cache | 0 | | Qcache_total_blocks | 1 | +-------------------------+---------+
Check the status information of the current cache:
Qcache_free_blocks
Indicates the number of memory blocks (number of fragments) in the query cache that are in the reproduced state. If the value of Qcache_free_blocks is large, it means that there are more fragments in the query cache, indicating that the query result set is small. At this time, the value of query_cache_min_res_unit can be reduced. Using flush query cache will organize several fragments in the cache, resulting in a relatively large free block. Cache fragmentation rate = Qcache_free_blocks/ Qcache_total_blocks * 100%
Qcache_free_memory
Indicates how much memory is available for the current MySQL service instance's query cache.
Qcache_hits
Indicates the number of times the query cache is used, and the value will increase in sequence. If Qcache_hits is relatively large, it means that the query cache is used very frequently, and the query cache needs to be added at this time.
Qcache_inserts
Indicates the result set of how many select statements have been cached in the query cache.
Qcache_lowmen_prunes
Indicates the number of query results that overflows because the query cache is full, resulting in MySQL deletion. If the value is larger, it means that the query cache is too small.
Qcache_not_cached
Indicates that the number of selects that have not entered the query cache
Qcache_queryies_in_cache
Indicates the result set of how many select statements are cached in the query cache
Qcache_total_blocks
Query the total number of caches
How to calculate the cache hit rate: Query the cache hit rate = Qcache_hits / Com_select * 100%
where Com_select is the number of select statements executed by the current MySQL instance. Generally, Com_select = Qcache_insert + Qcache_not_cached. Qcache_not_cached contains select statements that cause query cache to fail, so the hit rate is generally low. If the failure factor is aside, the hit rate of the query cache = Qcache_hits / (Qcache_hits + Qcache_inserts) If this formula is used to calculate that the hit rate of the query cache is relatively high, this means that most select statements hit the query cache.
Check how many select statements have been executed in the current system through the following command
mysql> show status like 'Com_select'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | Com_select | 1 | +---------------+-------+
2. Result set cache
The result set cache is the session cache, after the MySQL client successfully connects to the server. The MySQL server retains the result set cache for each MySQL client. Caches the connection information of the MySQL client connection thread and the cache returns the result set information of the MySQL client. When the MySQL client sends a select statement to the server, MySQL temporarily stores the execution result of the select statement in the result set cache. The cache size of the result set is defined by the net_buffer_length parameter value:
mysql> show variables like 'net_buffer_length'; +-------------------+-------+ | Variable_name | Value | +-------------------+-------+ | net_buffer_length | 16384 | +-------------------+-------+
If the result set exceeds the value set by net_buffer_length, the capacity will be automatically expanded, but not exceeding the threshold value of:max_allowd_packet:
mysql> show variables like 'max_allowed_packet'; +--------------------+---------+ | Variable_name | Value | +--------------------+---------+ | max_allowed_packet | 4194304 | +--------------------+---------+
3. Sort cache
MySQL is commonly used in two data storage engines: InnoDB and MyISAM. Therefore, when optimizing, each engine will adopt an optimization method that suits its own engine. Regarding the difference between MySQL and InnoDB table structure files and data log files, you can first read my blog MySQL log system to have a sufficient understanding of these basic concepts. Next, let’s look at the engine optimization method to be as boring as you can.
1. Ordinary sorting cache
The sort cache is a session cache. If the SQL statement sent by the client to the server contains the order by or group by clause for design sorting. MySQL will select the corresponding sorting algorithm to sort on ordinary sorting indexes to improve the sorting speed. The size of the normal sort index is defined by the sort_buffer_size parameter. If you want to improve the sorting speed, you should first add the appropriate index, and then you should increase the sort index cache sort_buffer_size.
mysql> select @@global.sort_buffer_size / 1024; +----------------------------------+ | @@global.sort_buffer_size / 1024 | +----------------------------------+ | 256.0000 | +----------------------------------+ 1 row in set (0.00 sec)
Next, let’s take a look at what parameters are related to sorting cache:
mysql> show variables like '%sort%'; +--------------------------------+---------------------+ | Variable_name | Value | +--------------------------------+---------------------+ | innodb_disable_sort_file_cache | OFF | | innodb_ft_sort_pll_degree | 2 | | innodb_sort_buffer_size | 1048576 | | max_length_for_sort_data | 1024 | | max_sort_length | 1024 | | myisam_max_sort_file_size | 9223372036853727232 | | myisam_sort_buffer_size | 8388608 | | sort_buffer_size | 262144 | +--------------------------------+---------------------+ mysql> show status like '%sort%'; +-------------------+-------+ | Variable_name | Value | +-------------------+-------+ | Sort_merge_passes | 0 | | Sort_range | 0 | | Sort_rows | 0 | | Sort_scan | 0 | +-------------------+-------+
max_length_for_sort_data
The default size is 1024 bytes. The sorting operation for each column is to increase MySQL performance by adding this parameter.
max_sort_length
When order by or group by, use the first max_sort_length bytes of the column to sort. After the sorting operation is completed, the information of this sorting will be recorded in the status of this session.
Sort_merge_passes
Number of times the sorting operation is completed using temporary files. When MySQL performs sorting operations, it first tries to complete the sorting in the normal sorting cache. If the cache space is not enough, MySQL will use the cache to sort it multiple times. And store the sorting results in each time in a temporary file, and finally sort the data in the temporary file once. The Sort_merge_passes value records the number of times a file is used for sorting. Since file sorting involves reading files, opening the file handle, and then closing the file. Therefore, the system for reading files consumes a lot. By increasing the normal sort cache sort_buffer_size, it reduces the number of times you use temporary file sorting, thereby increasing the sorting performance.
Sort_range
Number of times to sort using range
Sort_rows
Number of rows of records that have been sorted
Sort_scan
Number of sorting done through full table scan
2. MyISAM sorting cache
When we use alter table statement or create index statement to create an index of MyISAM table, or import part of data using load data infile path, these operations will cause the index to be reconstructed. When reconstructing the index, the index fields need to be sorted. In order to speed up the efficiency of reconstructing the index, MyISAM provides a sort cache to implement the sorting work of indexes. These methods are all done as much as possible in memory. The size of the MyISAM sort cache is defined by myisam_sort_buffer_size. After the index is rebuilt, the cache is released immediately.
However, when the sorting cache exceeds the threshold of myisam_sort_buffer_size, the sorting of the index field needs to be completed in the temporary file. The size of the external temporary file is set by the myisam_max_sort_file_size parameter. After the index is rebuilt, the temporary file will be deleted immediately.
mysql> select @@global.myisam_sort_buffer_size/1024; +---------------------------------------+ | @@global.myisam_sort_buffer_size/1024 | +---------------------------------------+ | 8192.0000 | +---------------------------------------+ mysql> select @@global.myisam_max_sort_file_size /1024; +------------------------------------------+ | @@global.myisam_max_sort_file_size /1024 | +------------------------------------------+ | 9007199254739967.7734 | +------------------------------------------+
3. InnoDB sorting cache
Similar to the MyISAM engine, when changing table and creating index are executed, InnoDB provides 3 InnoDB sorting caches to implement index sorting, and the size of each cache is defined by innodb_sort_buffer_size.
mysql> select @@global.innodb_sort_buffer_size/1024; +---------------------------------------+ | @@global.innodb_sort_buffer_size/1024 | +---------------------------------------+ | 1024.0000 | +---------------------------------------+
4. Join connection cache
Join cache is a session cache. If two tables are connected but the index cannot be used (the prerequisite for using join cache at this time), MySQL will allocate the join connection cache for each table.
mysql> select @@global.join_buffer_size/1024; +--------------------------------+ | @@global.join_buffer_size/1024 | +--------------------------------+ | 256.0000 | +--------------------------------+
join_buffer_size defines the size of the connection cache, as shown in the figure above, default is 256;
5. Table cache cache and table structure definition cache cache
When the MySQL service accesses tables in the database, MySQL actually does a file read operation. MySQL data is all files on the hard disk, which is different from some memory-type databases. When we query a table and use the select statement, we do not consider using the query cache. First, the operating system needs to open the file and generate the descriptor of the file. The operating system hand over the file descriptor to MySQL, and MySQL can only CURD operations on the database. Opening files and generating file descriptors requires consuming system resources, resulting in access delays. MySQL caches the already opened file, including the file descriptor. When accessing the file again in the future, there is no need to open the file, which improves the efficiency of reading the file.
The table structure does not change frequently. When accessing a table, in addition to implanting the table into MySQL's table cache, MySQL also puts the table structure into the table structure definition cache for next use.
mysql> show variables like 'table%'; +----------------------------+-------+ | Variable_name | Value | +----------------------------+-------+ | table_definition_cache | 1400 | | table_open_cache | 2000 | | table_open_cache_instances | 1 | +----------------------------+-------+ mysql> show variables like '%open%'; +----------------------------+----------+ | Variable_name | Value | +----------------------------+----------+ | have_openssl | DISABLED | | innodb_open_files | 2000 | | open_files_limit | 65535 | | table_open_cache | 2000 | | table_open_cache_instances | 1 | +----------------------------+----------+
table_open_cache
Set the limit on the number of tables and views that can be cached
table_definition_cache
Set how many frm table structures can be stored
For the MySQL MyISAM engine, the table structure includes MYI and MYD and the table structure frm. When accessing the MyISAM engine, two files (MYI and MYD) need to be opened at one time to generate two file descriptors.
open_files_limit
Open file upper limit
innodb_open_files
If the InnoDB table uses independent tablespace files (ibd), this parameter sets the number of files that can be opened at the same time.
The following are the status values related to opening the table:
mysql> show status like 'Open%'; +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | Open_files | 18 | | Open_streams | 0 | | Open_table_definitions | 70 | | Open_tables | 63 | | Opened_files | 125 | | Opened_table_definitions | 0 | | Opened_tables | 0 | +--------------------------+-------+
6. Table scan cache buffer
Table scanning is divided into two ways: Sequential Scan and Random Scan.
Sequential Scan When the MyISAM table does not have an index, the query speed will be performed for full table scanning, which is very inefficient. In order to improve the speed of full table scanning, MySQL provides a sequential scan cache (read buffer). At this time, MySQL reads out all data blocks according to the storage order of stored data. The data blocks read each time are cached in the order scan cache. After the read buffer is full, the data is returned to the upper caller.
Random scan
When there is a cache in the table and when scanning the table, the index field of the table will be placed in memory and the order will be taken first, and then search the data on the hard disk in the order it has been taken.
7. MyISAM index cache buffer
By caching the contents of MYI index files, you can speed up the speed of reading indexes and indexes. The index cache only works on the MyISAM table and is shared by all threads. When query statements or update indexes accessing table data through indexes, MySQL first checks whether the required index information already exists in the index cache. If there is an index in the cache, you can directly access the MYD file corresponding to the index. If not, the MYI file is read and the corresponding index data is read into the cache. Index cache plays a crucial role in the access performance of MyISAM tables.
mysql> show variables like 'key%'; +--------------------------+---------+ | Variable_name | Value | +--------------------------+---------+ | key_buffer_size | 8388608 (8M)| | key_cache_age_threshold | 300 | | key_cache_block_size | 1024 | | key_cache_division_limit | 100 | +--------------------------+---------+
key_buffer_size
Set the size of the index cache, the default is 8M. Recommended to improve.
key_cache_block_size
Specify the block size of each index cache, it is recommended to set it to 4K, that is, 4096
key_cache_division_limit
In order to use the cache effectively. By default, MySQL downcache is divided into two index cache areas, warm area and hot area. The key_cache_division_limit parameter is divided into multiple regions in the form of a percentage to the Zeng Ge index cache. When the default value is 100, it means that the index cache has only a temperature zone, and the LRU algorithm will be enabled to eliminate the index in the index cache.
key_cahe_age_threshold
Controls when indexes in hot zones in temperature zones are upgraded and when they are downgraded. If the value is less than 100, there is a hot zone. The movement algorithm is roughly similar to the LRU algorithm.
Check the status values of the current MySQL service instance index read and index write:
mysql> show status like 'Key%'; +------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | Key_blocks_not_flushed | 0 | | Key_blocks_unused | 6698 | | Key_blocks_used | 0 | | Key_read_requests | 0 | | Key_reads | 0 | | Key_write_requests | 0 | | Key_writes | 0 | +------------------------+-------+
8. Log Cache
Log cache is divided into binary log cache and InnoDB redo log cache
1. Binary log cache
mysql> show variables like '%binlog%cache%'; +----------------------------+----------------------+ | Variable_name | Value | +----------------------------+----------------------+ | binlog_cache_size | 32768 | | binlog_stmt_cache_size | 32768 | | max_binlog_cache_size | 18446744073709547520 | | max_binlog_stmt_cache_size | 18446744073709547520 | +----------------------------+----------------------+ mysql> show status like '%binlog%cache%'; +----------------------------+-------+ | Variable_name | Value | +----------------------------+-------+ | Binlog_cache_disk_use | 0 | | Binlog_cache_use | 0 | | Binlog_stmt_cache_disk_use | 0 | | Binlog_stmt_cache_use | 0 | +----------------------------+-------+
When Mysql creates or updates data, a binary log will be recorded. However, frequent I/O operations will have a great performance impact on MySQL. Therefore, MySQL has opened up a binary log cache binlog_cache_size. First write the operation to the binary log. When the operation is successful, write the binary log to the hard disk.
2. InnoDB redo log cache
Before commit, the generated redo log will be written to the InnoDB redo log cache, and then InnoDB [optional] executes the polling policy and writes the cached redo log files to the ib_logfile0 and ib_logfile1 redo logs.
mysql> show variables like 'innodb_log_buffer_size'; +------------------------+---------+ | Variable_name | Value | +------------------------+---------+ | innodb_log_buffer_size | 8388608 | +------------------------+---------+
InnoDB redo log cache ensures that before the transaction is committed, the redo log generated during the transaction is saved in the InnoDB log cache, but is not written to the redo log file. The write timing is controlled by the innodb_flush_log_at_trx_commit parameter.
mysql> show variables like 'innodb_flush_log%'; +--------------------------------+-------+ | Variable_name | Value | +--------------------------------+-------+ | innodb_flush_log_at_timeout | 1 | | innodb_flush_log_at_trx_commit | 1 | +--------------------------------+-------+
0: When the redo log file in the cache is written to the hard disk cache at a frequency once per second, and will be updated to the hard disk at the same time.
1: When each transaction is submitted, write the redo log in the cache to the redo log file and write it to the hard disk at the same time. This behavior is the default.
2: When the transaction is submitted, the cache is written to, but the synchronization operation between the file system and the hard disk does not trigger, but in addition, the hard disk is synchronized once every second.
9. Reading mechanism
The read-out mechanism mainly utilizes the principles described in the previous article MySQL optimization: 1. Cache optimization. That is, local characteristics, spatial locality, and temporal locality, which will not be repeated here.
1. InnoDB read preview mechanism
InnoDB adopts a read-premature mechanism to load "data to be accessed in the future" including indexes into the read-premature cache, thereby improving the read performance of the data. InnoDB supports two ways: linear read ahead and random read ahead.
The data block (page) is the smallest unit of InnoDB hard disk management. A zone consists of 64 consecutive data blocks. For sequential reading preview, InnoDB first chooses to place the data block where the data is located in the InnoDB cache pool. It can be predicted that the subsequent blocks of these data blocks will be accessed soon, so these data blocks and the pre-installed data blocks will be placed in memory. According to the innodb_read_ahead_threshold parameter, set how many data blocks are before and after reading.
mysql> show variables like 'innodb_read_ahead%'; +-----------------------------+-------+ | Variable_name | Value | +-----------------------------+-------+ | innodb_read_ahead_threshold | 56 | +-----------------------------+-------+
2. Index cache preload
Database administrators can use the MySQL command load index into cache to preload the MyISAM table index
10. MyISAM table delay insertion
mysql> show variables like '%delayed%'; +----------------------------+-------+ | Variable_name | Value | +----------------------------+-------+ | delayed_insert_limit | 100 | | delayed_insert_timeout | 300 | | delayed_queue_size | 1000 | | max_delayed_threads | 20 | | max_insert_delayed_threads | 20 | +----------------------------+-------+
Seeing this delayed insertion function, I remembered a slightly similar function in the project, which inspired my own ideas.
Use method: insert delyed into table values(*);
delyed_insert_limit
The default value is 100. After delaying insertion of 100 rows of records into the MySQL table, check whether there is a select statement waiting for execution in the table. If so, pause the execution of the insert statement.
delayed_insert_timeout
Within the timeout range, if there is no data in the delayed queue, the delayed insertion thread will be turned off.
delayed_queue_size
Delay the insertion queue length beyond which will block until there is enough space.
max_delayed_threads
The number of threads inserted delayed.
Batch delay insertion of MyISAM tables
Similar to insert into table values(1), values(2), values(n). MyISAM will perform batch insertion. First put the inserted data into the cache. When the cache is full or the submission is completed, MySQL writes the cache to the hard disk at one time. Through batch insertion, the connection syntax analysis and other consumption between MySQL clients and servers can be greatly reduced, making the efficiency much faster than executing a single insert statement separately.
mysql> select @@global.bulk_insert_buffer_size/(1024*1024); +----------------------------------------------+ | @@global.bulk_insert_buffer_size/(1024*1024) | +----------------------------------------------+ | 8.0000 | +----------------------------------------------+
The default batch insert size is 8M. If business needs are required, you can set it larger to improve the performance of batch insertion.
Index delay update of MyISAM table
Indexing can speed up data retrieval, but for updates, not only need to modify records, but also need to modify the index. Therefore, indexing will cause data update operations to slow down. If MySQL's delay_key_write parameter is set to 1 (ON), this defect can be made up. When the update operation is enabled, when modifying data, first submit the update of the data to the hard disk, and all the updates of the index are completed in the index cache. When closing the table, update to the hard disk together, so that the index can be updated faster. Only valid for MyISAM.
mysql> show variables like 'delay_key_write'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | delay_key_write | ON | +-----------------+-------+
InnoDB delayed update
The update operation of nonclustered indexes usually brings random I/O, reducing InoDB's performance. When updating the data of the nonclustered index (insert, delete, update=insert+delete), the nonclustered index page will be checked first whether the nonclustered index page is in the InnoDB cache pool. If it is updated directly, otherwise the "information modification" will be recorded in the update cache first (change buffer)
This blog has a lot of content, and it is summarized and refined for future review. There is a comprehensive framework for the optimization of the entire MySQL, and it is gradually progressing. These parameters can be found in the blog or Baidu without memory. By understanding Tao and knowing skills, you can complete the optimization process. Knowing the principle is much simpler than the principle of boring memory. Bloggers who are interested in MySQL optimization can follow my blog to see subsequent sharing.