EmergencyEMERGENCY? Get 24/7 Help Now!

Improved wsrep-stages and related instrumentation in Percona XtraDB Cluster

 | April 24, 2017 |  Posted In: MySQL, Percona XtraDB Cluster, XtraDB Cluster

PREVIOUS POST
NEXT POST

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

Introduction

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:

Conclusion

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.

PREVIOUS POST
NEXT POST
Krunal Bauskar

Krunal is PXC lead at Percona. He is responsible for day-day PXC development, what goes into PXC, bug fixes, releases, etc.. Before joining Percona he use to work as part of InnoDB team at MySQL/Oracle. He authored most of the temporary table revamp work, undo log truncate, atomic truncate and lot of other features. In past he was associated with Yahoo! Labs researching on bigdata problems and database startup which is now part of Teradata. His interest mainly includes data-management at any scale and has been practicing it for more than decade now.

2 Comments

  • 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.

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

Leave a Reply