Turbo-Charge Your MySQL Cluster: Performance Tuning Tungsten Replication

Introduction

Tungsten Cluster is a comprehensive and complete MySQL HA/DR/Geo-Scale solution. As part of the system, MySQL events are replicated from the Primary to one or more replicas using the built-in Tungsten Replicator.

Proper performance on the Replica apply side ensures that the data is as close to -up-to-date as possible, which is always the desired design goal for a cluster.

In this blog post we explore an advanced method for tuning Applier performance on a Replica node.

Background: Basic Operation

As seen in our “Mastering Tungsten Replicator Series”, events are extracted from the Primary, written in the global THL format, transferred to the Replicas and applied using JDBC to the target database server.

During this operation, it is possible for systems under high load to slow down at the apply-side due to the single-threaded nature of the write operation if Parallel Apply is not enabled.

When working to apply events, there are three Stages to getting the job done:

• Pull the THL over the network and write to the local disk.
• Load the events into an in-memory Queue.
• Write the events from the in-memory Queue to the database.

This method of tuning controls how often the Tungsten Replicator commits the events it is writing and therefore updates the tracking table, trep_commit_seqno with the latest committed sequence number that was applied.

THL, The Applier Model and Tuning

The repl-svc-applier-block-commit-size tpm INI setting controls the maximum number of events to be processed before we will trigger a commit and a corresponding update of the trep_commit_seqno table.

This is a threshold, not a fixed number.

There are a few cases where the commit will happen before reaching this threshold number:

• The apply operation uses a queue that buffers events to be processed. If this queue becomes empty, a commit will happen.
• Commit can also happen when processing fragmented events (big events).
• Commit can also get forced if the next event to apply should do a rollback (some events that are rolled back are saved into the binary log anyway because of possible side effects that require them to be applied).
• There might be other cases of forced commit, but these are the main cases when commit should happen.

Increasing the repl-svc-applier-block-commit-size value should indeed help committing less often (and updating the trep_commit_seqno table less often too), but changing this setting raises a few warnings:

• Data availability will get delayed on the replicas (until commit happens).
• In the case of a rollback, processing this event could take more time if the number of events that was processed is big.
• This will also take more time at restart if the number of events to apply again is big.

The tpm ini option repl-svc-applier-block-commit-size is the same as: property=replicator.stage.q-to-dbms.blockCommitRowCount.

Final Thoughts

Tuning the Applier performance in Tungsten Cluster is a critical step in optimizing replication efficiency. By adjusting the repl-svc-applier-block-commit-size setting, database administrators can balance replication speed with data availability, ensuring that Replica nodes remain as up-to-date as possible while minimizing unnecessary commit overhead. However, this tuning must be approached with care, considering potential trade-offs such as delayed data availability and longer recovery times in case of rollbacks. As with any performance optimization, real-world testing and monitoring are essential to finding the ideal configuration for your specific workload.

Smooth Sailing!