- Sysbench write workload
- debug build of 10.2-mdev16428 (mainline 10.2 on the master)
- t2.xlarge instance, default SSD
Master data:
Binlog_commits: 38686
|
Binlog_group_commits 6965
|
commits_per_group=5.7
|
Parallel slave in conservative mode:
set global slave_parallel_mode=conservative;
|
Threads Run_sec
|
1 126.037301
|
10 35.689163
|
20 32.19875
|
40 30.39881
|
About 4x speedup.
Parallel slave in agressive mode:
set global slave_parallel_mode=aggressive;
|
Threads Run_sec
|
1 126.406909
|
10 36.194796
|
20 33.268547
|
40 31.945867
|
The same, about 4x speedup.
|
|
Tried with binlog_commit_wait_count=0.
Then I get 2.1 commits/group on the master:
+ echo Binlog_commits: 38163
|
+ echo Binlog_group_commits 18116
|
but performance on the slave is the same regardless of the slave_parallel_mode setting? :
set global slave_parallel_mode=conservative;
Threads Run_sec
|
1 123.929715
|
10 37.422023
|
20 34.358405
|
40 32.571119
|
set global slave_parallel_mode=optimistic;
Threads Run_sec
|
1 126.526252
|
10 35.595277
|
20 32.859772
|
40 31.547516
|
set global slave_parallel_mode=aggressive;
Threads Run_sec
|
1 124.339723
|
10 35.253654
|
20 32.447963
|
40 31.096138
|
.
|
|
The above is on the patched tree.
On the non-patched tree, timings are different slave's error log contains errors/failures (not sure how the replication was able to proceed then? Does my benchmark script has a bug in "wait for slave to catch up" part?)
|
|
Re-ran with InnoDB:
No forced binlog grouping on master
|
Binlog_commits: 20329
|
Binlog_group_commits 13721
|
1.48 commits per group.
set global slave_parallel_mode=conservative;
|
Threads Run_sec
|
1 143.653053
|
10 113.315977
|
20 114.068702
|
40 113.06473
|
we get about 1.3x speedup. Roughly agrees with the commits/group ratio.
set global slave_parallel_mode=optimistic;
|
Threads Run_sec
|
1 152.828968
|
10 46.450177
|
20 36.141618
|
40 36.022234
|
optimistic mode gets us 4x speedup.
set global slave_parallel_mode=aggressive;
|
Threads Run_sec
|
1 156.378884
|
10 46.072578
|
20 36.213746
|
40 37.049414
|
aggressive is not faster than optimistic.
But the machine has just 4 cores, and the dataset fits into memory, so this might be expected.
|
|
let the slave be 2x bigger, t2.2xlarge, InnoDB
No forced binlog grouping on master
|
Binlog_commits: 19907
|
Binlog_group_commits 13481
|
set global slave_parallel_mode=conservative;
|
Threads Run_sec
|
1 138.968136
|
10 108.59486
|
20 108.82256
|
40 111.129492
|
set global slave_parallel_mode=optimistic;
|
Threads Run_sec
|
1 139.771637
|
10 47.61184
|
20 40.066993
|
40 44.076338
|
set global slave_parallel_mode=aggressive;
|
Threads Run_sec
|
1 138.512629
|
10 45.04877
|
20 45.348733
|
40 45.060744
|
|
|
Increased table size to 5M rows to make the load "more io-bound".
InnoDB run:
No forced binlog grouping on master
|
Binlog_commits: 5108
|
Binlog_group_commits 5053
|
Binlog_group_commit_trigger_count 0
|
Binlog_group_commit_trigger_lock_wait 0
|
Binlog_group_commit_trigger_timeout 0
|
set global slave_parallel_mode=conservative;
|
Threads Run_sec
|
1 394.053726
|
10 386.858101
|
20 387.655303
|
40 386.86305
|
set global slave_parallel_mode=optimistic;
|
Threads Run_sec
|
1 393.821621
|
10 408.869121
|
20 398.193657
|
40 394.127235
|
set global slave_parallel_mode=aggressive;
|
Threads Run_sec
|
1 393.573311
|
10 407.978196
|
20 397.33549
|
40 393.294125
|
|
|
Now with MyRocks:
No forced binlog grouping on master
|
Binlog_commits: 38019
|
Binlog_group_commits 19421
|
Binlog_group_commit_trigger_count 0
|
Binlog_group_commit_trigger_lock_wait 0
|
Binlog_group_commit_trigger_timeout 0
|
set global slave_parallel_mode=conservative;
|
Threads Run_sec
|
1 486.401132
|
10 377.77337
|
20 367.795479
|
40 378.082168
|
set global slave_parallel_mode=optimistic;
|
Threads Run_sec
|
1 482.882999
|
10 330.11872
|
20 329.315624
|
40 343.157583
|
set global slave_parallel_mode=aggressive;
|
Threads Run_sec
|
1 493.53358
|
10 336.028835
|
20 332.999317
|
40 325.27451
|
|