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 this article, a sample load.pl script is provided, which uses sysbench to run a load through a Tungsten Connector. 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.
Install the MySQL Command Line Client
You must have the mysql command available to run the load tests. The procedure for installing the client binary will vary widely from OS to OS and distribution to distribution.
For example, on my AWS Linux 2 host:
sudo yum -y install mysql-community-client
yum list installed | grep -i mysql
mysql -VInstall sysbench
The instructions for the various OS targets are located in the GitHub repository page.
For example, for Linux:
curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.rpm.sh | sudo bash
sudo yum -y install sysbench
sysbenchhttps://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 Primary to all other nodes.
Repeat on all nodes as the Tungsten OS user (default: tungsten):
sudo su - 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 prepareCreate the Load Test Script
Note that SSL support is enabled by default in the load.pl script below. If you are not using SSL to MySQL, then set --mysql-ssl=off in the script.
shell> cd
shell> touch load.pl; chmod 755 load.pl
shell> vim load.pl
#!/usr/bin/env perl
### load.pl
use strict;
our $time = 58;
our $threads = 1;
our $name = `/bin/hostname -s`;
chomp($name);
$name =~ s/-/_/g;
our $cmd = <<EOT;
(cd; sysbench --mysql-debug=off --db-driver=mysql --mysql-ssl=on --mysql-user=app_user --mysql-password=secret --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-db=test_$name --db-ps-mode=disable --range_size=100 --table_size=10000 --tables=2 --threads=$threads --events=0 --time=$time --rand-type=uniform /usr/share/sysbench/oltp_read_write.lua run)
EOT
print "Executing: $cmd\n";
system($cmd);
exit 0;
Please feel free to modify this script to suit your needs. This script is supposed to run a load using parallel connections (threads) through the Tungsten Connector into your target database. The best way to learn the limits of your system is to experiment with the thread count. After each test run, gradually increment the $threads value (INT) to find the best settings and performance results for your application (connections pool), database (connections limit) and Tungsten Connector.
Get the Load Test Script to Work With SSL
If sysbench is started with the --mysql-ssl=on option, it looks in the current directory for the following 3 files:
-
client-cert.pem -
client-key.pem -
cacert.pem(note no dash)
Locate the current MySQL database certificates, usually in /var/lib/mysql, and make sure that the OS user running sysbench is able to read the following 3 files:
-
ca.pem -
client-cert.pem -
client-key.pem
Next, cd to the OS user’s home directory and create symbolic links as follows:
export DIR=/var/lib/mysql
cd
ln -s ${DIR}/ca.pem cacert.pem
ln -s ${DIR}/client-cert.pem
ln -s ${DIR}/client-key.pem
Note that source file $DIR/ca.pem is sym-linked to target ~/cacert.pem.
Run the Load Test Script
cd
./load.pl
Below is the load.pl script in action:
shell> ./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 performance 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 our products.
For more information about monitoring Tungsten clusters, please visit our documentation.
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!
Want to learn more or run a POC? Contact us.
Comments
Add new comment