benchmark for tidb mysql interface

ben.wangzLess than 1 minute

benchmark for tidb mysql interface

reference

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

introduction

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.
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

prepare sysbench.job.yaml

apiVersion: batch/v1
kind: Job
metadata:
  name: sysbench-for-mysql-oltp
spec:
  backoffLimit: 4
  template:
    spec:
      containers:
      - name: sysbench
        image: m.daocloud.io/docker.io/openeuler/sysbench:latest
        command: ["/bin/sh", "-c"]
        args:
        - |
          set -e
          dnf install -y mariadb
          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=$SYSBENCH_THREADS \
            --time=$SYSBENCH_TIME \
            --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
          echo "sysbench cleanup completed!"
          echo "sysbench results:"
          cat $SYSBENCH_OUTPUT_PATH
        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: "500000"
          - name: SYSBENCH_TABLES
            value: "16"
          - name: SYSBENCH_OUTPUT_PATH
            value: "/results/sysbench_results.txt"
          - name: SYSBENCH_THREADS
            value: "16"
          - name: SYSBENCH_TIME
            value: "600"
        volumeMounts:
          - name: results-volume
            mountPath: /results
      restartPolicy: Never
      volumes:
        - name: results-volume
          emptyDir: {}

change the env SYSBENCH_THREADS to control the number of threads

apply the job

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

check logs

kubectl -n tidb-cluster logs -l job-name=sysbench-for-mysql-oltp

key logs example

sysbench results:
...
SQL statistics:
    queries performed:
        read:                            958258
        write:                           273670
        other:                           136841
        total:                           1368769
    transactions:                        68394  (113.96 per sec.)
    queries:                             1368769 (2280.72 per sec.)
    ignored errors:                      53     (0.09 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          600.1459s
    total number of events:              68394

Latency (ms):
         min:                                   26.18
         avg:                                  140.38
         max:                                 7127.99
         95th percentile:                      211.60
         sum:                              9601310.58

Threads fairness:
    events (avg/stddev):           4274.6250/11.18
    execution time (avg/stddev):   600.0819/0.03