ndb_cache2.test 10.5 KB
Newer Older
unknown's avatar
unknown committed
1
-- source include/have_query_cache.inc
2
-- source include/have_binlog_format_row.inc
unknown's avatar
unknown committed
3
-- source include/have_ndb.inc
unknown's avatar
unknown committed
4
-- source include/not_embedded.inc
unknown's avatar
unknown committed
5 6

--disable_warnings
7
drop table if exists t1, t2, t3, t4, t5;
unknown's avatar
unknown committed
8 9 10 11 12 13 14
--enable_warnings


# Turn on and reset query cache
set GLOBAL query_cache_type=on;
set GLOBAL query_cache_size=1355776;
# Turn on thread that will fetch commit count for open tables
15
set GLOBAL ndb_cache_check_time=100;
unknown's avatar
unknown committed
16 17 18
reset query cache;
flush status;

19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48
# Create test tables in NDB
CREATE TABLE t1 (
  pk int not null primary key,
  a1 int,
  b1 int not null,
  c1 varchar(20)
) ENGINE=ndb;
CREATE TABLE t2 (
  pk int not null primary key,
  a2 int,
  b2 int not null
) ENGINE=ndb;
CREATE TABLE t3 (
  pk int not null primary key,
  a3 int,
  b3 int not null,
  c3 int not null,
  d3 varchar(20)
) ENGINE=ndb;
CREATE TABLE t4 (
  a4 int,
  b4 int not null,
  c4 char(20)
) ENGINE=ndbcluster;
CREATE TABLE t5 (
  pk int not null primary key,
  a5 int,
  b5 int not null,
  c5 varchar(255)
) ENGINE=ndbcluster;
unknown's avatar
unknown committed
49
insert into t1 value (1, 2, 3, 'First row');
50 51 52 53
insert into t2 value (1, 2, 3);
insert into t3 value (1, 2, 3, 4, '3 - First row');
insert into t4 value (2, 3, '4 - First row');
insert into t5 value (1, 2, 3, '5 - First row');
unknown's avatar
unknown committed
54

55
# Perform one query which should be inserted in query cache
unknown's avatar
unknown committed
56 57 58 59 60 61 62 63 64 65
select * from t1;
show status like "Qcache_queries_in_cache";
show status like "Qcache_inserts";
show status like "Qcache_hits";

# Perform the same query and make sure the query cache is hit
select * from t1;
show status like "Qcache_hits";

# Update the table and make sure the correct data is returned
66
update t1 set a1=3 where pk=1;
unknown's avatar
unknown committed
67 68 69 70 71 72 73
select * from t1;
show status like "Qcache_inserts";
show status like "Qcache_hits";

# Insert a new record and make sure the correct data is returned
insert into t1 value (2, 7, 8, 'Second row');
insert into t1 value (4, 5, 6, 'Fourth row');
74
select * from t1 order by pk desc;
unknown's avatar
unknown committed
75 76
show status like "Qcache_inserts";
show status like "Qcache_hits";
77
select * from t1 order by pk desc;
unknown's avatar
unknown committed
78 79 80
show status like "Qcache_hits";

# Perform a "new" query and make sure the query cache is not hit
81
select * from t1 where b1=3;
unknown's avatar
unknown committed
82 83 84 85
show status like "Qcache_queries_in_cache";
show status like "Qcache_hits";

# Same query again...
86
select * from t1 where b1=3;
unknown's avatar
unknown committed
87 88 89
show status like "Qcache_hits";

# Delete from the table
90
delete from t1 where c1='Fourth row';
unknown's avatar
unknown committed
91
show status like "Qcache_queries_in_cache";
92
select * from t1 where b1=3;
unknown's avatar
unknown committed
93 94 95 96 97 98
show status like "Qcache_hits";

# Start another connection and check that the query cache is hit
connect (con1,localhost,root,,);
connection con1;
use test;
99
select * from t1 order by pk desc;
100
select * from t1 where b1=3;
unknown's avatar
unknown committed
101 102
show status like "Qcache_hits";

