If you work with MySQL on a consistent basis, then you most possibly have got of it. SysBench has been in the MySQL system for a extended time. It was intended to run CPU, memory and I/O tests. It had also a choice to perform OLTP capability on a MySQL database. OLTP stances for online transaction dealing out, typical workload for online requests like e-commerce, order entry or financial transaction structures.
we will emphasis on the SQL benchmark feature but have in mind that hardware benchmarks can also be very useful in recognizing issues on database servers. For example, I/O benchmark was planned to put on InnoDB I/O workload while CPU tests contain mockup of highly concurrent, multi-threaded setting along with tests for mutex disputes - somewhat which also look like a database type of load.
What tests we can do
As stated at the beginning, we will emphasis on OLTP benchmarks and just as a notice we’ll recurrence that SysBench can also be used to do I/O, CPU and memory tests. Let’s look at the benchmarks that SysBench comes with.
How you can arrange a benchmark
What is also significant, benchmarks are configurable - you can run diverse workload designs using the same benchmark. Let’s look at the two most shared benchmarks to execute. We’ll have a deep understanding into OLTP read_only and OLTP read_write benchmarks. First, SysBench has some all-purpose configuration choices. We will debate here only the most significant ones, you can crisscross all of them by running:
sysbench –help
In adding to the over-all configuration options, each of the tests may have its own arrangement. You can check what is likely by running:
Let’s make our dataset. Please have in attention that, by default, SysBench looks for ‘sbtest’ schema which has to be before you make the data set. You may have to create it yourself.
Once we take our data, let’s make a command to run the assessment. We want to assessment Primary Key lookups then we will deactivate all other forms of SELECT. We will also deactivate prepared statements as we want to test systematic queries. We will exam low concurrency, let’s say 16 threads. Our expertise may look like below:
sysbench /usr/share/sysbench/oltp_read_write.lua --threads=16 --events=0 --time=30 --mysql-host=localhost --mysql-user=root --mysql-password=password --mysql-port=3306 --tables=10 --table-size=100000 --range_selects=off --db-ps-mode=disable --report-interval=1 --db-driver=mysql --histogram run
We set the figure of threads to 16. We definite that we want our benchmark to run for 30 seconds, deprived of a limit of completed queries. We defined connectivity to the database, number of tables and their size. Finally, we set report interval to one second. This is how a sample output may look like:
Every second we get a snapshot of load stats. This is fairly useful to track and plot - final report will give you arithmetic mean only. Intermediate results will make it likely to track the performance on a second by second basis. The ending report may look like below:
You will find here info about executed queries and other (BEGIN/COMMIT) declarations. You’ll learn how many transactions were performed, how many errors happened, what was the material and total elapsed time. You can also crisscross latency metrics and the query spreading across threads.
If we were involved in latency distribution, we could also pass ‘--histogram’ argument to SysBench. This results in an extra yield like below:
Conclusion
As we presented above, SysBench is a great tool which can help to locate some of the performance issues of MySQL.