首批通过分布式安全可靠测评，为关键业务系统打造
Sysbench oltp
更新时间：2023-07-17 13:54:34
分享
Sysbench 简介
Sysbench 是一个基于 LuaJIT 的可编写脚本的多线程基准测试工具，可以执行 CPU、内存、线程、IO、数据库等方面的性能测试，常用于评估测试各种不同系统参数下的数据库负载情况，不需要修改源码，通过自定义 Lua 脚本就可以实现不同业务类型的测试。
环境信息
操作系统信息
$cat /etc/redhat-release
Alibaba Group Enterprise Linux Server release 7.2 (Paladin)
$uname -a
Linux obrd.32c.qavd-b0-n3 4.9.151-015.ali3000.alios7.x86_64 #1 SMP Tue Mar 12 19:10:26 CST 2019 x86_64 x86_64 x86_64 GNU/Linux
CPU 信息
$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:
$lsblk
NAME MAJ:MIN RM SIZE RO TYPE MOUNTPOINT
vdh 253:112 0 1.8T 0 disk
vdf 253:80 0 1.8T 0 disk
vdd 253:48 0 1.8T 0 disk
vdb 253:16 0 1.8T 0 disk
vdk 253:160 0 1.8T 0 disk
vdi 253:128 0 1.8T 0 disk
vdg 253:96 0 1.8T 0 disk
vde 253:64 0 1.8T 0 disk
vdc 253:32 0 1.8T 0 disk
vdl 253:176 0 300G 0 disk
vda 253:0 0 60G 0 disk
└─vda1 253:1 0 60G 0 part
vdj 253:144 0 1.8T 0 disk
$lspci | grep -i 'eth'
00:03.0 Ethernet controller: Red Hat, Inc Virtio network device
00:12.0 Ethernet controller: Red Hat, Inc Virtio network device
00:13.0 Ethernet controller: Red Hat, Inc Virtio network device
00:14.0 Ethernet controller: Red Hat, Inc Virtio network device
00:15.0 Ethernet controller: Red Hat, Inc Virtio network device
$free -m
total used free shared buffers cached
Mem: 262144 39697 222446 0 0 19948
-/+ buffers/cache: 19749 242394
Swap: 0 0 0
Handle 0x005E, DMI type 17, 40 bytes
Memory Device
Array Handle: 0x0052
Error Information Handle: Not Provided
Total Width: 72 bits
Data Width: 64 bits
Size: 32 GB
Form Factor: DIMM
Set: None
Locator: CPU1_DIMM_F1
Bank Locator: NODE 4
Type: DDR4
Type Detail: Synchronous
Speed: 2666 MHz
Manufacturer: Samsung
Serial Number: 31EA6A4E
Asset Tag: CPU1_DIMM_F1_AssetTag
Part Number: M393A4K40BB2-CTD
Rank: 2
Configured Clock Speed: 2666 MHz
Minimum Voltage: 1.2 V
Maximum Voltage: 1.2 V
Configured Voltage: 1.2 V
软件信息
OBServer
observer (OceanBase 3.1.2)
REVISION: 1-4cc3e3b28ec3568df6a19c9d6c625d50bf4034bb
BUILD_BRANCH: HEAD
BUILD_TIME: Oct 9 2021 12:44:11
BUILD_FLAGS: RelWithDebInfo
BUILD_INFO: jenkins-oceanbase_build_3_1_7u-B1980
Copyright (c) 2011-2020 Alipay Inc.
OBProxy
obproxy (OceanBase 3.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
部署信息
cluster 1:1:1
Region SZ:SZ:SZ
obproxy 1
测试内容
配置参数
OBServer 启动参数
alter system set net_thread_count=24;
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;
启动参数设置完成后，需要重启。
OBProxy 启动参数
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;
启动参数设置完成后，需要重启。
集群参数
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 ='0';
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;
OBProxy 参数
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';
租户配置
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 tenant tt3 resource_pool_list=('fpool'), zone_list('z1', 'z2', 'z3'),primary_zone='RANDOM', locality='F@zone1,F@zone2,F@zone3' set ob_compatibility_mode='mysql';
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';
Kernal 配置
sudo sysctl kernel.sched_migration_cost_ns=0
安装 Sysbench
按照以下步骤安装 Sysbench。
下载 Sysbench。
详细信息参考 Sysbench 下载地址。
解压 Sysbench。
unzip ./1.0.20.zip编译 Sysbench。
进入 Sysbench 解压后的目录，运行以下命令编译 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/参数说明：
参数名 说明 --prefix 指定 Sysbench 的安装目录。 --with-mysql-includes 指定 mysql 的 includes 目录。 --with-mysql-libs 指定 mysql 的 lib 目录。 --with-mysql Sysbench 默认支持 MySQL 运行以下命令，验证 Sysbench 是否安装成功：
[wieck@localhost sysbench-1.0.20] $./src/sysbench --help如果返回以下信息，则 Sysbench 安装成功。
Usage: sysbench [options]... [testname] [command] Commands implemented by most tests: prepare run cleanup help
安装 Sysbench
按照以下步骤安装 Sysbench。
下载 Sysbench。
详细信息参考 Sysbench 下载地址。
解压 Sysbench。
unzip ./1.0.20.zip编译 Sysbench。
进入 Sysbench 解压后的目录，运行以下命令编译 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/参数说明：
参数名 说明 --prefix 指定 Sysbench 的安装目录。 --with-mysql-includes 指定 mysql 的 includes 目录。 --with-mysql-libs 指定 mysql 的 lib 目录。 --with-mysql Sysbench 默认支持 MySQL 运行以下命令，验证 Sysbench 是否安装成功：
[wieck@localhost sysbench-1.0.20] $./src/sysbench --help如果返回以下信息，则 Sysbench 安装成功。
Usage: sysbench [options]... [testname] [command] Commands implemented by most tests: prepare run cleanup help
测试过程
初始化环境。
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 --warmup-time=5 ./oltp_read_write.lua cleanup导入 30 张表，每张表包含 1000 w 数据。
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 --warmup-time=5 --threads=20 ./oltp_read_write.lua prepare预热 10 s。
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 --warmup-time=5 --threads=1000 --time=10 ./oltp_point_select.lua run执行每一个 case 执行 30 s，避免执行过程中发生转储。
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 --warmup-time=5 --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 --warmup-time=5 --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 --warmup-time=5 --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 --warmup-time=5 --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 --warmup-time=5 --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 --warmup-time=5 --threads=1000 ./oltp_write_only.lua run
测试结果
分布式场景 （ primary zone = 'RANDOM' ）
| read write | write only | read only | point select | update_non | update | |
| qps | 171001.91 | 156263.12 | 275367.00 | 391592.10 | 173731.41 | 142439.98 |
| rt | 297.92 | 80.03 | 73.13 | 3.96 | 9.22 | 12.98 |
