Sysbench is a popular testing tool. It is mainly used to test the performance of fileio, cpu, memory, threads, mutex, etc., but the most important thing is to test the performance of databases. It is often used to test MySQL, Drizzle, AttachSQL, Oracle, PostgreSQL and other related databases. It can also be extended to support other databases and test cases, This paper mainly introduces the code logic, code debugging, lua script and related extended functions of sysbench.
This article mainly introduces the code based on sysbench 1.0:
repo: https://github.com/akopytov/sysbench
branch: 1.0
1. Code logic
The code logic of sysbench is as follows (some code files that do not require additional instructions have been filtered out):
sysbench/
├── src
│ ├── db_driver.c // The db driver test function interface is mainly connected in series with sysbench test framework and specific db driver
│ ├── db_driver.h // db driver interface declaration. See the drivers directory below for specific implementation
│ ├── drivers // Specific driver driver implementation
│ │ ├── attachsql
│ │ ├── drizzle
│ │ ├── mysql
│ │ ├── oracle
│ │ └── pgsql
│ ├── lua // Implementation of built-in oltp testing tool
│ │ ├── bulk_insert.lua
│ │ ├── internal
│ │ ├── 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
│ ├── sb_barrier.h // Thread barrier for thread synchronization
│ ├── sb_ck_pr.h // Atomic operation, specifically in.. / third_party/concurrency_kit
│ ├── sb_counter.h // Counter program, due to statistics
│ ├── sb_global.h
│ ├── sb_histogram.h // todo
│ ├── sb_list.h // C language implementation of List
│ ├── sb_logger.h // logger implementation
│ ├── sb_lua.c
│ ├── sb_lua.h // Connect C and Lua programs in series. Objectives: 1. Lua can call DB driver API in C; 2. C can call Lua program
│ ├── sb_options.h // Parameter parsing implementation
│ ├── sb_rand.h // Implementation of random number and string
│ ├── sb_thread.h // Thread function implementation
│ ├── sb_timer.h // Timer implementation
│ ├── sb_util.h // Function functions, mainly align related macros
│ ├── sb_win.h // Cross platform code, windows support
│ ├── sysbench.c // main function of sysbench
│ ├── sysbench.h
│ ├── tests // Other test model programs, including cpu, fileio, etc
│ │ ├── cpu
│ │ ├── fileio
│ │ ├── memory
│ │ ├── mutex
│ │ ├── sb_cpu.h
│ │ ├── sb_fileio.h
│ │ ├── sb_memory.h
│ │ ├── sb_mutex.h
│ │ ├── sb_threads.h
│ │ └── threads
│ └── xoroshiro128plus.h
├── tests // Test case of DB driver
│ ├── include
│ │ ├── api_sql_common.sh
│ │ ├── config.sh.in
│ │ ├── drv_common.sh
│ │ ├── inspect.lua
│ │ ├── mysql_common.sh
│ │ ├── oltp_legacy
│ │ ├── pgsql_common.sh
│ │ ├── script_bulk_insert_common.sh
│ │ ├── script_oltp_common.sh
│ │ ├── script_oltp_legacy_common.sh
│ │ ├── script_select_random_common.sh
│ │ └── script_select_random_legacy_common.sh
│ ├── t // sysbench unit test program
│ └── test_run.sh
└── third_party // Third party dependency libraries for sysbench
├── concurrency_kit // Concurrency control module
├── cram // Post command program testing framework
└── luajit // jit compiler for lua
After reading the code, the process logic is clear from the main function in sysbench.c:
// source file: src/sysbench.c
int main(int argc, char *argv[])
{
sb_test_t *test = NULL;
int rc;
sb_globals.argc = argc;
sb_globals.argv = malloc(argc * sizeof(char *));
memcpy(sb_globals.argv, argv, argc * sizeof(char *));
/* Initialize options library */
sb_options_init();
/* First register the logger */
if (log_register())
return EXIT_FAILURE;
/* Register available tests */
if (register_tests())
{
fprintf(stderr, "Failed to register tests.\n");
return EXIT_FAILURE;
}
/* Parse command line arguments */
if (parse_general_arguments(argc, argv))
return EXIT_FAILURE;
/* Initialize global variables and logger */
if (init() || log_init() || sb_counters_init())
return EXIT_FAILURE;
print_header();
test = sb_load_lua(NULL);
current_test = test;
/* Load and parse test-specific options */
if (parse_test_arguments(test, argc, argv))
return EXIT_FAILURE;
/* prepare, cleanup, run. */
if (!strcmp(sb_globals.cmdname, "prepare"))
{
rc = test->builtin_cmds.prepare();
}
else if (!strcmp(sb_globals.cmdname, "cleanup"))
{
rc = test->builtin_cmds.cleanup();
}
else if (!strcmp(sb_globals.cmdname, "run"))
{
rc = run_test(test) ? EXIT_FAILURE : EXIT_SUCCESS;
}
/* clean up. */
end:
if (sb_lua_loaded())
sb_lua_done();
db_done();
sb_counters_done();
log_done();
sb_options_done();
sb_rand_done();
sb_thread_done();
free(timers);
free(timers_copy);
free(sb_globals.argv);
return rc;
}
2. Lua script
Lua and C have very good affinity and are called the best glue language. Lua can call the methods defined in C, and C can also call Lua very conveniently. Why should C and Lua be used together
- C has the advantage of high performance, but it is a compilation language. If the program changes, it needs to be recompiled, which is relatively inflexible;
- Lua has the advantage of flexibility. It is an interpretation language that can be executed without compilation. It is generally used to write some regular code that changes frequently.
The question is: won't Lua slow down the performance of C? Then I have to mention LuaJIT. It is Lua's virtual machine. Its main function is to compile Lua programs into bytecode programs for execution. LuaJIT is relatively lightweight and can be easily integrated into C programs, which will not have a great impact on Lua's performance and supports dynamic loading of lua programs.
Here is a simple code of Lua:
-- file: lua_test.lua
function domain(num)
-- call C function.
local tab = gettab()
-- show key and value
for k, v in pairs(tab) do
print("key: ".. k)
print("val: ".. v)
print()
end
end
The following code interacts with C:
// file: c_test.c
int get_tab(lua_State *L) {
// create table.
lua_newtable(L);
// push (key, value).
int i;
char value[10] = {0};
for(i=0; i<5; ++i) {
sprintf(value, "value%d", i+1);
lua_pushnumber(L, i+1); //key
lua_pushstring(L, value); //value
lua_settable(L, -3); //push key,value
}
// deal return.
return 1;
}
int main()
{
// create a state and load standard library.
lua_State* L = luaL_newstate();
luaL_openlibs(L);
// register function be called by lua.
lua_register(L, "gettab", get_tab);
// load and exec the specified lua file.
int error = luaL_dofile(L, "lua_test.lua");
if(error) {
perror("luaL_dofile error");
exit(1);
}
// get the domain function from lua file.
lua_getglobal(L, "domain");
// exec the domain function.
error = lua_pcall(L, 0, 0, 0);
if (error) {
fprintf(stderr, "%s\n",lua_tostring(L, -1));
lua_pop(L, 1);
}
// close lua state.
lua_close(L);
return 0;
}
Through lua_State interaction has a great impact on C code. Another way is ffi. You can refer to this link:
https://moonbingbing.gitbooks.io/openresty-best-practices/content/lua/FFI.html
sysbench uses both of the above methods:
- Via lua_State interaction: mainly used by Lua to call DB driver API defined in C;
- ffi is mainly used for structures, field types, regular functions (sleep, random functions) defined in C.
3. Extended functions
The sysbench test db scenario is relatively simple, and the table structure is as follows:
CREATE TABLE `sbtest1` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `k` int(10) unsigned NOT NULL DEFAULT '0', `c` char(120) NOT NULL DEFAULT '', `pad` char(60) NOT NULL DEFAULT '', PRIMARY KEY (`id`), KEY `k_1` (`k`) BLOCK_SIZE 16384 GLOBAL );
When we test other scenarios, such as wide tables, large fields, small fields, etc., it is not enough to use the native sysbench test script.
At this time, it is necessary to modify the implementation of the code. The implementation is relatively simple, that is, to implement these functions in Lua script:
// src/sb_lua.c #define EVENT_ Func "event" / / the content executed by the worker thread #define PREPARE_ Func "prepare" / / prepare stage: create tables and preload data #define CLEANUP_ Func "cleanup" / / cleanup phase: data cleaning, etc #define HELP_ Func "help" / / help function, which shows the parameter usage of the test case #define THREAD_INIT_FUNC "thread_init" // worker thread initialization: set running parameters, generate labels, etc #define THREAD_ DONE_ Func "thread_done" / / after the worker thread finishes executing: clean up the resources in the thread #define THREAD_ RUN_ Func "thread_run" / / generally, there is no need to implement it, just focus on event #define INIT_ Func "init" / / global init: generally not required #define DONE_ Func "done" / / global done: generally not required
The test case of oltp provided by sysbench is implemented in this way. It mainly includes two files:
- oltp.lua: thread defined_ Init and event functions;
- common.lua: the prepare and cleanup functions are defined.
Let's write our own test case script:
-- file: test_cjf.lua
-- A simple test case.
function prepare()
db_connect()
-- create table.
local query = [[
CREATE TABLE `sbtest_cjf` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`k` int(10) unsigned NOT NULL DEFAULT '0',
`c` char(120) NOT NULL DEFAULT '',
`pad` char(60) NOT NULL DEFAULT '',
PRIMARY KEY (`id`)
)
]]
db_query(query)
print("create table sbtest_cjf succ.")
-- insert 1000 records.
local i
for i = 1, 1000 do
query = [[
insert into sbtest_cjf(`id`, `k`, `c`, `pad`) values(
]] .. i .. ',' .. i + 1000 .. ", 'c', 'pad')"
db_query(query)
end
print("insert 1000 record to sbtest_cjf succ.")
return 0
end
function cleanup()
db_query('DROP TABLE IF EXISTS sbtest_cjf')
print('drop table sbtest_cjf succ.')
return 0
end
function event()
db_query('select * from sbtest_cjf where id = 1')
end
Test the above with sysbench_ Cjf.lua to test:
$./src/sysbench ./tests/include/oltp_legacy/test_cjf.lua --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --mysql-password=xxx --mysql-db=dbtest --db-driver=mysql --report-interval=2 --threads=1 --time=10 prepare
sysbench 1.0.20-6cb07f3 (using bundled LuaJIT 2.1.0-beta2)
create table sbtest_cjf succ.
insert 1000 record to sbtest_cjf succ.
$./src/sysbench ./tests/include/oltp_legacy/test_cjf.lua --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --mysql-password=xxx --mysql-db=dbtest --db-driver=mysql --report-interval=2 --threads=1 --time=10 run
sysbench 1.0.20-6cb07f3 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options:
Number of threads: 1
Report intermediate results every 2 second(s)
Initializing random number generator from current time
Initializing worker threads...
Threads started!
[ 2s ] thds: 1 tps: 2182.39 qps: 2182.39 (r/w/o: 2182.39/0.00/0.00) lat (ms,95%): 0.55 err/s: 0.00 reconn/s: 0.00
[ 4s ] thds: 1 tps: 2323.41 qps: 2323.41 (r/w/o: 2323.41/0.00/0.00) lat (ms,95%): 0.47 err/s: 0.00 reconn/s: 0.00
[ 6s ] thds: 1 tps: 2299.49 qps: 2299.49 (r/w/o: 2299.49/0.00/0.00) lat (ms,95%): 0.47 err/s: 0.00 reconn/s: 0.00
[ 8s ] thds: 1 tps: 2304.50 qps: 2304.50 (r/w/o: 2304.50/0.00/0.00) lat (ms,95%): 0.48 err/s: 0.00 reconn/s: 0.00
SQL statistics:
queries performed:
read: 22804
write: 0
other: 0
total: 22804
transactions: 22804 (2279.41 per sec.)
queries: 22804 (2279.41 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 10.0024s
total number of events: 22804
Latency (ms):
min: 0.40
avg: 0.44
max: 14.68
95th percentile: 0.53
sum: 9974.14
Threads fairness:
events (avg/stddev): 22804.0000/0.00
execution time (avg/stddev): 9.9741/0.00
$./src/sysbench ./tests/include/oltp_legacy/test_cjf.lua --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --mysql-password=xxx --mysql-db=dbtest --db-driver=mysql --report-interval=2 --threads=1 --time=10 cleanup
sysbench 1.0.20-6cb07f3 (using bundled LuaJIT 2.1.0-beta2)
drop table sbtest_cjf succ.
4. Reference documents
Lua tutorial: https://www.w3xue.com/manual/lua/Lua-Chinese.pdf
Getting started with GDB: https://zhuanlan.zhihu.com/p/74897601
GDB command: https://developer.aliyun.com/article/232348