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

1 files changed, 0 insertions, 157 deletions

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;