Running MySQL / Percona Server in Kubernetes with a Custom Config

As we continue the development of our Percona Operators to simplify database deployment in Kubernetes ( Percona Server for MongoDB Operator 0.3.0 and Percona XtraDB Cluster Operator 0.3.0 ), one very popular question I get is: how does deployment in Kubernetes affect MySQL performance? Is there a big performance penalty? So I plan to look at how to measure and compare the performance of Kubernetes deployments to bare metal deployments. Kubernetes manages a lot of infrastructure resources like network, storage, cpu, and memory, so we need to look individually at different components.

To begin: I plan to run a single MySQL (Percona Server) instances in a Kubernetes deployment, and use local storage (fast NMVe device). I also want to customize my MySQL configuration, as the one that is supplied in public images are pretty much all set to defaults.

Let’s take a look at how we can customize it.

1. We are going to use a public Percona Server docker image “percona:ps-8.0.15-5”, it will deploy the latest version (at the time of writing) Percona Server for MySQL 8.0.15
2. We will deploy this on a specific node and will assign specific local storage to use for MySQL data
3. We’ll set up a custom configuration for MySQL.

Setting up Kubernetes

Here’s an example yaml file:

=====================
apiVersion: v1
kind: Service
metadata:
  name: mysql
spec:
  ports:
  - port: 3306
  selector:
    app: mysql
  clusterIP: None
---
apiVersion: apps/v1
kind: Deployment
metadata:
  name: mysql
  spec:
  selector:
    matchLabels:
      app: mysql
    strategy:
      type: Recreate
    template:
      metadata:
        labels:
          app: mysql
      spec:
        nodeSelector:
          kubernetes.io/hostname: smblade01
        volumes:
          - name: mysql-persistent-storage
            hostPath:
              path: /mnt/fast/mysql
              type: Directory
        containers:
        - image: percona:ps-8.0.15-5
          name: mysql
          env:
            # Use secret in real usage
          - name: MYSQL_ROOT_PASSWORD
            value: password
          ports:
          - containerPort: 3306
            name: mysql
          volumeMounts:
          - name: mysql-persistent-storage
            mountPath: /var/lib/mysql
===============

There is a lot of typical Kubernetes boilerplate to create a deployment, but the most important parts to note:

• We choose the node where to deploy with nodeSelector (lines 28–29).
• We allocate the local storage for MySQL volume with hostPath (lines 31–34).

After deploying this, we make sure the Pod is running

Kubectl get pods
NAME                      READY    STATUS     RESTARTS   AGE     IP             NODE         NOMINATED NODE    READINESS GATES
mysql-d74d5d459-b6zrs     1/1      Running    0          3m8s    192.168.1.15   smblade01    <none>            <none>

Set up MySQL to access fast storage and modify the default config for performance

Now as we are running MySQL on a dedicated node with fast storage, we want to customize the MySQL configuration to allocate a big buffer pool and adjust its IO settings.

As I said, a downloaded image will most likely run with default settings and there is no straightforward way to pass our custom my.cnf to deployment. I’ll show you how to resolve this now.

The default my.cnf contains the directive ! includedir / etc / my .cnf .d

So the solution for the custom my.cnf is as follows:

• Create a Kubernetes configmap from our custom my.cnf. Here’s how to achieve that:

kubectl create configmap mysql-config --from-file=my.cnf

• Define yaml to load the configmap into the volume that mapped to /etc/my.cnf.d ( nb lines 23–26 and 40-41).

apiVersion: apps/v1 # for versions before 1.9.0 use apps/v1beta2
kind: Deployment
metadata:
  name: mysql
spec:
  selector:
    matchLabels:
    app: mysql
  strategy:
    type: Recreate
  template:
    metadata:
      labels:
        app: mysql
    spec:
      nodeSelector:
        kubernetes.io/hostname: smblade01
      volumes:
        - name: mysql-persistent-storage
          hostPath:
            path: /mnt/fast/mysql
            type: Directory
       - name: config-volume
          configMap:
            name: mysql-config
            optional: true
      containers:
      - image: percona:ps-8
        name: mysql
        env:
          # Use secret in real usage
        - name: MYSQL_ROOT_PASSWORD
          value: password
        ports:
        - containerPort: 3306
          name: mysql
        volumeMounts:
        - name: mysql-persistent-storage
          mountPath: /var/lib/mysql
        - name: config-volume
          mountPath: /etc/my.cnf.d

And here’s our example my.cnf:

[mysqld]
 
skip-log-bin
ssl=0
 
table_open_cache = 200000
table_open_cache_instances=64
back_log=3500
max_connections=4000
 
innodb_file_per_table
 
innodb_log_file_size=10G
innodb_log_files_in_group=2
innodb_log_buffer_size=64M
 
innodb_open_files=4000
 
innodb_buffer_pool_size= 100G
innodb_buffer_pool_instances=8
 
innodb_flush_log_at_trx_commit = 1
innodb_doublewrite=1
innodb_flush_method = O_DIRECT
innodb_file_per_table = 1
innodb_io_capacity=2000
innodb_io_capacity_max=4000
innodb_flush_neighbors = 0
innodb_use_native_aio=1
 
join_buffer_size=256K
sort_buffer_size=256K

When we deploy this yaml, we will have a MySQL instance running on a dedicated box with fast local storage, big log files, and 100GB allocated for its InnoDB buffer pool.

Now we’re set to proceed to our performance measurements. Stay tuned!

—

Photo by  Joseph Barrientos  on  Unsplash