Debian Codegraph @

mysql-test/suite/innodb_undo/r/truncate.result

raw
1 #
2 # WL#6965: Truncate UNDO logs.
3 #
4 # While the purge thread is turned off, perform enough DML to cause
5 # undo tablespace sizes grow beyond the truncation threshold.
6 # Then check if truncation is accomplished by restarting mysqld
7 # causing each undo tablespace to be truncated.
8 # Create 2 explicit undo tablespaces.
9 CREATE UNDO TABLESPACE undo_003 ADD DATAFILE 'undo_003.ibu';
10 CREATE UNDO TABLESPACE undo_004 ADD DATAFILE 'undo_004.ibu';
11 # Make the 2 implicit undo tablespaces inactive so they get truncated.
12 SET GLOBAL innodb_purge_rseg_truncate_frequency=1;
13 ALTER UNDO TABLESPACE innodb_undo_001 SET INACTIVE;
14 ALTER UNDO TABLESPACE innodb_undo_002 SET INACTIVE;
15 # Make the 2 implicit undo tablespaces active.
16 ALTER UNDO TABLESPACE innodb_undo_001 SET ACTIVE;
17 ALTER UNDO TABLESPACE innodb_undo_002 SET ACTIVE;
18 # Get rid of the explcit undo tablespaces.
19 ALTER UNDO TABLESPACE undo_003 SET INACTIVE;
20 ALTER UNDO TABLESPACE undo_004 SET INACTIVE;
21 DROP UNDO TABLESPACE undo_003;
22 DROP UNDO TABLESPACE undo_004;
23 SET GLOBAL innodb_purge_rseg_truncate_frequency=default;
24 # The two undo tablespaces to truncate at shutdown will be innodb_undo_001 and innodb_undo_002
25 SELECT NAME, SPACE_TYPE, STATE, AUTOEXTEND_SIZE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
26 WHERE SPACE_TYPE = 'Undo' ORDER BY NAME;
27 NAME SPACE_TYPE STATE AUTOEXTEND_SIZE
28 innodb_undo_001 Undo active 0
29 innodb_undo_002 Undo active 0
30 # Turn off purging so that undo logs build up in the undo tablespaces.
31 SET GLOBAL innodb_purge_stop_now=ON;
32 # Perform enough DML action so that undo tablespace sizes grow beyond
33 # the threshold set by --innodb-max-undo-log-size.
34 create table t1(
35 keyc int,
36 c1 char(255),
37 c2 char(255),
38 c3 char(255),
39 c4 char(255),
40 c5 char(255),
41 c6 char(255),
42 primary key(keyc)) engine = innodb;
43 CREATE PROCEDURE populate_t1()
44 BEGIN
45 DECLARE i INT DEFAULT 1;
46 while (i <= 16000) DO
47 insert into t1 values (i, 'a', 'b', 'c', 'd', 'e', 'f' );
48 SET i = i + 1;
49 END WHILE;
50 END |
51 call populate_t1();
52 delete from t1 where keyc < 10000;
53 update t1 set c1 = 'mysql' where keyc > 10000;
54 update t1 set c2 = 'mysql' where keyc > 10000;
55 update t1 set c3 = 'mysql' where keyc > 10000;
56 update t1 set c4 = 'mysql' where keyc > 10000;
57 update t1 set c5 = 'mysql' where keyc > 10000;
58 update t1 set c6 = 'mysql' where keyc > 10000;
59 # Start a new transaction for both tablespaces to get utilized
60 create table t2 (
61 keyc int,
62 c1 char(255),
63 c2 char(255),
64 c3 char(255),
65 c4 char(255),
66 c5 char(255),
67 c6 char(255),
68 primary key(keyc)) engine = innodb;
69 CREATE PROCEDURE populate_t2()
70 BEGIN
71 DECLARE i INT DEFAULT 1;
72 while (i <= 16000) DO
73 insert into t2 values (i, 'a', 'b', 'c', 'd', 'e', 'f' );
74 SET i = i + 1;
75 END WHILE;
76 END |
77 call populate_t2();
78 delete from t2 where keyc < 10000;
79 update t2 set c1 = 'mysql' where keyc > 10000;
80 update t2 set c2 = 'mysql' where keyc > 10000;
81 update t2 set c3 = 'mysql' where keyc > 10000;
82 update t2 set c4 = 'mysql' where keyc > 10000;
83 update t2 set c5 = 'mysql' where keyc > 10000;
84 update t2 set c6 = 'mysql' where keyc > 10000;
85 drop PROCEDURE populate_t1;
86 drop PROCEDURE populate_t2;
87 drop table t1, t2;
88 SELECT NAME, SPACE_TYPE, STATE, AUTOEXTEND_SIZE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
89 WHERE SPACE_TYPE = 'Undo' ORDER BY NAME;
90 NAME SPACE_TYPE STATE AUTOEXTEND_SIZE
91 innodb_undo_001 Undo active 0
92 innodb_undo_002 Undo active 0
93 # Compare the undo tablespace sizes before and after DML growth.
94 # Do a slow shut down so the undo tablespaces can be truncated.
95 SET GLOBAL innodb_max_undo_log_size=20000000;
96 SET GLOBAL innodb_fast_shutdown=0;
97 SET GLOBAL innodb_purge_rseg_truncate_frequency=1;
98 SET GLOBAL innodb_purge_run_now=ON;
99 # restart:
100 # Compare the undo tablespace sizes before and after truncation.
101 SELECT NAME, SPACE_TYPE, STATE, AUTOEXTEND_SIZE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
102 WHERE SPACE_TYPE = 'Undo' ORDER BY NAME;
103 NAME SPACE_TYPE STATE AUTOEXTEND_SIZE
104 innodb_undo_001 Undo active 0
105 innodb_undo_002 Undo active 0
106 #
107 # Cleanup
108 #
109 SELECT NAME, SPACE_TYPE, STATE, AUTOEXTEND_SIZE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
110 WHERE SPACE_TYPE = 'Undo' ORDER BY NAME;
111 NAME SPACE_TYPE STATE AUTOEXTEND_SIZE
112 innodb_undo_001 Undo active 0
113 innodb_undo_002 Undo active 0