Scaling Percona XtraDB Cluster with ProxySQL in Kubernetes

June 16, 2016

Author

Vadim Tkachenko

MySQL

Share this Post:

How do you scale Percona XtraDB Cluster with ProxySQL in Kubernetes?

In my previous post I looked how to run Percona XtraDB Cluster in a Docker Swarm orchestration system, and today I want to review how can we do it in the more advanced Kubernetes environment.

There are already some existing posts from Patrick Galbraith (https://github.com/kubernetes/kubernetes/tree/release-1.2/examples/mysql-galera) and Raghavendra Prabhu (https://github.com/ronin13/pxc-kubernetes) on this topic. For this post, I will show how to run as many nodes as I want, see what happens if we add/remove nodes dynamically and handle incoming traffic with ProxySQL (which routes queries to one of working nodes). I also want to see if we can reuse the ReplicationController infrastructure from Kubernetes to scale nodes to a given number.

These goals should be easy to accomplish using our existing Docker images for Percona XtraDB Cluster (https://hub.docker.com/r/percona/percona-xtradb-cluster/), and I will again rely on the running service discovery (right now the images only work with etcd).

The process of setting up Kubernetes can be pretty involved (but it can be done; check out the Kubernetes documentation to see how: http://kubernetes.io/docs/getting-started-guides/ubuntu/). It is much more convenient to use a cloud that supports it already (Google Cloud, for example). I will use Microsoft Azure, and follow this guide: http://kubernetes.io/docs/getting-started-guides/coreos/azure/. Unfortunately the scripts from the guide install previous version of Kubernetes (1.1.2), which does not allow me to use ConfigMap. To compensate, I will duplicate the ENVIRONMENT variables definitions for Percona XtraDB Cluster and ProxySQL pods. This can be done more optimally in the recent version of Kubernetes.

After getting Kurbernetes running, starting Percona XtraDB Cluster with ProxySQL is easy using following pxc.yaml file (which you also can find with our Docker sources https://github.com/percona/percona-docker/tree/master/pxc-57/kubernetes):

apiVersion: v1<br>kind: ReplicationController<br>metadata:<br> name: pxc-rc<br> app: pxc-app<br>spec:<br> replicas: 3 # tells deployment to run N pods matching the template<br> selector:<br> app: pxc-app<br> template: # create pods using pod definition in this template<br> metadata:<br> name: pxc<br> labels:<br> app: pxc-app<br> spec:<br> containers:<br> - name: percona-xtradb-cluster<br> image: perconalab/percona-xtradb-cluster:5.6test<br> ports:<br> - containerPort: 3306<br> - containerPort: 4567<br> - containerPort: 4568<br> env:<br> - name: MYSQL_ROOT_PASSWORD<br> value: "Theistareyk"<br> - name: DISCOVERY_SERVICE<br> value: "172.18.0.4:4001"<br> - name: CLUSTER_NAME<br> value: "k8scluster2"<br> - name: XTRABACKUP_PASSWORD<br> value: "Theistare"<br> volumeMounts:<br> - name: mysql-persistent-storage<br> mountPath: /var/lib/mysql<br> volumes:<br> - name: mysql-persistent-storage<br> emptyDir: {}<br> imagePullPolicy: Always<br>---<br>apiVersion: v1<br>kind: ReplicationController<br>metadata:<br> name: proxysql-rc<br> app: proxysql-app<br>spec:<br> replicas: 1 # tells deployment to run N pods matching the template<br> selector:<br> front: proxysql<br> template: # create pods using pod definition in this template<br> metadata:<br> name: proxysql<br> labels:<br> app: pxc-app<br> front: proxysql<br> spec:<br> containers:<br> - name: proxysql<br> image: perconalab/proxysql<br> ports:<br> - containerPort: 3306<br> - containerPort: 6032<br> env:<br> - name: MYSQL_ROOT_PASSWORD<br> value: "Theistareyk"<br> - name: DISCOVERY_SERVICE<br> value: "172.18.0.4:4001"<br> - name: CLUSTER_NAME<br> value: "k8scluster2"<br> - name: MYSQL_PROXY_USER<br> value: "proxyuser"<br> - name: MYSQL_PROXY_PASSWORD<br> value: "s3cret"<br>---<br>apiVersion: v1<br>kind: Service<br>metadata:<br> name: pxc-service<br> labels:<br> app: pxc-app<br>spec:<br> ports:<br> # the port that this service should serve on<br> - port: 3306<br> targetPort: 3306<br> name: "mysql"<br> - port: 6032<br> targetPort: 6032<br> name: "proxyadm"<br> # label keys and values that must match in order to receive traffic for this service<br> selector:<br> front: proxysql

apiVersion: v1 kind: ReplicationController metadata: name: pxc-rc app: pxc-app spec: replicas: 3 # tells deployment to run N pods matching the template selector: app: pxc-app template: # create pods using pod definition in this template metadata: name: pxc labels: app: pxc-app spec: containers: - name: percona-xtradb-cluster image: perconalab/percona-xtradb-cluster:5.6test ports: - containerPort: 3306 - containerPort: 4567 - containerPort: 4568 env: - name: MYSQL_ROOT_PASSWORD value: "Theistareyk" - name: DISCOVERY_SERVICE value: "172.18.0.4:4001" - name: CLUSTER_NAME value: "k8scluster2" - name: XTRABACKUP_PASSWORD value: "Theistare" volumeMounts: - name: mysql-persistent-storage mountPath: /var/lib/mysql volumes: - name: mysql-persistent-storage emptyDir: {} imagePullPolicy: Always --- apiVersion: v1 kind: ReplicationController metadata: name: proxysql-rc app: proxysql-app spec: replicas: 1 # tells deployment to run N pods matching the template selector: front: proxysql template: # create pods using pod definition in this template metadata: name: proxysql labels: app: pxc-app front: proxysql spec: containers: - name: proxysql image: perconalab/proxysql ports: - containerPort: 3306 - containerPort: 6032 env: - name: MYSQL_ROOT_PASSWORD value: "Theistareyk" - name: DISCOVERY_SERVICE value: "172.18.0.4:4001" - name: CLUSTER_NAME value: "k8scluster2" - name: MYSQL_PROXY_USER value: "proxyuser" - name: MYSQL_PROXY_PASSWORD value: "s3cret" --- apiVersion: v1 kind: Service metadata: name: pxc-service labels: app: pxc-app spec: ports: # the port that this service should serve on - port: 3306 targetPort: 3306 name: "mysql" - port: 6032 targetPort: 6032 name: "proxyadm" # label keys and values that must match in order to receive traffic for this service selector: front: proxysql

Here is the command to start the cluster:

kubectl create -f pxc.yaml

1	kubectl create -f pxc.yaml

The command will start three pods with Percona XtraDB Cluster and one pod with ProxySQL.

Percona XtraDB Cluster nodes will register themselves in the discovery service and we will need to add them to ProxySQL (it can be done automatically with scripting, for now it is a manual task):

kubectl exec -it proxysql-rc-4e936 add_cluster_nodes.sh

1	kubectl exec -it proxysql-rc-4e936 add_cluster_nodes.sh

Increasing the cluster size can be done with the scale command:

kubectl scale --replicas=6 -f pxc.yaml

1	kubectl scale --replicas=6 -f pxc.yaml

You can connect to the cluster using a single connection point with ProxySQL: You can find it this way:

kubectl describe -f pxc.yaml<br><br>Name: pxc-service<br>Namespace: default<br>Labels: app=pxc-app<br>Selector: front=proxysql<br>Type: ClusterIP<br>IP: 10.23.123.236<br>Port: mysql 3306/TCP<br>Endpoints: &lt;none&gt;<br>Port: proxyadm 6032/TCP<br>Endpoints: &lt;none&gt;<br>Session Affinity: None

1	kubectl describe -f pxc.yaml<br><br>Name: pxc-service<br>Namespace: default<br>Labels: app=pxc-app<br>Selector: front=proxysql<br>Type: ClusterIP<br>IP: 10.23.123.236<br>Port: mysql 3306/TCP<br>Endpoints: <none><br>Port: proxyadm 6032/TCP<br>Endpoints: <none><br>Session Affinity: None

It exposes the endpoint IP address 10.23.123.236 and two ports: 3306 for the MySQL connection and 6032 for the ProxySQL admin connection.

So you can see that scaling Percona XtraDB Cluster with ProxySQL in Kubernetes is pretty easy. In the next post, I want to run benchmarks in the different Docker network environments.