Practical application of MySQL cache Query

The following articles mainly describe how to query and set globalquery_cache_size for MySQL cache. We all know that when the access volume increases, the pressure on the MySQL database will increase! What if we reduce the pressure on it? Cache first. I have a professional data engineer here to explain. Set the cache globalquery_cache_sizesetglobalqu

The following article describes how to query MySQL cache and set global query_cache_size. We all know that when the access volume increases, the pressure on the MySQL database will increase! What if we reduce the pressure on it? Cache first. I have a professional data engineer here to explain. Set global query_cache_size setglobal qu

The following article describes how to query MySQL cache and set global query_cache_size. We all know that when the access volume increases, the pressure on the MySQL database will increase! What if we reduce the pressure on it? Cache first.

I have a professional data engineer here to explain.

Set global query_cache_size

 
 

  
  
set global query_cache_size = 102760448; 
  
  
set global query_cache_limit = 2097152; 
  
  
set global query_cache_size = 600000; 
 
 

The cache mechanism simply caches the SQL text and query results. If the same SQL statement is run, the server directly retrieves the results from the cache without parsing and executing the SQL statement. If the table is changed, all buffered queries using this table will no longer be valid, and the items related to the query cache value will be cleared. Changes refer to any data or structure changes in the TABLE, including INSERT, UPDATE, DELETE, TRUNCATE, alter table, drop table, or drop database, this also includes queries for tables mapped to changed tables that use MERGE tables. Obviously, this is not suitable for MySQL cache query cache for frequently updated tables. For tables that do not often change data and have a large number of identical SQL queries, query cache saves a lot of performance.

The query must be exactly the same (in bytes) to be considered the same. In addition, the same query string may be considered different for other reasons. Queries using different databases, different protocol versions, or different default character sets are considered to be different queries and cache them separately.

The following SQL query cache is considered different:

 
 

  
  
SELECT * FROM tbl_name 
  
  
Select * from tbl_name 
 
 

Query cache Parameters

 
 

  
  
MySQL> SHOW VARIABLES LIKE '%query_cache%'; 
+------------------------------+---------+ | Variable_name | Value | 
+------------------------------+---------+ | have_query_cache | YES | 
 
 

Query whether the cache is available | query_cache_limit | 1048576 | -- maximum value of the specific query result that can be cached | query_cache_min_res_unit | 4096 | query_cache_size | 599040 | -- query the cache size | query_cache_type | ON | supports MySQL cache query Cache

 
 

  
  
| query_cache_wlock_invalidate | OFF | +------------------------------+---------+ 
 
 

The following is a simple example:

 
 

  
  
[MySQL@csdba1850 ~]$ MySQL -u root -p 
  
  
Enter password: 
  
  
Welcome to the MySQL monitor. Commands end with ; or \g. 
  
  
Your MySQL connection id is 3 
  
  
Server version: 5.0.45-community MySQL Community Edition (GPL) 
  
  
Type 'help;' or '\h' for help. Type '\c' to clear the buffer. 
  
  
MySQL> set global query_cache_size = 600000; 
 
 

Set cache memory

 
 

  
  
Query OK, 0 rows affected (0.00 sec) 
  
  
MySQL> set session query_cache_type = ON; 
 
 

Enable query Cache

 
 

  
  
Query OK, 0 rows affected (0.00 sec) 
  
  
MySQL> use test Reading table information for completion 
of table and column names You can turn off this feature to 
get a quicker startup with -A Database changed mysql> show tables; 
+----------------+ | Tables_in_test | +----------------+ | animals | 
| person | +----------------+ 5 rows in set (0.00 sec) 
mysql> select count(*) from animals; +----------+ | count(*) 
| +----------+ | 6 | +----------+ 1 row in set (0.00 sec) 
 
 

Qcache_hits indicates the cumulative number of times that mysql cache queries hit in the cache, which is the cumulative value.

 
 

  
  
Mysql> show status like 'qcache _ hits '; + --------------- + ------- +
| Variable_name | Value | + --------------- + ------- + | Qcache_hits
| 0 | -- 0 + --------------- + ------- + 8 rows in set (0.00 sec)
Mysql> select count (*) from animals; + ---------- + | count (*)
| + ---------- + | 6 | + ---------- + 1 row in set (0.00 sec)
Mysql> show status like 'qcache % '; + --------------- + ------- +
| Variable_name | Value | + --------------- + ------- + | Qcache_hits | 1 |
 
 

Indicates that the SQL statement is directly obtained in the cache and does not need to be parsed.

 
 

  
  
+---------------+-------+ 8 rows in set (0.00 sec) 
mysql> select count(*) from animals; +----------+ 
| count(*) | +----------+ | 6 | +----------+ 1 row in set (0.00 sec) 
mysql> select count(*) from animals; +----------+ | count(*) 
| +----------+ | 6 | +----------+ 1 row in set (0.00 sec) 
mysql> SHOW STATUS LIKE 'Qcache_hits'; +---------------+-------+ 
| Variable_name | Value | +---------------+-------+ | Qcache_hits | 3 | 
 
 

The preceding SQL statement is used to obtain the result directly from the cache.

 
 

  
  
+---------------+-------+ 1 row in set (0.00 sec) mysql> insert into animals select 9,'testsds' ; 
 
 

After data is inserted, all SQL caches related to the table will be cleared.

 
 

  
  
Query OK, 1 row affected (0.00 sec) Records: 
1 Duplicates: 0 Warnings: 0 mysql> select count(*) from animals; 
+----------+ | count(*) | +----------+ | 7 | +----------+ 
1 row in set (0.00 sec) mysql> SHOW STATUS LIKE 'Qcache_hits'; 
+---------------+-------+ | Variable_name | Value | 
+---------------+-------+ | Qcache_hits | 3 | 
 
 

Or equal to 3 indicates that the previous SQL statement is not directly obtained from the cache.

 
 

  
  
+---------------+-------+ 1 row in set (0.00 sec) 
mysql> select count(*) from animals; +----------+ 
| count(*) | +----------+ | 7 | +----------+ 
1 row in set (0.00 sec) mysql> SHOW STATUS LIKE 'Qcache_hits'; 
+---------------+-------+ | Variable_name | Value | +---------------+-------+ 
| Qcache_hits | 4 | +---------------+-------+ 1 row in set (0.00 sec) 
 
 

The above content is an introduction to mysql cache query and settings. I hope you will find some gains.