# 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:** TBD

## Sysbench

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.

If you want to create an additional EC2 instance (with more CPU cores than the bastion host, for instance) 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'

Output:

vpc-08b6b9356c7666acb

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

### Setup

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.

First, set some variables to make things easier. Copy the `EXTERNAL-IP` value for the `-tidb` service from the output of `kubectl get svc` to use as the TiDB service endpoint.

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

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.

### Load Data

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

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 dashboard 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/`.

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

Output:

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.

## Sysbench

We use sysbench as the workload to test TiDB cluster.

### Create a database for sysbench

$ mysql -h ${external_lb_host} -P 4000 -u root
mysql> create database sbtest;

### Set sysbench configuration file

Create a config file named `config`

mysql-host=${external_lb_host}
mysql-port=4000
mysql-user=root
mysql-db=sbtest
time=3600
threads=64
report-interval=10
db-driver=mysql

### Prepare data

sysbench --config-file=config oltp_point_select --tables=8 --table-size=1000 prepare

### Run

sysbench --config-file=config oltp_point_select --tables=8 --table-size=1000 run

## Monitoring

To follow these steps, you'll need to deploy TidbMonitor in your cluster.

sed "s/CLUSTER_NAME/${cluster_name}/" manifests/db-monitor.yaml.example > monitor.yaml
kubectl  create -f monitor.yaml -n "$namespace"

After starting data loading, let's open the Grafana dashboard so we can view some cluster metrics while placing load on it. Execute these commands back on your host OS where your Kubernetes config file is located (not on the bastion or other EC2 instance you may have used to run sysbench).

TiDB server instances write monitoring info to a Prometheus instance

TODO: explain what happens by looking through the statements and Key Visualizer.

#### Troubleshooting

##### describe the unusual case

describe the reason and how to fix

##### describe another unusual case

describe the reason and how to fix