MySQL Cache Optimization (continued) and mysql Cache Optimization

MySQL Cache Optimization (continued) and mysql Cache Optimization

MySQL is cached everywhere. When can I read the MySQL source code and analyze in detail how the cache is used. This part mainly optimizes various explicit cache:

1. Query Cache Optimization
2. Result set Cache
3. Sort Cache
4. Join connection Cache
5. Table Cache and table schema definition Cache
6. Table scan cache buffer
7. MyISAM index cache buffer
8. Log Cache
9. Pre-read Mechanism
10. Latency table and temporary table
    

1. query Cache Optimization

The query cache not only caches the query statement structure, but also caches the query results. If the same SQL statement is used for a period of time, the results are directly read from the cache to improve data search efficiency. However, when the data in the cache is inconsistent with the data in 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_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 by block. The size of each requested block is set. 4 K is a reasonable value and does not need to be modified.

query_cache_sizeQuery the cache size.

query_cache_typeThe cache Query type. The values include 0 (OFF), 1 (ON), and 2 (DEMOND ). OFF indicates that the query cache is disabled. ON indicates that the query always goes to the query cache, unless the select statement contains the SQL _no_cache option. DEMOND indicates that the cache is not applicable unless the select statement contains the SQL _cache option.

Query_cache_wlock_invalidate: this parameter is used to set the relationship between the row-level exclusive lock and the query cache. The default value is 0 (OFF), indicating that when the row-level exclusive lock is applied, all query caches of the table are still valid. If it is set to 1 (ON), it indicates that the cache of all queries in the table is invalid when the row-level exclusion lock is used.

View 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 status information of the current cache:

Qcache_free_blocks

Indicates the number of memory blocks in the cache that are reproduced (the number of fragments ). If the Qcache_free_blocks value is large, it means that the query cache contains many fragments, indicating that the query result set is small. In this case, the value of query_cache_min_res_unit can be reduced. Using the flush query cache will sort several fragments in the cache to obtain a relatively large idle block. Cache fragmentation rate = Qcache_free_blocks/Qcache_total_blocks * 100%

Qcache_free_memory

It indicates the amount of memory available in the current MySQL service instance query cache.

Qcache_hits

Indicates the number of times the cache is queried. This value increases in turn. If Qcache_hits is large, it indicates that the query cache is frequently used. In this case, you need to increase the query cache.

Qcache_inserts

Queries the result set of a total of select statements cached in the cache.

Qcache_lowmen_prunes

Indicates the number of query results deleted by MySQL due to full query cache overflow. If the value is large, the query cache is too small.

Qcache_not_cached

Indicates the number of select statements that do not enter the query cache.

Qcache_queryies_in_cache

Queries the result set of the select statements cached in the cache.

Qcache_total_blocks

Query the total number of caches

The cache hit rate is calculated by Qcache_hits/Com_select * 100%.

Here, 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 the select statement that causes query cache failure due to frequent data changes. Therefore, the hit rate is generally low. If the cache hit rate is Qcache_hits/(Qcache_hits + Qcache_inserts), if this formula is used to calculate a high cache hit rate, this means that most select statements hit the query cache.

Run the following command to check the total number of select statements executed by the current system:

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

2. Result set Cache

The result set cache is session cache. After the MySQL client successfully connects to the server. The MySQL Server retains the result set cache for each MySQL client. Cache the connection information of the MySQL client connection thread and the result set information of the MySQL client returned by the cache. When the MySQL client sends a select statement to the server, mySQL stores the execution results of select statements 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 is automatically expanded, but the limit of max_allowd_packet is not exceeded:

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

3. Sort Cache

MySQL commonly uses two data storage engines: InnoDB and MyISAM. Therefore, when optimizing, each engine will adopt an optimization method suitable for its own engine. For 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, next, let's take a look at the engine optimization methods.

1. Normal sorting Cache

The sort cache is session cache. If the SQL statement sent by the client to the server contains the order by or group by clause designed for sorting. MySQL selects the corresponding Sorting Algorithm to sort on the general Sorting index, improving the sorting speed. The size of a common Sorting index is defined by the sort_buffer_size parameter. To increase the sorting speed, you should first add an appropriate index. Then, you should increase the Sorting 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 the 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. To sort each column, if the value length of this column is long, add this parameter to improve MySQL performance.

Max_sort_length

When 'ORDER BY' or 'group by' clause, use the first max_sort_length byte of the column to sort the data. After the sorting operation is completed, the sorting information is recorded in the current session status.

