Sysbench OLTP

Overview

Sysbench is a LuaJIT-based multi-thread benchmark tool that allows you to write scripts and test the performance of CPU, memory, thread, I/O, and database. It is often used for evaluating and testing the database workload under various system parameters. You can run the Sysbench benchmark in diversified business scenarios by customizing Lua scripts without modifying the source code.

Test plan

• Deploy an OceanBase cluster in the 1:1:1 pattern, with three OBServer nodes evenly distributed across three zones. After successful deployment, create the tenant and users required for running the Sysbench test. The SYS tenant is a built-in system tenant used for managing the cluster. Do not use the sys tenant to run the benchmark. Set PRIMARY_ZONE of the tenant to RANDOM.

• Launch the Sysbench client, and run the point_select, read_write, and read_only tests.

• Set the --time parameter of each round of test to 60s. The number of threads can be 900, 1200, 1500, or 1800.

• The following table describes the specifications of the test environment.

  --mysql-db=test
  --mysql-host=$obproxy_ip (the IP address of ODP)
  --table_size=1000000
  --tables=30
  --threads=900/1200/1500/1800
  --report-interval=10
  --time=60
  --db-ps-mode=disable
  

Test environment

• Hardware configuration

  Project type	Memory	CPU
  OceanBase Database	3 × 262 GB	3 × 32 cores
  Sysbench	1 × 262 GB	1 × 32 cores
  ODP	1 × 262 GB	1 × 32 cores

• Tenant specifications

  CREATE RESOURCE UNIT box1 MAX_CPU 26, MAX_MEMORY 220554378624, MAX_IOPS 128, MAX_DISK_SIZE 536870912, MAX_SESSION_NUM 64, MIN_CPU=26, MIN_MEMORY=220554378624, MIN_IOPS=128;
  CREATE RESOURCE POOL fpool UNIT='box1', UNIT_NUM=1, ZONE_LIST=('zone1','zone2','zone3');
  CREATE TENANT tt1 RESOURCE_POOL_LIST=('fpool'), PRIMARY_ZONE='RANDOM', LOCALITY='F@zone1,F@zone2,F@zone3' SET ob_compatibility_mode='mysql',ob_tcp_invited_nodes='%';
  

  Note

  • You must dynamically adjust the parameters based on the hardware configurations of your database. For more information, see Modify a tenant.
  • To test the performance of a tenant, you must configure the maximum available resources of the tenant.

• Software version

  Project type	Software version and model
  OceanBase Database	V3.2.4
  ODP	V3.1.0
  Sysbench	1.0.20
  OS	CentOS Linux release 7.9.2009 (Core)
  CPU	Intel(R) Xeon(R) Platinum 8163 CPU @ 2.50GHz

Environment information

Operating system information

$cat /etc/redhat-release
CentOS Linux release 7.9.2009 (Core)

$uname -a
Linux testdriver 3.10.0-1160.24.1.el7.x86_64 #1 SMP Thu Apr 8 19:51:47 UTC 2021 x86_64 x86_64 x86_64 GNU/Linux

CPU information

$cat /proc/cpuinfo
processor : 31
vendor_id : GenuineIntel
cpu family : 6
model  : 85
model name : Intel(R) Xeon(R) Platinum 8163 CPU @ 2.50GHz
stepping : 4
microcode : 0x1
cpu MHz  : 2499.992
cache size : 33792 KB
physical id : 0
siblings : 32
core id  : 31
cpu cores : 32
apicid  : 47
initial apicid : 47
fpu  : yes
fpu_exception : yes
cpuid level : 22
wp  : yes
flags  : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush mmx fxsr sse sse2 ss ht syscall nx pdpe1gb rdtscp lm constant_tsc rep_good nopl nonstop_tsc pni pclmulqdq ssse3 fma cx16 pcid sse4_1 sse4_2 x2apic movbe popcnt tsc_deadline_timer aes xsave avx f16c rdrand hypervisor lahf_lm abm 3dnowprefetch invpcid_single ibrs ibpb stibp fsgsbase tsc_adjust bmi1 hle avx2 smep bmi2 erms invpcid rtm mpx avx512f avx512dq rdseed adx smap avx512cd avx512bw avx512vl xsaveopt xsavec xgetbv1 arat
bugs  : cpu_meltdown spectre_v1 spectre_v2 spec_store_bypass l1tf
bogomips : 4999.98
clflush size : 64
cache_alignment : 64
address sizes : 46 bits physical, 48 bits virtual
power management:

