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
125
126
127
128
129
130
131
132
133
134
135
136
137
138
|
SET GLOBAL innodb_defragment_stats_accuracy = 20;
DELETE FROM mysql.innodb_index_stats;
# Create table.
CREATE TABLE t1 (a INT PRIMARY KEY AUTO_INCREMENT, b VARCHAR(256),
KEY SECOND(a, b)) ENGINE=INNODB STATS_PERSISTENT=0;
INSERT INTO t1 SELECT 100*FLOOR(seq/70)+seq%70, REPEAT('A', 256)
FROM seq_1_to_1024;
# Not enough page splits to trigger persistent stats write yet.
SELECT * FROM mysql.innodb_index_stats;
database_name table_name index_name last_update stat_name stat_value sample_size stat_description
INSERT INTO t1 SELECT 100*FLOOR(seq/70)+seq%70, REPEAT('A', 256)
FROM seq_1025_to_1433;
BEGIN;
INSERT INTO t1 SELECT 100*20+seq, REPEAT('A', 256)
FROM seq_70_to_99;
INSERT INTO t1 SELECT 100*19+seq, REPEAT('A', 256)
FROM seq_70_to_99;
INSERT INTO t1 SELECT 100*18+seq, REPEAT('A', 256)
FROM seq_70_to_99;
INSERT INTO t1 SELECT 100*17+seq, REPEAT('A', 256)
FROM seq_70_to_99;
INSERT INTO t1 SELECT 100*16+seq, REPEAT('A', 256)
FROM seq_70_to_99;
INSERT INTO t1 SELECT 100*15+seq, REPEAT('A', 256)
FROM seq_70_to_99;
INSERT INTO t1 SELECT 100*14+seq, REPEAT('A', 256)
FROM seq_70_to_99;
INSERT INTO t1 SELECT 100*13+seq, REPEAT('A', 256)
FROM seq_70_to_99;
INSERT INTO t1 SELECT 100*12+seq, REPEAT('A', 256)
FROM seq_70_to_99;
INSERT INTO t1 SELECT 100*11+seq, REPEAT('A', 256)
FROM seq_70_to_99;
INSERT INTO t1 SELECT 100*10+seq, REPEAT('A', 256)
FROM seq_70_to_99;
INSERT INTO t1 SELECT 100*9+seq, REPEAT('A', 256)
FROM seq_70_to_99;
INSERT INTO t1 SELECT 100*8+seq, REPEAT('A', 256)
FROM seq_70_to_99;
INSERT INTO t1 SELECT 100*7+seq, REPEAT('A', 256)
FROM seq_70_to_99;
INSERT INTO t1 SELECT 100*6+seq, REPEAT('A', 256)
FROM seq_70_to_99;
INSERT INTO t1 SELECT 100*5+seq, REPEAT('A', 256)
FROM seq_70_to_99;
INSERT INTO t1 SELECT 100*4+seq, REPEAT('A', 256)
FROM seq_70_to_99;
INSERT INTO t1 SELECT 100*3+seq, REPEAT('A', 256)
FROM seq_70_to_99;
INSERT INTO t1 SELECT 100*2+seq, REPEAT('A', 256)
FROM seq_70_to_99;
INSERT INTO t1 SELECT 100*1+seq, REPEAT('A', 256)
FROM seq_70_to_99;
ROLLBACK;
SELECT @@GLOBAL.innodb_force_recovery<2 "have background defragmentation";
have background defragmentation
1
SELECT table_name, index_name, stat_name FROM mysql.innodb_index_stats;
table_name index_name stat_name
t1 PRIMARY n_leaf_pages_defrag
t1 PRIMARY n_leaf_pages_reserved
t1 PRIMARY n_page_split
t1 SECOND n_leaf_pages_defrag
t1 SECOND n_leaf_pages_reserved
t1 SECOND n_page_split
optimize table t1;
Table Op Msg_type Msg_text
test.t1 optimize status OK
SELECT table_name, index_name, stat_name FROM mysql.innodb_index_stats;
table_name index_name stat_name
t1 PRIMARY n_leaf_pages_defrag
t1 PRIMARY n_leaf_pages_reserved
t1 PRIMARY n_page_split
t1 PRIMARY n_pages_freed
t1 SECOND n_leaf_pages_defrag
t1 SECOND n_leaf_pages_reserved
t1 SECOND n_page_split
t1 SECOND n_pages_freed
set global innodb_defragment_stats_accuracy = 40;
INSERT INTO t1 (b) SELECT b from t1;
SELECT table_name, index_name, stat_name FROM mysql.innodb_index_stats;
table_name index_name stat_name
t1 PRIMARY n_leaf_pages_defrag
t1 PRIMARY n_leaf_pages_reserved
t1 PRIMARY n_page_split
t1 PRIMARY n_pages_freed
t1 SECOND n_leaf_pages_defrag
t1 SECOND n_leaf_pages_reserved
t1 SECOND n_page_split
t1 SECOND n_pages_freed
INSERT INTO t1 (b) SELECT b from t1;
SELECT stat_name FROM mysql.innodb_index_stats WHERE table_name='t1';
stat_name
n_leaf_pages_defrag
n_leaf_pages_defrag
n_leaf_pages_reserved
n_leaf_pages_reserved
n_page_split
n_page_split
n_pages_freed
n_pages_freed
# Table rename should cause stats rename.
rename table t1 to t2;
SELECT table_name, index_name, stat_name FROM mysql.innodb_index_stats;
table_name index_name stat_name
t2 PRIMARY n_leaf_pages_defrag
t2 PRIMARY n_leaf_pages_reserved
t2 PRIMARY n_page_split
t2 PRIMARY n_pages_freed
t2 SECOND n_leaf_pages_defrag
t2 SECOND n_leaf_pages_reserved
t2 SECOND n_page_split
t2 SECOND n_pages_freed
# Drop index should cause stats drop, but will not.
drop index SECOND on t2;
#
# MDEV-26636: Statistics must not be written for temporary tables
#
SET GLOBAL innodb_defragment_stats_accuracy = 1;
CREATE TEMPORARY TABLE t (a INT PRIMARY KEY, c CHAR(255) NOT NULL)
ENGINE=InnoDB;
INSERT INTO t SELECT seq, '' FROM seq_1_to_100;
# restart
SELECT table_name, index_name, stat_name FROM mysql.innodb_index_stats;
table_name index_name stat_name
t2 PRIMARY n_leaf_pages_defrag
t2 PRIMARY n_leaf_pages_reserved
t2 PRIMARY n_page_split
t2 PRIMARY n_pages_freed
t2 SECOND n_leaf_pages_defrag
t2 SECOND n_leaf_pages_reserved
t2 SECOND n_page_split
t2 SECOND n_pages_freed
# Clean up
ALTER TABLE t2 STATS_PERSISTENT=1;
DROP TABLE t2;
SELECT * FROM mysql.innodb_index_stats;
database_name table_name index_name last_update stat_name stat_value sample_size stat_description
|
