Enable MySQL query cache and MySQL Cache

Enable MySQL query cache and MySQL Cache

Enabling MySQL query cache can greatly reduce the CPU usage of the database server. The actual usage is: the CPU usage before enabling is about 120%, which is reduced to 10%.

View the query cache status: mysql> show variables like '% query_cache %'; (query_cache_type indicates that the query cache is enabled if it is ON) + bytes + ---------- + | Variable_name | Value | + bytes + ---------- + | have_query_cache | YES | query_cache_limit | 1048576 | bytes | 4096 | query_cache_size | 20971520 | query_cache_type | ON | | query_cache_wlock_invalidate | OFF | + ---------------------------- + ---------- +
If it is not ON, modify the configuration file to enable the query cache:> vi/etc/my. cnf [mysqld] Add: query_cache_size = 20Mquery_cache_type = ON
Restart mysql:> service mysql restart
View Cache Usage:
Mysql> show status like 'qcache % '; + metrics + ---------- + | Variable_name | Value | + metrics + ---------- + | Qcache_free_blocks | 83 | metrics | 19811040 | Qcache_hits | 3108196 | Qcache_inserts | 757254 | 20720 | | Qcache_not_cached | 47219 | Qcache_queries_in_cache | 47 | Qcache_total_blocks | 276 | + ----------------------- + ---------- +
The meanings of parameters are as follows:

• Qcache_free_blocks: Number of adjacent memory blocks in the cache. A large number of fragments may exist. Flush query cache sorts the fragments in the CACHE to obtain an idle block.
• Qcache_free_memory: idle memory in the cache.
• Qcache_hits: increases when a query hits the cache.
• Qcache_inserts: It increases every time a query is inserted. By dividing the number of hits by the number of inserts, This is the ratio of no hits.
• Qcache_lowmem_prunes: the cache has insufficient memory and must be cleaned up to provide more space for queries. It would be better to look at this number for a long time; if this number continues to grow, it may indicate that the fragmentation is very serious, or the memory is very small. (The free_blocks and free_memory above can tell you what the situation is)
• Qcache_not_cached: the number of queries that are not suitable for caching. It is generally because these queries are not SELECT statements or use functions such as now.
• Qcache_queries_in_cache: number of queries (and responses) cached currently.
• Qcache_total_blocks: Number of cached blocks.
  

For some statements that do not want to use the cache, you can use: select SQL _NO_CACHE count (*) from users where email = 'hello ';
(For Original Articles, please note