7.4. Testing database replication performance
If the cluster and the individual nodes are all healthy and stable, you can run performance benchmark tests on the replication throughput by querying specific database variables.
Every time you query one of these variables, a FLUSH STATUS command resets the variable value. To run benchmark tests, you must run multiple queries and analyze the variances. These variances can help you determine how much Flow Control is affecting the cluster’s performance.
Flow Control is a mechanism that the cluster uses to manage replication. When the local received queue exceeds a certain threshold, Flow Control pauses the replication until the queue size goes down. For more information about Flow Control, see Flow Control on the Galera Cluster website.
Procedure
Run the following command and replace VARIABLE with the wsrep database variable that you want to check:
$ sudo docker exec galera-bundle-docker-0 sudo mysql -B --password="[MYSQL-HIERA-PASSWORD]" -e "SHOW STATUS LIKE 'VARIABLE';"
The following table lists the wsrep database variables that you can use to test database replication performance.
| Variable | Summary | Usage |
|---|---|---|
|
| Average size of the local received write-set queue after the last query. |
A value higher than 0.0 indicates that the node cannot apply write-sets as quickly as it receives write-sets, which triggers replication throttling. Check |
|
| Average send queue length after the last query. | A value higher than 0.0 indicates a higher likelihood of replication throttling and network throughput problems. |
|
| Minimum and maximum size of the local receive queue after the last query. |
If the value of |
|
| Fraction of the time that Flow Control paused the node after the last query. |
A value higher than 0.0 indicates that Flow Control paused the node. To determine the duration of the pause, multiply the For example:
|
|
|
Average difference between the lowest and highest sequence number ( |
In case of throttling and pausing, this variable indicates how many write-sets on average can be applied in parallel. Compare the value with the |
|
| Number of threads that can be applied simultaneously |
You can increase the value of this variable to apply more threads simultaneously, which also increases the value of
For example, if the
If a problematic node already has an optimal |
