Verify the read/write splitting performance of an RDS MySQL instance -

Prerequisites

The read/write splitting function is enabled. For detailed operations, see Enable read/write splitting for an RDS MySQL instance.
The Sysbench 0.5 is installed. For detailed operations, see the Sysbench documentation.

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

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;

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;
```

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

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;

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;

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;