MySQL cache commands

 

Mysql> select @ query_cache_type;

+ -------------------- +

| @ Query_cache_type |

+ -------------------- +

| On |

+ -------------------- +

Mysql> set query_cache_type = off;

Mysql> set query_cache_type = on;

Mysql>

Mysql> select SQL _cache ID, title, body from article;

Mysql> select SQL _no_cache ID, title, body from article;

Mysql> show variables like 'have _ query_cache ';

+ ------------------ + ------- +

| Variable_name | value |

+ ------------------ + ------- +

| Have_query_cache | Yes |

+ ------------------ + ------- +

1 row in SET (0.00 Sec)

View the cache size of a MySQL Query

 

Mysql> select @ Global. query_cache_size;

+ --------------------------- +

| @ Global. query_cache_size |

+ --------------------------- +

| 1, 16777216 |

+ --------------------------- +

1 row in SET (0.00 Sec)

Mysql> select @ query_cache_size;

+ -------------------- +

| @ Query_cache_size |

+ -------------------- +

| 1, 16777216 |

+ -------------------- +

1 row in SET (0.00 Sec)

View the maximum number of cached results. If the number of result sets is greater than this value, the results are not cached.

 

Mysql> select @ Global. query_cache_limit;

+ ---------------------------- +

| @ Global. query_cache_limit |

+ ---------------------------- +

| 1, 1048576 |

+ ---------------------------- +

1 row in SET (0.00 Sec)

Fragment

 

Mysql> flush query Cache

->;

Query OK, 0 rows affected (0.00 Sec)

Clear Cache

 

Mysql> Reset query Cache

->;

Query OK, 0 rows affected (0.00 Sec

Monitor MySQL query cache performance:

 

Mysql> flush tables;

Query OK, 0 rows affected (0.04 Sec)

Mysql> show status like 'qcache % ';

+ ------------------------- + ---------- +

| Variable_name | value |

+ ------------------------- + ---------- +

| Qcache_free_blocks | 1 |

| Qcache_free_memory | 16768408 |

| Qcache_hits | 6 |

| Qcache_inserts | 36 |

| Qcache_lowmem_prunes | 0 |

| Qcache_not_cached | 86 |

| Qcache_queries_in_cache | 0 |

| Qcache_total_blocks | 1 |

+ ------------------------- + ---------- +

8 rows in SET (0.06 Sec)

 

 

Show variables like 'query _ cache %'

Query_cache_limit
If a single query result is greater than this value
Query_cache_size
Memory allocated to QC. If it is set to 0, it is equivalent to disabling QC. Note that QC must use approximately 40 kb to store its structure. If it is set to be smaller than 40 kb, it is equivalent to disabling QC. The minimum unit of QC storage is 1024 bytes. Therefore, if you set a value that is not a multiple of 1024, the value is rounded to a value that is equal to or greater than 1024 closest to the current value.
Query_cache_type
0: QC is completely forbidden and is not controlled by SQL statements. (In addition, even if it is disabled here, the memory size set by the preceding parameter will still be allocated.) 1. Enable QC, you can use SQL _no_cache to disable SQL statements; 2. You can use SQL _cache to enable SQL statements.
Query_cache_min_res_unit
Memory size allocated to QC results each time

 

Show status like 'qcache %'

Qcache_free_blocks
When a table is updated, its related cache blocks will be free. However, this block may still exist in the queue unless it is at the end of the queue. The blocks will be counted. You can use the flush query cache statement to clear free blocks.
Qcache_free_memory
Available memory. If it is small, consider adding query_cache_size.
Qcache_hits
Number of cache hits since the MySQL process was started
Qcache_inserts
The number of MySQL processes added to QC since the MySQL process was started.
Qcache_lowmem_prunes
The number of items deleted by QC due to a small amount of memory. Increase query_cache_size and try to keep this value increasing by 0.
Qcache_not_cached
The number of read-only queries not cached since the MySQL process was started (including select, show, use, DESC, etc)
Qcache_queries_in_cache
Number of currently cached SQL statements
Qcache_total_blocks
The number of blocks in QC. A query may be stored by multiple blocks, and the last of these blocks will be released if the memory is not used up. For example, a QC result occupies 6 kb of memory. If query_cache_min_res_unit is 4 kb, three blocks are generated at the end, and the first block is used to store the SQL statement text, this will not be counted in query + cache_size. The second block is 4 kb, and the third block is 2 kb (allocate4kb first, and then 2 kb later ). Each table uses a block to store table information when the first SQL query related to it is cached. That is to say, the block will be used in three places: Table information, SQL text, and query results.