Performance Schema: Measure Multi-Threaded Slave Activity

Performance Schema

In many types of database workloads, using a multi-threaded slave from 5.6+ helps improve replication performance. I’ve had a number of users enable this feature, but have not seen anyone ask how each thread is performing. Here’s a quick way with Performance_Schema to measure the amount of multi-threaded slave activity on each thread (after you have already configured MTS on your slave of course ).

First, we need to enable the  statements instruments:

Next, let’s find the THREAD_ID for our slave workers:

And then we capture measurements:

As you can see above, one of the multi-threadedthreads is doing more work compared to the other three. This is a sysbench on the master in the database doing --trx-rate=10, compared to the other three which are only doing  --trx-rate=2|5.

Below is another example, this time including the event times. With a slightly modified sysbench test, I’ve designed the tests to send large updates but one with a low transaction rate, another with a high transaction rate, and the rest in between. You can see specifically in threads 32 and 34, that the former is performing a lower amount of UPDATES, but spending more time per event.

Exploring a bit further, one thing I noticed is that when using binlog_format=ROW  for replication, you will only see the  BEGIN  events instrumented from performance_schema. I think this is a bug so I reported it here.

With MySQL 5.7, MTS has an additional improvement with how parallel replication is applied. By default, transactions are applied in parallel per database. With the new slave_parallel_type feature, another option called LOGICAL_CLOCK  is introduced that allows parallelization intra-database as well. I did some small tests below, with same workload as above. First with the default DATABASE type:

And then with LOGICAL_CLOCK:

With LOGICAL_LOCK  and slave_preserve_commit_order  enabled:

Combining the  INSERT, UPDATE and DELETE counts from these 3 tests, LOGICAL_CLOCK  tops with around 17k events within 120 seconds of the same workload. The  DATABASE  type was the slowest, recording only about 12k events committed on the slave.

Lastly, with MySQL 5.7 a new instrument has been added and enabled for metrics to be collected. You can also measure the per-transaction rate instead of per event class.

And a quick measurement:

Of course, not only statement events and transactions can be measured – you can also measure wait times and stages as well. Happy instrumenting!

Jervin Real

As Senior Consultant, Jervin partners with Percona's customers on building reliable and highly performant MySQL infrastructures while also doing other fun stuff like watching cat videos on the internet. Jervin joined Percona in Apr 2010.