mirror of
https://github.com/MariaDB/server.git
synced 2025-02-15 18:05:32 +01:00
1645 lines
34 KiB
Text
1645 lines
34 KiB
Text
|
||
# Execute FLUSH/RESET commands.
|
||
# On node-1
|
||
SET @query_cache_size_saved=@@GLOBAL.query_cache_size;
|
||
SET @query_cache_type_saved=@@GLOBAL.query_cache_type;
|
||
set GLOBAL query_cache_size=1355776;
|
||
flush query cache;
|
||
reset query cache;
|
||
flush status;
|
||
# On node-2
|
||
SET @query_cache_size_saved=@@GLOBAL.query_cache_size;
|
||
SET @query_cache_type_saved=@@GLOBAL.query_cache_type;
|
||
set GLOBAL query_cache_size=1355776;
|
||
flush query cache;
|
||
reset query cache;
|
||
flush status;
|
||
# On node-1
|
||
create table t1 (a int not null) engine=innodb;
|
||
insert into t1 values (1),(2),(3);
|
||
select * from t1;
|
||
a
|
||
1
|
||
2
|
||
3
|
||
select * from t1;
|
||
a
|
||
1
|
||
2
|
||
3
|
||
select sql_no_cache * from t1;
|
||
a
|
||
1
|
||
2
|
||
3
|
||
select length(now()) from t1;
|
||
length(now())
|
||
19
|
||
19
|
||
19
|
||
show status like "Qcache_queries_in_cache";
|
||
Variable_name Value
|
||
Qcache_queries_in_cache 1
|
||
show status like "Qcache_inserts";
|
||
Variable_name Value
|
||
Qcache_inserts 1
|
||
show status like "Qcache_hits";
|
||
Variable_name Value
|
||
Qcache_hits 1
|
||
# On node-2
|
||
select * from t1;
|
||
a
|
||
1
|
||
2
|
||
3
|
||
select * from t1;
|
||
a
|
||
1
|
||
2
|
||
3
|
||
select sql_no_cache * from t1;
|
||
a
|
||
1
|
||
2
|
||
3
|
||
select length(now()) from t1;
|
||
length(now())
|
||
19
|
||
19
|
||
19
|
||
show status like "Qcache_queries_in_cache";
|
||
Variable_name Value
|
||
Qcache_queries_in_cache 1
|
||
show status like "Qcache_inserts";
|
||
Variable_name Value
|
||
Qcache_inserts 1
|
||
show status like "Qcache_hits";
|
||
Variable_name Value
|
||
Qcache_hits 1
|
||
# On node-1
|
||
delete from t1 where a=1;
|
||
show status like "Qcache_queries_in_cache";
|
||
Variable_name Value
|
||
Qcache_queries_in_cache 0
|
||
show status like "Qcache_hits";
|
||
Variable_name Value
|
||
Qcache_hits 1
|
||
# On node-2
|
||
show status like "Qcache_queries_in_cache";
|
||
Variable_name Value
|
||
Qcache_queries_in_cache 0
|
||
show status like "Qcache_hits";
|
||
Variable_name Value
|
||
Qcache_hits 1
|
||
# On node-1
|
||
select * from t1;
|
||
a
|
||
2
|
||
3
|
||
show status like "Qcache_queries_in_cache";
|
||
Variable_name Value
|
||
Qcache_queries_in_cache 1
|
||
show status like "Qcache_hits";
|
||
Variable_name Value
|
||
Qcache_hits 1
|
||
# On node-2
|
||
select * from t1;
|
||
a
|
||
2
|
||
3
|
||
show status like "Qcache_queries_in_cache";
|
||
Variable_name Value
|
||
Qcache_queries_in_cache 1
|
||
show status like "Qcache_hits";
|
||
Variable_name Value
|
||
Qcache_hits 1
|
||
# On node-1
|
||
update t1 set a=1 where a=3;
|
||
show status like "Qcache_queries_in_cache";
|
||
Variable_name Value
|
||
Qcache_queries_in_cache 0
|
||
show status like "Qcache_hits";
|
||
Variable_name Value
|
||
Qcache_hits 1
|
||
# On node-2
|
||
show status like "Qcache_queries_in_cache";
|
||
Variable_name Value
|
||
Qcache_queries_in_cache 0
|
||
show status like "Qcache_hits";
|
||
Variable_name Value
|
||
Qcache_hits 1
|
||
# On node-1
|
||
select * from t1;
|
||
a
|
||
2
|
||
1
|
||
show status like "Qcache_queries_in_cache";
|
||
Variable_name Value
|
||
Qcache_queries_in_cache 1
|
||
show status like "Qcache_hits";
|
||
Variable_name Value
|
||
Qcache_hits 1
|
||
# On node-2
|
||
select * from t1;
|
||
a
|
||
2
|
||
1
|
||
show status like "Qcache_queries_in_cache";
|
||
Variable_name Value
|
||
Qcache_queries_in_cache 1
|
||
show status like "Qcache_hits";
|
||
Variable_name Value
|
||
Qcache_hits 1
|
||
# On node-1
|
||
drop table t1;
|
||
show status like "Qcache_queries_in_cache";
|
||
Variable_name Value
|
||
Qcache_queries_in_cache 0
|
||
show status like "Qcache_hits";
|
||
Variable_name Value
|
||
Qcache_hits 1
|
||
# On node-2
|
||
show status like "Qcache_queries_in_cache";
|
||
Variable_name Value
|
||
Qcache_queries_in_cache 0
|
||
show status like "Qcache_hits";
|
||
Variable_name Value
|
||
Qcache_hits 1
|
||
|
||
# On node-1
|
||
create table t1 (a int not null) ENGINE=MyISAM;
|
||
insert into t1 values (1),(2),(3);
|
||
create table t2 (a int not null) ENGINE=MyISAM;
|
||
insert into t2 values (4),(5),(6);
|
||
create table t3 (a int not null) engine=MERGE UNION=(t1,t2) INSERT_METHOD=FIRST;
|
||
select * from t3;
|
||
a
|
||
1
|
||
2
|
||
3
|
||
4
|
||
5
|
||
6
|
||
select * from t3;
|
||
a
|
||
1
|
||
2
|
||
3
|
||
4
|
||
5
|
||
6
|
||
show status like "Qcache_hits";
|
||
Variable_name Value
|
||
Qcache_hits 2
|
||
show status like "Qcache_queries_in_cache";
|
||
Variable_name Value
|
||
Qcache_queries_in_cache 1
|
||
show status like "Qcache_hits";
|
||
Variable_name Value
|
||
Qcache_hits 2
|
||
insert into t2 values (7);
|
||
show status like "Qcache_queries_in_cache";
|
||
Variable_name Value
|
||
Qcache_queries_in_cache 0
|
||
show status like "Qcache_hits";
|
||
Variable_name Value
|
||
Qcache_hits 2
|
||
select * from t1;
|
||
a
|
||
1
|
||
2
|
||
3
|
||
select * from t1;
|
||
a
|
||
1
|
||
2
|
||
3
|
||
show status like "Qcache_hits";
|
||
Variable_name Value
|
||
Qcache_hits 3
|
||
show status like "Qcache_queries_in_cache";
|
||
Variable_name Value
|
||
Qcache_queries_in_cache 1
|
||
show status like "Qcache_hits";
|
||
Variable_name Value
|
||
Qcache_hits 3
|
||
insert into t3 values (8);
|
||
show status like "Qcache_queries_in_cache";
|
||
Variable_name Value
|
||
Qcache_queries_in_cache 0
|
||
show status like "Qcache_hits";
|
||
Variable_name Value
|
||
Qcache_hits 3
|
||
select * from t3;
|
||
a
|
||
1
|
||
2
|
||
3
|
||
8
|
||
4
|
||
5
|
||
6
|
||
7
|
||
show status like "Qcache_queries_in_cache";
|
||
Variable_name Value
|
||
Qcache_queries_in_cache 1
|
||
show status like "Qcache_hits";
|
||
Variable_name Value
|
||
Qcache_hits 3
|
||
update t2 set a=9 where a=7;
|
||
show status like "Qcache_queries_in_cache";
|
||
Variable_name Value
|
||
Qcache_queries_in_cache 0
|
||
show status like "Qcache_hits";
|
||
Variable_name Value
|
||
Qcache_hits 3
|
||
select * from t1;
|
||
a
|
||
1
|
||
2
|
||
3
|
||
8
|
||
show status like "Qcache_queries_in_cache";
|
||
Variable_name Value
|
||
Qcache_queries_in_cache 1
|
||
show status like "Qcache_hits";
|
||
Variable_name Value
|
||
Qcache_hits 3
|
||
update t3 set a=10 where a=1;
|
||
show status like "Qcache_queries_in_cache";
|
||
Variable_name Value
|
||
Qcache_queries_in_cache 0
|
||
show status like "Qcache_hits";
|
||
Variable_name Value
|
||
Qcache_hits 3
|
||
select * from t3;
|
||
a
|
||
10
|
||
2
|
||
3
|
||
8
|
||
4
|
||
5
|
||
6
|
||
9
|
||
show status like "Qcache_queries_in_cache";
|
||
Variable_name Value
|
||
Qcache_queries_in_cache 1
|
||
show status like "Qcache_hits";
|
||
Variable_name Value
|
||
Qcache_hits 3
|
||
delete from t2 where a=9;
|
||
show status like "Qcache_queries_in_cache";
|
||
Variable_name Value
|
||
Qcache_queries_in_cache 0
|
||
show status like "Qcache_hits";
|
||
Variable_name Value
|
||
Qcache_hits 3
|
||
select * from t1;
|
||
a
|
||
10
|
||
2
|
||
3
|
||
8
|
||
show status like "Qcache_queries_in_cache";
|
||
Variable_name Value
|
||
Qcache_queries_in_cache 1
|
||
show status like "Qcache_hits";
|
||
Variable_name Value
|
||
Qcache_hits 3
|
||
delete from t3 where a=10;
|
||
select * from t3;
|
||
a
|
||
2
|
||
3
|
||
8
|
||
4
|
||
5
|
||
6
|
||
show status like "Qcache_queries_in_cache";
|
||
Variable_name Value
|
||
Qcache_queries_in_cache 1
|
||
show status like "Qcache_hits";
|
||
Variable_name Value
|
||
Qcache_hits 3
|
||
# On node-2
|
||
select * from t3;
|
||
a
|
||
select * from t3;
|
||
a
|
||
show status like "Qcache_queries_in_cache";
|
||
Variable_name Value
|
||
Qcache_queries_in_cache 1
|
||
show status like "Qcache_inserts";
|
||
Variable_name Value
|
||
Qcache_inserts 4
|
||
show status like "Qcache_hits";
|
||
Variable_name Value
|
||
Qcache_hits 2
|
||
drop table t1, t2, t3;
|
||
# On node-1
|
||
set query_cache_type=demand;
|
||
create table t1 (a int not null) engine=innodb;
|
||
insert into t1 values (1),(2),(3);
|
||
select * from t1;
|
||
a
|
||
1
|
||
2
|
||
3
|
||
show status like "Qcache_queries_in_cache";
|
||
Variable_name Value
|
||
Qcache_queries_in_cache 0
|
||
show status like "Qcache_hits";
|
||
Variable_name Value
|
||
Qcache_hits 3
|
||
select sql_cache * from t1 union select * from t1;
|
||
a
|
||
1
|
||
2
|
||
3
|
||
set query_cache_type=2;
|
||
select sql_cache * from t1 union select * from t1;
|
||
a
|
||
1
|
||
2
|
||
3
|
||
show status like "Qcache_hits";
|
||
Variable_name Value
|
||
Qcache_hits 4
|
||
show status like "Qcache_queries_in_cache";
|
||
Variable_name Value
|
||
Qcache_queries_in_cache 1
|
||
set query_cache_type=on;
|
||
# On node-2
|
||
set query_cache_type=demand;
|
||
select * from t1;
|
||
a
|
||
1
|
||
2
|
||
3
|
||
show status like "Qcache_queries_in_cache";
|
||
Variable_name Value
|
||
Qcache_queries_in_cache 0
|
||
show status like "Qcache_hits";
|
||
Variable_name Value
|
||
Qcache_hits 2
|
||
select sql_cache * from t1 union select * from t1;
|
||
a
|
||
1
|
||
2
|
||
3
|
||
set query_cache_type=2;
|
||
select sql_cache * from t1 union select * from t1;
|
||
a
|
||
1
|
||
2
|
||
3
|
||
show status like "Qcache_hits";
|
||
Variable_name Value
|
||
Qcache_hits 3
|
||
show status like "Qcache_queries_in_cache";
|
||
Variable_name Value
|
||
Qcache_queries_in_cache 1
|
||
set query_cache_type=on;
|
||
# On node-1
|
||
reset query cache;
|
||
show status like "Qcache_queries_in_cache";
|
||
Variable_name Value
|
||
Qcache_queries_in_cache 0
|
||
show status like "Qcache_hits";
|
||
Variable_name Value
|
||
Qcache_hits 4
|
||
# On node-2
|
||
reset query cache;
|
||
show status like "Qcache_queries_in_cache";
|
||
Variable_name Value
|
||
Qcache_queries_in_cache 0
|
||
show status like "Qcache_hits";
|
||
Variable_name Value
|
||
Qcache_hits 3
|
||
# On node-1
|
||
select sql_no_cache * from t1;
|
||
a
|
||
1
|
||
2
|
||
3
|
||
show status like "Qcache_queries_in_cache";
|
||
Variable_name Value
|
||
Qcache_queries_in_cache 0
|
||
show status like "Qcache_hits";
|
||
Variable_name Value
|
||
Qcache_hits 4
|
||
# On node-2
|
||
select sql_no_cache * from t1;
|
||
a
|
||
1
|
||
2
|
||
3
|
||
show status like "Qcache_queries_in_cache";
|
||
Variable_name Value
|
||
Qcache_queries_in_cache 0
|
||
show status like "Qcache_hits";
|
||
Variable_name Value
|
||
Qcache_hits 3
|
||
drop table t1;
|
||
# On node-1
|
||
create table t1 (a text not null) engine=innodb;
|
||
select CONNECTION_ID() from t1;
|
||
CONNECTION_ID()
|
||
select FOUND_ROWS();
|
||
FOUND_ROWS()
|
||
0
|
||
select NOW() from t1;
|
||
NOW()
|
||
select CURDATE() from t1;
|
||
CURDATE()
|
||
select CURTIME() from t1;
|
||
CURTIME()
|
||
select DATABASE() from t1;
|
||
DATABASE()
|
||
select ENCRYPT("test") from t1;
|
||
ENCRYPT("test")
|
||
select LAST_INSERT_ID() from t1;
|
||
LAST_INSERT_ID()
|
||
select RAND() from t1;
|
||
RAND()
|
||
select UNIX_TIMESTAMP() from t1;
|
||
UNIX_TIMESTAMP()
|
||
select USER() from t1;
|
||
USER()
|
||
select CURRENT_USER() from t1;
|
||
CURRENT_USER()
|
||
select benchmark(1,1) from t1;
|
||
benchmark(1,1)
|
||
explain extended select benchmark(1,1) from t1;
|
||
id select_type table type possible_keys key key_len ref rows filtered Extra
|
||
1 SIMPLE t1 ALL NULL NULL NULL NULL 1 100.00
|
||
Warnings:
|
||
Note 1003 select benchmark(1,1) AS `benchmark(1,1)` from `test`.`t1`
|
||
show status like "Qcache_queries_in_cache";
|
||
Variable_name Value
|
||
Qcache_queries_in_cache 0
|
||
show status like "Qcache_hits";
|
||
Variable_name Value
|
||
Qcache_hits 4
|
||
# On node-2
|
||
select CONNECTION_ID() from t1;
|
||
CONNECTION_ID()
|
||
select FOUND_ROWS();
|
||
FOUND_ROWS()
|
||
0
|
||
select NOW() from t1;
|
||
NOW()
|
||
select CURDATE() from t1;
|
||
CURDATE()
|
||
select CURTIME() from t1;
|
||
CURTIME()
|
||
select DATABASE() from t1;
|
||
DATABASE()
|
||
select ENCRYPT("test") from t1;
|
||
ENCRYPT("test")
|
||
select LAST_INSERT_ID() from t1;
|
||
LAST_INSERT_ID()
|
||
select RAND() from t1;
|
||
RAND()
|
||
select UNIX_TIMESTAMP() from t1;
|
||
UNIX_TIMESTAMP()
|
||
select USER() from t1;
|
||
USER()
|
||
select CURRENT_USER() from t1;
|
||
CURRENT_USER()
|
||
select benchmark(1,1) from t1;
|
||
benchmark(1,1)
|
||
explain extended select benchmark(1,1) from t1;
|
||
id select_type table type possible_keys key key_len ref rows filtered Extra
|
||
1 SIMPLE t1 ALL NULL NULL NULL NULL 1 100.00
|
||
Warnings:
|
||
Note 1003 select benchmark(1,1) AS `benchmark(1,1)` from `test`.`t1`
|
||
show status like "Qcache_queries_in_cache";
|
||
Variable_name Value
|
||
Qcache_queries_in_cache 0
|
||
show status like "Qcache_hits";
|
||
Variable_name Value
|
||
Qcache_hits 3
|
||
drop table t1;
|
||
# On node-1
|
||
create database mysqltest;
|
||
create table mysqltest.t1 (i int not null auto_increment, a int, primary key
|
||
(i)) engine=innodb;
|
||
insert into mysqltest.t1 values (1, 1);
|
||
select * from mysqltest.t1 where i is null;
|
||
i a
|
||
create table t1(a int) engine=innodb;
|
||
select * from t1;
|
||
a
|
||
show status like "Qcache_queries_in_cache";
|
||
Variable_name Value
|
||
Qcache_queries_in_cache 2
|
||
show status like "Qcache_hits";
|
||
Variable_name Value
|
||
Qcache_hits 4
|
||
select * from mysqltest.t1;
|
||
i a
|
||
1 1
|
||
show status like "Qcache_queries_in_cache";
|
||
Variable_name Value
|
||
Qcache_queries_in_cache 3
|
||
show status like "Qcache_hits";
|
||
Variable_name Value
|
||
Qcache_hits 4
|
||
# On node-2
|
||
select * from t1;
|
||
a
|
||
show status like "Qcache_queries_in_cache";
|
||
Variable_name Value
|
||
Qcache_queries_in_cache 1
|
||
show status like "Qcache_hits";
|
||
Variable_name Value
|
||
Qcache_hits 3
|
||
select * from mysqltest.t1;
|
||
i a
|
||
1 1
|
||
show status like "Qcache_queries_in_cache";
|
||
Variable_name Value
|
||
Qcache_queries_in_cache 2
|
||
show status like "Qcache_hits";
|
||
Variable_name Value
|
||
Qcache_hits 3
|
||
drop database mysqltest;
|
||
show status like "Qcache_queries_in_cache";
|
||
Variable_name Value
|
||
Qcache_queries_in_cache 1
|
||
show status like "Qcache_hits";
|
||
Variable_name Value
|
||
Qcache_hits 3
|
||
drop table t1;
|
||
# On node-1
|
||
create table t1 (a char(1) not null collate koi8r_general_ci) engine=innodb;
|
||
insert into t1 values(_koi8r"á");
|
||
Warnings:
|
||
Warning 1265 Data truncated for column 'a' at row 1
|
||
set CHARACTER SET koi8r;
|
||
select * from t1;
|
||
a
|
||
<EFBFBD>
|
||
set CHARACTER SET cp1251_koi8;
|
||
select * from t1;
|
||
a
|
||
<EFBFBD>
|
||
set CHARACTER SET DEFAULT;
|
||
show status like "Qcache_queries_in_cache";
|
||
Variable_name Value
|
||
Qcache_queries_in_cache 2
|
||
show status like "Qcache_hits";
|
||
Variable_name Value
|
||
Qcache_hits 4
|
||
# On node-2
|
||
set CHARACTER SET koi8r;
|
||
select * from t1;
|
||
a
|
||
<EFBFBD>
|
||
set CHARACTER SET cp1251_koi8;
|
||
select * from t1;
|
||
a
|
||
<EFBFBD>
|
||
set CHARACTER SET DEFAULT;
|
||
show status like "Qcache_queries_in_cache";
|
||
Variable_name Value
|
||
Qcache_queries_in_cache 2
|
||
show status like "Qcache_hits";
|
||
Variable_name Value
|
||
Qcache_hits 3
|
||
drop table t1;
|
||
# On node-1
|
||
create database if not exists mysqltest;
|
||
create table mysqltest.t1 (i int not null) engine=innodb;
|
||
create table t1 (i int not null) engine=innodb;
|
||
insert into mysqltest.t1 (i) values (1);
|
||
insert into t1 (i) values (2);
|
||
select * from t1;
|
||
i
|
||
2
|
||
use mysqltest;
|
||
select * from t1;
|
||
i
|
||
1
|
||
select * from t1;
|
||
i
|
||
1
|
||
use test;
|
||
select * from t1;
|
||
i
|
||
2
|
||
show status like "Qcache_queries_in_cache";
|
||
Variable_name Value
|
||
Qcache_queries_in_cache 2
|
||
show status like "Qcache_hits";
|
||
Variable_name Value
|
||
Qcache_hits 6
|
||
# On node-2
|
||
select * from t1;
|
||
i
|
||
2
|
||
use mysqltest;
|
||
select * from t1;
|
||
i
|
||
1
|
||
select * from t1;
|
||
i
|
||
1
|
||
use test;
|
||
select * from t1;
|
||
i
|
||
2
|
||
show status like "Qcache_queries_in_cache";
|
||
Variable_name Value
|
||
Qcache_queries_in_cache 2
|
||
show status like "Qcache_hits";
|
||
Variable_name Value
|
||
Qcache_hits 5
|
||
drop database mysqltest;
|
||
drop table t1;
|
||
# On node-1
|
||
create table t1 (i int not null) engine=innodb;
|
||
insert into t1 (i) values (1),(2),(3),(4);
|
||
select SQL_CALC_FOUND_ROWS * from t1 limit 2;
|
||
i
|
||
1
|
||
2
|
||
select FOUND_ROWS();
|
||
FOUND_ROWS()
|
||
4
|
||
show status like "Qcache_hits";
|
||
Variable_name Value
|
||
Qcache_hits 6
|
||
show status like "Qcache_queries_in_cache";
|
||
Variable_name Value
|
||
Qcache_queries_in_cache 1
|
||
select * from t1 where i=1;
|
||
i
|
||
1
|
||
select FOUND_ROWS();
|
||
FOUND_ROWS()
|
||
1
|
||
show status like "Qcache_hits";
|
||
Variable_name Value
|
||
Qcache_hits 6
|
||
show status like "Qcache_queries_in_cache";
|
||
Variable_name Value
|
||
Qcache_queries_in_cache 2
|
||
select SQL_CALC_FOUND_ROWS * from t1 limit 2;
|
||
i
|
||
1
|
||
2
|
||
select FOUND_ROWS();
|
||
FOUND_ROWS()
|
||
4
|
||
show status like "Qcache_hits";
|
||
Variable_name Value
|
||
Qcache_hits 7
|
||
show status like "Qcache_queries_in_cache";
|
||
Variable_name Value
|
||
Qcache_queries_in_cache 2
|
||
select * from t1 where i=1;
|
||
i
|
||
1
|
||
select FOUND_ROWS();
|
||
FOUND_ROWS()
|
||
1
|
||
show status like "Qcache_hits";
|
||
Variable_name Value
|
||
Qcache_hits 8
|
||
show status like "Qcache_queries_in_cache";
|
||
Variable_name Value
|
||
Qcache_queries_in_cache 2
|
||
# On node-2
|
||
select SQL_CALC_FOUND_ROWS * from t1 limit 2;
|
||
i
|
||
1
|
||
2
|
||
select FOUND_ROWS();
|
||
FOUND_ROWS()
|
||
4
|
||
show status like "Qcache_hits";
|
||
Variable_name Value
|
||
Qcache_hits 5
|
||
show status like "Qcache_queries_in_cache";
|
||
Variable_name Value
|
||
Qcache_queries_in_cache 1
|
||
select * from t1 where i=1;
|
||
i
|
||
1
|
||
select FOUND_ROWS();
|
||
FOUND_ROWS()
|
||
1
|
||
show status like "Qcache_hits";
|
||
Variable_name Value
|
||
Qcache_hits 5
|
||
show status like "Qcache_queries_in_cache";
|
||
Variable_name Value
|
||
Qcache_queries_in_cache 2
|
||
select SQL_CALC_FOUND_ROWS * from t1 limit 2;
|
||
i
|
||
1
|
||
2
|
||
select FOUND_ROWS();
|
||
FOUND_ROWS()
|
||
4
|
||
show status like "Qcache_hits";
|
||
Variable_name Value
|
||
Qcache_hits 6
|
||
show status like "Qcache_queries_in_cache";
|
||
Variable_name Value
|
||
Qcache_queries_in_cache 2
|
||
select * from t1 where i=1;
|
||
i
|
||
1
|
||
select FOUND_ROWS();
|
||
FOUND_ROWS()
|
||
1
|
||
show status like "Qcache_hits";
|
||
Variable_name Value
|
||
Qcache_hits 7
|
||
show status like "Qcache_queries_in_cache";
|
||
Variable_name Value
|
||
Qcache_queries_in_cache 2
|
||
drop table t1;
|
||
# On node-2
|
||
flush query cache;
|
||
reset query cache;
|
||
# On node-1
|
||
flush query cache;
|
||
reset query cache;
|
||
create table t1 (a int not null) ENGINE=MYISAM;
|
||
insert into t1 values (1),(2),(3);
|
||
select * from t1;
|
||
a
|
||
1
|
||
2
|
||
3
|
||
select * from t1;
|
||
a
|
||
1
|
||
2
|
||
3
|
||
show status like "Qcache_queries_in_cache";
|
||
Variable_name Value
|
||
Qcache_queries_in_cache 1
|
||
show status like "Qcache_hits";
|
||
Variable_name Value
|
||
Qcache_hits 9
|
||
insert delayed into t1 values (4);
|
||
select a from t1;
|
||
a
|
||
1
|
||
2
|
||
3
|
||
4
|
||
show status like "Qcache_queries_in_cache";
|
||
Variable_name Value
|
||
Qcache_queries_in_cache 1
|
||
show status like "Qcache_hits";
|
||
Variable_name Value
|
||
Qcache_hits 9
|
||
# On node-2
|
||
select * from t1;
|
||
a
|
||
select * from t1;
|
||
a
|
||
show status like "Qcache_queries_in_cache";
|
||
Variable_name Value
|
||
Qcache_queries_in_cache 1
|
||
show status like "Qcache_hits";
|
||
Variable_name Value
|
||
Qcache_hits 8
|
||
insert delayed into t1 values (4);
|
||
select a from t1;
|
||
a
|
||
4
|
||
show status like "Qcache_queries_in_cache";
|
||
Variable_name Value
|
||
Qcache_queries_in_cache 1
|
||
show status like "Qcache_hits";
|
||
Variable_name Value
|
||
Qcache_hits 8
|
||
drop table t1;
|
||
show status like "Qcache_queries_in_cache";
|
||
Variable_name Value
|
||
Qcache_queries_in_cache 0
|
||
show status like "Qcache_hits";
|
||
Variable_name Value
|
||
Qcache_hits 8
|
||
# On node-1
|
||
show status like "Qcache_queries_in_cache";
|
||
Variable_name Value
|
||
Qcache_queries_in_cache 0
|
||
show status like "Qcache_hits";
|
||
Variable_name Value
|
||
Qcache_hits 9
|
||
# On node-2
|
||
show global variables like "query_cache_min_res_unit";
|
||
Variable_name Value
|
||
query_cache_min_res_unit 4096
|
||
set GLOBAL query_cache_min_res_unit=1001;
|
||
Warnings:
|
||
Warning 1292 Truncated incorrect query_cache_min_res_unit value: '1001'
|
||
show global variables like "query_cache_min_res_unit";
|
||
Variable_name Value
|
||
query_cache_min_res_unit 1000
|
||
# On node-1
|
||
show global variables like "query_cache_min_res_unit";
|
||
Variable_name Value
|
||
query_cache_min_res_unit 4096
|
||
set GLOBAL query_cache_min_res_unit=1001;
|
||
Warnings:
|
||
Warning 1292 Truncated incorrect query_cache_min_res_unit value: '1001'
|
||
show global variables like "query_cache_min_res_unit";
|
||
Variable_name Value
|
||
query_cache_min_res_unit 1000
|
||
create table t1 (a int not null) engine=innodb;
|
||
insert into t1 values (1),(2),(3);
|
||
create table t2 (a int not null) engine=innodb;
|
||
insert into t2 values (1),(2),(3);
|
||
select * from t1;
|
||
a
|
||
1
|
||
2
|
||
3
|
||
select * from t1;
|
||
a
|
||
1
|
||
2
|
||
3
|
||
select * from t2;
|
||
a
|
||
1
|
||
2
|
||
3
|
||
select * from t2;
|
||
a
|
||
1
|
||
2
|
||
3
|
||
show status like "Qcache_hits";
|
||
Variable_name Value
|
||
Qcache_hits 11
|
||
show status like "Qcache_queries_in_cache";
|
||
Variable_name Value
|
||
Qcache_queries_in_cache 2
|
||
# On node-2
|
||
select * from t1;
|
||
a
|
||
1
|
||
2
|
||
3
|
||
select * from t1;
|
||
a
|
||
1
|
||
2
|
||
3
|
||
select * from t2;
|
||
a
|
||
1
|
||
2
|
||
3
|
||
select * from t2;
|
||
a
|
||
1
|
||
2
|
||
3
|
||
show status like "Qcache_hits";
|
||
Variable_name Value
|
||
Qcache_hits 10
|
||
show status like "Qcache_queries_in_cache";
|
||
Variable_name Value
|
||
Qcache_queries_in_cache 2
|
||
drop table t1;
|
||
select a from t2;
|
||
a
|
||
1
|
||
2
|
||
3
|
||
select a from t2;
|
||
a
|
||
1
|
||
2
|
||
3
|
||
show status like "Qcache_hits";
|
||
Variable_name Value
|
||
Qcache_hits 11
|
||
show status like "Qcache_queries_in_cache";
|
||
Variable_name Value
|
||
Qcache_queries_in_cache 2
|
||
set GLOBAL query_cache_min_res_unit=default;
|
||
show global variables like "query_cache_min_res_unit";
|
||
Variable_name Value
|
||
query_cache_min_res_unit 4096
|
||
# On node-1
|
||
select a from t2;
|
||
a
|
||
1
|
||
2
|
||
3
|
||
select a from t2;
|
||
a
|
||
1
|
||
2
|
||
3
|
||
show status like "Qcache_hits";
|
||
Variable_name Value
|
||
Qcache_hits 12
|
||
show status like "Qcache_queries_in_cache";
|
||
Variable_name Value
|
||
Qcache_queries_in_cache 2
|
||
drop table t2;
|
||
set GLOBAL query_cache_min_res_unit=default;
|
||
show global variables like "query_cache_min_res_unit";
|
||
Variable_name Value
|
||
query_cache_min_res_unit 4096
|
||
# On node-1
|
||
create table t1 (a int not null) engine=innodb;
|
||
insert into t1 values (1);
|
||
select "aaa" from t1;
|
||
aaa
|
||
aaa
|
||
select "AAA" from t1;
|
||
AAA
|
||
AAA
|
||
show status like "Qcache_queries_in_cache";
|
||
Variable_name Value
|
||
Qcache_queries_in_cache 2
|
||
show status like "Qcache_hits";
|
||
Variable_name Value
|
||
Qcache_hits 12
|
||
# On node-2
|
||
select "aaa" from t1;
|
||
aaa
|
||
aaa
|
||
select "AAA" from t1;
|
||
AAA
|
||
AAA
|
||
show status like "Qcache_queries_in_cache";
|
||
Variable_name Value
|
||
Qcache_queries_in_cache 2
|
||
show status like "Qcache_hits";
|
||
Variable_name Value
|
||
Qcache_hits 11
|
||
drop table t1;
|
||
# On node-1
|
||
create table t1 (a int) engine=innodb;
|
||
set GLOBAL query_cache_size=1000;
|
||
Warnings:
|
||
Warning 1292 Truncated incorrect query_cache_size value: '1000'
|
||
show global variables like "query_cache_size";
|
||
Variable_name Value
|
||
query_cache_size 0
|
||
select * from t1;
|
||
a
|
||
set GLOBAL query_cache_size=1024;
|
||
Warnings:
|
||
Warning 1282 Query cache failed to set size 1024; new query cache size is 0
|
||
show global variables like "query_cache_size";
|
||
Variable_name Value
|
||
query_cache_size 0
|
||
select * from t1;
|
||
a
|
||
set GLOBAL query_cache_size=10240;
|
||
Warnings:
|
||
Warning 1282 Query cache failed to set size 10240; new query cache size is 0
|
||
show global variables like "query_cache_size";
|
||
Variable_name Value
|
||
query_cache_size 0
|
||
select * from t1;
|
||
a
|
||
set GLOBAL query_cache_size=20480;
|
||
Warnings:
|
||
Warning 1282 Query cache failed to set size 20480; new query cache size is 0
|
||
show global variables like "query_cache_size";
|
||
Variable_name Value
|
||
query_cache_size 0
|
||
select * from t1;
|
||
a
|
||
set GLOBAL query_cache_size=40960;
|
||
Warnings:
|
||
Warning 1282 Query cache failed to set size 40960; new query cache size is 0
|
||
show global variables like "query_cache_size";
|
||
Variable_name Value
|
||
query_cache_size 0
|
||
select * from t1;
|
||
a
|
||
set GLOBAL query_cache_size=51200;
|
||
show global variables like "query_cache_size";
|
||
Variable_name Value
|
||
query_cache_size 51200
|
||
select * from t1;
|
||
a
|
||
set GLOBAL query_cache_size=61440;
|
||
show global variables like "query_cache_size";
|
||
Variable_name Value
|
||
query_cache_size 61440
|
||
select * from t1;
|
||
a
|
||
set GLOBAL query_cache_size=81920;
|
||
show global variables like "query_cache_size";
|
||
Variable_name Value
|
||
query_cache_size 81920
|
||
select * from t1;
|
||
a
|
||
set GLOBAL query_cache_size=102400;
|
||
show global variables like "query_cache_size";
|
||
Variable_name Value
|
||
query_cache_size 102400
|
||
select * from t1;
|
||
a
|
||
# On node-2
|
||
set GLOBAL query_cache_size=1000;
|
||
Warnings:
|
||
Warning 1292 Truncated incorrect query_cache_size value: '1000'
|
||
show global variables like "query_cache_size";
|
||
Variable_name Value
|
||
query_cache_size 0
|
||
select * from t1;
|
||
a
|
||
set GLOBAL query_cache_size=1024;
|
||
Warnings:
|
||
Warning 1282 Query cache failed to set size 1024; new query cache size is 0
|
||
show global variables like "query_cache_size";
|
||
Variable_name Value
|
||
query_cache_size 0
|
||
select * from t1;
|
||
a
|
||
set GLOBAL query_cache_size=10240;
|
||
Warnings:
|
||
Warning 1282 Query cache failed to set size 10240; new query cache size is 0
|
||
show global variables like "query_cache_size";
|
||
Variable_name Value
|
||
query_cache_size 0
|
||
select * from t1;
|
||
a
|
||
set GLOBAL query_cache_size=20480;
|
||
Warnings:
|
||
Warning 1282 Query cache failed to set size 20480; new query cache size is 0
|
||
show global variables like "query_cache_size";
|
||
Variable_name Value
|
||
query_cache_size 0
|
||
select * from t1;
|
||
a
|
||
set GLOBAL query_cache_size=40960;
|
||
Warnings:
|
||
Warning 1282 Query cache failed to set size 40960; new query cache size is 0
|
||
show global variables like "query_cache_size";
|
||
Variable_name Value
|
||
query_cache_size 0
|
||
select * from t1;
|
||
a
|
||
set GLOBAL query_cache_size=51200;
|
||
show global variables like "query_cache_size";
|
||
Variable_name Value
|
||
query_cache_size 51200
|
||
select * from t1;
|
||
a
|
||
set GLOBAL query_cache_size=61440;
|
||
show global variables like "query_cache_size";
|
||
Variable_name Value
|
||
query_cache_size 61440
|
||
select * from t1;
|
||
a
|
||
set GLOBAL query_cache_size=81920;
|
||
show global variables like "query_cache_size";
|
||
Variable_name Value
|
||
query_cache_size 81920
|
||
select * from t1;
|
||
a
|
||
set GLOBAL query_cache_size=102400;
|
||
show global variables like "query_cache_size";
|
||
Variable_name Value
|
||
query_cache_size 102400
|
||
select * from t1;
|
||
a
|
||
drop table t1;
|
||
# On node-1
|
||
set GLOBAL query_cache_size=1048576;
|
||
create table t1 (i int not null) engine=innodb;
|
||
create table t2 (i int not null) engine=innodb;
|
||
select * from t1;
|
||
i
|
||
show status like "Qcache_queries_in_cache";
|
||
Variable_name Value
|
||
Qcache_queries_in_cache 1
|
||
show status like "Qcache_hits";
|
||
Variable_name Value
|
||
Qcache_hits 12
|
||
create temporary table t3 (i int not null);
|
||
select * from t2;
|
||
i
|
||
show status like "Qcache_queries_in_cache";
|
||
Variable_name Value
|
||
Qcache_queries_in_cache 2
|
||
show status like "Qcache_hits";
|
||
Variable_name Value
|
||
Qcache_hits 12
|
||
select * from t3;
|
||
i
|
||
show status like "Qcache_queries_in_cache";
|
||
Variable_name Value
|
||
Qcache_queries_in_cache 2
|
||
show status like "Qcache_hits";
|
||
Variable_name Value
|
||
Qcache_hits 12
|
||
update t1 set i=(select distinct 1 from (select * from t2) a);
|
||
drop table t3;
|
||
# On node-2
|
||
set GLOBAL query_cache_size=1048576;
|
||
select * from t1;
|
||
i
|
||
show status like "Qcache_queries_in_cache";
|
||
Variable_name Value
|
||
Qcache_queries_in_cache 1
|
||
show status like "Qcache_hits";
|
||
Variable_name Value
|
||
Qcache_hits 11
|
||
select * from t2;
|
||
i
|
||
show status like "Qcache_queries_in_cache";
|
||
Variable_name Value
|
||
Qcache_queries_in_cache 2
|
||
show status like "Qcache_hits";
|
||
Variable_name Value
|
||
Qcache_hits 11
|
||
drop table t1, t2;
|
||
# On node-1
|
||
use mysql;
|
||
select * from db;
|
||
show status like "Qcache_queries_in_cache";
|
||
Variable_name Value
|
||
Qcache_queries_in_cache 0
|
||
show status like "Qcache_hits";
|
||
Variable_name Value
|
||
Qcache_hits 12
|
||
use test;
|
||
select * from mysql.db;
|
||
show status like "Qcache_queries_in_cache";
|
||
Variable_name Value
|
||
Qcache_queries_in_cache 0
|
||
show status like "Qcache_hits";
|
||
Variable_name Value
|
||
Qcache_hits 12
|
||
# On node-1
|
||
create table t1(id int auto_increment primary key) engine=innodb;
|
||
insert into t1 values (1), (2), (3);
|
||
select * from t1;
|
||
id
|
||
1
|
||
2
|
||
3
|
||
show status like "Qcache_queries_in_cache";
|
||
Variable_name Value
|
||
Qcache_queries_in_cache 1
|
||
show status like "Qcache_hits";
|
||
Variable_name Value
|
||
Qcache_hits 12
|
||
# On node-2
|
||
select * from t1;
|
||
id
|
||
1
|
||
2
|
||
3
|
||
show status like "Qcache_queries_in_cache";
|
||
Variable_name Value
|
||
Qcache_queries_in_cache 1
|
||
show status like "Qcache_hits";
|
||
Variable_name Value
|
||
Qcache_hits 11
|
||
# On node-1
|
||
alter table t1 rename to t2;
|
||
select * from t1;
|
||
ERROR 42S02: Table 'test.t1' doesn't exist
|
||
show status like "Qcache_queries_in_cache";
|
||
Variable_name Value
|
||
Qcache_queries_in_cache 0
|
||
show status like "Qcache_hits";
|
||
Variable_name Value
|
||
Qcache_hits 12
|
||
# On node-2
|
||
select * from t1;
|
||
ERROR 42S02: Table 'test.t1' doesn't exist
|
||
show status like "Qcache_queries_in_cache";
|
||
Variable_name Value
|
||
Qcache_queries_in_cache 0
|
||
show status like "Qcache_hits";
|
||
Variable_name Value
|
||
Qcache_hits 11
|
||
drop table t2;
|
||
# On node-1
|
||
create table t1 (word char(20) not null) engine=innodb;
|
||
select * from t1;
|
||
word
|
||
show status like "Qcache_queries_in_cache";
|
||
Variable_name Value
|
||
Qcache_queries_in_cache 1
|
||
show status like "Qcache_hits";
|
||
Variable_name Value
|
||
Qcache_hits 12
|
||
load data infile 'MYSQLTEST_VARDIR/std_data/words.dat' into table t1;
|
||
show status like "Qcache_queries_in_cache";
|
||
Variable_name Value
|
||
Qcache_queries_in_cache 0
|
||
show status like "Qcache_hits";
|
||
Variable_name Value
|
||
Qcache_hits 12
|
||
select count(*) from t1;
|
||
count(*)
|
||
70
|
||
# On node-2
|
||
select count(*) from t1;
|
||
count(*)
|
||
70
|
||
show status like "Qcache_queries_in_cache";
|
||
Variable_name Value
|
||
Qcache_queries_in_cache 1
|
||
show status like "Qcache_hits";
|
||
Variable_name Value
|
||
Qcache_hits 11
|
||
load data infile 'MYSQLTEST_VARDIR/std_data/words.dat' into table t1;
|
||
show status like "Qcache_queries_in_cache";
|
||
Variable_name Value
|
||
Qcache_queries_in_cache 0
|
||
show status like "Qcache_hits";
|
||
Variable_name Value
|
||
Qcache_hits 11
|
||
select count(*) from t1;
|
||
count(*)
|
||
140
|
||
drop table t1;
|
||
# On node-1
|
||
create table t1 (a int) engine=innodb;
|
||
insert into t1 values (1),(2),(3);
|
||
show status like "Qcache_queries_in_cache";
|
||
Variable_name Value
|
||
Qcache_queries_in_cache 0
|
||
show status like "Qcache_hits";
|
||
Variable_name Value
|
||
Qcache_hits 12
|
||
select * from t1 into outfile "query_cache.out.file";
|
||
select * from t1 into outfile "query_cache.out.file";
|
||
ERROR HY000: File 'query_cache.out.file' already exists
|
||
select * from t1 limit 1 into dumpfile "query_cache.dump.file";
|
||
show status like "Qcache_queries_in_cache";
|
||
Variable_name Value
|
||
Qcache_queries_in_cache 0
|
||
show status like "Qcache_hits";
|
||
Variable_name Value
|
||
Qcache_hits 12
|
||
drop table t1;
|
||
# On node-1
|
||
create table t1 (a int) engine=innodb;
|
||
insert into t1 values (1),(2);
|
||
show status like "Qcache_queries_in_cache";
|
||
Variable_name Value
|
||
Qcache_queries_in_cache 0
|
||
show status like "Qcache_hits";
|
||
Variable_name Value
|
||
Qcache_hits 12
|
||
select * from t1;
|
||
a
|
||
1
|
||
2
|
||
SET SQL_SELECT_LIMIT=1;
|
||
select * from t1;
|
||
a
|
||
1
|
||
show status like "Qcache_queries_in_cache";
|
||
Variable_name Value
|
||
Qcache_queries_in_cache 2
|
||
show status like "Qcache_hits";
|
||
Variable_name Value
|
||
Qcache_hits 12
|
||
SET SQL_SELECT_LIMIT=DEFAULT;
|
||
# On node-2
|
||
show status like "Qcache_queries_in_cache";
|
||
Variable_name Value
|
||
Qcache_queries_in_cache 0
|
||
show status like "Qcache_hits";
|
||
Variable_name Value
|
||
Qcache_hits 11
|
||
select * from t1;
|
||
a
|
||
1
|
||
2
|
||
SET SQL_SELECT_LIMIT=1;
|
||
select * from t1;
|
||
a
|
||
1
|
||
show status like "Qcache_queries_in_cache";
|
||
Variable_name Value
|
||
Qcache_queries_in_cache 2
|
||
show status like "Qcache_hits";
|
||
Variable_name Value
|
||
Qcache_hits 11
|
||
SET SQL_SELECT_LIMIT=DEFAULT;
|
||
drop table t1;
|
||
# On node-1
|
||
create table t1 (a int not null) engine=innodb;
|
||
create table t2 (a int not null) engine=innodb;
|
||
set query_cache_wlock_invalidate=1;
|
||
create view v1 as select * from t1;
|
||
select * from t1;
|
||
a
|
||
select * from t2;
|
||
a
|
||
show status like "Qcache_queries_in_cache";
|
||
Variable_name Value
|
||
Qcache_queries_in_cache 2
|
||
show status like "Qcache_hits";
|
||
Variable_name Value
|
||
Qcache_hits 12
|
||
lock table t1 write, t2 read;
|
||
show status like "Qcache_queries_in_cache";
|
||
Variable_name Value
|
||
Qcache_queries_in_cache 1
|
||
show status like "Qcache_hits";
|
||
Variable_name Value
|
||
Qcache_hits 12
|
||
unlock table;
|
||
select * from t1;
|
||
a
|
||
show status like "Qcache_queries_in_cache";
|
||
Variable_name Value
|
||
Qcache_queries_in_cache 2
|
||
show status like "Qcache_hits";
|
||
Variable_name Value
|
||
Qcache_hits 12
|
||
lock table v1 write;
|
||
show status like "Qcache_queries_in_cache";
|
||
Variable_name Value
|
||
Qcache_queries_in_cache 2
|
||
show status like "Qcache_hits";
|
||
Variable_name Value
|
||
Qcache_hits 12
|
||
unlock table;
|
||
drop view v1;
|
||
set query_cache_wlock_invalidate=default;
|
||
# On node-2
|
||
set query_cache_wlock_invalidate=1;
|
||
create view v1 as select * from t1;
|
||
select * from t1;
|
||
a
|
||
select * from t2;
|
||
a
|
||
show status like "Qcache_queries_in_cache";
|
||
Variable_name Value
|
||
Qcache_queries_in_cache 2
|
||
show status like "Qcache_hits";
|
||
Variable_name Value
|
||
Qcache_hits 11
|
||
lock table t1 write, t2 read;
|
||
show status like "Qcache_queries_in_cache";
|
||
Variable_name Value
|
||
Qcache_queries_in_cache 1
|
||
show status like "Qcache_hits";
|
||
Variable_name Value
|
||
Qcache_hits 11
|
||
unlock table;
|
||
select * from t1;
|
||
a
|
||
show status like "Qcache_queries_in_cache";
|
||
Variable_name Value
|
||
Qcache_queries_in_cache 2
|
||
show status like "Qcache_hits";
|
||
Variable_name Value
|
||
Qcache_hits 11
|
||
lock table v1 write;
|
||
show status like "Qcache_queries_in_cache";
|
||
Variable_name Value
|
||
Qcache_queries_in_cache 2
|
||
show status like "Qcache_hits";
|
||
Variable_name Value
|
||
Qcache_hits 11
|
||
unlock table;
|
||
drop view v1;
|
||
set query_cache_wlock_invalidate=default;
|
||
drop table t1,t2;
|
||
# On node-1
|
||
create table t1 (id int primary key) engine=innodb;
|
||
insert into t1 values (1),(2),(3);
|
||
select * from t1;
|
||
id
|
||
1
|
||
2
|
||
3
|
||
create temporary table t1 (a int not null auto_increment primary key);
|
||
select * from t1;
|
||
a
|
||
drop table t1;
|
||
drop table t1;
|
||
# On node-1
|
||
SET NAMES koi8r;
|
||
CREATE TABLE t1 (a char(1) character set koi8r) engine=innodb;
|
||
INSERT INTO t1 VALUES (_koi8r'á'),(_koi8r'Á');
|
||
Warnings:
|
||
Warning 1265 Data truncated for column 'a' at row 1
|
||
Warning 1265 Data truncated for column 'a' at row 2
|
||
SELECT a,'Â','â'='Â' FROM t1;
|
||
a  'â'='Â'
|
||
<EFBFBD> Â 0
|
||
<EFBFBD> Â 0
|
||
show status like "Qcache_hits";
|
||
Variable_name Value
|
||
Qcache_hits 12
|
||
show status like "Qcache_queries_in_cache";
|
||
Variable_name Value
|
||
Qcache_queries_in_cache 1
|
||
set collation_connection=koi8r_bin;
|
||
SELECT a,'Â','â'='Â' FROM t1;
|
||
a  'â'='Â'
|
||
<EFBFBD> Â 0
|
||
<EFBFBD> Â 0
|
||
show status like "Qcache_hits";
|
||
Variable_name Value
|
||
Qcache_hits 12
|
||
show status like "Qcache_queries_in_cache";
|
||
Variable_name Value
|
||
Qcache_queries_in_cache 2
|
||
set character_set_client=cp1251;
|
||
SELECT a,'Â','â'='Â' FROM t1;
|
||
a <09>? '<27>?'='<27>?'
|
||
<EFBFBD> <09>? 1
|
||
<EFBFBD> <09>? 1
|
||
show status like "Qcache_hits";
|
||
Variable_name Value
|
||
Qcache_hits 12
|
||
show status like "Qcache_queries_in_cache";
|
||
Variable_name Value
|
||
Qcache_queries_in_cache 3
|
||
set character_set_results=cp1251;
|
||
SELECT a,'Â','â'='Â' FROM t1;
|
||
a <09>? 'â'='Â'
|
||
<EFBFBD> <09>? 1
|
||
<EFBFBD> <09>? 1
|
||
show status like "Qcache_hits";
|
||
Variable_name Value
|
||
Qcache_hits 12
|
||
show status like "Qcache_queries_in_cache";
|
||
Variable_name Value
|
||
Qcache_queries_in_cache 4
|
||
SET NAMES default;
|
||
# On node-2
|
||
SELECT a,'Â','â'='Â' FROM t1;
|
||
a  'â'='Â'
|
||
? Â 0
|
||
? Â 0
|
||
show status like "Qcache_hits";
|
||
Variable_name Value
|
||
Qcache_hits 11
|
||
show status like "Qcache_queries_in_cache";
|
||
Variable_name Value
|
||
Qcache_queries_in_cache 1
|
||
set collation_connection=koi8r_bin;
|
||
SELECT a,'Â','â'='Â' FROM t1;
|
||
a ?? 'â'='Â'
|
||
? ?? 1
|
||
? ?? 1
|
||
show status like "Qcache_hits";
|
||
Variable_name Value
|
||
Qcache_hits 11
|
||
show status like "Qcache_queries_in_cache";
|
||
Variable_name Value
|
||
Qcache_queries_in_cache 2
|
||
set character_set_client=cp1251;
|
||
SELECT a,'Â','â'='Â' FROM t1;
|
||
a ?? '??'='?<3F>'
|
||
? ?? 1
|
||
? ?? 1
|
||
show status like "Qcache_hits";
|
||
Variable_name Value
|
||
Qcache_hits 11
|
||
show status like "Qcache_queries_in_cache";
|
||
Variable_name Value
|
||
Qcache_queries_in_cache 3
|
||
set character_set_results=cp1251;
|
||
SELECT a,'Â','â'='Â' FROM t1;
|
||
a <09>? 'â'='Â'
|
||
<EFBFBD> <09>? 1
|
||
<EFBFBD> <09>? 1
|
||
show status like "Qcache_hits";
|
||
Variable_name Value
|
||
Qcache_hits 11
|
||
show status like "Qcache_queries_in_cache";
|
||
Variable_name Value
|
||
Qcache_queries_in_cache 4
|
||
drop table t1;
|
||
# On node-1
|
||
create table t1 (a int) engine=innodb;
|
||
show status like "Qcache_queries_in_cache";
|
||
Variable_name Value
|
||
Qcache_queries_in_cache 0
|
||
show status like "Qcache_inserts";
|
||
Variable_name Value
|
||
Qcache_inserts 46
|
||
show status like "Qcache_hits";
|
||
Variable_name Value
|
||
Qcache_hits 12
|
||
/**/ select * from t1;
|
||
a
|
||
/**/ select * from t1;
|
||
a
|
||
show status like "Qcache_queries_in_cache";
|
||
Variable_name Value
|
||
Qcache_queries_in_cache 1
|
||
show status like "Qcache_inserts";
|
||
Variable_name Value
|
||
Qcache_inserts 47
|
||
show status like "Qcache_hits";
|
||
Variable_name Value
|
||
Qcache_hits 13
|
||
# On node-2
|
||
show status like "Qcache_queries_in_cache";
|
||
Variable_name Value
|
||
Qcache_queries_in_cache 0
|
||
show status like "Qcache_inserts";
|
||
Variable_name Value
|
||
Qcache_inserts 38
|
||
show status like "Qcache_hits";
|
||
Variable_name Value
|
||
Qcache_hits 11
|
||
/**/ select * from t1;
|
||
a
|
||
/**/ select * from t1;
|
||
a
|
||
show status like "Qcache_queries_in_cache";
|
||
Variable_name Value
|
||
Qcache_queries_in_cache 1
|
||
show status like "Qcache_inserts";
|
||
Variable_name Value
|
||
Qcache_inserts 39
|
||
show status like "Qcache_hits";
|
||
Variable_name Value
|
||
Qcache_hits 12
|
||
drop table t1;
|
||
# On node-1
|
||
set session query_cache_type = 2;
|
||
create table t1(a int) engine=innodb;
|
||
select table_name from information_schema.tables
|
||
where table_schema="test";
|
||
table_name
|
||
t1
|
||
drop table t1;
|
||
select table_name from information_schema.tables
|
||
where table_schema="test";
|
||
table_name
|
||
set session query_cache_type = 1;
|
||
set global query_cache_size=1024*1024;
|
||
flush query cache;
|
||
create table t1 ( a int ) engine=myisam;
|
||
insert into t1 values (1);
|
||
select a from t1;
|
||
a
|
||
1
|
||
select a from t1;
|
||
a
|
||
1
|
||
show status like 'qcache_queries_in_cache';
|
||
Variable_name Value
|
||
Qcache_queries_in_cache 1
|
||
show status like "Qcache_hits";
|
||
Variable_name Value
|
||
Qcache_hits 14
|
||
repair table t1;
|
||
Table Op Msg_type Msg_text
|
||
test.t1 repair status OK
|
||
show status like 'qcache_queries_in_cache';
|
||
Variable_name Value
|
||
Qcache_queries_in_cache 0
|
||
show status like "Qcache_hits";
|
||
Variable_name Value
|
||
Qcache_hits 14
|
||
# On node-2
|
||
select a from t1;
|
||
a
|
||
select a from t1;
|
||
a
|
||
show status like 'qcache_queries_in_cache';
|
||
Variable_name Value
|
||
Qcache_queries_in_cache 1
|
||
show status like "Qcache_hits";
|
||
Variable_name Value
|
||
Qcache_hits 13
|
||
repair table t1;
|
||
Table Op Msg_type Msg_text
|
||
test.t1 repair status OK
|
||
show status like 'qcache_queries_in_cache';
|
||
Variable_name Value
|
||
Qcache_queries_in_cache 0
|
||
show status like "Qcache_hits";
|
||
Variable_name Value
|
||
Qcache_hits 13
|
||
drop table t1;
|
||
# Restore original settings.
|
||
# On node-1
|
||
SET GLOBAL query_cache_size=@query_cache_size_saved;
|
||
SET GLOBAL query_cache_type=@query_cache_type_saved;
|
||
|
||
# On node-2
|
||
SET GLOBAL query_cache_size=@query_cache_size_saved;
|
||
SET GLOBAL query_cache_type=@query_cache_type_saved;
|
||
# End of test
|