103 104
# Update the table and switch to other connection
update t1 set a1=4 where b1=3;
unknown's avatar
unknown committed
105 106 107 108
connect (con2,localhost,root,,);
connection con2;
use test;
show status like "Qcache_queries_in_cache";
109 110
select * from t1 order by pk desc;
select * from t1 order by pk desc;
unknown's avatar
unknown committed
111 112 113
show status like "Qcache_inserts";
show status like "Qcache_hits";
connection con1;
114 115
select * from t1 order by pk desc;
select * from t1 order by pk desc;
unknown's avatar
unknown committed
116 117 118 119
show status like "Qcache_queries_in_cache";
show status like "Qcache_inserts";
show status like "Qcache_hits";

120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246
# Load all tables into cache
select * from t2;
select * from t3;
select * from t4;
select * from t5;
show status like "Qcache_queries_in_cache";

#####################################################################
# Start transaction and perform update
# Switch to other transaction and check that update does not show up
# Switch back and commit transaction
# Switch to other transaction and check that update shows up
#####################################################################
connection con1;
flush status;
begin;
update t1 set a1=5 where pk=1;
show status like "Qcache_queries_in_cache";
show status like "Qcache_inserts";
show status like "Qcache_hits";
connection con2;
select * from t1 order by pk desc;
show status like "Qcache_queries_in_cache";
show status like "Qcache_inserts";
show status like "Qcache_hits";
connection con1;
commit;
show status like "Qcache_queries_in_cache";
show status like "Qcache_inserts";
show status like "Qcache_hits";
connection con2;
select * from t1 order by pk desc;
show status like "Qcache_inserts";
show status like "Qcache_hits";
connection con1;
select * from t1 order by pk desc;
show status like "Qcache_queries_in_cache";
show status like "Qcache_inserts";
show status like "Qcache_hits";

#####################################################################
# Start transaction and perform update
# Switch to other transaction and check that update does not show up
# Switch back, perform selects and commit transaction
# Switch to other transaction and check that update shows up
#####################################################################
connection con1;
flush status;
begin;
update t1 set a1=6 where pk=1;
show status like "Qcache_queries_in_cache";
show status like "Qcache_inserts";
show status like "Qcache_hits";
connection con2;
select * from t1 order by pk desc;
select * from t1 order by pk desc;
show status like "Qcache_queries_in_cache";
show status like "Qcache_inserts";
show status like "Qcache_hits";
connection con1;
# The two queries below will not hit cache since transaction is ongoing
select * from t1 order by pk desc;
select * from t1 order by pk desc;
show status like "Qcache_queries_in_cache";
show status like "Qcache_inserts";
show status like "Qcache_hits";
commit;

show status like "Qcache_queries_in_cache";
show status like "Qcache_inserts";
show status like "Qcache_hits";
connection con2;
select * from t1 order by pk desc;
show status like "Qcache_inserts";
show status like "Qcache_hits";
connection con1;
select * from t1 order by pk desc;
show status like "Qcache_queries_in_cache";
show status like "Qcache_inserts";
show status like "Qcache_hits";

#####################################################################
# Start transaction and perform insert
# Switch to other transaction and check that insert does not show up
# Switch back, perform selects and commit transaction
# Switch to other transaction and check that update shows up
#####################################################################
connection con1;
flush status;
begin;
insert into t1 set pk=5, a1=6, b1=3, c1="New row";
show status like "Qcache_queries_in_cache";
show status like "Qcache_inserts";
show status like "Qcache_hits";
connection con2;
select * from t1 where pk=5;
select * from t1 order by pk desc;
show status like "Qcache_queries_in_cache";
show status like "Qcache_inserts";
show status like "Qcache_hits";
connection con1;
# The below four queries will not be cached, trans is ongoing
select * from t1 where pk=5;
select * from t1 where pk=5;
select * from t1 order by pk desc;
select * from t1 order by pk desc;
show status like "Qcache_queries_in_cache";
show status like "Qcache_inserts";
show status like "Qcache_hits";
commit;

connection con2;
select * from t1 order by pk desc;
show status like "Qcache_queries_in_cache";
show status like "Qcache_inserts";
show status like "Qcache_hits";