Memory information

$free -m
             total       used       free     shared    buffers     cached
Mem:        262144      39697     222446          0          0      19948
-/+ buffers/cache:      19749     242394
Swap:            0          0          0

Software information

OBServer

$./observer -V
observer (OceanBase Database V3.2.3)

REVISION: 20220125174436-b765b7557c36d62bcd42597142fe0ee34bd21f6e
BUILD_BRANCH: 3_2_x_release
BUILD_TIME: Jan 25 2022 17:56:14
BUILD_FLAGS: RelWithDebInfo
BUILD_INFO:

Copyright (c) 2011-2020 Alipay Inc.

ODP

$./obproxy -V
obproxy (OceanBase Database V3.1.0 4358.el6)
REVISION: 4358-local-9a6209a5e7db8e692882782ce67cf6359f03c3ab
BUILD_TIME: May  5 2021 23:00:26
BUILD_FLAGS: -g -O2 -D_OB_VERSION=1000 -D_NO_EXCEPTION -D__STDC_LIMIT_MACROS -D__STDC_CONSTANT_MACROS -DNDEBUG -D__USE_LARGEFILE64 -D_FILE_OFFSET_BITS=64 -D_LARGE_FILE -D_LARGEFILE_SOURCE -D_LARGEFILE64_SOURCE -Wall -Wextra -Wunused-parameter -Wformat -Wconversion -Wno-deprecated -Wno-invalid-offsetof -finline-functions -fno-strict-aliasing -mtune=core2 -Wno-psabi -Wno-sign-compare -DGCC_52 -DUSE_POSIX_FALLOCATE -DSUPPORT_SSE4_2 -DHAVE_SCHED_GETCPU -DHAVE_REALTIME_COARSE -DOB_HAVE_EVENTFD -DHAVE_FALLOCATE -DHAVE_MINIDUMP -Werror

Copyright (c) 2007-2020 Alipay Inc.

Sysbench

$./sysbench --version
sysbench 1.0.20

Test content

Parameters

OBServer startup parameters

ALTER SYSTEM SET net_thread_count=12;
ALTER SYSTEM SET use_large_pages="true";
ALTER SYSTEM SET system_cpu_quota=10;
ALTER SYSTEM SET syslog_io_bandwidth_limit='20M';
ALTER SYSTEM SET major_freeze_duty_time='disable';
ALTER SYSTEM SET memstore_limit_percentage=50;
ALTER SYSTEM SET merger_warm_up_duration_time='0s';
ALTER SYSTEM SET zone_merge_concurrency=0;
ALTER SYSTEM SET schema_history_expire_time='1d';
ALTER SYSTEM SET minor_freeze_times=3;
ALTER SYSTEM SET enable_syslog_wf=false;

After setting the startup parameters, you must restart the OBServer node.

ODP startup parameters

ALTER PROXYCONFIG SET enable_strict_kernel_release=false;
ALTER PROXYCONFIG SET work_thread_num=128;
ALTER PROXYCONFIG SET automatic_match_work_thread=false;
ALTER PROXYCONFIG SET enable_prometheus=false;
ALTER PROXYCONFIG SET enable_compression_protocol=false;
ALTER PROXYCONFIG SET proxy_mem_limited=4g;
ALTER PROXYCONFIG SET enable_qos=true;

After setting the startup parameters, you must restart the OBServer.

Cluster parameters

