Exploring Kubernetes CPU Resources in View of Percona XtraDB Cluster’s Flow Control

Even though I used a dedicated Kubernetes cluster to host my test database, I had this belief that by not explicitly allocating (or requesting, in Kubernetes vocabulary) CPU resources to my Percona XtraDB Cluster (PXC) pods or yet making just a small request, Kubernetes could be delaying access to the free CPU cycles available on the hosts and that this could be contributing to the cluster entering flow control so often, as it was happening in my tests. I was wrong.

The lesson I learned is very simple: if you’re not explicitly limiting how much CPU your pods should use and there is extra CPU available in the Kubernetes server that hosts them, they will be able to tap into that reservoir, no problem.

In this post, I will share how to quickly deploy a Kubernetes environment and generate load on it so you can try to reproduce your own experiments. Oh, and I will also remind you (and me) how to tune replication on PXC properly.

The problem

Last week, I started working on a health audit project for a PXC cluster that was recently migrated to Kubernetes and managed by the Percona Operator for MySQL. The first thing I noticed, almost immediately, was how much time the cluster was paused due to Galera’s flow control being triggered by one of the “reader” nodes; from looking at the PMM graphs, that appeared to be happening virtually all the time during business hours (the odd hours in the graph below are due to a mismatch in timezones):

Yet, customers were not complaining of slowness and the operations team was not noticing any stalls in the application. Even with the cluster entering and leaving flow control virtually the whole time, query throughput was still good enough.

A brief explanation of flow control

We say Galera, the replication library used by PXC, is semi-synchronous because even though the other nodes in the cluster need to receive and “approve” a write transaction initiated by a given node, they do not have to apply that change in their dataset before the transaction is fully committed on the source node. They confirm the transactions are “good to go” (the technical term for this in Galera is certification) and put it in their queue of events to apply locally. Yes, this opens the door for causal reads. It is a performance compromise: it’s possible to have a fully synchronous cluster operation, where a transaction is only committed once it has been applied in all nodes, but writes would take even longer to complete.

To avoid allowing nodes to continue operating with a large queue of not yet applied events, Galera employs a threshold mechanism: whenever a node’s apply queue reaches gcs.fc_limit (an option defined by the variable wsrep_provider_options), that node needs to inform the others. As a result of that, all nodes stop processing new writes, and the cluster enters a “paused” state – flow control is activated. This pause is only a few milliseconds at a time but allows some breathing room for all nodes to work on their backlogs. As soon as the nodes get their apply queues back “under control” (below the mark defined by gcs.fc_limit * gcs.fc_factor), they let the others know. The cluster exits the flow control state and starts processing writes again.

Note this can be a very dynamic occurrence: a cluster can get into and out of flow control virtually all the time. In the environment depicted by the above graph, all writes are sent to the same node, which I’m referring to as the “writer” node, while the other nodes are operating at the limit of their apply queues (175 by default for a 3-node cluster). They need that extra “breathing room” provided by flow control to go back below that threshold mark, but it all happens very fast, which is why it may not have been noticed by anyone:

For a more in-depth explanation of flow control, I still like to refer to this 10+ year-old blog post from former Perconian Jay Janssen: Galera Flow Control in Percona XtraDB Cluster for MySQL.

Why can’t the other nodes keep up with the writer node?

Said otherwise, why can’t the “reader” nodes apply the events in their queue in a timely manner?

It may be down to the exact same reason why sometimes a MySQL replica cannot keep up with the source server and the replication SQL_thread starts to lag: transactions might be too big, or the server might be unable to parallelize the application of the replication events as efficiently as they are being (concurrently) processed in the source server; multi-threaded replication has been improved a lot over the years, but it’s not perfect. And that’s exactly the point where I made a mistake in my investigative process, as you will see next.

What do I cover below?

In the rest of this article, I’ll deliberately walk you through the process I used to try and reproduce the problem, mistakes included. I hope you find it a useful reference to quickly deploy a similar test environment for your own experiments and that, different from me, you will remember how to properly tune multi-threaded replication in a PXC environment the next time you need it.

Test environment

Almost any Kubernetes environment will do, as indicated in the Percona Operator for MySQL documentation. For these quick tests, I went with GCP; if you already have an account and a project configured, deploying a new Kubernetes cluster is very simple. (As with any cloud provider, just pay attention to the usage fees).

I created a four-node GKE cluster using n2-standard-16 VMs (16 vCPU, 64G) distributed in two node pools. Here are the four steps it took me to create this cluster:

You only need three K8s nodes for deploying a PXC cluster. The fourth K8s node I created in a separate node pool (which I called monapp) for hosting the PMM server (monitoring) and Sysbench (application):

This being an experiment on CPU resources, the goal was to have each PXC node in a dedicated host (with no other services running in parallel).

NOTE: There are different ways to assign pods to a node/node pool in Kubernetes. In this post, I’ll employ a simple approach based on the use of nodeName and nodeSelector. Affinity and anti-affinity rules allow for more refined constraints.

Deploying a PXC cluster on GKE using the Percona Operator

In short, get a copy of the latest Percona Operator for MySQL branch from Github (1.15.0 at the time I wrote this procedure):

and edit the custom resources file (deploy/cr.yaml) to adjust some of the settings:

The four sections I changed were:

