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

qc.patch
LPexportBug1021131_qc.patch

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

please ignore the first one.
LPexportBug1021131_qc.patch

Re: dynamic setting query_cache_type but keeping the benefit of keeping away of overhead LOCK if query cache type is 0
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.

Re: dynamic setting query_cache_type but keeping the benefit of keeping away of overhead LOCK if query cache type is 0
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;

Re: dynamic setting query_cache_type but keeping the benefit of keeping away of overhead LOCK if query cache type is 0
Hi Sanja, Maybe I have not explained this issue clearly.

What I mean is not the dynamic switch query cache from OFF to ON, but is to talking the overhead lock from OFF to ON.

Let me demo it with an simple example:

1. Run the simple test and we could see the injected code triggered.
(DISABLED query cache still call LOCK/UNLCOK)

$./mtr -record --mysqld="-query_cache_type=OFF" t/qc.test

main.qc [ fail ] Found warnings/errors in server log file!
Test ended at 2012-08-08 10:15:46
line
120808 10:15:46 [Warning] locked when query_cache_type=2
120808 10:15:46 [Warning] locked when query_cache_type=2
120808 10:15:46 [Warning] locked when query_cache_type=2
^ Found warnings in /tmp/lab/mariadb-5.5.25/mysql-test/var/log/mysqld.1.err
ok

2. Test case is:
$cat t/qc.test
– source include/have_query_cache.inc
#

1. Start mysqld with query_cache_type = ON
2. Verify no warnings in error log.

3. SHOW GLOBAL VARIABLES LIKE 'query_cache_type';
   #--error ER_QUERY_CACHE_DISABLED
   SET GLOBAL query_cache_type=OFF;
   SET GLOBAL query_cache_size=0;

create table t1 (a int not null);
insert into t1 values (1),(2),(3);
select * from t1;
select * from t1;

update t1 set a=a+1;
select * from t1;
select * from t1;

drop table t1;

3. The injected code for test is:
Index: lab/mariadb-5.5.25/sql/sql_cache.cc
===================================================================
— lab.orig/mariadb-5.5.25/sql/sql_cache.cc
+++ lab/mariadb-5.5.25/sql/sql_cache.cc
@@ -3145,6 +3145,7 @@ void Query_cache::invalidate_table(THD *
lock(thd);

DEBUG_SYNC(thd, "wait_in_query_cache_invalidate2");
+ sql_print_warning ("locked when query_cache_type=%d", m_cache_status);

if (query_cache_size > 0)
invalidate_table_internal(thd, key, key_length);

4. The result patched for Percona is:

worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 13000..13009
main.qc [ pass ] 5
--------------------------------------------------------------------------

Re: dynamic setting query_cache_type but keeping the benefit of keeping away of overhead LOCK if query cache type is 0
Unpatched Percona could not dynamic swtith from OFF to ON, but MariaDB would, that's what you mentioned, and MariaDB got the same result for :
./mtr -record --mysqld="-query_cache_type=ON" t/qc.test
./mtr -record --mysqld="-query_cache_type=OFF" t/qc.test
Both warnings would be shown in error log.

Re: dynamic setting query_cache_type but keeping the benefit of keeping away of overhead LOCK if query cache type is 0
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.

Re: dynamic setting query_cache_type but keeping the benefit of keeping away of overhead LOCK if query cache type is 0
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.

Re: dynamic setting query_cache_type but keeping the benefit of keeping away of overhead LOCK if query cache type is 0
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.

Launchpad bug id: 1021131