Sort_merge_passes

The number of times a temporary file is used to complete sorting. When sorting, MySQL first tries to complete sorting in the normal sorting cache. If the cache space is insufficient, MySQL uses the cache for multiple sorting. Store the sorting results in the temporary files and sort the data in the temporary files. The Sort_merge_passes value records the number of times files are sorted. Because File Sorting involves reading files, open the file handle, and close operations such as files. Therefore, the system consumes a lot to read files. by increasing the sort_buffer_size of the general sorting cache, we can reduce the number of times temporary files are sorted, thus increasing the sorting performance.

Sort_range

Number of times range sorting is used

Sort_rows

Number of sorted records

Sort_scan

Number of times of sorting by full table Scan

2. MyISAM sorting Cache

When we use the alter table statement or the create index statement to create an index for the MyISAM table or import some data using the load data infile path, these operations will cause the index to be rebuilt, during index reconstruction, index fields must be sorted. To speed up index reconstruction efficiency, MyISAM provides a sort cache for sorting indexes, these methods are sorted in the memory as much as possible. The size of the MyISAM sort cache is defined by myisam_sort_buffer_size. After the index is rebuilt, the cache is immediately released.

However, when the cache size for sorting exceeds the threshold of myisam_sort_buffer_size, you need to sort the index fields in the temporary file. The size of the temporary external file is set by the myisam_max_sort_file_size parameter. After the index is rebuilt, delete temporary files 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 you execute alter table and create index to create indexes, InnoDB provides three InnoDB sorting caches for sorting indexes. 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 Cache

The join cache is session cache. If two tables are connected but indexes cannot be used (the prerequisite for using join to connect to the cache), MySQL allocates a 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 connection cache size. For example, the default value is 256;

5. Table Cache and table schema definition Cache

When the MySQL service accesses tables in the database, MySQL actually reads files. MySQL data is stored in 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 to generate the file descriptor. The operating system delivers the file descriptor to MySQL before MySQL can perform CURD operations on the database. Opening a file and generating a file descriptor all consume system resources, resulting in access latency. MySQL caches opened files, including file descriptors, and does not need to open the file when accessing the file again, improving the efficiency of File Reading.

The table structure does not change frequently. when accessing a table, in addition to inserting the table into the MySQL table cache, MySQL also puts the table structure into the table structure definition cache for the 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 number of tables and views that can be cached.

Table_definition_cache

Set the number of frm table structures that can be stored

For MySQL MyISAM engine, the table structure includes MYI and MYD, and the table structure frm. when accessing the MyISAM engine, you need to open two files (MYI and MYD) at one time to generate two file descriptors.

Open_files_limit

Maximum number of opened 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 table-related status values:

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 Scan is divided into Sequential Scan and Random Scan.

Sequential Scan when the MyISAM table does not have an index, the query speed will be full table scanning, with low efficiency. To speed up full table scan, MySQL provides Sequential Scan cache (read buffer ). In this case, MySQL reads all the data blocks according to the storage order of the stored data. The data blocks read each time are cached in the Sequential Scan cache. When the read buffer is full, the data is returned to the upper-layer caller.

Random Scan

When the table is cached and scanned, the index fields of the table are placed in the memory and sorted first, and then searched for data on the hard disk in the order they have been captured.

7. MyISAM index cache buffer

By caching the content of the MYI index file, you can speed up index reading and indexing. The index cache only applies to the MyISAM table and is shared by all threads. When querying statements or updating indexes to access table data through indexes, MySQL first checks whether required index information already exists in the index cache, if an index is cached, 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. The index cache plays a vital role in the access performance of the MyISAM table.

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 index cache size. The default value is 8 Mb. We recommend that you upgrade the SDK.

Key_cache_block_size

Specify the block size of each index cache. We recommend that you set it to 4 K, that is, 4096.

Key_cache_division_limit

To effectively use the cache. By default, the MySQL drop-down cache is divided into two index cache areas: the warm area and hot area ). The key_cache_division_limit parameter divides the index cache into multiple regions as percentages. When the default value is 100, it indicates that the index cache only has a temperature zone and the LRU algorithm is enabled to remove the indexes in the index cache.

Key_cahe_age_threshold

Controls when the index in the hot zone of the warm area is upgraded and downgraded. If the value is less than 100, there is a hot zone. The mobile algorithm is similar to the LRU algorithm.

View the status values of index reading and index writing for the current MySQL service instance:

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

