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
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
|
#
# WL7943 Implement INFORMATION_SCHEMA.FILES
#
# Mysqld is initialized in this test with with:
# --innodb_rollback_segments=1
# --bootstrap --innodb_page_size=16k
# 2 implicit and 30 explicit undo tablespaces
CREATE UNDO TABLESPACE undo_3 ADD DATAFILE 'undo_3.ibu';
CREATE UNDO TABLESPACE undo_4 ADD DATAFILE 'undo_4.ibu';
CREATE UNDO TABLESPACE undo_5 ADD DATAFILE 'undo_5.ibu';
CREATE UNDO TABLESPACE undo_6 ADD DATAFILE 'undo_6.ibu';
CREATE UNDO TABLESPACE undo_7 ADD DATAFILE 'undo_7.ibu';
CREATE UNDO TABLESPACE undo_8 ADD DATAFILE 'undo_8.ibu';
CREATE UNDO TABLESPACE undo_9 ADD DATAFILE 'undo_9.ibu';
CREATE UNDO TABLESPACE undo_10 ADD DATAFILE 'undo_10.ibu';
CREATE UNDO TABLESPACE undo_11 ADD DATAFILE 'undo_11.ibu';
CREATE UNDO TABLESPACE undo_12 ADD DATAFILE 'undo_12.ibu';
CREATE UNDO TABLESPACE undo_13 ADD DATAFILE 'undo_13.ibu';
CREATE UNDO TABLESPACE undo_14 ADD DATAFILE 'undo_14.ibu';
CREATE UNDO TABLESPACE undo_15 ADD DATAFILE 'undo_15.ibu';
CREATE UNDO TABLESPACE undo_16 ADD DATAFILE 'undo_16.ibu';
CREATE UNDO TABLESPACE undo_17 ADD DATAFILE 'undo_17.ibu';
CREATE UNDO TABLESPACE undo_18 ADD DATAFILE 'undo_18.ibu';
CREATE UNDO TABLESPACE undo_19 ADD DATAFILE 'undo_19.ibu';
CREATE UNDO TABLESPACE undo_20 ADD DATAFILE 'undo_20.ibu';
CREATE UNDO TABLESPACE undo_21 ADD DATAFILE 'undo_21.ibu';
CREATE UNDO TABLESPACE undo_22 ADD DATAFILE 'undo_22.ibu';
CREATE UNDO TABLESPACE undo_23 ADD DATAFILE 'undo_23.ibu';
CREATE UNDO TABLESPACE undo_24 ADD DATAFILE 'undo_24.ibu';
CREATE UNDO TABLESPACE undo_25 ADD DATAFILE 'undo_25.ibu';
CREATE UNDO TABLESPACE undo_26 ADD DATAFILE 'undo_26.ibu';
CREATE UNDO TABLESPACE undo_27 ADD DATAFILE 'undo_27.ibu';
CREATE UNDO TABLESPACE undo_28 ADD DATAFILE 'undo_28.ibu';
CREATE UNDO TABLESPACE undo_29 ADD DATAFILE 'undo_29.ibu';
CREATE UNDO TABLESPACE undo_30 ADD DATAFILE 'undo_30.ibu';
CREATE UNDO TABLESPACE undo_31 ADD DATAFILE 'undo_31.ibu';
CREATE UNDO TABLESPACE undo_32 ADD DATAFILE 'undo_32.ibu';
SET default_storage_engine=InnoDB;
CREATE TABLE t1 (a serial, b text, c text);
==I_S.FILES== ==I_S.FILES==
FILE_ID 0
TABLESPACE_NAME innodb_system
FILE_NAME MYSQLD_DATADIR/ibdata1
FILE_TYPE TABLESPACE
STATUS NORMAL
ENGINE InnoDB
EXTENT_SIZE 1048576
MAXIMUM_SIZE NULL
AUTOEXTEND_SIZE 67108864
==I_S.FILES== ==I_S.FILES==
FILE_ID 4294967278
TABLESPACE_NAME innodb_undo_002
FILE_NAME MYSQLD_DATADIR/undo_002
FILE_TYPE UNDO LOG
STATUS NORMAL
ENGINE InnoDB
EXTENT_SIZE 1048576
MAXIMUM_SIZE NULL
==I_S.FILES== ==I_S.FILES==
FILE_ID 4294967279
TABLESPACE_NAME innodb_undo_001
FILE_NAME MYSQLD_DATADIR/undo_001
FILE_TYPE UNDO LOG
STATUS NORMAL
ENGINE InnoDB
EXTENT_SIZE 1048576
MAXIMUM_SIZE NULL
==I_S.FILES== ==I_S.FILES==
TABLESPACE_NAME innodb_temporary
FILE_NAME MYSQLD_DATADIR/ibtmp1
FILE_TYPE TEMPORARY
STATUS NORMAL
ENGINE InnoDB
EXTENT_SIZE 1048576
INITIAL_SIZE 12582912
MAXIMUM_SIZE NULL
AUTOEXTEND_SIZE 67108864
==I_S.FILES== ==I_S.FILES==
TABLESPACE_NAME test/t1
FILE_NAME MYSQLD_DATADIR/test/t1.ibd
FILE_TYPE TABLESPACE
STATUS NORMAL
ENGINE InnoDB
DATA_FREE 0
FREE_EXTENTS 0
TOTAL_EXTENTS 0
EXTENT_SIZE 1048576
INITIAL_SIZE 114688
MAXIMUM_SIZE NULL
AUTOEXTEND_SIZE 1048576
#
# Populate t1 with about 3MB of data
#
CREATE PROCEDURE populate_t1()
BEGIN
DECLARE i INT DEFAULT 1;
while (i <= 6000) DO
INSERT INTO t1 (b,c) VALUES (repeat('b', 250), repeat('c', 250));
SET i = i + 1;
END WHILE;
END |
BEGIN;
CALL populate_t1();
FLUSH TABLE t1;
==I_S.FILES== ==I_S.FILES==
TABLESPACE_NAME test/t1
FILE_NAME MYSQLD_DATADIR/test/t1.ibd
FILE_TYPE TABLESPACE
STATUS NORMAL
ENGINE InnoDB
DATA_FREE 4194304
FREE_EXTENTS 2
TOTAL_EXTENTS 11
EXTENT_SIZE 1048576
INITIAL_SIZE 114688
MAXIMUM_SIZE NULL
AUTOEXTEND_SIZE 1048576
#
# Populate t1 with another 3MB of data
#
CALL populate_t1();
FLUSH TABLE t1;
==I_S.FILES== ==I_S.FILES==
TABLESPACE_NAME test/t1
FILE_NAME MYSQLD_DATADIR/test/t1.ibd
FILE_TYPE TABLESPACE
STATUS NORMAL
ENGINE InnoDB
DATA_FREE 4194304
FREE_EXTENTS 2
TOTAL_EXTENTS 15
EXTENT_SIZE 1048576
INITIAL_SIZE 114688
MAXIMUM_SIZE NULL
AUTOEXTEND_SIZE 1048576
#
# Delete the half of the records.
#
DELETE FROM t1 WHERE a % 2 = 1;
FLUSH TABLE t1;
==I_S.FILES== ==I_S.FILES==
TABLESPACE_NAME test/t1
FILE_NAME MYSQLD_DATADIR/test/t1.ibd
FILE_TYPE TABLESPACE
STATUS NORMAL
ENGINE InnoDB
DATA_FREE 4194304
FREE_EXTENTS 2
TOTAL_EXTENTS 15
EXTENT_SIZE 1048576
INITIAL_SIZE 114688
MAXIMUM_SIZE NULL
AUTOEXTEND_SIZE 1048576
#
# Cleanup
#
DROP TABLE t1;
DROP PROCEDURE populate_t1;
# Set all explicit undo tablespaces to inacitve.
ALTER UNDO TABLESPACE undo_3 SET INACTIVE;
ALTER UNDO TABLESPACE undo_4 SET INACTIVE;
ALTER UNDO TABLESPACE undo_5 SET INACTIVE;
ALTER UNDO TABLESPACE undo_6 SET INACTIVE;
ALTER UNDO TABLESPACE undo_7 SET INACTIVE;
ALTER UNDO TABLESPACE undo_8 SET INACTIVE;
ALTER UNDO TABLESPACE undo_9 SET INACTIVE;
ALTER UNDO TABLESPACE undo_10 SET INACTIVE;
ALTER UNDO TABLESPACE undo_11 SET INACTIVE;
ALTER UNDO TABLESPACE undo_12 SET INACTIVE;
ALTER UNDO TABLESPACE undo_13 SET INACTIVE;
ALTER UNDO TABLESPACE undo_14 SET INACTIVE;
ALTER UNDO TABLESPACE undo_15 SET INACTIVE;
ALTER UNDO TABLESPACE undo_16 SET INACTIVE;
ALTER UNDO TABLESPACE undo_17 SET INACTIVE;
ALTER UNDO TABLESPACE undo_18 SET INACTIVE;
ALTER UNDO TABLESPACE undo_19 SET INACTIVE;
ALTER UNDO TABLESPACE undo_20 SET INACTIVE;
ALTER UNDO TABLESPACE undo_21 SET INACTIVE;
ALTER UNDO TABLESPACE undo_22 SET INACTIVE;
ALTER UNDO TABLESPACE undo_23 SET INACTIVE;
ALTER UNDO TABLESPACE undo_24 SET INACTIVE;
ALTER UNDO TABLESPACE undo_25 SET INACTIVE;
ALTER UNDO TABLESPACE undo_26 SET INACTIVE;
ALTER UNDO TABLESPACE undo_27 SET INACTIVE;
ALTER UNDO TABLESPACE undo_28 SET INACTIVE;
ALTER UNDO TABLESPACE undo_29 SET INACTIVE;
ALTER UNDO TABLESPACE undo_30 SET INACTIVE;
ALTER UNDO TABLESPACE undo_31 SET INACTIVE;
ALTER UNDO TABLESPACE undo_32 SET INACTIVE;
# Wait for all explicit undo tablespaces to become empty.
# Drop all explicit undo tablespaces.
DROP UNDO TABLESPACE undo_3;
DROP UNDO TABLESPACE undo_4;
DROP UNDO TABLESPACE undo_5;
DROP UNDO TABLESPACE undo_6;
DROP UNDO TABLESPACE undo_7;
DROP UNDO TABLESPACE undo_8;
DROP UNDO TABLESPACE undo_9;
DROP UNDO TABLESPACE undo_10;
DROP UNDO TABLESPACE undo_11;
DROP UNDO TABLESPACE undo_12;
DROP UNDO TABLESPACE undo_13;
DROP UNDO TABLESPACE undo_14;
DROP UNDO TABLESPACE undo_15;
DROP UNDO TABLESPACE undo_16;
DROP UNDO TABLESPACE undo_17;
DROP UNDO TABLESPACE undo_18;
DROP UNDO TABLESPACE undo_19;
DROP UNDO TABLESPACE undo_20;
DROP UNDO TABLESPACE undo_21;
DROP UNDO TABLESPACE undo_22;
DROP UNDO TABLESPACE undo_23;
DROP UNDO TABLESPACE undo_24;
DROP UNDO TABLESPACE undo_25;
DROP UNDO TABLESPACE undo_26;
DROP UNDO TABLESPACE undo_27;
DROP UNDO TABLESPACE undo_28;
DROP UNDO TABLESPACE undo_29;
DROP UNDO TABLESPACE undo_30;
DROP UNDO TABLESPACE undo_31;
DROP UNDO TABLESPACE undo_32;
|
