From 7ca89af6f8faf1f8ec6ede01a9353ac499d37711 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Marko=20M=C3=A4kel=C3=A4?= Date: Sat, 11 Mar 2023 10:45:35 +0200 Subject: MDEV-30545 Remove innodb_defragment and related parameters MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit 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 --- mysql-test/suite/innodb/t/innodb_defragment.test | 157 ----------------------- 1 file changed, 157 deletions(-) delete mode 100644 mysql-test/suite/innodb/t/innodb_defragment.test (limited to 'mysql-test/suite/innodb/t/innodb_defragment.test') 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; -- cgit v1.2.1