If you’ve ever had to evaluate server performance, you likely know of sysbench, a performance benchmark project started by Peter Zaitsev with continued development by Alexey Kopitov. The last official release was the version 0.4, which is found in almost all official package repositories in various Linux distributions (although there was also a 0.5 release that was a fork from the main project). Alexey released a major refactor in February and, with that, we have version 1.0.
This refactor was mainly to improve how sysbench behaves with new hardware, but there was also a refactor in the Lua interpreter using LuaJIT. Default parameters changed, and now you can use sysbench as an interpreter for a Lua scripts.
In this post, I will demonstrate a few of the new features and how to use them.
Overwrite the Lua functions
Usually, a benchmark has three main steps:
1- Prepare
This is the step we would usually run before our test; creating a table in the target database and possibly inserting test data that we would run benchmark tests against. It could also create a directory where we would create files if we were to perform a filesystem IO tests, etc.
2- Benchmark
This is the performance test (or tests) occasionally run in multiple iterations with varying degrees of concurrency.
3- Cleanup
Typically run after your benchmark tests are complete, this would remove all data, tables, files, or structure that was created as part of the test preparation.
Each of the steps noted above can be modified by leveraging LUA. In the example below, we will use an empty event, which will logically assess sysbench’s ability to execute the test we created.
$ cat benchmark1.lua
#!/usr/bin/env sysbench
function prepare()
-- Not really needed and not used in this demonstration
print("Preparing the benchmark.")
end
function event()
-- Empty event
end
function cleanup()
-- Not really needed and not used in this demonstration
print("Cleaning up after the benchmark.")
end
And the execution of this benchmark would be:
$ ./benchmark1.lua run
sysbench 1.1.0 (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!
General statistics:
total time: 10.0005s
total number of events: 37180394
Latency (ms):
min: 0.00
avg: 0.00
max: 0.75
95th percentile: 0.00
sum: 2567.46
Threads fairness:
events (avg/stddev): 37180394.0000/0.00
execution time (avg/stddev): 2.5675/0.00
Not very impressive, right? Now let’s play a bit with the command line options.
Let’s run the same amount of events ( 37180394 ) but now with 4 threads, display the histogram and also show a report every second.
$ ./benchmark1.lua run --events=37180394 --threads=4 \
--histogram --report-interval=1
sysbench 1.1.0 (using bundled LuaJIT 2.1.0-beta2)
Running the test with 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 eps: 13146381.45 lat (ms,95%): 0.00
[ 2s ] thds: 4 eps: 13285045.89 lat (ms,95%): 0.00
Latency histogram (values are in milliseconds)
value ------------- distribution ------------- count
0.001 |**************************************** 37173737
(...)
0.141 | 15
0.144 | 11
0.147 | 12
0.149 | 15
0.152 | 10
0.155 | 5
0.158 | 15
0.160 | 12
0.163 | 8
0.166 | 12
0.169 | 13
0.172 | 10
0.176 | 10
0.179 | 13
0.182 | 9
0.185 | 9
0.189 | 7
0.192 | 10
0.196 | 11
0.199 | 11
0.203 | 12
0.206 | 14
0.210 | 12
0.214 | 10
0.218 | 19
(...)
General statistics:
total time: 2.8077s
total number of events: 37180394
Latency (ms):
min: 0.00
avg: 0.00
max: 6.66
95th percentile: 0.00
sum: 2915.96
Threads fairness:
events (avg/stddev): 9295098.5000/615277.33
execution time (avg/stddev): 0.7290/0.04
We were able to execute the same amount of events but in parallel, and also show the progress during the execution and the histogram. Also, the execution time was almost 1/4 of the time of the first execution (2.8077s vs 10 s), demonstrating the efficiency of multi-threading.
Modify the command line options
Another great new feature in sysbench is the availability to modify and add new options in the command line. To do that, we need to use sysbench.cmdline.options.
For example, sysbench has some built-in parameters for MySQL (if you build sysbench with MySQL support), If we want to overwrite some of those default parameters, we can do the following:
sysbench.cmdline.options = {
mysql_user = {"mysql user", "root"},
mysql_password = {"SB password", "sbpass"},
mysql_socket = {"SB socket", "/var/lib/mysql/mysql.sock"},
mysql_db = {"SB db", ""},
}
You can find the full list of options in the struct sb_arg_t in this file.
Also, you can add custom parameters to your script:
sysbench.cmdline.options = {
table_structure = {"Table structure", "i int, v varchar(32)"},
table_engine = {"Table engine", "InnoDB"},
}
After adding the parameters to your script, you can access them from sysbench.opt.PARAMETER_NAME. For example in this case sysbench.opt.table_structure and sysbench.opt.table_engine.
Putting it all together
Now sysbench is more flexible and will allow us to modify almost all the steps used by our benchmarks, so let’s put this all together.
I have created a lua script that creates tables in MySQL and I have also added the options to pass the table structure as a parameter as well as the table engine:
$ cat benchmark2.lua
#!/usr/bin/env sysbench
-- Modifying the commmand line parameters.
sysbench.cmdline.options = {
mysql_user = {"mysql user", "root"},
mysql_password = {"SB password", "sbpass"},
mysql_socket = {"SB socket", "/var/lib/mysql/mysql.sock"},
mysql_db = {"SB db", ""},
table_structure = {"Table structure", "i int, v varchar(32)"},
table_engine = {"Table engine", "InnoDB"},
}
-- Incremental global variable that we will use to create unique tables
table_no=0
-- Function to get a mysql connection
function get_conn()
drv = sysbench.sql.driver()
return drv:connect()
end
-- This function is called every time that we need to initialize a thread.
function thread_init()
-- Every time that we initialize a thread we want to get a new
-- mysql connection to use it.
con = get_conn()
end
-- Prepare function.
function prepare()
print("Preparing the benchmark.")
con = get_conn()
con:query("CREATE DATABASE sysbenchtest;")
end
-- Event function
function event( thread_id )
io.write(".")
table_no = table_no + 1
con:query("CREATE TABLE " ..
" sysbenchtest.t" .. table_no .. "_" .. thread_id ..
" (" .. sysbench.opt.table_structure .. ") " ..
"engine=" .. sysbench.opt.table_engine .. ";")
end
-- Cleanup function
function cleanup()
print("Cleaning up after the benchmark.")
con = get_conn()
con:query("DROP DATABASE sysbenchtest;")
end
Let’s execute the script:
$ ./benchmark2.lua prepare
sysbench 1.1.0 (using bundled LuaJIT 2.1.0-beta2)
Preparing the benchmark. Database sysbenchtest created successfully.
Ok, now we are ready to run our benchmark. By default sysbench will execute the event as many times as it can for 10 seconds.
$ ./benchmark2.lua run
sysbench 1.1.0 (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: 0
write: 0
other: 1915
total: 1915
transactions: 1915 (190.88 per sec.)
queries: 1915 (190.88 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 10.0324s
total number of events: 1915
Latency (ms):
min: 3.81
avg: 5.21
max: 84.75
95th percentile: 6.09
sum: 9980.40
Threads fairness:
events (avg/stddev): 1915.0000/0.00
execution time (avg/stddev): 9.9804/0.00
Let’s check the number of tables into MySQL after the benchmark test is complete:
mysql> SELECT count(*) FROM information_schema.tables
WHERE table_schema='sysbenchtest';
+----------+
| count(*) |
+----------+
| 1915 |
+----------+
And now we will cleanup the benchmark:
$ ./benchmark2.lua cleanup
sysbench 1.1.0 (using bundled LuaJIT 2.1.0-beta2)
Cleaning up after the benchmark.
Let’s try some different options, this time changing the table structure by adding a primary key, and also specifying that concurrency should be included by running this in 4 threads.
$ ./benchmark2.lua run --table-structure="i int, v varchar(32),primary key (i)" \
--threads=4 --events=1000
sysbench 1.1.0 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options:
Number of threads: 4
Initializing random number generator from current time
Initializing worker threads...
Threads started!
.................................................................................
.................................................................................
.................................................................................
(...)
SQL statistics:
queries performed:
read: 0
write: 0
other: 1000
total: 1000
transactions: 1000 (180.16 per sec.)
queries: 1000 (180.16 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 5.5506s
total number of events: 1000
Latency (ms):
min: 5.83
avg: 22.09
max: 474.74
95th percentile: 29.72
sum: 22090.16
Threads fairness:
events (avg/stddev): 250.0000/2.55
execution time (avg/stddev): 5.5225/0.01
Since we added the thread_id in the name of the table ( ” sysbenchtest.t” .. table_no .. “_” .. thread_id ), now we can count how many tables were created by each thread.
The table names are as follows:
mysql> SELECT table_name FROM information_schema.tables
WHERE table_schema='sysbenchtest' LIMIT 12;
+------------+
| table_name |
+------------+
| t100_0 |
| t100_1 |
| t100_2 |
| t100_3 |
| t101_0 |
| t101_1 |
| t101_2 |
| t101_3 |
| t102_0 |
| t102_1 |
| t102_2 |
| t102_3 |
+------------+
Now let’s count the number of tables per thread:
mysql> SELECT count(*) FROM information_schema.tables
WHERE table_schema='sysbenchtest' AND table_name like "%_0";
+----------+
| count(*) |
+----------+
| 252 |
+----------+
mysql> SELECT count(*) FROM information_schema.tables
WHERE table_schema='sysbenchtest' AND table_name like "%_1";
+----------+
| count(*) |
+----------+
| 248 |
+----------+
mysql> SELECT count(*) FROM information_schema.tables
WHERE table_schema='sysbenchtest' AND table_name like "%_2";
+----------+
| count(*) |
+----------+
| 253 |
+----------+
mysql> SELECT count(*) FROM information_schema.tables
WHERE table_schema='sysbenchtest' AND table_name like "%_3";
+----------+
| count(*) |
+----------+
| 247 |
+----------+
As you can see, there is no improvement (in 10 seconds we have created 1915 tables and in 5.5 seconds we were able to create 1000) when we execute concurrently with 4 threads due to the dictionary mutex that InnoDB uses when you create a table.
For additional consideration, if we create MyISAM tables this is 5X times faster ;).
Disclaimer: This MyISAM example is just a representative example as MyISAM has a lot of disadvantages that are noteworthy, but out of the scope for this post.
$ ./benchmark2.lua run --table-structure="i int, v varchar(32),primary key (i)" \
--threads=4 --events=1000 --table-engine=myisam
sysbench 1.1.0 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options:
Number of threads: 4
Initializing random number generator from current time
Initializing worker threads...
Threads started!
.................................................................................
.................................................................................
.................................................................................
(...)
SQL statistics:
queries performed:
read: 0
write: 0
other: 1000
total: 1000
transactions: 1000 (995.92 per sec.)
queries: 1000 (995.92 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 1.0041s
total number of events: 1000
Latency (ms):
min: 0.84
avg: 3.99
max: 110.10
95th percentile: 11.04
sum: 3988.50
Threads fairness:
events (avg/stddev): 250.0000/49.10
execution time (avg/stddev): 0.9971/0.01
Conclusion
Sysbench is now more flexible, simpler and has better performance. This is just the beginning.
Many things are in the roadmap including improving the documentation, MongoDB support, centralized repository to share benchmarks, etc. I think this is great news for the community.
1 Comment. Leave new
great job!