
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.
Comments
Add new comment