227 lines
6.3 KiB
SQL
227 lines
6.3 KiB
SQL
#
|
|
# Performance Schema, Index validation
|
|
#
|
|
--echo
|
|
--echo ===== SETUP =====
|
|
let $get_thread_id=
|
|
SELECT thread_id INTO @my_thread_id
|
|
FROM performance_schema.threads
|
|
WHERE processlist_id = connection_id();
|
|
|
|
let $get_thread_os_id=
|
|
SELECT thread_os_id INTO @my_thread_os_id
|
|
FROM performance_schema.threads
|
|
WHERE processlist_id = connection_id();
|
|
|
|
let $get_processlist_id=
|
|
SELECT connection_id() INTO @my_processlist_id;
|
|
|
|
--disable_query_log
|
|
|
|
--connection default
|
|
--echo ## Connection Default
|
|
--echo ## Disable events from the default connection
|
|
UPDATE performance_schema.threads SET instrumented = 'NO' WHERE processlist_id = CONNECTION_ID();
|
|
|
|
## Remove residual entries in the tables performance_schema.users
|
|
## and performance_schema.accounts
|
|
TRUNCATE TABLE performance_schema.users;
|
|
TRUNCATE TABLE performance_schema.accounts;
|
|
|
|
## Get the thread_id of the default connection
|
|
eval $get_thread_id;
|
|
let $default_thread_id= `SELECT @my_thread_id`;
|
|
eval $get_thread_os_id;
|
|
let $default_thread_os_id= `SELECT @my_thread_os_id`;
|
|
eval $get_processlist_id;
|
|
let $default_processlist_id= `SELECT @my_processlist_id`;
|
|
|
|
USE test;
|
|
|
|
--enable_query_log
|
|
|
|
--echo
|
|
--echo ## Create user1, user2, user3
|
|
CREATE USER user1@localhost;
|
|
CREATE USER user2@localhost;
|
|
CREATE USER user3@localhost;
|
|
GRANT ALL ON *.* to 'user1'@localhost;
|
|
GRANT ALL ON *.* to 'user2'@localhost;
|
|
GRANT ALL ON *.* to 'user3'@localhost;
|
|
|
|
--disable_query_log
|
|
SET DEFAULT_STORAGE_ENGINE=XENGINE;
|
|
|
|
--echo
|
|
--echo ## Setup test connection 1
|
|
connect(con1, localhost, user1,,);
|
|
## Get the thread_id of connection 1
|
|
eval $get_thread_id;
|
|
let $con1_thread_id= `SELECT @my_thread_id`;
|
|
eval $get_thread_os_id;
|
|
let $con1_thread_os_id= `SELECT @my_thread_os_id`;
|
|
eval $get_processlist_id;
|
|
let $con1_processlist_id= `SELECT @my_processlist_id`;
|
|
|
|
--echo ## Setup test connection 2
|
|
connect(con2, localhost, user2,,);
|
|
## Get the thread_id of connection 2
|
|
eval $get_thread_id;
|
|
let $con2_thread_id= `SELECT @my_thread_id`;
|
|
eval $get_thread_os_id;
|
|
let $con2_thread_os_id= `SELECT @my_thread_os_id`;
|
|
eval $get_processlist_id;
|
|
let $con2_processlist_id= `SELECT @my_processlist_id`;
|
|
|
|
--echo ## Setup test connection 3
|
|
connect(con3, localhost, user3,,);
|
|
## Get the thread_id of connection 3
|
|
eval $get_thread_id;
|
|
let $con3_thread_id= `SELECT @my_thread_id`;
|
|
eval $get_thread_os_id;
|
|
let $con3_thread_os_id= `SELECT @my_thread_os_id`;
|
|
eval $get_processlist_id;
|
|
let $con3_processlist_id= `SELECT @my_processlist_id`;
|
|
|
|
--enable_query_log
|
|
|
|
--echo
|
|
--echo ## Default connection
|
|
--connection default
|
|
|
|
--echo
|
|
--echo ## Create data tables
|
|
CREATE TABLE test.t1(a INT, b INT, c INT, d INT DEFAULT 0, PRIMARY KEY(a), INDEX index_b(b), INDEX index_cb(c, b));
|
|
CREATE TABLE test.t2 LIKE test.t1;
|
|
CREATE TABLE test.t3 LIKE test.t1;
|
|
|
|
--disable_query_log
|
|
--disable_result_log
|
|
|
|
--echo
|
|
--echo ## Generate event data from 3 connections
|
|
|
|
--connection con1
|
|
--echo ## Connection 1
|
|
SET @uvar_temp = 1;
|
|
INSERT INTO test.t1 SET a=101, b=1, c=1;
|
|
INSERT INTO test.t2 SET a=102, b=2, c=2;
|
|
INSERT INTO test.t2 SET a=103, b=3, c=3;
|
|
INSERT INTO test.t3 SET a=104, b=4, c=4;
|
|
INSERT INTO test.t3 SET a=105, b=5, c=5;
|
|
INSERT INTO test.t3 SET a=106, b=6, c=6;
|
|
SELECT * FROM test.t1;
|
|
SELECT * FROM test.t2;
|
|
SELECT * FROM test.t3;
|
|
UPDATE test.t1 SET d=d+1;
|
|
UPDATE test.t2 SET d=d+1;
|
|
UPDATE test.t3 SET d=d+1;
|
|
UPDATE test.t1 SET d=d+1 WHERE a=101;
|
|
UPDATE test.t2 SET d=d+1 WHERE a=101;
|
|
UPDATE test.t3 SET d=d+1 WHERE a=101;
|
|
SELECT * FROM test.t1 FORCE INDEX(index_b) WHERE b=5;
|
|
SELECT * FROM test.t2 FORCE INDEX(index_b) WHERE b=5;
|
|
SELECT * FROM test.t3 FORCE INDEX(index_b) WHERE b=5;
|
|
|
|
--connection con2
|
|
--echo ## Connection 2
|
|
SET @uvar_temp = 2;
|
|
INSERT INTO test.t1 SET a=201, b=1, c=1;
|
|
INSERT INTO test.t2 SET a=202, b=2, c=2;
|
|
INSERT INTO test.t2 SET a=203, b=3, c=3;
|
|
INSERT INTO test.t3 SET a=204, b=4, c=4;
|
|
INSERT INTO test.t3 SET a=205, b=5, c=5;
|
|
INSERT INTO test.t3 SET a=206, b=6, c=6;
|
|
SELECT * FROM test.t1;
|
|
SELECT * FROM test.t2;
|
|
SELECT * FROM test.t3;
|
|
UPDATE test.t1 SET d=d+1;
|
|
UPDATE test.t2 SET d=d+1;
|
|
UPDATE test.t3 SET d=d+1;
|
|
UPDATE test.t1 SET d=d+1 WHERE a=201;
|
|
UPDATE test.t2 SET d=d+1 WHERE a=201;
|
|
UPDATE test.t3 SET d=d+1 WHERE a=201;
|
|
SELECT * FROM test.t1 FORCE INDEX(index_b) WHERE b=5;
|
|
SELECT * FROM test.t2 FORCE INDEX(index_b) WHERE b=5;
|
|
SELECT * FROM test.t3 FORCE INDEX(index_b) WHERE b=5;
|
|
|
|
--connection con3
|
|
--echo ## Connection 3
|
|
SET @uvar_temp = 3;
|
|
INSERT INTO test.t1 SET a=301, b=1, c=1;
|
|
INSERT INTO test.t2 SET a=302, b=2, c=2;
|
|
INSERT INTO test.t2 SET a=303, b=3, c=3;
|
|
INSERT INTO test.t3 SET a=304, b=4, c=4;
|
|
INSERT INTO test.t3 SET a=305, b=5, c=5;
|
|
INSERT INTO test.t3 SET a=306, b=6, c=6;
|
|
SELECT * FROM test.t1;
|
|
SELECT * FROM test.t2;
|
|
SELECT * FROM test.t3;
|
|
UPDATE test.t1 SET d=d+1;
|
|
UPDATE test.t2 SET d=d+1;
|
|
UPDATE test.t3 SET d=d+1;
|
|
UPDATE test.t1 SET d=d+1 WHERE a=301;
|
|
UPDATE test.t2 SET d=d+1 WHERE a=301;
|
|
UPDATE test.t3 SET d=d+1 WHERE a=301;
|
|
SELECT * FROM test.t1 FORCE INDEX(index_b) WHERE b=5;
|
|
SELECT * FROM test.t2 FORCE INDEX(index_b) WHERE b=5;
|
|
SELECT * FROM test.t3 FORCE INDEX(index_b) WHERE b=5;
|
|
|
|
--enable_result_log
|
|
--enable_query_log
|
|
|
|
--connection con2
|
|
--echo ## Connection 2
|
|
--echo ## Create prepared statements on connection 2
|
|
PREPARE st1 FROM 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse';
|
|
PREPARE st3 FROM 'INSERT INTO test.t1 SELECT * FROM test.t1 WHERE a<=?';
|
|
PREPARE st4 FROM '(SELECT a FROM test.t1) UNION (SELECT a+10 FROM test.t1) ORDER BY RAND()*0+a';
|
|
|
|
--echo ## Create stored procedure connection 2
|
|
DELIMITER |;
|
|
CREATE PROCEDURE SampleProc1()
|
|
BEGIN
|
|
SET @table = 'test.t1';
|
|
SET @s = CONCAT('SELECT * FROM ', @table);
|
|
PREPARE st2 FROM @s;
|
|
END|
|
|
DELIMITER ;|
|
|
|
|
--echo
|
|
--echo Call SampleProc1() to prepare a statement
|
|
CALL SampleProc1();
|
|
|
|
--disable_query_log
|
|
--disable_result_log
|
|
|
|
## Generate a system error
|
|
--error ER_NO_SUCH_TABLE
|
|
SELECT * from test.no_table;
|
|
|
|
--enable_result_log
|
|
|
|
#DEBUG --echo
|
|
#DEBUG SELECT * FROM performance_schema.prepared_statements_instances;
|
|
|
|
--echo ## Connection default
|
|
--connection default
|
|
|
|
# Set default column names
|
|
SET @pfs_columns = "*";
|
|
let $target_host = localhost;
|
|
|
|
--echo
|
|
--echo ## Use Connection 2 for default target thread id
|
|
eval SET @target_thread_id= $con2_thread_id;
|
|
eval SET @default_thread_id= $default_thread_id;
|
|
|
|
# DEBUG
|
|
#--echo ### Default thread: $default_thread_id
|
|
#--echo ### Con1 thread id: $con1_thread_id
|
|
#--echo ### Con2 thread id: $con2_thread_id (target)
|
|
#--echo ### Con3 thread id: $con3_thread_id
|
|
|
|
--source ../include/idx_compare_setup_index_test.inc
|
|
|
|
--enable_query_log
|