diff options
author | Marko Mäkelä <marko.makela@mariadb.com> | 2021-09-24 08:42:23 +0300 |
---|---|---|
committer | Marko Mäkelä <marko.makela@mariadb.com> | 2021-09-24 08:42:23 +0300 |
commit | 7e2b42324caf6faaaa5a3a033c2b24e1fee93669 (patch) | |
tree | 282e59399ddb9e3f72f2607deb163e580f06a88f /mysql-test/suite/innodb | |
parent | f5794e1dc6e3d27405daeae850b8e69fd631b62d (diff) | |
parent | 913efaa328026455cef0e608c25eaed042344b15 (diff) | |
download | mariadb-git-7e2b42324caf6faaaa5a3a033c2b24e1fee93669.tar.gz |
Merge 10.4 into 10.5
Diffstat (limited to 'mysql-test/suite/innodb')
-rw-r--r-- | mysql-test/suite/innodb/r/innodb_defrag_stats.result | 104 | ||||
-rw-r--r-- | mysql-test/suite/innodb/t/innodb_defrag_stats.test | 72 |
2 files changed, 60 insertions, 116 deletions
diff --git a/mysql-test/suite/innodb/r/innodb_defrag_stats.result b/mysql-test/suite/innodb/r/innodb_defrag_stats.result index 598124e4ccb..b60f5f5c5a9 100644 --- a/mysql-test/suite/innodb/r/innodb_defrag_stats.result +++ b/mysql-test/suite/innodb/r/innodb_defrag_stats.result @@ -1,22 +1,8 @@ -DROP TABLE if exists t1; -select @@global.innodb_stats_persistent; -@@global.innodb_stats_persistent -0 -set global innodb_defragment_stats_accuracy = 20; +SET GLOBAL innodb_defragment_stats_accuracy = 20; +DELETE FROM mysql.innodb_index_stats; # Create table. CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY AUTO_INCREMENT, b VARCHAR(256), KEY SECOND(a, b)) ENGINE=INNODB; -# Populate data -INSERT INTO t1 VALUES(1, REPEAT('A', 256)); -INSERT INTO t1 (b) SELECT b from t1; -INSERT INTO t1 (b) SELECT b from t1; -INSERT INTO t1 (b) SELECT b from t1; -INSERT INTO t1 (b) SELECT b from t1; -INSERT INTO t1 (b) SELECT b from t1; -INSERT INTO t1 (b) SELECT b from t1; -INSERT INTO t1 (b) SELECT b from t1; -INSERT INTO t1 (b) SELECT b from t1; -INSERT INTO t1 (b) SELECT b from t1; -INSERT INTO t1 (b) SELECT b from t1; +INSERT INTO t1 SELECT seq, REPEAT('A', 256) FROM seq_1_to_1024; # Not enough page splits to trigger persistent stats write yet. select count(stat_value) = 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_page_split'); count(stat_value) = 0 @@ -27,7 +13,7 @@ count(stat_value) = 0 select count(stat_value) = 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_leaf_pages_defrag'); count(stat_value) = 0 1 -INSERT INTO t1 (b) SELECT b from t1; +INSERT INTO t1 SELECT seq, REPEAT('A', 256) FROM seq_1025_to_2048; # Persistent stats recorded. select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_page_split'); count(stat_value) > 0 @@ -39,6 +25,7 @@ select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like count(stat_value) > 0 1 # Delete some rows. +BEGIN; delete from t1 where a between 100 * 20 and 100 * 20 + 30; delete from t1 where a between 100 * 19 and 100 * 19 + 30; delete from t1 where a between 100 * 18 and 100 * 18 + 30; @@ -59,9 +46,7 @@ delete from t1 where a between 100 * 4 and 100 * 4 + 30; delete from t1 where a between 100 * 3 and 100 * 3 + 30; delete from t1 where a between 100 * 2 and 100 * 2 + 30; delete from t1 where a between 100 * 1 and 100 * 1 + 30; -# restart -# Server Restarted -# Confirm persistent stats still there after restart. +COMMIT; select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_page_split'); count(stat_value) > 0 1 @@ -74,9 +59,6 @@ count(stat_value) > 0 optimize table t1; Table Op Msg_type Msg_text test.t1 optimize status OK -select sleep(2); -sleep(2) -0 select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_page_split'); count(stat_value) > 0 1 @@ -109,9 +91,6 @@ count(stat_value) > 0 1 # Table rename should cause stats rename. rename table t1 to t2; -select sleep(1); -sleep(1) -0 select count(stat_value) = 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_page_split'); count(stat_value) = 0 1 @@ -130,48 +109,37 @@ count(stat_value) > 0 select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like '%t2%' and stat_name in ('n_leaf_pages_defrag'); count(stat_value) > 0 1 -# Drop index should cause stats drop. +# Drop index should cause stats drop, but will not. drop index SECOND on t2; -select sleep(3); -sleep(3) -0 -select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like '%t2%' and index_name = 'SECOND' and stat_name in ('n_page_split'); -count(stat_value) > 0 -1 -select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like '%t2%' and index_name = 'SECOND' and stat_name in ('n_pages_freed'); -count(stat_value) > 0 -1 -select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like '%t2%' and index_name = 'SECOND' and stat_name in ('n_leaf_pages_defrag'); -count(stat_value) > 0 -1 +SELECT stat_name, stat_value>0 FROM mysql.innodb_index_stats +WHERE table_name like '%t2%' AND index_name='SECOND'; +stat_name stat_value>0 +n_leaf_pages_defrag 1 +n_leaf_pages_reserved 1 +n_page_split 1 +n_pages_freed 1 +# +# MDEV-26636: Statistics must not be written for temporary tables +# +SET GLOBAL innodb_defragment_stats_accuracy = 1; +CREATE TEMPORARY TABLE t (a INT PRIMARY KEY, c CHAR(255) NOT NULL) +ENGINE=InnoDB; +INSERT INTO t SELECT seq, '' FROM seq_1_to_100; # restart -Server Restarted -select count(stat_value) = 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_page_split'); -count(stat_value) = 0 -1 -select count(stat_value) = 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_pages_freed'); -count(stat_value) = 0 -1 -select count(stat_value) = 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_leaf_pages_defrag'); -count(stat_value) = 0 -1 -select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like '%t2%' and stat_name in ('n_page_split'); -count(stat_value) > 0 -1 -select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like '%t2%' and stat_name in ('n_pages_freed'); -count(stat_value) > 0 -1 -select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like '%t2%' and stat_name in ('n_leaf_pages_defrag'); -count(stat_value) > 0 -1 +SELECT * FROM mysql.innodb_index_stats where table_name like '%t1%'; +database_name table_name index_name last_update stat_name stat_value sample_size stat_description +SELECT table_name, index_name, stat_name, stat_value>0 +FROM mysql.innodb_index_stats; +table_name index_name stat_name stat_value>0 +t2 PRIMARY n_leaf_pages_defrag 1 +t2 PRIMARY n_leaf_pages_reserved 1 +t2 PRIMARY n_page_split 1 +t2 PRIMARY n_pages_freed 1 +t2 SECOND n_leaf_pages_defrag 1 +t2 SECOND n_leaf_pages_reserved 1 +t2 SECOND n_page_split 1 +t2 SECOND n_pages_freed 1 # Clean up DROP TABLE t2; -select count(stat_value) = 0 from mysql.innodb_index_stats where table_name like '%t2%' and stat_name in ('n_page_split'); -count(stat_value) = 0 -1 -select count(stat_value) = 0 from mysql.innodb_index_stats where table_name like '%t2%' and stat_name in ('n_pages_freed'); -count(stat_value) = 0 -1 -select count(stat_value) = 0 from mysql.innodb_index_stats where table_name like '%t2%' and stat_name in ('n_leaf_pages_defrag'); -count(stat_value) = 0 -1 +SELECT * FROM mysql.innodb_index_stats; +database_name table_name index_name last_update stat_name stat_value sample_size stat_description diff --git a/mysql-test/suite/innodb/t/innodb_defrag_stats.test b/mysql-test/suite/innodb/t/innodb_defrag_stats.test index 2a5026a68e5..e1e88a07477 100644 --- a/mysql-test/suite/innodb/t/innodb_defrag_stats.test +++ b/mysql-test/suite/innodb/t/innodb_defrag_stats.test @@ -1,41 +1,23 @@ --source include/have_innodb.inc ---source include/big_test.inc --source include/not_valgrind.inc --source include/not_embedded.inc +--source include/have_sequence.inc ---disable_warnings -DROP TABLE if exists t1; ---enable_warnings +SET GLOBAL innodb_defragment_stats_accuracy = 20; ---disable_query_log -let $innodb_defragment_stats_accuracy_orig=`select @@innodb_defragment_stats_accuracy`; ---enable_query_log - -select @@global.innodb_stats_persistent; -set global innodb_defragment_stats_accuracy = 20; +DELETE FROM mysql.innodb_index_stats; --echo # Create table. CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY AUTO_INCREMENT, b VARCHAR(256), KEY SECOND(a, b)) ENGINE=INNODB; ---echo # Populate data -INSERT INTO t1 VALUES(1, REPEAT('A', 256)); -INSERT INTO t1 (b) SELECT b from t1; -INSERT INTO t1 (b) SELECT b from t1; -INSERT INTO t1 (b) SELECT b from t1; -INSERT INTO t1 (b) SELECT b from t1; -INSERT INTO t1 (b) SELECT b from t1; -INSERT INTO t1 (b) SELECT b from t1; -INSERT INTO t1 (b) SELECT b from t1; -INSERT INTO t1 (b) SELECT b from t1; -INSERT INTO t1 (b) SELECT b from t1; -INSERT INTO t1 (b) SELECT b from t1; +INSERT INTO t1 SELECT seq, REPEAT('A', 256) FROM seq_1_to_1024; --echo # Not enough page splits to trigger persistent stats write yet. select count(stat_value) = 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_page_split'); select count(stat_value) = 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_pages_freed'); select count(stat_value) = 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_leaf_pages_defrag'); -INSERT INTO t1 (b) SELECT b from t1; +INSERT INTO t1 SELECT seq, REPEAT('A', 256) FROM seq_1025_to_2048; --echo # Persistent stats recorded. select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_page_split'); @@ -43,6 +25,7 @@ select count(stat_value) = 0 from mysql.innodb_index_stats where table_name like select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_leaf_pages_defrag'); --echo # Delete some rows. +BEGIN; let $num_delete = 20; while ($num_delete) { @@ -50,17 +33,13 @@ while ($num_delete) eval delete from t1 where a between $j and $j + 30; dec $num_delete; } +COMMIT; ---source include/restart_mysqld.inc ---echo # Server Restarted - ---echo # Confirm persistent stats still there after restart. select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_page_split'); select count(stat_value) = 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_pages_freed'); select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_leaf_pages_defrag'); optimize table t1; -select sleep(2); select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_page_split'); select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_pages_freed'); @@ -84,7 +63,6 @@ select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like --echo # Table rename should cause stats rename. rename table t1 to t2; -select sleep(1); select count(stat_value) = 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_page_split'); select count(stat_value) = 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_pages_freed'); @@ -94,32 +72,30 @@ select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like '%t2%' and stat_name in ('n_pages_freed'); select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like '%t2%' and stat_name in ('n_leaf_pages_defrag'); ---echo # Drop index should cause stats drop. +--echo # Drop index should cause stats drop, but will not. drop index SECOND on t2; -select sleep(3); -select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like '%t2%' and index_name = 'SECOND' and stat_name in ('n_page_split'); -select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like '%t2%' and index_name = 'SECOND' and stat_name in ('n_pages_freed'); -select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like '%t2%' and index_name = 'SECOND' and stat_name in ('n_leaf_pages_defrag'); +--sorted_result +SELECT stat_name, stat_value>0 FROM mysql.innodb_index_stats +WHERE table_name like '%t2%' AND index_name='SECOND'; + +--echo # +--echo # MDEV-26636: Statistics must not be written for temporary tables +--echo # +SET GLOBAL innodb_defragment_stats_accuracy = 1; +CREATE TEMPORARY TABLE t (a INT PRIMARY KEY, c CHAR(255) NOT NULL) +ENGINE=InnoDB; +INSERT INTO t SELECT seq, '' FROM seq_1_to_100; --source include/restart_mysqld.inc ---echo Server Restarted -select count(stat_value) = 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_page_split'); -select count(stat_value) = 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_pages_freed'); -select count(stat_value) = 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_leaf_pages_defrag'); +SELECT * FROM mysql.innodb_index_stats where table_name like '%t1%'; -select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like '%t2%' and stat_name in ('n_page_split'); -select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like '%t2%' and stat_name in ('n_pages_freed'); -select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like '%t2%' and stat_name in ('n_leaf_pages_defrag'); +--sorted_result +SELECT table_name, index_name, stat_name, stat_value>0 +FROM mysql.innodb_index_stats; --echo # Clean up DROP TABLE t2; -select count(stat_value) = 0 from mysql.innodb_index_stats where table_name like '%t2%' and stat_name in ('n_page_split'); -select count(stat_value) = 0 from mysql.innodb_index_stats where table_name like '%t2%' and stat_name in ('n_pages_freed'); -select count(stat_value) = 0 from mysql.innodb_index_stats where table_name like '%t2%' and stat_name in ('n_leaf_pages_defrag'); - ---disable_query_log -EVAL SET GLOBAL innodb_defragment_stats_accuracy = $innodb_defragment_stats_accuracy_orig; ---enable_query_log +SELECT * FROM mysql.innodb_index_stats; |