Tips on MySQL query cache

Preface

We know that the design concept of cache is ubiquitous in RDBMS databases. Take the Oracle database, which is said to have 25 million lines of code and is full of bugs, for example. The SQL execution plan can be cached in the library cache to avoid hard parsing (syntactic analysis -> semantic analysis -> generation of execution plan) when the same SQL is executed again. The SQL execution results are cached in the RESULT CACHE memory component, which effectively converts physical IO into logical IO and improves SQL execution efficiency.

MySQL's QueryCache is similar to Oracle's. It caches SQL statement text and the corresponding result set. It seems to be a great idea. Then why is it disabled by default in MySQL 5.6, deprecated in 5.7, and removed in version 8.0 after the release of MySQL 4.0? Today, let's talk about the past and present of MySQL QueryCache.

Introduction to QueryCache

MySQL query cache (QC: QueryCache) was introduced in MySQL 4.0.1. The query cache stores the text of SELECT statements and the result set sent to the client. If the same SQL is executed again, the server will retrieve the result from the query cache and return it to the client instead of parsing and executing the SQL again. The query cache is shared between sessions, so the cached result set generated by one client can respond to another client executing the same SQL.

Back to the question at the beginning, how to determine whether SQL is shared?

The SQL text is judged by whether it is completely consistent, including uppercase, lowercase, space and other characters are exactly the same. It can be shared. The advantage of sharing is that it can avoid hard parsing and directly get the results from QC and return them to the client. The following two SQLs are not shared because one is from and the other is From.

--SQL 1
select id, balance from account where id = 121;
--SQL 2
select id, balance From account where id = 121;

The following is the algorithm that Oracle database uses to generate sql_id through SQL_TEXT. If the sql_id is different, it means that it is not the same SQL and is not shared, and hard parsing will occur.

#!/usr/bin/perl -w
use Digest::MD5 qw(md5 md5_hex md5_base64);
use Math::BigInt;
my $stmt = "select id, balance from account where id = 121\0"; 
my $hash = md5 $stmt; 
my($a,$b,$msb,$lsb) = unpack("V*",$hash);
my $sqln = $msb*(2**32)+$lsb;
my $stop = log($sqln) / log(32) + 1;
my $sqlid = '';
my $charbase32 = '0123456789abcdfghjkmnpqrstuvwxyz';
my @chars = split '', $charbase32;
for($i=0; $i < $stop-1; $i++){
 my $x = Math::BigInt->new($sqln);
 my $seq = $x->bdiv(32**$i)->bmod(32);
 $sqlid = $chars[$seq].$sqlid;
}
print "SQL is:\n $stmt \nSQL_ID is\n $sqlid\n";

You can find that the sql_id values ​​generated by the code in SQL 1 and SQL 2 are different, so they are not shared.

SQL is: select id, balance from account where id = 121 
SQL_ID is dm5c6ck1g7bds
SQL is: select id, balance From account where id = 121 
SQL_ID is 6xb8gvs5cmc9b

If you are asked to compare the contents of two Java code files to see the differences, you only need to understand this code thoroughly, and then you can transform it to implement your own business logic.

QueryCache Configuration

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 | 16777216 |
| query_cache_type | OFF |
| query_cache_wlock_invalidate | OFF |

query_cache_min_res_unit description

The default size is 4KB. If you have many queries with small result sets, the default chunk size may cause memory fragmentation. Fragmentation may force the query cache to remove queries from the cache due to insufficient memory.

In this case, you can reduce the value of query_cache_min_res_unit. The number of free blocks and queries removed due to pruning is given by the values ​​of the Qcache_free_blocks and Qcache_lowmem_prunes status variables. If a large number of queries have large result sets, you can increase the value of this parameter to improve performance.

Usually open QueryCache mode

# Modify the MySQL configuration file /etc/my.cnf, add the following configuration, and restart the MySQL server.
[mysqld]
query_cache_size = 32M
query_cache_type = 1

QueryCache usage

Let's get some test data first and test the scenarios with QueryCache disabled and enabled.

