Help Center> > Performance White Paper> RDS for MySQL> Test Method

Test Method

Updated at: May 28, 2019 15:45

MySQL is one of the world's most popular open-source relational databases. It works with the Linux, Apache, and PHP (LAMP) stack to provide efficient web solutions. It solves problems such as poor database performance, long data replication delay, and long fault recovery time in high concurrency scenarios.

Huawei's RDS MySQL databases supports immediate use, backup and recovery, 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 configuration while your data integrity and service continuity is guaranteed.

Test Environment

  • Region: CN North-Beijing1
  • AZ: AZ1
  • Elastic Cloud Server (ECS): c3.2xlarge.2 flavor with 8 vCPUs, 16 GB of memory, and CentOS7.4 64 bit image. Bind an elastic IP address (EIP) to the ECS to access the Internet.
    Figure 1 ECS configuration

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.12 is used as an example. Run the following commands to install sysbench:

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

# yum install autoconf libtool mysql mysql-devel vim unzip

# unzip 1.0.12.zip

# cd sysbench-1.0.12

# ./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 the load 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 Models

  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.

Did you find this page helpful?

Submit successfully!

Thank you for your feedback. Your feedback helps make our documentation better.

Failed to submit the feedback. Please try again later.

Which of the following issues have you encountered?







Please complete at least one feedback item.

Content most length 200 character

Content is empty.

OK Cancel