Some of you may use MySQL’s asynchronous replication feature called Semisynchronous Replication (aka semi-sync), and now with the MySQL Semi-Sync Summary Dashboard + Percona Monitoring and Management (PMM), you can see the most important metrics! Refer to the Install & Usage steps for deployment details (note you need Replication Set defined!).
What is Semisynchronous Replication
When enabled, Semisynchronous Replication instructs the Primary to wait until at least one replica has received and logged the event to the replica’s local relay log before completing the COMMIT on a transaction. This provides a higher level of data integrity because now it is known that the data exists in two places. This feature ensures a balance between data integrity (number of replicas acknowledging receipt of a transaction) vs the speed of commits, which will be slower since they need to wait on replica acknowledgment. Also, keep in mind that semi-sync does not wait for COMMIT on the replica; it only waits until the transaction is queued in the relay log. The actual execution of the transaction from the relay log is still asynchronous.
Dashboard Layout
Now that we know we can improve data integrity but pay a penalty on writes, I want to display the following information:
- Replica semi-sync status – enabled or not
- Waits by type, on Network or on Transactions
- How much total time was spent waiting on Transactions – what’s my penalty due to writes slowing down
- How much average time was spent waiting per transaction
- Commit acknowledgments – what’s my replication throughput
Replica Status
This lists the states that each replica has been in, whether the Replica semi-sync was enabled or disabled:
Waits by Net & TX
How many waits on the Network and on Transactions. Since the Primary is only waiting on one successful acknowledgment even though there could be multiple semi-sync replicas (the fastest one wins), your count of TX waits should be the same as TX commits on the Primary, but the wait on Network can be much higher.
Time Spent Waiting on Transactions
This is the contribution to query latency that semi-sync incurs on the Primary related to waits on transactions.
Average Wait Time per Transaction
This is the overhead of waiting on a single transaction acknowledgment.
Commit Acknowledgments
The semi-synchronous replication feature considers the possibility that Replicas may be unavailable, and is controlled by the rpl_semi_sync_master_timeout. This controls how long the Primary will wait on a commit for acknowledgment from a Replica before timing out and reverting to asynchronous replication. Was the commit acknowledged by semi-sync (Yes) or did the Primary lose all semi-sync replicas and did not acknowledge the commit (No) aka running in asynchronous mode. You should be seeing Acknowledged only when things are working smoothly.
Installation & Usage
- Download the dashboard definition in JSON from https://grafana.com/grafana/dashboards/14636/
- Import into PMM Server (tested on 2.18 but should work on older 2.x versions)
I built the dashboard to leverage the Replication Set (–replication-set) variable (which can be set to any string you want), so you will need this enabled for all servers that you want to view statistics, for example, your pmm-admin add mysql statement should look like:
1 | <span style="font-weight: 400;">pmm-admin add mysql … --replication-set=semi-sync</span> |
You can check to see whether the Replication Set variable is defined by referencing the PMM Inventory dashboard, in the last column called Other Details:
When you have the dashboard loaded, select your Replication Set from the drop-down:
New to Percona Monitoring and Management (PMM)?
Check out the PMM Quickstart guide, which helps you deploy docker for PMM Server, and pmm2-client package from the Percona Repositories, to have you up and monitoring in minutes!
I hope you find this dashboard useful! Feel free to let me know if there are missing fields or other features you’d like to see included!