A binary log is recorded when Mysql creates or updates data. However, frequent I/O operations will significantly affect MySQL performance. Therefore, MySQL opened a binary log cache binlog_cache_size. First, write the operation to the binary log. After the operation is successful, write the binary log to the hard disk.

2. InnoDB redo log Cache

Before a transaction is committed, it will write the generated redo logs to the InnoDB redo log cache, and then InnoDB [select the machine] will execute the round robin policy to write the redo log files in the cache into ib_logfile0 and ib_logfile1 redo logs.

mysql> show variables like 'innodb_log_buffer_size';+------------------------+---------+| Variable_name     | Value  |+------------------------+---------+| innodb_log_buffer_size | 8388608 |+------------------------+---------+

The InnoDB redo log cache ensures that the redo logs generated during the transaction run are stored in the InnoDB log cache before the transaction is committed, but are not written into the redo log file. The write time 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 log files are replayed in the cache, they are written to the hard disk cache at a frequency once per second and updated to the hard disk at the same time.

1: When each transaction is committed, redo logs in the cache are written to the redo log file and written to the hard disk. This action is performed by default.

2: when a transaction is committed, it is written to the cache, but does not trigger synchronization from the file system to the hard disk, but also synchronizes the hard disk once per second.

9. Pre-read Mechanism

The pre-read mechanism mainly utilizes MySQL optimization: 1. The principle described in cache optimization. That is, local features, spatial locality, and temporal locality are not described here.

1. InnoDB pre-read Mechanism

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

Page is the smallest unit of InnoDB hard disk management. A zone consists of 64 consecutive data blocks. For sequential pre-read, innoDB prefers to place the data block where the data is located in the InnoDB cache pool. It can be predicted that subsequent data blocks will soon be accessed, therefore, these data blocks and the front data blocks are placed in the memory. Set the number of data blocks before and after the pre-read operation according to the innodb_read_ahead_threshold parameter.

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

2. Index cache pre-loading

The database administrator can use the MySQL Command load index into cache to pre-load the MyISAM Table index.

10. MyISAM Table delayed 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 feature reminds me of a similar feature in the project and inspired my own ideas.

Use: insert delyed into table values (*);

Delyed_insert_limit

The default value is 100. When 100 rows of records are inserted to the MySQL table with a delay, check whether the select statement is waiting for execution. If yes, the insert statement is paused.

Delayed_insert_timeout

If no data exists in the delayed queue within the timeout range, the delayed insertion thread is disabled.

Delayed_queue_size

The queue length of delayed insertion. exceeding the queue will block until there is enough space.

Max_delayed_threads

Number of threads with delayed insertion.

Batch delayed insertion of MyISAM tables

Similar to insert into table values (1), values (2), values (n ). MyISAM inserts data in batches. Put the inserted data into the cache first. When the cache is full or submitted, MySQL writes the cache to the hard disk at one time. Batch insert can greatly reduce the consumption of connection syntax analysis between MySQL clients and service machines, making it much faster to execute a single insert statement.

mysql> select @@global.bulk_insert_buffer_size/(1024*1024);+----------------------------------------------+| @@global.bulk_insert_buffer_size/(1024*1024) |+----------------------------------------------+|                    8.0000 |+----------------------------------------------+

The default batch insert size is 8 Mb. If necessary, you can set a larger value to Improve the Performance of batch inserts.

Index delay update of MyISAM table

Indexes can accelerate data retrieval. However, for updates, you not only need to modify the record, but you may also need to modify the index. Therefore, the index will slow down the data update operation, if the delay_key_write parameter of MySQL is set to 1 (ON), this defect can be remedied. After the feature is enabled, the update operation submits data updates to the hard disk. Index updates are all completed in the index cache. When you close the table, update it to the hard disk together to make the Index Update faster. Only valid for MyISAM.

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

InnoDB delayed update

Non-clustered Index Update usually results in random I/O, reducing InoDB performance. When updating (insert, delete, update = insert + delete) Non-clustered index data, the system first checks whether the non-clustered index page is located in the InnoDB cache pool. If it is directly updated, otherwise, record "Information Modification" in the update cache (change buffer)

This blog has a lot of content, which can be summarized and extracted for future reference. There is an overall framework for optimizing the entire MySQL, And the progress is gradual and gradual. These parameters do not need to be remembered. You can search for them in your blog or Baidu. You can complete the optimization process by understanding and understanding the technique. Knowing the principle is much easier than memorizing the boring principle. If you are interested in MySQL optimization, you can follow my blog to learn more about it.