Performance Schema: Measure Multi-Threaded Slave Activity

performance schemaPerformance 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.