MySQL Cluster Performance Validation via Load Testing

Your MySQL database cluster contains your most business-critical data and therefore proper performance under load is critical to business health. If response time is slow, customers (and staff) get frustrated and the business suffers a slow-down.

If the database layer is unable to keep up with demand, all applications can and will suffer slow performance as a result.

To prevent this situation, use load tests to determine the throughput as objectively as possible.

In the sample load.pl script below, increase load by increasing the thread quantity.

You could also run this on a database with data in it without polluting the existing data since new test databases are created to match each node's hostname for uniqueness.

Note: The examples in this blog post assume that a Connector is running on each database node and listening on port 3306, and that the database itself is listening on non-standard port 13306.

Install Sysbench

As the `root` user:

wget https://www.percona.com/redir/downloads/percona-release/redhat/latest/percona-release-0.1-4.noarch.rpm
rpm -Uvh percona-release-0.1-4.noarch.rpm
yum install sysbench
sysbench

https://github.com/akopytov/sysbench#usage

Create and Prepare the Test Databases

First, prepare the per-host test databases by writing via the Connector so that the `create database` commands are replicated from the master to all nodes.

Repeat on all nodes as OS user `tungsten`:

export HOST=`/bin/hostname -s`; mysql -u app_user -P3306 -psecret -h127.0.0.1 -e "create database test_$HOST;"
sysbench --db-driver=mysql --mysql-user=app_user --mysql-password=secret --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-db=test_`/bin/hostname -s` --db-ps-mode=disable --range_size=100 --table_size=10000 --tables=2 --threads=1 --events=0 --time=60 --rand-type=uniform /usr/share/sysbench/oltp_read_write.lua prepare

Create and run the load test script

touch load.pl; chmod 755 load.pl; vim load.pl
./load.pl

Below is the load.pl script:

tungsten@db5:/home/tungsten # ./load.pl
Executing: sysbench --db-driver=mysql --mysql-user=app_user --mysql-password=secret --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-db=test_db5 --db-ps-mode=disable --range_size=100 --table_size=10000 --tables=2 --threads=1 --events=0 --time=58 --rand-type=uniform /usr/share/sysbench/oltp_read_write.lua run 
 
sysbench 1.0.15 (using bundled LuaJIT 2.1.0-beta2)
 
Running the test with following options:
Number of threads: 1
Initializing random number generator from current time
 
 
Initializing worker threads...
 
Threads started!
 
SQL statistics:
    queries performed:
        read:                            71204
        write:                           8220
        other:                           22296
        total:                           101720
    transactions:                        5086   (87.68 per sec.)
    queries:                             101720 (1753.69 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)
 
General statistics:
    total time:                          58.0014s
    total number of events:              5086
 
Latency (ms):
         min:                                    9.09
         avg:                                   11.40
         max:                                  218.45
         95th percentile:                       13.70
         sum:                                57978.28
 
Threads fairness:
    events (avg/stddev):           5086.0000/0.00
    execution time (avg/stddev):   57.9783/0.00

• Overall throughput via the Connector (also helps prove out the overall network bandwidth)
• MySQL read and write capabilities on the Master in terms of throughput and system utilization
• Replicator speed to the slaves - check the slave latency - are they up to date? This tests the replicator, the network and the write speed of the slave databases
• Behavior and erformance of the application (do a switch under load. Does it reconnect properly? Are there any issues or errors?)

Perform as much testing as possible BEFORE you go live and save yourself tons of headaches!

To learn about Continuent solutions in general, check out https://www.continuent.com/solutions

For more information about monitoring Tungsten clusters, please visit https://docs.continuent.com/tungsten-clustering-6.0/ecosystem-nagios.html.

Tungsten Clustering is the most flexible, performant global database layer available today - use it underlying your SaaS offering as a strong base upon which to grow your worldwide business!

For more information, please visit https://www.continuent.com/solutions

Want to learn more or run a POC? Contact us.