1. MySQL configuration: set the buffer pool size to 8G, the redo log space to 2G, and disabled the Adaptive Hash Index (which doesn’t benefit the Sysbench workload I’ll use later);
2. Reserved (request) 10G of memory for the pods (I shouldn’t have to since this Kubernetes cluster is dedicated to the database, but I didn’t want to let this “door” open and focus on isolating CPU resources usage);
3. Storage: increased the PXC pod data volume to 300G;
4. Used nodeSelector to ensure the PXC pods (both the pxc and the haproxy containers) will be scheduled to run on the default node pool.

Once these changes were made, I deployed the PXC cluster in a custom namespace I named “pxc” (I could as well have used the “default” namespace):

• bundle.yaml deploys the Percona Operator (alongside a series of other things, like secrets – that’s why it is called a “bundle”)
• cr.yaml is the custom resources file we modified above; it deploys the PXC and the HAproxy pods.

Once you apply the bundle file, wait until the operator pod is created before applying the custom resources file; it should only take a few seconds. Make sure the PXC cluster is up and running before proceeding. A simple way to connect is to get the MySQL root password from the main secret object and start a MySQL client container to connect to the cluster1-pxc entry point, which will take you to the HAproxy assigned writer node:

Keep that session open; we will need it later.

PMM server

I could have deployed the PMM server upfront and configured the custom resources file with PMM monitoring already set to enabled. The reason I didn’t do it was to properly split the tasks and get the PXC cluster up first.

I deployed PMM with the help of a Helm chart as follows (note the use of –set spec.nodeName to indicate the PMM pod should be deployed on the 4th node):

The last step in the procedure above consisted of creating a PMM API key, which we will configure the operator to use to register the PXC pods in the PMM server. Alternatively, you can create this API key manually through the PMM web interface.

The kubectl port-forward command is used so you can access the PMM interface by pointing your browser to https://localhost:8080 and authenticate with the user admin and the password in $ADMIN_PASS. You can view the admin password and the API key generated above by checking the contents of those two environment variables:

Cluster monitoring

We can now configure our PXC cluster to be monitored by the new PMM server with the help of the operator in two steps:

1) Edit the deploy/secrets.yaml configuration file adding the PMM key as the value for variable pmmserverkey, as shown below (replace the <API_KEY> placeholder accordingly):

Then apply the changes with:

2) Edit the custom resources file (deploy/cr.yaml) to enable PMM monitoring:

Then apply the changes with:

That’s all!

Sysbench

Finally, deploy a Sysbench container also in the 4th node (defined by spec.nodeName below):

K9s view of the pods’ distribution in the cluster

An easy way to navigate around Kubernetes is with the help of K9s Kubernetes management CLI. In the screenshot below, we can see the pods have been scheduled as designed, with the PXC cluster hosted in the main node pool and both the PMM server and Sysbench container running on the “4th node” (monapp):

Running the benchmark

First, we need to create the target database and user. Use the MySQL client session created earlier for this:

Now, connect to the Sysbench container:

Prepare the data, distributed in eight tables:

This results in less than 20G in total for data and indexes, which is small enough for the hot data to fit in the buffer pool:

Run a 15-minute OLTP read-write workload with 128 concurrent threads:

Results

I ran this workload for five different configurations at first:

1. No CPU resources requested
2. spec.pxc.resources.requests.cpu: 600m
3. spec.pxc.resources.requests.cpu: 8
4. Using an almost 3x higher flow control threshold (wsrep_provider_options=”gcs.fc_limit=500″)
5. Limiting replication to a single thread (replica_parallel_workers=1)

I found no performance gains with any of the customized configurations from above. In fact, allowing the reader nodes’ events queue to grow bigger before they triggered flow control (#4) resulted in the worst throughput of all:

Query throughput

The only way I found to avoid flow control for this workload for this particular cluster and configuration was to limit query throughput below 3k QPS, which can be achieved using different combinations of Sysbench’s –threads and –rate options. Here’s a simple comparison of a few such combinations:

with the results shown below:

I wanted to show this graph to highlight the fact that, even though we want to avoid flow control, from the limited combinations in the above test, we still got the best query throughput in the scenario where the cluster was in-and-out of flow control the whole time (C). Can it do better? When you get the right configuration for replication, yes.

Replication threads

I did make a mistake in forgetting that replica_parallel_workers has no effect on Galera replication; I should be using wsrep_applier_threads to configure replication parallelization instead, which is set to 2 by default.

As soon as I tested with wsrep_applier_threads=4, I got twice as much throughput from the cluster. That number kept growing with more applier threads, even if we can’t avoid flow control kicking in often – although not virtually the whole time as before. The exception, for this environment and workload, is when I “throttle” Sysbench using –rate=400 (for 32 applier threads):

As we can see above, we cannot avoid flow control without throttling writes. On the other hand, in a way, throttling writes is precisely what flow control does. Thus, as long as we are able to squeeze the performance we need out of the cluster, we’re good.

In the tests above, we can say the best replication parallelization was achieved once it was operating with 64 wsrep_applier_threads:

Deleting the test environment

Remember to delete your test environment once you’re done with your experiments. All you probably need is something like:

MySQL Performance Tuning is an essential guide covering the critical aspects of MySQL performance optimization.

&nbsp;

Download the guide today!