Help Center/ GaussDB(for MySQL)/ Performance White Paper/ Test Method
Updated on 2023-10-18 GMT+08:00
View PDF

Test Method

GaussDB(for MySQL) is a MySQL-compatible, enterprise-grade distributed database service. It uses a compute-storage decoupled architecture and supports up to 128 TB of storage. With GaussDB(for MySQL), there is no need to do sharding, and no need to worry about data loss. It provides the superior performance of commercial databases at the price of open-source databases.

Test Environment

GaussDB(for MySQL) test environment is as follows:

  • Region: EU-Dublin
  • AZ: AZ1
  • Instance: The instance contains a primary node and a read replica for the test.
  • Elastic Cloud Server (ECS): general computing-plus | c7.8xlarge.4 | 32 vCPUs | 128 GB, CentOS 7.6 (64 bit). The ECS and instance nodes are in the same AZ. Bind an EIP to the ECS because additional compilation tools need to be installed on stress testing tools.

Test Tool

Table 1 Test tool

Tool

Description

Version

Sysbench

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

Perform the following commands to install sysbench:

Log in to an ECS and download the sysbench software package:

wget https://codeload.github.com/akopytov/sysbench/zip/refs/tags/1.0.18

yum install -y autoconf libtool mysql mysql-devel vim unzip

Decompress the software package.

unzip 1.0.18

Install the software package.

cd sysbench-1.0.18

./autogen.sh

./configure

make

make install

Test Procedure

The following tests are performed on an ECS. Replace the number of concurrent threads, connection IP address, connection port, username, and user password based on the site requirements.

Performance test data (including SQL) is automatically generated by the sysbench tool.

The ECS and the instance are in the same AZ.

To ensure that sysbench runs properly in high-concurrency scenarios (concurrent requests: 512-1000), increase the value of max_prepared_stmt_count. The recommended value is 1048576. Too many Prepare statements consume a lot of memory space, resulting in out-of-memory (OOM). For an instance with 4 vCPUs and 16 GB memory, set this parameter to 400000.

To improve performance, configure the following parameter and reboot the instance. Modifying the parameter value does not affect the product functions and reliability.

log-bin: OFF

Testing write-only performance:

  1. Import data.

    1. Create the test database sbtest.

      mysql -u<user>-P <port> -h <host> -p -e "create database sbtest"

    2. Import the test background data to the sbtest database.

      sysbench --db-driver=mysql --mysql-host=<host> --mysql-port=<port> --mysql-user=<user> --mysql-password=<password> --mysql-db=sbtest --table_size=25000 --tables=250 --threads=<thread_num> oltp_common prepare

  2. Test the write-only performance. The process takes about 10 minutes.

    sysbench --db-driver=mysql --mysql-host=<host> --mysql-port=<port> --mysql-user=<user> --mysql-password=<password> --mysql-db=sbtest --table_size=25000 --tables=250 --time=600 --threads=<thread_num> --percentile=95 --report-interval=1 oltp_write_only run

  3. Delete data.

    sysbench --db-driver=mysql --mysql-host=<host> --mysql-port=<port> --mysql-user=<user> --mysql-password=<password> --mysql-db=sbtest --table_size=25000 --tables=250 --threads=<thread_num> oltp_common cleanup

Testing read-only performance.

  1. Import data.

    1. Create the test database sbtest.

      mysql -u<user> -P<port> -h<host> -p -e "create database sbtest"

    2. Import the test background data to the sbtest database.

      sysbench --db-driver=mysql --mysql-host=<host> --mysql-port=<port> --mysql-user=<user> --mysql-password=<password> --mysql-db=sbtest --table_size=25000 --tables=250 --threads=<thread_num> oltp_common prepare

  2. Test the read-only performance. The process takes about 10 minutes.

    sysbench --db-driver=mysql --mysql-host=<host> --mysql-port=<port> --mysql-user=<user> --mysql-password=<password> --mysql-db=sbtest --table_size=25000 --tables=250 --time=600 --range_selects=0 --skip-trx=1 --threads=<thread_num> --percentile=95 --report-interval=1 oltp_read_only run

  3. Delete data.

    sysbench --db-driver=mysql --mysql-host=<host> --mysql-port=<port> --mysql-user=<user> --mysql-password=<password> --mysql-db=sbtest --table_size=25000 --tables=250 --threads=<thread_num> oltp_common cleanup

Testing read/write performance:

  1. Import data.

    1. Create the test database sbtest.

      mysql -u<user> -P<port> -h <host> -p -e "create database sbtest"

    2. Import the test background data to the sbtest database.

      sysbench --db-driver=mysql --mysql-host=<host> --mysql-port=<port> --mysql-user=<user> --mysql-password=<password> --mysql-db=sbtest --table_size=250000 --tables=25 --threads=<thread_num> oltp_common prepare

  2. Test the read and write performance. The process takes about 10 minutes.

    sysbench --db-driver=mysql --mysql-host=<host> --mysql-port=<port> --mysql-user=<user> --mysql-password=<password> --mysql-db=sbtest --table_size=250000 --tables=25 --time=600 --threads=<thread_num> --percentile=95 --report-interval=1 oltp_read_write run

  3. Delete data.

    sysbench --db-driver=mysql --mysql-host=<host> --mysql-port=<port> --mysql-user=<user> --mysql-password=<password> --mysql-db=sbtest --table_size=250000 --tables=25 --threads=<thread_num> oltp_common cleanup

Test Metric

  • Transactions per second (TPS) indicates the number of transactions executed per second.
  • Queries per second (QPS) indicates the number of SQL statements, including INSERT, SELECT, UPDATE, and DELETE statements, executed per second.