ALTER SYSTEM SET enable_sql_audit=false;
ALTER SYSTEM SET cpu_quota_concurrency=4;
ALTER SYSTEM SET server_data_copy_out_concurrency=1000;
ALTER SYSTEM SET server_data_copy_in_concurrency=1000;
ALTER SYSTEM SET memory_chunk_cache_size='16G';
ALTER SYSTEM SET minor_freeze_times=200;
ALTER SYSTEM SET clog_transport_compress_all=false;
ALTER SYSTEM SET trx_try_wait_lock_timeout='0ms';
ALTER SYSTEM SET large_query_threshold='1s';
ALTER SYSTEM SET trace_log_slow_query_watermark='500ms';
ALTER SYSTEM SET syslog_io_bandwidth_limit='30m';
ALTER SYSTEM SET enable_async_syslog=true;
ALTER SYSTEM SET merger_warm_up_duration_time='0';
ALTER SYSTEM SET merger_switch_leader_duration_time='0';
ALTER SYSTEM SET large_query_worker_percentage=10;
ALTER SYSTEM SET minor_compact_trigger= 2;
ALTER SYSTEM SET builtin_db_data_verify_cycle=0;
ALTER SYSTEM SET micro_block_merge_verify_level=0;
ALTER SYSTEM SET freeze_trigger_percentage=50;
ALTER SYSTEM SET sys_bkgd_io_low_percentage=70;
ALTER SYSTEM SET _mini_merge_concurrency=5;
ALTER SYSTEM SET weak_read_version_refresh_interval=0;
ALTER SYSTEM SET _ob_enable_prepared_statement=true;
ALTER SYSTEM SET _clog_aggregation_buffer_amount=4 tenant=all;
ALTER SYSTEM SET enable_early_lock_release=false tenant=all;
ALTER SYSTEM SET enable_perf_event=false;
ALTER SYSTEM SET enable_auto_leader_switch=false;
ALTER SYSTEM SET weak_read_version_refresh_interval='5s';

ODP parameters

ALTER PROXYCONFIG SET enable_ob_protocol_v2=false;
ALTER PROXYCONFIG SET proxy_mem_limited='10G';
ALTER PROXYCONFIG SET enable_qos=false;
ALTER PROXYCONFIG SET slow_proxy_process_time_threshold='500ms';

Tenant configuration

ALTER SYSTEM SET _clog_aggregation_buffer_amount=4;
ALTER SYSTEM SET _flush_clog_aggregation_buffer_timeout='1ms';
ALTER SYSTEM SET weak_read_version_refresh_interval='5s';
ALTER SYSTEM SET enable_monotonic_weak_read=false;
SET GLOBAL binlog_row_image='MINIMAL';

Kernel configuration

sudo sysctl kernel.sched_migration_cost_ns=0

Sysbench installation

Perform the following steps to install Sysbench.

1. Download Sysbench.

   Download Sysbench Release 1.0.20.

2. Unzip the Sysbench installation package.

   unzip ./1.0.20.zip
   

