diff options
Diffstat (limited to 'mysql-test/suite/percona')
29 files changed, 1805 insertions, 0 deletions
diff --git a/mysql-test/suite/percona/disabled.def b/mysql-test/suite/percona/disabled.def index 4f35807b7a1..a99b952a5eb 100644 --- a/mysql-test/suite/percona/disabled.def +++ b/mysql-test/suite/percona/disabled.def @@ -8,6 +8,15 @@ percona_query_cache_with_comments_prepared_statements: Feature not merged int percona_show_temp_tables: Feature not merged into MariaDB percona_slow_query_log-use_global_long_query_time: Feature not merged into MariaDB percona_query_cache_with_comments_disable: Feature not merged into MariaDB +percona_log_connection_error: Feature not merged into MariaDB +percona_query_response_time: Feature not merged into MariaDB +percona_query_response_time-stored: Feature not merged into MariaDB +percona_sql_no_fcache: Feature not merged into MariaDB +percona_status_wait_query_cache_mutex: Feature not merged into MariaDB +percona_slave_innodb_stats: Feature not merged into MariaDB +percona_query_response_time-replication: Feature not merged into MariaDB +percona_server_variables: Feature not merged into MariaDB percona_slow_query_log-log_slow_verbosity: InnoDB filtering information not fully merged into MariaDB +percona_innodb_buffer_pool_shm: Requires big shmmax not default on many systems diff --git a/mysql-test/suite/percona/percona_innodb_buffer_pool_shm-master.opt b/mysql-test/suite/percona/percona_innodb_buffer_pool_shm-master.opt new file mode 100644 index 00000000000..5974ef6e2be --- /dev/null +++ b/mysql-test/suite/percona/percona_innodb_buffer_pool_shm-master.opt @@ -0,0 +1 @@ +--innodb_buffer_pool_shm_key=123456 diff --git a/mysql-test/suite/percona/percona_innodb_buffer_pool_shm.result b/mysql-test/suite/percona/percona_innodb_buffer_pool_shm.result new file mode 100644 index 00000000000..08ece8fb9a9 --- /dev/null +++ b/mysql-test/suite/percona/percona_innodb_buffer_pool_shm.result @@ -0,0 +1,6 @@ +show variables like 'innodb_buffer_pool_shm%'; +Variable_name Value +innodb_buffer_pool_shm_key 123456 +show variables like 'innodb_buffer_pool_shm%'; +Variable_name Value +innodb_buffer_pool_shm_key 123456 diff --git a/mysql-test/suite/percona/percona_innodb_buffer_pool_shm.test b/mysql-test/suite/percona/percona_innodb_buffer_pool_shm.test new file mode 100644 index 00000000000..7b81bb8d54b --- /dev/null +++ b/mysql-test/suite/percona/percona_innodb_buffer_pool_shm.test @@ -0,0 +1,18 @@ +--source include/have_innodb.inc +show variables like 'innodb_buffer_pool_shm%'; + +#clean shutdown (restart_mysqld.inc is not clean if over 10 sec...) +--write_file $MYSQLTEST_VARDIR/tmp/mysqld.1.expect +wait +EOF +shutdown_server 120; +--append_file $MYSQLTEST_VARDIR/tmp/mysqld.1.expect +restart +EOF +--enable_reconnect +--source include/wait_until_connected_again.inc +--disable_reconnect + +show variables like 'innodb_buffer_pool_shm%'; +--sleep 1 +--system ipcrm -M 123456 diff --git a/mysql-test/suite/percona/percona_innodb_use_sys_stats_table-master.opt b/mysql-test/suite/percona/percona_innodb_use_sys_stats_table-master.opt new file mode 100644 index 00000000000..c6865f5704c --- /dev/null +++ b/mysql-test/suite/percona/percona_innodb_use_sys_stats_table-master.opt @@ -0,0 +1 @@ +--innodb_use_sys_stats_table diff --git a/mysql-test/suite/percona/percona_innodb_use_sys_stats_table.result b/mysql-test/suite/percona/percona_innodb_use_sys_stats_table.result new file mode 100644 index 00000000000..cb64de41901 --- /dev/null +++ b/mysql-test/suite/percona/percona_innodb_use_sys_stats_table.result @@ -0,0 +1,3 @@ +show variables like 'innodb_use_sys_stats%'; +Variable_name Value +innodb_use_sys_stats_table ON diff --git a/mysql-test/suite/percona/percona_innodb_use_sys_stats_table.test b/mysql-test/suite/percona/percona_innodb_use_sys_stats_table.test new file mode 100644 index 00000000000..02791137f08 --- /dev/null +++ b/mysql-test/suite/percona/percona_innodb_use_sys_stats_table.test @@ -0,0 +1,2 @@ +--source include/have_innodb.inc +show variables like 'innodb_use_sys_stats%'; diff --git a/mysql-test/suite/percona/percona_log_connection_error-master.opt b/mysql-test/suite/percona/percona_log_connection_error-master.opt new file mode 100644 index 00000000000..32a891789f3 --- /dev/null +++ b/mysql-test/suite/percona/percona_log_connection_error-master.opt @@ -0,0 +1 @@ +--log-error
\ No newline at end of file diff --git a/mysql-test/suite/percona/percona_log_connection_error.result b/mysql-test/suite/percona/percona_log_connection_error.result new file mode 100644 index 00000000000..3c6c67f770c --- /dev/null +++ b/mysql-test/suite/percona/percona_log_connection_error.result @@ -0,0 +1,15 @@ +SET @old_max_connections = @@max_connections; +SET @old_log_warnings = @@log_warnings; +SET GLOBAL max_connections=2; +SET GLOBAL LOG_WARNINGS = 0; +connect(localhost,root,,test,port,socket); +ERROR HY000: Too many connections +SET GLOBAL LOG_WARNINGS = 1; +connect(localhost,root,,test,port,socket); +ERROR HY000: Too many connections +SET GLOBAL LOG_WARNINGS = 0; +connect(localhost,root,,test,port,socket); +ERROR HY000: Too many connections +SET GLOBAL max_connections = @old_max_connections; +SET GLOBAL log_warnings = @old_log_warnings; +1 diff --git a/mysql-test/suite/percona/percona_log_connection_error.test b/mysql-test/suite/percona/percona_log_connection_error.test new file mode 100644 index 00000000000..57cd652bd24 --- /dev/null +++ b/mysql-test/suite/percona/percona_log_connection_error.test @@ -0,0 +1,52 @@ +--source include/not_embedded.inc + +connect (main,localhost,root,,); +connection main; +SET @old_max_connections = @@max_connections; +SET @old_log_warnings = @@log_warnings; +SET GLOBAL max_connections=2; +let $port=`SELECT Variable_value FROM INFORMATION_SCHEMA.SESSION_VARIABLES WHERE Variable_name LIKE 'port'`; +let $socket=`SELECT Variable_value FROM INFORMATION_SCHEMA.SESSION_VARIABLES WHERE Variable_name LIKE 'socket'`; + +SET GLOBAL LOG_WARNINGS = 0; +--connect (conn0,localhost,root,,) +connection conn0; +replace_result $port port $socket socket; +--error 1040 +--connect(conn1,localhost,root,,) +disconnect conn0; +SLEEP 0.1; # tsarev: hack, but i don't know (and didn't find) how right + +connection main; +SET GLOBAL LOG_WARNINGS = 1; +--connect (conn1,localhost,root,,) +replace_result $port port $socket socket; +--error 1040 +--connect (conn0,localhost,root,,) +disconnect conn1; +SLEEP 0.1; # tsarev: hack, but i don't know (and didn't find) how right + +connection main; +SET GLOBAL LOG_WARNINGS = 0; +--connect (conn0,localhost,root,,) +replace_result $port port $socket socket; +--error 1040 +--connect(conn1,localhost,root,,) +disconnect conn0; +SLEEP 0.1; # tsarev: hack, but i don't know (and didn't find) how right + +connection main; +SET GLOBAL max_connections = @old_max_connections; +SET GLOBAL log_warnings = @old_log_warnings; +let $log_error_= `SELECT @@GLOBAL.log_error`; +if(!`select LENGTH('$log_error_')`) +{ + # MySQL Server on windows is started with --console and thus + # does not know the location of its .err log, use default location + let $log_error_ = $MYSQLTEST_VARDIR/log/mysqld.1.err; +} +# Assign env variable LOG_ERROR +let LOG_ERROR=$log_error_; + +let cmd=cat $log_error | grep "Too many connections" | wc -l; +exec $cmd; diff --git a/mysql-test/suite/percona/percona_query_response_time-replication.result b/mysql-test/suite/percona/percona_query_response_time-replication.result new file mode 100644 index 00000000000..df5c73812df --- /dev/null +++ b/mysql-test/suite/percona/percona_query_response_time-replication.result @@ -0,0 +1,60 @@ +stop slave; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; +reset master; +reset slave; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; +start slave; +DROP TABLE IF EXISTS t; +CREATE TABLE t(id INT); +SELECT * from t; +id +SELECT * from t; +id +SET GLOBAL QUERY_RESPONSE_TIME_RANGE_BASE = 1; +Warnings: +Warning 1292 Truncated incorrect query_response_time_range_base value: '1' +SHOW GLOBAL VARIABLES where Variable_name like 'QUERY_RESPONSE_TIME_RANGE_BASE'; +Variable_name Value +query_response_time_range_base 2 +SET GLOBAL QUERY_RESPONSE_TIME_RANGE_BASE = 10; +SHOW GLOBAL VARIABLES where Variable_name like 'QUERY_RESPONSE_TIME_RANGE_BASE'; +Variable_name Value +query_response_time_range_base 10 +FLUSH QUERY_RESPONSE_TIME; +SET GLOBAL ENABLE_QUERY_RESPONSE_TIME_STATS=ON; +INSERT INTO t VALUES(0); +SELECT SUM(INFORMATION_SCHEMA.QUERY_RESPONSE_TIME.count) FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME; +SUM(INFORMATION_SCHEMA.QUERY_RESPONSE_TIME.count) +0 +INSERT INTO t VALUES(1); +SELECT SUM(INFORMATION_SCHEMA.QUERY_RESPONSE_TIME.count) FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME; +SUM(INFORMATION_SCHEMA.QUERY_RESPONSE_TIME.count) +0 +SELECT SUM(INFORMATION_SCHEMA.QUERY_RESPONSE_TIME.count) FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME; +SUM(INFORMATION_SCHEMA.QUERY_RESPONSE_TIME.count) +2 +SELECT SUM(INFORMATION_SCHEMA.QUERY_RESPONSE_TIME.count) FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME; +SUM(INFORMATION_SCHEMA.QUERY_RESPONSE_TIME.count) +3 +SET GLOBAL QUERY_RESPONSE_TIME_RANGE_BASE = 2; +SHOW GLOBAL VARIABLES where Variable_name like 'QUERY_RESPONSE_TIME_RANGE_BASE'; +Variable_name Value +query_response_time_range_base 2 +FLUSH QUERY_RESPONSE_TIME; +INSERT INTO t VALUES(0); +SELECT SUM(INFORMATION_SCHEMA.QUERY_RESPONSE_TIME.count) FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME; +SUM(INFORMATION_SCHEMA.QUERY_RESPONSE_TIME.count) +0 +INSERT INTO t VALUES(1); +SELECT SUM(INFORMATION_SCHEMA.QUERY_RESPONSE_TIME.count) FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME; +SUM(INFORMATION_SCHEMA.QUERY_RESPONSE_TIME.count) +0 +SELECT SUM(INFORMATION_SCHEMA.QUERY_RESPONSE_TIME.count) FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME; +SUM(INFORMATION_SCHEMA.QUERY_RESPONSE_TIME.count) +2 +SELECT SUM(INFORMATION_SCHEMA.QUERY_RESPONSE_TIME.count) FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME; +SUM(INFORMATION_SCHEMA.QUERY_RESPONSE_TIME.count) +3 +DROP TABLE IF EXISTS t; +SET GLOBAL QUERY_RESPONSE_TIME_RANGE_BASE = 10; +SET GLOBAL ENABLE_QUERY_RESPONSE_TIME_STATS=OFF; diff --git a/mysql-test/suite/percona/percona_query_response_time-replication.test b/mysql-test/suite/percona/percona_query_response_time-replication.test new file mode 100644 index 00000000000..4f674c2fd19 --- /dev/null +++ b/mysql-test/suite/percona/percona_query_response_time-replication.test @@ -0,0 +1,52 @@ +--source include/master-slave.inc + +connection master; +-- disable_warnings +DROP TABLE IF EXISTS t; +-- enable_warnings +CREATE TABLE t(id INT); +SELECT * from t; + +sync_slave_with_master; + +connection slave; +SELECT * from t; +SET GLOBAL QUERY_RESPONSE_TIME_RANGE_BASE = 1; +SHOW GLOBAL VARIABLES where Variable_name like 'QUERY_RESPONSE_TIME_RANGE_BASE'; +SET GLOBAL QUERY_RESPONSE_TIME_RANGE_BASE = 10; +SHOW GLOBAL VARIABLES where Variable_name like 'QUERY_RESPONSE_TIME_RANGE_BASE'; +source include/percona_query_response_time_flush.inc; +SET GLOBAL ENABLE_QUERY_RESPONSE_TIME_STATS=ON; + +connection master; +INSERT INTO t VALUES(0); +SELECT SUM(INFORMATION_SCHEMA.QUERY_RESPONSE_TIME.count) FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME; +INSERT INTO t VALUES(1); +SELECT SUM(INFORMATION_SCHEMA.QUERY_RESPONSE_TIME.count) FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME; +sync_slave_with_master; + +connection slave; +SELECT SUM(INFORMATION_SCHEMA.QUERY_RESPONSE_TIME.count) FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME; +SELECT SUM(INFORMATION_SCHEMA.QUERY_RESPONSE_TIME.count) FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME; + +SET GLOBAL QUERY_RESPONSE_TIME_RANGE_BASE = 2; +SHOW GLOBAL VARIABLES where Variable_name like 'QUERY_RESPONSE_TIME_RANGE_BASE'; +source include/percona_query_response_time_flush.inc; + +connection master; +INSERT INTO t VALUES(0); +SELECT SUM(INFORMATION_SCHEMA.QUERY_RESPONSE_TIME.count) FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME; +INSERT INTO t VALUES(1); +SELECT SUM(INFORMATION_SCHEMA.QUERY_RESPONSE_TIME.count) FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME; +sync_slave_with_master; + +connection slave; +SELECT SUM(INFORMATION_SCHEMA.QUERY_RESPONSE_TIME.count) FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME; +SELECT SUM(INFORMATION_SCHEMA.QUERY_RESPONSE_TIME.count) FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME; + +connection master; +DROP TABLE IF EXISTS t; +sync_slave_with_master; +connection slave; +SET GLOBAL QUERY_RESPONSE_TIME_RANGE_BASE = 10; +SET GLOBAL ENABLE_QUERY_RESPONSE_TIME_STATS=OFF; diff --git a/mysql-test/suite/percona/percona_query_response_time-stored.result b/mysql-test/suite/percona/percona_query_response_time-stored.result new file mode 100644 index 00000000000..386180c791a --- /dev/null +++ b/mysql-test/suite/percona/percona_query_response_time-stored.result @@ -0,0 +1,310 @@ +CREATE FUNCTION test_f() +RETURNS CHAR(30) DETERMINISTIC +BEGIN +DECLARE first VARCHAR(5); +DECLARE second VARCHAR(5); +DECLARE result VARCHAR(20); +SELECT SLEEP(1.11) INTO first; +SET first= 'Hello'; +SET second=', '; +SET result= CONCAT(first,second); +SET result= CONCAT(result,'world!'); +RETURN result; +END/ +SET GLOBAL QUERY_RESPONSE_TIME_RANGE_BASE = 1; +Warnings: +Warning 1292 Truncated incorrect query_response_time_range_base value: '1' +SHOW GLOBAL VARIABLES where Variable_name like 'QUERY_RESPONSE_TIME_RANGE_BASE'; +Variable_name Value +query_response_time_range_base 2 +SET GLOBAL QUERY_RESPONSE_TIME_RANGE_BASE = 2; +SHOW GLOBAL VARIABLES where Variable_name like 'QUERY_RESPONSE_TIME_RANGE_BASE'; +Variable_name Value +query_response_time_range_base 2 +FLUSH QUERY_RESPONSE_TIME; +SELECT c.count, +(SELECT SUM(a.count) FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME as a WHERE a.count != 0) as query_count, +(SELECT COUNT(*) FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME as b WHERE b.count != 0) as not_zero_region_count, +(SELECT COUNT(*) FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME) as region_count +FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME as c WHERE c.count > 0; +count query_count not_zero_region_count region_count +SELECT COUNT(*) as region_count FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME; +region_count +44 +SELECT time FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME; +time + 0.000001 + 0.000003 + 0.000007 + 0.000015 + 0.000030 + 0.000061 + 0.000122 + 0.000244 + 0.000488 + 0.000976 + 0.001953 + 0.003906 + 0.007812 + 0.015625 + 0.031250 + 0.062500 + 0.125000 + 0.250000 + 0.500000 + 1.000000 + 2.000000 + 4.000000 + 8.000000 + 16.000000 + 32.000000 + 64.000000 + 128.000000 + 256.000000 + 512.000000 + 1024.000000 + 2048.000000 + 4096.000000 + 8192.000000 + 16384.000000 + 32768.000000 + 65536.000000 + 131072.000000 + 262144.000000 + 524288.000000 + 1048576.00000 + 2097152.00000 + 4194304.00000 + 8388608.00000 +TOO LONG QUERY +SET GLOBAL ENABLE_QUERY_RESPONSE_TIME_STATS=1; +SELECT test_f(); +test_f() +Hello, world! +SELECT test_f(); +test_f() +Hello, world! +SELECT test_f(); +test_f() +Hello, world! +SELECT test_f(); +test_f() +Hello, world! +SET GLOBAL ENABLE_QUERY_RESPONSE_TIME_STATS=0; +SELECT c.count, +(SELECT SUM(a.count) FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME as a WHERE a.count != 0) as query_count, +(SELECT COUNT(*) FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME as b WHERE b.count != 0) as not_zero_region_count, +(SELECT COUNT(*) FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME) as region_count +FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME as c WHERE c.count > 0; +count query_count not_zero_region_count region_count +1 5 2 44 +4 5 2 44 +SELECT COUNT(*) as region_count FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME; +region_count +44 +SELECT time FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME; +time + 0.000001 + 0.000003 + 0.000007 + 0.000015 + 0.000030 + 0.000061 + 0.000122 + 0.000244 + 0.000488 + 0.000976 + 0.001953 + 0.003906 + 0.007812 + 0.015625 + 0.031250 + 0.062500 + 0.125000 + 0.250000 + 0.500000 + 1.000000 + 2.000000 + 4.000000 + 8.000000 + 16.000000 + 32.000000 + 64.000000 + 128.000000 + 256.000000 + 512.000000 + 1024.000000 + 2048.000000 + 4096.000000 + 8192.000000 + 16384.000000 + 32768.000000 + 65536.000000 + 131072.000000 + 262144.000000 + 524288.000000 + 1048576.00000 + 2097152.00000 + 4194304.00000 + 8388608.00000 +TOO LONG QUERY +SHOW GLOBAL VARIABLES where Variable_name like 'QUERY_RESPONSE_TIME_RANGE_BASE'; +Variable_name Value +query_response_time_range_base 2 +SET GLOBAL QUERY_RESPONSE_TIME_RANGE_BASE = 10; +SHOW GLOBAL VARIABLES where Variable_name like 'QUERY_RESPONSE_TIME_RANGE_BASE'; +Variable_name Value +query_response_time_range_base 10 +FLUSH QUERY_RESPONSE_TIME; +SET GLOBAL ENABLE_QUERY_RESPONSE_TIME_STATS=1; +SELECT test_f(); +test_f() +Hello, world! +SET GLOBAL ENABLE_QUERY_RESPONSE_TIME_STATS=0; +SELECT c.count, +(SELECT SUM(a.count) FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME as a WHERE a.count != 0) as query_count, +(SELECT COUNT(*) FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME as b WHERE b.count != 0) as not_zero_region_count, +(SELECT COUNT(*) FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME) as region_count +FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME as c WHERE c.count > 0; +count query_count not_zero_region_count region_count +1 2 2 14 +1 2 2 14 +SELECT COUNT(*) as region_count FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME; +region_count +14 +SELECT time FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME; +time + 0.000001 + 0.000010 + 0.000100 + 0.001000 + 0.010000 + 0.100000 + 1.000000 + 10.000000 + 100.000000 + 1000.000000 + 10000.000000 + 100000.000000 + 1000000.00000 +TOO LONG QUERY +SHOW GLOBAL VARIABLES where Variable_name like 'QUERY_RESPONSE_TIME_RANGE_BASE'; +Variable_name Value +query_response_time_range_base 10 +SET GLOBAL QUERY_RESPONSE_TIME_RANGE_BASE = 7; +SHOW GLOBAL VARIABLES where Variable_name like 'QUERY_RESPONSE_TIME_RANGE_BASE'; +Variable_name Value +query_response_time_range_base 7 +FLUSH QUERY_RESPONSE_TIME; +SET GLOBAL ENABLE_QUERY_RESPONSE_TIME_STATS=1; +SELECT test_f(); +test_f() +Hello, world! +SET GLOBAL ENABLE_QUERY_RESPONSE_TIME_STATS=0; +SELECT c.count, +(SELECT SUM(a.count) FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME as a WHERE a.count != 0) as query_count, +(SELECT COUNT(*) FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME as b WHERE b.count != 0) as not_zero_region_count, +(SELECT COUNT(*) FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME) as region_count +FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME as c WHERE c.count > 0; +count query_count not_zero_region_count region_count +1 2 2 17 +1 2 2 17 +SELECT COUNT(*) as region_count FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME; +region_count +17 +SELECT time FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME; +time + 0.000001 + 0.000008 + 0.000059 + 0.000416 + 0.002915 + 0.020408 + 0.142857 + 1.000000 + 7.000000 + 49.000000 + 343.000000 + 2401.000000 + 16807.000000 + 117649.000000 + 823543.000000 + 5764801.00000 +TOO LONG QUERY +SHOW GLOBAL VARIABLES where Variable_name like 'QUERY_RESPONSE_TIME_RANGE_BASE'; +Variable_name Value +query_response_time_range_base 7 +SET GLOBAL QUERY_RESPONSE_TIME_RANGE_BASE = 156; +SHOW GLOBAL VARIABLES where Variable_name like 'QUERY_RESPONSE_TIME_RANGE_BASE'; +Variable_name Value +query_response_time_range_base 156 +FLUSH QUERY_RESPONSE_TIME; +SET GLOBAL ENABLE_QUERY_RESPONSE_TIME_STATS=1; +SELECT test_f(); +test_f() +Hello, world! +SET GLOBAL ENABLE_QUERY_RESPONSE_TIME_STATS=0; +SELECT c.count, +(SELECT SUM(a.count) FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME as a WHERE a.count != 0) as query_count, +(SELECT COUNT(*) FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME as b WHERE b.count != 0) as not_zero_region_count, +(SELECT COUNT(*) FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME) as region_count +FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME as c WHERE c.count > 0; +count query_count not_zero_region_count region_count +1 2 2 7 +1 2 2 7 +SELECT COUNT(*) as region_count FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME; +region_count +7 +SELECT time FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME; +time + 0.000041 + 0.006410 + 1.000000 + 156.000000 + 24336.000000 + 3796416.00000 +TOO LONG QUERY +SHOW GLOBAL VARIABLES where Variable_name like 'QUERY_RESPONSE_TIME_RANGE_BASE'; +Variable_name Value +query_response_time_range_base 156 +SET GLOBAL QUERY_RESPONSE_TIME_RANGE_BASE = 1000; +SHOW GLOBAL VARIABLES where Variable_name like 'QUERY_RESPONSE_TIME_RANGE_BASE'; +Variable_name Value +query_response_time_range_base 1000 +FLUSH QUERY_RESPONSE_TIME; +SET GLOBAL ENABLE_QUERY_RESPONSE_TIME_STATS=1; +SELECT test_f(); +test_f() +Hello, world! +SET GLOBAL ENABLE_QUERY_RESPONSE_TIME_STATS=0; +SELECT c.count, +(SELECT SUM(a.count) FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME as a WHERE a.count != 0) as query_count, +(SELECT COUNT(*) FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME as b WHERE b.count != 0) as not_zero_region_count, +(SELECT COUNT(*) FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME) as region_count +FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME as c WHERE c.count > 0; +count query_count not_zero_region_count region_count +1 2 2 6 +1 2 2 6 +SELECT COUNT(*) as region_count FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME; +region_count +6 +SELECT time FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME; +time + 0.000001 + 0.001000 + 1.000000 + 1000.000000 + 1000000.00000 +TOO LONG QUERY +SHOW GLOBAL VARIABLES where Variable_name like 'QUERY_RESPONSE_TIME_RANGE_BASE'; +Variable_name Value +query_response_time_range_base 1000 +SET GLOBAL QUERY_RESPONSE_TIME_RANGE_BASE = 1001; +Warnings: +Warning 1292 Truncated incorrect query_response_time_range_base value: '1001' +SHOW GLOBAL VARIABLES where Variable_name like 'QUERY_RESPONSE_TIME_RANGE_BASE'; +Variable_name Value +query_response_time_range_base 1000 +SET GLOBAL ENABLE_QUERY_RESPONSE_TIME_STATS=0; +SET GLOBAL QUERY_RESPONSE_TIME_RANGE_BASE =10; +DROP FUNCTION test_f; diff --git a/mysql-test/suite/percona/percona_query_response_time-stored.test b/mysql-test/suite/percona/percona_query_response_time-stored.test new file mode 100644 index 00000000000..f761dd7d01c --- /dev/null +++ b/mysql-test/suite/percona/percona_query_response_time-stored.test @@ -0,0 +1,87 @@ +source include/have_innodb.inc; + +delimiter /; +CREATE FUNCTION test_f() +RETURNS CHAR(30) DETERMINISTIC +BEGIN + DECLARE first VARCHAR(5); + DECLARE second VARCHAR(5); + DECLARE result VARCHAR(20); + SELECT SLEEP(1.11) INTO first; + SET first= 'Hello'; + SET second=', '; + SET result= CONCAT(first,second); + SET result= CONCAT(result,'world!'); + RETURN result; +END/ +delimiter ;/ + +SET GLOBAL QUERY_RESPONSE_TIME_RANGE_BASE = 1; +SHOW GLOBAL VARIABLES where Variable_name like 'QUERY_RESPONSE_TIME_RANGE_BASE'; +SET GLOBAL QUERY_RESPONSE_TIME_RANGE_BASE = 2; +SHOW GLOBAL VARIABLES where Variable_name like 'QUERY_RESPONSE_TIME_RANGE_BASE'; + +source include/percona_query_response_time_flush.inc; +source include/percona_query_response_time_show.inc; + +SET GLOBAL ENABLE_QUERY_RESPONSE_TIME_STATS=1; +SELECT test_f(); +SELECT test_f(); +SELECT test_f(); +SELECT test_f(); +SET GLOBAL ENABLE_QUERY_RESPONSE_TIME_STATS=0; + +source include/percona_query_response_time_show.inc; + +SHOW GLOBAL VARIABLES where Variable_name like 'QUERY_RESPONSE_TIME_RANGE_BASE'; +SET GLOBAL QUERY_RESPONSE_TIME_RANGE_BASE = 10; +SHOW GLOBAL VARIABLES where Variable_name like 'QUERY_RESPONSE_TIME_RANGE_BASE'; + +source include/percona_query_response_time_flush.inc; +SET GLOBAL ENABLE_QUERY_RESPONSE_TIME_STATS=1; +SELECT test_f(); +SET GLOBAL ENABLE_QUERY_RESPONSE_TIME_STATS=0; + +source include/percona_query_response_time_show.inc; + +SHOW GLOBAL VARIABLES where Variable_name like 'QUERY_RESPONSE_TIME_RANGE_BASE'; +SET GLOBAL QUERY_RESPONSE_TIME_RANGE_BASE = 7; +SHOW GLOBAL VARIABLES where Variable_name like 'QUERY_RESPONSE_TIME_RANGE_BASE'; + +source include/percona_query_response_time_flush.inc; +SET GLOBAL ENABLE_QUERY_RESPONSE_TIME_STATS=1; +SELECT test_f(); +SET GLOBAL ENABLE_QUERY_RESPONSE_TIME_STATS=0; + +source include/percona_query_response_time_show.inc; + +SHOW GLOBAL VARIABLES where Variable_name like 'QUERY_RESPONSE_TIME_RANGE_BASE'; +SET GLOBAL QUERY_RESPONSE_TIME_RANGE_BASE = 156; +SHOW GLOBAL VARIABLES where Variable_name like 'QUERY_RESPONSE_TIME_RANGE_BASE'; + +source include/percona_query_response_time_flush.inc; +SET GLOBAL ENABLE_QUERY_RESPONSE_TIME_STATS=1; +SELECT test_f(); +SET GLOBAL ENABLE_QUERY_RESPONSE_TIME_STATS=0; + +source include/percona_query_response_time_show.inc; + +SHOW GLOBAL VARIABLES where Variable_name like 'QUERY_RESPONSE_TIME_RANGE_BASE'; +SET GLOBAL QUERY_RESPONSE_TIME_RANGE_BASE = 1000; +SHOW GLOBAL VARIABLES where Variable_name like 'QUERY_RESPONSE_TIME_RANGE_BASE'; + +source include/percona_query_response_time_flush.inc; +SET GLOBAL ENABLE_QUERY_RESPONSE_TIME_STATS=1; +SELECT test_f(); +SET GLOBAL ENABLE_QUERY_RESPONSE_TIME_STATS=0; + +source include/percona_query_response_time_show.inc; + +SHOW GLOBAL VARIABLES where Variable_name like 'QUERY_RESPONSE_TIME_RANGE_BASE'; +SET GLOBAL QUERY_RESPONSE_TIME_RANGE_BASE = 1001; +SHOW GLOBAL VARIABLES where Variable_name like 'QUERY_RESPONSE_TIME_RANGE_BASE'; + +SET GLOBAL ENABLE_QUERY_RESPONSE_TIME_STATS=0; +SET GLOBAL QUERY_RESPONSE_TIME_RANGE_BASE =10; + +DROP FUNCTION test_f;
\ No newline at end of file diff --git a/mysql-test/suite/percona/percona_query_response_time.result b/mysql-test/suite/percona/percona_query_response_time.result new file mode 100644 index 00000000000..3c12284a525 --- /dev/null +++ b/mysql-test/suite/percona/percona_query_response_time.result @@ -0,0 +1,564 @@ +SET GLOBAL QUERY_RESPONSE_TIME_RANGE_BASE = 1; +Warnings: +Warning 1292 Truncated incorrect query_response_time_range_base value: '1' +SHOW GLOBAL VARIABLES where Variable_name like 'QUERY_RESPONSE_TIME_RANGE_BASE'; +Variable_name Value +query_response_time_range_base 2 +SET GLOBAL QUERY_RESPONSE_TIME_RANGE_BASE = 2; +SHOW GLOBAL VARIABLES where Variable_name like 'QUERY_RESPONSE_TIME_RANGE_BASE'; +Variable_name Value +query_response_time_range_base 2 +FLUSH QUERY_RESPONSE_TIME; +SELECT c.count, +(SELECT SUM(a.count) FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME as a WHERE a.count != 0) as query_count, +(SELECT COUNT(*) FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME as b WHERE b.count != 0) as not_zero_region_count, +(SELECT COUNT(*) FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME) as region_count +FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME as c WHERE c.count > 0; +count query_count not_zero_region_count region_count +SELECT COUNT(*) as region_count FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME; +region_count +44 +SELECT time FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME; +time + 0.000001 + 0.000003 + 0.000007 + 0.000015 + 0.000030 + 0.000061 + 0.000122 + 0.000244 + 0.000488 + 0.000976 + 0.001953 + 0.003906 + 0.007812 + 0.015625 + 0.031250 + 0.062500 + 0.125000 + 0.250000 + 0.500000 + 1.000000 + 2.000000 + 4.000000 + 8.000000 + 16.000000 + 32.000000 + 64.000000 + 128.000000 + 256.000000 + 512.000000 + 1024.000000 + 2048.000000 + 4096.000000 + 8192.000000 + 16384.000000 + 32768.000000 + 65536.000000 + 131072.000000 + 262144.000000 + 524288.000000 + 1048576.00000 + 2097152.00000 + 4194304.00000 + 8388608.00000 +TOO LONG QUERY +SET GLOBAL ENABLE_QUERY_RESPONSE_TIME_STATS=1; +SELECT SLEEP(0.31); +SLEEP(0.31) +0 +SELECT SLEEP(0.32); +SLEEP(0.32) +0 +SELECT SLEEP(0.33); +SLEEP(0.33) +0 +SELECT SLEEP(0.34); +SLEEP(0.34) +0 +SELECT SLEEP(0.35); +SLEEP(0.35) +0 +SELECT SLEEP(0.36); +SLEEP(0.36) +0 +SELECT SLEEP(0.37); +SLEEP(0.37) +0 +SELECT SLEEP(0.38); +SLEEP(0.38) +0 +SELECT SLEEP(0.39); +SLEEP(0.39) +0 +SELECT SLEEP(0.40); +SLEEP(0.40) +0 +SELECT SLEEP(1.1); +SLEEP(1.1) +0 +SELECT SLEEP(1.2); +SLEEP(1.2) +0 +SELECT SLEEP(1.3); +SLEEP(1.3) +0 +SELECT SLEEP(1.5); +SLEEP(1.5) +0 +SELECT SLEEP(1.4); +SLEEP(1.4) +0 +SELECT SLEEP(0.5); +SLEEP(0.5) +0 +SELECT SLEEP(2.1); +SLEEP(2.1) +0 +SELECT SLEEP(2.3); +SLEEP(2.3) +0 +SELECT SLEEP(2.5); +SLEEP(2.5) +0 +SET GLOBAL ENABLE_QUERY_RESPONSE_TIME_STATS=0; +SELECT c.count, +(SELECT SUM(a.count) FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME as a WHERE a.count != 0) as query_count, +(SELECT COUNT(*) FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME as b WHERE b.count != 0) as not_zero_region_count, +(SELECT COUNT(*) FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME) as region_count +FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME as c WHERE c.count > 0; +count query_count not_zero_region_count region_count +1 20 5 44 +10 20 5 44 +1 20 5 44 +5 20 5 44 +3 20 5 44 +SELECT COUNT(*) as region_count FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME; +region_count +44 +SELECT time FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME; +time + 0.000001 + 0.000003 + 0.000007 + 0.000015 + 0.000030 + 0.000061 + 0.000122 + 0.000244 + 0.000488 + 0.000976 + 0.001953 + 0.003906 + 0.007812 + 0.015625 + 0.031250 + 0.062500 + 0.125000 + 0.250000 + 0.500000 + 1.000000 + 2.000000 + 4.000000 + 8.000000 + 16.000000 + 32.000000 + 64.000000 + 128.000000 + 256.000000 + 512.000000 + 1024.000000 + 2048.000000 + 4096.000000 + 8192.000000 + 16384.000000 + 32768.000000 + 65536.000000 + 131072.000000 + 262144.000000 + 524288.000000 + 1048576.00000 + 2097152.00000 + 4194304.00000 + 8388608.00000 +TOO LONG QUERY +SHOW GLOBAL VARIABLES where Variable_name like 'QUERY_RESPONSE_TIME_RANGE_BASE'; +Variable_name Value +query_response_time_range_base 2 +SET GLOBAL QUERY_RESPONSE_TIME_RANGE_BASE = 10; +SHOW GLOBAL VARIABLES where Variable_name like 'QUERY_RESPONSE_TIME_RANGE_BASE'; +Variable_name Value +query_response_time_range_base 10 +FLUSH QUERY_RESPONSE_TIME; +SET GLOBAL ENABLE_QUERY_RESPONSE_TIME_STATS=1; +SELECT SLEEP(0.31); +SLEEP(0.31) +0 +SELECT SLEEP(0.32); +SLEEP(0.32) +0 +SELECT SLEEP(0.33); +SLEEP(0.33) +0 +SELECT SLEEP(0.34); +SLEEP(0.34) +0 +SELECT SLEEP(0.35); +SLEEP(0.35) +0 +SELECT SLEEP(0.36); +SLEEP(0.36) +0 +SELECT SLEEP(0.37); +SLEEP(0.37) +0 +SELECT SLEEP(0.38); +SLEEP(0.38) +0 +SELECT SLEEP(0.39); +SLEEP(0.39) +0 +SELECT SLEEP(0.40); +SLEEP(0.40) +0 +SELECT SLEEP(1.1); +SLEEP(1.1) +0 +SELECT SLEEP(1.2); +SLEEP(1.2) +0 +SELECT SLEEP(1.3); +SLEEP(1.3) +0 +SELECT SLEEP(1.5); +SLEEP(1.5) +0 +SELECT SLEEP(1.4); +SLEEP(1.4) +0 +SELECT SLEEP(0.5); +SLEEP(0.5) +0 +SELECT SLEEP(2.1); +SLEEP(2.1) +0 +SELECT SLEEP(2.3); +SLEEP(2.3) +0 +SELECT SLEEP(2.5); +SLEEP(2.5) +0 +SET GLOBAL ENABLE_QUERY_RESPONSE_TIME_STATS=0; +SELECT c.count, +(SELECT SUM(a.count) FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME as a WHERE a.count != 0) as query_count, +(SELECT COUNT(*) FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME as b WHERE b.count != 0) as not_zero_region_count, +(SELECT COUNT(*) FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME) as region_count +FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME as c WHERE c.count > 0; +count query_count not_zero_region_count region_count +1 20 3 14 +11 20 3 14 +8 20 3 14 +SELECT COUNT(*) as region_count FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME; +region_count +14 +SELECT time FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME; +time + 0.000001 + 0.000010 + 0.000100 + 0.001000 + 0.010000 + 0.100000 + 1.000000 + 10.000000 + 100.000000 + 1000.000000 + 10000.000000 + 100000.000000 + 1000000.00000 +TOO LONG QUERY +SHOW GLOBAL VARIABLES where Variable_name like 'QUERY_RESPONSE_TIME_RANGE_BASE'; +Variable_name Value +query_response_time_range_base 10 +SET GLOBAL QUERY_RESPONSE_TIME_RANGE_BASE = 7; +SHOW GLOBAL VARIABLES where Variable_name like 'QUERY_RESPONSE_TIME_RANGE_BASE'; +Variable_name Value +query_response_time_range_base 7 +FLUSH QUERY_RESPONSE_TIME; +SET GLOBAL ENABLE_QUERY_RESPONSE_TIME_STATS=1; +SELECT SLEEP(0.31); +SLEEP(0.31) +0 +SELECT SLEEP(0.32); +SLEEP(0.32) +0 +SELECT SLEEP(0.33); +SLEEP(0.33) +0 +SELECT SLEEP(0.34); +SLEEP(0.34) +0 +SELECT SLEEP(0.35); +SLEEP(0.35) +0 +SELECT SLEEP(0.36); +SLEEP(0.36) +0 +SELECT SLEEP(0.37); +SLEEP(0.37) +0 +SELECT SLEEP(0.38); +SLEEP(0.38) +0 +SELECT SLEEP(0.39); +SLEEP(0.39) +0 +SELECT SLEEP(0.40); +SLEEP(0.40) +0 +SELECT SLEEP(1.1); +SLEEP(1.1) +0 +SELECT SLEEP(1.2); +SLEEP(1.2) +0 +SELECT SLEEP(1.3); +SLEEP(1.3) +0 +SELECT SLEEP(1.5); +SLEEP(1.5) +0 +SELECT SLEEP(1.4); +SLEEP(1.4) +0 +SELECT SLEEP(0.5); +SLEEP(0.5) +0 +SELECT SLEEP(2.1); +SLEEP(2.1) +0 +SELECT SLEEP(2.3); +SLEEP(2.3) +0 +SELECT SLEEP(2.5); +SLEEP(2.5) +0 +SET GLOBAL ENABLE_QUERY_RESPONSE_TIME_STATS=0; +SELECT c.count, +(SELECT SUM(a.count) FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME as a WHERE a.count != 0) as query_count, +(SELECT COUNT(*) FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME as b WHERE b.count != 0) as not_zero_region_count, +(SELECT COUNT(*) FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME) as region_count +FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME as c WHERE c.count > 0; +count query_count not_zero_region_count region_count +1 20 3 17 +11 20 3 17 +8 20 3 17 +SELECT COUNT(*) as region_count FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME; +region_count +17 +SELECT time FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME; +time + 0.000001 + 0.000008 + 0.000059 + 0.000416 + 0.002915 + 0.020408 + 0.142857 + 1.000000 + 7.000000 + 49.000000 + 343.000000 + 2401.000000 + 16807.000000 + 117649.000000 + 823543.000000 + 5764801.00000 +TOO LONG QUERY +SHOW GLOBAL VARIABLES where Variable_name like 'QUERY_RESPONSE_TIME_RANGE_BASE'; +Variable_name Value +query_response_time_range_base 7 +SET GLOBAL QUERY_RESPONSE_TIME_RANGE_BASE = 156; +SHOW GLOBAL VARIABLES where Variable_name like 'QUERY_RESPONSE_TIME_RANGE_BASE'; +Variable_name Value +query_response_time_range_base 156 +FLUSH QUERY_RESPONSE_TIME; +SET GLOBAL ENABLE_QUERY_RESPONSE_TIME_STATS=1; +SELECT SLEEP(0.31); +SLEEP(0.31) +0 +SELECT SLEEP(0.32); +SLEEP(0.32) +0 +SELECT SLEEP(0.33); +SLEEP(0.33) +0 +SELECT SLEEP(0.34); +SLEEP(0.34) +0 +SELECT SLEEP(0.35); +SLEEP(0.35) +0 +SELECT SLEEP(0.36); +SLEEP(0.36) +0 +SELECT SLEEP(0.37); +SLEEP(0.37) +0 +SELECT SLEEP(0.38); +SLEEP(0.38) +0 +SELECT SLEEP(0.39); +SLEEP(0.39) +0 +SELECT SLEEP(0.40); +SLEEP(0.40) +0 +SELECT SLEEP(1.1); +SLEEP(1.1) +0 +SELECT SLEEP(1.2); +SLEEP(1.2) +0 +SELECT SLEEP(1.3); +SLEEP(1.3) +0 +SELECT SLEEP(1.5); +SLEEP(1.5) +0 +SELECT SLEEP(1.4); +SLEEP(1.4) +0 +SELECT SLEEP(0.5); +SLEEP(0.5) +0 +SELECT SLEEP(2.1); +SLEEP(2.1) +0 +SELECT SLEEP(2.3); +SLEEP(2.3) +0 +SELECT SLEEP(2.5); +SLEEP(2.5) +0 +SET GLOBAL ENABLE_QUERY_RESPONSE_TIME_STATS=0; +SELECT c.count, +(SELECT SUM(a.count) FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME as a WHERE a.count != 0) as query_count, +(SELECT COUNT(*) FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME as b WHERE b.count != 0) as not_zero_region_count, +(SELECT COUNT(*) FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME) as region_count +FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME as c WHERE c.count > 0; +count query_count not_zero_region_count region_count +1 20 3 7 +11 20 3 7 +8 20 3 7 +SELECT COUNT(*) as region_count FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME; +region_count +7 +SELECT time FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME; +time + 0.000041 + 0.006410 + 1.000000 + 156.000000 + 24336.000000 + 3796416.00000 +TOO LONG QUERY +SHOW GLOBAL VARIABLES where Variable_name like 'QUERY_RESPONSE_TIME_RANGE_BASE'; +Variable_name Value +query_response_time_range_base 156 +SET GLOBAL QUERY_RESPONSE_TIME_RANGE_BASE = 1000; +SHOW GLOBAL VARIABLES where Variable_name like 'QUERY_RESPONSE_TIME_RANGE_BASE'; +Variable_name Value +query_response_time_range_base 1000 +FLUSH QUERY_RESPONSE_TIME; +SET GLOBAL ENABLE_QUERY_RESPONSE_TIME_STATS=1; +SELECT SLEEP(0.31); +SLEEP(0.31) +0 +SELECT SLEEP(0.32); +SLEEP(0.32) +0 +SELECT SLEEP(0.33); +SLEEP(0.33) +0 +SELECT SLEEP(0.34); +SLEEP(0.34) +0 +SELECT SLEEP(0.35); +SLEEP(0.35) +0 +SELECT SLEEP(0.36); +SLEEP(0.36) +0 +SELECT SLEEP(0.37); +SLEEP(0.37) +0 +SELECT SLEEP(0.38); +SLEEP(0.38) +0 +SELECT SLEEP(0.39); +SLEEP(0.39) +0 +SELECT SLEEP(0.40); +SLEEP(0.40) +0 +SELECT SLEEP(1.1); +SLEEP(1.1) +0 +SELECT SLEEP(1.2); +SLEEP(1.2) +0 +SELECT SLEEP(1.3); +SLEEP(1.3) +0 +SELECT SLEEP(1.5); +SLEEP(1.5) +0 +SELECT SLEEP(1.4); +SLEEP(1.4) +0 +SELECT SLEEP(0.5); +SLEEP(0.5) +0 +SELECT SLEEP(2.1); +SLEEP(2.1) +0 +SELECT SLEEP(2.3); +SLEEP(2.3) +0 +SELECT SLEEP(2.5); +SLEEP(2.5) +0 +SET GLOBAL ENABLE_QUERY_RESPONSE_TIME_STATS=0; +SELECT c.count, +(SELECT SUM(a.count) FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME as a WHERE a.count != 0) as query_count, +(SELECT COUNT(*) FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME as b WHERE b.count != 0) as not_zero_region_count, +(SELECT COUNT(*) FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME) as region_count +FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME as c WHERE c.count > 0; +count query_count not_zero_region_count region_count +1 20 3 6 +11 20 3 6 +8 20 3 6 +SELECT COUNT(*) as region_count FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME; +region_count +6 +SELECT time FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME; +time + 0.000001 + 0.001000 + 1.000000 + 1000.000000 + 1000000.00000 +TOO LONG QUERY +SHOW GLOBAL VARIABLES where Variable_name like 'QUERY_RESPONSE_TIME_RANGE_BASE'; +Variable_name Value +query_response_time_range_base 1000 +SET GLOBAL QUERY_RESPONSE_TIME_RANGE_BASE = 1001; +Warnings: +Warning 1292 Truncated incorrect query_response_time_range_base value: '1001' +SHOW GLOBAL VARIABLES where Variable_name like 'QUERY_RESPONSE_TIME_RANGE_BASE'; +Variable_name Value +query_response_time_range_base 1000 +SET GLOBAL ENABLE_QUERY_RESPONSE_TIME_STATS=0; +SET GLOBAL QUERY_RESPONSE_TIME_RANGE_BASE =10; diff --git a/mysql-test/suite/percona/percona_query_response_time.test b/mysql-test/suite/percona/percona_query_response_time.test new file mode 100644 index 00000000000..a58cafc8d01 --- /dev/null +++ b/mysql-test/suite/percona/percona_query_response_time.test @@ -0,0 +1,65 @@ +source include/have_innodb.inc; +SET GLOBAL QUERY_RESPONSE_TIME_RANGE_BASE = 1; +SHOW GLOBAL VARIABLES where Variable_name like 'QUERY_RESPONSE_TIME_RANGE_BASE'; +SET GLOBAL QUERY_RESPONSE_TIME_RANGE_BASE = 2; +SHOW GLOBAL VARIABLES where Variable_name like 'QUERY_RESPONSE_TIME_RANGE_BASE'; + +source include/percona_query_response_time_flush.inc; +source include/percona_query_response_time_show.inc; + +SET GLOBAL ENABLE_QUERY_RESPONSE_TIME_STATS=1; +source include/percona_query_response_time_sleep.inc; +SET GLOBAL ENABLE_QUERY_RESPONSE_TIME_STATS=0; + +source include/percona_query_response_time_show.inc; + +SHOW GLOBAL VARIABLES where Variable_name like 'QUERY_RESPONSE_TIME_RANGE_BASE'; +SET GLOBAL QUERY_RESPONSE_TIME_RANGE_BASE = 10; +SHOW GLOBAL VARIABLES where Variable_name like 'QUERY_RESPONSE_TIME_RANGE_BASE'; + +source include/percona_query_response_time_flush.inc; +SET GLOBAL ENABLE_QUERY_RESPONSE_TIME_STATS=1; +source include/percona_query_response_time_sleep.inc; +SET GLOBAL ENABLE_QUERY_RESPONSE_TIME_STATS=0; + +source include/percona_query_response_time_show.inc; + +SHOW GLOBAL VARIABLES where Variable_name like 'QUERY_RESPONSE_TIME_RANGE_BASE'; +SET GLOBAL QUERY_RESPONSE_TIME_RANGE_BASE = 7; +SHOW GLOBAL VARIABLES where Variable_name like 'QUERY_RESPONSE_TIME_RANGE_BASE'; + +source include/percona_query_response_time_flush.inc; +SET GLOBAL ENABLE_QUERY_RESPONSE_TIME_STATS=1; +source include/percona_query_response_time_sleep.inc; +SET GLOBAL ENABLE_QUERY_RESPONSE_TIME_STATS=0; + +source include/percona_query_response_time_show.inc; + +SHOW GLOBAL VARIABLES where Variable_name like 'QUERY_RESPONSE_TIME_RANGE_BASE'; +SET GLOBAL QUERY_RESPONSE_TIME_RANGE_BASE = 156; +SHOW GLOBAL VARIABLES where Variable_name like 'QUERY_RESPONSE_TIME_RANGE_BASE'; + +source include/percona_query_response_time_flush.inc; +SET GLOBAL ENABLE_QUERY_RESPONSE_TIME_STATS=1; +source include/percona_query_response_time_sleep.inc; +SET GLOBAL ENABLE_QUERY_RESPONSE_TIME_STATS=0; + +source include/percona_query_response_time_show.inc; + +SHOW GLOBAL VARIABLES where Variable_name like 'QUERY_RESPONSE_TIME_RANGE_BASE'; +SET GLOBAL QUERY_RESPONSE_TIME_RANGE_BASE = 1000; +SHOW GLOBAL VARIABLES where Variable_name like 'QUERY_RESPONSE_TIME_RANGE_BASE'; + +source include/percona_query_response_time_flush.inc; +SET GLOBAL ENABLE_QUERY_RESPONSE_TIME_STATS=1; +source include/percona_query_response_time_sleep.inc; +SET GLOBAL ENABLE_QUERY_RESPONSE_TIME_STATS=0; + +source include/percona_query_response_time_show.inc; + +SHOW GLOBAL VARIABLES where Variable_name like 'QUERY_RESPONSE_TIME_RANGE_BASE'; +SET GLOBAL QUERY_RESPONSE_TIME_RANGE_BASE = 1001; +SHOW GLOBAL VARIABLES where Variable_name like 'QUERY_RESPONSE_TIME_RANGE_BASE'; + +SET GLOBAL ENABLE_QUERY_RESPONSE_TIME_STATS=0; +SET GLOBAL QUERY_RESPONSE_TIME_RANGE_BASE =10; diff --git a/mysql-test/suite/percona/percona_server_variables.result b/mysql-test/suite/percona/percona_server_variables.result new file mode 100644 index 00000000000..25f961b201f --- /dev/null +++ b/mysql-test/suite/percona/percona_server_variables.result @@ -0,0 +1,340 @@ +show variables; +Variable_name Value +auto_increment_increment Value +auto_increment_offset Value +autocommit Value +automatic_sp_privileges Value +back_log Value +basedir Value +big_tables Value +binlog_cache_size Value +binlog_direct_non_transactional_updates Value +binlog_format Value +bulk_insert_buffer_size Value +character_set_client Value +character_set_connection Value +character_set_database Value +character_set_filesystem Value +character_set_results Value +character_set_server Value +character_set_system Value +character_sets_dir Value +collation_connection Value +collation_database Value +collation_server Value +completion_type Value +concurrent_insert Value +connect_timeout Value +datadir Value +date_format Value +datetime_format Value +debug Value +debug_sync Value +default_week_format Value +delay_key_write Value +delayed_insert_limit Value +delayed_insert_timeout Value +delayed_queue_size Value +div_precision_increment Value +enable_query_response_time_stats Value +engine_condition_pushdown Value +error_count Value +event_scheduler Value +expire_logs_days Value +fast_index_creation Value +flush Value +flush_time Value +foreign_key_checks Value +ft_boolean_syntax Value +ft_max_word_len Value +ft_min_word_len Value +ft_query_expansion_limit Value +ft_stopword_file Value +general_log Value +general_log_file Value +group_concat_max_len Value +have_community_features Value +have_compress Value +have_crypt Value +have_csv Value +have_dynamic_loading Value +have_geometry Value +have_innodb Value +have_ndbcluster Value +have_openssl Value +have_partitioning Value +have_query_cache Value +have_rtree_keys Value +have_ssl Value +have_symlink Value +hostname Value +identity Value +ignore_builtin_innodb Value +init_connect Value +init_file Value +init_slave Value +innodb_adaptive_checkpoint Value +innodb_adaptive_flushing Value +innodb_adaptive_hash_index Value +innodb_additional_mem_pool_size Value +innodb_autoextend_increment Value +innodb_autoinc_lock_mode Value +innodb_buffer_pool_shm_key Value +innodb_buffer_pool_size Value +innodb_change_buffering Value +innodb_checkpoint_age_target Value +innodb_checksums Value +innodb_commit_concurrency Value +innodb_concurrency_tickets Value +innodb_data_file_path Value +innodb_data_home_dir Value +innodb_dict_size_limit Value +innodb_doublewrite Value +innodb_doublewrite_file Value +innodb_enable_unsafe_group_commit Value +innodb_expand_import Value +innodb_extra_rsegments Value +innodb_extra_undoslots Value +innodb_fast_checksum Value +innodb_fast_recovery Value +innodb_fast_shutdown Value +innodb_file_format Value +innodb_file_format_check Value +innodb_file_per_table Value +innodb_flush_log_at_trx_commit Value +innodb_flush_log_at_trx_commit_session Value +innodb_flush_method Value +innodb_flush_neighbor_pages Value +innodb_force_recovery Value +innodb_ibuf_accel_rate Value +innodb_ibuf_active_contract Value +innodb_ibuf_max_size Value +innodb_io_capacity Value +innodb_lock_wait_timeout Value +innodb_locks_unsafe_for_binlog Value +innodb_log_buffer_size Value +innodb_log_file_size Value +innodb_log_files_in_group Value +innodb_log_group_home_dir Value +innodb_max_dirty_pages_pct Value +innodb_max_purge_lag Value +innodb_mirrored_log_groups Value +innodb_old_blocks_pct Value +innodb_old_blocks_time Value +innodb_open_files Value +innodb_overwrite_relay_log_info Value +innodb_page_size Value +innodb_pass_corrupt_table Value +innodb_read_ahead Value +innodb_read_ahead_threshold Value +innodb_read_io_threads Value +innodb_recovery_stats Value +innodb_replication_delay Value +innodb_rollback_on_timeout Value +innodb_show_locks_held Value +innodb_show_verbose_locks Value +innodb_spin_wait_delay Value +innodb_stats_auto_update Value +innodb_stats_method Value +innodb_stats_on_metadata Value +innodb_stats_sample_pages Value +innodb_stats_update_need_lock Value +innodb_strict_mode Value +innodb_support_xa Value +innodb_sync_spin_loops Value +innodb_table_locks Value +innodb_thread_concurrency Value +innodb_thread_concurrency_timer_based Value +innodb_thread_sleep_delay Value +innodb_use_purge_thread Value +innodb_use_sys_malloc Value +innodb_use_sys_stats_table Value +innodb_version Value +innodb_write_io_threads Value +insert_id Value +interactive_timeout Value +join_buffer_size Value +keep_files_on_create Value +key_buffer_size Value +key_cache_age_threshold Value +key_cache_block_size Value +key_cache_division_limit Value +language Value +large_files_support Value +large_page_size Value +large_pages Value +last_insert_id Value +lc_time_names Value +license Value +local_infile Value +locked_in_memory Value +log Value +log_bin Value +log_bin_trust_function_creators Value +log_bin_trust_routine_creators Value +log_error Value +log_output Value +log_queries_not_using_indexes Value +log_slave_updates Value +log_slow_filter Value +log_slow_queries Value +log_slow_rate_limit Value +log_slow_slave_statements Value +log_slow_sp_statements Value +log_slow_timestamp_every Value +log_slow_verbosity Value +log_warnings Value +long_query_time Value +low_priority_updates Value +lower_case_file_system Value +lower_case_table_names Value +max_allowed_packet Value +max_binlog_cache_size Value +max_binlog_size Value +max_connect_errors Value +max_connections Value +max_delayed_threads Value +max_error_count Value +max_heap_table_size Value +max_insert_delayed_threads Value +max_join_size Value +max_length_for_sort_data Value +max_prepared_stmt_count Value +max_relay_log_size Value +max_seeks_for_key Value +max_sort_length Value +max_sp_recursion_depth Value +max_tmp_tables Value +max_user_connections Value +max_write_lock_count Value +min_examined_row_limit Value +multi_range_count Value +myisam_data_pointer_size Value +myisam_max_sort_file_size Value +myisam_mmap_size Value +myisam_recover_options Value +myisam_repair_threads Value +myisam_sort_buffer_size Value +myisam_stats_method Value +myisam_use_mmap Value +net_buffer_length Value +net_read_timeout Value +net_retry_count Value +net_write_timeout Value +new Value +old Value +old_alter_table Value +old_passwords Value +open_files_limit Value +optimizer_fix Value +optimizer_prune_level Value +optimizer_search_depth Value +optimizer_switch Value +pid_file Value +plugin_dir Value +port Value +preload_buffer_size Value +profiling Value +profiling_history_size Value +profiling_server Value +profiling_use_getrusage Value +protocol_version Value +pseudo_thread_id Value +query_alloc_block_size Value +query_cache_limit Value +query_cache_min_res_unit Value +query_cache_size Value +query_cache_strip_comments Value +query_cache_type Value +query_cache_wlock_invalidate Value +query_prealloc_size Value +query_response_time_range_base Value +rand_seed1 Value +rand_seed2 Value +range_alloc_block_size Value +read_buffer_size Value +read_only Value +read_rnd_buffer_size Value +relay_log Value +relay_log_index Value +relay_log_info_file Value +relay_log_purge Value +relay_log_space_limit Value +report_host Value +report_password Value +report_port Value +report_user Value +rpl_recovery_rank Value +secure_auth Value +secure_file_priv Value +server_id Value +skip_external_locking Value +skip_name_resolve Value +skip_networking Value +skip_show_database Value +slave_compressed_protocol Value +slave_exec_mode Value +slave_load_tmpdir Value +slave_net_timeout Value +slave_skip_errors Value +slave_transaction_retries Value +slow_launch_time Value +slow_query_log Value +slow_query_log_file Value +slow_query_log_microseconds_timestamp Value +socket Value +sort_buffer_size Value +sql_auto_is_null Value +sql_big_selects Value +sql_big_tables Value +sql_buffer_result Value +sql_log_bin Value +sql_log_off Value +sql_log_update Value +sql_low_priority_updates Value +sql_max_join_size Value +sql_mode Value +sql_notes Value +sql_quote_show_create Value +sql_safe_updates Value +sql_select_limit Value +sql_slave_skip_counter Value +sql_warnings Value +ssl_ca Value +ssl_capath Value +ssl_cert Value +ssl_cipher Value +ssl_key Value +storage_engine Value +suppress_log_warning_1592 Value +sync_binlog Value +sync_frm Value +system_time_zone Value +table_definition_cache Value +table_lock_wait_timeout Value +table_open_cache Value +table_type Value +thread_cache_size Value +thread_handling Value +thread_stack Value +thread_statistics Value +time_format Value +time_zone Value +timed_mutexes Value +timestamp Value +tmp_table_size Value +tmpdir Value +transaction_alloc_block_size Value +transaction_prealloc_size Value +tx_isolation Value +unique_checks Value +updatable_views_with_limit Value +use_global_log_slow_control Value +use_global_long_query_time Value +userstat_running Value +version Value +version_comment Value +version_compile_machine Value +version_compile_os Value +wait_timeout Value +warning_count Value diff --git a/mysql-test/suite/percona/percona_server_variables.test b/mysql-test/suite/percona/percona_server_variables.test new file mode 100644 index 00000000000..232cbb15e25 --- /dev/null +++ b/mysql-test/suite/percona/percona_server_variables.test @@ -0,0 +1,7 @@ +--source include/have_innodb.inc +--source include/have_debug.inc + +#check the list of variable names +--replace_column 2 Value +show variables; + diff --git a/mysql-test/suite/percona/percona_slave_innodb_stats-master.opt b/mysql-test/suite/percona/percona_slave_innodb_stats-master.opt new file mode 100644 index 00000000000..286a9c4484d --- /dev/null +++ b/mysql-test/suite/percona/percona_slave_innodb_stats-master.opt @@ -0,0 +1 @@ +--long_query_time=0 --log_slow_verbosity=innodb --log_slow_slave_statements diff --git a/mysql-test/suite/percona/percona_slave_innodb_stats-slave.opt b/mysql-test/suite/percona/percona_slave_innodb_stats-slave.opt new file mode 100644 index 00000000000..286a9c4484d --- /dev/null +++ b/mysql-test/suite/percona/percona_slave_innodb_stats-slave.opt @@ -0,0 +1 @@ +--long_query_time=0 --log_slow_verbosity=innodb --log_slow_slave_statements diff --git a/mysql-test/suite/percona/percona_slave_innodb_stats.result b/mysql-test/suite/percona/percona_slave_innodb_stats.result new file mode 100644 index 00000000000..c2406bdfd85 --- /dev/null +++ b/mysql-test/suite/percona/percona_slave_innodb_stats.result @@ -0,0 +1,21 @@ +stop slave; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; +reset master; +reset slave; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; +start slave; +DROP TABLE IF EXISTS t; +CREATE TABLE t(id INT,data CHAR(30)) ENGINE=InnoDB; +INSERT INTO t VALUES +(1,"aaaaabbbbbcccccdddddeeeeefffff"), +(2,"aaaaabbbbbcccccdddddeeeeefffff"), +(3,"aaaaabbbbbcccccdddddeeeeefffff"), +(4,"aaaaabbbbbcccccdddddeeeeefffff"), +(5,"aaaaabbbbbcccccdddddeeeeefffff"); +INSERT INTO t SELECT t2.id,t2.data from t as t1, t as t2; +INSERT INTO t SELECT t2.id,t2.data from t as t1, t as t2; +STOP SLAVE; +START SLAVE; +INSERT INTO t SELECT t.id,t.data from t; +DROP TABLE IF EXISTS t; +4 diff --git a/mysql-test/suite/percona/percona_slave_innodb_stats.test b/mysql-test/suite/percona/percona_slave_innodb_stats.test new file mode 100644 index 00000000000..defcd1fc406 --- /dev/null +++ b/mysql-test/suite/percona/percona_slave_innodb_stats.test @@ -0,0 +1,43 @@ +source include/have_innodb.inc; +source include/master-slave.inc; + +connection master; +-- disable_warnings +DROP TABLE IF EXISTS t; +-- enable_warnings +CREATE TABLE t(id INT,data CHAR(30)) ENGINE=InnoDB; +INSERT INTO t VALUES +(1,"aaaaabbbbbcccccdddddeeeeefffff"), +(2,"aaaaabbbbbcccccdddddeeeeefffff"), +(3,"aaaaabbbbbcccccdddddeeeeefffff"), +(4,"aaaaabbbbbcccccdddddeeeeefffff"), +(5,"aaaaabbbbbcccccdddddeeeeefffff"); +INSERT INTO t SELECT t2.id,t2.data from t as t1, t as t2; +INSERT INTO t SELECT t2.id,t2.data from t as t1, t as t2; +sync_slave_with_master; + +connection slave; +STOP SLAVE; +-- source include/wait_for_slave_to_stop.inc +--write_file $MYSQLTEST_VARDIR/tmp/mysqld.2.expect +wait +EOF +--shutdown_server 10 +--source include/wait_until_disconnected.inc +--append_file $MYSQLTEST_VARDIR/tmp/mysqld.2.expect +restart +EOF +--enable_reconnect +--source include/wait_until_connected_again.inc +START SLAVE; +-- source include/wait_for_slave_to_start.inc + +connection master; +INSERT INTO t SELECT t.id,t.data from t; +sync_slave_with_master; + +connection master; +DROP TABLE IF EXISTS t; +sync_slave_with_master; + +exec cat var/mysqld.2/mysqld-slow.log | grep InnoDB_IO_r_ops | wc -l; diff --git a/mysql-test/suite/percona/percona_slow_query_log-use_global_long_query_time.result b/mysql-test/suite/percona/percona_slow_query_log-use_global_long_query_time.result index 759f0636b5c..e294525fdd2 100644 --- a/mysql-test/suite/percona/percona_slow_query_log-use_global_long_query_time.result +++ b/mysql-test/suite/percona/percona_slow_query_log-use_global_long_query_time.result @@ -22,3 +22,38 @@ set global long_query_time=2; set global use_global_long_query_time=0; cat MYSQLD_DATADIR/percona_slow_query_log-use_global_long_query_time.log | grep -c Query_time 3 +show global variables like 'use_global_log_slow_control'; +Variable_name Value +use_global_log_slow_control none +show global variables like 'use_global_long_query_time'; +Variable_name Value +use_global_long_query_time OFF +set global use_global_log_slow_control = long_query_time; +show global variables like 'use_global_log_slow_control'; +Variable_name Value +use_global_log_slow_control long_query_time +show global variables like 'use_global_long_query_time'; +Variable_name Value +use_global_long_query_time ON +set global use_global_log_slow_control = log_slow_filter; +show global variables like 'use_global_log_slow_control'; +Variable_name Value +use_global_log_slow_control log_slow_filter +show global variables like 'use_global_long_query_time'; +Variable_name Value +use_global_long_query_time OFF +set global use_global_long_query_time = ON; +show global variables like 'use_global_log_slow_control'; +Variable_name Value +use_global_log_slow_control log_slow_filter,long_query_time +show global variables like 'use_global_long_query_time'; +Variable_name Value +use_global_long_query_time ON +set global use_global_long_query_time = OFF; +show global variables like 'use_global_log_slow_control'; +Variable_name Value +use_global_log_slow_control log_slow_filter +show global variables like 'use_global_long_query_time'; +Variable_name Value +use_global_long_query_time OFF +set global use_global_log_slow_control = none; diff --git a/mysql-test/suite/percona/percona_slow_query_log-use_global_long_query_time.test b/mysql-test/suite/percona/percona_slow_query_log-use_global_long_query_time.test index 70021da1e4c..da627ae72b0 100644 --- a/mysql-test/suite/percona/percona_slow_query_log-use_global_long_query_time.test +++ b/mysql-test/suite/percona/percona_slow_query_log-use_global_long_query_time.test @@ -20,3 +20,24 @@ let $cmd = cat $MYSQLD_DATADIR/percona_slow_query_log-use_global_long_query_time --replace_result $MYSQLD_DATADIR MYSQLD_DATADIR exec echo '$cmd'; exec $cmd; + +show global variables like 'use_global_log_slow_control'; +show global variables like 'use_global_long_query_time'; + +set global use_global_log_slow_control = long_query_time; +show global variables like 'use_global_log_slow_control'; +show global variables like 'use_global_long_query_time'; + +set global use_global_log_slow_control = log_slow_filter; +show global variables like 'use_global_log_slow_control'; +show global variables like 'use_global_long_query_time'; + +set global use_global_long_query_time = ON; +show global variables like 'use_global_log_slow_control'; +show global variables like 'use_global_long_query_time'; + +set global use_global_long_query_time = OFF; +show global variables like 'use_global_log_slow_control'; +show global variables like 'use_global_long_query_time'; + +set global use_global_log_slow_control = none; diff --git a/mysql-test/suite/percona/percona_sql_no_fcache.result b/mysql-test/suite/percona/percona_sql_no_fcache.result new file mode 100644 index 00000000000..bc1413fb96d --- /dev/null +++ b/mysql-test/suite/percona/percona_sql_no_fcache.result @@ -0,0 +1,12 @@ +drop table if exists t1; +create table t (a int not null); +insert into t values (1),(2),(3); +SELECT SQL_NO_FCACHE SLEEP(0); +SLEEP(0) +0 +SELECT /*!40001 SQL_NO_CACHE */ /*!50084 SQL_NO_FCACHE */ * FROM t; +a +1 +2 +3 +DROP TABLE t; diff --git a/mysql-test/suite/percona/percona_sql_no_fcache.test b/mysql-test/suite/percona/percona_sql_no_fcache.test new file mode 100644 index 00000000000..da0c2ecef7d --- /dev/null +++ b/mysql-test/suite/percona/percona_sql_no_fcache.test @@ -0,0 +1,11 @@ +--disable_warnings +drop table if exists t1; +--enable_warnings + +create table t (a int not null); +insert into t values (1),(2),(3); + +SELECT SQL_NO_FCACHE SLEEP(0); +SELECT /*!40001 SQL_NO_CACHE */ /*!50084 SQL_NO_FCACHE */ * FROM t; + +DROP TABLE t;
\ No newline at end of file diff --git a/mysql-test/suite/percona/percona_status_wait_query_cache_mutex.result b/mysql-test/suite/percona/percona_status_wait_query_cache_mutex.result new file mode 100644 index 00000000000..348dcef4d30 --- /dev/null +++ b/mysql-test/suite/percona/percona_status_wait_query_cache_mutex.result @@ -0,0 +1,27 @@ +set GLOBAL query_cache_size=1355776; +flush query cache; +flush query cache; +reset query cache; +flush status; +DROP TABLE IF EXISTS t; +CREATE TABLE t(id INT, number INT); +INSERT INTO t VALUES (0,1); +INSERT INTO t VALUES (1,2); +INSERT INTO t VALUES (2,3); +SELECT number from t where id > 0; +number +2 +3 +SET SESSION debug="+d,status_wait_query_cache_mutex_sleep"; +SELECT number from t where id > 0; +SET SESSION debug="+d,status_wait_query_cache_mutex_sleep"; +SELECT number from t where id > 0; +SET SESSION debug="+d,status_wait_query_cache_mutex_sleep"; +SHOW PROCESSLIST; +Id User Host db Command Time State Info +Id root localhost test Sleep Time NULL +Id root localhost test Query Time Waiting on query cache mutex SELECT number from t where id > 0 +Id root localhost test Query Time Waiting on query cache mutex SELECT number from t where id > 0 +Id root localhost test Query Time NULL SHOW PROCESSLIST +DROP TABLE t; +set GLOBAL query_cache_size=0; diff --git a/mysql-test/suite/percona/percona_status_wait_query_cache_mutex.test b/mysql-test/suite/percona/percona_status_wait_query_cache_mutex.test new file mode 100644 index 00000000000..64a9fe7db71 --- /dev/null +++ b/mysql-test/suite/percona/percona_status_wait_query_cache_mutex.test @@ -0,0 +1,37 @@ +--source include/have_query_cache.inc +--source include/have_debug.inc +set GLOBAL query_cache_size=1355776; +--source include/percona_query_cache_with_comments_clear.inc + +-- disable_warnings +DROP TABLE IF EXISTS t; +-- enable_warnings +CREATE TABLE t(id INT, number INT); +INSERT INTO t VALUES (0,1); +INSERT INTO t VALUES (1,2); +INSERT INTO t VALUES (2,3); +SELECT number from t where id > 0; +--connect (conn0,localhost,root,,) +--connect (conn1,localhost,root,,) +--connect (conn2,localhost,root,,) + +--connection conn0 +--error 0, ER_UNKNOWN_SYSTEM_VARIABLE +SET SESSION debug="+d,status_wait_query_cache_mutex_sleep"; +SEND SELECT number from t where id > 0; +SLEEP 1.0; + +--connection conn1 +--error 0, ER_UNKNOWN_SYSTEM_VARIABLE +SET SESSION debug="+d,status_wait_query_cache_mutex_sleep"; +SEND SELECT number from t where id > 0; +SLEEP 1.0; + +--connection conn2 +--error 0, ER_UNKNOWN_SYSTEM_VARIABLE +SET SESSION debug="+d,status_wait_query_cache_mutex_sleep"; +--replace_column 1 Id 6 Time +SHOW PROCESSLIST; + +DROP TABLE t; +set GLOBAL query_cache_size=0;
\ No newline at end of file diff --git a/mysql-test/suite/percona/percona_xtradb_bug317074.test b/mysql-test/suite/percona/percona_xtradb_bug317074.test index 91c59cefb73..abf5d8ee1f6 100644 --- a/mysql-test/suite/percona/percona_xtradb_bug317074.test +++ b/mysql-test/suite/percona/percona_xtradb_bug317074.test @@ -22,15 +22,18 @@ CREATE PROCEDURE insert_many(p1 int) BEGIN SET @x = 0; SET @y = 0; +start transaction; REPEAT insert into test1 set b=1; SET @x = @x + 1; SET @y = @y + 1; IF @y >= 1000 THEN commit; + start transaction; SET @y = 0; END IF; UNTIL @x >= p1 END REPEAT; +commit; END| delimiter ;| call insert_many(100000); |