This topic uses Sysbench 0.5, a MySQL stress testing tool, as an example to describe how to correctly configure parameters to verify the read/write splitting performance of an RDS for MySQL instance. After read/write splitting is enabled, all transactions are routed to the master instance by default.

Prerequisites

Precautions

  • We recommend that a case with prepare or a transaction not be for testing the load balance performance of read/write splitting.
  • Prevent the master/slave latency from exceeding the threshold set for the monitoring check due to high read stress.
  • We recommend that you use the following Sysbench scripts to build a specific SQL statement as needed.
    function thread_init(thread_id)
          db_connect()
      end
      function event(thread_id)
          rs =  db_query("select 1")
      end

Set Sysbench parameters

A transaction is used by default to test the Sysbench oltp.lua script. If you use default parameters, all SQL statements are executed in the transaction and read-only SQL statements are routed to the master database for execution. Therefore, when the Sysbench is used to test read/write splitting performance, you must set the Sysbench parameters as needed. For example, you can set the oltp-skip-trx parameter to make sure that the Sysbench does not run the SQL statement in a transaction.

Set common parameters

You can set the following parameters as needed.

Name Description
test Path of the test file
mysql-host IP address of the MySQL server
mysql-port Port of the MySQL server
mysql-user User name
mysql-password Password
mysql-db Database for testing, which must be created in advance
oltp-tables-count Number of created tables
oltp-table-size Number of records generated in each table
rand-init Whether data is randomly initialized
max-time Stress testing duration
max-requests Total number of requests within a stress testing duration
num-threads Number of concurrent threads
report-interval Reporting interval of operating logs

Set parameters for transactions and read/write SQL statements

The following parameters can affect transactions and read/write SQL statements. Therefore, you must set parameters in read/write splitting tests as needed.

Name Description
oltp-test-mode Indicates the test mode. This parameter is unavailable in Sysbench 0.5, so this parameter can be ignored. Possible values:
  • complex: Default value. For transactional tests.
  • simple: Simple test for read-only SQL statements.
  • nontrx: For non-transactional tests.
  • sp: Stored procedures.
oltp-skip-trx Indicates whether "begin" and "commit" of SQL statements are omitted. Possible values:
  • off: Default value. All SQL statements are executed in transactions.
  • on: Non-transactional mode. If a comparative stress test is executed repeatedly, you must run prepare and cleanup again.
Note When a stress test is executed to test the read/write splitting performance, you must set it to on and omit "begin" and "commit" of SQL statements.
oltp-read-only Indicates whether read-only SQL statements are generated. Possible values:
  • off: Default value. The mixed read/write SQL statements of oltp.lua is executed.
  • on: Only read-only SQL statements are generated. UPDATE, DELETE, and INSERT SQL statements are not applicable.
Note Set the parameter value as needed to perform read-only or read/write tests.

Stress testing examples

Test read/write performance
  1. Run the following command to prepare data:
    sysbench --test=./tests/db/oltp.lua --mysql-host=127.0.0.1 --mysql-port=3001 --mysql-user=abc --mysql-password=abc123456 --mysql-db=testdb --oltp-tables-count=10 --oltp-table-size=500000 --report-interval=5 --oltp-skip-trx=on --oltp-read-only=off --rand-init=on --max-requests=0 --max-time=300 --num-threads=100 prepare;
  2. Run the following command to conduct the test:
    Note When data is updated for non-transactional read/write tests, errors such as ALERT: Error 1062 Duplicate entry 'xxx' for key 'PRIMARY' may occur. You must add --mysql-ignore-errors=1062 to skip these errors. If the parameter mysql-ignore-errors does not take effect, your current Sysbench version is too old and you must upgrade it to the latest version.
    sysbench --test=./tests/db/oltp.lua --mysql-host=127.0.0.1 --mysql-port=3001 --mysql-user=abc --mysql-password=abc123456 --mysql-db=testdb --oltp-tables-count=10 --oltp-table-size=500000 --report-interval=5 --oltp-skip-trx=on --oltp-read-only=off --mysql-ignore-errors=1062 --rand-init=on --max-requests=0 --max-time=300 --num-threads=100 run;
  3. Run the following command to clean up data:
    sysbench --test=./tests/db/oltp.lua --mysql-host=127.0.0.1 --mysql-port=3001 --mysql-user=abc --mysql-password=abc123456 --mysql-db=testdb --oltp-tables-count=10 --oltp-table-size=500000 --report-interval=5 --oltp-skip-trx=on --oltp-read-only=off --rand-init=on --max-requests=0 --max-time=300 --num-threads=100 cleanup;
Test read-only performance
  1. Run the following command to prepare data:
    sysbench --test=./tests/db/oltp.lua --mysql-host=127.0.0.1--mysql-port=3001--mysql-user=abc --mysql-password=abc123456 --mysql-db=testdb --oltp-tables-count=10--oltp-table-size=500000--report-interval=5--oltp-skip-trx=on --oltp-read-only=on --rand-init=on --max-requests=0--max-time=300--num-threads=100 prepare;
  2. Run the following command to conduct the test:
    sysbench --test=./tests/db/oltp.lua --mysql-host=127.0.0.1 --mysql-port=3001 --mysql-user=abc --mysql-password=abc123456 --mysql-db=testdb --oltp-tables-count=10 --oltp-table-size=500000 --report-interval=5 --oltp-skip-trx=on --oltp-read-only=on --rand-init=on --max-requests=0 --max-time=300 --num-threads=100 run;
  3. Run the following command to clean up data:
    sysbench --test=./tests/db/oltp.lua --mysql-host=127.0.0.1 --mysql-port=3001 --mysql-user=abc --mysql-password=abc123456 --mysql-db=testdb --oltp-tables-count=10 --oltp-table-size=500000 --report-interval=5 --oltp-skip-trx=on --oltp-read-only=on --rand-init=on --max-requests=0 --max-time=300 --num-threads=100 cleanup;