--Create a user table users and insert 1 million data.
CREATE TABLE `users` (
 `id` bigint NOT NULL AUTO_INCREMENT,
 `name` varchar(20) NOT NULL DEFAULT '' COMMENT 'Name',
 `age` tinyint NOT NULL DEFAULT '0' COMMENT 'age',
 `gender` char(1) NOT NULL DEFAULT 'M' COMMENT 'Gender',
 `phone` varchar(16) NOT NULL DEFAULT '' COMMENT 'Phone number',
 `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Creation time',
 `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'modification time',
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='User information table';

select count(*) from users;
+----------+
| count(*) |
+----------+
| 1000000 |

Disable queryCache scenario

When QueryCache is not used, a hard parse will occur each time the same query statement is executed, consuming a lot of resources.

#Disable QueryCache configuration query_cache_size = 0
query_cache_type = 0

Repeat the following query and observe the execution time.

--First execution of query statementmysql> select * from users order by create_time desc limit 10;
+---------+------------+-----+--------+-------------+---------------------+---------------------+
| id | name | age | gender | phone | create_time | update_time |
+---------+------------+-----+--------+-------------+---------------------+---------------------+
| 997855 | User997854 | 54 | M | 15240540354 | 2020-12-15 14:34:50 | 2020-12-15 14:34:50 |
.......
10 rows in set (0.89 sec)
--Execute the same query for the second timemysql> select * from users order by create_time desc limit 10;
+---------+------------+-----+--------+-------------+---------------------+---------------------+
| id | name | age | gender | phone | create_time | update_time |
+---------+------------+-----+--------+-------------+---------------------+---------------------+
| 997855 | User997854 | 54 | M | 15240540354 | 2020-12-15 14:34:50 | 2020-12-15 14:34:50 |
.......
10 rows in set (0.90 sec)
-- Profile tracking mysql> show profile cpu,block io for query 1; 
+----------------------+----------+----------+------------+--------------+---------------+
| Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+----------------------+----------+----------+------------+--------------+---------------+
| preparing | 0.000022 | 0.000017 | 0.000004 | 0 | 0 |
| Sorting result | 0.000014 | 0.000009 | 0.000005 | 0 | 0 |
| executing | 0.000011 | 0.000007 | 0.000004 | 0 | 0 |
| Sending data | 0.000021 | 0.000016 | 0.000004 | 0 | 0 |
| Creating sort index | 0.906290 | 0.826584 | 0.000000 | 0 | 0 |

It can be seen that the execution time of executing the same SQL query statement multiple times is about 0.89s, with almost no difference. At the same time, the time is mainly consumed in the Creating sort index stage.

Enable queryCache scenario

When query caching is enabled, the SQL text and query results will be cached in QC when the query statement is executed for the first time. The next time the same SQL statement is executed, the data will be obtained from QC and returned to the client.

#Disable QueryCache configuration query_cache_size = 32M
query_cache_type = 1

--First execution of query statementmysql> select * from users order by create_time desc limit 10;
+---------+------------+-----+--------+-------------+---------------------+---------------------+
| id | name | age | gender | phone | create_time | update_time |
+---------+------------+-----+--------+-------------+---------------------+---------------------+
| 997855 | User997854 | 54 | M | 15240540354 | 2020-12-15 14:34:50 | 2020-12-15 14:34:50 |
.......
10 rows in set (0.89 sec)
--Execute the query statement for the second timemysql> select * from users order by create_time desc limit 10;
+---------+------------+-----+--------+-------------+---------------------+---------------------+
| id | name | age | gender | phone | create_time | update_time |
+---------+------------+-----+--------+-------------+---------------------+---------------------+
| 997855 | User997854 | 54 | M | 15240540354 | 2020-12-15 14:34:50 | 2020-12-15 14:34:50 |
.......
10 rows in set (0.00 sec)
-- profile tracking datamysql> show profile cpu,block io for query 3;
+--------------------------------+----------+----------+------------+--------------+---------------+
| Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+--------------------------------+----------+----------+------------+--------------+---------------+
| Waiting for query cache lock | 0.000016 | 0.000015 | 0.000001 | 0 | 0 |
| checking query cache for query | 0.000007 | 0.000007 | 0.000000 | 0 | 0 |
| checking privileges on cached | 0.000004 | 0.000003 | 0.000000 | 0 | 0 |
| checking permissions | 0.000034 | 0.000033 | 0.000001 | 0 | 0 |
| sending cached result to client | 0.000018 | 0.000017 | 0.000001 | 0 | 0 |

It can be seen that the first time QueryCache is executed, no SQL text and data are cached, and the execution time is 0.89s. Since QC is enabled, the SQL text and execution results are cached in QC. The second time the same SQL query statement is executed, QC is directly hit and data is returned without hard parsing, so the execution time is reduced to 0s. From the profile, we can see that sending cached result to client directly sends the data in QC back to the client.

Query cache hit rate

Query cache-related status variables

mysql>SHOW GLOBAL STATUS LIKE 'QCache\_%';
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| Qcache_free_blocks | 1 | --The number of free memory blocks in the query cache.
| Qcache_free_memory | 33268592 | --Amount of free memory for the query cache.
| Qcache_hits | 121 | --The number of times the result set is obtained from QC.
| Qcache_inserts | 91 | --The number of times a query result set was added to the QC, meaning the query is no longer in the QC.
| Qcache_lowmem_prunes | 0 | -- Number of queries removed from the query cache due to low memory.
| Qcache_not_cached | 0 | --Number of uncached queries.
| Qcache_queries_in_cache | 106 | --The number of queries registered in the query cache.
| Qcache_total_blocks | 256 | -- Total number of blocks in the query cache.

Query cache hit rate and average size

           Qcache_hits
Query cache hit rate = ------------------------------------------------ x 100%
      Qcache_hits + Qcache_inserts + Qcache_not_cached
      
        query_cache_size = Qcache_free_memory
Query Cache Avg Query Size = --------------------------------------- 
          Qcache_queries_in_cache

Impact of update operations on QC

For example, in the transfer logic of the payment system, you need to lock the account first and then modify the balance. The main steps are as follows:

Why abandon QueryCache?

General business scenarios

Scenarios suitable for QueryCache

First, the size of the query cache QC is only a few MB, so it is not suitable to set the cache too large. Since the thread needs to lock the QueryCache during the update process, you may see lock contention issues for very large caches. So, what situations are conducive to benefiting from query caching? The following are ideal conditions:

1. The same query is issued repeatedly by the same or multiple clients.
2. The underlying data being accessed is static or semi-static in nature.
3. The query may be resource intensive and/or construct a brief but computationally complex result set, while the result set is small.
4. Concurrency and query QPS are not high.

These four situations are just ideal situations. Actual business systems all have CRUD operations, data updates are relatively frequent, and the QPS of the query interface is relatively high. Therefore, there are very few business scenarios that can meet the above ideal situations. What I can think of is that configuration tables and data dictionary tables are basically static or semi-static, and QC can be used to improve query efficiency.

Scenarios Not Suitable for QueryCache

If the table data changes quickly, the query cache will become invalid and the server load will increase, causing processing to become slower due to constant removal of queries from the cache. If the data is updated every few seconds or more frequently, the query cache is unlikely to be appropriate.

At the same time, the query cache uses a single mutex to control access to the cache, which actually imposes a single-threaded gateway on the server SQL processing engine. When the query QPS is relatively high, it may become a performance bottleneck and seriously reduce the query processing speed. Therefore, the query cache is disabled by default in MySQL 5.6.

Deleting QueryCache

The query cache is deprecated as of MySQL 5.7.20, and is removed in MySQL 8.0. Deprecation includes query_cache_type. From the default disabling of MySQL 5.6, to the abandonment of 5.7 and the complete removal of 8.0, Oracle made this choice after comprehensive consideration of various aspects.

We discussed the business scenarios that are suitable and unsuitable for QueryCache above. We found that this feature is too demanding on business scenarios and is difficult to match with actual business. Moreover, after it is enabled, the database concurrency and processing capacity will be greatly reduced. The following summarizes the main reasons why MySQL went from Disabled->Deprecated->Removed QueryCache.

At the same time, query cache fragmentation will also increase the server load and affect the stability of the database. Searching for QueryCache on Oracle's official website reveals that there are many bugs, which is why MySQL 8.0 directly and decisively removes this feature.

Summarize

The above introduces the process of MySQL QueryCache from launch -> disable -> abandon -> delete. It was originally designed to reduce the hard parsing overhead caused by repeated SQL queries and convert physical IO into logical IO to improve SQL execution efficiency. However, MySQL has gone through multiple versions of iterations. At the same time, with the rapid development of hardware storage today, QC has almost no benefits and will also reduce the concurrent processing capability of the database. Finally, it was directly removed in version 8.0.

In fact, cache design ideas are everywhere in the fields of hardware and software. In terms of hardware: RAID cards and CPUs have their own caches. In terms of software, there are too many, including OS cache, database buffer pool, and Java program cache. As a R&D engineer, it is very important to choose a suitable cache solution according to the business scenario. If none of them is suitable, you need to customize the cache development to better match your own business scenario. That’s all for today, and I hope it will be helpful to everyone.

I am Ao Bing. The more you know, the more you don’t know. Thank you all for your likes, favorites and comments. See you next time!

The above is the detailed content of the little knowledge about MySQL query cache. For more information about MySQL query cache, please pay attention to other related articles on 123WORDPRESS.COM!