| 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 |
show variables like 'replica_allow_batching'; |
| 7 |
Variable_name Value |
| 8 |
replica_allow_batching ON |
| 9 |
set @s_batch_size:=@@ndb_batch_size; |
| 10 |
set @s_blob_write_batch:=@@ndb_blob_write_batch_bytes; |
| 11 |
set @s_blob_read_batch:=@@ndb_blob_read_batch_bytes; |
| 12 |
set @batch_sz = 8 * 1024 * 1024; |
| 13 |
set global ndb_batch_size = @batch_sz; |
| 14 |
set ndb_batch_size = @batch_sz; |
| 15 |
set global ndb_blob_write_batch_bytes = @batch_sz; |
| 16 |
set ndb_blob_write_batch_bytes = @batch_sz; |
| 17 |
set global ndb_blob_read_batch_bytes = @batch_sz; |
| 18 |
set ndb_blob_read_batch_bytes = @batch_sz; |
| 19 |
show variables like 'ndb_log_update_as_write'; |
| 20 |
Variable_name Value |
| 21 |
ndb_log_update_as_write ON |
| 22 |
|
| 23 |
Bulk insert (20 x ~3kb) |
| 24 |
----------------------- |
| 25 |
Varchar table |
| 26 |
round_trips_slave 1 |
| 27 |
pk_ops_slave 21 |
| 28 |
|
| 29 |
1 Blob table |
| 30 |
round_trips_slave 3 |
| 31 |
pk_ops_slave 121 |
| 32 |
|
| 33 |
2 Blob table |
| 34 |
round_trips_slave 3 |
| 35 |
pk_ops_slave 221 |
| 36 |
|
| 37 |
|
| 38 |
Bulk update (20 x ~3kB) |
| 39 |
--------------------------------- |
| 40 |
begin; |
| 41 |
update tv set b=repeat('J', 3000) where a in |
| 42 |
(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20); |
| 43 |
commit; |
| 44 |
Varchar table |
| 45 |
round_trips_slave 1 |
| 46 |
pk_ops_slave 21 |
| 47 |
|
| 48 |
begin; |
| 49 |
update tb1 set b=repeat('J', 3000) where a in |
| 50 |
(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20); |
| 51 |
commit; |
| 52 |
1 Blob table |
| 53 |
round_trips_slave 2 |
| 54 |
pk_ops_slave 101 |
| 55 |
|
| 56 |
begin; |
| 57 |
update tb2 set b=repeat('J', 3000), c=repeat('C', 3000) where a in |
| 58 |
(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20); |
| 59 |
commit; |
| 60 |
2 Blob table |
| 61 |
round_trips_slave 2 |
| 62 |
pk_ops_slave 181 |
| 63 |
|
| 64 |
|
| 65 |
Mixed batch (6 x ~3kB update, 5x ~1kB delete, 5x ~3kB insert all independent) |
| 66 |
----------------------------------------------------------------------------- |
| 67 |
As all are independent, should get maximal batching |
| 68 |
begin; |
| 69 |
update tv set b=repeat('C', 3000) where a in (15,16,17,18,19,20); |
| 70 |
delete from tv where a in (1,2,3,4,5); |
| 71 |
insert into tv values (21, repeat('B', 3000)); |
| 72 |
insert into tv values (22, repeat('B', 3000)); |
| 73 |
insert into tv values (23, repeat('B', 3000)); |
| 74 |
insert into tv values (24, repeat('B', 3000)); |
| 75 |
insert into tv values (25, repeat('B', 3000)); |
| 76 |
commit; |
| 77 |
Varchar table |
| 78 |
round_trips_slave 1 |
| 79 |
pk_ops_slave 17 |
| 80 |
|
| 81 |
begin; |
| 82 |
update tb1 set b=repeat('C', 3000) where a in (15,16,17,18,19,20); |
| 83 |
delete from tb1 where a in (1,2,3,4,5); |
| 84 |
insert into tb1 values (21, repeat('B', 3000)); |
| 85 |
insert into tb1 values (22, repeat('B', 3000)); |
| 86 |
insert into tb1 values (23, repeat('B', 3000)); |
| 87 |
insert into tb1 values (24, repeat('B', 3000)); |
| 88 |
insert into tb1 values (25, repeat('B', 3000)); |
| 89 |
commit; |
| 90 |
1 Blob table |
| 91 |
round_trips_slave 3 |
| 92 |
pk_ops_slave 81 |
| 93 |
|
| 94 |
begin; |
| 95 |
update tb2 set b=repeat('C', 3000), c=repeat('T', 4000) where a in (15,16,17,18,19,20); |
| 96 |
delete from tb2 where a in (1,2,3,4,5); |
| 97 |
insert into tb2 values (21, repeat('B', 3000), repeat('S', 4000)); |
| 98 |
insert into tb2 values (22, repeat('B', 3000), repeat('G', 3000)); |
| 99 |
insert into tb2 values (23, repeat('B', 3000), repeat('S', 4000)); |
| 100 |
insert into tb2 values (24, repeat('B', 3000), repeat('G', 3000)); |
| 101 |
insert into tb2 values (25, repeat('B', 3000), repeat('!', 4000)); |
| 102 |
commit; |
| 103 |
2 Blob table |
| 104 |
round_trips_slave 3 |
| 105 |
pk_ops_slave 145 |
| 106 |
|
| 107 |
|
| 108 |
Mixed batch multiple tables |
| 109 |
(6 x ~3kB update, 5x ~1kB delete, 5x ~3kB insert all independent) |
| 110 |
----------------------------------------------------------------------------- |
| 111 |
As all are independent, should get maximal batching |
| 112 |
Same keys used in different tables, should not impact batching |
| 113 |
begin; |
| 114 |
update tv set b=repeat('B', 3000) where a in (15,16,17,18,19,20); |
| 115 |
delete from tv where a in (21,22,23,24,25); |
| 116 |
insert into tv values (1, repeat('D', 4000)); |
| 117 |
insert into tv values (2, repeat('N', 4000)); |
| 118 |
insert into tv values (3, repeat('B', 3000)); |
| 119 |
insert into tv values (4, repeat('D', 5000)); |
| 120 |
insert into tv values (5, repeat('N', 2999)); |
| 121 |
update tb1 set b=repeat('B', 3000) where a in (15,16,17,18,19,20); |
| 122 |
delete from tb1 where a in (21,22,23,24,25); |
| 123 |
insert into tb1 values (1, repeat('D', 4000)); |
| 124 |
insert into tb1 values (2, repeat('N', 4000)); |
| 125 |
insert into tb1 values (3, repeat('B', 3000)); |
| 126 |
insert into tb1 values (4, repeat('D', 5000)); |
| 127 |
insert into tb1 values (5, repeat('N', 2999)); |
| 128 |
update tb2 set b=repeat('I', 3000), c=repeat('L', 4000) where a in (15,16,17,18,19,20); |
| 129 |
delete from tb2 where a in (21,22,23,24,25); |
| 130 |
insert into tb2 values (1, repeat('F', 4000), repeat('Y', 3000)); |
| 131 |
insert into tb2 values (2, repeat('W', 4000), repeat('S', 3500)); |
| 132 |
insert into tb2 values (3, repeat('P', 3000), repeat('A', 2999)); |
| 133 |
insert into tb2 values (4, repeat('M', 4500), repeat('L', 4000)); |
| 134 |
insert into tb2 values (5, repeat('C', 2999), repeat('!', 3200)); |
| 135 |
commit; |
| 136 |
Mixed operations |
| 137 |
round_trips_slave 3 |
| 138 |
pk_ops_slave 243 |
| 139 |
|
| 140 |
|
| 141 |
Bulk delete (20 x ~3kB) |
| 142 |
----------------------- |
| 143 |
begin; |
| 144 |
delete from tv where a in |
| 145 |
(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20); |
| 146 |
commit; |
| 147 |
Varchar table |
| 148 |
round_trips_slave 1 |
| 149 |
pk_ops_slave 21 |
| 150 |
|
| 151 |
begin; |
| 152 |
delete from tb1 where a in |
| 153 |
(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20); |
| 154 |
commit; |
| 155 |
1 Blob table |
| 156 |
round_trips_slave 2 |
| 157 |
pk_ops_slave 82 |
| 158 |
|
| 159 |
begin; |
| 160 |
delete from tb2 where a in |
| 161 |
(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20); |
| 162 |
commit; |
| 163 |
2 Blob table |
| 164 |
round_trips_slave 2 |
| 165 |
pk_ops_slave 142 |
| 166 |
|
| 167 |
drop table tv; |
| 168 |
drop table tb1; |
| 169 |
drop table tb2; |
| 170 |
|
| 171 |
Multiple key dependencies (3 x (I 10kB, D)) |
| 172 |
------------------------------------------- |
| 173 |
Dependencies are on PK which is case-insensitive |
| 174 |
and supports trailing spaces |
| 175 |
create table tv (a varchar(20) character set utf8 primary key, |
| 176 |
b varbinary(5000)) engine=innodb; |
| 177 |
create table tb1(a varchar(20) character set utf8 primary key, |
| 178 |
b varbinary(5000)) engine=innodb; |
| 179 |
create table tb2(a varchar(20) character set utf8 primary key, |
| 180 |
b varbinary(5000), |
| 181 |
c varbinary(5000)) engine=innodb; |
| 182 |
drop table tv; |
| 183 |
drop table tb1; |
| 184 |
drop table tb2; |
| 185 |
create table tv (a varchar(20) character set utf8 primary key, |
| 186 |
b varbinary(5000)) engine=ndb; |
| 187 |
create table tb1(a varchar(20) character set utf8 primary key, |
| 188 |
b blob) engine=ndb; |
| 189 |
create table tb2(a varchar(20) character set utf8 primary key, |
| 190 |
b blob, |
| 191 |
c blob) engine=ndb; |
| 192 |
stop slave; |
| 193 |
set global replica_type_conversions="ALL_LOSSY,ALL_NON_LOSSY"; |
| 194 |
start slave; |
| 195 |
insert into tsync values (2); |
| 196 |
insert into tv values ("Sync", "Sync"); |
| 197 |
insert into tb1 values ("Sync", "Sync"); |
| 198 |
insert into tb2 values ("Sync", "Sync", "Sync"); |
| 199 |
delete from tv; |
| 200 |
delete from tb1; |
| 201 |
delete from tb2; |
| 202 |
begin; |
| 203 |
insert into tv values ("A", repeat('B', 5000)); |
| 204 |
delete from tv where a="a "; |
| 205 |
insert into tv values ("a", repeat('A', 5000)); |
| 206 |
delete from tv where a="A "; |
| 207 |
insert into tv values ("A", repeat('H', 5000)); |
| 208 |
delete from tv where a="a "; |
| 209 |
commit; |
| 210 |
Varchar table |
| 211 |
round_trips_slave 1 |
| 212 |
pk_ops_slave 7 |
| 213 |
|
| 214 |
begin; |
| 215 |
insert into tb1 values ("a", repeat('B', 5000)); |
| 216 |
delete from tb1 where a="A"; |
| 217 |
insert into tb1 values ("a ", repeat('A', 5000)); |
| 218 |
delete from tb1 where a="A "; |
| 219 |
insert into tb1 values ("a", repeat('H', 5000)); |
| 220 |
delete from tb1 where a="A"; |
| 221 |
commit; |
| 222 |
1 Blob table |
| 223 |
round_trips_slave 15 |
| 224 |
pk_ops_slave 37 |
| 225 |
|
| 226 |
begin; |
| 227 |
insert into tb2 values ("a ", repeat('B', 5000), repeat('C', 5000)); |
| 228 |
delete from tb2 where a="a"; |
| 229 |
insert into tb2 values ("A ", repeat('A', 5000), repeat('M', 5000)); |
| 230 |
delete from tb2 where a="A"; |
| 231 |
insert into tb2 values ("a", repeat('H', 5000), repeat('F', 5000)); |
| 232 |
delete from tb2 where a="A"; |
| 233 |
commit; |
| 234 |
2 Blob table |
| 235 |
round_trips_slave 15 |
| 236 |
pk_ops_slave 67 |
| 237 |
|
| 238 |
set global replica_type_conversions=""; |
| 239 |
set global ndb_batch_size=@s_batch_size; |
| 240 |
set ndb_batch_size=@s_batch_size; |
| 241 |
set global ndb_blob_write_batch_bytes=@s_blob_write_batch; |
| 242 |
set ndb_blob_write_batch_bytes=@s_blob_write_batch; |
| 243 |
set global ndb_blob_read_batch_bytes=@s_blob_read_batch; |
| 244 |
set ndb_blob_read_batch_bytes=@s_blob_read_batch; |
| 245 |
drop table tv; |
| 246 |
drop table tsync; |
| 247 |
drop table tb1; |
| 248 |
drop table tb2; |
| 249 |
include/rpl_end.inc |