Debian Codegraph @

mysql-test/suite/clone/r/remote_dml_auto_tune.result

raw
1 CREATE UNDO TABLESPACE undo_003 ADD DATAFILE 'undo_003.ibu';
2 CREATE UNDO TABLESPACE undo_004 ADD DATAFILE 'undo_004.ibu';
3 SELECT NAME, SPACE_TYPE, STATE
4 FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
5 WHERE SPACE_TYPE = 'Undo' ORDER BY NAME;
6 NAME SPACE_TYPE STATE
7 innodb_undo_001 Undo active
8 innodb_undo_002 Undo active
9 undo_003 Undo active
10 undo_004 Undo active
11 SELECT TABLESPACE_NAME, FILE_NAME, FILE_TYPE, ENGINE
12 FROM INFORMATION_SCHEMA.FILES
13 WHERE FILE_NAME LIKE '%undo_00%'
14 ORDER BY TABLESPACE_NAME;
15 TABLESPACE_NAME FILE_NAME FILE_TYPE ENGINE
16 innodb_undo_001 ./undo_001 UNDO LOG InnoDB
17 innodb_undo_002 ./undo_002 UNDO LOG InnoDB
18 undo_003 ./undo_003.ibu UNDO LOG InnoDB
19 undo_004 ./undo_004.ibu UNDO LOG InnoDB
20 INSTALL PLUGIN clone SONAME 'CLONE_PLUGIN';
21 SHOW VARIABLES LIKE "clone_buffer_size";
22 Variable_name Value
23 clone_buffer_size 4194304
24 SET GLOBAL clone_buffer_size = 2097152;
25 SHOW VARIABLES LIKE "clone_buffer_size";
26 Variable_name Value
27 clone_buffer_size 2097152
28 CREATE TABLE t1(col1 INT PRIMARY KEY, col2 int, col3 varchar(64), col4 BLOB);
29 CREATE TABLE t2(col1 INT PRIMARY KEY, col2 int, col3 varchar(64), col4 BLOB)
30 PARTITION BY KEY(col1) PARTITIONS 5;
31 CREATE PROCEDURE execute_dml(
32 p_dml_type INT,
33 p_key_min INT,
34 p_key_range INT,
35 p_loop_count INT,
36 p_frequency INT,
37 p_is_rand INT)
38 BEGIN
39 DECLARE v_idx INT DEFAULT 0;
40 DECLARE v_commit INT DEFAULT 0;
41 DECLARE v_key INT DEFAULT 0;
42 /* Loop and INSERT data at random position */
43 WHILE(v_idx < p_loop_count) DO
44 /* Generate key between 1 to p_loop_count */
45 IF p_is_rand = 1 THEN
46 SET v_key = p_key_min + FLOOR(RAND() * p_key_range);
47 ELSE
48 SET v_key = p_key_min + (v_idx % p_key_range);
49 END IF;
50 CASE p_dml_type
51 WHEN 0 THEN
52 SET @clol3_text = CONCAT('Clone Test Row - ', v_key);
53 INSERT INTO t1 (col1, col2, col3, col4) VALUES (
54 v_key, v_key * 10,
55 @clol3_text, REPEAT('Large Column Data ', 2048))
56 ON DUPLICATE KEY UPDATE col2 = col2 + 1;
57 INSERT INTO t2 (col1, col2, col3, col4) VALUES (
58 v_key, v_key * 10,
59 @clol3_text, REPEAT('Large Column Data ', 2048))
60 ON DUPLICATE KEY UPDATE col2 = col2 + 1;
61 WHEN 1 THEN
62 UPDATE t1 SET col2 = v_idx + 1 WHERE col1 = v_key;
63 UPDATE t2 SET col2 = v_idx + 1 WHERE col1 = v_key;
64 WHEN 2 THEN
65 DELETE FROM t1 WHERE col1 = v_key;
66 DELETE FROM t2 WHERE col1 = v_key;
67 ELSE
68 DELETE FROM t1;
69 DELETE FROM t2;
70 END CASE;
71 SET v_idx = v_idx + 1;
72 /* Commit or rollback work at specified frequency. */
73 IF v_idx % p_frequency = 0 THEN
74 SET v_commit = FLOOR(RAND() * 2);
75 IF v_commit = 0 AND p_is_rand = 1 THEN
76 ROLLBACK;
77 START TRANSACTION;
78 ELSE
79 COMMIT;
80 START TRANSACTION;
81 END IF;
82 END IF;
83 END WHILE;
84 COMMIT;
85 END|
86 call execute_dml(0, 0, 1000, 1000, 10, 0);
87 SHOW CREATE TABLE t1;
88 Table Create Table
89 t1 CREATE TABLE `t1` (
90 `col1` int NOT NULL,
91 `col2` int DEFAULT NULL,
92 `col3` varchar(64) DEFAULT NULL,
93 `col4` blob,
94 PRIMARY KEY (`col1`)
95 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
96 SELECT count(*) from t1;
97 count(*)
98 1000
99 SELECT col1, col2, col3 FROM t1 ORDER BY col1 LIMIT 10;
100 col1 col2 col3
101 0 0 Clone Test Row - 0
102 1 10 Clone Test Row - 1
103 2 20 Clone Test Row - 2
104 3 30 Clone Test Row - 3
105 4 40 Clone Test Row - 4
106 5 50 Clone Test Row - 5
107 6 60 Clone Test Row - 6
108 7 70 Clone Test Row - 7
109 8 80 Clone Test Row - 8
110 9 90 Clone Test Row - 9
111 SELECT col1, col2, col3 FROM t1 ORDER BY col1 DESC LIMIT 10;
112 col1 col2 col3
113 999 9990 Clone Test Row - 999
114 998 9980 Clone Test Row - 998
115 997 9970 Clone Test Row - 997
116 996 9960 Clone Test Row - 996
117 995 9950 Clone Test Row - 995
118 994 9940 Clone Test Row - 994
119 993 9930 Clone Test Row - 993
120 992 9920 Clone Test Row - 992
121 991 9910 Clone Test Row - 991
122 990 9900 Clone Test Row - 990
123 SHOW CREATE TABLE t2;
124 Table Create Table
125 t2 CREATE TABLE `t2` (
126 `col1` int NOT NULL,
127 `col2` int DEFAULT NULL,
128 `col3` varchar(64) DEFAULT NULL,
129 `col4` blob,
130 PRIMARY KEY (`col1`)
131 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
132 /*!50100 PARTITION BY KEY (col1)
133 PARTITIONS 5 */
134 SELECT count(*) from t2;
135 count(*)
136 1000
137 SELECT col1, col2, col3 FROM t2 ORDER BY col1 LIMIT 10;
138 col1 col2 col3
139 0 0 Clone Test Row - 0
140 1 10 Clone Test Row - 1
141 2 20 Clone Test Row - 2
142 3 30 Clone Test Row - 3
143 4 40 Clone Test Row - 4
144 5 50 Clone Test Row - 5
145 6 60 Clone Test Row - 6
146 7 70 Clone Test Row - 7
147 8 80 Clone Test Row - 8
148 9 90 Clone Test Row - 9
149 SELECT col1, col2, col3 FROM t2 ORDER BY col1 DESC LIMIT 10;
150 col1 col2 col3
151 999 9990 Clone Test Row - 999
152 998 9980 Clone Test Row - 998
153 997 9970 Clone Test Row - 997
154 996 9960 Clone Test Row - 996
155 995 9950 Clone Test Row - 995
156 994 9940 Clone Test Row - 994
157 993 9930 Clone Test Row - 993
158 992 9920 Clone Test Row - 992
159 991 9910 Clone Test Row - 991
160 990 9900 Clone Test Row - 990
161 # In connection con1 - Running Insert Random [100 - 200 Key range]
162 call execute_dml(0, 100, 100, 100, 20, 1);
163 # In connection con2 - Running Update Random [0 - 25 Key Range]
164 call execute_dml(1, 0, 25, 100, 20, 1);
165 # In connection con3 - Running Delete Random [26 - 50 Key Range]
166 call execute_dml(2, 26, 25, 100, 20, 1);
167 # In connection con4 - Running Update Random uncommited [51 - 75 Key Range]
168 begin;
169 call execute_dml(1, 51, 25, 100, 200, 1);
170 # In connection default - Cloning database
171 SET GLOBAL clone_max_data_bandwidth = 5;
172 SET GLOBAL clone_max_network_bandwidth = 4;
173 SET GLOBAL clone_valid_donor_list = 'HOST:PORT';
174 CLONE INSTANCE FROM USER@HOST:PORT IDENTIFIED BY '' DATA DIRECTORY = 'CLONE_DATADIR';
175 select ID, STATE, ERROR_NO from performance_schema.clone_status;
176 ID STATE ERROR_NO
177 1 Completed 0
178 select ID, STAGE, STATE from performance_schema.clone_progress;
179 ID STAGE STATE
180 1 DROP DATA Completed
181 1 FILE COPY Completed
182 1 PAGE COPY Completed
183 1 REDO COPY Completed
184 1 FILE SYNC Completed
185 1 RESTART Not Started
186 1 RECOVERY Not Started
187 # In connection default - Finished Cloning
188 show variables like 'clone_autotune_concurrency';
189 Variable_name Value
190 clone_autotune_concurrency ON
191 # In connection con1 - Waiting
192 # In connection con2 - Waiting
193 # In connection con3 - Waiting
194 # In connection con4 - Waiting
195 commit;
196 # In connection default
197 # Restart cloned database
198 # restart: --datadir=CLONE_DATADIR
199 select STATE, ERROR_NO, length(BINLOG_FILE) > 0, length(GTID_EXECUTED) > 0,
200 (BINLOG_POSITION > 0) from performance_schema.clone_status;
201 STATE ERROR_NO length(BINLOG_FILE) > 0 length(GTID_EXECUTED) > 0 (BINLOG_POSITION > 0)
202 Completed 0 1 0 1
203 call execute_dml(3, 0, 1, 1, 1, 0);
204 call execute_dml(0, 0, 2000, 2000, 10, 0);
205 SHOW CREATE TABLE t1;
206 Table Create Table
207 t1 CREATE TABLE `t1` (
208 `col1` int NOT NULL,
209 `col2` int DEFAULT NULL,
210 `col3` varchar(64) DEFAULT NULL,
211 `col4` blob,
212 PRIMARY KEY (`col1`)
213 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
214 SELECT count(*) from t1;
215 count(*)
216 2000
217 SELECT col1, col2, col3 FROM t1 ORDER BY col1 LIMIT 10;
218 col1 col2 col3
219 0 0 Clone Test Row - 0
220 1 10 Clone Test Row - 1
221 2 20 Clone Test Row - 2
222 3 30 Clone Test Row - 3
223 4 40 Clone Test Row - 4
224 5 50 Clone Test Row - 5
225 6 60 Clone Test Row - 6
226 7 70 Clone Test Row - 7
227 8 80 Clone Test Row - 8
228 9 90 Clone Test Row - 9
229 SELECT col1, col2, col3 FROM t1 ORDER BY col1 DESC LIMIT 10;
230 col1 col2 col3
231 1999 19990 Clone Test Row - 1999
232 1998 19980 Clone Test Row - 1998
233 1997 19970 Clone Test Row - 1997
234 1996 19960 Clone Test Row - 1996
235 1995 19950 Clone Test Row - 1995
236 1994 19940 Clone Test Row - 1994
237 1993 19930 Clone Test Row - 1993
238 1992 19920 Clone Test Row - 1992
239 1991 19910 Clone Test Row - 1991
240 1990 19900 Clone Test Row - 1990
241 SHOW CREATE TABLE t2;
242 Table Create Table
243 t2 CREATE TABLE `t2` (
244 `col1` int NOT NULL,
245 `col2` int DEFAULT NULL,
246 `col3` varchar(64) DEFAULT NULL,
247 `col4` blob,
248 PRIMARY KEY (`col1`)
249 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
250 /*!50100 PARTITION BY KEY (col1)
251 PARTITIONS 5 */
252 SELECT count(*) from t2;
253 count(*)
254 2000
255 SELECT col1, col2, col3 FROM t2 ORDER BY col1 LIMIT 10;
256 col1 col2 col3
257 0 0 Clone Test Row - 0
258 1 10 Clone Test Row - 1
259 2 20 Clone Test Row - 2
260 3 30 Clone Test Row - 3
261 4 40 Clone Test Row - 4
262 5 50 Clone Test Row - 5
263 6 60 Clone Test Row - 6
264 7 70 Clone Test Row - 7
265 8 80 Clone Test Row - 8
266 9 90 Clone Test Row - 9
267 SELECT col1, col2, col3 FROM t2 ORDER BY col1 DESC LIMIT 10;
268 col1 col2 col3
269 1999 19990 Clone Test Row - 1999
270 1998 19980 Clone Test Row - 1998
271 1997 19970 Clone Test Row - 1997
272 1996 19960 Clone Test Row - 1996
273 1995 19950 Clone Test Row - 1995
274 1994 19940 Clone Test Row - 1994
275 1993 19930 Clone Test Row - 1993
276 1992 19920 Clone Test Row - 1992
277 1991 19910 Clone Test Row - 1991
278 1990 19900 Clone Test Row - 1990
279 SELECT NAME, SPACE_TYPE, STATE
280 FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
281 WHERE SPACE_TYPE = 'Undo';
282 NAME SPACE_TYPE STATE
283 innodb_undo_001 Undo active
284 innodb_undo_002 Undo active
285 undo_003 Undo active
286 undo_004 Undo active
287 SELECT TABLESPACE_NAME, FILE_NAME, FILE_TYPE, ENGINE
288 FROM INFORMATION_SCHEMA.FILES
289 WHERE FILE_NAME LIKE '%undo_00%';
290 TABLESPACE_NAME FILE_NAME FILE_TYPE ENGINE
291 innodb_undo_001 ./undo_001 UNDO LOG InnoDB
292 innodb_undo_002 ./undo_002 UNDO LOG InnoDB
293 undo_003 ./undo_003.ibu UNDO LOG InnoDB
294 undo_004 ./undo_004.ibu UNDO LOG InnoDB
295 SET GLOBAL clone_valid_donor_list = 'HOST:PORT';
296 CLONE INSTANCE FROM USER@HOST:PORT IDENTIFIED BY '' DATA DIRECTORY = 'CLONE_DATADIR';
297 select ID, STATE, ERROR_NO from performance_schema.clone_status;
298 ID STATE ERROR_NO
299 1 Completed 0
300 select ID, STAGE, STATE from performance_schema.clone_progress;
301 ID STAGE STATE
302 1 DROP DATA Completed
303 1 FILE COPY Completed
304 1 PAGE COPY Completed
305 1 REDO COPY Completed
306 1 FILE SYNC Completed
307 1 RESTART Not Started
308 1 RECOVERY Not Started
309 # Restart cloned database
310 # restart: --datadir=CLONE_DATADIR1
311 select STATE, ERROR_NO, length(BINLOG_FILE) > 0, length(GTID_EXECUTED) > 0,
312 (BINLOG_POSITION > 0) from performance_schema.clone_status;
313 STATE ERROR_NO length(BINLOG_FILE) > 0 length(GTID_EXECUTED) > 0 (BINLOG_POSITION > 0)
314 Completed 0 1 0 1
315 call execute_dml(3, 0, 1, 1, 1, 0);
316 call execute_dml(0, 0, 100, 100, 10, 0);
317 SHOW CREATE TABLE t1;
318 Table Create Table
319 t1 CREATE TABLE `t1` (
320 `col1` int NOT NULL,
321 `col2` int DEFAULT NULL,
322 `col3` varchar(64) DEFAULT NULL,
323 `col4` blob,
324 PRIMARY KEY (`col1`)
325 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
326 SELECT count(*) from t1;
327 count(*)
328 100
329 SELECT col1, col2, col3 FROM t1 ORDER BY col1 LIMIT 10;
330 col1 col2 col3
331 0 0 Clone Test Row - 0
332 1 10 Clone Test Row - 1
333 2 20 Clone Test Row - 2
334 3 30 Clone Test Row - 3
335 4 40 Clone Test Row - 4
336 5 50 Clone Test Row - 5
337 6 60 Clone Test Row - 6
338 7 70 Clone Test Row - 7
339 8 80 Clone Test Row - 8
340 9 90 Clone Test Row - 9
341 SELECT col1, col2, col3 FROM t1 ORDER BY col1 DESC LIMIT 10;
342 col1 col2 col3
343 99 990 Clone Test Row - 99
344 98 980 Clone Test Row - 98
345 97 970 Clone Test Row - 97
346 96 960 Clone Test Row - 96
347 95 950 Clone Test Row - 95
348 94 940 Clone Test Row - 94
349 93 930 Clone Test Row - 93
350 92 920 Clone Test Row - 92
351 91 910 Clone Test Row - 91
352 90 900 Clone Test Row - 90
353 SHOW CREATE TABLE t2;
354 Table Create Table
355 t2 CREATE TABLE `t2` (
356 `col1` int NOT NULL,
357 `col2` int DEFAULT NULL,
358 `col3` varchar(64) DEFAULT NULL,
359 `col4` blob,
360 PRIMARY KEY (`col1`)
361 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
362 /*!50100 PARTITION BY KEY (col1)
363 PARTITIONS 5 */
364 SELECT count(*) from t2;
365 count(*)
366 100
367 SELECT col1, col2, col3 FROM t2 ORDER BY col1 LIMIT 10;
368 col1 col2 col3
369 0 0 Clone Test Row - 0
370 1 10 Clone Test Row - 1
371 2 20 Clone Test Row - 2
372 3 30 Clone Test Row - 3
373 4 40 Clone Test Row - 4
374 5 50 Clone Test Row - 5
375 6 60 Clone Test Row - 6
376 7 70 Clone Test Row - 7
377 8 80 Clone Test Row - 8
378 9 90 Clone Test Row - 9
379 SELECT col1, col2, col3 FROM t2 ORDER BY col1 DESC LIMIT 10;
380 col1 col2 col3
381 99 990 Clone Test Row - 99
382 98 980 Clone Test Row - 98
383 97 970 Clone Test Row - 97
384 96 960 Clone Test Row - 96
385 95 950 Clone Test Row - 95
386 94 940 Clone Test Row - 94
387 93 930 Clone Test Row - 93
388 92 920 Clone Test Row - 92
389 91 910 Clone Test Row - 91
390 90 900 Clone Test Row - 90
391 # restart:
392 ALTER UNDO TABLESPACE undo_003 SET INACTIVE;
393 DROP UNDO TABLESPACE undo_003;
394 ALTER UNDO TABLESPACE undo_004 SET INACTIVE;
395 DROP UNDO TABLESPACE undo_004;
396 SELECT NAME, SPACE_TYPE, STATE
397 FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
398 WHERE SPACE_TYPE = 'Undo' ORDER BY NAME;
399 NAME SPACE_TYPE STATE
400 innodb_undo_001 Undo active
401 innodb_undo_002 Undo active
402 SELECT TABLESPACE_NAME, FILE_NAME, FILE_TYPE, ENGINE
403 FROM INFORMATION_SCHEMA.FILES
404 WHERE FILE_NAME LIKE '%undo_00%'
405 ORDER BY TABLESPACE_NAME;
406 TABLESPACE_NAME FILE_NAME FILE_TYPE ENGINE
407 innodb_undo_001 ./undo_001 UNDO LOG InnoDB
408 innodb_undo_002 ./undo_002 UNDO LOG InnoDB
409 DROP TABLE t1;
410 DROP TABLE t2;
411 DROP PROCEDURE execute_dml;
412 UNINSTALL PLUGIN clone;