Querycache lock model in MySQL

Original article: http://dinglin.iteye.com/blog/1625195

Some people ask whether the locks of querycache (QC) in MySQL are "global locks" or "Table locks ". Here is a brief description.

 

1,Basic QC concepts

This is a memory structure implemented in the MySQL layer (non-engine layer). The basic rule is to cache query results that meet certain conditions in the memory. If the same query is executed for the second time, in addition, if the cache is not invalid, the query results can be directly returned without the need to obtain data from the engine.

 

Several notes:

A) The QC structure is hash, and the key is the original text of the query string. Therefore, to hit QC, the query statement must be identical to the previous one, including the case must be consistent and spaces cannot be increased or decreased.

B) QC can cache multiple query statements and results in a table.

C) DML or DDL operations on a table will delete the cache related to the table from QC.

 

2,Lock Model

 

So we talk about the lock granularity. The entire QC only has one instance in the memory: query_cache;

Let's take a look at the code of the failure logic mentioned in C above.

CPP Code

1. Void query_cache: invalidate_table (THD * thd, uchar * Key, uint32 key_length)
2. {
3. Debug_sync (THD, "wait_in_query_cache_invalidate1 ″);
5. Lock ();
7. Debug_sync (THD, "wait_in_query_cache_invalidate2 ″);
9. If (query_cache_size> 0)
10. Invalidate_table_internal (THD, key, key_length );
12. Unlock ();
13. }

 

 

Here we can see that lock () has no parameter, and the function uses a global semaphore cond_cache_status_changed to control it.

Therefore, even if the two DML update Tables are different, they will lock each other because they both fail the cache items in QC.

Therefore, it is a "global lock ".

 

3,Lock Policy

 

After the above conclusion, we are a little worried about whether the "query" will be locked as a global variable ". Imagine if we need to invalidate the cache entry of the table when making a DDL statement, and the lock will last for a long time. During this period, the normal query of other tables will also be affected. If yes, the loss is too great.

 

We know that the access to Qc in the query process includes two parts: Before the query starts, we can judge from QC whether the current query result has been cached; if not, after the query is completed, (possibly) insert the result to QC.

 

Both operations also require the QC lock. In this case, the frequency of the lock is so high that we will worry about whether the loss will be worth the candle?

 

Invalid cache items are necessary operations when updating, but QC operations are not required during queries. MySQL uses the try_lock policy. To put it simply, in the above two stages, we tried to unlock the lock,If it times out, give up.

 

This time-out time is written to the Code for 50 ms. Therefore, if the lock timeout occurs for both QC operations during a query, this query will take an additional Ms.

 

Of course, if the DML operation needs to invalidate the items in QC and encounter lock wait, it will have to wait.

 

4,Summary

 

From the above descriptions, we can draw some conclusions that it will be good to enable QC for services with relatively small update operations, the try_lock policy used during the query does not cause mutual locks in the QC phase. (If this 50 ms is too large, you can remove 0 from the source code ).

 

Of course, if the table is updated frequently, we recommend that you disable QC. Currently, the function of disabling QC with parameters in the trunk version is incomplete, and some CPU consumption may occur. If you are interested, you can read this article.