Percona XtraDB Cluster Scheduler HandlerWorking on a real case scenario in a five node Percona XtraDB Cluster (PXC), we were forced to use wsrep_sync_wait = 1, because the app does reads-after-write and we send reads to all the nodes. We had the idea to leave some nodes in DESYNC mode to reduce the flow control messages during peak load and expected to have a steadier write throughput keeping the read consistency.

We decided to test Perconas’s new PXC Scheduler Handler which is an application that manages integration between ProxySQL and Galera/PXC (the scope is to maintain the ProxySQL mysql_server table, if a negative scenario occurs, like: failures, service degradation, and maintenance). However, we realized that when a node is in DESYNC mode, it is kicked out of the read hostgroup. That is why we asked Marco Tusa to implement this new feature which will remove the node from the read hostgroup if wsrep_local_recv_queue is higher than max_replication_lag.

Environment

  • 5 PXC nodes
  • 1 ProxySQL server

In db01, we run sysbench to simulate write traffic:

In ProxySQL we run sysbench to simulate read-only traffic:

DESYNC Test

The goal of this test is to see the differences between wsrep_desync ON/OFF and wsrep_sync_wait = 1.

In the next graph, we are going to see both scenarios in the same graph, on the left of each graph when wsrep_desync is ON and on the right when it is OFF.

DESYNC Test 

As you can see there are decreases in the read traffic when DESYNC is OFF. It occurs in  the same period of time when the flow control messages are sent:

This is expected and it is not new. The number of queries executed were:

Basically, you can execute 8% of queries if flow control is not enabled.

Consistency Test

Now, we are going to simulate a scenario when the cluster receives CPU-intensive queries. We are going to execute in db03 (or any other node):

It starts 40 threads that execute 250 times the same group by query which is enough for our testing.

And we are going to add a timestamp column on the sbtest1 to monitor the lag:

Now we have this 4 scenario where we are going to monitor with:

And the status variable: wsrep_local_recv_queue.

This will be the header of the tables:

– With wsrep_desync=OFF and wsrep_sync_wait=1

– With wsrep_desync=OFF and wsrep_sync_wait=0

– With wsrep_desync=ON and wsrep_sync_wait=1

– With wsrep_desync=ON and wsrep_sync_wait=0

With wsrep_desync=OFF, the behavior is similar, and this means that the node needs to be on sync and then it checks wsrep_sync_wait. 

With wsrep_desync=ON and wsrep_sync_wait=1, we can see that the query is delayed because it needs to apply the transaction in the apply queue. It is not the case when wsrep_sync_wait=0, which data is far behind the writer node, and the query is answered immediately.

The two cases that matter are when wsrep_sync_wait=1, and both cases are read-consistent even if they show different timediff values, as the query time is measuring the flow control lag when wsrep_desync=OFF and the apply queue time when wsrep_desync=ON. 

Cluster Behavior During Load Increases

It is time to merge both test and simulate when ProxySQL spreads the CPU-intensive queries over the cluster. We are going to execute in ProxySQL the same script:

In the next graph we are going to see how the active connections on different nodes went up and down as the status of the node changed: 

MySQL Node Change

And when the script finished it went back to normal.

In ProxySQL, you will see how the status of the servers changes to ONLINE to OFFLINE_SOFT and back to ONLINE because of PXC Scheduler Handler intervention, like this:

This can be reviewed also in PMM:

percona monitoring and management

This means that PXC Scheduler Handler is helping us to spread the load across DESYNC nodes, improving the response time for the read-only traffic.

Architecture

With traditional replication we had an abstract diagram like this:

traditional replication

We didn’t have any production-ready option to guarantee Read Consistency on the Read Replicas.

With Galera/PXC we don’t need the Replication Manager as it will be replaced with PXC Scheduler Handler: 

PXC Scheduler Handler

We have the same amount of nodes with Read Consistency (thanks to wsrep_sync_wait) and a synced secondary writer node. What is missing, but not difficult to add, is a tool that monitors the WriterNodes, as in a failure scenario, we might want to keep not less than two synced nodes.

Conclusion

I think that Marco Tusa did a great job with the PXC Scheduler Handler which allowed us to implement this new Architecture that might help people that need to scale reads, need consistent write throughput, need consistent reads, and don’t want reads to affect the flow of the replication process.

If you’re interested in learning more about Percona XtraDB Cluster and ProxySQL, be sure to check out Percona’s Training Services. We offer an advanced two-day hands-on tutorial for XtraDB Cluster, in addition to our one-day ProxySQL intensive. Contact us today to learn more!

Subscribe
Notify of
guest

0 Comments
Inline Feedbacks
View all comments