---
title: "Sysbench oltp | OceanBase 文档中心"
description: Sysbench oltp Sysbench 简介 Sysbench 是一个基于 LuaJIT 的可编写脚本的多线程基准测试工具，可以执行 CPU、内存、线程、IO、数据库等方面的性能测试，常用于评估测试各种不同系统参数下的数据库负载情况，不需要修改源码，通过自定义 Lua 脚本就可以实现不同业务类型的测试。 环境信…
---
切换语言

- 中文站 - 简体中文
- International - English
- 日本站 - 日本語

文档反馈![](https://mdn.alipayobjects.com/huamei_22khvb/afts/img/A*P8CuR4UJ_FkAAAAAAAAAAAAADiGDAQ/original) OceanBase 数据库分布式版 - V 3.1.2 企业版

# Sysbench oltp

更新时间：2023-07-17 13:54:34

## Sysbench 简介

Sysbench 是一个基于 LuaJIT 的可编写脚本的多线程基准测试工具，可以执行 CPU、内存、线程、IO、数据库等方面的性能测试，常用于评估测试各种不同系统参数下的数据库负载情况，不需要修改源码，通过自定义 Lua 脚本就可以实现不同业务类型的测试。

## 环境信息

### 操作系统信息

```unknow
$cat /etc/redhat-release
Alibaba Group Enterprise Linux Server release 7.2 (Paladin)

```

```unknow
$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 信息

```unknow
$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:

```

```unknow
$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

```

```unknow
$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

```

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

```

```unknow
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

```unknow
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

```unknow
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

```unknow
$./sysbench --version
sysbench 1.0.20

```

## 部署信息

```unknow
cluster 1:1:1
Region SZ:SZ:SZ
obproxy 1

```

## 测试内容

### 配置参数

#### OBServer 启动参数

```unknow
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 启动参数

```unknow
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;

```

#### 集群参数

```unknow
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 参数

```unknow
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';

```

#### 租户配置

```unknow
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 配置

```unknow
sudo sysctl kernel.sched_migration_cost_ns=0

```

## 安装 Sysbench

按照以下步骤安装 Sysbench。

1. 下载 Sysbench。

   详细信息参考 [Sysbench 下载地址](https://github.com/akopytov/sysbench/releases/tag/1.0.20)。
 2. 解压 Sysbench。

   ```bash
   unzip ./1.0.20.zip

   ```
 3. 编译 Sysbench。

   进入 Sysbench 解压后的目录，运行以下命令编译 Sysbench：

   ```bash
   [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 |
 4. 运行以下命令，验证 Sysbench 是否安装成功：

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

   ```

   如果返回以下信息，则 Sysbench 安装成功。

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

   ```

   ```

   参数说明：

   ```

   ```

## 测试过程

1. 初始化环境。

   ```unknow
   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

   ```
 2. 导入 30 张表，每张表包含 1000 w 数据。

   ```unknow
   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

   ```
 3. 预热 10 s。

   ```unknow
   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

   ```
 4. 执行每一个 case 执行 30 s，避免执行过程中发生转储。

   ```unknow
   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 |

 上一篇 下一篇 ![有帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*y6ocSqN8cqsAAAAAAAAAAAAAARQnAQ)![无帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*BG9IQJyLHF8AAAAAAAAAAAAAARQnAQ)![反馈](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*eTWdQKCRKHwAAAAAAAAAAAAAARQnAQ)[AI](https://www.oceanbase.com/obi) 咨询热线