This topic describes how to use SysBench to test the performance of X-Engine used with ApsaraDB RDS for MySQL. This helps you evaluate the performance of X-Engine.

Prerequisites

  • The default storage engine of your RDS instance is X-Engine.
    Note If X-Engine is used, the value of the XENGINE parameter must be DEFAULT in the Support column.
    MySQL [(none)]> show storage engines;
    +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
    | Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
    +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
    | FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
    | BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
    | XENGINE            | DEFAULT | X-Engine storage engine                                        | YES          | YES  | YES        |
    | MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
    | InnoDB             | YES     | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
    | PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
    | Sequence           | YES     | Sequence Storage Engine Helper                                 | NO           | NO   | NO         |
    | MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
    | MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
    | CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
    | ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
    +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
    11 rows in set (0.00 sec)
  • The table used for testing is stored in X-Engine.
    Note In this example, the table used for testing is created with the ENGINE parameter set to XENGINE. If you set the ENGINE parameter to INNODB or another storage engine, the table used for testing is stored in the specified storage engine rather than X-Engine.
    MySQL [sbtest]> show create table sbtest1;
    +---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | Table   | Create Table                                                                                                                                                                                                                                                                                                                                                          |
    +---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | sbtest1 | CREATE TABLE `sbtest1` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `k` int(11) NOT NULL DEFAULT '0',
      `c` char(120) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
      `pad` char(60) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
      PRIMARY KEY (`id`),
      KEY `k_1` (`k`)
    ) ENGINE=XENGINE AUTO_INCREMENT=2001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci |
    +---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    1 row in set (0.01 sec)
Note We recommend that you use SysBench 1.1.0 or later.

Use DTS to test storage usage

We recommend that you use Alibaba Cloud Data Migration Service (DTS) to migrate your actual database data to your RDS instance and then check the disk usage of X-Engine. In this case, the test results are closer to your actual business situation. X-Engine adopts technologies such as space-friendly storage format, prefix encoding, tiered storage, and efficient compression algorithms to reduce disk usage. The actual effect of these technologies varies based on schemas and record length in your databases. Therefore, if you use your actual database data, you can obtain more accurate test results.

DTS does not support automatic conversion of the storage engine during data migration. You must manually create databases and tables on your RDS instance that runs X-Engine, set the ENGINE parameter to XENGINE in the table creation statements as described in the "Prerequisites" section, and migrate data by using DTS. Do not migrate the schemas.

We recommend that you do not execute SQL statements immediately after the data import is complete. You can monitor the CPU utilization and IOPS usage of your RDS instance in the ApsaraDB RDS console. After the CPU utilization and IOPS approach zero, you can execute SQL statements to test the performance of X-Engine. In this case, the disk usage is calculated more accurately. This is because the log-structured merge-tree (LSM tree) architecture used by X-Engine depends on background asynchronous tasks to implement functions such as data compression. These functions help reduce storage costs. The background asynchronous tasks take some time and consume CPU and IOPS resources.

For more information, see Migrate data between ApsaraDB RDS for MySQL instances.

Use SysBench to test the storage usage

To fully test the compression efficiency of X-Engine, we recommend that you set the table_size parameter in the following command to a large value within the storage capacity for your RDS instance.

We recommend that you monitor the CPU utilization and IOPS usage of your RDS instance in the ApsaraDB RDS console. After the CPU utilization and IOPS approach zero, the storage usage is calculated more accurately.

Run the following command to test the storage usage:

sysbench /usr/share/sysbench/oltp_update_index.lua \
  --mysql-host=[The endpoint of your RDS instance] \
  --mysql-user=sbtest \
  --mysql-password=sbtest@888 \
  --mysql-db=sbtest \
  --threads=32 \
  --tables=32 \
  --table_size=1000000000 \
  --mysql-storage-engine=XENGINE \
  prepare

Use SysBench to test performance

If you use SysBench for performance testing, we recommend that you set the rand-type parameter to zipfian and the rand-zipfian-exp parameter to 0.9.

  • rand-type: specifies the type of the distribution that is used to generate random numbers in SQL statements.
  • Zipfian distribution: common data distribution with hot spots. When the rand-zipfian-exp parameter is set to 0.9, the random numbers generated by using Zipfian distribution are closer to those generated in actual scenarios. The test results are more valuable in comparison to those generated by using the default uniform distribution.

We recommend that you conduct a single test for a long period of time, such as 3,600 seconds. The test results of average performance obtained from a long-time test is more valuable and less affected by potential interference factors.

We recommend that you use a large number of threads, for example, 512 threads, to test the throughput.

To improve the performance of X-Engine by configuring parameters, you can contact your Alibaba Cloud account manager or after-sales engineers.

Run the following command to test the performance:

sysbench /usr/share/sysbench/oltp_point_select.lua \
  --mysql-host=[The endpoint of your RDS instance] \
  --mysql-user=sbtest \
  --mysql-password=sbtest@888 \
  --time=3600 \
  --mysql-db=sbtest \
  --tables=32 \
  --threads=512 \
  --table_size=10000000 \
  --rand-type=zipfian \
  --rand-zipfian-exp=0.9 \
  --report-interval=1 \
  run

Use a Python script to perform multiple tests at a time

If you want to perform multiple tests at a time by using SysBench, we recommend that you use a Python script that can automatically perform the tests and record the test results. When you execute the script, you are prompted to enter the endpoint of your RDS instance. Example:

import subprocess
import time
import sys

def execute_test(test_name, db_conn_string):
  # setup sysbench parameters
  mysql = "--mysql-host=%s" % db_conn_string
  user = "--mysql-user=sbtest"
  password = "--mysql-password=*********"
  time = "--time=3600"
  database = "--mysql-db=sbtest"
  tables = "--tables=32"
  threads = "--threads=512"
  table_size = "--table_size=1000000"
  distribution = "--rand-type=pareto --rand-pareto-h=0.9"
  # formulate the sysbench command
  cmd = 'sysbench ' + test_name + " " + mysql + " " + user+ " " + password + " " + time+ " " + database+ " " + tables + " " + threads + " " + table_size+ " " + distribution+ " " + "--report-interval=1"+ " " +'run'
  # execute
  out = subprocess.check_output(cmd,
    stderr = subprocess.STDOUT, shell=True)
  # output sysbench outputs to a file
  output_file_name = "xengine_result_"+test_name[20:len(test_name)]
  output_file = open(output_file_name, "w")
  output_file.write(out)
  output_file.close()

if __name__ == '__main__':
  # the connection string for the MySQL (X-Engine) instance to be tested
  db_conn_string = sys.argv[1]

  test = [
    "/usr/share/sysbench/oltp_update_index.lua",
    "/usr/share/sysbench/oltp_point_select.lua",
    "/usr/share/sysbench/oltp_read_only.lua",
    "/usr/share/sysbench/oltp_write_only.lua",
    "/usr/share/sysbench/oltp_read_write.lua",
    "/usr/share/sysbench/oltp_insert.lua"
  ]

  for atest in test:
    print("start test:\t%s\t%s" % (atest, time.ctime()))
    execute_test(atest, db_conn_string)
    print("end test:\t%s\t%s" % (atest, time.ctime()))
    # sleep for some seconds
    # after a period of testing with inserts/updates/deletes, x-engine needs some time to complete
    # its asynchronous background compactions.
    time.sleep(1000)