Evaluating MySQL Parallel Replication Part 3, Annex: Under the Hood

Photo by Master Wen on Unsplash

Environments

As in the two previous posts (Part 1 and Part 2), we are using the same four environments. Each environment is composed of four servers:

  • B is an intermediate master running MariaDB 10.0.16 without parallel replication enabled (slave_parallel_threads = 0),
  • C is an intermediate master running MariaDB 10.0.16 with slave group commit enabled (slave_parallel_threads > 1, binlog_commit_wait_count > 1 and binlog_commit_wait_usec > 1),
  • D is the slave where parallel replication tests are run with different parameters.

Group Commit: Slave vs. Master

It is briefly mentioned in the main post that slave group commit identifies less parallelism than a true master would. More details are given here.

------Time----->
T1: B--C
T2: B-----C
T3: B-----C
T4: B----C
T5: B-- . . --C
T6: B----C
---------------Time---------------->
T1: B-- . . . . . . . . C
T2: B----- . . . . . C
T3: B----- . . C
T4: B----C
T5: B---- . . C
T6: B----C
------Time----->
T1: B-- . C
T2: B----- . C
T3: B----- . C
T4: B---- . C
T5: B-- . . . .--C
T6: B----C

Results

As outlined in the main post, our tests are run in the following binary log configurations:

  • Slave with Binary Logs (SB): binary logs enabled and log-slave-updates disabled.
  • Standard Slave (SS): binary logs and log-slave-updates disabled.
  • No Durability (ND): sync_binlog = 0 and innodb_flush_log_at_trx_commit = 2 (also described/known as relaxed durability).
Table # 1: E1 Execution Times and Speedups
Table # 2: E2 Execution Times and Speedups
Table # 3: E3 Execution Times and Speedups
Table # 4: E4 Execution Times and Speedups

Graphs during Tests

If you spot something we might have missed in the graphs below, please post a comment. Those graphs include the number of commits per second, CPU stats and Read IOPS for all environments, for the Slave with Binary Logs configuration (log-slave-updates disabled), in both durability settings (high and no/relaxed).

Graphs # 1a: E1 Stats — Slave with Binary Logs — High Durability
Graphs # 1b: E1 Stats — Slave with Binary Logs — Relaxed Durability
Graphs # 2a: E2 Stats — Slave with Binary Logs — High Durability
Graphs # 2b: E2 Stats — Slave with Binary Logs — Relaxed Durability
Graphs # 3a: E3 Stats — Slave with Binary Logs — High Durability
Graphs # 3b: E3 Stats — Slave with Binary Logs — Relaxed Durability
Graphs # 4a: E4 Stats — Slave with Binary Logs — High Durability
Graphs # 4b: E4 Stats — Slave with Binary Logs — Relaxed Durability

Workloads

It is mentioned briefly in the main post that the four test environments have different workloads:

  • E1 is also mostly CPU-bound but with some cache misses in the InnoDB buffer pool, needing a page fetch from disk before doing a write.
  • E3 is a mixed CPU and IO workload (more cache misses in the InnoDB buffer pool but still with enough cache hit to get a good commit throughput).
  • E4 is an IO-bound workload (mostly cache misses).
Graphs # 5: E2 is CPU-bound (good commit throughput, low IOWait and few Read IOPS).
Graphs # 6: E1 is also mostly CPU-bound (good commit throughput, low IOWait and few Read IOPS).
Graphs # 7: E4 is IO-bound (low commit throughput, high IOWait and high Read IOPS)
Graphs # 8: E3 is mixed CPU and IO-bound (respectable commit throughput, high IOWait and high Read IOPS)

Additional Discussions

Another Problem with Long-Running Transactions

--------Time-------->
T1: B-----------C
T2: B--C
T3: B--C
T4: B--C
T5: B--C
T6: B--C
--------Time-------->
T1: B-----------C
T2: B-- . . . . C
T3: B-- . . . . C
T4: B-- . . . . C
T5: B--C
T6: B--C
1
12345678901234567
---------Time-------->
T1: B-----------C
T2: B-- . . . . C
T3: B--C
T4: B--C
T5: B--C
T6: B--C
1 2
123456789012345678901
Graphs # 9: E1 Stats — Slave with Binary Logs — Relaxed Durability
Graphs # 10: E2 Stats — Slave with Binary Logs — Relaxed Durability

Booking.com Infrastructure

Core Infrastructure in Booking.com

Jean-François Gagné

Written by

Was a System Engineer @ Booking DOT com

Booking.com Infrastructure

Core Infrastructure in Booking.com