Using Sysbench to Benchmark MySQL 5.7
So, you want to see how many connections your database can take before it gives up? It’s better to know ahead of time when you might expect performance issues than to wait until your service goes down. One of the most common tools around for performance testing is sysbench.
I’ve used sysbench with MySQL 5.5 and 5.6 with no trouble. However, for this particular set of tests, I wanted to use 5.7 as it is supported on AWS in both RDS and Aurora. I’ll run through installing and running sysbench on MySQl 5.7. I experienced a handful of issues; one specific to the 5.7 installation and the others more generic that you will see with default MySQL configurations regardless of the version when benchmarking or under heavy load. For reference, I used an Amazon Linux AMI [Amazon Linux AMI 2017.09.1 (HVM), SSD Volume Type – ami-1853ac65].
Installing MySQL 5.7
This is the easy part. SSH to your cloud server and:
$ sudo yum update -y $ sudo yum install -y mysql57-devel mysql57-server $ sudo service mysqld start
Installing sysbench
To get a current version of sysbench you’ll have to compile from source. From yum you’ll get version 0.5 rather than the 1.x that is available on github. There are more directions on the sysbench github page, even some for adding repos so you can use yum, but this works for me:
$ sudo yum install -y libtool $ wget https://github.com/akopytov/sysbench/archive/master.zip $ unzip master.zip $ cd sysbench-master $ ./autogen.sh $ ./configure $ make $ sudo make install
To make your life a little easier you may want to add the install location to your path as well:
$ PATH=$PATH:/usr/local/bin
Running Sysbench Against MySQL
I typically run sysbench from a shell script. In the prepare, run, and cleanup sections below I’ll provide a sample for testing a database on the same host your logged in to or testing against a database on a different host (like RDB, Aurora, or another VM you have running your database. Regardless, you’ll see the variables I have defined in each here:
dest='/tmp/sysbench' table_size=25000 table_count=250 run_time=60 local_user='your_sysbench_test_user' local_pass='your_secure_password' local_sock='/var/lib/mysql/mysql.sock' local_db='sysbench' rds_host='sysbench.[host_id].[region].rds.amazonaws.com' rds_port='3306' rds_user='you_sysbench_test_user' rds_pass='your_secure_password' rds_db='sysbench'
You also have options to run pre-built scripts to test your database. In my test, sysbench installed these in /usr/local/share/sysbench. Feel free to use these or write your own but note sysbench only supports Lua scripts. The following are the scripts included. Feel free to read through them to see what they are doing. The upside to writing your own or modifying one of the existing scripts is you can customize the output so it’s easier to pull in to Excel or any other application. That’s really outside the scope of this post but if there is demand for it I can cover it in another post.
bulk_insert.lua oltp_common.lua oltp_delete.lua oltp_insert.lua oltp_point_select.lua oltp_read_only.lua oltp_read_write.lua oltp_update_index.lua oltp_update_non_index.lua oltp_write_only.lua select_random_points.lua select_random_ranges.lua
Prepare
The prepare command instructs sysbench to create the tables and populate with data for the tests.
Localhost:
# sysbench --db-driver=mysql --mysql-user=${local_user} --mysql-password=${local_pass} --mysql-socket=${local_sock} --mysql-db=${local_db} --table_size=${table_size} --tables=${table_count} /usr/local/share/sysbench/oltp_read_write.lua prepare
Remote Server:
# sysbench --db-driver=mysql --mysql-user=${rds_user} --mysql-password=${rds_pass} --mysql-host=${rds_host} --mysql-port=${rds_port} --mysql-db=${rds_db} --table_size=${table_size} --tables=${table_count} /usr/local/share/sysbench/oltp_read_write.lua prepare
Run
As mentioned above, I run sysbench from a script so I put my run commands in a for loop stepping up the number of threads each time. Hence you’ll notice the ${thread} variable. You can define this for any value you’d like from 1 to whatever you want to test against. If you set it too high for the resources available to your server you’ll get different responses from sysbench depending on the cause. I’ve outlined several of these in the Issues section below.
Localhost:
# sysbench --db-driver=mysql --mysql-user=${local_user} --mysql-password=${local_pass} --mysql-socket=${local_sock} --mysql-db=${local_db} --mysql-ignore-errors=1062,1213 --rand-type=uniform --table_size=${table_size} --tables=${table_count} --threads=${thread} --time=${run_time} /usr/local/share/sysbench/oltp_read_write.lua run > ${dest}/EC2-MYSQL-RW-${thread}T
Remove Server:
# sysbench --db-driver=mysql --mysql-user=${rds_user} --mysql-password=${rds_pass} --mysql-host=${rds_host} --mysql-port=${rds_port} --mysql-db=${rds_db} --mysql-ignore-errors=1062,1213 --rand-type=uniform --table_size=${table_size} --tables=${table_count} --threads=${thread} --time=${run_time} /usr/local/share/sysbench/oltp_read_write.lua run > ${dest}/RDS-MYSQL-RW-${thread}T
Cleanup
The cleanup command will drop all the tables that were created by the prepare command. It’s highly recommended to cleanup especially if you are going to run more tests with different numbers of tables or different number of rows. Otherwise you’ll have to manually drop all your tables. Using cleanup is much faster!
Localhost:
# sysbench --db-driver=mysql --mysql-user=${local_user} --mysql-password=${local_pass} --mysql-socket=${local_sock} --mysql-db=${local_db} --table_size=${table-size} --tables=${table_count} /usr/local/share/sysbench/oltp_read_write.lua cleanup
Remote Server:
# sysbench --db-driver=mysql --mysql-user=${rds_user} --mysql-password=${rds_pass} --mysql-host=${rds_host} --mysql-port=${rds_port} --mysql-db=${rds_db} --table_size=${table-size} --tables=${table_count} /usr/local/share/sysbench/oltp_read_write.lua cleanup
Issues You Might Encounter
First issue
When you try to execute sysbench you may see:
sysbench: error while loading shared libraries: libmysqlclient.so.1020: cannot open shared object file: No such file or directory
This is a result of MySQL 5.7 installing it’s shared libraries where the version of Linux I’m running is not looking. You’ll see this with the ldd tool and find in libmysqlclient.so.1020 in the output similar to the following:
# ldd /usr/local/bin/sysbench linux-vdso.so.1 => (0x00007ffeb63ce000) libmysqlclient.so.1020 => not found libpthread.so.0 => /lib64/libpthread.so.0 (0x00007fec01912000)
You need to tell the dynamic linker where it can find the MySQL 5.7 libraries:
# echo '/usr/lib64/mysql57' >> /etc/ld.so.conf.d/mysqlclient.conf # ldconfig -v
Now if you re-run sysbench you should be good to go.
Second Issue
When you run sysbench, depending on the number of connections, tables, and rows you may see the following error from MySQL causing sysbench to stop:
FATAL: MySQL error: 1461 "Can't create more than max_prepared_stmt_count statements (current value: 16382)"
This is a setting within MySQL that limits the number of prepared statements that can be passed to the server. The purpose is to limit the potential for denial-of-service attacks based on running the server out of memory by preparing huge numbers of statements. When you are trying to test the limits of your server you need to bump this up a bit. As memory serves the default value for max_prepared_stmt_count is 16382. The maximum you can raise this to is 1048576.
You’ll also want to increase the max_connections as well. The default for MySQL max_connections is 151. This is high enough for many applications but if you are performance testing you’ll want to make sure you have breathing room for your testing.
If you are running MySQL on an EC2 instance you can update these values by editing your /etc/my.cnf file:
max_prepared_stmt_count=1048576 max_connections=16000
Followed by restarting the MySQL service so the changes take effect:
# service mysqld restart
If you are running on AWS RDS or Aurora you need to make these changes in the Parameter Groups section of the RDS console:
Then search for each of the fields and edit them:
I strongly suggest you create a new parameter group for your testing called something like sysbench-rds or sysbench-aurora so you can keep your performance testing parameters separate from your production or dev server configs.
Third Issue
When testing read/write tests with sysbench you’re likely to see the following errors pop up that will skew your results and break your test:
FATAL: mysql_stmt_execute() returned error 1062 (Duplicate entry '2147483647' for key 'PRIMARY') for query 'INSERT INTO sbtest114 (id, k, c, pad) VALUES (?, ?, ?, ?)' FATAL: `thread_run' function failed: /usr/local/share/sysbench/oltp_common.lua:491: SQL error, errno = 1062, state = '23000': Duplicate entry '2147483647' for key 'PRIMARY' FATAL: mysql_stmt_execute() returned error 1213 (Deadlock found when trying to get lock; try restarting transaction) for query 'INSERT INTO sbtest25 (id, k, c, pad) VALUES (?, ?, ?, ?)' FATAL: `thread_run' function failed: /usr/local/share/sysbench/oltp_common.lua:491: SQL error, errno = 1213, state = '40001': Deadlock found when trying to get lock; try restarting transaction
By default, sysbench will ignore the following MySQL errors (source):
- 1213 (
ER_LOCK_DEADLOCK) - 1205 (
ER_LOCK_WAIT_TIMEOUT) - 1020 (
ER_CHECK_READ)
These are listed in --mysql-ignore-errors and cover one of our examples above. When sysbench receives one of the errors listed in --mysql-ignore-errors, it restarts the current transaction but does not increment the TPS counter, as that transaction wasn’t successful. Instead, it increments the “ignored errors” counter. You can add errors to ignore to the --mysql-ignore-errors but it will override the list rather than append to the list, as you’d expect. So you need to list any errors you expect to encounter so they can be ignored. In my case I added --mysql-ignore-errors=1062,1213 option to my sysbench command.
Fourth Issue
This error is relatively self explanatory. For some reason sysbench cannot connect to your database.
I have a script that increments the number of connections to my database and captures the results so I can easily see where performance peaks. After a certain number of connections, depending on the resources you’ve specified for your database, you may cause your server to crash or reboot. During the reboot you won’t be able to connect to the database.
The following day you may also get nice emails from AWS asking you to increase the resources to your database because their admins noticed you kept rebooting due to resource constraints.
FATAL: unable to connect to MySQL server on host 'sysbench-rw.c3gvs4t5hwg8.us-east-1.rds.amazonaws.com', port 3306, aborting... FATAL: error 2003: Can't connect to MySQL server on 'sysbench-rw.c3gvs4t5hwg8.us-east-1.rds.amazonaws.com' (111)
Fifth Issue
Sysbench will basically try to pre-warm all its connections to the database. If your server does not have the memory and resources to handle the number of connections you’ve requested then sysbench will timeout waiting to initialize giving you this error:
FATAL: Worker threads failed to initialize within 30 seconds!
Your only options here will be to increase the resources to your database server VM or count this test as a failure because your VM can’t accommodate the number of connections.