3. Compile Sysbench.

   Enter the directory of Sysbench and run the following command to compile Sysbench:

   [wieck@localhost ~] $ cd sysbench-1.0.20
   [wieck@localhost sysbench-1.0.20] $./autogen.sh
   [wieck@localhost sysbench-1.0.20] $./configure --prefix=/usr/sysbench/ --with-mysql-includes=/usr/include/mysql/ --with-mysql-libs=/usr/lib64/mysql/ --with-mysql
   [wieck@localhost sysbench-1.0.20] $make
   [wieck@localhost sysbench-1.0.20] $make install
   [wieck@localhost sysbench-1.0.20] $cp -r /usr/sysbench/share/sysbench/* /usr/sysbench/bin/
   

   Parameters:

   Parameter	Description
   --prefix	The installation directory of Sysbench.
   --with-mysql-includes	The includes directory of MySQL.
   --with-mysql-libs	The lib directory of MySQL.
   --with-mysql	Specifies to support MySQL.

4. Run the following command to check whether Sysbench is installed:

   [wieck@localhost sysbench-1.0.20] $./src/sysbench --help
   

   The following information indicates a successful installation of Sysbench.

   Usage:
     sysbench [options]... [testname] [command]
   Commands implemented by most tests: prepare run cleanup help
   

Test process

1. Initialize the environment.

   sysbench --mysql-host=127.0.0.1 --mysql-port=2828 --mysql-user=root@tt1 --mysql-db=test --report-interval=3 --time=30 --threads=1000 --db-ps-mode=disable --percentile=99 --auto_inc=on --tables=30 --mysql-ignore-errors=1062 --table_size=10000000  ./oltp_read_write.lua cleanup
   

2. Import 30 tables, with each table containing 10 million rows of data.

   sysbench --mysql-host=127.0.0.1 --mysql-port=2828 --mysql-user=root@tt1 --mysql-db=test --report-interval=3 --time=30 --threads=1000 --db-ps-mode=disable --percentile=99 --auto_inc=on --tables=30 --mysql-ignore-errors=1062 --table_size=10000000  --threads=20 ./oltp_read_write.lua prepare
   

3. Warm up for 10s.

   sysbench --mysql-host=127.0.0.1 --mysql-port=2828 --mysql-user=root@tt1 --mysql-db=test --report-interval=3 --time=30 --threads=1000 --db-ps-mode=disable --percentile=99 --auto_inc=on --tables=30 --mysql-ignore-errors=1062 --table_size=10000000  --threads=1000 --time=10 ./oltp_point_select.lua run
   

4. Run each case for 30s. Avoid minor compactions during execution.

   sysbench --mysql-host=127.0.0.1 --mysql-port=2828 --mysql-user=root@tt1 --mysql-db=test --report-interval=3 --time=30 --threads=1000 --db-ps-mode=disable --percentile=99 --auto_inc=on --tables=30 --mysql-ignore-errors=1062 --table_size=10000000  --threads=1000 ./oltp_point_select.lua run
   sysbench --mysql-host=127.0.0.1 --mysql-port=2828 --mysql-user=root@tt1 --mysql-db=test --report-interval=3 --time=30 --threads=1000 --db-ps-mode=disable --percentile=99 --auto_inc=on --tables=30 --mysql-ignore-errors=1062 --table_size=10000000  --threads=1000 ./oltp_read_only.lua run
   sysbench --mysql-host=127.0.0.1 --mysql-port=2828 --mysql-user=root@tt1 --mysql-db=test --report-interval=3 --time=30 --threads=1000 --db-ps-mode=disable --percentile=99 --auto_inc=on --tables=30 --mysql-ignore-errors=1062 --table_size=10000000  --threads=1000 --rand-type=uniform  ./oltp_read_write.lua run
   sysbench --mysql-host=127.0.0.1 --mysql-port=2828 --mysql-user=root@tt1 --mysql-db=test --report-interval=3 --time=30 --threads=1000 --db-ps-mode=disable --percentile=99 --auto_inc=on --tables=30 --mysql-ignore-errors=1062 --table_size=10000000  --threads=1000 ./oltp_insert.lua run
   sysbench --mysql-host=127.0.0.1 --mysql-port=2828 --mysql-user=root@tt1 --mysql-db=test --report-interval=3 --time=30 --threads=1000 --db-ps-mode=disable --percentile=99 --auto_inc=on --tables=30 --mysql-ignore-errors=1062 --table_size=10000000  --threads=1000 ./oltp_update_non_index.lua run
   sysbench --mysql-host=127.0.0.1 --mysql-port=2828 --mysql-user=root@tt1 --mysql-db=test --report-interval=3 --time=30 --threads=1000 --db-ps-mode=disable --percentile=99 --auto_inc=on --tables=30 --mysql-ignore-errors=1062 --table_size=10000000  --threads=1000 ./oltp_write_only.lua run
   

Test result

Distributed scenario (primary zone = 'RANDOM')