[MCOL-794] query cache not work with all engine with columnstore Created: 2017-06-29  Updated: 2020-08-25  Resolved: 2017-07-13

Status: Closed
Project: MariaDB ColumnStore
Component/s: MariaDB Server
Affects Version/s: 1.0.9
Fix Version/s: 1.0.10, 1.1.0

Type: Bug Priority: Major
Reporter: Richard Stracke Assignee: Daniel Lee (Inactive)
Resolution: Fixed Votes: 0
Labels: None
Environment:

Docker container with Columnstore 1.09

Sprint: 2017-14

 Description   

Query cache not work with columnstore regardless the engine.

CREATE TABLE `qtest` (
 
	`id` INT NULL
 
)
 
COLLATE='utf8_unicode_ci'
 
ENGINE= InnoDB
 
;
 
 
 
 
 
SET GLOBAL query_cache_size = 1000000;
 
 
 
SET GLOBAL query_cache_type = 1;
 
 
 
 
 
INSERT INTO `qtest` (`id`) VALUES ('1'),('1'),('2'),('3');
 
 
 
 
 
select * from `qtest` where `id` = 2;
 
 
 
 

No query in the query cache

show global status like 'qca%';
 
 
 
Variable_name;Value
 
Qcache_free_blocks;1
 
Qcache_free_memory;982184
 
Qcache_hits;0
 
Qcache_inserts;0
 
Qcache_lowmem_prunes;0
 
Qcache_not_cached;0
 
Qcache_queries_in_cache;0
 
Qcache_total_blocks;1

But query cache is active.

show global variables like 'q%';
 
 
 
Variable_name;Value
 
query_alloc_block_size;16384
 
query_cache_limit;1048576
 
query_cache_min_res_unit;4096
 
query_cache_size;999424
 
query_cache_strip_comments;OFF
 
query_cache_type;ON
 
query_cache_wlock_invalidate;OFF
 
query_prealloc_size;24576



 Comments   
Comment by Andrew Hutchings (Inactive) [ 2017-07-12 ]

The cause appears to be that the lexer adds the database name to the end of the query buffer. The query cache then sees the lengths of the DB names as different so thinks that this is part of a multi-statement query and disables. We overwrite the query buffer in vtable mode but don't add that back again. This is the comment in sql_cache we need to look for, this is the point query cache decides to turn off:

      We have allocated buffer space (in alloc_query) to hold the
 
      SQL statement(s) + the current database name + a flags struct.
 
      If the database name has changed during execution, which might
 
      happen if there are multiple statements, we need to make
 
      sure the new current database has a name with the same length
 
      as the previous one.

Comment by Andrew Hutchings (Inactive) [ 2017-07-12 ]

Two pull requests, one for 1.0, one for 1.1.

Note that this only fixes query cache with non-ColumnStore tables. We can't do query cache for ColumnStore tables due to the way vtable works.

Comment by Daniel Lee (Inactive) [ 2017-07-13 ]

Builds verified: Github source 1.0.10, 1.1.0;

1.0.10-1

[root@localhost mariadb-columnstore-server]# git show
commit 435972e50ee33911ce39696ce101d1cd23ed9c2b
Merge: b1d1ca1 5d3fcfe
Author: David.Hall <david.hall@mariadb.com>
Date: Wed Jul 12 13:07:55 2017 -0500

[root@localhost mariadb-columnstore-engine]# git show
commit 3501c1a17a920ee765c6255c5a5fd8c64fed7c8e
Author: david hill <david.hill@mariadb.com>
Date: Wed Jul 12 09:52:28 2017 -0500

1.1.0-1

[root@localhost mariadb-columnstore-server]# git show
commit 8e07495da650d922c4d1f3f09d77382168132b11
Merge: 80e57a8 c27e1e5
Author: David.Hall <david.hall@mariadb.com>
Date: Wed Jul 12 13:07:42 2017 -0500

[root@localhost mariadb-columnstore-engine]# git show
commit d1386928dcaf1d9acc92ab11e2415c808c75dd49
Author: david hill <david.hill@mariadb.com>
Date: Thu Jul 13 11:20:08 2017 -0500

Note: Need to turn on query cache at session level for this to work.

Generated at Thu Feb 08 02:23:52 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.