Improved wsrep-stages and related instrumentation in Percona XtraDB Cluster

wsrep-stagesIn this blog post, we’ll look at how we’ve improved wsrep-stages and related instrumentation in Percona XtraDB Cluster.


When you execute a workload and need to find out what the given thread is working on, “SHOW PROCESSLIST” comes to the top of your mind. It is an effective way to track the thread status. We decided to improve the stages in Percona XtraDB Cluster to make “SHOW PROCESSLIST” more meaningful.

In the blog below, we will check out the different wsrep-stages and the significance associated with them.

Loading of data

Running a simple insert/sysbench prepare workload. The state is stable as it mainly captures MySQL stages indicating that the table is being updated:

Running UPDATE workload

Running simple sysbench update-key workload. Let’s look at the different states that the user sees and their significance. (MASTER and SLAVE states are different and are marked accordingly.)

MASTER view:

  • This stage indicates that the write-set is trying to replicate. Global sequence numbers are assigned after the write-set is replicated and so the global-seqno is currently -1:

  • This stage indicates successful replication of the write-set. This means the write-set is now added to the group-channel. Global-seqno is updated in the message too:

  • This stage indicates the write-set has successfully passed the certification stage (making its path clear for commit):

  • This stage indicates that InnoDB commit has been triggered for the write-set:

SLAVE/Replicating node view:

  • This stage indicates that the slave thread is trying to commit the replicated write-set with the given seqno. It is likely waiting for its turn of the CommitMonitor:

  • This stage indicates a successful commit of the replicated write-set with the given seqno:

  • This stage indicates that updating the rows is in progress. (Often it was difficult to know what the workload is trying to do: UPDATE/INSERT/DELETE.) Now there is an easy way to find out:

  • This stage indicates that the given write-set is being applied:

Improved Instrumentation

Let’s answer some simple questions that most profiling experts will face:

  • How long did replication take (adding write-set to channel)?

  • How long did it take for pre-commit/certification checks?

  • How long did it take to commit a transaction on the slave (slave_thread=16 threads)?

  • Increasing the number of slave thread creates more contention (slave_thread=64):

  • The amount oftTime taken to apply a write-set:


The improved wsrep-stage framework makes it more effective for a user to find out the state of a given thread. Using the derived instrumentation through wsrep-stage is a good way to understand where the time is being spent.

Share this post

Comments (2)

  • vojamo

    mysql> select * from performance_schema.events_stages_history;
    Empty set (0.00 sec)

    It should be noted that P_S.events_stages_history needs to be enabled in performance_schema.setup_consumers. It is disabled by default at least on older versions. There are also variables to control the history length.

    A great slide deck that includes more about this is Percona’s Bill Karwin’s “Getting the Most Out of MySQL 5.6” from 2014.

    May 5, 2017 at 12:22 am
    • Krunal Bauskar

      Right. If one need to use Performance Schema all those pre-condition as per MySQL semantics needs to be followed.
      consumers, instruments …etc.

      May 7, 2017 at 9:27 pm

Comments are closed.

Use Percona's Technical Forum to ask any follow-up questions on this blog topic.