MySQL middleware proxysql (9): Proxysql Query caching function

Back to Proxysql series article: http://www.cnblogs.com/f-ck-need-u/p/7586194.html

Proxysql supports the ability of query caching to cache the result set returned by the backend in its own memory, and before the cache entry for a query is cleaned up (for example, expired), the front end initiates the same query statement, fetching the data directly from the cache and returning it to the front end. As a result, the performance of proxysql processing can be significantly increased, and the pressure on the backend MySQL server will be greatly reduced.

1. Turn on the query cache function

The query caching feature of Proxysql ismysql_query_rulescontrolled by the Cache_ttl field in the table, which sets the cache duration for each rule, in milliseconds.

The SQL statement sent by the current side hits a rule (strictly speaking, it is the last rule to apply, because a chain rule operates multiple rules), and if the rule also sets the value of the "Cache_ttl" field, the result returned by this SQL statement will be cached for a certain amount of time, After it expires, it waits for a dedicated thread (purge thread) to clean up.

For example:

delete from mysql_query_rules;
select * from stats_mysql_query_digest_reset where 1=0;

insert into mysql_query_rules(rule_id,active,apply,destination_hostgroup,match_pattern,cache_ttl) 
values(1,1,1,10,"^select .* test1.t1",20000);

load mysql query rules to runtime;
save mysql query rules to disk;

select rule_id,destination_hostgroup,match_pattern,cache_ttl from mysql_query_rules;

This means that the query result set that matches the above rule (query Test1.t1 table) will be cached on Proxysql for 20 seconds.

The following statements can be executed for testing, with each statement looping 10 times:

# Executed under bash
proc = "mysql -uroot [email protected]! -h127.0.0.1 -P6033 -e"
for ((i = 0; i <10; i ++)); do
     $ proc "select * from test1.t1;"
     $ proc "select * from test1.t2;"
     $ proc "select * from test2.t1;"
     $ proc

Check out the rules:

Admin> select * from stats_mysql_query_rules;
+ --------- + ------ +
| rule_id | hits |
+ --------- + ------ +
| 1 | 10 | <-rule_id = 1 hits 10 times
+ --------- + ------ +

Admin> select hostgroup, count_star, sum_time, digest_text from stats_mysql_query_digest;
+ ----------- + ------------ + ---------- + ------------- ----------- +
hostgroup | count_star | sum_time | digest_text |
+ ----------- + ------------ + ---------- + ------------- ----------- +
| -1 | 9 | 0 | select * from test1.t1 | <-9 times, hg = "-1"
| 10 | 10 | 3640 | select * from test2.t1 |
| 10 | 10 | 3686 | select * from test2.t2 |
| 10 | 10 | 3986 | select * from test1.t2 |
| 10 | 1 | 1236 | select * from test1.t1 | <-1 time, hg = "10"
+ ----------- + ------------ + ---------- + ------------- ----------- +

Whichhostgroup=-1means fetching data from the cache.

From the above results, it can be seen that theselect * from test1.t1;statement in addition to the normal route to the back end of the execution of the other 9 times is directly from the cache to get the data, and cache the data of the total statement execution time sum_time=0.

Not all query results should be cached, Proxysql cache space is limited after all. So it is necessary tostats_mysql_query_digestfind out which statements should be cached in the table: many executions, long execution times, and long average execution times should be cached. These indicators are also reasonable to determine the reading and writing separation indicators.

2. Querying cache-related statistics

Proxysql Although the query cache has been implemented, but the query cache design is not complete, missing some of the icing on the cake, such as the number of cache class indicators can be queried less, the way to control the cache is also relatively scarce. However, the author has been trying to improve and improve constantly.

The following are the status variables related to query caching:

Admin> SELECT * FROM stats_mysql_global WHERE Variable_Name LIKE '%Cache%';
+--------------------------+----------------+
| Variable_Name            | Variable_Value |
+--------------------------+----------------+
| Stmt_Cached              | 0              |
| Query_Cache_Memory_bytes | 0              |
| Query_Cache_count_GET    | 0              |
| Query_Cache_count_GET_OK | 0              |
| Query_Cache_count_SET    | 0              |
| Query_Cache_bytes_IN     | 0              |
| Query_Cache_bytes_OUT    | 0              |
| Query_Cache_Purged       | 0              |
| Query_Cache_Entries      | 0              |
+--------------------------+----------------+

