dynamic setting query_cache_type but keeping the benefit of keeping away of overhead LOCK if query cache type is 0

[Hui Liu]

As we know, query_cache overhead for LOCK can only be avoided when start-up mysql with query_cache_type is 0. That's what Percona contributed: http://www.percona.com/doc/percona-server/5.5/performance/query_cache_enhance.html?id=percona-server:features:query_cache_enhance#disabling_the_cache_completely

That's a nice work. However, if we want the query cache back, the mysqld must be restarted with query_cache_type=1. That's the problem, and some customers faced the scenarios.

I looked into the code, and found it might be resolved without the limitation of restarted mysqld. If we call is_disabled with a traced query_cache_type and combined with current setting query_cache_type, it's Okay to judge whether we could get the same result as old is_disabled function in Query_cache class.

Detail info could refer to qc.patch, which is based on Percona-5.5.18

[Oleksandr "Sanja" Byelkin (sanja-byelkin)]

We added the patch which allow to stwitch the QC (the percona patch) but also modified it to make the QC be able to be switched again without stopping the server. The change is in MariaDB since 5.2.

[Oleksandr "Sanja" Byelkin (sanja-byelkin)]

Here is part ofthe patch in mysql-test/t/query_cache.test:

--echo New query cache switching OFF mechanism test
set global query_cache_size=1024*1024*20;
set global query_cache_type=on;
select @@query_cache_size, @@global.query_cache_type, @@local.query_cache_type;
set global query_cache_size=0;
select @@query_cache_size, @@global.query_cache_type, @@local.query_cache_type;
set global query_cache_size=1024*1024*20;
select @@query_cache_size, @@global.query_cache_type, @@local.query_cache_type;
set global query_cache_type=off;
select @@query_cache_size, @@global.query_cache_type, @@local.query_cache_type;
set global query_cache_type=on;
select @@query_cache_size, @@global.query_cache_type, @@local.query_cache_type;
set local query_cache_type= on;
select @@query_cache_size, @@global.query_cache_type, @@local.query_cache_type;

[Oleksandr "Sanja" Byelkin (sanja-byelkin)]

I enabled logging of locking in Query cache and checked that the lock taken only when QC is ON, when QC is off MariaDB do not try to take lock.

[Hui Liu (hickey)]

Yes, I walked through the code of QC in MariaDB-5.5.25, it has fixed this issue with a more smart idea. Nice work, Sanja.

[Raghavendra D Prabhu (raghavendra-prabhu)]

Tested with MariaDB 5.5 and works as expected.

I noticed that there is a tracking variable used: m_requests_in_progress which is to make sure that disabling/enabling is not done while queries are in-flight within the query cache critical section.

I see that the enabling the a disabled query cache can fail only when

      a) query cache disabling is in progress or
      b) global query cache is disabled but session query_cache_type is tried.

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-2375