diff options
Diffstat (limited to 'mysql-test/suite/innodb/t/innodb_defragment.test')
| -rw-r--r-- | mysql-test/suite/innodb/t/innodb_defragment.test | 157 |
1 files changed, 157 insertions, 0 deletions
diff --git a/mysql-test/suite/innodb/t/innodb_defragment.test b/mysql-test/suite/innodb/t/innodb_defragment.test new file mode 100644 index 00000000..51ef7837 --- /dev/null +++ b/mysql-test/suite/innodb/t/innodb_defragment.test @@ -0,0 +1,157 @@ +--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; |