Sysbench

Test Environment

• Region: CN-Hong Kong
• AZ: AZ4
• Shards per DB instance: 16
• Logical tables: 1
• Data volume: 320 million lines
• RDS instance specifications: 8 vCPUs | 16 GB, 16 vCPUs | 32 GB, 32 vCPUs | 64 GB, ultra-high I/O
• One testing tool (sysbench): 32 vCPUs | 64 GB

Test Tool

Sysbench is a multi-threaded modular benchmark tool based on LuaJIT. It is mostly used for testing database benchmarks. With sysbench, you can perform multi-thread concurrent operations using the built-in database test model to evaluate the database performance. For details, visit https://github.com/akopytov/sysbench.

Sysbench 1.0.20 is used in this test. Run the following commands to install it:

# wget -c https://github.com/akopytov/sysbench/archive/1.0.20.zip

# yum install autoconf libtool mysql mysql-devel vim unzip

# unzip 1.0.20.zip

# cd sysbench-1.0.20

# ./autogen.sh

# ./configure

# make

# make install

Test Procedure

Replace the database name, connection IP address, and user password based on site requirements.

1. Import test data.
   1. Create a schema.
   2. Log in to the target DDM database using the CLI and create a table.

      Table structure: CREATE TABLE sbtest1(

      id int UNSIGNED NOT NULL auto_increment PRIMARY KEY,

      k INTEGER UNSIGNED DEFAULT '0' NOT NULL,

      c varCHAR(1200) DEFAULT '' NOT NULL,

      pad varCHAR(1200) DEFAULT '' NOT NULL

      ) dbpartition by hash(id);

   3. Run the following command to import test data into the database:

      sysbench --test=/usr/local/share/sysbench/tests/include/oltp_legacy/oltp.lua --oltp_tables_count=1 --report-interval=5 --oltp-table-size=<data> --mysql-user=<user> --mysql-password=<password> --mysql-table-engine=innodb --rand-init=on --mysql-host=<host> --mysql-port=5066 --mysql-db=<db-name> --max-time=300 --max-requests=0 --oltp_skip_trx=off --oltp_auto_inc=on --oltp_range_size=5 --num-threads=256 --oltp_secondary --id-start-val=1 --id-step-val=1000 prepare

      

      Comment out line db_query(query) in script common.lua.

2. Run the following command to perform stress testing:

   sysbench --test=/usr/local/share/sysbench/tests/include/oltp_legacy/oltp.lua --oltp_tables_count=1 --report-interval=1 --oltp-table-size=<data> --mysql-user=<user> --mysql-password=<password> --mysql-table-engine=innodb --rand-init=on --mysql-host=<host> --mysql-port=5066 --mysql-db=<db_name> --max-time=300 --max-requests=0 --oltp_skip_trx=off --oltp_auto_inc=on --oltp_range_size=5 --num-threads=256 --oltp_secondary --forced-shutdown=0 run

3. Run the following command to delete the test data:

   sysbench --test=/usr/local/share/sysbench/tests/include/oltp_legacy/oltp.lua --db-driver=mysql --mysql-db=<db_name> --mysql-user=<user> --mysql-password=<password> --mysql-port=5066 --mysql-host=<host> --oltp-tables-count=64 --oltp-table-size=<data> --max-time=3600 --max-requests=0 --num-threads=200 cleanup

Concepts

• Transaction Per Second (TPS) refers to the number of transactions executed per second by a database.
• Query Per Second (QPS) refers to the number of SQL statements, including INSERT, SELECT, UPDATE, and DELETE statements, executed per second.

Test Data

The DDM kernel has the overload protection capability. If overload occurs, the kernel may trigger a series of actions, for example, attempting to release the SQL statements that have occupied system resources for a long period of time. If overload protection is triggered, a performance bottleneck occurs, and jitter may be found when you observe the stress testing curve. If you continue to increase the pressure, the performance cannot be improved. Try to reduce the pressure to obtain higher performance.