Test Method

Test Environment

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

  The 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. It is most frequently used for database benchmarks. With sysbench, you can quickly get an impression of database performance. For details, visit https://github.com/akopytov/sysbench.

Sysbench 1.0.18 is used as an example. Run the following commands to install it:

#wget -c https://github.com/akopytov/sysbench/archive/1.0.18.zip
#yum install make automake libtool pkgconfig libaio-devel postgresql-devel
#unzip 1.0.18.zip
#cd sysbench-1.0.18
#./autogen.sh
#./configure --with-pgsql --without-mysql
#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 commands to log in to a database and create the test database loadtest:

      psql -h<host> -p5432 "dbname=postgres user=root password=<password>" <<TEST
      create database loadtest;
      TEST

   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=pgsql --pgsql-db=loadtest --pgsql-user=root --pgsql-password=<password> --pgsql-port=5432 --pgsql-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=pgsql --pgsql-db=loadtest --pgsql-user=root --pgsql-password=<password> --pgsql-port=5432 --pgsql-host=<host> --oltp-tables-count=64 --oltp-table-size=10000000 --max-time=3600 --max-requests=0 --num-threads=64 --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=pgsql --pgsql-db=loadtest --pgsql-user=root --pgsql-password=<password> --pgsql-port=5432 --pgsql-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 IDENTITY(1,1) NOT NULL,
   `k` INTEGER DEFAULT '0' NOT NULL,
   `c` CHAR(120) DEFAULT '' NOT NULL,
   `pad` CHAR(60) DEFAULT '' NOT NULL,
   PRIMARY KEY (`id`)
   )

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

• Transactions per second (TPS) refers to the number of transactions executed per second by a database. Each transaction contains 18 SQL statements.