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
|
# A rhino can weigh over 2000 kg, run about 12 meters per second,
# but see accurately only 10 meters...
# A group of rhinos is called a 'crash'!
#
# InnoDB should make the way wide and smooth for undo truncation
# to occur while dml is happening.
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 DATABASE africa;
USE africa;
CREATE TABLE serengeti (a INT PRIMARY KEY, b TEXT);
DELIMITER |;
# Offset the timing of each thread's DDL.
CREATE PROCEDURE crash (IN loops INT, IN undo_number INT)
BEGIN
SET @undo_num = undo_number;
SET @before = 5 * (@undo_num - 2);
SET @during = 25;
SET @after = 25 - @before;
SET @all = @before + @during + @after;
PREPARE stmt_ins FROM "INSERT INTO serengeti VALUES (?, ?)";
SET @a = ((@undo_num - 2) * 10000) + 1;
SET @n = 0;
WHILE (@n < @all) DO
SET @b = concat("Insert #", @a);
EXECUTE stmt_ins USING @a, @b;
SET @a = @a + 1;
SET @n = @n + 1;
END WHILE;
PREPARE stmt FROM "UPDATE serengeti SET b = ? WHERE a = ?";
SET @loop = 0;
WHILE (@loop < loops) DO
SET @a = ((@undo_num - 2) * 10000) + 1;
SET @n = 0;
WHILE (@n < @before) DO
SET @b = concat("Update #", @a, ", loop ", @loop, " BEFORE undo_00", @undo_num, " is SET INACTIVE");
EXECUTE stmt USING @b, @a;
SET @a = @a + 1;
SET @n = @n + 1;
END WHILE;
IF (@undo_num = 3) THEN
ALTER UNDO TABLESPACE undo_003 SET INACTIVE;
END IF;
IF (@undo_num = 4) THEN
ALTER UNDO TABLESPACE undo_004 SET INACTIVE;
END IF;
IF (@undo_num = 5) THEN
ALTER UNDO TABLESPACE undo_005 SET INACTIVE;
END IF;
IF (@undo_num = 6) THEN
ALTER UNDO TABLESPACE undo_006 SET INACTIVE;
END IF;
SET @n = 0;
WHILE (@n < @during) DO
SET @b = concat("Update #", @a, ", loop ", @loop, " WHILE undo_00", @undo_num, " is SET INACTIVE");
EXECUTE stmt USING @b, @a;
SET @a = @a + 1;
SET @n = @n + 1;
END WHILE;
IF (@undo_num = 3) THEN
ALTER UNDO TABLESPACE undo_003 SET ACTIVE;
END IF;
IF (@undo_num = 4) THEN
ALTER UNDO TABLESPACE undo_004 SET ACTIVE;
END IF;
IF (@undo_num = 5) THEN
ALTER UNDO TABLESPACE undo_005 SET ACTIVE;
END IF;
IF (@undo_num = 6) THEN
ALTER UNDO TABLESPACE undo_006 SET ACTIVE;
END IF;
SET @n = 0;
WHILE (@n < @after) DO
SET @b = concat("Update #", @a, ", loop ", @loop, " AFTER undo_00", @undo_num, " is SET ACTIVE");
EXECUTE stmt USING @b, @a;
SET @a = @a + 1;
SET @n = @n + 1;
END WHILE;
SET @loop = @loop + 1;
END WHILE;
END|
DELIMITER ;|
connect (con1,localhost,root,,);
connect (con2,localhost,root,,);
connect (con3,localhost,root,,);
connect (con4,localhost,root,,);
connection con1;
--send CALL africa.crash(10, 3);
connection con2;
--send CALL africa.crash(10, 4);
connection con3;
--send CALL africa.crash(10, 5);
connection con4;
--send CALL africa.crash(10, 6);
connection con1;
--reap;
connection con2;
--reap;
connection con3;
--reap;
connection con4;
--reap;
connection default;
disconnect con1;
disconnect con2;
disconnect con3;
disconnect con4;
SELECT count(*) from serengeti;
USE test;
DROP DATABASE africa;
ALTER UNDO TABLESPACE undo_003 SET INACTIVE;
let $inactive_undo_space = undo_003;
source include/wait_until_undo_space_is_empty.inc;
DROP UNDO TABLESPACE undo_003;
ALTER UNDO TABLESPACE undo_004 SET INACTIVE;
let $inactive_undo_space = undo_004;
source include/wait_until_undo_space_is_empty.inc;
DROP UNDO TABLESPACE undo_004;
ALTER UNDO TABLESPACE undo_005 SET INACTIVE;
let $inactive_undo_space = undo_005;
source include/wait_until_undo_space_is_empty.inc;
DROP UNDO TABLESPACE undo_005;
ALTER UNDO TABLESPACE undo_006 SET INACTIVE;
let $inactive_undo_space = undo_006;
source include/wait_until_undo_space_is_empty.inc;
DROP UNDO TABLESPACE undo_006;
|