IT Tutorial IT Tutorial | Oracle DBA | SQL Server, Goldengate, Exadata, Big Data, Data ScienceTutorial
In this article we are going to discuss sysbench, the actual standard for MySQL benchmarking. We are going to take a look at sysbench usage basics and how can we use sysbench for learning about MySQL and the second is the most important aspect for us. We will practically use sysbench as a tool to generate traffic which we know a lot about because sysbench will hold put some information about the generated traffic every second.
SysBench MySQL Test
Sysbench is a multi-threaded benchmark tool based on luaJIT it’s the actual standard for MySQL benchmarks, it needs to be able to connect to the database.
Sysbench Installation
First, we need to install the sysbench, I am installing sysbench on another server so that we can test the actual impact of load on our MySQL server.
curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.rpm.sh | sudo bash yum -y install sysbench
That’s set it’s very easy to install sysbench, it’s better to allow sysbench to interact with the MySQL Server at firewall level, as this a test environment I have disabled the firewall on both hosts to prevent any difficulties.
Ready Environment for SysBench:
For this test, I am creating the sbtest database and user sbtest_user and will grant all PRIVILEGES to sbtest_user on sbtest database.
using root;
mysql> create database sbtest mysql> create user sbtest_user identified by 'password'; mysql> grant all on sbtest.* to `sbtest_user`@`%`; mysql> show grants for sbtest_user; +---------------------------------------------------------+ | Grants for sbtest_user@% | +---------------------------------------------------------+ | GRANT USAGE ON *.* TO `sbtest_user`@`%` | | GRANT ALL PRIVILEGES ON `sbtest`.* TO `sbtest_user`@`%` | +---------------------------------------------------------+
Performance of MySQL Using SysBench
Benchmark Configuration:
The prepare step of sysbench creates the tables with the data that will be used in the benchmark. In this example we are running the prepare command. There are a few parameters with MySQL at the beginning, those will be the connection parameters. The other parameters are parameters of the oltp_read_write.lua test and we are specifying the test itself which is oltp_read_write.lua and that we are running the prepare command. The option starting with MySQL are specifying the MySQL connection, the hostname and port to connect to, the username and password to connect with, and the default schema for the connection. The tables and the table_size parameters are the properties of the oltp_read_write.lua test.
This means that the prepare step will create 16 tables with 10,000 rules in each of them. The next step is to run the benchmark.
To run typically all the parameters are passed which will pass to prepared and some additional ones which we were review now, these are specific to the actual run of the benchmark. The “TIME” parameter specifies the time limit for the benchmark to run, zero means unlimited time, the benchmark will run until we hit control+c. This is how we will use sysbench in the lab and this is how people typically use it in learning and not in a benchmarking setup.
We just want to unleash traffic on something we’ll examine and we can stop it with control+c once we are done with the examination.
The “report-interval” parameters specify how frequently sysbench were printed statistics. Typically, this is set to 1 like in our example which one makes sysbench print out the line for every second. Even in benchmarking setups this parameter is widely used because imagine if we have an hour-long benchmark and we only have aggregate statistics at the end, that doesn’t tell anything about the distribution of the data like how performance was on the server over time. The “thread” option specifies the number of client threads or MySQL connections to use in sysbench. The number of client’s threads will also have an effect on the number of server threads that can be used. The “rate” parameter specifies the arrival rate of sysbench transactions as a way of really meeting the load caused by the benchmark. If transactions can proceed, they are queued, this is again something which is typically used in this kind of setup what we are going to use now in a learning type of setup.
From sysbench host:
Prepare a data set:
On the benchmarking virtual machine, we are going to run the sysbench prepare command to create a database for our benchmarks.
Here we can see that we are using the sbtest_user it as a username, the password is password and we are connecting to 192.168.66.5 DB as the database server.
sysbench \ --db-driver=mysql \ --mysql-user=sbtest_user \ --mysql_password=password \ --mysql-db=sbtest \ --mysql-host=192.168.66.5 \ --mysql-port=3306 \ --tables=16 \ --table-size=10000 \ /usr/share/sysbench/oltp_read_write.lua prepare sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2) Creating table 'sbtest1'... Inserting 10000 records into 'sbtest1' Creating a secondary index on 'sbtest1'... . . . Creating table 'sbtest16'... Inserting 10000 records into 'sbtest16' Creating a secondary index on 'sbtest16'..
You have the sbtest database right here, let’s change the default schema to the sbtest database, check what tables do we have.
We specified that the benchmark should create sixteen tables and it did create 16 tables, we can see it here
mysql> show tables; +------------------+ | Tables_in_sbtest +------------------+ | sbtest1 | | sbtest2 | . . . | sbtest16 | +------------------+ 16 rows in set (0.01 sec)
Let’s check a few records from a table.
mysql> select * from sbtest1 limit 6;
we are going to run a benchmark. This benchmark will have one line of output for every second because we set rapport the interval equals one and it has four client threads because we set threads equals four.
--events=N limit for total number of events [0] --time=N limit for total execution time in seconds [10]
Those above two settings (events and time) govern how long SysBench should keep running. It can either execute some number of queries or it can keep running for a predefined time.
On sysbench host:
sysbench \ --db-driver=mysql \ --mysql-user=sbtest_user \ --mysql_password=password \ --mysql-db=sbtest \ --mysql-host=192.168.66.5 \ --mysql-port=3306 \ --tables=16 \ --table-size=10000 \ --threads=4 \ --time=0 \ --events=0 \ --report-interval=1 \ /usr/share/sysbench/oltp_read_write.lua run WARNING: Both event and time limits are disabled, running an endless test sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2) Running the test with the following options: Number of threads: 4 Report intermediate results every 1 second(s) Initializing random number generator from current time Initializing worker threads... Threads started! [ 1s ] thds: 4 tps: 62.79 qps: 1320.63 (r/w/o: 933.91/257.15/129.57) lat (ms,95%): 80.03 err/s: 0.00 reconn/s: 0.00 [ 2s ] thds: 4 tps: 77.01 qps: 1530.26 (r/w/o: 1065.18/312.05/153.03) lat (ms,95%): 61.08 err/s: 0.00 reconn/s: 0.00 [ 3s ] thds: 4 tps: 74.03 qps: 1463.67 (r/w/o: 1025.47/289.13/149.07) lat (ms,95%): 70.55 err/s: 0.00 reconn/s: 0.00 [ 4s ] thds: 4 tps: 69.99 qps: 1414.84 (r/w/o: 991.89/282.97/139.98) lat (ms,95%): 65.65 err/s: 0.00 reconn/s: 0.00 [ 5s ] thds: 4 tps: 74.02 qps: 1488.34 (r/w/o: 1048.24/292.07/148.03) lat (ms,95%): 74.46 err/s: 0.00 reconn/s: 0.00 [ 6s ] thds: 4 tps: 72.99 qps: 1444.89 (r/w/o: 1003.92/294.98/145.99) lat (ms,95%): 70.55 err/s: 0.00 reconn/s: 0.00 [ 7s ] thds: 4 tps: 63.00 qps: 1271.04 (r/w/o: 890.03/255.01/126.00) lat (ms,95%): 87.56 err/s: 0.00 reconn/s: 0.00 [ 8s ] thds: 4 tps: 72.99 qps: 1439.82 (r/w/o: 1008.87/284.96/145.98) lat (ms,95%): 73.13 err/s: 0.00 reconn/s: 0.00 [ 9s ] thds: 4 tps: 74.00 qps: 1488.01 (r/w/o: 1038.01/302.00/148.00) lat (ms,95%): 73.13 err/s: 0.00 reconn/s: 0.00
so we can see that it’s doing roughly 70 80 transactions per second on my machine which translates to roughly more than a thousand queries per second. This is running in VirtualBox on a laptop.
From these queries, we can see how many of them are reads, how many of them are writes, how many of them are other what’s the 95th percentile latency for the transaction (r/w/o: 1038.01/302.00/148.00), how many errors per second (err/s: 0.00 ) we have and how many connections per second (reconn/s: 0.00) we have. Because we specify time equals zero this will run until we hit ctrl+c.
Let’s check the show process list on the database host.
mysql> show processlist; +----+-----------------+--------------------+--------+---------+-------+----------------------------+--------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-----------------+--------------------+--------+---------+-------+----------------------------+--------------------------------------+ | 5 | event_scheduler | localhost | NULL | Daemon | 23200 | Waiting on empty queue | NULL | | 11 | root | localhost | NULL | Sleep | 18438 | | NULL | | 19 | root | localhost | sbtest | Query | 0 | starting | show processlist | | 23 | root | localhost | NULL | Sleep | 4098 | | NULL | | 30 | sbtest_user | 192.168.66.6:37298 | sbtest | Sleep | 0 | | NULL | | 31 | sbtest_user | 192.168.66.6:37300 | sbtest | Execute | 0 | waiting for handler commit | COMMIT | | 32 | sbtest_user | 192.168.66.6:37302 | sbtest | Sleep | 0 | | NULL | | 33 | sbtest_user | 192.168.66.6:37304 | sbtest | Execute | 0 | Opening tables | SELECT c FROM sbtest13 WHERE id=4978 | +----+-----------------+--------------------+--------+---------+-------+----------------------------+--------------------------------------+
8 rows in set (0.00 sec)
The database server was practically always busy. I saw that the executed time never changes from zero practically, and it was very easy to catch the database server in action like when it’s running “SELECT c FROM sbtest13 WHERE id=4978”. And definitely, we have four connections from the benchmarking machine
By default, SysBench will attempt to execute queries as fast as possible. To simulate slower traffic this option may be used. You can define here how many transactions should be executed per second.
--rate=N average transactions rate. 0 for unlimited rate [0]
On sysbench host
[root@localhost ~]# sysbench \ --db-driver=mysql \ --mysql-user=sbtest_user \ --mysql_password=password \ --mysql-db=sbtest \ --mysql-host=192.168.66.5 \ --mysql-port=3306 \ --tables=16 \ --table-size=10000 \ --threads=4 \ --time=0 \ --events=0 \ --report-interval=1 \ --rate=40 \ /usr/share/sysbench/oltp_read_write.lua run WARNING: Both event and time limits are disabled, running an endless test sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2) Running the test with following options: Number of threads: 4 Target transaction rate: 40/sec Report intermediate results every 1 second(s) Initializing random number generator from current time Initializing worker threads... Threads started! [ 1s ] thds: 4 tps: 42.87 qps: 858.43 (r/w/o: 600.20/171.49/86.74) lat (ms,95%): 73.13 err/s: 0.00 reconn/s: 0.00 [ 1s ] queue length: 0, concurrency: 1 [ 2s ] thds: 4 tps: 41.01 qps: 857.25 (r/w/o: 609.17/164.05/84.02) lat (ms,95%): 101.13 err/s: 0.00 reconn/s: 0.00 [ 2s ] queue length: 0, concurrency: 3 [ 3s ] thds: 4 tps: 57.01 qps: 1119.29 (r/w/o: 778.20/228.06/113.03) lat (ms,95%): 73.13 err/s: 0.00 reconn/s: 0.00 [ 3s ] queue length: 0, concurrency: 2 . . . [ 15s ] thds: 4 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00 [ 15s ] queue length: 145, concurrency: 4 [ 16s ] thds: 4 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00 [ 16s ] queue length: 179, concurrency: 4
So the new parameter here is –rate equals 40 which means that we will have two lines per second two lines of output and not one. Because we set the arrival rate of the benchmarking events to 40 per second, we will see the current TPS.
This is not guaranteed to be 40/second but the arrival guarantees it that on average we roughly do 40 transactions per second and we can monitor the queue length and the concurrency on the second line. If we do a short process list it’s much easier to catch the database in a state where some connections are just waiting here.
While a session is busy you can see that transaction per second is zero (tps: 0.00 ).
mysql> show processlist; +----+-----------------+--------------------+--------+---------+-------+------------------------+------------------------------------------------------------------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-----------------+--------------------+--------+---------+-------+------------------------+------------------------------------------------------------------------------------------------------+ | 5 | event_scheduler | localhost | NULL | Daemon | 19162 | Waiting on empty queue | NULL | | 8 | root | localhost | NULL | Query | 0 | starting | show processlist | | | 21 | sbtest_user | 192.168.66.6:49060 | sbtest | Execute | 33 | updating | UPDATE sbtest8 SET k=k+1 WHERE id=5005 | | 22 | sbtest_user | 192.168.66.6:49062 | sbtest | Execute | 22 | updating | UPDATE sbtest14 SET c='54592761471-89397085016-24424731626-29460127219-18466786462-73074657089-48925 | 23 | sbtest_user | 192.168.66.6:49064 | sbtest | Execute | 21 | updating | UPDATE sbtest10 SET c='68520795048-46094139936-88850487689-12482054639-29231339380-71050139550-93403 | | 24 | sbtest_user | 192.168.66.6:49066 | sbtest | Execute | 31 | updating | DELETE FROM sbtest14 WHERE id=4994 | +----+-----------------+--------------------+--------+---------+-------+------------------------+------------------------------------------------------------------------------------------------------+ 10 rows in set (0.00 sec)
We can see that this is sleeping for a few seconds, this was pretty much impossible in the previous scenario to get something like this.
Write-heavy traffic with end Report:
Let’s execute a write-heavy (but not write-only) workload and, for example, the test I/O subsystem’s performance, as I mentioned time=300 then the benchmark will run for 300sec and It will give us an end report to analyze it.
[root@localhost ~]# sysbench \ --db-driver=mysql \ --mysql-user=sbtest_user \ --mysql_password=password \ --mysql-db=sbtest \ --mysql-host=192.168.66.5 \ --mysql-port=3306 \ --tables=16 \ --table-size=10000 \ --threads=8 \ --time=300 \ --events=0 \ --report-interval=1 \ --rate=40 \ /usr/share/sysbench/oltp_read_write.lua run sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2) Running the test with following options: Number of threads: 8 Target transaction rate: 40/sec Report intermediate results every 1 second(s) Initializing random number generator from current time Initializing worker threads... Threads started! [ 1s ] thds: 8 tps: 39.87 qps: 810.27 (r/w/o: 570.08/159.46/80.73) lat (ms,95%): 82.96 err/s: 0.00 reconn/s: 0.00 [ 1s ] queue length: 0, concurrency: 1 [ 2s ] thds: 8 tps: 43.02 qps: 847.39 (r/w/o: 590.27/172.08/85.04) lat (ms,95%): 125.52 err/s: 0.00 reconn/s: 0.00 [ 2s ] queue length: 0, concurrency: 0 . . . [ 350s ] thds: 8 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00 [ 350s ] queue length: 6545, concurrency: 1 SQL statistics: queries performed: read: 78624 write: 22385 other: 11205 total: 112214 transactions: 5589 (15.94 per sec.) queries: 112214 (320.02 per sec.) ignored errors: 27 (0.08 per sec.) reconnects: 0 (0.00 per sec.) General statistics: total time: 350.6412s total number of events: 5589 Latency (ms): min: 12.45 avg: 74639.59 max: 213244.02 95th percentile: 100000.00 sum: 417160677.24 Threads fairness: events (avg/stddev): 698.6250/196.36 execution time (avg/stddev): 52145.0847/15557.93
REPORT ANALYSIS:
This is quite useful to check the final report will give you averages only. Intermediate results will make it possible to track the performance on a second by second basis. The final report may look like above, You will find here information about executed queries, transactions were executed, how many errors happened, any connection lost happened, what was the throughput and total elapsed time. You can also check latency metrics and the query distribution across threads.
