Test the performance and impact of MariaDB configuration changes.
Too often people make changes to their database configuration without considering the impact it may have.
There is a chance that your change will make the system slower, but you will not know unless you benchmark the system.
TL; DR
Scroll to the bottom of this document to find a way to automate the testing of SysBench and formating the results into a CSV file.
When to benchmark?
Database configuration tweaking is part of being a good DBA. As the data, the users, the application or underlying hardware changes your configuration should too. You may need to increase the max_connections which means you may need to decrease the innodb_buffer_pool_size, but how do you know if this will impact your system performance if you do not test it. There are hundreds of configuration changes you can make and each one of them will have an impact.
Or you may also be considering changing your infrastructure, maybe migrating to Docker and Kubernetes, a cloud provider or different physical hardware. SysBench provides a great set of insights as to how your system will perform on the new infrastructure.
How to benchmark?
There are various tools on the market for benchmarking, but a good standard test is to use sysbench. This is an open-source, free to use application that uses LUA scripts to test, CPU, Memory, IO and usefully to us your database. It comes as standard with a MySQL driver, which can be used to test your MariaDB installation. Within the standard installation, there is a good selection of scripts for testing inserts, deletes, reads and writes.
I have used my MacBook Pro for carrying out these tests, but you can also run the same from a Linux Installation.
Installing SysBench
To install SysBench on the Mac it is as simple as running brew install sysbench or on an RPM-based system you could run yum install sysbench. This provides you with a test application ready to run.
The test scripts
By default, a collection of test scripts are installed. On my Mac, these are in /user/local/Cellar/sysbench/1.0.20/share/sysbench. These scripts can be run in turn against your database installation to find out if the configuration change is going to impact different operations in different ways.
Preparing MariaDB
You need to have a database available to carry out these tests on, and of course, it would be recommended that this is not your live system. To ensure the results are going to mirror that of your live system, your test system should be the same as live. Create a database to use for the tests you can do this simply from the command line by running mysqladmin create sysbenchtest.
You will need to specify any user, host and password flags required. I recommend you create yourself a local .my.cnf to hold these values.
SysBench preparation
Sysbench prepares the database you created, by creating a table and populating it with data. This is the initialisation stage:
sysbench oltp_common --db-driver=mysql --table-size=100000 --mysql-user=<USER NAME> --mysql-password=<PASSWORD> --mysql-port=<PORT> --mysql-host=<HOST IP> --mysql-db=sysbenchtest prepare
Running your first test
To run a test, you can run a similar command, you will note the word prepare becomes run.
sysbench oltp_read_write --db-driver=mysql --table-size=100000 --mysql-user=<USER NAME> --mysql-password=<PASSWORD> --mysql-port=<PORT> --mysql-host=<HOST IP> --mysql-db=sysbenchtest --threads=12 --report-interval=1 run
In this example, SysBench will run an Online Transactional Processing (OLTP) Read Write test against the 100,000 records created.
It is important to note that it is possible to run SysBench with multiple threads and this can be set. When comparing results from multiple tests the number of threads should be the same.
The results
When your specified test has run, hopefully, you’ll see results similar to this image.
You can see that 25 transactions were carried out and each transaction took 2.44 seconds.
In total 500 queries were ran and there were no errors or reconnects.
You will also be interested in the average latency. These results allow you to start to benchmark your system.
Making Changes
Now that you have a benchmark for your system, you can make your configuration changes to MariaDB. If these changes are not dynamic, then you will have to restart the MariaDB service before running the test again. Before you run a new test you need to clear up SysBench.
Clearing up SysBench
Once you have run a test you should clear up the table used by the SysBench test. You need to run a similar command again, but replace the word run with cleanup. This will leave the system ready to prepare then run the next test.
sysbench oltp_common --db-driver=mysql --table-size=100000 --mysql-user=<USER NAME> --mysql-password=<PASSWORD> --mysql-port=<PORT> --mysql-host=<HOST IP> --mysql-db=sysbenchtest cleanup
Clearing up MariaDB
Once you have completed your tests you can drop the database created in the preparation of MariaDB stage. To drop the database you could run mariadb -e 'drop database sysbenchtest'.
Using the Data
The data is output to screen, and you could plot this data into a spreadsheet, to allow you to graph the tests. The following graph displays the tests that I ran on two different sets of infrastructure, using the oltp_read_write test.
Automating the testing and results
By reading this document you will of noticed there are many steps involved in creating the tests and the results are not provided to you in a user-friendly format to be able to model within a spreadsheet.
I have written a simple script that you can run against your system and you can specify the tests you would like to run and for how many threads. The results are collected in a CSV file to allow you to import them to your spreadsheet software and easily compare the results.
You need to ensure SysBench is installed on your client. The script will then take care of the rest for you.
Clone GitHub
Clone my Github project git clone https://github.com/kesterriley/mariadb-sysbench.git, and cd ./mariadb-sysbench. In this directory, there is a .sysbench.cfg file which needs to be provided with some details, your database username, password, hostname and port. You can also set the number of threads you would like to run the tests on and the tests you would like to use.
Once you have configured the .sysbench.cfg file you can execute the main sysbenchtest.sh bash script.
This will prepare a folder of all the results for you and then after each type of test has run, will create a CSV file you.
There are more detailed instructions and usage examples in the README.md file, within the project.
Summary
It is important to test any change to a database system before making it and understanding the impact it may have.
I have made this easier for you with a script, that could be integrated into a performance test every time you do a release.
If you have any issues with the script please use the issues section in the GitHub repositories https://github.com/kesterriley/mariadb-sysbench/issues
