benchmark for tidb mysql interface

reference

• https://docs.pingcap.com/zh/tidb/stable/benchmark-tidb-using-sysbench

introduction

1. What's TiDB MySQL interface
   • TiDB is a distributed SQL database that is compatible with the MySQL protocol. The TiDB MySQL interface allows users to interact with the TiDB database using familiar MySQL commands and tools. This compatibility enables seamless migration of existing MySQL applications to TiDB without significant code changes, providing users with a consistent experience while leveraging TiDB's scalability, high availability, and distributed capabilities.
2. What's SysBench
   • SysBench is an open-source, cross-platform, and multi-threaded benchmarking tool designed to evaluate system performance under various load conditions. It supports a wide range of database systems, including MySQL, PostgreSQL, and TiDB. SysBench can simulate different types of database workloads, such as OLTP (Online Transaction Processing) and OLAP (Online Analytical Processing), helping users to measure the performance of their database systems, identify bottlenecks, and optimize configurations.

benchmark with SysBench via k8s Job

1. prepare sysbench.job.yaml

   • apiVersion: batch/v1
     kind: Job
     metadata:
       name: sysbench-for-mysql-oltp
     spec:
       backoffLimit: 4
       template:
         spec:
           containers:
           - name: sysbench
             image: docker.io/zyclonite/sysbench:latest
             command: ["/bin/sh", "-c"]
             args:
             - |
               set -e
               apk add --no-cache mariadb-client
               echo "Waiting for MySQL to be ready..."
               until mysql -h $MYSQL_HOST -P $MYSQL_PORT -u$MYSQL_USER -p$MYSQL_PASSWORD -e "SELECT 1" > /dev/null 2>&1; do
                 sleep 5
               done
               echo "MySQL is ready!"
               echo "Dropping and creating database $MYSQL_DB..."
               mysql -h $MYSQL_HOST -P $MYSQL_PORT -u$MYSQL_USER -p$MYSQL_PASSWORD -e "DROP DATABASE IF EXISTS $MYSQL_DB;"
               mysql -h $MYSQL_HOST -P $MYSQL_PORT -u$MYSQL_USER -p$MYSQL_PASSWORD -e "CREATE DATABASE IF NOT EXISTS $MYSQL_DB;"
               echo "preparing data for sysbench..."
               sysbench oltp_read_write \
                 --db-driver=mysql \
                 --mysql-host=$MYSQL_HOST \
                 --mysql-port=$MYSQL_PORT \
                 --mysql-user=$MYSQL_USER \
                 --mysql-password=$MYSQL_PASSWORD \
                 --mysql-db=$MYSQL_DB \
                 --table-size=$SYSBENCH_TABLE_SIZE \
                 --tables=$SYSBENCH_TABLES \
                 prepare
               echo "running sysbench..."
               sysbench oltp_read_write \
                 --db-driver=mysql \
                 --mysql-host=$MYSQL_HOST \
                 --mysql-port=$MYSQL_PORT \
                 --mysql-user=$MYSQL_USER \
                 --mysql-password=$MYSQL_PASSWORD \
                 --mysql-db=$MYSQL_DB \
                 --threads=32 \
                 --time=300 \
                 --report-interval=10 \
                 run > $SYSBENCH_OUTPUT_PATH
               echo "sysbench completed! please check the results at $SYSBENCH_OUTPUT_PATH"
               echo "cleaning up..."
               sysbench oltp_read_write \
                 --db-driver=mysql \
                 --mysql-host=$MYSQL_HOST \
                 --mysql-port=$MYSQL_PORT \
                 --mysql-user=$MYSQL_USER \
                 --mysql-password=$MYSQL_PASSWORD \
                 --mysql-db=$MYSQL_DB \
                 cleanup
             env:
               - name: MYSQL_HOST
                 value: "basic-tidb.tidb-cluster.svc.cluster.local"
               - name: MYSQL_PORT
                 value: "4000"
               - name: MYSQL_USER
                 value: "root"
               - name: MYSQL_PASSWORD
                 valueFrom:
                   secretKeyRef:
                     name: basic-tidb-credentials
                     key: root
               - name: MYSQL_DB
                 value: "sysbench_test"
               - name: SYSBENCH_TABLE_SIZE
                 value: "100000"
               - name: SYSBENCH_TABLES
                 value: "10"
               - name: SYSBENCH_OUTPUT_PATH
                 value: "/results/sysbench_results.txt"
             volumeMounts:
               - name: results-volume
                 mountPath: /results
           restartPolicy: Never
           volumes:
             - name: results-volume
               emptyDir: {}
     
     

2. apply the job

   • kubectl -n tidb-cluster apply -f sysbench.job.yaml