作者简介：马顺华
从事运维管理工作多年，目前就职于六棱镜（杭州）科技有限公司，熟悉运维自动化、OceanBase 部署运维、MySQL 运维以及各种云平台技术和产品。并已获得 OceanBase 认证 OBCA、OBCP 证书。
本文介绍如何使用 Sysbench 测试对 OceanBase 数据库的 OLTP 性能进行测试。包括安装 Sysbench，在本文中使用2种方式，对 OceanBase 运行 Sysbench 测试； 通过 OBD test 命令一键进行 Sysbench 测试； 基于官方 Sysbench 工具进行手动进行测试，并对 OceanBase 做一些调优，再结合测试程序快速找到适合的最佳性能。Sysbench 压测 OceanBase，可以建几个结构相同的表，然后执行纯读、纯写、读写混合。其中读又分根据主键或者二级索引查询，等值查询、IN查询或范围查询几种。详细的可以查看官方介绍。
机器信息
机器角色划分
安装环境部署版本
一、安装配置 Sysbench
1、安装 MySQL
从 Oracle 官网上下载 MySQL 5.7及以上的安装包，别用5.6及以下版本安装，在执行 Config 时会报错。
安装 MySQL 步骤参考前面发表的文章
https://www.modb.pro/db/337262 Linux 使用 rpm 部署安装 mysql-5.7
[root@CAIP131 sysbench]# mysql --version mysql Ver 14.14 Distrib 5.7.16, for Linux (x86_64) using EditLine wrapper
2、下载、安装 Sysbench
1）通过 Github 网站下载 Sysbench
https://github.com/akopytov/sysbench
2）检查 Git 工具是否安装
[root@CAIP131 sysbench]# git --version
3）使用 Git 工具下载 Sysbench
[root@CAIP131 opt]# git clone https://github.com/akopytov/sysbench.git Cloning into 'sysbench'... remote: Enumerating objects: 10290, done. remote: Counting objects: 100% (130/130), done. remote: Compressing objects: 100% (67/67), done. remote: Total 10290 (delta 72), reused 90 (delta 51), pack-reused 10160 Receiving objects: 100% (10290/10290), 4.26 MiB | 2.18 MiB/s, done. Resolving deltas: 100% (7371/7371), done.
4）查看 Sysbench 是否安装，此处提示未安装
[root@CAIP131 opt]# sysbench --version -bash: sysbench: command not found
3、安装编译工具、安装依赖包
1）进入目录
[root@CAIP131 opt]# cd sysbench [root@CAIP131 sysbench]# ls autogen.sh config COPYING Dockerfile m4 missing README.md scripts src third_party ChangeLog configure.ac debian install-sh Makefile.am mkinstalldirs rpm
2）安装 Make libtool 依赖包
[root@CAIP131 sysbench]# yum install -y automake libtool
3）安装 MySQL 依赖包 MySQL-community-devel
rpm 包在官网下载对应版本就可以了，这是下载好 MySQL 的 rpm 包
[root@CAIP131 mysql]# rpm -ivh mysql-community-devel-5.7.16-1.el7.x86_64.rpm
4) 再次检查 MySQL
mysql --version
4、初始化 sysbench 目录./autogen.sh
[root@CAIP131 opt]# cd sysbench [root@CAIP131 sysbench]# ls autogen.sh config COPYING Dockerfile m4 missing README.md scripts src third_party ChangeLog configure.ac debian install-sh Makefile.am mkinstalldirs rpm snap tests [root@CAIP131 sysbench]# ./autogen.sh autoreconf: Entering directory `.' autoreconf: configure.ac: not using Gettext autoreconf: running: aclocal -I m4 autoreconf: configure.ac: tracing autoreconf: running: libtoolize --copy libtoolize: putting auxiliary files in AC_CONFIG_AUX_DIR, `config'. libtoolize: copying file `config/ltmain.sh' libtoolize: putting macros in AC_CONFIG_MACRO_DIR, `m4'. libtoolize: copying file `m4/libtool.m4' libtoolize: copying file `m4/ltoptions.m4' libtoolize: copying file `m4/ltsugar.m4' libtoolize: copying file `m4/ltversion.m4' libtoolize: copying file `m4/lt~obsolete.m4' autoreconf: running: /usr/bin/autoconf autoreconf: running: /usr/bin/autoheader autoreconf: running: automake --add-missing --copy --no-force configure.ac:59: installing 'config/ar-lib' configure.ac:45: installing 'config/compile' configure.ac:27: installing 'config/config.guess' configure.ac:27: installing 'config/config.sub' configure.ac:32: installing 'config/install-sh' configure.ac:32: installing 'config/missing' src/Makefile.am: installing 'config/depcomp' parallel-tests: installing 'config/test-driver' autoreconf: Leaving directory `.'
执行 Config 命令
[root@CAIP131 sysbench]# ./configure --prefix=/usr/sysbench/ --with-mysql-includes=/usr/include/mysql/ --with-mysql-libs=/usr/lib64/mysql/ --with-mysql
5、执行 make编译 Sysbench
[root@CAIP131 sysbench]# make
执行 make install
[root@CAIP131 sysbench]# make install
参数说明：
查看帮助，验证 Sysbench 是否安装成功：
[admin@CAIP131 sysbench]$ ./src/sysbench --help
能够查看帮助和 version 说明 Sysbench 安装成功了
[admin@CAIP131 sysbench]$ sysbench --version sysbench 1.0.20
二、服务器初始化设置
安装部署 OceanBase 此处省略，详细步骤参考前面发表的部署文章
https://www.modb.pro/db/328171 OceanBase 手动部署三节点 OBServer 文档
https://www.modb.pro/db/324460 使用 OBD 自动部署三节点 OceanBase 文档
https://www.modb.pro/db/322997 OceanBase 单节点手动部署 OceanBase 文档
https://www.modb.pro/db/250245 Docker 单节点自动化部署 OceanBase 集群
此次尝鲜体验 OceanBase 开源版3.1.3最新版数据库性能测试。
MySQL [oceanbase]> SELECT version(); +--------------------+ | version() | +--------------------+ | 3.1.3-OceanBase CE | +--------------------+ 1 row in set (0.012 sec)
1、查看集群
obd cluster list
2、创建资源及租户
OceanBase 集群默认有个内部租户（ SYS ），可以查看和管理集群的资源。查看集群可用资源请使用下面 SQL。
1）系统租户连接到 OBServer
[admin@CAIP131 ~]$ obclient -h127.1 -uroot@sys#obce-demo -p#### -P2883 -A -c oceanbase Welcome to the OceanBase. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.6.25 OceanBase 3.1.3 (r10000292022032916-3d79cacb37012cf61b7cb8faf00d9a6bb152bcd1) (Built Mar 29 2022 08:20:39) Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
2）查询系统资源总计资源
MySQL [oceanbase]> SELECT svr_ip,svr_port, cpu_total, mem_total/1024/1024/1024, disk_total/1024/1024/1024, zone FROM __all_virtual_server_stat; +--------------+----------+-----------+--------------------------+---------------------------+-------+ | svr_ip | svr_port | cpu_total | mem_total/1024/1024/1024 | disk_total/1024/1024/1024 | zone | +--------------+----------+-----------+--------------------------+---------------------------+-------+ | 127.1 | 2882 | 14 | 48.000000000000 | 50.000000000000 | zone1 | | 127.1 | 2882 | 14 | 48.000000000000 | 50.000000000000 | zone2 | | 127.1 | 2882 | 14 | 48.000000000000 | 50.000000000000 | zone3 | +--------------+----------+-----------+--------------------------+---------------------------+-------+ 3 rows in set (0.010 sec)
3）查询租户已分配资源：
MySQL [oceanbase]> SELECT sum(c.max_cpu), sum(c.max_memory)/1024/1024/1024 FROM __all_resource_pool as a, __all_unit_config AS c WHERE a.unit_config_id=c.unit_config_id; +----------------+----------------------------------+ | sum(c.max_cpu) | sum(c.max_memory)/1024/1024/1024 | +----------------+----------------------------------+ | 5 | 14.399999999440 | +----------------+----------------------------------+ 1 row in set (0.009 sec)
4）创建资源单元规格：
MySQL [oceanbase]> CREATE RESOURCE UNIT sysbench_unit max_cpu = 8, max_memory = '32G', min_memory = '32G', max_iops = 100000, min_iops = 100000, max_session_num = 30000, max_disk_size = '50G'; Query OK, 0 rows affected (0.009 sec)
5）创建资源池：
MySQL [oceanbase]> create resource pool sysbench_pool unit = 'sysbench_unit', unit_num = 1, zone_list=('zone1','zone2','zone3');
Query OK, 0 rows affected (0.019 sec)
6）创建租户：
MySQL [oceanbase]> create tenant sysbench_tenant resource_pool_list=('sysbench_pool'), charset=utf8mb4, replica_num=3, zone_list('zone1', 'zone2', 'zone3'), primary_zone=RANDOM, locality='F@zone1,F@zone2,F@zone3' set variables ob_compatibility_mode='mysql', ob_tcp_invited_nodes='%';
Query OK, 0 rows affected (1.551 sec)
7）查看租户：
MySQL [oceanbase]> select tenant_id,tenant_name,primary_zone from __all_tenant; +-----------+-----------------+-------------------+ | tenant_id | tenant_name | primary_zone | +-----------+-----------------+-------------------+ | 1 | sys | zone1;zone2,zone3 | | 1001 | sysbench_tenant | RANDOM | +-----------+-----------------+-------------------+ 2 rows in set (0.007 sec)
3、环境调优
1）系统租户连接到 OBServer
[admin@CAIP131 ~]$ obclient -h127.1 -uroot@sys#obce-demo -p#### -P2883 -A -c oceanbase Welcome to the OceanBase. Commands end with ; or \g. Your MySQL connection id is 262146 Server version: 5.6.25 OceanBase 3.1.3 (r10000292022032916-3d79cacb37012cf61b7cb8faf00d9a6bb152bcd1) (Built Mar 29 2022 08:20:39) Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
2）设置 OBServer
在执行 Sysbench 测试前，您需要对 OceanBase 数据库进行简单的设置，以发挥其最大性能。
MySQL [oceanbase]> alter system set weak_read_version_refresh_interval='5s'; Query OK, 0 rows affected (0.034 sec) MySQL [oceanbase]> alter system set enable_auto_leader_switch=false; Query OK, 0 rows affected (0.026 sec) MySQL [oceanbase]> alter system set enable_one_phase_commit=false; Query OK, 0 rows affected (0.029 sec) MySQL [oceanbase]> alter system set weak_read_version_refresh_interval='5s'; Query OK, 0 rows affected (0.026 sec) MySQL [oceanbase]> alter system set system_memory ='30G'; Query OK, 0 rows affected (0.031 sec) MySQL [oceanbase]> alter system set enable_monotonic_weak_read = true; Query OK, 0 rows affected (0.023 sec)
3）调整日志级别及保存个数
MySQL [oceanbase]> alter system set syslog_level='PERF'; time_warn_threshold='2000ms'; alter system set syslog_io_bandwidth_limit='10M'; alter system set enable_sql_audit=false; alter system set enable_perf_event=false; alter system set clog_max_unconfirmed_log_count=5000; alter system set memory_chunk_cache_size ='0'; alter system set autoinc_cache_refresh_interval='86400s'; alter system set cpu_quota_concurrency=2; alter system set enable_early_lock_release=false tenant=all; alter system set default_compress_func = 'lz4_1.0';Query OK, 0 rows affected (0.054 sec) MySQL [oceanbase]> alter system set max_syslog_file_count=100; Query OK, 0 rows affected (0.041 sec) MySQL [oceanbase]> alter system set enable_syslog_recycle='True'; Query OK, 0 rows affected (0.035 sec) MySQL [oceanbase]> alter system set trace_log_slow_query_watermark='10s'; Query OK, 0 rows affected (0.038 sec) MySQL [oceanbase]> alter system set large_query_threshold='1s'; Query OK, 0 rows affected (0.034 sec) MySQL [oceanbase]> alter system set clog_sync_time_warn_threshold='2000ms'; Query OK, 0 rows affected (0.028 sec) MySQL [oceanbase]> alter system set syslog_io_bandwidth_limit='10M'; Query OK, 0 rows affected (0.049 sec) MySQL [oceanbase]> alter system set enable_sql_audit=false; Query OK, 0 rows affected (0.047 sec) MySQL [oceanbase]> alter system set enable_perf_event=false; Query OK, 0 rows affected (0.064 sec) MySQL [oceanbase]> alter system set clog_max_unconfirmed_log_count=5000; Query OK, 0 rows affected (0.036 sec) MySQL [oceanbase]> alter system set memory_chunk_cache_size ='0'; Query OK, 0 rows affected (0.038 sec) MySQL [oceanbase]> alter system set autoinc_cache_refresh_interval='86400s'; Query OK, 0 rows affected (0.031 sec) ##cpu_quota_concurrency*租户cpu=工作线程数，具体的数值需要根据业务模型和机器配置调整 MySQL [oceanbase]> alter system set cpu_quota_concurrency=2; Query OK, 0 rows affected (0.039 sec) MySQL [oceanbase]> alter system set enable_early_lock_release=false tenant=all; Query OK, 0 rows affected (0.031 sec) MySQL [oceanbase]> alter system set default_compress_func = 'lz4_1.0'; Query OK, 0 rows affected (0.054 sec)
4）ODP调优（sys 租户登录设置）
MySQL [oceanbase]> alter proxyconfig set proxy_mem_limited='16G'; Query OK, 0 rows affected (0.002 sec) MySQL [oceanbase]> alter proxyconfig set enable_prometheus=false; Query OK, 0 rows affected (0.002 sec) MySQL [oceanbase]> alter proxyconfig set enable_metadb_used=false; Query OK, 0 rows affected (0.001 sec) MySQL [oceanbase]> alter proxyconfig set enable_standby=false; Query OK, 0 rows affected (0.001 sec) MySQL [oceanbase]> alter proxyconfig set enable_strict_stat_time=false; Query OK, 0 rows affected (0.001 sec) MySQL [oceanbase]> alter proxyconfig set use_local_dbconfig=true; Query OK, 0 rows affected (0.001 sec)
5）设置租户（进行测试的用户登录设置）使用测试租户 sysbench_tenant 登录
[admin@CAIP131 ~]$ obclient -h127.1 -uroot@sysbench_tenant#obce-demo -P2883 -A -c Welcome to the OceanBase. Commands end with ; or \g. Your MySQL connection id is 262147 Server version: 5.6.25 OceanBase 3.1.3 (r10000292022032916-3d79cacb37012cf61b7cb8faf00d9a6bb152bcd1) (Built Mar 29 2022 08:20:39) Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
6）对租户设置密码
MySQL [(none)]> alter user root identified by '####'; Query OK, 0 rows affected (0.043 sec)
4、设置数据库租户变量，防止事务超时
##把日志聚合，减小网络开销，提高并发读 MySQL [(none)]> alter system set _clog_aggregation_buffer_amount=4; #需要在建表前配置 Query OK, 0 rows affected (0.011 sec) MySQL [(none)]> alter system set _flush_clog_aggregation_buffer_timeout='1ms'; Query OK, 0 rows affected (0.012 sec) ##数据库下租户设置，防止事务超时 MySQL [(none)]> set global ob_timestamp_service='GTS'; Query OK, 0 rows affected (0.004 sec) MySQL [(none)]> set global autocommit=ON; Query OK, 0 rows affected (0.003 sec) MySQL [(none)]> set global ob_query_timeout=36000000000; vers=80; set global parallel_servers_target=192;Query OK, 0 rows affected (0.030 sec) MySQL [(none)]> set global ob_trx_timeout=36000000000; Query OK, 0 rows affected (0.031 sec) MySQL [(none)]> set global max_allowed_packet=67108864; Query OK, 0 rows affected (0.023 sec) MySQL [(none)]> set global ob_sql_work_area_percentage=100; Query OK, 0 rows affected (0.025 sec) MySQL [(none)]> set global parallel_max_servers=80; Query OK, 0 rows affected (0.026 sec) MySQL [(none)]> set global parallel_servers_target=192; Query OK, 0 rows affected (0.028 sec)
#parallel_max_servers 推荐设置为测试租户分配的 resource unit cpu 数的 10 倍如测试租户使用的 unit 配置为：create resource unit $unit_name max_cpu 26那么该值设置为 260 #parallel_server_target 推荐设置为 parallel_max_servers * 机器数0.8那么该值为 2603*0.8=624 （此处根据实际配置修改值）
5、重启集群，使配置生效
[admin@CAIP131 ~]$ obd cluster restart obce-demo Get local repositories and plugins ok Open ssh connection ok Stop observer ok Stop obproxy ok obce-demo stopped Get local repositories and plugins ok Open ssh connection ok Load cluster param plugin ok Check before start observer ok Check before start obproxy ok Start observer ok observer program health check ok Connect to observer ok Wait for observer init ok +------------------------------------------------+ | observer | +--------------+---------+------+-------+--------+ | ip | version | port | zone | status | +--------------+---------+------+-------+--------+ | 127.1 | 3.1.3 | 2881 | zone1 | active | | 127.1 | 3.1.3 | 2881 | zone2 | active | | 127.1 | 3.1.3 | 2881 | zone3 | active | +--------------+---------+------+-------+--------+ Start obproxy ok obproxy program health check ok Connect to obproxy ok +------------------------------------------------+ | obproxy | +--------------+------+-----------------+--------+ | ip | port | prometheus_port | status | +--------------+------+-----------------+--------+ | 127.1 | 2883 | 2884 | active | | 127.1 | 2883 | 2884 | active | | 127.1 | 2883 | 2884 | active | +--------------+------+-----------------+--------+ obce-demo running
三、手动进行 Sysbench 测试
Sysbench 工具可以建几个结构相同的表，然后执行纯读、纯写、读写混合。其中读又分根据主键或者二级索引查询，等值查询、IN 查询或范围查询几种。详细的可以查看官方介绍。用 Sysbench 压测 OceanBase ，创建很多表是一种方法。另外一种方法就是创建分区表。OceanBase 是分布式数据库，数据迁移和高可用的最小粒度是分区，分区是数据表的子集。分区表有多个分区，非分区表只有一个分区。分区表的拆分细节是业务可以定义的。OceanBase 可以将多个分区分布到不同节点，也可能不会分布到多个节点。这个取决于 OceanBase 集群和租户的规划设计。所以对 Sysbench 创建的表(分区)，在性能分析时要分析分区具体的位置。
按照以下步骤进行 Sysbench 测试：
1、准备测试的规格
--mysql-db=test --table_size=1000000 --tables=30 --threads=32/64/128/256/512/1024 --report-interval=10 --time=60 --db-ps-mode=disable
Sysbench 主要参数说明
- --threads=30:表示发起30个并发连接
- --report-interval=10:表示每10秒输出一次测试进度报告
- --oltp-tables-count=3:表示会生成3个测试表
- --oltp-table-size=1000000:表示每个测试表填充数据量为1000000
prepare 是为测试提前准备数据，run 是执行正式的测试，cleanup 是在测试完成后对数据库进行清理。
testname 指定了要进行的测试，在老版本的 sysbench 中，可以通过 –test 参数指定测试的脚本；而在新版本中，–test 参数已经声明为废弃，可以不使用 –test，而是直接指定脚本。
sysbench 命令参数 sysbench 的机制是压测过程中如果有错误就会报错退出，所以需要针对一些常见的错误进行忽略处理，这样 sysbench 会话可以重试继续运行。比如说主键或者唯一键冲突、事务被杀等等。 下面的运行命令供参考，根据实际配置填写。
./sysbench_mysql --test=./tests/include/oltp_legacy/oltp.lua \ --mysql-host=服务域名或地址 --mysql-port=服务端口 --mysql-user=数据库用户 --mysql-password=数据库密码 --mysql-db=测试DB \ --oltp-test-mode=complex --oltp-tables-count=3 --oltp-table-size=50000000 \ --threads=12 --time=120 --report-interval=20 prepare
2、清理数据库：cleanup（oltp_read_write.lua）
执行完测试前后，清理数据，否则后面的测试会受到影响
[admin@CAIP131 sysbench]$ /opt/sysbench/src/sysbench /opt/sysbench/src/lua/oltp_read_write.lua --mysql-host=127.1 --mysql-port=2883 --mysql-db=test --mysql-user=root@sysbench_tenant#obce-demo --mysql-password=#### --table_size=1000000 --tables=30 --threads=150 --report-interval=10 --time=60 cleanup
3、初始化数据预热 prepare（oltp_read_write.lua）
[admin@CAIP131 sysbench]$ /opt/sysbench/src/sysbench /opt/sysbench/src/lua/oltp_read_write.lua --mysql-host=127.1 --mysql-port=2883 --mysql-db=test --mysql-user=root@sysbench_tenant#obce-demo --mysql-password=### --tables=30 --table_size=100000000 --threads=32 --time=60 --report-interval=10 --db-driver=mysql --db-ps-mode=disable --skip-trx=on --mysql-ignore-errors=6002,6004,4012,2013,4016 prepare
4、读写混合测试场景 run（oltp_read_write.lua）
[admin@CAIP131 sysbench]$ /opt/sysbench/src/sysbench /opt/sysbench/src/lua/oltp_read_write.lua --mysql-host=127.1 --mysql-port=2883 --mysql-db=test --mysql-user=root@sysbench_tenant#obce-demo --mysql-password=#### --tables=30 --table_size=100000000 --threads=32 --time=60 --report-interval=10 --db-driver=mysql --db-ps-mode=disable --skip-trx=on --mysql-ignore-errors=6002,6004,4012,2013,4016 --secondary=on run
sysbench 1.1.0-df89d34 (using bundled LuaJIT 2.1.0-beta3)
Running the test with following options:
Number of threads: 32
Report intermediate results every 10 second(s)
Initializing random number generator from current time
Initializing worker threads...
Threads started!
[ 10s ] thds: 32 tps: 368.71 qps: 6673.51 (r/w/o: 5195.06/435.30/1043.15) lat (ms,95%): 204.11 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 32 tps: 381.51 qps: 6858.13 (r/w/o: 5334.28/450.22/1073.64) lat (ms,95%): 196.89 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 32 tps: 388.82 qps: 6998.02 (r/w/o: 5442.05/460.32/1095.65) lat (ms,95%): 186.54 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 32 tps: 414.89 qps: 7468.70 (r/w/o: 5810.32/487.89/1170.48) lat (ms,95%): 183.21 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 32 tps: 1228.12 qps: 22106.90 (r/w/o: 17193.51/1450.93/3462.46) lat (ms,95%): 51.02 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 32 tps: 1000.50 qps: 18018.92 (r/w/o: 14015.12/1173.30/2830.50) lat (ms,95%): 104.84 err/s: 0.00 reconn/s: 0.00
SQL statistics:
queries performed:
read: 530012
write: 44621
other: 106811
total: 681444
transactions: 37858 (630.35 per sec.)
queries: 681444 (11346.22 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
Throughput:
events/s (eps): 630.3455
time elapsed: 60.0591s
total number of events: 37858
Latency (ms):
min: 9.81
avg: 50.73
max: 387.8195th percentile: 155.80
sum: 1920601.22
Threads fairness:
events (avg/stddev): 1183.0625/69.00
execution time (avg/stddev): 60.0188/0.02
纯读场景刚开始，多跑几次性能会逐步变好。TiKV 内部使用 Rocksdb 引擎，数据 IO 都是 Buffer io，主机的 pagecache 达到43G左右不再增长，8-16个并发的时候，推测数据在 pagecache 命中率很高，所以 TiKV 节点的 IO 压力比 OBServer 的 IO 压力小（ OceanBase 都是 Direct io），rt 更好，QPS 更高。而 OceanBase 主机随着并发增加，运行时间变成， block cache 的命中率从80%提升到97%后，OceanBase 的 rt 逐渐下降，OBServer节点的 IO 压力从早期的100%回落到80%左右。
5、 纯写测试场景 run（oltp_write_only.lua）
[admin@CAIP131 sysbench]$ /opt/sysbench/src/sysbench /opt/sysbench/src/lua/oltp_write_only.lua --mysql-host=127.1 --mysql-port=2883 --mysql-db=test --mysql-user=root@sysbench_tenant#obce-demo --mysql-password=#### --tables=30 --table_size=100000000 --threads=32 --time=60 --report-interval=10 --db-driver=mysql --db-ps-mode=disable --skip-trx=on --mysql-ignore-errors=6002,6004,4012,2013,4016 --secondary=on run
sysbench 1.1.0-df89d34 (using bundled LuaJIT 2.1.0-beta3)
Running the test with following options:
Number of threads: 32
Report intermediate results every 10 second(s)
Initializing random number generator from current time
Initializing worker threads...
Threads started!
[ 10s ] thds: 32 tps: 2080.45 qps: 8325.49 (r/w/o: 0.00/2442.19/5883.31) lat (ms,95%): 57.87 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 32 tps: 1757.63 qps: 7033.53 (r/w/o: 0.00/2066.44/4967.09) lat (ms,95%): 56.84 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 32 tps: 1485.01 qps: 5939.34 (r/w/o: 0.00/1752.91/4186.43) lat (ms,95%): 63.32 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 32 tps: 1571.26 qps: 6285.34 (r/w/o: 0.00/1853.72/4431.62) lat (ms,95%): 61.08 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 32 tps: 1347.47 qps: 5389.98 (r/w/o: 0.00/1593.27/3796.72) lat (ms,95%): 66.84 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 32 tps: 3185.92 qps: 12743.07 (r/w/o: 0.00/3760.19/8982.88) lat (ms,95%): 24.38 err/s: 0.00 reconn/s: 0.00
SQL statistics:
queries performed:
read: 0
write: 134723
other: 322513
total: 457236
transactions: 114309 (1903.43 per sec.)
queries: 457236 (7613.72 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
Throughput:
events/s (eps): 1903.4307
time elapsed: 60.0542s
total number of events: 114309
Latency (ms):
min: 2.84
avg: 16.80
max: 190.8595th percentile: 54.83
sum: 1919913.01
Threads fairness:
events (avg/stddev): 3572.1562/208.39
execution time (avg/stddev): 59.9973/0.01
OceanBase 的写特点是写增量在内存里不落盘，待内存使用达到某个阀值后触发冻结、转储和大合并事件。因此 OceanBase 初始化过程中发生多次冻结事件有部分数据写入失败回滚。
6、纯读测试场景 run（oltp_read_only.lua）
[admin@CAIP131 sysbench]$ /opt/sysbench/src/sysbench /opt/sysbench/src/lua/oltp_read_only.lua --mysql-host=127.1 --mysql-port=2883 --mysql-db=test --mysql-user=root@sysbench_tenant#obce-demo --mysql-password=### --tables=30 --table_size=100000000 --threads=32 --time=60 --report-interval=10 --db-driver=mysql --db-ps-mode=disable --skip-trx=on --mysql-ignore-errors=6002,6004,4012,2013,4016 --secondary=on run
sysbench 1.1.0-df89d34 (using bundled LuaJIT 2.1.0-beta3)
Running the test with following options:
Number of threads: 32
Report intermediate results every 10 second(s)
Initializing random number generator from current time
Initializing worker threads...
Threads started!
[ 10s ] thds: 32 tps: 910.33 qps: 12765.95 (r/w/o: 12765.95/0.00/0.00) lat (ms,95%): 97.55 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 32 tps: 2249.10 qps: 31486.96 (r/w/o: 31486.96/0.00/0.00) lat (ms,95%): 27.17 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 32 tps: 2408.42 qps: 33716.95 (r/w/o: 33716.95/0.00/0.00) lat (ms,95%): 24.38 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 32 tps: 1548.94 qps: 21687.26 (r/w/o: 21687.26/0.00/0.00) lat (ms,95%): 42.61 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 32 tps: 1369.69 qps: 19177.30 (r/w/o: 19177.30/0.00/0.00) lat (ms,95%): 49.21 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 32 tps: 1190.09 qps: 16661.51 (r/w/o: 16661.51/0.00/0.00) lat (ms,95%): 65.65 err/s: 0.00 reconn/s: 0.00
SQL statistics:
queries performed:
read: 1355200
write: 0
other: 0
total: 1355200
transactions: 96800 (1611.02 per sec.)
queries: 1355200 (22554.26 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
Throughput:
events/s (eps): 1611.0184
time elapsed: 60.0862s
total number of events: 96800
Latency (ms):
min: 5.45
avg: 19.84
max: 296.8895th percentile: 48.34
sum: 1920479.29
Threads fairness:
events (avg/stddev): 3025.0000/161.17
execution time (avg/stddev): 60.0150/0.02
7、删除测试 run（oltp_delete.lua）
[admin@CAIP131 sysbench]$ /opt/sysbench/src/sysbench /opt/sysbench/src/lua/oltp_delete.lua --mysql-host=127.1 --mysql-port=2883 --mysql-db=test --mysql-user=root@sysbench_tenant#obce-demo --mysql-password=#### --tables=30 --table_size=100000000 --threads=32 --time=60 --report-interval=10 --db-driver=mysql --db-ps-mode=disable --skip-trx=on --mysql-ignore-errors=6002,6004,4012,2013,4016 --secondary=on run
sysbench 1.1.0-df89d34 (using bundled LuaJIT 2.1.0-beta3)
Running the test with following options:
Number of threads: 32
Report intermediate results every 10 second(s)
Initializing random number generator from current time
Initializing worker threads...
Threads started!
[ 10s ] thds: 32 tps: 19124.46 qps: 19124.46 (r/w/o: 0.00/1136.60/17987.87) lat (ms,95%): 6.43 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 32 tps: 24014.26 qps: 24014.26 (r/w/o: 0.00/1437.41/22576.85) lat (ms,95%): 4.18 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 32 tps: 19374.31 qps: 19374.31 (r/w/o: 0.00/1152.87/18221.44) lat (ms,95%): 5.18 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 32 tps: 16701.13 qps: 16701.13 (r/w/o: 0.00/982.90/15718.22) lat (ms,95%): 5.88 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 32 tps: 14504.50 qps: 14504.50 (r/w/o: 0.00/858.61/13645.89) lat (ms,95%): 8.13 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 32 tps: 7633.56 qps: 7633.56 (r/w/o: 0.00/459.21/7174.35) lat (ms,95%): 19.29 err/s: 0.00 reconn/s: 0.00
SQL statistics:
queries performed:
read: 0
write: 60289
other: 953364
total: 1013653
transactions: 1013653 (16888.38 per sec.)
queries: 1013653 (16888.38 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
Throughput:
events/s (eps): 16888.3760
time elapsed: 60.0208s
total number of events: 1013653
Latency (ms):
min: 0.30
avg: 1.89
max: 86.1995th percentile: 6.79
sum: 1919300.61
Threads fairness:
events (avg/stddev): 31676.6562/1504.35
execution time (avg/stddev): 59.9781/0.00
8、清理数据库：cleanup（oltp_read_write.lua）
执行完测试前后，清理数据，否则后面的测试会受到影响
[admin@CAIP131 sysbench]$ /opt/sysbench/src/sysbench /opt/sysbench/src/lua/oltp_read_write.lua --mysql-host=127.1 --mysql-port=2883 --mysql-db=test --mysql-user=root@sysbench_tenant#obce-demo --mysql-password=### --table_size=1000000 --tables=30 --threads=150 --report-interval=10 --time=60 cleanup
四、OBD 一键自动测试
1、添加一脚本 ob_sysbench.sh
[root@CAIP131 sysbench]# vim ob_sysbench.sh
脚本ob_sysbench.sh
#!/bin/bash echo "run oltp_read_only test" obd test sysbench obce-demo --component=obproxy --tenant=test --script-name=oltp_read_only.lua --table-size=1000000 --threads=32 obd test sysbench obce-demo --component=obproxy --tenant=test --script-name=oltp_read_only.lua --table-size=1000000 --threads=64 obd test sysbench obce-demo --component=obproxy --tenant=test --script-name=oltp_read_only.lua --table-size=1000000 --threads=128 obd test sysbench obce-demo --component=obproxy --tenant=test --script-name=oltp_read_only.lua --table-size=1000000 --threads=256 obd test sysbench obce-demo --component=obproxy --tenant=test --script-name=oltp_read_only.lua --table-size=1000000 --threads=512 obd test sysbench obce-demo --component=obproxy --tenant=test --script-name=oltp_read_only.lua --table-size=1000000 --threads=1024 echo "run oltp_write_only test" obd test sysbench obce-demo --component=obproxy --tenant=test --script-name=oltp_write_only.lua --table-size=1000000 --threads=32 obd test sysbench obce-demo --component=obproxy --tenant=test --script-name=oltp_write_only.lua --table-size=1000000 --threads=64 obd test sysbench obce-demo --component=obproxy --tenant=test --script-name=oltp_write_only.lua --table-size=1000000 --threads=128 obd test sysbench obce-demo --component=obproxy --tenant=test --script-name=oltp_write_only.lua --table-size=1000000 --threads=256 obd test sysbench obce-demo --component=obproxy --tenant=test --script-name=oltp_write_only.lua --table-size=1000000 --threads=512 obd test sysbench obce-demo --component=obproxy --tenant=test --script-name=oltp_write_only.lua --table-size=1000000 --threads=1024 echo "run oltp_read_write test" obd test sysbench obce-demo --component=obproxy --tenant=test --script-name=oltp_read_write.lua --table-size=1000000 --threads=32 obd test sysbench obce-demo --component=obproxy --tenant=test --script-name=oltp_read_write.lua --table-size=1000000 --threads=64 obd test sysbench obce-demo --component=obproxy --tenant=test --script-name=oltp_read_write.lua --table-size=1000000 --threads=128 obd test sysbench obce-demo --component=obproxy --tenant=test --script-name=oltp_read_write.lua --table-size=1000000 --threads=256 obd test sysbench obce-demo --component=obproxy --tenant=test --script-name=oltp_read_write.lua --table-size=1000000 --threads=512 obd test sysbench obce-demo --component=obproxy --tenant=test --script-name=oltp_read_write.lua --table-size=1000000 --threads=1024
2、创建测试 test 租户
MySQL [oceanbase]> create tenant test resource_pool_list=('sysbench_pool'), charset=utf8mb4, replica_num=3, zone_list('zone1', 'zone2', 'zone3'), primary_zone=RANDOM, locality='F@zone1,F@zone2,F@zone3' set variables ob_compatibility_mode='mysql', ob_tcp_invited_nodes='%';
3、安装依赖包
#sudo yum install -y yum-utils #sudo yum-config-manager --add-repo https://mirrors.aliyun.com/oceanbase/OceanBase.repo #sudo yum install ob-sysbench
4、OBD 一键测试
[admin @CAIP131 sysbench]# ./ob_sysbench.sh
注意
- 使用OBD进行一键测试时, 集群的部署必须是由 OBD 进行安装和部署，不能是手动部署，否则无法获取集群的信息, 导致无法根据集群的配置进行性能调优.
- obd test Sysbench 会自动完成所有操作, 无需其他额外任何操作, 包含测试数据的生成, OceanBase 参数优化, 加载和测试, 当中间环节出错时, 可以参考 obd test 文档 进行重试, 例如 可以跳过数据的生成, 直接进行加载和测试.
- 在查询验证数据的时候，可能会碰到超时类错误。OceanBase 里超时的可能场景有多个：
单条 SQL 执行超时时间，由租户变量ob_query_timeout控制。单位是微秒，默认是10秒。 单个事务的空闲超时时间，由租户变量ob_trx_idle_timeout控制。单位是微秒，默认120秒。 事务超时时间，由租户变量ob_trx_timeout控制。单位是微秒，默认100秒。 事务执行时间阈值，proxy 参数 slow_transaction_time_threshold 默认5秒。 以上超时时间都可以在租户里根据实际情况修改。
五、测试结果对比
测试结束后，查看各个测试输出文件：
其中，对于我们比较重要的信息包括：
queries：查询总数及 qps
transactions：事务总数及 tps
Latency (ms)：-95th percentile
1、读写混合测试场景对比
transactions: 37858 (630.35 per sec.)
queries: 681444 (11346.22 per sec.)
Latency (ms):
95th percentile: 155.80
tps：37858
qps: 681444
Latency (ms)：前95%的请求的最大响应时间，本例中读写混合测试场景是155.80毫秒，这个延迟有些大，是因为我用的服务器性能未调大建议内存在64G以上；在实际生产环境中这个数值是不能接受。
2、纯写测试场景对比
transactions: 114309 (1903.43 per sec.)
queries: 457236 (7613.72 per sec.)
Latency (ms):
95th percentile: 54.83
tps：114309
qps: 457236
Latency (ms)：前95%的请求的最大响应时间，本例中纯写测试场景是54.83毫秒，表现还是可以。
3、纯读测试场景对比
transactions: 96800 (1611.02 per sec.)
queries: 1355200 (22554.26 per sec.)
Latency (ms):
95th percentile: 48.34
tps：114309
qps: 457236
Latency (ms)：前95%的请求的最大响应时间，本例中纯读测试场景对比是48.34毫秒，表现还是可以。
4、删除测试场景对比
transactions: 1013653 (16888.38 per sec.)
queries: 1013653 (16888.38 per sec.)
Latency (ms):
95th percentile: 6.79
tps：1013653
qps: 1013653
Latency (ms)：前95%的请求的最大响应时间，本例删除测试场景对比是6.79毫秒，表现已经很优秀了。
可能遇到的问题？
1、必须提前部署好 MySQL；
2、安装 MySQL 依赖包 mysql-community-devel
否则会报错，在 make 的时候提示 fatal error: mysql.h不存在；
3、在 OBD 一键压力测试时，必须采用 OBD 部署的 OBServer 集群；
4、在 OBD 一键压力测试时，需要单独再安装 OBD 版的 Sysbench。
写在最后
本节是使用 Sysbench 对 OceanBase 压力测试场景的经验，大家在测试时需要提前了解 OceanBase 的高级功能原理特性介绍。在写入压力非常大情形时，跑了一段时间后报内存不足的提示，这个就是租户内存资源相对写入速度和量不足了(OceanBase 的转储和合并对内存的回收赶不上写入对内存的消耗)，此时需要扩容或者调整测试需求。还有在使用 Sysbench 测试，不同的做法可以得到不同的结果。如果是同一个 OceanBase 租户，但不同的人设计的表结构，或者SQL不同，会得出不同的性能数据。了解这些原理的更容易发挥 OceanBase 的分布式数据库特点。还有如果环境、场景不同，还是会遇到一些问题。
本人对 OceanBase 了解尚浅，配置文件主要是参考网络搭建，可能存在某些参数不是最优。这方面对性能可能有些影响。有好的经验的同学，欢迎大家提出来，一起学习。
最后的最后，有任何问题都可以和我们联系。
联系我们
欢迎广大 OceanBase 爱好者、用户和客户随时与我们联系、反馈，方式如下：
社区版官网论坛
社区版项目网站提 Issue
