diff options
| author | Marko Mäkelä <marko.makela@mariadb.com> | 2023-03-11 10:45:35 +0200 |
|---|---|---|
| committer | Marko Mäkelä <marko.makela@mariadb.com> | 2023-03-11 10:45:35 +0200 |
| commit | 7ca89af6f8faf1f8ec6ede01a9353ac499d37711 (patch) | |
| tree | 9d35bed4173a3bba55060c911f467e2ba83fd9e7 /mysql-test/suite | |
| parent | b314f7b6420069a01d64b33fa461f0914feb8638 (diff) | |
| download | mariadb-git-7ca89af6f8faf1f8ec6ede01a9353ac499d37711.tar.gz | |
MDEV-30545 Remove innodb_defragment and related parameters
The deprecated parameters will be removed:
innodb_defragment
innodb_defragment_n_pages
innodb_defragment_stats_accuracy
innodb_defragment_fill_factor_n_recs
innodb_defragment_fill_factor
innodb_defragment_frequency
The mysql.innodb_index_stats.stat_name values 'n_page_split' and
'n_pages_freed' will lose their special meaning.
The related changes to OPTIMIZE TABLE in InnoDB will be removed as well.
The parameter innodb_optimize_fulltext_only will retain its special
meaning in OPTIMIZE TABLE.
Tested by: Matthias Leich
Diffstat (limited to 'mysql-test/suite')
42 files changed, 0 insertions, 2000 deletions
diff --git a/mysql-test/suite/encryption/r/innodb-bad-key-change4.result b/mysql-test/suite/encryption/r/innodb-bad-key-change4.result deleted file mode 100644 index 9983c26c9d7..00000000000 --- a/mysql-test/suite/encryption/r/innodb-bad-key-change4.result +++ /dev/null @@ -1,25 +0,0 @@ -call mtr.add_suppression("InnoDB: Table `test`\\.`t1` (has an unreadable root page)"); -call mtr.add_suppression("InnoDB: The page \\[page id: space=[1-9][0-9]*, page number=[1-9][0-9]*\\] in file '.*test.t1\\.ibd' cannot be decrypted; key_version=1"); -call mtr.add_suppression("InnoDB: Recovery failed to read page"); -call mtr.add_suppression("Couldn't load plugins from 'file_key_management"); -call mtr.add_suppression("Table .*t1.* is corrupted"); -call mtr.add_suppression("\\[ERROR\\] InnoDB: Cannot decrypt \\[page id: space="); -# restart: --plugin-load-add=file_key_management --file-key-management --file-key-management-filename=MYSQL_TEST_DIR/std_data/keys2.txt -CREATE TABLE t1 (pk INT PRIMARY KEY, f VARCHAR(8)) ENGINE=InnoDB -ENCRYPTED=YES ENCRYPTION_KEY_ID=4; -INSERT INTO t1 VALUES (1,'foo'),(2,'bar'); -# restart: --plugin-load-add=file_key_management --file-key-management --file-key-management-filename=MYSQL_TEST_DIR/std_data/keys3.txt -OPTIMIZE TABLE t1; -Table Op Msg_type Msg_text -test.t1 optimize Error Table 'test.t1' doesn't exist in engine -test.t1 optimize status Operation failed -SHOW WARNINGS; -Level Code Message -CHECK TABLE t1; -Table Op Msg_type Msg_text -test.t1 check Error Table test/t1 is corrupted. Please drop the table and recreate. -test.t1 check error Corrupt -SHOW WARNINGS; -Level Code Message -# restart: --plugin-load-add=file_key_management --file-key-management --file-key-management-filename=MYSQL_TEST_DIR/std_data/keys2.txt -DROP TABLE t1; diff --git a/mysql-test/suite/encryption/t/innodb-bad-key-change4.combinations b/mysql-test/suite/encryption/t/innodb-bad-key-change4.combinations deleted file mode 100644 index 729380593f3..00000000000 --- a/mysql-test/suite/encryption/t/innodb-bad-key-change4.combinations +++ /dev/null @@ -1,5 +0,0 @@ -[strict_crc32] ---innodb-checksum-algorithm=strict_crc32 - -[strict_full_crc32] ---innodb-checksum-algorithm=strict_full_crc32 diff --git a/mysql-test/suite/encryption/t/innodb-bad-key-change4.opt b/mysql-test/suite/encryption/t/innodb-bad-key-change4.opt deleted file mode 100644 index d20d3b60913..00000000000 --- a/mysql-test/suite/encryption/t/innodb-bad-key-change4.opt +++ /dev/null @@ -1,6 +0,0 @@ ---loose-innodb-buffer-pool-stats ---loose-innodb-buffer-page ---loose-innodb-buffer-page-lru ---innodb-defragment=1 ---innodb-purge-rseg-truncate-frequency=1 ---skip-innodb-fast-shutdown diff --git a/mysql-test/suite/encryption/t/innodb-bad-key-change4.test b/mysql-test/suite/encryption/t/innodb-bad-key-change4.test deleted file mode 100644 index b041c988851..00000000000 --- a/mysql-test/suite/encryption/t/innodb-bad-key-change4.test +++ /dev/null @@ -1,42 +0,0 @@ ---source include/have_innodb.inc -# embedded does not support restart --- source include/not_embedded.inc --- source filekeys_plugin_exists.inc -# -# MDEV-8769: Server crash at file btr0btr.ic line 122 when defragmenting encrypted table using incorrect keys -# MDEV-8768: Server crash at file btr0btr.ic line 122 when checking encrypted table using incorrect keys -# - -call mtr.add_suppression("InnoDB: Table `test`\\.`t1` (has an unreadable root page)"); -call mtr.add_suppression("InnoDB: The page \\[page id: space=[1-9][0-9]*, page number=[1-9][0-9]*\\] in file '.*test.t1\\.ibd' cannot be decrypted; key_version=1"); -call mtr.add_suppression("InnoDB: Recovery failed to read page"); -# Suppression for builds where file_key_management plugin is linked statically -call mtr.add_suppression("Couldn't load plugins from 'file_key_management"); -call mtr.add_suppression("Table .*t1.* is corrupted"); -# for innodb_checksum_algorithm=full_crc32 only -call mtr.add_suppression("\\[ERROR\\] InnoDB: Cannot decrypt \\[page id: space="); - ---let $restart_parameters=--plugin-load-add=file_key_management --file-key-management --file-key-management-filename=$MYSQL_TEST_DIR/std_data/keys2.txt ---source include/restart_mysqld.inc - -CREATE TABLE t1 (pk INT PRIMARY KEY, f VARCHAR(8)) ENGINE=InnoDB -ENCRYPTED=YES ENCRYPTION_KEY_ID=4; -INSERT INTO t1 VALUES (1,'foo'),(2,'bar'); - ---let $restart_parameters=--plugin-load-add=file_key_management --file-key-management --file-key-management-filename=$MYSQL_TEST_DIR/std_data/keys3.txt ---source include/restart_mysqld.inc - ---replace_regex /key_id [1-9][0-9]*/\1 / -OPTIMIZE TABLE t1; ---replace_regex /key_id [1-9][0-9]*/\1 / -SHOW WARNINGS; - ---replace_regex /key_id [1-9][0-9]*/\1 / -CHECK TABLE t1; ---replace_regex /key_id [1-9][0-9]*/\1 / -SHOW WARNINGS; - ---let $restart_parameters=--plugin-load-add=file_key_management --file-key-management --file-key-management-filename=$MYSQL_TEST_DIR/std_data/keys2.txt ---source include/restart_mysqld.inc - -DROP TABLE t1; diff --git a/mysql-test/suite/innodb/r/defrag_mdl-9155.result b/mysql-test/suite/innodb/r/defrag_mdl-9155.result deleted file mode 100644 index b1b70de3373..00000000000 --- a/mysql-test/suite/innodb/r/defrag_mdl-9155.result +++ /dev/null @@ -1,22 +0,0 @@ -set global innodb_defragment=1; -Warnings: -Warning 1287 '@@innodb_defragment' is deprecated and will be removed in a future release -create table t1 (a int not null primary key auto_increment, b varchar(256), key second(a, b)) engine=innodb; -insert t1 select null, repeat('a', 256) from seq_1_to_100; -select count(*) from t1; -count(*) -100 -connect con1,localhost,root; -start transaction; -select count(*) from t1; -count(*) -100 -connection default; -optimize table t1; -Table Op Msg_type Msg_text -test.t1 optimize status OK -connection con1; -drop table t1; -set global innodb_defragment=default; -Warnings: -Warning 1287 '@@innodb_defragment' is deprecated and will be removed in a future release diff --git a/mysql-test/suite/innodb/r/innodb_defrag_binlog.result b/mysql-test/suite/innodb/r/innodb_defrag_binlog.result deleted file mode 100644 index b97061cc9db..00000000000 --- a/mysql-test/suite/innodb/r/innodb_defrag_binlog.result +++ /dev/null @@ -1,31 +0,0 @@ -include/master-slave.inc -[connection master] -drop table if exists t1; -create table t1(a int not null primary key auto_increment, b varchar(256), key second(b)) engine=innodb; -insert into t1 values (1, REPEAT("a", 256)); -insert into t1 values (2, REPEAT("a", 256)); -optimize table t1; -Table Op Msg_type Msg_text -test.t1 optimize status OK -drop table t1; -include/show_binlog_events.inc -Log_name Pos Event_type Server_id End_log_pos Info -master-bin.000001 # Gtid # # GTID #-#-# -master-bin.000001 # Query # # use `test`; DROP TABLE IF EXISTS `t1` /* generated by server */ -master-bin.000001 # Gtid # # GTID #-#-# -master-bin.000001 # Query # # use `test`; create table t1(a int not null primary key auto_increment, b varchar(256), key second(b)) engine=innodb -master-bin.000001 # Gtid # # BEGIN GTID #-#-# -master-bin.000001 # Annotate_rows # # insert into t1 values (1, REPEAT("a", 256)) -master-bin.000001 # Table_map # # table_id: # (test.t1) -master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F -master-bin.000001 # Xid # # COMMIT /* XID */ -master-bin.000001 # Gtid # # BEGIN GTID #-#-# -master-bin.000001 # Annotate_rows # # insert into t1 values (2, REPEAT("a", 256)) -master-bin.000001 # Table_map # # table_id: # (test.t1) -master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F -master-bin.000001 # Xid # # COMMIT /* XID */ -master-bin.000001 # Gtid # # GTID #-#-# -master-bin.000001 # Query # # use `test`; optimize table t1 -master-bin.000001 # Gtid # # GTID #-#-# -master-bin.000001 # Query # # use `test`; DROP TABLE `t1` /* generated by server */ -include/rpl_end.inc diff --git a/mysql-test/suite/innodb/r/innodb_defrag_concurrent.result b/mysql-test/suite/innodb/r/innodb_defrag_concurrent.result deleted file mode 100644 index 72d520a61b4..00000000000 --- a/mysql-test/suite/innodb/r/innodb_defrag_concurrent.result +++ /dev/null @@ -1,110 +0,0 @@ -SET @n_pages= @@GLOBAL.innodb_defragment_n_pages; -SET @accuracy= @@GLOBAL.innodb_defragment_stats_accuracy; -SET @sp= @@GLOBAL.innodb_stats_persistent; -SET GLOBAL innodb_stats_persistent = 0; -set global innodb_defragment_stats_accuracy = 80; -Warnings: -Warning 1287 '@@innodb_defragment_stats_accuracy' is deprecated and will be removed in a future release -CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY AUTO_INCREMENT, -b VARCHAR(256), -c INT, -g GEOMETRY NOT NULL, -t VARCHAR(256), -KEY second(a, b), -KEY third(c), -SPATIAL gk(g), -FULLTEXT INDEX fti(t)) ENGINE=INNODB; -connect con1,localhost,root,,test,$MASTER_MYPORT,$MASTER_MYSOCK; -connect con2,localhost,root,,test,$MASTER_MYPORT,$MASTER_MYSOCK; -connect con3,localhost,root,,test,$MASTER_MYPORT,$MASTER_MYSOCK; -connect con4,localhost,root,,test,$MASTER_MYPORT,$MASTER_MYSOCK; -connection default; -SET @@global.innodb_defragment_n_pages = 20; -Warnings: -Warning 1287 '@@innodb_defragment_n_pages' is deprecated and will be removed in a future release -CREATE TEMPORARY TABLE tt (a INT, KEY(a)) ENGINE=InnoDB ROW_FORMAT=REDUNDANT; -INSERT INTO tt SELECT 0 FROM seq_1_to_180; -INSERT INTO tt SELECT 5 FROM seq_1_to_160; -INSERT INTO tt SELECT 1 FROM seq_1_to_1000; -OPTIMIZE TABLE tt; -Table Op Msg_type Msg_text -test.tt optimize note Table does not support optimize, doing recreate + analyze instead -test.tt optimize status OK -select count(*) from t1; -count(*) -20000 -select count(*) from t1 force index (second); -count(*) -20000 -select count(*) from t1 force index (third); -count(*) -20000 -select count(*) from t1; -count(*) -15800 -select count(*) from t1 force index (second); -count(*) -15800 -select count(*) from t1 force index (third); -count(*) -15800 -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 -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 -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 -connection con1; -optimize table t1;; -connection default; -INSERT INTO t1 VALUES (400000, REPEAT('A', 256),300000, Point(1,1),'More like a test but different.');; -connection con2; -INSERT INTO t1 VALUES (500000, REPEAT('A', 256),400000, Point(1,1),'Totally different text book.');; -connection con3; -DELETE FROM t1 where a between 1 and 100;; -connection con4; -UPDATE t1 SET c = c + 1 where c between 2000 and 8000;; -connection con1; -connection con2; -connection con3; -connection con4; -connection default; -disconnect con1; -disconnect con2; -disconnect con3; -disconnect con4; -optimize table t1; -Table Op Msg_type Msg_text -test.t1 optimize status OK -check table t1 extended; -Table Op Msg_type Msg_text -test.t1 check status OK -select count(*) from t1; -count(*) -15723 -select count(*) from t1 force index (second); -count(*) -15723 -select count(*) from t1 force index (third); -count(*) -15723 -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_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_leaf_pages_defrag'); -count(stat_value) > 0 -1 -drop table t1; -SET GLOBAL innodb_defragment_n_pages = @n_pages; -Warnings: -Warning 1287 '@@innodb_defragment_n_pages' is deprecated and will be removed in a future release -SET GLOBAL innodb_defragment_stats_accuracy = @accuracy; -Warnings: -Warning 1287 '@@innodb_defragment_stats_accuracy' is deprecated and will be removed in a future release -SET GLOBAL innodb_stats_persistent = @sp; diff --git a/mysql-test/suite/innodb/r/innodb_defrag_stats.result b/mysql-test/suite/innodb/r/innodb_defrag_stats.result deleted file mode 100644 index 1753ce36bed..00000000000 --- a/mysql-test/suite/innodb/r/innodb_defrag_stats.result +++ /dev/null @@ -1,139 +0,0 @@ -SET GLOBAL innodb_defragment_stats_accuracy = 20; -Warnings: -Warning 1287 '@@innodb_defragment_stats_accuracy' is deprecated and will be removed in a future release -DELETE FROM mysql.innodb_index_stats; -# Create table. -CREATE TABLE t1 (a INT PRIMARY KEY AUTO_INCREMENT, b VARCHAR(256), -KEY SECOND(a, b)) ENGINE=INNODB STATS_PERSISTENT=0; -INSERT INTO t1 SELECT 100*FLOOR(seq/70)+seq%70, REPEAT('A', 256) -FROM seq_1_to_1024; -# Not enough page splits to trigger persistent stats write yet. -SELECT * FROM mysql.innodb_index_stats; -database_name table_name index_name last_update stat_name stat_value sample_size stat_description -INSERT INTO t1 SELECT 100*FLOOR(seq/70)+seq%70, REPEAT('A', 256) -FROM seq_1025_to_1433; -BEGIN; -INSERT INTO t1 SELECT 100*20+seq, REPEAT('A', 256) -FROM seq_70_to_99; -INSERT INTO t1 SELECT 100*19+seq, REPEAT('A', 256) -FROM seq_70_to_99; -INSERT INTO t1 SELECT 100*18+seq, REPEAT('A', 256) -FROM seq_70_to_99; -INSERT INTO t1 SELECT 100*17+seq, REPEAT('A', 256) -FROM seq_70_to_99; -INSERT INTO t1 SELECT 100*16+seq, REPEAT('A', 256) -FROM seq_70_to_99; -INSERT INTO t1 SELECT 100*15+seq, REPEAT('A', 256) -FROM seq_70_to_99; -INSERT INTO t1 SELECT 100*14+seq, REPEAT('A', 256) -FROM seq_70_to_99; -INSERT INTO t1 SELECT 100*13+seq, REPEAT('A', 256) -FROM seq_70_to_99; -INSERT INTO t1 SELECT 100*12+seq, REPEAT('A', 256) -FROM seq_70_to_99; -INSERT INTO t1 SELECT 100*11+seq, REPEAT('A', 256) -FROM seq_70_to_99; -INSERT INTO t1 SELECT 100*10+seq, REPEAT('A', 256) -FROM seq_70_to_99; -INSERT INTO t1 SELECT 100*9+seq, REPEAT('A', 256) -FROM seq_70_to_99; -INSERT INTO t1 SELECT 100*8+seq, REPEAT('A', 256) -FROM seq_70_to_99; -INSERT INTO t1 SELECT 100*7+seq, REPEAT('A', 256) -FROM seq_70_to_99; -INSERT INTO t1 SELECT 100*6+seq, REPEAT('A', 256) -FROM seq_70_to_99; -INSERT INTO t1 SELECT 100*5+seq, REPEAT('A', 256) -FROM seq_70_to_99; -INSERT INTO t1 SELECT 100*4+seq, REPEAT('A', 256) -FROM seq_70_to_99; -INSERT INTO t1 SELECT 100*3+seq, REPEAT('A', 256) -FROM seq_70_to_99; -INSERT INTO t1 SELECT 100*2+seq, REPEAT('A', 256) -FROM seq_70_to_99; -INSERT INTO t1 SELECT 100*1+seq, REPEAT('A', 256) -FROM seq_70_to_99; -ROLLBACK; -SELECT @@GLOBAL.innodb_force_recovery<2 "have background defragmentation"; -have background defragmentation -1 -SELECT table_name, index_name, stat_name FROM mysql.innodb_index_stats; -table_name index_name stat_name -t1 PRIMARY n_leaf_pages_defrag -t1 PRIMARY n_leaf_pages_reserved -t1 PRIMARY n_page_split -t1 SECOND n_leaf_pages_defrag -t1 SECOND n_leaf_pages_reserved -t1 SECOND n_page_split -optimize table t1; -Table Op Msg_type Msg_text -test.t1 optimize status OK -SELECT table_name, index_name, stat_name FROM mysql.innodb_index_stats; -table_name index_name stat_name -t1 PRIMARY n_leaf_pages_defrag -t1 PRIMARY n_leaf_pages_reserved -t1 PRIMARY n_page_split -t1 PRIMARY n_pages_freed -t1 SECOND n_leaf_pages_defrag -t1 SECOND n_leaf_pages_reserved -t1 SECOND n_page_split -t1 SECOND n_pages_freed -set global innodb_defragment_stats_accuracy = 40; -Warnings: -Warning 1287 '@@innodb_defragment_stats_accuracy' is deprecated and will be removed in a future release -INSERT INTO t1 (b) SELECT b from t1; -SELECT table_name, index_name, stat_name FROM mysql.innodb_index_stats; -table_name index_name stat_name -t1 PRIMARY n_leaf_pages_defrag -t1 PRIMARY n_leaf_pages_reserved -t1 PRIMARY n_page_split -t1 PRIMARY n_pages_freed -t1 SECOND n_leaf_pages_defrag -t1 SECOND n_leaf_pages_reserved -t1 SECOND n_page_split -t1 SECOND n_pages_freed -INSERT INTO t1 (b) SELECT b from t1; -SELECT stat_name FROM mysql.innodb_index_stats WHERE table_name='t1'; -stat_name -n_leaf_pages_defrag -n_leaf_pages_defrag -n_leaf_pages_reserved -n_leaf_pages_reserved -n_page_split -n_page_split -n_pages_freed -n_pages_freed -# Table rename should cause stats rename. -rename table t1 to t2; -SELECT table_name, index_name, stat_name FROM mysql.innodb_index_stats; -table_name index_name stat_name -t2 PRIMARY n_leaf_pages_defrag -t2 PRIMARY n_leaf_pages_reserved -t2 PRIMARY n_page_split -t2 PRIMARY n_pages_freed -t2 SECOND n_leaf_pages_defrag -t2 SECOND n_leaf_pages_reserved -t2 SECOND n_page_split -t2 SECOND n_pages_freed -drop index SECOND on t2; -# -# MDEV-26636: Statistics must not be written for temporary tables -# -SET GLOBAL innodb_defragment_stats_accuracy = 1; -Warnings: -Warning 1287 '@@innodb_defragment_stats_accuracy' is deprecated and will be removed in a future release -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 -SELECT table_name, index_name, stat_name FROM mysql.innodb_index_stats; -table_name index_name stat_name -t2 PRIMARY n_leaf_pages_defrag -t2 PRIMARY n_leaf_pages_reserved -t2 PRIMARY n_page_split -t2 PRIMARY n_pages_freed -# Clean up -ALTER TABLE t2 STATS_PERSISTENT=1; -DROP TABLE t2; -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/r/innodb_defrag_stats_many_tables.result b/mysql-test/suite/innodb/r/innodb_defrag_stats_many_tables.result deleted file mode 100644 index 37049b6386e..00000000000 --- a/mysql-test/suite/innodb/r/innodb_defrag_stats_many_tables.result +++ /dev/null @@ -1,42 +0,0 @@ -DROP TABLE if exists t1; -SET @start_table_definition_cache = @@global.table_definition_cache; -SET @@global.table_definition_cache = 400; -SET @start_flush_log_at_trx_commit = @@global.innodb_flush_log_at_trx_commit; -SET @@global.innodb_flush_log_at_trx_commit=2; -SET @start_innodb_defragment_stats_accuracy = @@global.innodb_defragment_stats_accuracy; -SET @@global.innodb_defragment_stats_accuracy = 80; -Warnings: -Warning 1287 '@@innodb_defragment_stats_accuracy' is deprecated and will be removed in a future release -CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY AUTO_INCREMENT, b VARCHAR(256), KEY SECOND(a, b)) ENGINE=INNODB; -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 (b) SELECT b from t1; -select stat_value > 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name = 'n_page_split'; -stat_value > 0 -Create 505 table to overflow the table cache. -Sleep for a while to make sure t1 is evicted. -select sleep(15); -sleep(15) -0 -Reload t1 to get defrag stats from persistent storage -INSERT INTO t1 (b) SELECT b from t1; -make sure the stats thread will wake up and do the write even if there's a race condition between set and reset. -select sleep(15); -sleep(15) -0 -select stat_value > 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name = 'n_page_split'; -stat_value > 0 -SET @@global.innodb_defragment_stats_accuracy = @start_innodb_defragment_stats_accuracy; -Warnings: -Warning 1287 '@@innodb_defragment_stats_accuracy' is deprecated and will be removed in a future release -SET @@global.table_definition_cache = @start_table_definition_cache; -DROP TABLE t1; diff --git a/mysql-test/suite/innodb/r/innodb_defragment.result b/mysql-test/suite/innodb/r/innodb_defragment.result deleted file mode 100644 index 72471060668..00000000000 --- a/mysql-test/suite/innodb/r/innodb_defragment.result +++ /dev/null @@ -1,139 +0,0 @@ -set global innodb_defragment_stats_accuracy = 80; -Warnings: -Warning 1287 '@@innodb_defragment_stats_accuracy' is deprecated and will be removed in a future release -CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY AUTO_INCREMENT, b VARCHAR(256), KEY SECOND(a, b)) ENGINE=INNODB; -optimize table t1; -Table Op Msg_type Msg_text -test.t1 optimize status OK -INSERT INTO t1 VALUES (100000, REPEAT('A', 256)); -INSERT INTO t1 VALUES (200000, REPEAT('A', 256)); -INSERT INTO t1 VALUES (300000, REPEAT('A', 256)); -INSERT INTO t1 VALUES (400000, REPEAT('A', 256)); -optimize table t1; -Table Op Msg_type Msg_text -test.t1 optimize status OK -create procedure defragment() -begin -set @i = 0; -repeat -set @i = @i + 1; -optimize table t1; -until @i = 3 end repeat; -end // -select count(stat_value) from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_pages_freed'); -count(stat_value) -0 -select count(stat_value) from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_page_split'); -count(stat_value) -2 -select count(stat_value) from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_leaf_pages_defrag'); -count(stat_value) -2 -select count(*) from t1; -count(*) -10004 -connect con1,localhost,root,,test,$MASTER_MYPORT,$MASTER_MYSOCK; -connection con1; -call defragment(); -connection default; -connection con1; -connection default; -disconnect con1; -optimize table t1; -Table Op Msg_type Msg_text -test.t1 optimize status OK -# restart -select count(*) from t1; -count(*) -7904 -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 -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 -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(*) from t1 force index (second); -count(*) -7904 -select count(stat_value) = 0 from mysql.innodb_index_stats where table_name like '%t1%' 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 '%t1%' 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 '%t1%' and index_name = 'second' and stat_name in ('n_leaf_pages_defrag'); -count(stat_value) = 0 -1 -SET @@global.innodb_defragment_n_pages = 3; -Warnings: -Warning 1287 '@@innodb_defragment_n_pages' is deprecated and will be removed in a future release -optimize table t1; -Table Op Msg_type Msg_text -test.t1 optimize status OK -# restart -select count(stat_value) < 3 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_pages_freed'); -count(stat_value) < 3 -1 -select count(stat_value) < 3 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_page_split'); -count(stat_value) < 3 -1 -select count(stat_value) < 3 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_leaf_pages_defrag'); -count(stat_value) < 3 -1 -select count(*) from t1; -count(*) -6904 -select count(stat_value) < 3 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_pages_freed'); -count(stat_value) < 3 -1 -select count(stat_value) < 3 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_page_split'); -count(stat_value) < 3 -1 -select count(stat_value) < 3 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_leaf_pages_defrag'); -count(stat_value) < 3 -1 -select count(*) from t1 force index (second); -count(*) -6904 -select count(stat_value) = 0 from mysql.innodb_index_stats where table_name like '%t1%' 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 '%t1%' 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 '%t1%' and index_name = 'second' and stat_name in ('n_leaf_pages_defrag'); -count(stat_value) = 0 -1 -SET @@global.innodb_defragment_n_pages = 10; -Warnings: -Warning 1287 '@@innodb_defragment_n_pages' is deprecated and will be removed in a future release -optimize table t1; -Table Op Msg_type Msg_text -test.t1 optimize status OK -# restart -select count(stat_value) > 1 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_pages_freed'); -count(stat_value) > 1 -1 -select count(stat_value) > 1 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_page_split'); -count(stat_value) > 1 -1 -select count(stat_value) > 1 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_leaf_pages_defrag'); -count(stat_value) > 1 -1 -select count(*) from t1 force index (second); -count(*) -6904 -select count(stat_value) = 0 from mysql.innodb_index_stats where table_name like '%t1%' 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 '%t1%' 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 '%t1%' and index_name = 'second' and stat_name in ('n_leaf_pages_defrag'); -count(stat_value) = 0 -1 -DROP PROCEDURE defragment; -DROP TABLE t1; diff --git a/mysql-test/suite/innodb/r/innodb_defragment_fill_factor.result b/mysql-test/suite/innodb/r/innodb_defragment_fill_factor.result deleted file mode 100644 index 8453050a92a..00000000000 --- a/mysql-test/suite/innodb/r/innodb_defragment_fill_factor.result +++ /dev/null @@ -1,118 +0,0 @@ -Testing tables with large records -CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY AUTO_INCREMENT, b VARCHAR(256), c VARCHAR(256), KEY SECOND(a, b,c)) ENGINE=INNODB; -INSERT INTO t1 VALUES (1, REPEAT('A', 256), REPEAT('B', 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; -SET GLOBAL innodb_fast_shutdown = 0; -# restart -optimize table t1; -Table Op Msg_type Msg_text -test.t1 optimize status OK -select count(*) from t1; -count(*) -927 -select count(*) from t1 force index (second); -count(*) -927 -# A few more insertions on the page should not cause a page split. -insert into t1 values (81, REPEAT('A', 256), REPEAT('B', 256)); -insert into t1 values (83, REPEAT('A', 256), REPEAT('B', 256)); -insert into t1 values (87, REPEAT('A', 256), REPEAT('B', 256)); -insert into t1 values (82, REPEAT('A', 256), REPEAT('B', 256)); -insert into t1 values (86, REPEAT('A', 256), REPEAT('B', 256)); -# Insert more rows to cause a page split -insert into t1 values (180, REPEAT('A', 256), REPEAT('B', 256)); -insert into t1 values (181, REPEAT('A', 256), REPEAT('B', 256)); -insert into t1 values (182, REPEAT('A', 256), REPEAT('B', 256)); -insert into t1 values (183, REPEAT('A', 256), REPEAT('B', 256)); -insert into t1 values (184, REPEAT('A', 256), REPEAT('B', 256)); -insert into t1 values (185, REPEAT('A', 256), REPEAT('B', 256)); -insert into t1 values (186, REPEAT('A', 256), REPEAT('B', 256)); -insert into t1 values (187, REPEAT('A', 256), REPEAT('B', 256)); -insert into t1 values (188, REPEAT('A', 256), REPEAT('B', 256)); -insert into t1 values (189, REPEAT('A', 256), REPEAT('B', 256)); -insert into t1 values (190, REPEAT('A', 256), REPEAT('B', 256)); -insert into t1 values (191, REPEAT('A', 256), REPEAT('B', 256)); -insert into t1 values (192, REPEAT('A', 256), REPEAT('B', 256)); -insert into t1 values (193, REPEAT('A', 256), REPEAT('B', 256)); -insert into t1 values (194, REPEAT('A', 256), REPEAT('B', 256)); -insert into t1 values (195, REPEAT('A', 256), REPEAT('B', 256)); -insert into t1 values (196, REPEAT('A', 256), REPEAT('B', 256)); -insert into t1 values (197, REPEAT('A', 256), REPEAT('B', 256)); -insert into t1 values (198, REPEAT('A', 256), REPEAT('B', 256)); -insert into t1 values (199, REPEAT('A', 256), REPEAT('B', 256)); -insert into t1 values (200, REPEAT('A', 256), REPEAT('B', 256)); -insert into t1 values (201, REPEAT('A', 256), REPEAT('B', 256)); -insert into t1 values (202, REPEAT('A', 256), REPEAT('B', 256)); -insert into t1 values (203, REPEAT('A', 256), REPEAT('B', 256)); -insert into t1 values (204, REPEAT('A', 256), REPEAT('B', 256)); -DROP TABLE t1; -Testing table with small records -CREATE TABLE t2 (a INT NOT NULL PRIMARY KEY AUTO_INCREMENT, b VARCHAR(16), c VARCHAR(32), KEY SECOND(a,b,c)) ENGINE=INNODB; -SET GLOBAL innodb_fast_shutdown = 0; -# restart -optimize table t2; -Table Op Msg_type Msg_text -test.t2 optimize status OK -select count(*) from t2; -count(*) -3701 -select count(*) from t2 force index(second); -count(*) -3701 -The page should have room for about 20 insertions -insert into t2 values(1181, REPEAT('A', 16), REPEAT('B',32)); -insert into t2 values(1191, REPEAT('A', 16), REPEAT('B',32)); -insert into t2 values(1182, REPEAT('A', 16), REPEAT('B',32)); -insert into t2 values(1192, REPEAT('A', 16), REPEAT('B',32)); -insert into t2 values(1183, REPEAT('A', 16), REPEAT('B',32)); -insert into t2 values(1193, REPEAT('A', 16), REPEAT('B',32)); -insert into t2 values(1184, REPEAT('A', 16), REPEAT('B',32)); -insert into t2 values(1194, REPEAT('A', 16), REPEAT('B',32)); -insert into t2 values(1185, REPEAT('A', 16), REPEAT('B',32)); -insert into t2 values(1195, REPEAT('A', 16), REPEAT('B',32)); -insert into t2 values(1186, REPEAT('A', 16), REPEAT('B',32)); -insert into t2 values(1196, REPEAT('A', 16), REPEAT('B',32)); -insert into t2 values(1187, REPEAT('A', 16), REPEAT('B',32)); -insert into t2 values(1197, REPEAT('A', 16), REPEAT('B',32)); -insert into t2 values(1188, REPEAT('A', 16), REPEAT('B',32)); -insert into t2 values(1198, REPEAT('A', 16), REPEAT('B',32)); -insert into t2 values(1189, REPEAT('A', 16), REPEAT('B',32)); -insert into t2 values(1199, REPEAT('A', 16), REPEAT('B',32)); -insert into t2 values(1190, REPEAT('A', 16), REPEAT('B',32)); -insert into t2 values(1180, REPEAT('A', 16), REPEAT('B',32)); -# Insert more rows to cause a page split -insert into t2 values (180, REPEAT('A', 16), REPEAT('B', 32)); -insert into t2 values (181, REPEAT('A', 16), REPEAT('B', 32)); -insert into t2 values (182, REPEAT('A', 16), REPEAT('B', 32)); -insert into t2 values (183, REPEAT('A', 16), REPEAT('B', 32)); -insert into t2 values (184, REPEAT('A', 16), REPEAT('B', 32)); -insert into t2 values (185, REPEAT('A', 16), REPEAT('B', 32)); -insert into t2 values (186, REPEAT('A', 16), REPEAT('B', 32)); -insert into t2 values (187, REPEAT('A', 16), REPEAT('B', 32)); -insert into t2 values (188, REPEAT('A', 16), REPEAT('B', 32)); -insert into t2 values (189, REPEAT('A', 16), REPEAT('B', 32)); -insert into t2 values (190, REPEAT('A', 16), REPEAT('B', 32)); -insert into t2 values (191, REPEAT('A', 16), REPEAT('B', 32)); -insert into t2 values (192, REPEAT('A', 16), REPEAT('B', 32)); -insert into t2 values (193, REPEAT('A', 16), REPEAT('B', 32)); -insert into t2 values (194, REPEAT('A', 16), REPEAT('B', 32)); -insert into t2 values (195, REPEAT('A', 16), REPEAT('B', 32)); -insert into t2 values (196, REPEAT('A', 16), REPEAT('B', 32)); -insert into t2 values (197, REPEAT('A', 16), REPEAT('B', 32)); -insert into t2 values (198, REPEAT('A', 16), REPEAT('B', 32)); -insert into t2 values (199, REPEAT('A', 16), REPEAT('B', 32)); -insert into t2 values (200, REPEAT('A', 16), REPEAT('B', 32)); -insert into t2 values (201, REPEAT('A', 16), REPEAT('B', 32)); -insert into t2 values (202, REPEAT('A', 16), REPEAT('B', 32)); -insert into t2 values (203, REPEAT('A', 16), REPEAT('B', 32)); -insert into t2 values (204, REPEAT('A', 16), REPEAT('B', 32)); -DROP TABLE t2; diff --git a/mysql-test/suite/innodb/r/innodb_defragment_small.result b/mysql-test/suite/innodb/r/innodb_defragment_small.result deleted file mode 100644 index 6b80637f3f8..00000000000 --- a/mysql-test/suite/innodb/r/innodb_defragment_small.result +++ /dev/null @@ -1,46 +0,0 @@ -SET @innodb_defragment_orig=@@GLOBAL.innodb_defragment; -SET @innodb_optimize_fulltext_orig=@@GLOBAL.innodb_optimize_fulltext_only; -SET GLOBAL innodb_defragment = 1; -Warnings: -Warning 1287 '@@innodb_defragment' is deprecated and will be removed in a future release -SET GLOBAL innodb_optimize_fulltext_only = 0; -# -# MDEV-12198 innodb_defragment=1 crashes server on -# OPTIMIZE TABLE when FULLTEXT index exists -# -CREATE TABLE t1 (a INT PRIMARY KEY, b VARCHAR(256), -KEY(a, b), FULLTEXT KEY(b)) ENGINE=INNODB; -OPTIMIZE TABLE t1; -Table Op Msg_type Msg_text -test.t1 optimize status OK -INSERT INTO t1 VALUES (100000, REPEAT('A', 256)); -INSERT INTO t1 VALUES (200000, REPEAT('A', 256)); -INSERT INTO t1 VALUES (300000, REPEAT('A', 256)); -INSERT INTO t1 VALUES (400000, REPEAT('A', 256)); -OPTIMIZE TABLE t1; -Table Op Msg_type Msg_text -test.t1 optimize status OK -# -# MDEV-15824 innodb_defragment=ON trumps -# innodb_optimize_fulltext_only=ON in OPTIMIZE TABLE -# -SET GLOBAL innodb_optimize_fulltext_only = 1; -OPTIMIZE TABLE t1; -Table Op Msg_type Msg_text -test.t1 optimize status OK -SET GLOBAL innodb_defragment = 0; -Warnings: -Warning 1287 '@@innodb_defragment' is deprecated and will be removed in a future release -OPTIMIZE TABLE t1; -Table Op Msg_type Msg_text -test.t1 optimize status OK -DROP TABLE t1; -CREATE TABLE t1 (c POINT PRIMARY KEY, SPATIAL INDEX(c)) ENGINE=InnoDB; -OPTIMIZE TABLE t1; -Table Op Msg_type Msg_text -test.t1 optimize status OK -DROP TABLE t1; -SET GLOBAL innodb_defragment = @innodb_defragment_orig; -Warnings: -Warning 1287 '@@innodb_defragment' is deprecated and will be removed in a future release -SET GLOBAL innodb_optimize_fulltext_only = @innodb_optimize_fulltext_orig; diff --git a/mysql-test/suite/innodb/r/innodb_status_variables.result b/mysql-test/suite/innodb/r/innodb_status_variables.result index 2e8272f444b..dcd8ff4d619 100644 --- a/mysql-test/suite/innodb/r/innodb_status_variables.result +++ b/mysql-test/suite/innodb/r/innodb_status_variables.result @@ -81,9 +81,6 @@ INNODB_HAVE_LZMA INNODB_HAVE_BZIP2 INNODB_HAVE_SNAPPY INNODB_HAVE_PUNCH_HOLE -INNODB_DEFRAGMENT_COMPRESSION_FAILURES -INNODB_DEFRAGMENT_FAILURES -INNODB_DEFRAGMENT_COUNT INNODB_INSTANT_ALTER_COLUMN INNODB_ONLINEDDL_ROWLOG_ROWS INNODB_ONLINEDDL_ROWLOG_PCT_USED diff --git a/mysql-test/suite/innodb/r/instant_alter_debug.result b/mysql-test/suite/innodb/r/instant_alter_debug.result index 6efe5d7c734..5e1ec3aec60 100644 --- a/mysql-test/suite/innodb/r/instant_alter_debug.result +++ b/mysql-test/suite/innodb/r/instant_alter_debug.result @@ -277,16 +277,6 @@ SET GLOBAL innodb_limit_optimistic_insert_debug = 2; INSERT INTO t1 VALUES (1),(5),(4),(3),(2); SET GLOBAL innodb_limit_optimistic_insert_debug = @old_limit; ALTER TABLE t1 ADD COLUMN b INT, ALGORITHM=INSTANT; -SET @old_defragment = @@innodb_defragment; -SET GLOBAL innodb_defragment = 1; -Warnings: -Warning 1287 '@@innodb_defragment' is deprecated and will be removed in a future release -OPTIMIZE TABLE t1; -Table Op Msg_type Msg_text -test.t1 optimize status OK -SET GLOBAL innodb_defragment = @old_defragment; -Warnings: -Warning 1287 '@@innodb_defragment' is deprecated and will be removed in a future release ALTER TABLE t1 ADD vb INT AS (b) VIRTUAL; CHECK TABLE t1; Table Op Msg_type Msg_text diff --git a/mysql-test/suite/innodb/t/defrag_mdl-9155.test b/mysql-test/suite/innodb/t/defrag_mdl-9155.test deleted file mode 100644 index 1e1efb9c4c6..00000000000 --- a/mysql-test/suite/innodb/t/defrag_mdl-9155.test +++ /dev/null @@ -1,22 +0,0 @@ -# -# MDEV-9155 Enabling Defragmenting in 10.1.8 still causes OPTIMIZE TABLE to take metadatalocks -# -source include/have_innodb.inc; -source include/have_sequence.inc; - -set global innodb_defragment=1; -create table t1 (a int not null primary key auto_increment, b varchar(256), key second(a, b)) engine=innodb; -insert t1 select null, repeat('a', 256) from seq_1_to_100; -select count(*) from t1; - -connect (con1,localhost,root); -start transaction; -select count(*) from t1; - -connection default; -optimize table t1; - -connection con1; -drop table t1; - -set global innodb_defragment=default; diff --git a/mysql-test/suite/innodb/t/innodb_defrag_binlog.opt b/mysql-test/suite/innodb/t/innodb_defrag_binlog.opt deleted file mode 100644 index 8a432b8c76e..00000000000 --- a/mysql-test/suite/innodb/t/innodb_defrag_binlog.opt +++ /dev/null @@ -1,5 +0,0 @@ ---loose-innodb-buffer-pool-stats ---loose-innodb-buffer-page ---loose-innodb-buffer-page-lru ---binlog-format=row ---innodb-defragment=1
\ No newline at end of file diff --git a/mysql-test/suite/innodb/t/innodb_defrag_binlog.test b/mysql-test/suite/innodb/t/innodb_defrag_binlog.test deleted file mode 100644 index db6849f8477..00000000000 --- a/mysql-test/suite/innodb/t/innodb_defrag_binlog.test +++ /dev/null @@ -1,21 +0,0 @@ ---source include/have_innodb.inc ---source include/master-slave.inc ---source include/big_test.inc ---source include/not_valgrind.inc ---source include/not_embedded.inc - ---disable_warnings -drop table if exists t1; ---enable_warnings - -create table t1(a int not null primary key auto_increment, b varchar(256), key second(b)) engine=innodb; - -insert into t1 values (1, REPEAT("a", 256)); -insert into t1 values (2, REPEAT("a", 256)); -optimize table t1; - -drop table t1; - ---source include/show_binlog_events.inc - ---source include/rpl_end.inc diff --git a/mysql-test/suite/innodb/t/innodb_defrag_concurrent.opt b/mysql-test/suite/innodb/t/innodb_defrag_concurrent.opt deleted file mode 100644 index 3242c6cf2ab..00000000000 --- a/mysql-test/suite/innodb/t/innodb_defrag_concurrent.opt +++ /dev/null @@ -1,5 +0,0 @@ ---loose-innodb-buffer-pool-stats ---loose-innodb-buffer-page ---loose-innodb-buffer-page-lru ---innodb-log-buffer-size=3m ---innodb-defragment=1
\ No newline at end of file diff --git a/mysql-test/suite/innodb/t/innodb_defrag_concurrent.test b/mysql-test/suite/innodb/t/innodb_defrag_concurrent.test deleted file mode 100644 index 1e4e14eb7c6..00000000000 --- a/mysql-test/suite/innodb/t/innodb_defrag_concurrent.test +++ /dev/null @@ -1,143 +0,0 @@ ---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 - -SET @n_pages= @@GLOBAL.innodb_defragment_n_pages; -SET @accuracy= @@GLOBAL.innodb_defragment_stats_accuracy; -SET @sp= @@GLOBAL.innodb_stats_persistent; - -SET GLOBAL innodb_stats_persistent = 0; -set global innodb_defragment_stats_accuracy = 80; - -# Create table. -# -# TODO: Currently we do not defragment spatial indexes, -# because doing it properly would require -# appropriate logic around the SSN (split -# sequence number). -# -# Also do not defragment auxiliary tables related to FULLTEXT INDEX. -# -# Both types added to this test to make sure they do not cause -# problems. -# -CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY AUTO_INCREMENT, -b VARCHAR(256), -c INT, -g GEOMETRY NOT NULL, -t VARCHAR(256), -KEY second(a, b), -KEY third(c), -SPATIAL gk(g), -FULLTEXT INDEX fti(t)) ENGINE=INNODB; - -connect (con1,localhost,root,,test,$MASTER_MYPORT,$MASTER_MYSOCK); -connect (con2,localhost,root,,test,$MASTER_MYPORT,$MASTER_MYSOCK); -connect (con3,localhost,root,,test,$MASTER_MYPORT,$MASTER_MYSOCK); -connect (con4,localhost,root,,test,$MASTER_MYPORT,$MASTER_MYSOCK); - -connection default; - -SET @@global.innodb_defragment_n_pages = 20; - -CREATE TEMPORARY TABLE tt (a INT, KEY(a)) ENGINE=InnoDB ROW_FORMAT=REDUNDANT; -INSERT INTO tt SELECT 0 FROM seq_1_to_180; -INSERT INTO tt SELECT 5 FROM seq_1_to_160; -INSERT INTO tt SELECT 1 FROM seq_1_to_1000; -OPTIMIZE TABLE tt; - -let $data_size = 20000; -let $delete_size = 2000; - -# Populate table. -let $i = $data_size; ---disable_query_log -while ($i) -{ - eval - INSERT INTO t1 VALUES ($data_size + 1 - $i, REPEAT('A', 256), $i, Point($i,$i), 'This is a test message.'); - dec $i; -} ---enable_query_log - -select count(*) from t1; -select count(*) from t1 force index (second); -select count(*) from t1 force index (third); - -# Delete some data ---disable_query_log -let $size = $delete_size; -while ($size) -{ - let $j = 100 * $size; - eval delete from t1 where a between $j - 20 and $j; - dec $size; -} ---enable_query_log - -select count(*) from t1; -select count(*) from t1 force index (second); -select count(*) from t1 force index (third); - -# Above delete will free some pages and insert causes page split and these could cause defrag -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_page_split'); -select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_leaf_pages_defrag'); - -connection con1; ---send optimize table t1; - -connection default; ---send INSERT INTO t1 VALUES (400000, REPEAT('A', 256),300000, Point(1,1),'More like a test but different.'); - -connection con2; ---send INSERT INTO t1 VALUES (500000, REPEAT('A', 256),400000, Point(1,1),'Totally different text book.'); - -connection con3; ---send DELETE FROM t1 where a between 1 and 100; - -connection con4; ---send UPDATE t1 SET c = c + 1 where c between 2000 and 8000; - -connection con1; ---disable_result_log ---reap ---enable_result_log - -connection con2; ---reap - -connection con3; ---reap - -connection con4; ---reap - -connection default; ---reap - -disconnect con1; -disconnect con2; -disconnect con3; -disconnect con4; - -optimize table t1; -check table t1 extended; - -select count(*) from t1; -select count(*) from t1 force index (second); -select count(*) from t1 force index (third); - -# Now pages are freed -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_page_split'); -select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_leaf_pages_defrag'); - -drop table t1; - -# reset system -SET GLOBAL innodb_defragment_n_pages = @n_pages; -SET GLOBAL innodb_defragment_stats_accuracy = @accuracy; -SET GLOBAL innodb_stats_persistent = @sp; diff --git a/mysql-test/suite/innodb/t/innodb_defrag_stats.opt b/mysql-test/suite/innodb/t/innodb_defrag_stats.opt deleted file mode 100644 index d3525162f03..00000000000 --- a/mysql-test/suite/innodb/t/innodb_defrag_stats.opt +++ /dev/null @@ -1 +0,0 @@ ---innodb-defragment=1 diff --git a/mysql-test/suite/innodb/t/innodb_defrag_stats.test b/mysql-test/suite/innodb/t/innodb_defrag_stats.test deleted file mode 100644 index ef79d8d6ade..00000000000 --- a/mysql-test/suite/innodb/t/innodb_defrag_stats.test +++ /dev/null @@ -1,88 +0,0 @@ ---source include/have_innodb.inc ---source include/not_valgrind.inc ---source include/not_embedded.inc ---source include/have_sequence.inc - -SET GLOBAL innodb_defragment_stats_accuracy = 20; - -DELETE FROM mysql.innodb_index_stats; - ---echo # Create table. -CREATE TABLE t1 (a INT PRIMARY KEY AUTO_INCREMENT, b VARCHAR(256), - KEY SECOND(a, b)) ENGINE=INNODB STATS_PERSISTENT=0; - -INSERT INTO t1 SELECT 100*FLOOR(seq/70)+seq%70, REPEAT('A', 256) -FROM seq_1_to_1024; - ---echo # Not enough page splits to trigger persistent stats write yet. -SELECT * FROM mysql.innodb_index_stats; - -INSERT INTO t1 SELECT 100*FLOOR(seq/70)+seq%70, REPEAT('A', 256) -FROM seq_1025_to_1433; - -BEGIN; -let $num_delete = 20; -while ($num_delete) -{ - eval INSERT INTO t1 SELECT 100*$num_delete+seq, REPEAT('A', 256) - FROM seq_70_to_99; - dec $num_delete; -} -ROLLBACK; - -SELECT @@GLOBAL.innodb_force_recovery<2 "have background defragmentation"; - -# Wait for defrag_pool to be processed. - -let $wait_timeout=30; -let $wait_condition = SELECT COUNT(*)>0 FROM mysql.innodb_index_stats; ---source include/wait_condition.inc - ---sorted_result -SELECT table_name, index_name, stat_name FROM mysql.innodb_index_stats; - -optimize table t1; - ---sorted_result -SELECT table_name, index_name, stat_name FROM mysql.innodb_index_stats; - -set global innodb_defragment_stats_accuracy = 40; - -INSERT INTO t1 (b) SELECT b from t1; - ---sorted_result -SELECT table_name, index_name, stat_name FROM mysql.innodb_index_stats; - -INSERT INTO t1 (b) SELECT b from t1; - ---sorted_result -SELECT stat_name FROM mysql.innodb_index_stats WHERE table_name='t1'; - ---echo # Table rename should cause stats rename. -rename table t1 to t2; - ---sorted_result -SELECT table_name, index_name, stat_name FROM mysql.innodb_index_stats; - -drop index SECOND on t2; - ---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 - ---sorted_result -SELECT table_name, index_name, stat_name FROM mysql.innodb_index_stats; - ---echo # Clean up -# Starting with 10.6, DROP TABLE will not touch persistent statistics -# (not defragmentation statistics either) if the table has none! -ALTER TABLE t2 STATS_PERSISTENT=1; -DROP TABLE t2; - -SELECT * FROM mysql.innodb_index_stats; diff --git a/mysql-test/suite/innodb/t/innodb_defrag_stats_many_tables.opt b/mysql-test/suite/innodb/t/innodb_defrag_stats_many_tables.opt deleted file mode 100644 index d3525162f03..00000000000 --- a/mysql-test/suite/innodb/t/innodb_defrag_stats_many_tables.opt +++ /dev/null @@ -1 +0,0 @@ ---innodb-defragment=1 diff --git a/mysql-test/suite/innodb/t/innodb_defrag_stats_many_tables.test b/mysql-test/suite/innodb/t/innodb_defrag_stats_many_tables.test deleted file mode 100644 index a0f43fa6841..00000000000 --- a/mysql-test/suite/innodb/t/innodb_defrag_stats_many_tables.test +++ /dev/null @@ -1,77 +0,0 @@ ---source include/have_innodb.inc ---source include/big_test.inc ---source include/not_valgrind.inc ---source include/not_embedded.inc - ---disable_warnings -DROP TABLE if exists t1; ---enable_warnings - -let $num_tables = 505; - -SET @start_table_definition_cache = @@global.table_definition_cache; -SET @@global.table_definition_cache = 400; - -SET @start_flush_log_at_trx_commit = @@global.innodb_flush_log_at_trx_commit; -SET @@global.innodb_flush_log_at_trx_commit=2; - -# set stats accuracy to be pretty high so stats sync is easily triggered. -SET @start_innodb_defragment_stats_accuracy = @@global.innodb_defragment_stats_accuracy; -SET @@global.innodb_defragment_stats_accuracy = 80; - -# 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 (b) SELECT b from t1; - -select stat_value > 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name = 'n_page_split'; - -# Create many tables to over flow the table definition cache - ---echo Create $num_tables table to overflow the table cache. ---disable_query_log -let $count = $num_tables; -while ($count) -{ - EVAL CREATE TABLE t_$count (a INT NOT NULL PRIMARY KEY AUTO_INCREMENT) ENGINE=INNODB; - EVAL INSERT INTO t_$count VALUES (1), (2); - dec $count; -} ---enable_query_log ---echo Sleep for a while to make sure t1 is evicted. -select sleep(15); - ---echo Reload t1 to get defrag stats from persistent storage -INSERT INTO t1 (b) SELECT b from t1; - ---echo make sure the stats thread will wake up and do the write even if there's a race condition between set and reset. -select sleep(15); - -select stat_value > 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name = 'n_page_split'; - - -# Clean up -SET @@global.innodb_defragment_stats_accuracy = @start_innodb_defragment_stats_accuracy; -SET @@global.table_definition_cache = @start_table_definition_cache; ---disable_query_log -let $count = $num_tables; -while ($count) -{ - EVAL DROP TABLE t_$count; - dec $count; -} -set @@global.innodb_flush_log_at_trx_commit = @start_flush_log_at_trx_commit; ---enable_query_log -DROP TABLE t1; diff --git a/mysql-test/suite/innodb/t/innodb_defragment.opt b/mysql-test/suite/innodb/t/innodb_defragment.opt deleted file mode 100644 index 12b046be786..00000000000 --- a/mysql-test/suite/innodb/t/innodb_defragment.opt +++ /dev/null @@ -1,4 +0,0 @@ ---loose-innodb-buffer-pool-stats ---loose-innodb-buffer-page ---loose-innodb-buffer-page-lru ---innodb-defragment=1 diff --git a/mysql-test/suite/innodb/t/innodb_defragment.test b/mysql-test/suite/innodb/t/innodb_defragment.test deleted file mode 100644 index 51ef78377cb..00000000000 --- a/mysql-test/suite/innodb/t/innodb_defragment.test +++ /dev/null @@ -1,157 +0,0 @@ ---source include/have_innodb.inc ---source include/big_test.inc ---source include/not_embedded.inc -# Valgrind is to slow for this test ---source include/not_valgrind.inc - -set global innodb_defragment_stats_accuracy = 80; - -# Create table. -CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY AUTO_INCREMENT, b VARCHAR(256), KEY SECOND(a, b)) ENGINE=INNODB; - -## Test-1 defragment an empty table -optimize table t1; - -## Test-2 defragment a single page table -INSERT INTO t1 VALUES (100000, REPEAT('A', 256)); -INSERT INTO t1 VALUES (200000, REPEAT('A', 256)); -INSERT INTO t1 VALUES (300000, REPEAT('A', 256)); -INSERT INTO t1 VALUES (400000, REPEAT('A', 256)); - -optimize table t1; - -## Test-3 defragment (somewhat) in parallel with delete queries -let $data_size = 10000; -let $delete_size = 100; - -delimiter //; -create procedure defragment() -begin - set @i = 0; - repeat - set @i = @i + 1; - optimize table t1; - until @i = 3 end repeat; -end // -delimiter ;// - - -# Populate table. -let $i = $data_size; ---disable_query_log -BEGIN; -while ($i) -{ - eval - INSERT INTO t1 VALUES ($data_size + 1 - $i, REPEAT('A', 256)); - dec $i; -} -COMMIT; ---enable_query_log - -select count(stat_value) from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_pages_freed'); -select count(stat_value) from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_page_split'); -select count(stat_value) from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_leaf_pages_defrag'); - -select count(*) from t1; - -connect (con1,localhost,root,,test,$MASTER_MYPORT,$MASTER_MYSOCK); - -connection con1; ---send call defragment() - -connection default; - ---disable_query_log -let $size = $delete_size; -while ($size) -{ - let $j = 100 * $size; - eval delete from t1 where a between $j - 20 and $j; - dec $size; -} ---enable_query_log - -connection con1; ---disable_result_log ---reap ---enable_result_log - -connection default; -disconnect con1; - -optimize table t1; - ---source include/restart_mysqld.inc -select count(*) from t1; - -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_page_split'); -select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_leaf_pages_defrag'); - -select count(*) from t1 force index (second); - -select count(stat_value) = 0 from mysql.innodb_index_stats where table_name like '%t1%' 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 '%t1%' 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 '%t1%' and index_name = 'second' and stat_name in ('n_leaf_pages_defrag'); - -## Test-4 defragment with larger n_pages - -# delete some more records ---disable_query_log -let $size = $delete_size; -while ($size) -{ - let $j = 100 * $size; - eval delete from t1 where a between $j - 30 and $j - 20; - dec $size; -} ---enable_query_log - -SET @@global.innodb_defragment_n_pages = 3; - -# This will not reduce number of pages by a lot -optimize table t1; - ---source include/restart_mysqld.inc - -select count(stat_value) < 3 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_pages_freed'); -select count(stat_value) < 3 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_page_split'); -select count(stat_value) < 3 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_leaf_pages_defrag'); - -select count(*) from t1; - -# We didn't create large wholes with the previous deletion, so if innodb_defragment_n_pages = 3, we won't be able to free up many pages. - -select count(stat_value) < 3 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_pages_freed'); -select count(stat_value) < 3 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_page_split'); -select count(stat_value) < 3 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_leaf_pages_defrag'); - - -select count(*) from t1 force index (second); - -# Same holds for secondary index, not many pages are released. -select count(stat_value) = 0 from mysql.innodb_index_stats where table_name like '%t1%' 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 '%t1%' 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 '%t1%' and index_name = 'second' and stat_name in ('n_leaf_pages_defrag'); - -SET @@global.innodb_defragment_n_pages = 10; - -optimize table t1; - ---source include/restart_mysqld.inc - -select count(stat_value) > 1 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_pages_freed'); -select count(stat_value) > 1 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_page_split'); -select count(stat_value) > 1 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_leaf_pages_defrag'); - - -select count(*) from t1 force index (second); - -# Same holds for secondary index, not many pages are released. -select count(stat_value) = 0 from mysql.innodb_index_stats where table_name like '%t1%' 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 '%t1%' 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 '%t1%' and index_name = 'second' and stat_name in ('n_leaf_pages_defrag'); - -DROP PROCEDURE defragment; -DROP TABLE t1; diff --git a/mysql-test/suite/innodb/t/innodb_defragment_fill_factor.opt b/mysql-test/suite/innodb/t/innodb_defragment_fill_factor.opt deleted file mode 100644 index 6426bac41a0..00000000000 --- a/mysql-test/suite/innodb/t/innodb_defragment_fill_factor.opt +++ /dev/null @@ -1,4 +0,0 @@ ---loose-innodb-buffer-pool-stats ---loose-innodb-buffer-page ---loose-innodb-buffer-page-lru ---innodb-defragment=1
\ No newline at end of file diff --git a/mysql-test/suite/innodb/t/innodb_defragment_fill_factor.test b/mysql-test/suite/innodb/t/innodb_defragment_fill_factor.test deleted file mode 100644 index 3a5897b9911..00000000000 --- a/mysql-test/suite/innodb/t/innodb_defragment_fill_factor.test +++ /dev/null @@ -1,203 +0,0 @@ ---source include/have_innodb.inc ---source include/big_test.inc ---source include/not_valgrind.inc ---source include/not_embedded.inc ---source include/have_innodb_16k.inc - ---echo Testing tables with large records -# Create table. -CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY AUTO_INCREMENT, b VARCHAR(256), c VARCHAR(256), KEY SECOND(a, b,c)) ENGINE=INNODB; - -# Populate table. -INSERT INTO t1 VALUES (1, REPEAT('A', 256), REPEAT('B', 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; - ---disable_query_log -let $size = 10; -while ($size) -{ - let $j = 100 * $size; - eval delete from t1 where a between $j - 20 and $j + 5; - dec $size; -} ---enable_query_log - -SET GLOBAL innodb_fast_shutdown = 0; ---source include/restart_mysqld.inc -optimize table t1; - -select count(*) from t1; - ---let $primary_before = query_get_value(select count(*) as Value from information_schema.innodb_buffer_page where table_name like '%t1%' and index_name = 'PRIMARY', Value, 1) - -select count(*) from t1 force index (second); - ---let $second_before = query_get_value(select count(*) as Value from information_schema.innodb_buffer_page where table_name like '%t1%' and index_name = 'second', Value, 1) - ---echo # A few more insertions on the page should not cause a page split. -insert into t1 values (81, REPEAT('A', 256), REPEAT('B', 256)); -insert into t1 values (83, REPEAT('A', 256), REPEAT('B', 256)); -insert into t1 values (87, REPEAT('A', 256), REPEAT('B', 256)); -insert into t1 values (82, REPEAT('A', 256), REPEAT('B', 256)); -insert into t1 values (86, REPEAT('A', 256), REPEAT('B', 256)); ---let $primary_after = query_get_value(select count(*) as Value from information_schema.innodb_buffer_page where table_name like '%t1%' and index_name = 'PRIMARY', Value, 1) ---let $second_after = query_get_value(select count(*) as Value from information_schema.innodb_buffer_page where table_name like '%t1%' and index_name = 'second', Value, 1) - -if ($primary_before != $primary_after) { - --echo Insertion caused page split on primary, which should be avoided by innodb_defragment_fill_factor. -} - -if ($second_before != $second_after) { - --echo Insertion caused page split on second, which should be avoided by innodb_defragment_fill_factor. -} - ---echo # Insert more rows to cause a page split -insert into t1 values (180, REPEAT('A', 256), REPEAT('B', 256)); -insert into t1 values (181, REPEAT('A', 256), REPEAT('B', 256)); -insert into t1 values (182, REPEAT('A', 256), REPEAT('B', 256)); -insert into t1 values (183, REPEAT('A', 256), REPEAT('B', 256)); -insert into t1 values (184, REPEAT('A', 256), REPEAT('B', 256)); -insert into t1 values (185, REPEAT('A', 256), REPEAT('B', 256)); -insert into t1 values (186, REPEAT('A', 256), REPEAT('B', 256)); -insert into t1 values (187, REPEAT('A', 256), REPEAT('B', 256)); -insert into t1 values (188, REPEAT('A', 256), REPEAT('B', 256)); -insert into t1 values (189, REPEAT('A', 256), REPEAT('B', 256)); -insert into t1 values (190, REPEAT('A', 256), REPEAT('B', 256)); -insert into t1 values (191, REPEAT('A', 256), REPEAT('B', 256)); -insert into t1 values (192, REPEAT('A', 256), REPEAT('B', 256)); -insert into t1 values (193, REPEAT('A', 256), REPEAT('B', 256)); -insert into t1 values (194, REPEAT('A', 256), REPEAT('B', 256)); -insert into t1 values (195, REPEAT('A', 256), REPEAT('B', 256)); -insert into t1 values (196, REPEAT('A', 256), REPEAT('B', 256)); -insert into t1 values (197, REPEAT('A', 256), REPEAT('B', 256)); -insert into t1 values (198, REPEAT('A', 256), REPEAT('B', 256)); -insert into t1 values (199, REPEAT('A', 256), REPEAT('B', 256)); -insert into t1 values (200, REPEAT('A', 256), REPEAT('B', 256)); -insert into t1 values (201, REPEAT('A', 256), REPEAT('B', 256)); -insert into t1 values (202, REPEAT('A', 256), REPEAT('B', 256)); -insert into t1 values (203, REPEAT('A', 256), REPEAT('B', 256)); -insert into t1 values (204, REPEAT('A', 256), REPEAT('B', 256)); - ---let $primary_after = query_get_value(select count(*) as Value from information_schema.innodb_buffer_page where table_name like '%t1%' and index_name = 'PRIMARY', Value, 1) - ---let $second_after = query_get_value(select count(*) as Value from information_schema.innodb_buffer_page where table_name like '%t1%' and index_name = 'second', Value, 1) - -if ($primary_before == $primary_after) { - --echo Too little space is reserved on primary index. -} - -if ($second_before == $second_after) { - --echo Too little space is reserved on second index. -} - -DROP TABLE t1; - ---echo Testing table with small records -CREATE TABLE t2 (a INT NOT NULL PRIMARY KEY AUTO_INCREMENT, b VARCHAR(16), c VARCHAR(32), KEY SECOND(a,b,c)) ENGINE=INNODB; -# Populate table. ---disable_query_log -INSERT INTO t2 VALUES (1, REPEAT('A', 16), REPEAT('B', 32)); -INSERT INTO t2 (b) SELECT b from t2; -INSERT INTO t2 (b) SELECT b from t2; -INSERT INTO t2 (b) SELECT b from t2; -INSERT INTO t2 (b) SELECT b from t2; -INSERT INTO t2 (b) SELECT b from t2; -INSERT INTO t2 (b) SELECT b from t2; -INSERT INTO t2 (b) SELECT b from t2; -INSERT INTO t2 (b) SELECT b from t2; -INSERT INTO t2 (b) SELECT b from t2; -INSERT INTO t2 (b) SELECT b from t2; -INSERT INTO t2 (b) SELECT b from t2; -INSERT INTO t2 (b) SELECT b from t2; ---enable_query_log - ---disable_query_log -let $size = 40; -while ($size) -{ - let $j = 100 * $size; - eval delete from t2 where a between $j - 20 and $j; - dec $size; -} ---enable_query_log - -SET GLOBAL innodb_fast_shutdown = 0; ---source include/restart_mysqld.inc -optimize table t2; - -select count(*) from t2; -select count(*) from t2 force index(second); - ---let $second_before = query_get_value(select count(*) as Value from information_schema.innodb_buffer_page where table_name like '%t2%' and index_name = 'second', Value, 1) - ---echo The page should have room for about 20 insertions -insert into t2 values(1181, REPEAT('A', 16), REPEAT('B',32)); -insert into t2 values(1191, REPEAT('A', 16), REPEAT('B',32)); -insert into t2 values(1182, REPEAT('A', 16), REPEAT('B',32)); -insert into t2 values(1192, REPEAT('A', 16), REPEAT('B',32)); -insert into t2 values(1183, REPEAT('A', 16), REPEAT('B',32)); -insert into t2 values(1193, REPEAT('A', 16), REPEAT('B',32)); -insert into t2 values(1184, REPEAT('A', 16), REPEAT('B',32)); -insert into t2 values(1194, REPEAT('A', 16), REPEAT('B',32)); -insert into t2 values(1185, REPEAT('A', 16), REPEAT('B',32)); -insert into t2 values(1195, REPEAT('A', 16), REPEAT('B',32)); -insert into t2 values(1186, REPEAT('A', 16), REPEAT('B',32)); -insert into t2 values(1196, REPEAT('A', 16), REPEAT('B',32)); -insert into t2 values(1187, REPEAT('A', 16), REPEAT('B',32)); -insert into t2 values(1197, REPEAT('A', 16), REPEAT('B',32)); -insert into t2 values(1188, REPEAT('A', 16), REPEAT('B',32)); -insert into t2 values(1198, REPEAT('A', 16), REPEAT('B',32)); -insert into t2 values(1189, REPEAT('A', 16), REPEAT('B',32)); -insert into t2 values(1199, REPEAT('A', 16), REPEAT('B',32)); -insert into t2 values(1190, REPEAT('A', 16), REPEAT('B',32)); -insert into t2 values(1180, REPEAT('A', 16), REPEAT('B',32)); - ---let $second_after = query_get_value(select count(*) as Value from information_schema.innodb_buffer_page where table_name like '%t2%' and index_name = 'second', Value, 1) - -if ($second_before != $second_after) { - --echo Insertion caused page split on second, which should be avoided by innodb_defragment_fill_factor. -} - ---echo # Insert more rows to cause a page split -insert into t2 values (180, REPEAT('A', 16), REPEAT('B', 32)); -insert into t2 values (181, REPEAT('A', 16), REPEAT('B', 32)); -insert into t2 values (182, REPEAT('A', 16), REPEAT('B', 32)); -insert into t2 values (183, REPEAT('A', 16), REPEAT('B', 32)); -insert into t2 values (184, REPEAT('A', 16), REPEAT('B', 32)); -insert into t2 values (185, REPEAT('A', 16), REPEAT('B', 32)); -insert into t2 values (186, REPEAT('A', 16), REPEAT('B', 32)); -insert into t2 values (187, REPEAT('A', 16), REPEAT('B', 32)); -insert into t2 values (188, REPEAT('A', 16), REPEAT('B', 32)); -insert into t2 values (189, REPEAT('A', 16), REPEAT('B', 32)); -insert into t2 values (190, REPEAT('A', 16), REPEAT('B', 32)); -insert into t2 values (191, REPEAT('A', 16), REPEAT('B', 32)); -insert into t2 values (192, REPEAT('A', 16), REPEAT('B', 32)); -insert into t2 values (193, REPEAT('A', 16), REPEAT('B', 32)); -insert into t2 values (194, REPEAT('A', 16), REPEAT('B', 32)); -insert into t2 values (195, REPEAT('A', 16), REPEAT('B', 32)); -insert into t2 values (196, REPEAT('A', 16), REPEAT('B', 32)); -insert into t2 values (197, REPEAT('A', 16), REPEAT('B', 32)); -insert into t2 values (198, REPEAT('A', 16), REPEAT('B', 32)); -insert into t2 values (199, REPEAT('A', 16), REPEAT('B', 32)); -insert into t2 values (200, REPEAT('A', 16), REPEAT('B', 32)); -insert into t2 values (201, REPEAT('A', 16), REPEAT('B', 32)); -insert into t2 values (202, REPEAT('A', 16), REPEAT('B', 32)); -insert into t2 values (203, REPEAT('A', 16), REPEAT('B', 32)); -insert into t2 values (204, REPEAT('A', 16), REPEAT('B', 32)); - ---let $second_after = query_get_value(select count(*) as Value from information_schema.innodb_buffer_page where table_name like '%t2%' and index_name = 'second', Value, 1) - -if ($second_before == $second_after) { - --echo Too little space is reserved on second index. -} - -DROP TABLE t2; diff --git a/mysql-test/suite/innodb/t/innodb_defragment_small.test b/mysql-test/suite/innodb/t/innodb_defragment_small.test deleted file mode 100644 index 6259dac448e..00000000000 --- a/mysql-test/suite/innodb/t/innodb_defragment_small.test +++ /dev/null @@ -1,41 +0,0 @@ ---source include/have_innodb.inc - -SET @innodb_defragment_orig=@@GLOBAL.innodb_defragment; -SET @innodb_optimize_fulltext_orig=@@GLOBAL.innodb_optimize_fulltext_only; -SET GLOBAL innodb_defragment = 1; -SET GLOBAL innodb_optimize_fulltext_only = 0; - ---echo # ---echo # MDEV-12198 innodb_defragment=1 crashes server on ---echo # OPTIMIZE TABLE when FULLTEXT index exists ---echo # - -CREATE TABLE t1 (a INT PRIMARY KEY, b VARCHAR(256), - KEY(a, b), FULLTEXT KEY(b)) ENGINE=INNODB; -OPTIMIZE TABLE t1; - -INSERT INTO t1 VALUES (100000, REPEAT('A', 256)); -INSERT INTO t1 VALUES (200000, REPEAT('A', 256)); -INSERT INTO t1 VALUES (300000, REPEAT('A', 256)); -INSERT INTO t1 VALUES (400000, REPEAT('A', 256)); - -OPTIMIZE TABLE t1; - ---echo # ---echo # MDEV-15824 innodb_defragment=ON trumps ---echo # innodb_optimize_fulltext_only=ON in OPTIMIZE TABLE ---echo # - -SET GLOBAL innodb_optimize_fulltext_only = 1; -OPTIMIZE TABLE t1; -SET GLOBAL innodb_defragment = 0; -OPTIMIZE TABLE t1; - -DROP TABLE t1; - -CREATE TABLE t1 (c POINT PRIMARY KEY, SPATIAL INDEX(c)) ENGINE=InnoDB; -OPTIMIZE TABLE t1; -DROP TABLE t1; - -SET GLOBAL innodb_defragment = @innodb_defragment_orig; -SET GLOBAL innodb_optimize_fulltext_only = @innodb_optimize_fulltext_orig; diff --git a/mysql-test/suite/innodb/t/instant_alter_debug.test b/mysql-test/suite/innodb/t/instant_alter_debug.test index 64f94a78f2e..faa9bdbcf1c 100644 --- a/mysql-test/suite/innodb/t/instant_alter_debug.test +++ b/mysql-test/suite/innodb/t/instant_alter_debug.test @@ -325,11 +325,6 @@ SET GLOBAL innodb_limit_optimistic_insert_debug = @old_limit; ALTER TABLE t1 ADD COLUMN b INT, ALGORITHM=INSTANT; -SET @old_defragment = @@innodb_defragment; -SET GLOBAL innodb_defragment = 1; -OPTIMIZE TABLE t1; -SET GLOBAL innodb_defragment = @old_defragment; - # Exploit MDEV-17468 to force the table definition to be reloaded ALTER TABLE t1 ADD vb INT AS (b) VIRTUAL; CHECK TABLE t1; diff --git a/mysql-test/suite/sys_vars/r/innodb_defragment_basic.result b/mysql-test/suite/sys_vars/r/innodb_defragment_basic.result deleted file mode 100644 index d9226fee99f..00000000000 --- a/mysql-test/suite/sys_vars/r/innodb_defragment_basic.result +++ /dev/null @@ -1,24 +0,0 @@ -SET @orig = @@global.innodb_defragment; -SELECT @orig; -@orig -0 -SET GLOBAL innodb_defragment = OFF; -Warnings: -Warning 1287 '@@innodb_defragment' is deprecated and will be removed in a future release -SELECT @@global.innodb_defragment; -@@global.innodb_defragment -0 -SET GLOBAL innodb_defragment = ON; -Warnings: -Warning 1287 '@@innodb_defragment' is deprecated and will be removed in a future release -SELECT @@global.innodb_defragment; -@@global.innodb_defragment -1 -SET GLOBAL innodb_defragment = 100; -ERROR 42000: Variable 'innodb_defragment' can't be set to the value of '100' -SELECT @@global.innodb_defragment; -@@global.innodb_defragment -1 -SET GLOBAL innodb_defragment = @orig; -Warnings: -Warning 1287 '@@innodb_defragment' is deprecated and will be removed in a future release diff --git a/mysql-test/suite/sys_vars/r/innodb_defragment_fill_factor_basic.result b/mysql-test/suite/sys_vars/r/innodb_defragment_fill_factor_basic.result deleted file mode 100644 index 04db2580f15..00000000000 --- a/mysql-test/suite/sys_vars/r/innodb_defragment_fill_factor_basic.result +++ /dev/null @@ -1,47 +0,0 @@ -SET @start_innodb_defragment_fill_factor = @@global.innodb_defragment_fill_factor; -SELECT @start_innodb_defragment_fill_factor; -@start_innodb_defragment_fill_factor -0.9 -SELECT COUNT(@@global.innodb_defragment_fill_factor); -COUNT(@@global.innodb_defragment_fill_factor) -1 -SET @@global.innodb_defragment_fill_factor = 0.77777777777777; -Warnings: -Warning 1287 '@@innodb_defragment_fill_factor' is deprecated and will be removed in a future release -SELECT @@global.innodb_defragment_fill_factor; -@@global.innodb_defragment_fill_factor -0.777778 -SET @@global.innodb_defragment_fill_factor = 1; -Warnings: -Warning 1287 '@@innodb_defragment_fill_factor' is deprecated and will be removed in a future release -SELECT @@global.innodb_defragment_fill_factor; -@@global.innodb_defragment_fill_factor -1.000000 -SET @@global.innodb_defragment_fill_factor = 0.7; -Warnings: -Warning 1287 '@@innodb_defragment_fill_factor' is deprecated and will be removed in a future release -SELECT @@global.innodb_defragment_fill_factor; -@@global.innodb_defragment_fill_factor -0.700000 -SET @@global.innodb_defragment_fill_factor = -1; -Warnings: -Warning 1287 '@@innodb_defragment_fill_factor' is deprecated and will be removed in a future release -Warning 1292 Truncated incorrect innodb_defragment_fill_factor value: '-1' -SELECT @@global.innodb_defragment_fill_factor; -@@global.innodb_defragment_fill_factor -0.700000 -SET @@global.innodb_defragment_fill_factor = 2; -Warnings: -Warning 1287 '@@innodb_defragment_fill_factor' is deprecated and will be removed in a future release -Warning 1292 Truncated incorrect innodb_defragment_fill_factor value: '2' -SELECT @@global.innodb_defragment_fill_factor; -@@global.innodb_defragment_fill_factor -1.000000 -SET @@global.innodb_defragment_fill_factor = "abc"; -ERROR 42000: Incorrect argument type to variable 'innodb_defragment_fill_factor' -SELECT @@global.innodb_defragment_fill_factor; -@@global.innodb_defragment_fill_factor -1.000000 -SET @@global.innodb_defragment_fill_factor = @start_innodb_defragment_fill_factor; -Warnings: -Warning 1287 '@@innodb_defragment_fill_factor' is deprecated and will be removed in a future release diff --git a/mysql-test/suite/sys_vars/r/innodb_defragment_fill_factor_n_recs_basic.result b/mysql-test/suite/sys_vars/r/innodb_defragment_fill_factor_n_recs_basic.result deleted file mode 100644 index 12bec5204f6..00000000000 --- a/mysql-test/suite/sys_vars/r/innodb_defragment_fill_factor_n_recs_basic.result +++ /dev/null @@ -1,52 +0,0 @@ -SET @start_innodb_defragment_fill_factor_n_recs = @@global.innodb_defragment_fill_factor_n_recs; -SELECT @start_innodb_defragment_fill_factor_n_recs; -@start_innodb_defragment_fill_factor_n_recs -20 -SELECT COUNT(@@global.innodb_defragment_fill_factor_n_recs); -COUNT(@@global.innodb_defragment_fill_factor_n_recs) -1 -SET @@global.innodb_defragment_fill_factor_n_recs = 50; -Warnings: -Warning 1287 '@@innodb_defragment_fill_factor_n_recs' is deprecated and will be removed in a future release -SELECT @@global.innodb_defragment_fill_factor_n_recs; -@@global.innodb_defragment_fill_factor_n_recs -50 -SET @@global.innodb_defragment_fill_factor_n_recs = 100; -Warnings: -Warning 1287 '@@innodb_defragment_fill_factor_n_recs' is deprecated and will be removed in a future release -SELECT @@global.innodb_defragment_fill_factor_n_recs; -@@global.innodb_defragment_fill_factor_n_recs -100 -SET @@global.innodb_defragment_fill_factor_n_recs = 1; -Warnings: -Warning 1287 '@@innodb_defragment_fill_factor_n_recs' is deprecated and will be removed in a future release -SELECT @@global.innodb_defragment_fill_factor_n_recs; -@@global.innodb_defragment_fill_factor_n_recs -1 -SET @@global.innodb_defragment_fill_factor_n_recs = -1; -Warnings: -Warning 1287 '@@innodb_defragment_fill_factor_n_recs' is deprecated and will be removed in a future release -Warning 1292 Truncated incorrect innodb_defragment_fill_factor... value: '-1' -SELECT @@global.innodb_defragment_fill_factor_n_recs; -@@global.innodb_defragment_fill_factor_n_recs -1 -SET @@global.innodb_defragment_fill_factor_n_recs = 10000; -Warnings: -Warning 1287 '@@innodb_defragment_fill_factor_n_recs' is deprecated and will be removed in a future release -Warning 1292 Truncated incorrect innodb_defragment_fill_factor... value: '10000' -SELECT @@global.innodb_defragment_fill_factor_n_recs; -@@global.innodb_defragment_fill_factor_n_recs -100 -SET @@global.innodb_defragment_fill_factor_n_recs = 10.5; -ERROR 42000: Incorrect argument type to variable 'innodb_defragment_fill_factor_n_recs' -SELECT @@global.innodb_defragment_fill_factor_n_recs; -@@global.innodb_defragment_fill_factor_n_recs -100 -SET @@global.innodb_defragment_fill_factor_n_recs = "abc"; -ERROR 42000: Incorrect argument type to variable 'innodb_defragment_fill_factor_n_recs' -SELECT @@global.innodb_defragment_fill_factor_n_recs; -@@global.innodb_defragment_fill_factor_n_recs -100 -SET @@global.innodb_defragment_fill_factor_n_recs = @start_innodb_defragment_fill_factor_n_recs; -Warnings: -Warning 1287 '@@innodb_defragment_fill_factor_n_recs' is deprecated and will be removed in a future release diff --git a/mysql-test/suite/sys_vars/r/innodb_defragment_frequency_basic.result b/mysql-test/suite/sys_vars/r/innodb_defragment_frequency_basic.result deleted file mode 100644 index b9f76d60dac..00000000000 --- a/mysql-test/suite/sys_vars/r/innodb_defragment_frequency_basic.result +++ /dev/null @@ -1,52 +0,0 @@ -SET @start_innodb_defragment_frequency = @@global.innodb_defragment_frequency; -SELECT @start_innodb_defragment_frequency; -@start_innodb_defragment_frequency -40 -SELECT COUNT(@@global.innodb_defragment_frequency); -COUNT(@@global.innodb_defragment_frequency) -1 -SET @@global.innodb_defragment_frequency = 200; -Warnings: -Warning 1287 '@@innodb_defragment_frequency' is deprecated and will be removed in a future release -SELECT @@global.innodb_defragment_frequency; -@@global.innodb_defragment_frequency -200 -SET @@global.innodb_defragment_frequency = 1; -Warnings: -Warning 1287 '@@innodb_defragment_frequency' is deprecated and will be removed in a future release -SELECT @@global.innodb_defragment_frequency; -@@global.innodb_defragment_frequency -1 -SET @@global.innodb_defragment_frequency = 1000; -Warnings: -Warning 1287 '@@innodb_defragment_frequency' is deprecated and will be removed in a future release -SELECT @@global.innodb_defragment_frequency; -@@global.innodb_defragment_frequency -1000 -SET @@global.innodb_defragment_frequency = -1; -Warnings: -Warning 1287 '@@innodb_defragment_frequency' is deprecated and will be removed in a future release -Warning 1292 Truncated incorrect innodb_defragment_frequency value: '-1' -SELECT @@global.innodb_defragment_frequency; -@@global.innodb_defragment_frequency -1 -SET @@global.innodb_defragment_frequency = 10000; -Warnings: -Warning 1287 '@@innodb_defragment_frequency' is deprecated and will be removed in a future release -Warning 1292 Truncated incorrect innodb_defragment_frequency value: '10000' -SELECT @@global.innodb_defragment_frequency; -@@global.innodb_defragment_frequency -1000 -SET @@global.innodb_defragment_frequency = 10.5; -ERROR 42000: Incorrect argument type to variable 'innodb_defragment_frequency' -SELECT @@global.innodb_defragment_frequency; -@@global.innodb_defragment_frequency -1000 -SET @@global.innodb_defragment_frequency = "abc"; -ERROR 42000: Incorrect argument type to variable 'innodb_defragment_frequency' -SELECT @@global.innodb_defragment_frequency; -@@global.innodb_defragment_frequency -1000 -SET @@global.innodb_defragment_frequency = @start_innodb_defragment_frequency; -Warnings: -Warning 1287 '@@innodb_defragment_frequency' is deprecated and will be removed in a future release diff --git a/mysql-test/suite/sys_vars/r/innodb_defragment_n_pages_basic.result b/mysql-test/suite/sys_vars/r/innodb_defragment_n_pages_basic.result deleted file mode 100644 index 823ddad7d59..00000000000 --- a/mysql-test/suite/sys_vars/r/innodb_defragment_n_pages_basic.result +++ /dev/null @@ -1,36 +0,0 @@ -SET @start_innodb_defragment_n_pages = @@global.innodb_defragment_n_pages; -SELECT @start_innodb_defragment_n_pages; -@start_innodb_defragment_n_pages -7 -SELECT COUNT(@@global.innodb_defragment_n_pages); -COUNT(@@global.innodb_defragment_n_pages) -1 -SET @@global.innodb_defragment_n_pages = 1; -Warnings: -Warning 1287 '@@innodb_defragment_n_pages' is deprecated and will be removed in a future release -Warning 1292 Truncated incorrect innodb_defragment_n_pages value: '1' -SELECT @@global.innodb_defragment_n_pages; -@@global.innodb_defragment_n_pages -2 -SET @@global.innodb_defragment_n_pages = 2; -Warnings: -Warning 1287 '@@innodb_defragment_n_pages' is deprecated and will be removed in a future release -SELECT @@global.innodb_defragment_n_pages; -@@global.innodb_defragment_n_pages -2 -SET @@global.innodb_defragment_n_pages = 32; -Warnings: -Warning 1287 '@@innodb_defragment_n_pages' is deprecated and will be removed in a future release -SELECT @@global.innodb_defragment_n_pages; -@@global.innodb_defragment_n_pages -32 -SET @@global.innodb_defragment_n_pages = 64; -Warnings: -Warning 1287 '@@innodb_defragment_n_pages' is deprecated and will be removed in a future release -Warning 1292 Truncated incorrect innodb_defragment_n_pages value: '64' -SELECT @@global.innodb_defragment_n_pages; -@@global.innodb_defragment_n_pages -32 -SET @@global.innodb_defragment_n_pages = @start_innodb_defragment_n_pages; -Warnings: -Warning 1287 '@@innodb_defragment_n_pages' is deprecated and will be removed in a future release diff --git a/mysql-test/suite/sys_vars/r/innodb_defragment_stats_accuracy_basic.result b/mysql-test/suite/sys_vars/r/innodb_defragment_stats_accuracy_basic.result deleted file mode 100644 index 7a2abfa1350..00000000000 --- a/mysql-test/suite/sys_vars/r/innodb_defragment_stats_accuracy_basic.result +++ /dev/null @@ -1,41 +0,0 @@ -SET @start_innodb_defragment_stats_accuracy = @@global.innodb_defragment_stats_accuracy; -SELECT @start_innodb_defragment_stats_accuracy; -@start_innodb_defragment_stats_accuracy -0 -SELECT COUNT(@@global.innodb_defragment_stats_accuracy); -COUNT(@@global.innodb_defragment_stats_accuracy) -1 -SET @@global.innodb_defragment_stats_accuracy = 1; -Warnings: -Warning 1287 '@@innodb_defragment_stats_accuracy' is deprecated and will be removed in a future release -SELECT @@global.innodb_defragment_stats_accuracy; -@@global.innodb_defragment_stats_accuracy -1 -SET @@global.innodb_defragment_stats_accuracy = 1000; -Warnings: -Warning 1287 '@@innodb_defragment_stats_accuracy' is deprecated and will be removed in a future release -SELECT @@global.innodb_defragment_stats_accuracy; -@@global.innodb_defragment_stats_accuracy -1000 -SET @@global.innodb_defragment_stats_accuracy = -1; -Warnings: -Warning 1287 '@@innodb_defragment_stats_accuracy' is deprecated and will be removed in a future release -Warning 1292 Truncated incorrect innodb_defragment_stats_accuracy value: '-1' -SELECT @@global.innodb_defragment_stats_accuracy; -@@global.innodb_defragment_stats_accuracy -0 -SET @@global.innodb_defragment_stats_accuracy = 1000000000000; -Warnings: -Warning 1287 '@@innodb_defragment_stats_accuracy' is deprecated and will be removed in a future release -Warning 1292 Truncated incorrect innodb_defragment_stats_accuracy value: '1000000000000' -SELECT @@global.innodb_defragment_stats_accuracy; -@@global.innodb_defragment_stats_accuracy -4294967295 -SET @@global.innodb_defragment_stats_accuracy = "abc"; -ERROR 42000: Incorrect argument type to variable 'innodb_defragment_stats_accuracy' -SELECT @@global.innodb_defragment_stats_accuracy; -@@global.innodb_defragment_stats_accuracy -4294967295 -SET @@global.innodb_defragment_stats_accuracy = @start_innodb_defragment_stats_accuracy; -Warnings: -Warning 1287 '@@innodb_defragment_stats_accuracy' is deprecated and will be removed in a future release diff --git a/mysql-test/suite/sys_vars/r/sysvars_innodb.result b/mysql-test/suite/sys_vars/r/sysvars_innodb.result index 79341b85822..e6518f060f1 100644 --- a/mysql-test/suite/sys_vars/r/sysvars_innodb.result +++ b/mysql-test/suite/sys_vars/r/sysvars_innodb.result @@ -415,78 +415,6 @@ NUMERIC_BLOCK_SIZE NULL ENUM_VALUE_LIST redundant,compact,dynamic READ_ONLY NO COMMAND_LINE_ARGUMENT REQUIRED -VARIABLE_NAME INNODB_DEFRAGMENT -SESSION_VALUE NULL -DEFAULT_VALUE OFF -VARIABLE_SCOPE GLOBAL -VARIABLE_TYPE BOOLEAN -VARIABLE_COMMENT Enable/disable InnoDB defragmentation (default FALSE). When set to FALSE, all existing defragmentation will be paused. And new defragmentation command will fail.Paused defragmentation commands will resume when this variable is set to true again. -NUMERIC_MIN_VALUE NULL -NUMERIC_MAX_VALUE NULL -NUMERIC_BLOCK_SIZE NULL -ENUM_VALUE_LIST OFF,ON -READ_ONLY NO -COMMAND_LINE_ARGUMENT REQUIRED -VARIABLE_NAME INNODB_DEFRAGMENT_FILL_FACTOR -SESSION_VALUE NULL -DEFAULT_VALUE 0.900000 -VARIABLE_SCOPE GLOBAL -VARIABLE_TYPE DOUBLE -VARIABLE_COMMENT A number between [0.7, 1] that tells defragmentation how full it should fill a page. Default is 0.9. Number below 0.7 won't make much sense.This variable, together with innodb_defragment_fill_factor_n_recs, is introduced so defragmentation won't pack the page too full and cause page split on the next insert on every page. The variable indicating more defragmentation gain is the one effective. -NUMERIC_MIN_VALUE 0.7 -NUMERIC_MAX_VALUE 1 -NUMERIC_BLOCK_SIZE NULL -ENUM_VALUE_LIST NULL -READ_ONLY NO -COMMAND_LINE_ARGUMENT REQUIRED -VARIABLE_NAME INNODB_DEFRAGMENT_FILL_FACTOR_N_RECS -SESSION_VALUE NULL -DEFAULT_VALUE 20 -VARIABLE_SCOPE GLOBAL -VARIABLE_TYPE INT UNSIGNED -VARIABLE_COMMENT How many records of space defragmentation should leave on the page. This variable, together with innodb_defragment_fill_factor, is introduced so defragmentation won't pack the page too full and cause page split on the next insert on every page. The variable indicating more defragmentation gain is the one effective. -NUMERIC_MIN_VALUE 1 -NUMERIC_MAX_VALUE 100 -NUMERIC_BLOCK_SIZE 0 -ENUM_VALUE_LIST NULL -READ_ONLY NO -COMMAND_LINE_ARGUMENT REQUIRED -VARIABLE_NAME INNODB_DEFRAGMENT_FREQUENCY -SESSION_VALUE NULL -DEFAULT_VALUE 40 -VARIABLE_SCOPE GLOBAL -VARIABLE_TYPE INT UNSIGNED -VARIABLE_COMMENT Do not defragment a single index more than this number of time per second.This controls the number of time defragmentation thread can request X_LOCK on an index. Defragmentation thread will check whether 1/defragment_frequency (s) has passed since it worked on this index last time, and put the index back to the queue if not enough time has passed. The actual frequency can only be lower than this given number. -NUMERIC_MIN_VALUE 1 -NUMERIC_MAX_VALUE 1000 -NUMERIC_BLOCK_SIZE 0 -ENUM_VALUE_LIST NULL -READ_ONLY NO -COMMAND_LINE_ARGUMENT REQUIRED -VARIABLE_NAME INNODB_DEFRAGMENT_N_PAGES -SESSION_VALUE NULL -DEFAULT_VALUE 7 -VARIABLE_SCOPE GLOBAL -VARIABLE_TYPE INT UNSIGNED -VARIABLE_COMMENT Number of pages considered at once when merging multiple pages to defragment -NUMERIC_MIN_VALUE 2 -NUMERIC_MAX_VALUE 32 -NUMERIC_BLOCK_SIZE 0 -ENUM_VALUE_LIST NULL -READ_ONLY NO -COMMAND_LINE_ARGUMENT REQUIRED -VARIABLE_NAME INNODB_DEFRAGMENT_STATS_ACCURACY -SESSION_VALUE NULL -DEFAULT_VALUE 0 -VARIABLE_SCOPE GLOBAL -VARIABLE_TYPE INT UNSIGNED -VARIABLE_COMMENT How many defragment stats changes there are before the stats are written to persistent storage. Set to 0 meaning disable defragment stats tracking. -NUMERIC_MIN_VALUE 0 -NUMERIC_MAX_VALUE 4294967295 -NUMERIC_BLOCK_SIZE 0 -ENUM_VALUE_LIST NULL -READ_ONLY NO -COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME INNODB_DISABLE_SORT_FILE_CACHE SESSION_VALUE NULL DEFAULT_VALUE OFF diff --git a/mysql-test/suite/sys_vars/t/innodb_defragment_basic.test b/mysql-test/suite/sys_vars/t/innodb_defragment_basic.test deleted file mode 100644 index 9667f63f687..00000000000 --- a/mysql-test/suite/sys_vars/t/innodb_defragment_basic.test +++ /dev/null @@ -1,20 +0,0 @@ --- source include/have_innodb.inc - -# Check the default value -SET @orig = @@global.innodb_defragment; -SELECT @orig; - -# Turn off -SET GLOBAL innodb_defragment = OFF; -SELECT @@global.innodb_defragment; - -# Turn on -SET GLOBAL innodb_defragment = ON; -SELECT @@global.innodb_defragment; - -# Wrong value ---error ER_WRONG_VALUE_FOR_VAR -SET GLOBAL innodb_defragment = 100; -SELECT @@global.innodb_defragment; - -SET GLOBAL innodb_defragment = @orig; diff --git a/mysql-test/suite/sys_vars/t/innodb_defragment_fill_factor_basic.test b/mysql-test/suite/sys_vars/t/innodb_defragment_fill_factor_basic.test deleted file mode 100644 index ae9863e806a..00000000000 --- a/mysql-test/suite/sys_vars/t/innodb_defragment_fill_factor_basic.test +++ /dev/null @@ -1,27 +0,0 @@ ---source include/have_innodb.inc - -SET @start_innodb_defragment_fill_factor = @@global.innodb_defragment_fill_factor; -SELECT @start_innodb_defragment_fill_factor; - -SELECT COUNT(@@global.innodb_defragment_fill_factor); - -SET @@global.innodb_defragment_fill_factor = 0.77777777777777; -SELECT @@global.innodb_defragment_fill_factor; - -SET @@global.innodb_defragment_fill_factor = 1; -SELECT @@global.innodb_defragment_fill_factor; - -SET @@global.innodb_defragment_fill_factor = 0.7; -SELECT @@global.innodb_defragment_fill_factor; - -SET @@global.innodb_defragment_fill_factor = -1; -SELECT @@global.innodb_defragment_fill_factor; - -SET @@global.innodb_defragment_fill_factor = 2; -SELECT @@global.innodb_defragment_fill_factor; - ---Error ER_WRONG_TYPE_FOR_VAR -SET @@global.innodb_defragment_fill_factor = "abc"; -SELECT @@global.innodb_defragment_fill_factor; - -SET @@global.innodb_defragment_fill_factor = @start_innodb_defragment_fill_factor; diff --git a/mysql-test/suite/sys_vars/t/innodb_defragment_fill_factor_n_recs_basic.test b/mysql-test/suite/sys_vars/t/innodb_defragment_fill_factor_n_recs_basic.test deleted file mode 100644 index 366817c4bbc..00000000000 --- a/mysql-test/suite/sys_vars/t/innodb_defragment_fill_factor_n_recs_basic.test +++ /dev/null @@ -1,31 +0,0 @@ ---source include/have_innodb.inc - -SET @start_innodb_defragment_fill_factor_n_recs = @@global.innodb_defragment_fill_factor_n_recs; -SELECT @start_innodb_defragment_fill_factor_n_recs; - -SELECT COUNT(@@global.innodb_defragment_fill_factor_n_recs); - -SET @@global.innodb_defragment_fill_factor_n_recs = 50; -SELECT @@global.innodb_defragment_fill_factor_n_recs; - -SET @@global.innodb_defragment_fill_factor_n_recs = 100; -SELECT @@global.innodb_defragment_fill_factor_n_recs; - -SET @@global.innodb_defragment_fill_factor_n_recs = 1; -SELECT @@global.innodb_defragment_fill_factor_n_recs; - -SET @@global.innodb_defragment_fill_factor_n_recs = -1; -SELECT @@global.innodb_defragment_fill_factor_n_recs; - -SET @@global.innodb_defragment_fill_factor_n_recs = 10000; -SELECT @@global.innodb_defragment_fill_factor_n_recs; - ---Error ER_WRONG_TYPE_FOR_VAR -SET @@global.innodb_defragment_fill_factor_n_recs = 10.5; -SELECT @@global.innodb_defragment_fill_factor_n_recs; - ---Error ER_WRONG_TYPE_FOR_VAR -SET @@global.innodb_defragment_fill_factor_n_recs = "abc"; -SELECT @@global.innodb_defragment_fill_factor_n_recs; - -SET @@global.innodb_defragment_fill_factor_n_recs = @start_innodb_defragment_fill_factor_n_recs; diff --git a/mysql-test/suite/sys_vars/t/innodb_defragment_frequency_basic.test b/mysql-test/suite/sys_vars/t/innodb_defragment_frequency_basic.test deleted file mode 100644 index 3ab45744a9c..00000000000 --- a/mysql-test/suite/sys_vars/t/innodb_defragment_frequency_basic.test +++ /dev/null @@ -1,37 +0,0 @@ ---source include/have_innodb.inc - -SET @start_innodb_defragment_frequency = @@global.innodb_defragment_frequency; -SELECT @start_innodb_defragment_frequency; - -SELECT COUNT(@@global.innodb_defragment_frequency); - -# test valid value -SET @@global.innodb_defragment_frequency = 200; -SELECT @@global.innodb_defragment_frequency; - -# test valid min -SET @@global.innodb_defragment_frequency = 1; -SELECT @@global.innodb_defragment_frequency; - -# test valid max -SET @@global.innodb_defragment_frequency = 1000; -SELECT @@global.innodb_defragment_frequency; - -# test invalid value < min -SET @@global.innodb_defragment_frequency = -1; -SELECT @@global.innodb_defragment_frequency; - -# test invalid value > max -SET @@global.innodb_defragment_frequency = 10000; -SELECT @@global.innodb_defragment_frequency; - -# test wrong type ---Error ER_WRONG_TYPE_FOR_VAR -SET @@global.innodb_defragment_frequency = 10.5; -SELECT @@global.innodb_defragment_frequency; - ---Error ER_WRONG_TYPE_FOR_VAR -SET @@global.innodb_defragment_frequency = "abc"; -SELECT @@global.innodb_defragment_frequency; - -SET @@global.innodb_defragment_frequency = @start_innodb_defragment_frequency; diff --git a/mysql-test/suite/sys_vars/t/innodb_defragment_n_pages_basic.test b/mysql-test/suite/sys_vars/t/innodb_defragment_n_pages_basic.test deleted file mode 100644 index 64aa20a615f..00000000000 --- a/mysql-test/suite/sys_vars/t/innodb_defragment_n_pages_basic.test +++ /dev/null @@ -1,22 +0,0 @@ ---source include/have_innodb.inc - -SET @start_innodb_defragment_n_pages = @@global.innodb_defragment_n_pages; -SELECT @start_innodb_defragment_n_pages; - -SELECT COUNT(@@global.innodb_defragment_n_pages); - -SET @@global.innodb_defragment_n_pages = 1; -SELECT @@global.innodb_defragment_n_pages; - -SET @@global.innodb_defragment_n_pages = 2; -SELECT @@global.innodb_defragment_n_pages; - -SET @@global.innodb_defragment_n_pages = 32; -SELECT @@global.innodb_defragment_n_pages; - -SET @@global.innodb_defragment_n_pages = 64; -SELECT @@global.innodb_defragment_n_pages; - -SET @@global.innodb_defragment_n_pages = @start_innodb_defragment_n_pages; - - diff --git a/mysql-test/suite/sys_vars/t/innodb_defragment_stats_accuracy_basic.test b/mysql-test/suite/sys_vars/t/innodb_defragment_stats_accuracy_basic.test deleted file mode 100644 index 062753f27ea..00000000000 --- a/mysql-test/suite/sys_vars/t/innodb_defragment_stats_accuracy_basic.test +++ /dev/null @@ -1,24 +0,0 @@ ---source include/have_innodb.inc - -SET @start_innodb_defragment_stats_accuracy = @@global.innodb_defragment_stats_accuracy; -SELECT @start_innodb_defragment_stats_accuracy; - -SELECT COUNT(@@global.innodb_defragment_stats_accuracy); - -SET @@global.innodb_defragment_stats_accuracy = 1; -SELECT @@global.innodb_defragment_stats_accuracy; - -SET @@global.innodb_defragment_stats_accuracy = 1000; -SELECT @@global.innodb_defragment_stats_accuracy; - -SET @@global.innodb_defragment_stats_accuracy = -1; -SELECT @@global.innodb_defragment_stats_accuracy; - -SET @@global.innodb_defragment_stats_accuracy = 1000000000000; -SELECT @@global.innodb_defragment_stats_accuracy; - ---Error ER_WRONG_TYPE_FOR_VAR -SET @@global.innodb_defragment_stats_accuracy = "abc"; -SELECT @@global.innodb_defragment_stats_accuracy; - -SET @@global.innodb_defragment_stats_accuracy = @start_innodb_defragment_stats_accuracy; |