Debian Codegraph @

mysql-test/suite/ndb/r/ndb_blob_batching.result

raw
1 set @saved_batch_size:=@@ndb_batch_size;
2 set @saved_blob_write_batch:=@@ndb_blob_write_batch_bytes;
3 set @saved_blob_read_batch:=@@ndb_blob_read_batch_bytes;
4 set @batch_sz = 8 * 1024 * 1024;
5 set global ndb_batch_size=@batch_sz;
6 set ndb_batch_size=@batch_sz;
7 set global ndb_blob_write_batch_bytes=@batch_sz;
8 set ndb_blob_write_batch_bytes=@batch_sz;
9 set global ndb_blob_read_batch_bytes=@batch_sz;
10 set ndb_blob_read_batch_bytes=@batch_sz;
11 create table t1 (a varchar(10) character set utf8mb4 primary key,
12 b text) engine=ndb;
13 create table t2 (a varchar(10) character set utf8mb4 primary key,
14 b text,
15 c text) engine=ndb;
16 create table tv (a varchar(10) character set utf8mb4 primary key,
17 b varbinary(5000)) engine=ndb;
18 Testing round trips with varying factors
19 rows - Number of rows affected by single statement
20 blobs - Number of blobs per row affected by single statement
21 autocommit - Whether autocommit or separate prepare, commit
22 parts - Whether the operation requires part rows ops or not
23 Update
24 blind/RMW - column value updated with no reference to existing
25 value
26 read-modify-write
27 - new column value is a function of existing value
28 *********************************************************
29 Basic varbinary batching tests showing baseline behaviour
30 *********************************************************
31 Single row
32 ----------
33 INSERT single row, no parts, autocommit
34 Expect 2 RTs
35 insert into tv values ("A", repeat("J", 20));;
36 round_trips_session
37 2
38 pk_ops_session
39 1
40 INSERT Single row, no parts, prepare only
41 Expect 1 RT
42 begin; insert into tv values ("A", repeat("J", 20));;
43 round_trips_session
44 1
45 pk_ops_session
46 1
47 UPDATE single row, no parts, autocommit, blind update
48 Expect 1 RT
49 update tv set b=repeat("Y",20) where a="Short1";;
50 round_trips_session
51 1
52 pk_ops_session
53 1
54 UPDATE single row, no parts, autocommit, read-modify-write
55 Expect 2 RTs
56 update tv set b=concat(b, "U") where a="Short1";;
57 round_trips_session
58 2
59 pk_ops_session
60 2
61 DELETE single row, no parts, autocommit
62 Expect 1 Rt
63 delete from tv where a="Short1";;
64 round_trips_session
65 1
66 pk_ops_session
67 1
68 DELETE single row, no parts, prepare only
69 Expect 1 Rt
70 begin; delete from tv where a="Short1";;
71 round_trips_session
72 1
73 pk_ops_session
74 1
75 Multi row
76 ---------
77 INSERT multi row, no parts, autocommit
78 Expect 1 RT
79 insert into tv values ("C", repeat("C", 20)), ("D", repeat("T", 20));;
80 round_trips_session
81 1
82 pk_ops_session
83 2
84 INSERT multi row, no parts, prepare only
85 Expect 1 Rt
86 begin; insert into tv values ("E", repeat("S", 20)), ("F", repeat("D", 20));;
87 round_trips_session
88 1
89 pk_ops_session
90 2
91 UPDATE multi row, no parts, autocommit, blind update
92 Expect 1 Rt
93 update tv set b=repeat("Y",20) where a in ("Short1", "Short2");;
94 round_trips_session
95 1
96 pk_ops_session
97 2
98 UPDATE multi row, no parts, autocommit, read-modify-write
99 Expect 2 Rts
100 update tv set b=concat(b, "M") where a in ("Short1", "Short2");;
101 round_trips_session
102 2
103 pk_ops_session
104 4
105 DELETE multi row, no parts, autocommit
106 Expect 1 Rt
107 delete from tv where a in ("Short1", "Short2");;
108 round_trips_session
109 1
110 pk_ops_session
111 2
112 DELETE multi row, no parts, prepare-only
113 Expect 1 Rt
114 begin; delete from tv where a in("Short1", "Short2");;
115 round_trips_session
116 1
117 pk_ops_session
118 2
119 *************************
120 Basic blob batching tests
121 *************************
122 -----------------------------
123 Single row, single blob tests
124 -----------------------------
125 INSERT single row, single blob, no parts, autocommit
126 Expect 3 Rts
127 insert into t1 values ("A", repeat("B", 20));;
128 round_trips_session
129 3
130 pk_ops_session
131 2
132 INSERT single row, single blob, parts, autocommit
133 Expect 3 Rts
134 insert into t1 values ("B", repeat("J", 5000));;
135 round_trips_session
136 3
137 pk_ops_session
138 5
139 INSERT single row, single blob, no parts, prepare only
140 Expect 2 RTs
141 begin; insert into t1 values ("C", repeat("C", 20));;
142 round_trips_session
143 2
144 pk_ops_session
145 2
146 INSERT single row, single blob, parts, prepare only
147 Expect 2 RTs
148 begin; insert into t1 values ("D", repeat("T", 40000));;
149 round_trips_session
150 2
151 pk_ops_session
152 22
153 UPDATE single row, single blob, no parts, autocommit, blind update
154 Expect 4 Rts
155 update t1 set b=repeat("Y",20) where a="Short1";;
156 round_trips_session
157 4
158 pk_ops_session
159 4
160 UPDATE single row, single blob, no parts, autocommit, read-modify-write
161 Expect 4 Rts
162 update t1 set b=concat(b, "U") where a="Short1";;
163 round_trips_session
164 4
165 pk_ops_session
166 4
167 UPDATE single row, single blob, parts, autocommit, blind update
168 Expect 4 Rts
169 update t1 set b=repeat("Y",40000) where a="Long1";;
170 round_trips_session
171 4
172 pk_ops_session
173 24
174 UPDATE single row, single blob, parts, autocommit, read-modify-write
175 Expect 5 RTs
176 update t1 set b=concat(b, "U") where a="Long1";;
177 round_trips_session
178 5
179 pk_ops_session
180 10
181 DELETE single row, single blob, no parts, autocommit
182 Expect 3 Rts
183 delete from t1 where a="Short1";;
184 round_trips_session
185 3
186 pk_ops_session
187 3
188 DELETE single row, single blob, parts, autocommit
189 Expect 3 Rts
190 delete from t1 where a="Long1";;
191 round_trips_session
192 3
193 pk_ops_session
194 6
195 ----------------------------
196 Single row, multi blob tests
197 ----------------------------
198 INSERT single row, multi blob, no parts, autocommit
199 Expect 3 Rts
200 insert into t2 values ("A", repeat("B", 20), repeat("B", 20));;
201 round_trips_session
202 3
203 pk_ops_session
204 3
205 INSERT single row, multi blob, parts, autocommit
206 Expect 3 Rts
207 insert into t2 values ("B", repeat("J", 5000), repeat("J", 5000));;
208 round_trips_session
209 3
210 pk_ops_session
211 9
212 INSERT single row, multi blob, no parts, prepare only
213 Expect 2 Rts
214 begin; insert into t2 values("C", repeat("C", 20), repeat("C", 20));;
215 round_trips_session
216 2
217 pk_ops_session
218 3
219 INSERT single row, multi blob, parts, prepare only
220 Expect 2 Rts
221 begin; insert into t2 values("D", repeat("T", 40000), repeat("C", 40000));;
222 round_trips_session
223 2
224 pk_ops_session
225 43
226 UPDATE single row, multi blob, no parts, autocommit, blind update
227 Expect 4 Rts
228 update t2 set b=repeat("Y",20), c=repeat("Y", 20) where a="Short1";;
229 round_trips_session
230 4
231 pk_ops_session
232 6
233 UPDATE single row, multi blob, no parts, autocommit, read-modify-write
234 Expect 4 Rts
235 update t2 set b=concat(b, "U"), c=concat(c, "U") where a="Short1";;
236 round_trips_session
237 4
238 pk_ops_session
239 6
240 UPDATE single row, multi blob, parts, autocommit, blind update
241 Expect 4 Rts
242 update t2 set b=repeat("Y",5000), c=repeat("Y", 5000) where a="Long1";;
243 round_trips_session
244 4
245 pk_ops_session
246 12
247 UPDATE single row, multi blob, parts, autocommit, read-modify-write
248 Expect 6 RTs
249 update t2 set b=concat(b, "U"), c=concat(c, "U") where a="Long1";;
250 round_trips_session
251 6
252 pk_ops_session
253 18
254 DELETE single row, multi blob, no parts, autocommit
255 Expect 3 Rts
256 delete from t2 where a="Short1";;
257 round_trips_session
258 3
259 pk_ops_session
260 4
261 DELETE single row, multi blob, parts, autocommit
262 Expect 4 Rts
263 delete from t2 where a="Long1";;
264 round_trips_session
265 3
266 pk_ops_session
267 10
268 ---------------
269 Multi row tests
270 ---------------
271 ----------------------------
272 Multi row, single blob tests
273 ----------------------------
274 INSERT multi row, single blob, no parts, autocommit
275 Expect 1 Rt
276 insert into t1 values ("A", repeat("B", 20)), ("B", repeat("J", 20));;
277 round_trips_session
278 1
279 pk_ops_session
280 4
281 INSERT multi row, single blob, parts, autocommit
282 Expect 1 Rt
283 insert into t1 values ("C", repeat("C", 5000)), ("D", repeat("T", 5000));;
284 round_trips_session
285 1
286 pk_ops_session
287 10
288 INSERT multi row, single blob, noparts, prepare only
289 Expect 2 Rts
290 begin; insert into t1 values ("E", repeat("B", 20)), ("F", repeat("J", 20));;
291 round_trips_session
292 2
293 pk_ops_session
294 4
295 INSERT multi row, single blob, parts, prepare only
296 Expect 2 Rts
297 begin; insert into t1 values ("G", repeat("C", 5000)),("H", repeat("T", 5000));;
298 round_trips_session
299 2
300 pk_ops_session
301 10
302 UPDATE multi row, single blob, no parts, autocommit, blind update
303 Expect 4 Rts
304 update t1 set b=repeat("Y", 20) where a in ("Short1", "Short2");;
305 round_trips_session
306 4
307 pk_ops_session
308 8
309 UPDATE multi row, single blob, no parts, autocommit, read-modify-write
310 Expect 6 Rts
311 update t1 set b=concat(b, "U") where a in ("Short1", "Short2");;
312 round_trips_session
313 6
314 pk_ops_session
315 8
316 UPDATE multi row, single blob, parts, autocommit, blind update
317 Expect 4 Rts
318 update t1 set b=repeat("M", 5000) where a in ("Long1", "Long2");;
319 round_trips_session
320 4
321 pk_ops_session
322 14
323 UPDATE multi row, single blob, parts, autocommit, read-modify-write
324 Expect 8 Rts
325 update t1 set b=concat(b, "M") where a in ("Long1", "Long2");;
326 round_trips_session
327 8
328 pk_ops_session
329 20
330 DELETE multi row, single blob, noparts, autocommit
331 expect 3 Rts
332 delete from t1 where a in ("Short1", "Short2");;
333 round_trips_session
334 3
335 pk_ops_session
336 6
337 DELETE multi row, single blob, parts, autocommit
338 expect 3 Rts
339 delete from t1 where a in ("Long1", "Long2");;
340 round_trips_session
341 3
342 pk_ops_session
343 12
344 ---------------------------
345 Multi row, multi blob tests
346 ---------------------------
347 INSERT multi row, multi blob, no parts, autocommit
348 Expect 1 Rt
349 insert into t2 values ("I", repeat("S", 20), repeat("S", 20)),("J", repeat("D", 20), repeat("D", 20));;
350 round_trips_session
351 1
352 pk_ops_session
353 6
354 INSERT multi row, multi blob, parts, autocommit
355 Expect 1 Rt
356 insert into t2 values ("K", repeat("G", 5000), repeat("G", 5000)),("L", repeat("!", 5000), repeat("!", 5000));;
357 round_trips_session
358 1
359 pk_ops_session
360 18
361 INSERT multi row, multi blob, no parts, prepare only
362 Expect 2 Rts
363 begin;insert into t2 values ("M", repeat("S", 20), repeat("S", 20)),("N", repeat("D", 20), repeat("D", 20));;
364 round_trips_session
365 2
366 pk_ops_session
367 6
368 INSERT multi row, multi blob, parts, prepare only
369 Expect 2 Rts
370 begin;insert into t2 values ("O", repeat("G", 5000), repeat("G", 5000)),("P", repeat("!", 5000), repeat("!", 5000));;
371 round_trips_session
372 2
373 pk_ops_session
374 18
375 UPDATE multi row, multi blob, no parts, autocommit, blind update
376 Expect 4 Rts
377 update t2 set b=repeat("Y", 20), c= repeat("U", 20) where a in ("Short1", "Short2");;
378 round_trips_session
379 4
380 pk_ops_session
381 12
382 UPDATE multi row, multi blob, no parts, autocommit, read-modify-write
383 Expect 6 Rts
384 update t2 set b=concat(b, "M"), c= concat(b, "M") where a in ("Short1", "Short2");;
385 round_trips_session
386 6
387 pk_ops_session
388 12
389 UPDATE multi row, multi blob, parts, autocommit, blind update
390 Expect 4 Rts
391 update t2 set b=repeat("Y", 5000), c= repeat("U", 5000) where a in ("Long1", "Long2");;
392 round_trips_session
393 4
394 pk_ops_session
395 24
396 UPDATE multi row, multi blob, parts, autocommit, read-modify-write
397 Expect 8 Rts
398 update t2 set b=concat(b, "M"), c= concat(b, "M") where a in ("Long1", "Long2");;
399 round_trips_session
400 8
401 pk_ops_session
402 30
403 DELETE multi row, multi blob, no parts, autocommit
404 Expect 3 Rts
405 delete from t2 where a in ("Short1", "Short2");;
406 round_trips_session
407 3
408 pk_ops_session
409 8
410 DELETE multi row, multi blob, parts, autocommit
411 Expect 3 Rts
412 delete from t2 where a in ("Long1", "Long2");;
413 round_trips_session
414 3
415 pk_ops_session
416 20
417 drop table t1;
418 drop table t2;
419 drop table tv;
420 ****************************************
421 Testing of various bulk insert scenarios
422 ****************************************
423 Create table with blob content on InnoDB, which can be scanned
424 without any Ndb round trips
425 create table ta (a int primary key, b blob, c blob) engine=innodb;
426 *********************
427 Test LOAD DATA INFILE
428 *********************
429 select * into outfile 'MYSQLD_DATADIR/ta_data' from test.ta;;
430 Varbinary Load Data Infile
431 create table ti (a int primary key, b varbinary(5000), c varbinary(5000)) engine=ndb;
432 load data infile 'MYSQLD_DATADIR/ta_data' into table ti;;
433 round_trips_session
434 1
435 pk_ops_session
436 40
437 drop table ti;
438 Blob Load Data Infile
439 create table ti (a int primary key, b blob, c blob) engine=ndb;
440 load data infile 'MYSQLD_DATADIR/ta_data' into table ti;;
441 round_trips_session
442 1
443 pk_ops_session
444 360
445 drop table ti;
446 ***************************
447 Test Create Table As Select
448 ***************************
449 Varbinary CTAS
450 create table tn (a int primary key, b varbinary(5000), c varbinary(5000)) engine=ndb
451 as select * from ta;
452 round_trips_session
453 7
454 pk_ops_session
455 45
456 drop table tn;
457 Blob CTAS
458 create table tn (a int primary key, b blob, c blob) engine=ndb
459 as select * from ta;
460 round_trips_session
461 7
462 pk_ops_session
463 364
464 drop table tn;
465 *****************************
466 Test ALTER engine bulk insert
467 *****************************
468 create table tb (a int primary key, b varbinary(5000), c varbinary(5000)) engine=innodb
469 as select * from ta;
470 Varbinary ALTER
471 alter table tb engine=ndb;
472 round_trips_session
473 6
474 pk_ops_session
475 45
476 drop table tb;
477 Blob ALTER
478 alter table ta engine=ndb;
479 round_trips_session
480 6
481 pk_ops_session
482 365
483 drop table ta;
484 **************
485 Test JSON type
486 **************
487 Single JSON columns in row
488 --------------------------
489 create table js1 (a int primary key, b json) engine=ndb;
490 Single row JSON insert autocommit.
491 Expect 3 RTs
492 round_trips_session
493 3
494 pk_ops_session
495 7
496 Multi row JSON insert autocommit
497 Expect 1 RT
498 round_trips_session
499 1
500 pk_ops_session
501 21
502 Single row JSON update autocommit
503 Expect 4 RTs
504 round_trips_session
505 4
506 pk_ops_session
507 9
508 Multi row JSON update autocommit
509 Expect 4 RTs
510 round_trips_session
511 4
512 pk_ops_session
513 27
514 Single row JSON delete autocommit
515 Expect 3 RTs
516 delete from js1 where a=1;
517 round_trips_session
518 3
519 pk_ops_session
520 8
521 Expect 3RTs
522 delete from js1 where a in (2,3,4);
523 round_trips_session
524 3
525 pk_ops_session
526 24
527 drop table js1;
528 Multiple JSON columns in row
529 ----------------------------
530 create table js2 (a int primary key, b json, c json) engine=ndb;
531 Single row JSON insert autocommit.
532 Expect 3 RTs
533 round_trips_session
534 3
535 pk_ops_session
536 13
537 Multi row JSON insert autocommit
538 Expect 1 RT
539 round_trips_session
540 1
541 pk_ops_session
542 39
543 Single row JSON update autocommit
544 Expect 4 RTs
545 round_trips_session
546 4
547 pk_ops_session
548 16
549 Multi row JSON update autocommit
550 Expect 4 RTs
551 round_trips_session
552 4
553 pk_ops_session
554 48
555 Single row JSON delete autocommit
556 Expect 3 RTs
557 delete from js2 where a=1;
558 round_trips_session
559 3
560 pk_ops_session
561 14
562 Expect 3RTs
563 delete from js2 where a in (2,3,4);
564 round_trips_session
565 3
566 pk_ops_session
567 42
568 drop table js2;
569 set global ndb_batch_size=@saved_batch_size;
570 set ndb_batch_size=@saved_batch_size;
571 set global ndb_blob_write_batch_bytes=@saved_blob_write_batch;
572 set ndb_blob_write_batch_bytes=@saved_blob_write_batch;
573 set global ndb_blob_read_batch_bytes=@saved_blob_read_batch;
574 set ndb_blob_read_batch_bytes=@saved_blob_read_batch;