MySQL Optimization: Cache Optimization (Continued)

There are caches everywhere inside MySQL. When I read the source code of MySQL, I will analyze in detail how the cache is used. This section mainly optimizes various explicit caches:

1. Query cache optimization
2. Result Set Cache
3. Sorting Cache
4. join connection cache
5. Table cache Cache and table structure definition cache Cache
6. Table scan buffer
7. MyISAM index cache buffer
8. Log Cache
9. Pre-reading mechanism
10. Delayed tables and temporary tables
    

1. Query cache optimization

The query cache not only caches the query statement structure, but also caches the query results. If the SQL is the same within a period of time, the results are read directly from the cache to improve the efficiency of searching for data. But when the data in the cache is inconsistent with the data on the hard disk, the cache will become invalid.

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_cache Whether query cache is supported.

query_cache_limit If 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_unit The query cache applies for memory space in blocks, and the size of each block applied is the set value. 4K is a very reasonable value and does not need to be modified.

query_cache_size The size of the query cache.

query_cache_type query cache type, the values ​​are 0 (OFF), 1 (ON), 2 (DEMOND). OFF means query cache is disabled. ON means that the query always searches the query cache first, unless the sql_no_cache option is included in the select statement. DEMOND means that caching is not applied unless the sql_cache option is included in the select statement.

query_cache_wlock_invalidate This parameter is used to set the relationship between row-level exclusive locks and query caches. The default value is 0 (OFF), which means that while row-level exclusive locks are applied, all query caches of the table remain valid. If set to 1 (ON), it means that all query caches for the table are invalidated while the row-level exclusive lock is granted.

View the query cache hit rate

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 |
+-------------------------+---------+

View the current cache status information:

Qcache_free_blocks

Indicates the number of memory blocks (number of fragments) in the query cache that are in a recurring state. If the value of Qcache_free_blocks is large, it means that there are many fragments in the query cache, indicating that the query result set is small. In this case, you can reduce the value of query_cache_min_res_unit. Using flush query cache will sort out several fragments in the cache to get a larger free block. Cache fragmentation rate = Qcache_free_blocks/ Qcache_total_blocks * 100%

Qcache_free_memory

Indicates how much available memory is available for the query cache of the current MySQL service instance.

Qcache_hits

Indicates the number of times the query cache is used. The value increases sequentially. If Qcache_hits is relatively large, it means that the query cache is used very frequently, and you need to increase the query cache.

Qcache_inserts

Indicates the total number of select statement result sets that have been cached in the query cache.

Qcache_lowmen_prunes

Indicates the number of query results that MySQL deleted because the query cache was full and overflowed. If this value is large, it indicates that the query cache is too small.

Qcache_not_cached

Indicates the number of selects that have not entered the query cache

Qcache_queryies_in_cache

Indicates how many select statement result sets are cached in the query cache

Qcache_total_blocks

The total number of query caches

Cache hit rate calculation method: Query cache hit rate = Qcache_hits / Com_select * 100%

Com_select is the number of select statements executed by the current MySQL instance. In general, Com_select = Qcache_insert + Qcache_not_cached. Qcache_not_cached contains select statements that cause query cache invalidation due to frequent data changes, so the hit rate is generally low. If we ignore the factor of invalidation, the query cache hit rate = Qcache_hits / (Qcache_hits + Qcache_inserts) If the query cache hit rate calculated using this formula is relatively high, it means that most select statements hit the query cache.

Use the following command to view how many select statements are executed in the current system

mysql> show status like 'Com_select';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_select | 1 |
+---------------+-------+

2. Result Set Cache

The result set cache is a session cache that is created after the MySQL client successfully connects to the server. The MySQL server maintains a result set cache for each MySQL client. Cache the connection information of the MySQL client connection thread and cache the result set information returned to 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 result set buffer size 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 is automatically expanded, but it does not exceed the threshold value of max_allowd_packet:

mysql> show variables like 'max_allowed_packet';
+--------------------+---------+
| Variable_name | Value |
+--------------------+---------+
| max_allowed_packet | 4194304 |
+--------------------+---------+

3. Sorting Cache

MySQL commonly uses two data storage engines: InnoDB and MyISAM. Therefore, when optimizing, each engine will use an optimization method that suits its own engine. Regarding the differences 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. Then you can read the engine optimization methods to be able to do it easily and not feel bored.

1. Ordinary sort cache

