| 1 |
include/master-slave.inc |
| 2 |
Warnings: |
| 3 |
Note #### Sending passwords in plain text without SSL/TLS is extremely insecure. |
| 4 |
Note #### Storing MySQL user name or password information in the connection metadata repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START REPLICA; see the 'START REPLICA Syntax' in the MySQL Manual for more information. |
| 5 |
[connection master] |
| 6 |
|
| 7 |
Assert bounds of ndb_replica_batch_size |
| 8 |
|
| 9 |
set @@GLOBAL.ndb_replica_batch_size = -1; |
| 10 |
Warnings: |
| 11 |
Warning 1292 Truncated incorrect ndb_replica_batch_size value: '-1' |
| 12 |
show variables like 'ndb_replica_batch_size'; |
| 13 |
Variable_name Value |
| 14 |
ndb_replica_batch_size 0 |
| 15 |
set @@GLOBAL.ndb_replica_batch_size = 2147483649; |
| 16 |
Warnings: |
| 17 |
Warning 1292 Truncated incorrect ndb_replica_batch_size value: '2147483649' |
| 18 |
show variables like 'ndb_replica_batch_size'; |
| 19 |
Variable_name Value |
| 20 |
ndb_replica_batch_size 2147483648 |
| 21 |
set @@SESSION.ndb_replica_batch_size = 32768; |
| 22 |
ERROR HY000: Variable 'ndb_replica_batch_size' is a GLOBAL variable and should be set with SET GLOBAL |
| 23 |
set @@GLOBAL.ndb_replica_batch_size = DEFAULT; |
| 24 |
show variables like 'ndb_replica_batch_size'; |
| 25 |
Variable_name Value |
| 26 |
ndb_replica_batch_size 2097152 |
| 27 |
|
| 28 |
set @@GLOBAL.ndb_replica_batch_size = 32768; |
| 29 |
set global replica_allow_batching=ON; |
| 30 |
create table t1 (a int primary key, b varchar(7000)) |
| 31 |
engine=ndb; |
| 32 |
create table t2 (a int primary key, b varchar(7000)) |
| 33 |
engine=ndb; |
| 34 |
create table t3 (a int primary key, b varchar(7000)) |
| 35 |
engine=ndb; |
| 36 |
create table t4 (a int primary key, b varchar(7000)) |
| 37 |
engine=ndb; |
| 38 |
|
| 39 |
Record the replica's NDB API current wait count. |
| 40 |
Insert some wide data into the tables, synchronize |
| 41 |
and record how many more times NDB API waited for |
| 42 |
and primary key operations. |
| 43 |
|
| 44 |
begin; |
| 45 |
insert into t1 values (1, repeat("B", 7000)); |
| 46 |
insert into t2 values (2, repeat("A", 7000)); |
| 47 |
insert into t2 values (3, repeat("T", 7000)); |
| 48 |
insert into t3 values (4, repeat("C", 7000)); |
| 49 |
insert into t4 values (5, repeat("H", 7000)); |
| 50 |
insert into t1 values (6, repeat("B", 7000)); |
| 51 |
insert into t2 values (7, repeat("A", 7000)); |
| 52 |
insert into t2 values (8, repeat("T", 7000)); |
| 53 |
insert into t3 values (9, repeat("C", 7000)); |
| 54 |
insert into t4 values (0, repeat("H", 7000)); |
| 55 |
update t1 set b = repeat('S', 7000); |
| 56 |
update t2 set b = repeat('I', 7000); |
| 57 |
update t3 set b = repeat('Z', 7000); |
| 58 |
update t4 set b = repeat('E', 7000); |
| 59 |
delete from t1 where a = 1; |
| 60 |
delete from t4 where a in (5,0); |
| 61 |
commit; |
| 62 |
|
| 63 |
Record replica's API execute() calls and PK ops, and cleanup |
| 64 |
|
| 65 |
delete from t1; |
| 66 |
delete from t2; |
| 67 |
delete from t3; |
| 68 |
delete from t4; |
| 69 |
|
| 70 |
Increase replica's batch size for the binlog injector by 4-fold |
| 71 |
and perform the same operations. |
| 72 |
|
| 73 |
set @@GLOBAL.ndb_replica_batch_size = DEFAULT; |
| 74 |
begin; |
| 75 |
insert into t1 values (1, repeat("B", 7000)); |
| 76 |
insert into t2 values (2, repeat("A", 7000)); |
| 77 |
insert into t2 values (3, repeat("T", 7000)); |
| 78 |
insert into t3 values (4, repeat("C", 7000)); |
| 79 |
insert into t4 values (5, repeat("H", 7000)); |
| 80 |
insert into t1 values (6, repeat("B", 7000)); |
| 81 |
insert into t2 values (7, repeat("A", 7000)); |
| 82 |
insert into t2 values (8, repeat("T", 7000)); |
| 83 |
insert into t3 values (9, repeat("C", 7000)); |
| 84 |
insert into t4 values (0, repeat("H", 7000)); |
| 85 |
update t1 set b = repeat('S', 7000); |
| 86 |
update t2 set b = repeat('I', 7000); |
| 87 |
update t3 set b = repeat('Z', 7000); |
| 88 |
update t4 set b = repeat('E', 7000); |
| 89 |
delete from t1 where a = 1; |
| 90 |
delete from t4 where a in (5,0); |
| 91 |
commit; |
| 92 |
Observe that the number of execute() decrease significantly as well as the PK ops. |
| 93 |
Execute() calls should have decreased by, at least, a factor of 4. |
| 94 |
PK ops should have decreased by, at least, a factor of 2. |
| 95 |
SELECT @large_batch_exec_count < @low_batch_exec_count AS 'Large batch size execute calls are less'; |
| 96 |
Large batch size execute calls are less |
| 97 |
1 |
| 98 |
SELECT @large_batch_pk_op_count < @low_batch_pk_op_count AS 'Large batch size PK ops are less'; |
| 99 |
Large batch size PK ops are less |
| 100 |
1 |
| 101 |
SELECT @low_batch_exec_count / @large_batch_exec_count >= 4 AS 'Factor of >=4 for large batch execute calls'; |
| 102 |
Factor of >=4 for large batch execute calls |
| 103 |
1 |
| 104 |
SELECT @low_batch_pk_op_count / @large_batch_pk_op_count > 1 AS 'Factor of > 1 for large batch PK op'; |
| 105 |
Factor of > 1 for large batch PK op |
| 106 |
1 |
| 107 |
Cleanup |
| 108 |
set global ndb_replica_batch_size = DEFAULT; |
| 109 |
set global replica_allow_batching = DEFAULT; |
| 110 |
drop table t1; |
| 111 |
drop table t2; |
| 112 |
drop table t3; |
| 113 |
drop table t4; |
| 114 |
set global ndb_replica_batch_size = DEFAULT; |
| 115 |
set global replica_allow_batching = DEFAULT; |
| 116 |
include/rpl_end.inc |