The meanings of each variable are as follows:

• query_cache_memory_bytes: The total size of the query result set that was successfully cached in memory and does not contain those meta data;
  
• query_cache_count_get: Total number of requests to fetch data from the query cache (GET requests);
  
• QUERY_CACHE_COUNT_GET_OK: Total number of requests successfully from cache to cache (i.e. hit cache and cache not expired);
  
• Query_cache_count_set: The total number of result sets cached in the query cache (that is, how many query result sets are cached);
  
• query_cache_bytes_in: The total amount of data written to the query cache;
  
• query_cache_bytes_out: The total amount of data fetched from the query cache;
  
• query_cache_purged: The number of cached result sets (Cache records) removed from the cache (purged);
  
• query_cache_entries: How many cache records are in the current query cache.

The specific cache record information in the current cache space cannot be queried.

3.ProxySQL Query Cache Details

Such as:

Proxysqlmysql_query_cache_size_MBThe amount of space the query cache opens up through variable control, whichmysql-threshold_resultset_sizedefines the size of a single maximum result set that the Proxysql can cache.

Admin> show  variables like '%size%';
+--------------------------------+-----------+
| Variable_name                  | Value     |
+--------------------------------+-----------+
| mysql-eventslog_filesize       | 104857600 |
| mysql-threshold_resultset_size | 4194304   |  <<<<
| mysql-query_cache_size_MB      | 256       |  <<<<
| mysql-stacksize                | 1048576   |
+--------------------------------+-----------+

Proxysql The result set returned by the back end into the query cache space through a set call , and a get call takes the cached data out of the query cache space back to the front end. When the get call discovers that the cache record has expired, it is put into the purge queue .

The purge thread periodically cleans up expired records in the purge queue and periodically scans the cache space to find out the expired records. When the query cache space is full (mysql-query_cache_size_MB), the next set call will also trigger the purge thread to clean up cached records that have expired. Therefore, the set call never fails. Note, however, that if the cached content consumes less than 3% of the cache space, the purge thread will not clean up anything, even if they have expired.

The data structure of the cache record is as follows:

key:value:length:expire_ms:access_ms:ref_count

which

1. Key/value are both the ID of the cache and the actual cached data, and key is based onusername + schemaname + SQL statementthe value of the hash operation, which guarantees that the user will only be able to access the result set of its own, specified schema.
   
2. length is used to record the size of the cache record. Becausemysql-threshold_resultset_sizeof the variable, the extra result set that exceeds the threshold of the variable is not put into the cache.
   
3. Expire_ms: Used to record how long each cache record has expired.
   
4. Access_ms: Records how long each cache record was last accessed from now.
   
5. ref_count: Records the current number of references to each cache record. Each get calls a cache record, and the ref_count of the cache record is added 1, and the call is completed minus 1. This is to prevent the cached record that is being called from expiring and being cleaned up by purge thread discovery.

Therefore, the 3 fields that follow are designed to expire the cache record. However, currently proxysql does not support Access_ms to determine whether to clean up, such as cache space is full, and Access_ms time has passed long, but as long as it has not expired, it will not be cleaned up. This field may be prepared for subsequent LRU (or other similar cache cleanup algorithms).

4. Easy-to-misunderstand query caching

The key for each query cache record is based on theusername + schemaname +SQLhash operation, where SQL is the complete SQL statement containing the arguments, not the parameterized statement, and if the SQL statement is overridden, the hash is used in the full SQL statement after the rewrite . This is important.

For example:

select * from tab where id between 10 and 20;
select * from tab where id between 10 and 19;
select * from tab where id=15;
select * from tab where id between 10 and 19;

The first statement caches the result set between id=10 and Id=20, but the second and third statements do not fetch data from this cache record, but instead set their own cache records from the back-end query, even if the second statement is exactly the same as the first (they are the same class of statements, as shown in the following code). The required data has also been cached by the first statement. A fourth statement hits the cache of the second statement.

select * from tab where id between ? and ?

But because the second statement and the first statement hit the same rule, the total number of executions of such a statement is incremented.

Such as:

MySQL middleware proxysql (9): Proxysql Query caching function