The sort cache is a session cache if the SQL statement sent by the client to the server contains an order by or group by clause for design sorting. MySQL will select the corresponding sorting algorithm and sort on the common sorting index to improve the sorting speed. The size of the normal sort index is defined by the sort_buffer_size parameter. If you want to increase the sort speed, you should first add a suitable index, and then 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 the parameters related to sort 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 is performed on each column. If the value length of the column is long, MySQL performance can be improved by increasing this parameter.

max_sort_length

When order by or group by is used, the first max_sort_length bytes of the column are used for sorting. After the sorting operation is completed, the sorting information is recorded in the status of this session.

Sort_merge_passes

The number of times a sort operation was completed using a temporary file. When MySQL performs a sort operation, it first tries to complete the sort in the normal sort cache. If the cache space is insufficient, MySQL will use the cache to perform multiple sorts. And store each sorting result in a temporary file, and finally sort the data in the temporary file again. The Sort_merge_passes value records the number of times the file is sorted. Because file sorting involves reading files, opening file handles, and then closing files. Therefore, the system consumption of reading files is relatively large. By increasing the ordinary sort cache sort_buffer_size, the number of times temporary files are used for sorting can be reduced, thereby improving the sorting performance.

Sort_range

The number of times a range sort is used

Sort_rows

The number of rows that have been sorted

Sort_scan

Number of times the sort was completed via a full table scan

2. MyISAM sort cache

When we use the alter table statement or create index statement to create an index for a MyISAM table, or import a portion of data using load data infile path, these operations will cause the index to be rebuilt. When rebuilding the index, the index field needs to be sorted. In order to speed up the efficiency of rebuilding the index, MyISAM provides a sort cache to implement the index sorting work. These methods try to complete the sorting work 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, it is necessary to complete the sorting of the index fields in a 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 is 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 sort cache

Similar to the MyISAM engine, when executing alter table or create index, InnoDB provides three InnoDB sort buffers for index sorting. 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

The join cache is a session cache. If two tables are connected but the index cannot be used (this is the premise for using the join cache), MySQL will allocate a join 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 buffer, as shown above, the default is 256;

5. Table Cache and Table Structure Definition Cache

When the MySQL service accesses a table in the database, MySQL actually performs a file read operation. MySQL data is stored in files on the hard disk, which is different from some memory-based databases. When we query a table using a select statement, without considering the use of query cache, the operating system must first open the file and generate a descriptor for the file. The operating system hands the file descriptor to MySQL, so that MySQL can perform CURD operations on the database. Opening files and generating file descriptors consumes system resources and causes access delays. MySQL caches the opened files, including the file descriptors, so that when you access the file again in the future, you do not need to open the file, which improves the efficiency of reading files.

The table structure does not change often. When a table is accessed, 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

Sets a limit on the number of tables and views that can be cached

table_definition_cache

Sets how many frm table structures can be stored

For the MySQL MyISAM engine, the table structure includes MYI and MYD as well as 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

Maximum number of open files

innodb_open_files

If the InnoDB table uses an independent tablespace file (ibd), this parameter sets the number of files that can be opened at the same time.

The following are status values ​​related to opening a 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 buffer

Table scans are divided into two types: sequential scan and random scan.

When a MyISAM table is not indexed, the query speed will be a full table scan, which is very inefficient. To increase the speed of full table scans, MySQL provides a sequential scan cache (read buffer). At this time, MySQL reads all the data blocks according to the storage order of the stored data. Each data block read is cached in the sequential scan cache. When the read buffer is full, the data is returned to the upper-level caller.

Random Scan

When there is a cache in the table, when scanning the table, the index fields of the table will be put into the memory and sorted first, and then the data will be searched on the hard disk according to the sorted order.

7. MyISAM index cache buffer

By caching the contents of the MYI index file, you can speed up the speed of reading the index and the speed of indexing. The index cache only works for MyISAM tables and is shared by all threads. When a query statement or update index accesses table data through an index, MySQL first checks whether the required index information already exists in the index cache. If so, the MYD file corresponding to the index can be directly accessed through the index in the cache. If not, the MYI file is read and the corresponding index data is read into the cache. The index cache plays a vital 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. Recommend improvement.

key_cache_block_size

Specify the block size of each index cache. It is recommended to set it to 4K, i.e. 4096

key_cache_division_limit

To use cache effectively. By default, MySQL cache is divided into two index cache areas, warm area and hot area. The key_cache_division_limit parameter divides the index cache into multiple regions in the form of percentages. When the default value is 100, it means that the index cache has only a warm zone, and the LRU algorithm will be enabled to eliminate indexes in the index cache.

key_cahe_age_threshold

