Why Mysql disables query cache

Maybe some people just need to adjust the size of the query_cache_size to 0, you can ignore the value of the Query_cache_type parameter, it can be adjusted online anyway.

Is that really the case? Let's go through the actual simulation test to know.

We simulate the following scenarios:

1. When initializing, the value of setting query_cache_size and Query_cache_type is 0;

2, initialization, set query_cache_size = 0, but set query_cache_type = 1;

3, initialization, set query_cache_size = 0,query_cache_type = 1, but immediately after the start of the modification Query_cache_type = 0

4, initialization, set query_cache_size = 0,query_cache_type = 0, but immediately after the start of the modification Query_cache_type = 1

5, initialization, set query_cache_size = Xmb,query_cache_type = 1, but immediately after the start of the modification Query_cache_type = 0



After testing, you can get the following important conclusions (detailed test procedure see FINAL):

1, want to completely shut down query cache, must be set at the beginning of Query_cache_type = 0, even after the start of the Query_cache_type from 1 to 0, also not;

2, even if query_cache_size = 0, but query_cache_type not 0, in the actual environment, may occur frequently waiting for query cache lock;

3, at the beginning of the set Query_cache_type = 0, there is no way to dynamically enable in the run process again, the reverse can be. That is, the start is to enable query cache, in the process of shutting it down, but in fact will still occur waiting for query cache lock, and no real shutdown;

Detailed testing process:

First, test methods

Using Sysbench to simulate concurrent OLTP requests:

Sysbench--test=tests/db/oltp.lua--oltp_tables_count=10--oltp-table-size=100000--rand-init=on--num-threads=64-- Oltp-read-only=off--report-interval=10--rand-type=uniform--max-time=1800--max-requests=0 Run

Second, the specific types of test mode

1, has been turned off the QC (query cache of shorthand, the same below), that is query_cache_size = 0, Query_cache_type = 0

During the test, there was no state associated with query cache lock and the result tps:2295.34



2, the commissioning of QC, but the QC size is set to 0, that is: query_cache_size = 0,query_cache_type = 1

During the test, there was always a waiting for query cache lock state, resulting in tps:2272.52



3, enable the QC, but the QC size is 0, but immediately turn off the QC at startup, that is, when the initialization of query_cache_size = 0,query_cache_type = 1, immediately after starting to modify Query_cache_type = 0

During the testing process, there has been waiting for query cache lock state, resulting in tps:2311.54



4, close the QC, but start immediately after the commissioning of QC, that is, when the initialization of query_cache_size = 0,query_cache_type = 0, immediately after the start of the modification Query_cache_type = 1

At this point, the error message is prompted:

Failure: ERROR 1651 (HY000): Query cache is disabled; Restart the server with query_cache_type=1 to enable it

In other words, if you turn off the QC at the beginning, there is no way to dynamically enable the QC during the run.



5, enable the QC, and set the QC size to 256M, that is query_cache_size = 256m,query_cache_type = 1

In this case, there is always a waiting for query cache lock state appearing during the test, and the result TPs is poor, only 1395.39 (the worst of several cases)



6, the commissioning of QC, set the QC size of 256M, but immediately after the start of the QC, that is query_cache_size = 256m,query_cache_type = 1, immediately after the start of the modification Query_cache_type = 0

In this case, there has been waiting for query cache lock State in the test process, resulting in tps:2295.79 (in this mode, if set Query_cache_type = 2, the effect is also poor)



In the third mode, although the TPS looks good, it's just a simple simulation test, and the TPS will not look too good if you have frequent query cache lock.

So, in general, if you want to get a higher TPS, it's best to shut down the QC from the start, and avoid getting lucky or sticking to stereotypes.