Test Method

Test Environment

• Elastic Cloud Server (ECS): general computing | c3.2xlarge.2 | 8 vCPUs | 16 GB, CentOS7.4 64 bit image. Bind an elastic IP (EIP) to the ECS because additional compilation tools need to be installed on stress testing tools.
  

  RDS for MySQL 8.0 test environment is as follows:

  • ECS: general computing-plus | c6.4xlarge.2 | 16 vCPUs | 32 GB, CentOS 7.6 (64 bit). Bind an EIP to the ECS because additional compilation tools need to be installed on stress testing tools.

Test Tool

Sysbench is a multi-threaded benchmark tool based on LuaJIT, allowing you to quickly get an impression of system performance by using a built-in database test model. For details, visit https://github.com/akopytov/sysbench.

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

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

# yum install autoconf libtool mysql mysql-devel vim unzip

# unzip 1.0.18.zip

# cd sysbench-1.0.18

# ./autogen.sh

# ./configure

# make

# make install

Test Procedure

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

1. Import data.
   1. Run the following command to log in to a database and create the test database loadtest:

      mysql -u root -P 3306 -h <host> -p -e "create database loadtest"

   2. Run the following command to import the test background data to the loadtest database:

      sysbench --test=/usr/local/share/sysbench/tests/include/oltp_legacy/oltp.lua --db-driver=mysql --mysql-db=loadtest --mysql-user=root --mysql-password=<password> --mysql-port=3306 --mysql-host=<host> --oltp-tables-count=64 --oltp-table-size=10000000 --num-threads=20 prepare

2. Run the following command to perform a stress testing:

   sysbench --test=/usr/local/share/sysbench/tests/include/oltp_legacy/oltp.lua --db-driver=mysql --mysql-db=loadtest --mysql-user=root --mysql-password=<password> --mysql-port=3306 --mysql-host=<host> --oltp-tables-count=64 --oltp-table-size=10000000 --max-time=3600 --max-requests=0 --num-threads=200 --report-interval=3 --forced-shutdown=1 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=loadtest --mysql-user=root --mysql-password=<password> --mysql-port=3306 --mysql-host=<host> --oltp-tables-count=64 --oltp-table-size=10000000 --max-time=3600 --max-requests=0 --num-threads=200 cleanup

Testing Model

1. Table structure:

   CREATE TABLE `sbtest` (

   `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,

   `k` INTEGER UNSIGNED DEFAULT '0' NOT NULL,

   `c` CHAR(120) DEFAULT '' NOT NULL,

   `pad` CHAR(60) DEFAULT '' NOT NULL,

   PRIMARY KEY (`id`)

   ) ENGINE=InnoDB

2. Read/write ratio:

   The default transaction submitted by sysbench contains 18 SQL statements. The details are as follows:

   • Ten primary key select statements:

     SELECT c FROM ${rand_table_name} where id=${rand_id};

   • Four range select statements:

     SELECT c FROM ${rand_table_name} WHERE id BETWEEN ${rand_id_start} AND ${rand_id_end};

     SELECT SUM(K) FROM ${rand_table_name} WHERE id BETWEEN ${rand_id_start} AND ${rand_id_end};

     SELECT c FROM ${rand_table_name} WHERE id BETWEEN ${rand_id_start} AND ${rand_id_end} ORDER BY c;

     SELECT DISTINCT c FROM ${rand_table_name} WHERE id BETWEEN ${rand_id_start} AND ${rand_id_end} ORDER BY c;

   • Two update statements:

     UPDATE ${rand_table_name} SET k=k+1 WHERE id=${rand_id}

     UPDATE ${rand_table_name} SET c=${rand_str} WHERE id=${rand_id}

   • One delete statement:

     DELETE FROM ${rand_table_name} WHERE id=${rand_id}

   • One insert statement:

     INSERT INTO ${rand_table_name} (id, k, c, pad) VALUES (${rand_id},${rand_k},${rand_str_c},${rand_str_pad})

Test Metrics

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