Control when indexes in warm and hot zones are promoted or demoted. If the value is less than 100, there is a hot spot. The moving algorithm is roughly similar to the LRU algorithm.

View 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

The 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, it records a binary log. However, frequent I/O operations will have a significant impact on MySQL performance. Therefore, MySQL has opened up a binary log cache binlog_cache_size. First, the operation is written to the binary log, and when the operation is successful, the binary log is written to the hard disk.

2. InnoDB redo log cache

Before a transaction is committed, the generated redo logs are written to the InnoDB redo log cache. Then InnoDB [selects an opportunity] to execute the polling strategy and write the redo log files in the cache 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 can ensure that the redo logs generated during the transaction are saved in the InnoDB log cache before the transaction is committed, but are not written to the redo log file. The timing of writing 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 disk cache once per second, it is also updated to the disk at the same time.

1: Each time a transaction is committed, the redo log in the cache is written to the redo log file and written to the hard disk at the same time. This is the default behavior.

2: When a transaction is committed, it is written to the cache, but does not trigger the synchronization operation from the file system to the hard disk. In addition, the hard disk is synchronized once a second.

9. Pre-reading mechanism

The pre-reading mechanism mainly uses the principles described in the previous MySQL optimization: 1. Cache optimization. That is, local characteristics, spatial locality, and temporal locality, which will not be elaborated here.

1. InnoDB read-ahead mechanism

InnoDB uses a read-ahead mechanism to load "data to be accessed in the future" including indexes into the read-ahead cache, thereby improving data reading performance. InnoDB supports two methods: linear read ahead and random read ahead.

A data block (page) is the smallest unit of InnoDB hard disk management. A zone consists of 64 consecutive data blocks. For sequential read-ahead, InnoDB prefers to place the data blocks where the data is located into 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 preceding data blocks will be placed in the memory. Set the number of data blocks to read ahead based on the innodb_read_ahead_threshold parameter.

mysql> show variables like 'innodb_read_ahead%';
+-----------------------------+-------+
| Variable_name | Value |
+-----------------------------+-------+
| innodb_read_ahead_threshold | 56 |
+-----------------------------+-------+

2. Index cache preloading

Database administrators can use the MySQL command load index into cache to preload MyISAM table indexes.

10. Delayed insertion of MyISAM tables

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 reminded me of a somewhat similar function in the project, which inspired my own ideas.

The usage is: insert delyed into table values(*);

delyed_insert_limit

The default value is 100. After 100 rows are inserted into the MySQL table, check whether there is a select statement waiting to be executed in the table. If so, suspend 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 shut down.

delayed_queue_size

The queue length for delayed insertions, exceeding which will block until there is enough space.

max_delayed_threads

The number of threads for delayed inserts.

Batch delayed inserts into MyISAM tables

Similar to insert into table values(1),values(2),values(n). MyISAM will do batch inserts. Put the inserted data into the cache first. When the cache is full or committed, MySQL writes the cache to disk at once. Batch insert can greatly reduce the connection syntax analysis and other costs between the MySQL client and the server, making the efficiency much faster than executing individual insert statements 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 necessary for business purposes, you can set it larger to improve batch insert performance.

Delayed update of indexes for MyISAM tables

Indexes can speed up data retrieval, but for updates, not only do you need to modify the records, you may also need to modify the indexes. Therefore, indexes will slow down data update operations. If you set the MySQL delay_key_write parameter to 1 (ON), you can make up for this shortcoming. When the update operation is enabled, the data update is first submitted to the hard disk when the data is modified, and the index update is all completed in the index cache. When closing the table, update it to the hard disk together, so that the index can be updated faster. Valid only for MyISAM.

mysql> show variables like 'delay_key_write';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| delay_key_write | ON |
+-----------------+-------+

InnoDB Delayed Updates

Update operations on non-clustered indexes usually cause random I/O and reduce the performance of InoDB. When updating (insert, delete, update=insert+delete) the data of a non-clustered index, it will first check whether the non-clustered index page is in the InnoDB buffer pool. If it is, it will be updated directly. Otherwise, the "information modification" will be recorded in the update buffer first.

This blog contains a lot of content, so I summarize it for later review. First, we need to have an overall framework for optimizing the entire MySQL, and then make gradual progress. You don't need to remember these parameters. When you need them, just look them up in the blog or on Baidu. If you understand the Tao and know the technique, you can complete the optimization process. It is much easier to know the principle than to memorize boring principles. Bloggers who are interested in MySQL optimization can follow my blog to see subsequent sharing.