Help Center/ Relational Database Service/ Performance White Paper/ RDS for MySQL/ Test Method
Updated on 2024-06-06 GMT+08:00
View PDF
Share

Test Method

MySQL is one of the most popular open-source relational databases. It works with the Linux, Apache, and Perl/PHP/Python to establish a LAMP model for efficient web solutions. It solves problems such as poor database performance, long data replication delay, and slow fault recovery in high-concurrency scenarios.

RDS for MySQL is ready for immediate use, and provides backup and restoration, data migration, security protection, high availability, and elastic scalability. You can obtain a production database with high performance and scalability in a few minutes after simple configurations while your data integrity and service continuity are guaranteed.

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

  • Transactions per second (TPS) refers to the number of transactions executed per second by a database. Each transaction contains 18 SQL statements.
  • Queries per second (QPS) refers to the number of SQL statements, including INSERT, SELECT, UPDATE, and DELETE statements, executed per second.
Parent topic: RDS for MySQL