The Ultimate Guide to High Availability with MariaDB
This is a read-only copy of the MariaDB Knowledgebase generated on 2024-11-21. For the latest, interactive version please visit https://mariadb.com/kb/.

sysbench Benchmark Setup

Contents

  1. Current general parameters
  2. Current InnoDB parameters
  3. Compile
  4. Start and prepare database to use
    1. Tests
  5. Custom added tests

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.

Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.