connection con1;

#####################################################################
# Start transaction and perform delete
# Switch to other transaction and check that delete does not show up
# Switch back, perform selects and commit transaction
# Switch to other transaction and check that update shows up
#####################################################################
connection con1;
flush status;
unknown's avatar
unknown committed
247
begin;
248
delete from t1 where pk=2;
unknown's avatar
unknown committed
249 250 251 252
show status like "Qcache_queries_in_cache";
show status like "Qcache_inserts";
show status like "Qcache_hits";
connection con2;
253
select * from t1 where pk=2;
254
select * from t1 order by pk desc;
unknown's avatar
unknown committed
255 256 257 258
show status like "Qcache_queries_in_cache";
show status like "Qcache_inserts";
show status like "Qcache_hits";
connection con1;
259 260 261 262 263 264 265 266
# The below four queries will not be cached, trans is ongoing
select * from t1 where pk=2;
select * from t1 order by pk desc;
select * from t1 order by pk desc;
select * from t1 where pk=2;
show status like "Qcache_queries_in_cache";
show status like "Qcache_inserts";
show status like "Qcache_hits";
unknown's avatar
unknown committed
267
commit;
268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290

connection con2;
select * from t1 order by pk desc;
select * from t1 where pk=2;
show status like "Qcache_queries_in_cache";
show status like "Qcache_inserts";
show status like "Qcache_hits";

connection con1;

#####################################################################
# Start a transaction which updates all tables
# Switch to other transaction and check updates does not show up
# Switch back, perform selects and commit transaction
# Switch to other transaction and check that update shows up
#####################################################################
flush status;
begin;
update t1 set a1=9 where pk=1;
update t2 set a2=9 where pk=1;
update t3 set a3=9 where pk=1;
update t4 set a4=9 where a4=2;
update t5 set a5=9 where pk=1;
unknown's avatar
unknown committed
291 292 293 294
show status like "Qcache_queries_in_cache";
show status like "Qcache_inserts";
show status like "Qcache_hits";
connection con2;
295
select * from t1 order by pk desc;
296 297 298 299 300
select * from t2;
select * from t3;
select * from t4;
select * from t5;
show status like "Qcache_queries_in_cache";
unknown's avatar
unknown committed
301 302 303
show status like "Qcache_inserts";
show status like "Qcache_hits";
connection con1;
304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344
# The below five queries will not be cached, trans is ongoing
select * from t1 order by pk desc;
select * from t1 order by pk desc;
select * from t2;
select * from t3;
select * from t4;
select * from t5;
show status like "Qcache_queries_in_cache";
show status like "Qcache_inserts";
show status like "Qcache_hits";
commit;

connection con2;
select * from t1 order by pk desc;
select * from t2;
select * from t3;
select * from t4;
select * from t5;
show status like "Qcache_queries_in_cache";
show status like "Qcache_inserts";
show status like "Qcache_hits";

connection con1;
select * from t1 order by pk desc;
select * from t2;
select * from t3;
select * from t4;
select * from t5;
show status like "Qcache_queries_in_cache";
show status like "Qcache_inserts";
show status like "Qcache_hits";
select * from t1 order by pk desc;
select * from t2;
select * from t3;
select * from t4;
select * from t5;
show status like "Qcache_queries_in_cache";
show status like "Qcache_inserts";
show status like "Qcache_hits";

connection con2;
345
select * from t1 order by pk desc;
346 347 348 349
select * from t2;
select * from t3;
select * from t4;
select * from t5;
unknown's avatar
unknown committed
350 351 352 353
show status like "Qcache_queries_in_cache";
show status like "Qcache_inserts";
show status like "Qcache_hits";

354
drop table t1, t2, t3, t4, t5;
unknown's avatar
unknown committed
355

356
# There should be no queries in cache, when tables have been dropped
unknown's avatar
unknown committed
357 358 359 360 361 362
show status like "Qcache_queries_in_cache";

SET GLOBAL query_cache_size=0;
SET GLOBAL ndb_cache_check_time=0;