EVOLUTION-MANAGER

Edit File: innodb_defrag_stats_many_tables.result

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; 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; SET @@global.table_definition_cache = @start_table_definition_cache; DROP TABLE t1;