|
Using query cache and partitioned tables are a "problem"
Changing a row in partition 1, will remove all queries of that table, instead of removing all queries of partition 1
CREATE TABLE a (a INT NOT NULL,b INT NOT NULL, PRIMARY KEY (a))
|
PARTITION BY RANGE (a)
|
(PARTITION p0 VALUES LESS THAN (1),
|
PARTITION p1 VALUES LESS THAN MAXVALUE);
|
|
INSERT INTO a values (0,1),(1,1),(2,1);
|
SELECT SQL_CACHE * FROM a; (partition p0 and p1)
|
SELECT SQL_CACHE * FROM a WHERE a=0; (partition p0)
|
SELECT SQL_CACHE * FROM a WHERE a=1; (partition p1)
|
here we have 3 queries in cache
UPDATE a SET b=2 WHERE a=0; (should queries with partition p0)
|
here we have 0 queries in cache, we should have 1 query:
SELECT SQL_CACHE * FROM a WHERE a=1; (partition p1)
|
but it was removed
–
sugestion, add a new var in sql_class.h to store partition part (many partitions = many table blocks)
or create an array of all partitions used (one table with 1000 partitions = 1 table block + 1 array with 1000 partition)
today we are using many tables instead of an array of all partitions
check this:
drop table t1;
|
reset query cache;
|
CREATE TABLE t1
|
(c DATETIME)
|
PARTITION BY HASH ( YEAR(c) )
|
partitions 4; *using myisam engine
|
insert into t1 values ('2014-01-01'),('2013-01-01'),('2012-01-01'),('2011-01-01'),('2010-01-01');
|
select * from t1;
|
select * from t1 partition (p0);
|
select * from t1 partition (p1);
|
select * from t1 partition (p2);
|
select * from t1 partition (p3);
|
select * from information_schema.QUERY_CACHE_QUERIES_TABLES;
|
| QUERY_CACHE_ID |
SCHEMA |
TABLE |
| 1 |
teste |
t1 |
| 2 |
teste |
t1 |
| 3 |
teste |
t1 |
| 4 |
teste |
t1 |
| 5 |
teste |
t1 |
change to innodb:
alter table t1 engine=innodb;
|
select * from t1;
|
select * from t1 partition (p0);
|
select * from t1 partition (p1);
|
select * from t1 partition (p2);
|
select * from t1 partition (p3);
|
select * from information_schema.QUERY_CACHE_QUERIES_TABLES;
|
| QUERY_CACHE_ID |
SCHEMA |
TABLE |
| 1 |
teste |
t1 |
| 1 |
teste |
t1#P#p0 |
| 1 |
teste |
t1#P#p1 |
| 1 |
teste |
t1#P#p2 |
| 1 |
teste |
t1#P#p3 |
| 2 |
teste |
t1 |
| 2 |
teste |
t1#P#p0 |
| 2 |
teste |
t1#P#p1 |
| 2 |
teste |
t1#P#p2 |
| 2 |
teste |
t1#P#p3 |
| 3 |
teste |
t1 |
| 3 |
teste |
t1#P#p0 |
| 3 |
teste |
t1#P#p1 |
| 3 |
teste |
t1#P#p2 |
| 3 |
teste |
t1#P#p3 |
| 4 |
teste |
t1 |
| 4 |
teste |
t1#P#p0 |
| 4 |
teste |
t1#P#p1 |
| 4 |
teste |
t1#P#p2 |
| 4 |
teste |
t1#P#p3 |
| 5 |
teste |
t1 |
| 5 |
teste |
t1#P#p0 |
| 5 |
teste |
t1#P#p1 |
| 5 |
teste |
t1#P#p2 |
| 5 |
teste |
t1#P#p3 |
—
internall notes:
function: query_cache_store_query(thd, all_tables) at sql_parse.cc
all_tables comes from - execute_sqlcom_select function , and the value of all_tables is:
all_tables= lex->query_tables;
in other words, "all_tables" MUST be changed to query cache the right tables (partitions), in this case we should only cache partition names, not table name. without this, invalidation of any partition will invalidate all partitions
other important point is:
ha_partition::register_query_cache_dependant_tables
this function only store partitions if we are using a innodb table! why!? this function should always register partitions, but should not register partitions if engine report HA_CACHE_NO_CACHE (like black_hole? or like spider? must check)
/* see ha_partition::count_query_cache_dependant_tables */
|
if (m_file[0]->table_cache_type() == HA_CACHE_TBL_NOCACHE)
|
DBUG_RETURN(FALSE); // nothing to register
|
at ha_partition::count_query_cache_dependant_tables:
/*
|
We need save underlying tables only for HA_CACHE_TBL_ASKTRANSACT:
|
HA_CACHE_TBL_NONTRANSACT - because all changes goes through partition table
|
HA_CACHE_TBL_NOCACHE - because will not be cached
|
HA_CACHE_TBL_TRANSACT - QC need to know that such type present
|
*/
|
DBUG_RETURN(type != HA_CACHE_TBL_NOCACHE ? m_tot_parts : 0);
|
—
other point to check...
when using query cache with oqgraph, we should include dependent tables (but it's another mdev bug)
|