1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
|
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;
|
