1set global innodb_defragment_stats_accuracy = 80; 2CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY AUTO_INCREMENT, b VARCHAR(256), KEY SECOND(a, b)) ENGINE=INNODB; 3optimize table t1; 4Table Op Msg_type Msg_text 5test.t1 optimize status OK 6INSERT INTO t1 VALUES (100000, REPEAT('A', 256)); 7INSERT INTO t1 VALUES (200000, REPEAT('A', 256)); 8INSERT INTO t1 VALUES (300000, REPEAT('A', 256)); 9INSERT INTO t1 VALUES (400000, REPEAT('A', 256)); 10optimize table t1; 11Table Op Msg_type Msg_text 12test.t1 optimize status OK 13create procedure defragment() 14begin 15set @i = 0; 16repeat 17set @i = @i + 1; 18optimize table t1; 19until @i = 3 end repeat; 20end // 21select count(stat_value) from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_pages_freed'); 22count(stat_value) 230 24select count(stat_value) from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_page_split'); 25count(stat_value) 262 27select count(stat_value) from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_leaf_pages_defrag'); 28count(stat_value) 292 30select count(*) from t1; 31count(*) 3210004 33connect con1,localhost,root,,test,$MASTER_MYPORT,$MASTER_MYSOCK; 34connection con1; 35call defragment(); 36connection default; 37connection con1; 38connection default; 39disconnect con1; 40optimize table t1; 41Table Op Msg_type Msg_text 42test.t1 optimize status OK 43# restart 44select count(*) from t1; 45count(*) 467904 47select count(stat_value) = 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_pages_freed'); 48count(stat_value) = 0 490 50select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_page_split'); 51count(stat_value) > 0 521 53select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_leaf_pages_defrag'); 54count(stat_value) > 0 551 56select count(*) from t1 force index (second); 57count(*) 587904 59select 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'); 60count(stat_value) = 0 611 62select 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'); 63count(stat_value) = 0 641 65select 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'); 66count(stat_value) = 0 671 68SET @@global.innodb_defragment_n_pages = 3; 69optimize table t1; 70Table Op Msg_type Msg_text 71test.t1 optimize status OK 72# restart 73select count(stat_value) < 3 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_pages_freed'); 74count(stat_value) < 3 751 76select count(stat_value) < 3 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_page_split'); 77count(stat_value) < 3 781 79select count(stat_value) < 3 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_leaf_pages_defrag'); 80count(stat_value) < 3 811 82select count(*) from t1; 83count(*) 846904 85select count(stat_value) < 3 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_pages_freed'); 86count(stat_value) < 3 871 88select count(stat_value) < 3 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_page_split'); 89count(stat_value) < 3 901 91select count(stat_value) < 3 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_leaf_pages_defrag'); 92count(stat_value) < 3 931 94select count(*) from t1 force index (second); 95count(*) 966904 97select 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'); 98count(stat_value) = 0 991 100select 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'); 101count(stat_value) = 0 1021 103select 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'); 104count(stat_value) = 0 1051 106SET @@global.innodb_defragment_n_pages = 10; 107optimize table t1; 108Table Op Msg_type Msg_text 109test.t1 optimize status OK 110# restart 111select count(stat_value) > 1 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_pages_freed'); 112count(stat_value) > 1 1131 114select count(stat_value) > 1 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_page_split'); 115count(stat_value) > 1 1161 117select count(stat_value) > 1 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_leaf_pages_defrag'); 118count(stat_value) > 1 1191 120select count(*) from t1 force index (second); 121count(*) 1226904 123select 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'); 124count(stat_value) = 0 1251 126select 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'); 127count(stat_value) = 0 1281 129select 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'); 130count(stat_value) = 0 1311 132DROP PROCEDURE defragment; 133DROP TABLE t1; 134