Stress Test TiDB Using TiUP Bench Component

When you test the performance of a database, it is often required to stress test the database. To facilitate this, TiUP has integrated the bench component, which provides multiple workloads for stress testing. You can access these workloads by the following commands:

tiup bench tpcc   # Benchmark a database using TPC-C
tiup bench tpch   # Benchmark a database using TPC-H
tiup bench ch     # Benchmark a database using CH-benCHmark
tiup bench ycsb   # Benchmark a database using YCSB
tiup bench rawsql # Benchmark a database using arbitrary SQL files

tpcc, tpch, ch, and rawsql share the following common command flags. However, ycsb is mainly configured by a .properties file, which is described in its usage guide.

  -t, --acThreads int         OLAP client concurrency, only for CH-benCHmark (default to 1)
      --conn-params string    Session variables, such as setting `--conn-params tidb_isolation_read_engines='tiflash'` for TiDB queries and setting `--conn-params sslmode=disable` for PostgreSQL connections
      --count int             Total execution count (0 means infinite count)
  -D, --db string             Database name (default to "test")
  -d, --driver string         Database driver: mysql, postgres (default to "mysql")
      --dropdata              Clean up historical data before preparing
  -H, --host strings          Database host (default to [127.0.0.1])
      --ignore-error          Ignore errors when running workload
      --interval duration     Output interval time (default to 10s)
      --isolation int         Isolation Level (0: Default; 1: ReadUncommitted;
                              2: ReadCommitted; 3: WriteCommitted; 4: RepeatableRead;
                              5: Snapshot; 6: Serializable; 7: Linerizable)
      --max-procs int         runtime.GOMAXPROCS of golang, the limits of how many cores can be used
      --output string         Output style. Valid values can be { plain | table | json } (default to "plain")
  -p, --password string       Database password
  -P, --port ints             Database port (default to [4000])
      --pprof string          Address of pprof endpoint
      --silence               Don't print errors when running workload
  -S, --statusPort int        Database status port (default to 10080)
  -T, --threads int           Thread concurrency (default to 1)
      --time duration         Total execution time (default to 2562047h47m16.854775807s)
  -U, --user string           Database user (default to "root")

• You can pass comma-separated values to --host and --port to enable client-side load balancing. For example, when you specify --host 172.16.4.1,172.16.4.2 --port 4000,4001, the program will connect to 172.16.4.1:4000, 172.16.4.1:4001, 172.16.4.2:4000, and 172.16.4.2:4001, chosen in round-robin fashion.
• --conn-params must follow the format of query string. Different databases might have different parameters. For example:
  • --conn-params tidb_isolation_read_engines='tiflash' forces TiDB to read from TiFlash.
  • --conn-params sslmode=disable disables SSL when you connect to PostgreSQL.
• When running CH-benCHmark, you can use --ap-host, --ap-port, and --ap-conn-params to specify a standalone TiDB server for OLAP queries.

The following sections describe how to run TPC-C, TPC-H, YCSB tests using TiUP.

Run TPC-C test using TiUP

The TiUP bench component supports the following commands and flags to run the TPC-C test:

Available Commands:
  check       Check data consistency for the workload
  cleanup     Cleanup data for the workload
  prepare     Prepare data for the workload
  run         Run workload

Flags:
      --check-all            Run all consistency checks
  -h, --help                 Help for TPC-C
      --partition-type int   Partition type: 1 - HASH, 2 - RANGE, 3 - LIST (HASH-like), 4 - LIST (RANGE-like) (default to 1)
      --parts int            Number of partitions (default to 1)
      --warehouses int       Number of warehouses (default to 10)

Test procedures

The following provides simplified steps for running a TPC-C test. For detailed steps, see How to Run TPC-C Test on TiDB.

1. Create 4 warehouses using 4 partitions via hash:

   tiup bench tpcc --warehouses 4 --parts 4 prepare
   

2. Run the TPC-C test:

   tiup bench tpcc --warehouses 4 --time 10m run
   

3. Check the consistency:

   tiup bench tpcc --warehouses 4 check
   

4. Clean up data:

   tiup bench tpcc --warehouses 4 cleanup
   

Preparing data via SQL might be slow when you want to run a benchmark with a large data set. In that case, you can generate data in the CSV format by the following commands and then import it to TiDB via TiDB Lightning.

• Generate the CSV file:

  tiup bench tpcc --warehouses 4 prepare --output-dir data --output-type=csv
  

• Generate the CSV file for the specified table:

  tiup bench tpcc --warehouses 4 prepare --output-dir data --output-type=csv --tables history,orders
  

Run TPC-H test using TiUP

The TiUP bench component supports the following commands and parameters to run the TPC-H test:

Available Commands:
  cleanup     Cleanup data for the workload
  prepare     Prepare data for the workload
  run         Run workload

Flags:
      --check            Check output data, only when the scale factor equals 1
  -h, --help             help for tpch
      --queries string   All queries (default "q1,q2,q3,q4,q5,q6,q7,q8,q9,q10,q11,q12,q13,q14,q15,q16,q17,q18,q19,q20,q21,q22")
      --sf int           scale factor

Test procedures

1. Prepare data:

   tiup bench tpch --sf=1 prepare
   

2. Collect statistics:

   For the OLAP scenarios, to ensure that the TiDB optimizer can generate the optimal execution plan, execute the following SQL statements to collect statistics in advance. Be sure to set tidb_analyze_column_options to ALL, otherwise collecting statistics can result in a significant drop in query performance.

   set global tidb_analyze_column_options='ALL';
   

3. Run the TPC-H test by executing one of the following commands:

   • If you check the result, run this command:

     tiup bench tpch --count=22 --sf=1 --check=true run
     

   • If you do not check the result, run this command:

     tiup bench tpch --count=22 --sf=1 run
     

4. Clean up data:

   tiup bench tpch cleanup
   

Run YCSB test using TiUP

You can stress test both TiDB and TiKV via YCSB.

Stress test TiDB

1. Prepare data:

   tiup bench ycsb load tidb -p tidb.instances="127.0.0.1:4000" -p recordcount=10000
   

2. Run the YCSB workload:

   # The read-write percent is 95% by default
   tiup bench ycsb run tidb -p tidb.instances="127.0.0.1:4000" -p operationcount=10000
   

Stress test TiKV

1. Prepare data:

   tiup bench ycsb load tikv -p tikv.pd="127.0.0.1:2379" -p recordcount=10000
   

2. Run the YCSB workload:

   # The read-write percent is 95% by default
   tiup bench ycsb run tikv -p tikv.pd="127.0.0.1:2379" -p operationcount=10000
   

Run RawSQL test using TiUP

You can write an arbitrary query in a SQL file, and then use it for the test by executing tiup bench rawsql as follows:

1. Prepare data and the query:

   -- Prepare data
   CREATE TABLE t (a int);
   INSERT INTO t VALUES (1), (2), (3);
   
   -- Save your query in a SQL file. For example, you can save the following query in `demo.sql`.
   SELECT a, sleep(rand()) FROM t WHERE a < 4*rand();
   

2. Run the RawSQL test:

   tiup bench rawsql run --count 60 --query-files demo.sql