MySQL query Cache

MySQL query Cache

When the query cache (QC) function is enabled for your database, the database puts the results in QC when executing the SELECT statement. When the same select request is processed, the database will obtain results from QC without querying the data table.

In this era of "cache is King", we always cache our results in different ways to improve response efficiency. But is a cache mechanism effective and effective, but it is a question that requires careful consideration. Query cache in MySQL is a cache mechanism suitable for a small number of cases. If the cache hit rate is very high, some tests show that in extreme cases, the efficiency can be improved by 238% [1]. But what is the actual situation?Query cache has the following rules. If the data table is changed, all the caches related to the data table will be invalid and deleted. Here, "data table changes" include:Insert,Update,Delete,Truncate,Alter
Table,Drop table, OrDrop
Database.For example, if the data table posts is frequently accessed, it means that many of its data will be cached by QC, but every time the data table posts is updated, no matter whether the update affects the data in the cache or not, all the cache related to the posts table will be cleared. If your data tables are updated frequently, the query cache will become a burden on the system. Some experiments show that, in poor cases, QC will reduce the processing capability of the system by 13% [1.

If your application has few updates to the database, QC will play a significant role. Typical blogs, such as blog systems, are generally relatively slow to update, and data tables are relatively stable. At this time, QC will play a significant role.

Another example is a frequently updated BBS system. The following is the status parameter of a running Forum database:

Qcache_hit	5280438
Qcache_insert	8008948
Qcache_not_cache	95372
Com select	8104159

As you can see, the database writes a total of cache times to QC, but the actual hit is only about times. That is to say, the usage of each cache is about 0.66 times. It is hard to say whether the caching function is greater than the overhead of the QC system. However, it is certain that the QC cache function is very small. If the application layer can implement the cache, the QC effect can be ignored.

----- The following are some other details about QC ------

1. query cache parameters:

• Query_cache_size the space occupied by QC. Disable the QC function by setting it to 0.
• Query_cache_type 0 indicates that QC is disabled; 1 indicates normal cache; 2 indicates that SQL _cache is cached.
• Query_cache_limit Maximum Cache result set
• In the query_cache_min_res_unit manual, QC allocates the cache Block Size Based on this value.
• Qcache_lowmem_prunes this is a state variable (show
  Status). When the cache space is insufficient and the old cache needs to be released, this value will automatically increase.

Ii. query cache observation:

Create Table T1 (ID int, var1 varchar (10); // com_select: 8qcache_hits: 1 insert into T1 values (1, 'www '); // com_select: 8qcache_hits: 1 select * from T1 where id = 1; // com_select: 9qcache_hits: 1 select * from T1 where id = 1; // com_select: 9qcache_hits: 2 qcache_queries_in_cache: 1 insert into T1 values (2, 'rrrr '); // com_select: 9qcache_hits: 2 qcache_queries_in_cache: 0 select * from T1 where id = 1; // cache invalidation after insert // com_select: 10 qcache_hits: 2 qcache_queries_in_cache: 1

Refer:

1. Http://dev.mysql.com/doc/refman/5.0/en/query-cache.html
2. Http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html
3. Http://www.mysqlperformanceblog.com/2006/07/27/mysql-query-cache/

Address: http://www.taobaodba.com/html/325_query-cache-cool-or-not.html