Bug #106676 Increase query_cache_size make Query_cache clean empty.
Submitted: 9 Mar 2022 7:58 Modified: 10 Mar 2022 10:19
Reporter: teng wang (OCA) Email Updates:
Status: Won't fix Impact on me:
None 
Category:MySQL Server: Query Cache Severity:S3 (Non-critical)
Version:5.7.19, 5.7.35, 5.7.37 OS:Linux
Assigned to: CPU Architecture:Any

[9 Mar 2022 7:58] teng wang
Description:
I want to use Query_cache in Mysql, so I set option query_cache_type=1 before mysql starts. Firstly, I set query_cache_size=1355776, and I can see one query stored in Query_cache after one "select*" command. However, after I increase query_cache_size to 10 times, I found the Query_cache is empty.
I think it is not reasonable because increasing query_cache_size not means to miss the original queries, not like reducing and reseting. I put the test case and result followed.

How to repeat:
Test case:

set GLOBAL query_cache_size=1355776;
reset query cache;
flush status;

create database if not exists mysqltest;
create table mysqltest.t1 (i int not null);
insert into mysqltest.t1 (i) values (1);
show status like "Qcache_queries_in_cache";

select * from mysqltest.t1;
show status like "Qcache_queries_in_cache";

# increase query_cache_size to 10x
SET GLOBAL query_cache_size = 13557760;
show status like "Qcache_queries_in_cache";

Result:

flush status;
create database if not exists mysqltest;
create table mysqltest.t1 (i int not null);
insert into mysqltest.t1 (i) values (1);
show status like "Qcache_queries_in_cache";
Variable_name	Value
Qcache_queries_in_cache	0
select * from mysqltest.t1;
i
1
show status like "Qcache_queries_in_cache";
Variable_name	Value
Qcache_queries_in_cache	1
SET GLOBAL query_cache_size = 13557760;
show status like "Qcache_queries_in_cache";
Variable_name	Value
Qcache_queries_in_cache	0

Suggested fix:
increasing query_cache_size not means to miss the original queries, not like reducing and reseting
[9 Mar 2022 8:24] MySQL Verification Team
Hello!

Thank you for the report and feedback.
Please note that *query cache* is deprecated as of MySQL 5.7.20, and is removed in MySQL 8.0. 

regards,
Umesh
[10 Mar 2022 10:19] Ståle Deraas
Posted by developer:
 
QC is deprecated from 5.7.20 and removed in 8.0, so not feasible to fix.