Optimize Your Database via Kubernetes ProxySQL Operator
As enterprises increasingly adopt containerization and orchestration technologies, operating databases in a dynamic and scalable environment becomes quite challenging. Kubernetes, the popular container orchestration technology, provides powerful features for automating the deployment, scaling, and maintenance of containerized applications. However, when it comes to managing databases in a Kubernetes cluster, specific considerations need to be made to guarantee optimum performance and scalability.
This article explores the importance of ProxySQL in database management in a Kubernetes environment. We’ll go through what ProxySQL is, why it’s useful in database management, and best practices for deploying and configuring ProxySQL on Kubernetes. By the end of this article, You will have a thorough understanding of how Kubernetes ProxySQL improves your database management in a Kubernetes environment.
Advantage of ProxySQL in Kubernetes
ProxySQL is a high-performance open-source SQL proxy that works between applications and databases. It serves as an intermediary layer, manages and optimizes database connections and queries. With ProxySQL, you can load balance queries across multiple database instances, route read and write operations to replicas, and provide failover support.
One of the key reasons to choose ProxySQL is its ability to boost database performance and scalability. By effectively load-balancing queries and connections, ProxySQL distributes the database workload precisely, preventing any single instance from becoming a bottleneck. This load distribution is especially important in a Kubernetes environment, where applications dynamically scale, and the database must keep up with changing demand.
Additionally, ProxySQL provides query caching capabilities, which can significantly minimize database load by providing frequently run queries directly from the cache. This functionality is very useful for read-heavy workloads and can result in significant improvements in application response times.
Deploying ProxySQL on Kubernetes
Pre-requisites
We have to set up the environment to deploy ProxySQL on Kubernetes using a Kubernetes ProxySQL operator. You requires to have a running Kubernetes cluster and a basic understanding of ProxySQL. Here we are using Kind to create our Kubernetes cluster. Additionally, you should install Helm to your Kubernetes cluster.
In this tutorial, We will use the Kubernetes ProxySQL operator KubeDB to deploy ProxySQL on Kubernetes. First, We must install KubeDB in our Kubernetes cluster. We requires a license to setup KubeDB in our Kubernetes cluster. We can obtain a free enterprise license via the Appscode License Server. To obtain the license we must provide our Kubernetes cluster ID. Run the following command to get the cluster ID.
$ kubectl get ns kube-system -o jsonpath='{.metadata.uid}'
fc435a61-c74b-9243-83a5-f1110ef2462c
After providing the required information, we will receive an email from the license server including a license.txt file. To install KubeDB, use the following helm commands.
$ helm repo add appscode https://charts.appscode.com/stable/
$ helm repo update
$ helm install kubedb appscode/kubedb \
--version v2023.06.19 \
--namespace kubedb --create-namespace \
--set kubedb-provisioner.enabled=true \
--set kubedb-ops-manager.enabled=true \
--set kubedb-autoscaler.enabled=true \
--set kubedb-dashboard.enabled=true \
--set kubedb-schema-manager.enabled=true \
--set-file global.license=/path/to/the/license.txt
Verify the KubeDB installation by the following command,
$ kubectl get pods --all-namespaces -l "app.kubernetes.io/instance=kubedb"
NAMESPACE NAME READY STATUS RESTARTS AGE
kubedb kubedb-kubedb-autoscaler-5b8b68c9d9-hqjlz 1/1 Running 0 59s
kubedb kubedb-kubedb-dashboard-6bfc6fb797-lkl2b 1/1 Running 0 59s
kubedb kubedb-kubedb-ops-manager-c67d67684-mrgfq 0/1 Running 0 59s
kubedb kubedb-kubedb-provisioner-54898dd948-tfdd4 1/1 Running 0 59s
kubedb kubedb-kubedb-schema-manager-fb749f8-ls9xl 1/1 Running 0 59s
kubedb kubedb-kubedb-webhook-server-85b7fcffd6-vhnpp 1/1 Running 0 59s
If all of the pod status is running, we can move to the next step.
Create a Namespace
We will now create a new namespace and deploy the server. The following command can be used to create a namespace:
$ kubectl create namespace proxydemo
namespace/proxydemo created
Deploy MySQL Group Replication
We need to create a yaml manifest to Deploy MySQL on Kubernetes. Here is the yaml manifest we’ll be using,
apiVersion: kubedb.com/v1alpha2
kind: MySQL
metadata:
name: mysql-server
namespace: proxydemo
spec:
version: "8.0.32"
replicas: 3
topology:
mode: GroupReplication
storageType: Durable
storage:
storageClassName: "standard"
accessModes:
- ReadWriteOnce
resources:
requests:
storage: 1Gi
terminationPolicy: WipeOut
You can see the detailed yaml specifications in the Kubernetes MySQL documentation.
We will save this yaml configuration to mysql-server.yaml. Then create the above MySQL object.
$ kubectl create -f mysql-server.yaml
mysql.kubedb.com/mysql-server created
Let’s check if the server is ready to use,
$ kubectl get mysql -n proxydemo mysql-server
NAME VERSION STATUS AGE
mysql-server 8.0.32 Ready 3m21s
Deploy ProxySQL Cluster
We will use KubeDB to deploy the ProxySQL cluster. Here is the yaml manifest that will be used.
apiVersion: kubedb.com/v1alpha2
kind: ProxySQL
metadata:
name: proxy-server
namespace: proxydemo
spec:
version: "2.4.4-debian"
replicas: 3
mode: GroupReplication
backend:
name: mysql-server
syncUsers: true
terminationPolicy: WipeOut
You can see the detailed yaml specifications in the Kubernetes ProxySQL documentation.
We will save this yaml configuration to proxy-server.yaml. Then create the above ProxySQL object.
$ kubectl create -f proxy-server.yaml
proxysql.kubedb.com/proxy-server created
If all of the previous steps are followed correctly, and MySQL and ProxySQL are deployed, the following objects will be created:
$ kubectl get all -n proxydemo
NAME READY STATUS RESTARTS AGE
pod/mysql-server-0 2/2 Running 0 9m13s
pod/mysql-server-1 2/2 Running 0 8m42s
pod/mysql-server-2 2/2 Running 0 6m3s
pod/proxy-server-0 1/1 Running 0 3m19s
pod/proxy-server-1 1/1 Running 0 3m10s
pod/proxy-server-2 1/1 Running 0 84s
NAME TYPE CLUSTER-IP EXTERNAL-IP PORT(S) AGE
service/mysql-server ClusterIP 10.0.47.68 <none> 3306/TCP 9m14s
service/mysql-server-pods ClusterIP None <none> 3306/TCP 9m14s
service/mysql-server-standby ClusterIP 10.0.111.109 <none> 3306/TCP 9m14s
service/proxy-server ClusterIP 10.0.102.106 <none> 6033/TCP 3m19s
service/proxy-server-pods ClusterIP None <none> 6032/TCP,6033/TCP 3m19s
NAME READY AGE
statefulset.apps/mysql-server 3/3 9m15s
statefulset.apps/proxy-server 3/3 3m21s
NAME TYPE VERSION AGE
appbinding.appcatalog.appscode.com/mysql-server kubedb.com/mysql 8.0.32 9m16s
NAME VERSION STATUS AGE
mysql.kubedb.com/mysql-server 8.0.32 Ready 9m20s
NAME VERSION STATUS AGE
proxysql.kubedb.com/proxy-server 2.4.4-debian Ready 3m26s
We have successfully deployed ProxySQL to Kubernetes via the Kubernetes ProxySQL operator. Now, we will connect to the MySQL database through ProxySQL to insert some sample data to verify the deployed ProxySQL is working.
Now, we must get the necessary credentials to connect to the database. KubeDB will generate Secret and Service for the mysql-server instance. Let’s check them using the following commands,
$ kubectl get secret -n proxydemo -l=app.kubernetes.io/instance=mysql-server
NAME TYPE DATA AGE
mysql-server-auth kubernetes.io/basic-auth 2 10m
$ kubectl get service -n proxydemo -l=app.kubernetes.io/instance=mysql-server
NAME TYPE CLUSTER-IP EXTERNAL-IP PORT(S) AGE
mysql-server ClusterIP 10.0.47.68 <none> 3306/TCP 11m
mysql-server-pods ClusterIP None <none> 3306/TCP 11m
mysql-server-standby ClusterIP 10.0.111.109 <none> 3306/TCP 11m
Now, we are going to use mysql-server-auth to get the required credentials.
$ kubectl get secrets -n proxydemo mysql-server-auth -o jsonpath='{.data.username}' | base64 -d
root
$ kubectl get secrets -n proxydemo mysql-server-auth -o jsonpath='{.data.password}' | base64 -d
3i7ig3RQXKD!2ksc
Insert Sample Data
Now, we will connect to the MySQL database through ProxySQL pod using the required credentials and insert some sample data,
$ kubectl exec -it proxy-server-0 -n proxydemo -- bash
root@proxy-server-0:/# mysql --user=root --password='3i7ig3RQXKD!2ksc' --host 127.0.0.1 --port=6033
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]> CREATE DATABASE Music;
Query OK, 1 row affected (0.035 sec)
MySQL [(none)]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| Music |
| information_schema |
| kubedb_system |
| mysql |
| performance_schema |
| sys |
+--------------------+
6 rows in set (0.004 sec)
MySQL [(none)]> CREATE TABLE Music.Artist (id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY, Name VARCHAR(50), Song VARCHAR(50));
Query OK, 0 rows affected, 1 warning (0.049 sec)
MySQL [(none)]> INSERT INTO Music.Artist (Name, Song) VALUES ("Bobby Bare", "Five Hundred Miles");
Query OK, 1 row affected (0.010 sec)
MySQL [(none)]> SELECT * FROM Music.Artist;
+----+----------+--------------+
| id | Name | Song |
+----+----------+--------------+
| 1 | Bon Jovi | It's My Life |
+----+----------+--------------+
1 row in set (0.014 sec)
MySQL [(none)]> exit
Bye
root@proxy-server-0:/# exit
exit
We’ve successfully deployed ProxySQL to Kubernetes via Kubernetes ProxySQL operator KubeDB. Also, we use ProxySQL to connect to the MySQL database and insert some sample data into it.
ProxySQL on Kubernetes: Best Practices
To ensure the stability of your application when using ProxySQL on Kubernetes, there are some best practices that you should follow:
Leveraging the Kubernetes ProxySQL Operator: To simplify the deployment and management of ProxySQL in a Kubernetes cluster, it is recommended to use the Kubernetes ProxySQL Operator. Kubernetes operators are controllers that encapsulate the operational knowledge of the application and automate administration tasks. The Kubernetes ProxySQL Operator simplifies the configuration and scaling of ProxySQL instances, reducing the strain on database administrators.
Ensuring High Availability: High availability is essential in any production-grade database environment. When deploying ProxySQL on Kubernetes, Having the option of scaling provides the flexibility to meet increasing demands and ensure optimal performance. It enables enterprises to instantly allocate resources and handle expanding workloads in real-time.
Configuring Connection Pooling: Connection pooling helps manage the number of connections between ProxySQL and the database backends. Properly configuring connection pooling ensures efficient use of resources and prevents overloading the database with excessive connections. It is important to achieve the proper balance between providing the maximum number of connections while still keeping some connections open to deal with the demand.
Monitoring & Security: Implementing monitoring allows effectively identifying performance issues, optimizing resource utilization, and ensuring efficient operations. Tracking essential metrics, such as connection rate, query cache hit rate, and backend server status, allows administrators to proactively respond to changes in the database workload. Also, Enabling TLS/SSL Support in the database guarantees secure communication, safeguarding sensitive data against unauthorized access or interception. By establishing an encrypted connection between the client and the database, It improves data privacy and ensure high security standards.
Conclusion
To summarize, ProxySQL is an extremely effective tool for scaling database management in a Kubernetes environment. ProxySQL can substantially boost database speed and scalability by properly load-balancing queries, supporting read/write splits, and giving query caching features.
When deploying ProxySQL on Kubernetes, following best practices such as leveraging the Kubernetes ProxySQL Operator, ensuring high availability, configuring connection pooling, and monitoring performance are essential for achieving optimal results.
As enterprises continue to adopt containerization and microservices architecture, the demand for effective database management in Kubernetes clusters grows. ProxySQL comes as a valuable solution for addressing the issues of expanding databases and satisfying the expectations of dynamic applications in Kubernetes systems. By introducing Kubernetes ProxySQL to your infrastructure, you will be able to use the full potential of your database while maintaining excellent performance and availability.
