MySQL Bugs: #12761: Query cache doesn't work with NDB tables

Bug #12761	Query cache doesn't work with NDB tables
Submitted:	23 Aug 2005 18:28	Modified:	2 Oct 2005 5:04
Reporter:	Geert Vanderkelen	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Cluster: Cluster (NDB) storage engine	Severity:	S3 (Non-critical)
Version:	5.0.11	OS:	Linux (Linux)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
Hi,

Simple setup: 2 Data nodes, 1 SQL node.
Query cache is enabled and works with MyISAM tables. In same session, it doesn't with
NDB tables.

config.ini is quite simple:
[NDBD DEFAULT]
NoOfReplicas=2
DataMemory=50M
IndexMemory=18M

Best regards,

Geert

How to repeat:
Two tables:
CREATE TABLE `t1myisam` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(20) default NULL,
  `subid` int(11) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `t1ndb` (
  `id` int(11) NOT NULL default '0',
  `name` varchar(20) default NULL,
  `subid` int(11) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=ndbcluster DEFAULT CHARSET=latin1;

Simple data like:
   id: 331169
 name: test
subid: 168

+------------------------------+----------+
| Variable_name                | Value    |
+------------------------------+----------+
| have_query_cache             | YES      |
| query_cache_limit            | 1048576  |
| query_cache_min_res_unit     | 4096     |
| query_cache_size             | 50331648 |
| query_cache_type             | ON       |
| query_cache_wlock_invalidate | OFF      |
+------------------------------+----------+

Here an SQL script:

SHOW GLOBAL VARIABLES LIKE '%query_cache%';
SHOW SESSION VARIABLES LIKE '%query_cache%';

RESET QUERY CACHE;
FLUSH STATUS;

SHOW CREATE TABLE t1ndb\G
SHOW CREATE TABLE t1myisam\G

-- Test on NDB table
SHOW STATUS LIKE 'Qcache%';
SELECT subid FROM t1ndb WHERE subid = 356465;
SHOW STATUS LIKE 'Qcache%';
SELECT subid FROM t1ndb WHERE subid = 356465;

-- Test on MyISAM table
SHOW STATUS LIKE 'Qcache%';
SELECT subid FROM t1myisam WHERE subid = 356465;
SHOW STATUS LIKE 'Qcache%';
SELECT subid FROM t1myisam WHERE subid = 356465;

This has been verified and reproduced using the test case provided.

Geert

CREATE TABLE `t1ndb` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(20) default NULL,
  `subid` int(11) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=ndbcluster DEFAULT CHARSET=latin1;

I have put an auto_increment more, so the following shell script is easier.
This fills up the table:

for((i=0; i<10000; i++)); 
do 
   mysql -uroot -e "INSERT INTO t1ndb (name,subid) VALUES ('test',1),('test',2),('test',3),('test',4),('test',5),('test',6),('test',7),('test',8),('test',9),('test',10),('test',11),('test',12),('test',13),('test',14),('test',15),('test',16),('test',17),('test',18),('test',19),('test',20),('test',21),('test',22),('test',23),('test',24),('test',25),('test',26),('test',27),('test',28),('test',29),('test',30),('test',31),('test',32),('test',33),('test',34),('test',35),('test',36),('test',37),('test',38),('test',39),('test',30)" cluster; 
done

Hi,

I tried your exact table.
With 30k rows.
It works fine

mysql> SHOW STATUS LIKE 'Qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1        |
| Qcache_free_memory      | 10476768 |
| Qcache_hits             | 0        |
| Qcache_inserts          | 0        |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 1        |
| Qcache_queries_in_cache | 0        |
| Qcache_total_blocks     | 1        |
+-------------------------+----------+
8 rows in set (0.00 sec)

mysql> SELECT subid FROM t1ndb WHERE subid = 356465;
Empty set (0.30 sec)

mysql> SHOW STATUS LIKE 'Qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1        |
| Qcache_free_memory      | 10475232 |
| Qcache_hits             | 0        |
| Qcache_inserts          | 1        |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 2        |
| Qcache_queries_in_cache | 1        |
| Qcache_total_blocks     | 4        |
+-------------------------+----------+
8 rows in set (0.00 sec)

mysql> SELECT subid FROM t1ndb WHERE subid = 356465;
Empty set (0.00 sec)

mysql> SHOW STATUS LIKE 'Qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1        |
| Qcache_free_memory      | 10475232 |
| Qcache_hits             | 1        |
| Qcache_inserts          | 1        |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 3        |
| Qcache_queries_in_cache | 1        |
| Qcache_total_blocks     | 4        |
+-------------------------+----------+
8 rows in set (0.00 sec)

mysql> select sum(subid) from t1ndb;
+------------+
| sum(subid) |
+------------+
|      32768 |
+------------+
1 row in set (0.30 sec)

mysql> SHOW STATUS LIKE 'Qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1        |
| Qcache_free_memory      | 10474208 |
| Qcache_hits             | 1        |
| Qcache_inserts          | 2        |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 5        |
| Qcache_queries_in_cache | 2        |
| Qcache_total_blocks     | 6        |
+-------------------------+----------+
8 rows in set (0.00 sec)

mysql> select sum(subid) from t1ndb;
+------------+
| sum(subid) |
+------------+
|      32768 |
+------------+
1 row in set (0.01 sec)

mysql> SHOW STATUS LIKE 'Qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1        |
| Qcache_free_memory      | 10474208 |
| Qcache_hits             | 2        |
| Qcache_inserts          | 2        |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 6        |
| Qcache_queries_in_cache | 2        |
| Qcache_total_blocks     | 6        |
+-------------------------+----------+
8 rows in set (0.01 sec)

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".