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
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
|
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;
optimize table t1;
Table Op Msg_type Msg_text
test.t1 optimize status OK
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;
Table Op Msg_type Msg_text
test.t1 optimize status OK
create procedure defragment()
begin
set @i = 0;
repeat
set @i = @i + 1;
optimize table t1;
until @i = 3 end repeat;
end //
select count(stat_value) from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_pages_freed');
count(stat_value)
0
select count(stat_value) from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_page_split');
count(stat_value)
2
select count(stat_value) from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_leaf_pages_defrag');
count(stat_value)
2
select count(*) from t1;
count(*)
10004
call defragment();
optimize table t1;
Table Op Msg_type Msg_text
test.t1 optimize status OK
select count(*) from t1;
count(*)
7904
select count(stat_value) = 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_pages_freed');
count(stat_value) = 0
0
select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_page_split');
count(stat_value) > 0
1
select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_leaf_pages_defrag');
count(stat_value) > 0
1
select count(*) from t1 force index (second);
count(*)
7904
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');
count(stat_value) = 0
1
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');
count(stat_value) = 0
1
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');
count(stat_value) = 0
1
SET @@global.innodb_defragment_n_pages = 3;
optimize table t1;
Table Op Msg_type Msg_text
test.t1 optimize status OK
select count(stat_value) < 3 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_pages_freed');
count(stat_value) < 3
1
select count(stat_value) < 3 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_page_split');
count(stat_value) < 3
1
select count(stat_value) < 3 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_leaf_pages_defrag');
count(stat_value) < 3
1
select count(*) from t1;
count(*)
6904
select count(stat_value) < 3 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_pages_freed');
count(stat_value) < 3
1
select count(stat_value) < 3 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_page_split');
count(stat_value) < 3
1
select count(stat_value) < 3 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_leaf_pages_defrag');
count(stat_value) < 3
1
select count(*) from t1 force index (second);
count(*)
6904
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');
count(stat_value) = 0
1
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');
count(stat_value) = 0
1
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');
count(stat_value) = 0
1
SET @@global.innodb_defragment_n_pages = 10;
optimize table t1;
Table Op Msg_type Msg_text
test.t1 optimize status OK
select count(stat_value) > 1 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_pages_freed');
count(stat_value) > 1
1
select count(stat_value) > 1 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_page_split');
count(stat_value) > 1
1
select count(stat_value) > 1 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_leaf_pages_defrag');
count(stat_value) > 1
1
select count(*) from t1 force index (second);
count(*)
6904
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');
count(stat_value) = 0
1
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');
count(stat_value) = 0
1
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');
count(stat_value) = 0
1
DROP PROCEDURE defragment;
DROP TABLE t1;
|
