After read/write splitting is enabled, all transactions are routed to the master instance by default. Using Sysbench 0.5, the MySQL stress testing tool, as an example, this document describes how to correctly configure parameters to test read/write splitting performance.

Prerequisites

Attentions

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