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
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
|
#
# This test tests various DML operations and their effect on the tablespaces
#
# Test tables with file_per_table tablespace
#
# Scenario-1: Create a table without autoextend_size and validate that the allocation
# happens as per the old logic
#
CREATE TABLE t1 (c1 INT, c2 TEXT);
# Verify the initial size of the file
SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
WHERE NAME LIKE '%t1%';
NAME FILE_SIZE AUTOEXTEND_SIZE
test/t1 114688 0
# This is a smaller tablespace. The tablespace should extend by adding smaller number
# of pages initially
# Add few rows to the table and check the size of the file
select count(*) from t1;
count(*)
1
SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
WHERE NAME LIKE '%t1%';
NAME FILE_SIZE AUTOEXTEND_SIZE
test/t1 163840 0
select count(*) from t1;
count(*)
2
SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
WHERE NAME LIKE '%t1%';
NAME FILE_SIZE AUTOEXTEND_SIZE
test/t1 229376 0
select count(*) from t1;
count(*)
3
SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
WHERE NAME LIKE '%t1%';
NAME FILE_SIZE AUTOEXTEND_SIZE
test/t1 294912 0
# Once the tablespace size is more than half of the extent size, tablespace is extended with
# 4 extents at a time
select count(*) from t1;
count(*)
10
SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
WHERE NAME LIKE '%t1%';
NAME FILE_SIZE AUTOEXTEND_SIZE
test/t1 4194304 0
drop table t1;
#
# Scenario-2: Create table with autoextend_size clause and validate that the allocation happens as per the
# new logic and the initial size of the tablespace file is the value of AUTOEXTEND_SIZE
#
CREATE TABLE t2(c1 INT, c2 TEXT) AUTOEXTEND_SIZE 4M;
select count(*) from t2;
count(*)
10
SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
WHERE NAME LIKE '%t2%';
NAME FILE_SIZE AUTOEXTEND_SIZE
test/t2 4194304 4194304
select count(*) from t2;
count(*)
20
SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
WHERE NAME LIKE '%t2%';
NAME FILE_SIZE AUTOEXTEND_SIZE
test/t2 4194304 4194304
select count(*) from t2;
count(*)
1020
SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
WHERE NAME LIKE '%t2%';
NAME FILE_SIZE AUTOEXTEND_SIZE
test/t2 71303168 4194304
select count(*) from t2;
count(*)
2020
SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
WHERE NAME LIKE '%t2%';
NAME FILE_SIZE AUTOEXTEND_SIZE
test/t2 138412032 4194304
DROP TABLE t2;
#
# Scenario-3: Create a table without autoextend_size and alter it later on to add autoextend_size property
# Validate that the allocation is done with the old logic until autoextend_size is introduced to the table
#
CREATE TABLE t3(c1 INT, c2 TEXT);
SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
WHERE NAME LIKE '%t3%';
NAME FILE_SIZE AUTOEXTEND_SIZE
test/t3 114688 0
SELECT COUNT(*) FROM t3;
COUNT(*)
1
SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
WHERE NAME LIKE '%t3%';
NAME FILE_SIZE AUTOEXTEND_SIZE
test/t3 163840 0
ALTER TABLE t3 AUTOEXTEND_SIZE 4M;
SELECT COUNT(*) FROM t3;
COUNT(*)
2
SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
WHERE NAME LIKE '%t3%';
NAME FILE_SIZE AUTOEXTEND_SIZE
test/t3 4194304 4194304
DROP TABLE t3;
#
# Scenario-4: Test crash recovery and verify that the autoextend_size values are reinstated
# after recovery
#
CREATE TABLE t4(c1 INT, c2 longblob) AUTOEXTEND_SIZE 8m;
# The initialize size of the table should be same as the autoextend_size
SELECT NAME, FILE_SIZE/AUTOEXTEND_SIZE, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
WHERE NAME LIKE '%t4%';
NAME FILE_SIZE/AUTOEXTEND_SIZE FILE_SIZE AUTOEXTEND_SIZE
test/t4 1.0000 8388608 8388608
# Crash the server while attempting to allocate more space
INSERT INTO t4 VALUES (1, repeat(1, 4 * 1024 * 1024));
SET DEBUG="+d, fsp_crash_before_space_extend";
INSERT INTO t4 VALUES (1, repeat(1, 4 * 1024 * 1024));
ERROR HY000: Lost connection to MySQL server during query
# Restart mysqld after the crash and reconnect
# restart
SELECT NAME, FILE_SIZE/AUTOEXTEND_SIZE, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
WHERE NAME LIKE '%t4%';
NAME FILE_SIZE/AUTOEXTEND_SIZE FILE_SIZE AUTOEXTEND_SIZE
test/t4 1.0000 8388608 8388608
INSERT INTO t4 VALUES (1, repeat(1, 4 * 1024 * 1024));
SELECT COUNT(*) FROM t4;
COUNT(*)
2
# Verify new file size is a multiple of autoextend_size
SELECT NAME, FILE_SIZE/AUTOEXTEND_SIZE, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
WHERE NAME LIKE '%t4%';
NAME FILE_SIZE/AUTOEXTEND_SIZE FILE_SIZE AUTOEXTEND_SIZE
test/t4 2.0000 16777216 8388608
DROP TABLE t4;
#
# Scenario-5: Test crash recovery when server is started with --skip-innodb-validate-tablespace-paths
# option and verify that the server respects the autoextend_size value
#
CREATE TABLE t5(c1 INT, c2 longblob) AUTOEXTEND_SIZE 8m;
# The initialize size of the table should be same as the autoextend_size
SELECT NAME, FILE_SIZE/AUTOEXTEND_SIZE, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
WHERE NAME LIKE '%t5%';
NAME FILE_SIZE/AUTOEXTEND_SIZE FILE_SIZE AUTOEXTEND_SIZE
test/t5 1.0000 8388608 8388608
# Crash the server while attempting to allocate more space
INSERT INTO t5 VALUES (1, repeat(1, 4 * 1024 * 1024));
SET DEBUG="+d, fsp_crash_before_space_extend";
INSERT INTO t5 VALUES (1, repeat(1, 4 * 1024 * 1024));
ERROR HY000: Lost connection to MySQL server during query
# Restart mysqld with --skip-innodb-validate-tablespace-paths option after
# the crash and reconnect
# restart: --skip-innodb-validate-tablespace-paths
SELECT NAME, FILE_SIZE/AUTOEXTEND_SIZE, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
WHERE NAME LIKE '%t5%';
NAME FILE_SIZE/AUTOEXTEND_SIZE FILE_SIZE AUTOEXTEND_SIZE
test/t5 1.0000 8388608 8388608
INSERT INTO t5 VALUES (1, repeat(1, 4 * 1024 * 1024));
SELECT COUNT(*) FROM t5;
COUNT(*)
2
# Verify new file size is a multiple of autoextend_size
SELECT NAME, FILE_SIZE/AUTOEXTEND_SIZE, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
WHERE NAME LIKE '%t5%';
NAME FILE_SIZE/AUTOEXTEND_SIZE FILE_SIZE AUTOEXTEND_SIZE
test/t5 2.0000 16777216 8388608
DROP TABLE t5;
# restart
#
# Scenario-6: Test concurrent updates to autoextend_size while inserts are going on
# on another connection
#
CREATE TABLE t6(id int, l longblob, v varchar(100)) AUTOEXTEND_SIZE 4m;
CREATE PROCEDURE update_aes()
BEGIN
DECLARE i INT DEFAULT 0;
WHILE i < 1000 DO
ALTER TABLE t6 AUTOEXTEND_SIZE=4m;
ALTER TABLE t6 AUTOEXTEND_SIZE=64m;
ALTER TABLE t6 AUTOEXTEND_SIZE=16m;
ALTER TABLE t6 AUTOEXTEND_SIZE=8m;
ALTER TABLE t6 AUTOEXTEND_SIZE=32m;
SET i = i + 1;
END WHILE;
END |
CREATE PROCEDURE insert_data()
BEGIN
DECLARE i INT DEFAULT 0;
WHILE i < 10000 DO
INSERT INTO t6 (l) VALUES (repeat(2, 1024 * 16));
SET i = i + 1;
END WHILE;
END |
CALL insert_data();;
CALL update_aes();
SELECT COUNT(*) FROM t6;
COUNT(*)
10000
DROP TABLE t6;
DROP PROCEDURE insert_data;
DROP PROCEDURE update_aes;
|
