Run Sysbench

Objective: Run simulated workloads against the TiDB cluster

Prerequisites:

background knowledge of TiDB components

AWS account

deployed TiDB cluster

Optionality: Required

Estimated time: 20 minutes

After confirming that we can connect to the cluster, we'll create a TiDB user account and database to run sysbench, so that we can put some load on the cluster.

Connecting

The easiest approach is to run sysbench on the "bastion" host. However, default configurations have a bastion host with very few resources, so it's not possible to put very much load on the cluster.

Create an EC2 Instance

Optionality: Optional

If you want to create an additional EC2 instance (with more CPU cores than the bastion host, for example) in order to put more load on the cluster, make sure you create it in the same VPC as the Kubernetes cluster.

The easiest way to do this is to find the bastion EC2 instance in the AWS console and use the console's "Launch More Like This". The bastion instance will have its Name tag set to ${cluster_name}-bastion.

If you want to manually identify the VPC and deploy an EC2 instance to it yourself, you can find it using the aws CLI with this command:

aws ec2 describe-instances \
    --filter Name=tag:Name,Values=${cluster_name}-bastion | 
jq -r .'Reservations[].Instances[].VpcId'

vpc-08b6b9356c7666acb

In this guide, we'll use the bastion host that's already deployed by the terraform scripts.

Copy the EXTERNAL-IP value for the -tidb service from the output of kubectl get svc to use as the TiDB service endpoint before connection to the bastion.

After connecting to the bastion, you may want to start a screen session before beginning so you can open another window to run other commands while sysbench is running.

Setup

Set some variables to make things easier.
```
tidb_host=<TiDB service endpoint>
tidb_user=sysbench
tidb_password=password
threads=32
```
If your bastion host has a large number of CPU cores, you can set threads to a higher number. Running with 1000+ threads is possible on larger EC2 instances with 8 cores, but make sure you increase the hard and soft nofile ulimit (edit /etc/security/limits.conf).

Create a user to run sysbench, create a database for sysbench to use, and grant privileges:

mysql -h "$tidb_host" -P 4000 -u root <<EoSQL
  create user $tidb_user identified by '${tidb_password}';
  create database sbtest;
  grant all privileges on sbtest.* to sysbench;
EoSQL

Create schema and load data

It's common in sysbench to insert into a large number of tables concurrently, but inserting into a smaller number of tables in TiDB better illustrates automatic region splitting and better simulates a typical TiDB workload where there are a relatively small number of very large tables. So, we'll use a single table in this test, with all threads inserting into the same table.

Run a prepare step to create the table to insert into. We don't write many rows to the table here, we'll do that separately. That allows us to stop and start the oltp_insert job without having to re-create the schema.
```
sysbench \
  --mysql-host=${tidb_host} \
  --mysql-port=4000 \
  --mysql-user=${tidb_user} \
  --mysql-password=${tidb_password} \
  --mysql-db=sbtest \
  --db-driver=mysql \
  --tables=1 \
  oltp_common \
  prepare
```

Run workload

test=oltp_insert
sysbench \
  --mysql-host=${tidb_host} \
  --mysql-port=4000 \
  --mysql-user=${tidb_user} \
  --mysql-password="${tidb_password}" \
  --mysql-db=sbtest \
  --time=6000 \
  --threads=${threads} \
  --report-interval=10 \
  --db-driver=mysql \
  --rand-type=uniform \
  --rand-seed=$RANDOM \
  --tables=1 \
  --table-size=10000000 \
  ${test} \
  run

This will run for a long time, which will give us an opportunity to load some dashboards and view some cluster metrics while it works.

You can kill (Ctrl-C) the oltp_insert run of sysbench any time you want to run some other workload. You can find the various supported tests in /usr/share/sysbench/. Simply change the value of the test variable and re-run the sysbench command as given above.

Verify load balancing

After loading has started, open a new screen window or make another SSH connection to the host where sysbench is running. You can execute this command to see the distribution of connections across the backend TiDB server instances in your Kubernetes TiDB service:

while true
do
    mysql -h "$tidb_host" -P 4000 -u root -BN \
      -e 'select @@hostname, count(*) from information_schema.processlist'
    sleep .5
done

poc-cluster-tidb-0      14
poc-cluster-tidb-0      14
poc-cluster-tidb-1      20
poc-cluster-tidb-0      14
poc-cluster-tidb-0      14
poc-cluster-tidb-0      14
poc-cluster-tidb-1      20
poc-cluster-tidb-0      14
poc-cluster-tidb-1      20
poc-cluster-tidb-1      20
poc-cluster-tidb-0      14
poc-cluster-tidb-0      14
poc-cluster-tidb-0      14
poc-cluster-tidb-1      20

You can stop that loop using Ctrl-C.

If you find that only 1 of several TiDB instances is being used, please enable cross-zone routing.

Now that the cluster is running and a load is being applied to it, you're ready to Deploy Monitoring.