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
|
#
# WL7943 Implement INFORMATION_SCHEMA.FILES
#
# Mysqld is initialized in this test with with:
# --innodb_rollback_segments=1
# --bootstrap --innodb_page_size=32k
# 2 implicit and 6 explicit undo tablespaces
CREATE UNDO TABLESPACE undo_003 ADD DATAFILE 'undo_003.ibu';
CREATE UNDO TABLESPACE undo_004 ADD DATAFILE 'undo_004.ibu';
CREATE UNDO TABLESPACE undo_005 ADD DATAFILE 'undo_005.ibu';
CREATE UNDO TABLESPACE undo_006 ADD DATAFILE 'undo_006.ibu';
CREATE UNDO TABLESPACE undo_007 ADD DATAFILE 'undo_007.ibu';
CREATE UNDO TABLESPACE undo_008 ADD DATAFILE 'undo_008.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 2097152
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 2097152
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 2097152
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 2097152
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 2097152
INITIAL_SIZE 229376
MAXIMUM_SIZE NULL
AUTOEXTEND_SIZE 2097152
#
# 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 8388608
FREE_EXTENTS 3
TOTAL_EXTENTS 10
EXTENT_SIZE 2097152
INITIAL_SIZE 229376
MAXIMUM_SIZE NULL
AUTOEXTEND_SIZE 2097152
#
# 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 8388608
FREE_EXTENTS 2
TOTAL_EXTENTS 11
EXTENT_SIZE 2097152
INITIAL_SIZE 229376
MAXIMUM_SIZE NULL
AUTOEXTEND_SIZE 2097152
#
# 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 8388608
FREE_EXTENTS 2
TOTAL_EXTENTS 11
EXTENT_SIZE 2097152
INITIAL_SIZE 229376
MAXIMUM_SIZE NULL
AUTOEXTEND_SIZE 2097152
#
# Cleanup
#
DROP TABLE t1;
DROP PROCEDURE populate_t1;
# Set all explicit undo tablespaces to inacitve.
ALTER UNDO TABLESPACE undo_003 SET INACTIVE;
ALTER UNDO TABLESPACE undo_004 SET INACTIVE;
ALTER UNDO TABLESPACE undo_005 SET INACTIVE;
ALTER UNDO TABLESPACE undo_006 SET INACTIVE;
ALTER UNDO TABLESPACE undo_007 SET INACTIVE;
ALTER UNDO TABLESPACE undo_008 SET INACTIVE;
# Wait for all explicit undo tablespaces to become empty.
# Drop all explicit undo tablespaces.
DROP UNDO TABLESPACE undo_003;
DROP UNDO TABLESPACE undo_004;
DROP UNDO TABLESPACE undo_005;
DROP UNDO TABLESPACE undo_006;
DROP UNDO TABLESPACE undo_007;
DROP UNDO TABLESPACE undo_008;
|
