Query Cache, it looks beautiful.

Query cache in the development should be considered a very wide range of applications. But in fact, in the High concurrent website, query cache should and shut down (I refer to the forum, SNS type of site), but should be cached in other ways, not with the database.
As we all know, after the query cache is set, successive queries for the same SQL are cached, but the CAHCE will be emptied once there is an update. When the amount of data is small, you may not feel it, if the amount of data is large, it actually affects efficiency. Therefore, it is generally not very used.

The MySQL parameter is: xml/html code # query cache is used to cache select results  and later return them   # without actual executing the  same query once again. having the query   # cache enabled  may result in significant speed improvements, if your   #  have a lot of identical queries and rarely changing tables.  See the   #  "Qcache_lowmem_prunes"  status variable to check  if the current value   # is high enough for your load.    # note: in case your tables change very often or  if your queries are   # textually different every time,  the query cache may result in a   # slowdown instead of a  performance improvement.   query_cache_size = 0   

Taobao DBA's Soup said:

When your database opens the query Cache (QC) feature, the database executes the SELECT statement with its results in the QC, and the next time the same select request is processed, the database results from the QC without having to query the datasheet.

In this "cache is King" era, we always in different ways to cache our results to improve response efficiency, but a caching mechanism is effective, the effect is a need to think about the problem. The query cache in MySQL is a caching mechanism that applies less. In the above illustration, if the cache hit rate is very high, there are tests to show that in extreme cases it can improve efficiency 238%[1]. But what is the actual situation. Query Cache has the following rules, if the datasheet is changed, then all the cache related to this data table will be invalid and deleted. Here "datasheet Changes" include: INSERT , UPDATE , DELETE , TRUNCATE c8>, ALTER table , drop table , or drop DATABASE , etc. For example, if the data table posts frequent access, it means that many of its data will be the QC cache, but each update of the posts datasheet, regardless of whether the update is affecting the cache data, will be all and posts table related cache removal. If your datasheet is updated frequently, query cache will be a burden to the system. Experiments have shown that, in bad times, QC can reduce the processing power of the system 13%[1.

If your application does not update the database very well, then the QC will do a significant role. More typical such as blog system, the general blog update relatively slow, the data table relatively stable, this time the role of QC will be more obvious.

Another example, a frequent update of the BBS system. The following is a status parameter for the actual running forum database:

Qcache_hit	5280438
Qcache_insert	8008948
Qcache_not_cache	95372
Com Select	8104159

As you can see, the database has been written to QC for approximately 800W times, but the actual hit is only about 500W. That is, the utilization rate of each cache is about 0.66 times. It's hard to say whether the cache is more expensive than the QC system. But one thing is certain, the role of the QC cache is very small, if the application layer can implement the cache, will be able to ignore the results of the QC.

————-below are some of the other details about QC ————— –

First, Query cache related parameters: Query_cache_size QC footprint size, by setting it to 0 turn off the QC function Query_cache_type 0 means close the qc;1 indicates normal cache; 2 indicates sql_cache cache Query_ Cache_limit Maximum cache result set Query_cache_min_res_unit the manual says that the QC will allocate the size of the cache block according to this value. Qcache_lowmem_prunes This is a state variable (show status), which increases itself when there is not enough cache space to release the old cache.

Second, Query cache observation:

CREATE TABLE T1 (id int,var1 varchar (10));

	Com_select:8	qcache_hits:1

INSERT into T1 VALUES (1, ' WWW ');

	Com_select:8	qcache_hits:1

SELECT * from T1 WHERE id=1;

	Com_select:9	qcache_hits:1

SELECT * from T1 WHERE id=1;

	Com_select:9	Qcache_hits:2 qcache_queries_in_cache:1

INSERT into T1 VALUES (2, ' rrrr ');

	Com_select:9	Qcache_hits:2 qcache_queries_in_cache:0

SELECT * from T1 WHERE id=1; Cache failure after insert

	Com_select:10  Qcache_hits:2 qcache_queries_in_cache:1

Reference: Http://dev.mysql.com/doc/refman/5.0/en/query-cache.html http://dev.mysql.com/doc/refman/5.0/en/ Server-system-variables.html http://www.mysqlperformanceblog.com/2006/07/27/mysql-query-cache/

(End of full text)

This article refers to the address: http://rdc.taobao.com/blog/dba/html/325_query-cache-cool-or-not.html