0

I am migrating my MySQL database from bare metal setup to Kubernetes. So I have exported a MySQL dump with size of around 8.9GB and uploaded the MySQL dump on my Kubernetes master node. The dump is inserted using the command

kubectl exec -it [podname] -n [namespace] -- mysql -u [db user] -p[password] [db name] < [name of the dump].sql

The insert speed is super slow and thus I import the tables one by one to observe its behavior. A 1.8GB dump takes more than 5 hours to complete.

The SELECT command itself take 0.013 seconds to select 1000 entries. The INSERT INTO for batch of data can take up to 72 seconds to 120 seconds.

I search through the internet and find that the MySQL dump insertion speed is slow to container.

Does anyone experience the same? And can give me some clue to speed up the import speed of the dump?

Some details of my cluster

MySQL Pod version: MySQL version 5.7
Kubernetes Version: v1.20.9
File System: btrfs

The MySQL Pod is deployed using pod deployment and the db is dumped to the pvc defined.

YAML config of the Pod

apiVersion: apps/v1
kind: Deployment
metadata:
  name: mysqldb01
spec:
  strategy:
    rollingUpdate:
      maxSurge: 1
      maxUnavailable: 1
    type: RollingUpdate
  replicas: 1
  selector:
    matchLabels:
      app: mysqldb01
  template:
    metadata:
      labels:
        app: mysqldb01
    spec:
      schedulerName: stork
      containers:
      - name: mysql
        image: mysql:5.7
        imagePullPolicy: "Always"
        env:
        - name: MYSQL_ROOT_PASSWORD
          value: xxxxxxxxxxxxxxxxxxxxxxxxx
        args:
        - --lower_case_table_names=1
        ports:
        - containerPort: 3306
        volumeMounts:
        - mountPath: /var/lib/mysql
          name: mysql-data
        - name: mysql-custom-config
          mountPath: /etc/mysql/mysql.conf.d/custom.my.cnf
          subPath: my.custom.conf
      volumes:
      - name: mysql-data
        persistentVolumeClaim:
          claimName: px-mysql-db01-pvc
      - name: mysql-custom-config
        configMap:
          name: mysql-custom-config

One master node and three workers node are installed with rancher.

Kubernetes is installed using

curl https://releases.rancher.com/install-docker/20.10.sh | sh

Thanks in advance.

ony4869
  • 1
  • 1
  • Please provide details about your kubernetes setup and mysql setup – Thomas Nov 08 '21 at 09:13
  • Additional information request, please from MySQL server. RAM size, # cores, any SSD or NVME devices on MySQL Host server? Post on pastebin.com and share the links. From your SSH login root, Text results of: A) SELECT COUNT(*) FROM information_schema.tables; B) SHOW GLOBAL STATUS; after minimum 24 hours UPTIME C) SHOW GLOBAL VARIABLES; D) SHOW FULL PROCESSLIST; G) SHOW ENGINE INNODB STATUS; for server workload tuning analysis to provide suggestions. – Wilson Hauck Nov 08 '21 at 13:15
  • Let's see `SHOW CREATE TABLE` for that 2-minute `INSERT`. Was it a single `INSERT` with 1000 rows? – Rick James Nov 10 '21 at 01:49

0 Answers0