sysbench Benchmark Setup
Contents
For our automated MariaDB/MySQL sysbench benchmark tests, we use sysbench from
lp:sysbench
. This page describes the basic parameters and configuration we
use.
You can find the automation wrapper scripts we use for running sysbench in lp:mariadb-tools
Current general parameters
table_open_cache = 512 thread_cache = 512 query_cache_size = 0 query_cache_type = 0
Current InnoDB parameters
innodb_data_home_dir = /data/mysql/ innodb_data_file_path = ibdata1:128M:autoextend innodb_log_group_home_dir = /data/mysql/ innodb_buffer_pool_size = 1024M innodb_additional_mem_pool_size = 32M innodb_log_file_size = 256M innodb_log_buffer_size = 16M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 50 innodb_doublewrite = 0 innodb_flush_method = O_DIRECT innodb_thread_concurrency = 0 innodb_max_dirty_pages_pct = 80
Compile
Install MariaDB or MySQL to /usr/local and make a symlink to /usr/local/mysql. Please use non-debug version! On Mac OS X we currently need automake1.10.
./autogen.sh ./configure \ --without-drizzle \ --with-mysql-includes=/usr/local/mysql/include/mysql \ --with-mysql-libs=/usr/local/mysql/lib/mysql make optionally: make install
Start and prepare database to use
mysqladmin -uroot drop sbtest mysqladmin -uroot create sbtest
Tests
We use the latest sysbench with Lua scripting support. Therefore the test names differ from sysbench <= 0.4. To get reasonable results we use a run time of 5 minutes.
We run the tests with 1, 4, 8, 16, 32, 64, and 128 threads.
NUM_THREADS="1 4 8 16 32 64 128" SYSBENCH_TESTS="delete.lua \ insert.lua \ oltp_complex_ro.lua \ oltp_complex_rw.lua \ oltp_simple.lua \ select.lua \ update_index.lua \ update_non_index.lua" NUM_THREADS=1 TEST_DIR=${HOME}/work/monty_program/sysbench/sysbench/tests/db ./sysbench \ --test=${TEST_DIR}/oltp_simple.lua \ --oltp-table-size=2000000 \ --max-time=300 \ --max-requests=0 \ --mysql-table-engine=InnoDB \ --mysql-user=root \ --mysql-engine-trx=yes \ --num-threads=$NUM_THREADS \ prepare ./sysbench \ --test=${TEST_DIR}/oltp_simple.lua \ --oltp-table-size=2000000 \ --max-time=300 \ --max-requests=0 \ --mysql-table-engine=InnoDB \ --mysql-user=root \ --mysql-engine-trx=yes \ --num-threads=$NUM_THREADS \ run
Custom added tests
We created a couple of custom tests for SysBench:
- select_random_ranges.lua
- select_random_points.lua
Both of these have been added to the latest SysBench v0.5 repository.