--- title: "BenchMarkSQL | OceanBase 文档中心" description: BenchMarkSQL BenchMarkSQL 简介 BenchMarkSQL 是基于 TPC-C 标准的一种 JAVA 实现工具。TPC-C 是一个对 OLTP（联机交易处理）系统进行测试的规范，使用一个商品销售模型对 OLTP 系统进行测试。详细信息，参考 TPC-C 简介 。 测试方案 OceanBase … image: https://mdn.alipayobjects.com/huamei_22khvb/afts/img/A*OSPzQ6GUQF4AAAAAQHAAAAgAeiGDAQ/original --- 切换语言 - 中文站 - 简体中文 - International - English - 日本站 - 日本語 文档反馈![](https://mdn.alipayobjects.com/huamei_22khvb/afts/img/A*P8CuR4UJ_FkAAAAAAAAAAAAADiGDAQ/original) OceanBase 数据库分布式版 - V 3.2.3 企业版 LTS # BenchMarkSQL 更新时间：2023-07-19 13:03:07 ## BenchMarkSQL 简介 BenchMarkSQL 是基于 TPC-C 标准的一种 JAVA 实现工具。TPC-C 是一个对 OLTP（联机交易处理）系统进行测试的规范，使用一个商品销售模型对 OLTP 系统进行测试。详细信息，参考 [TPC-C 简介](https://github.com/domino-succ/tpcc-hbase/wiki/%E4%B8%AD%E6%96%87-TPC-C%E7%AE%80%E4%BB%8B)。 ## 测试方案 - OceanBase 集群部署规模为 1：1：1。部署成功后，新建执行 TPC-C 测试的租户及用户（`sys` 租户是管理集群的内置系统租户，请勿直接使用该租户进行测试），将租户的 `PRIMARY_ZONE` 设置为 `RANDOM`。 - 测试规格如下所示： ```shell warehouses=1000 loadWorkers=40 terminals=800 runMins=5 newOrderWeight=45 paymentWeight=43 orderStatusWeight=4 deliveryWeight=4 stockLevelWeight=4 ``` ## 测试环境 - 硬件配置 | 项目类型 | 内存 | CPU | | --- | --- | --- | | OceanBase 数据库 | 3 台 * 128 G | 3 台 * 32 核 | | BenchMarkSQL | 1 台 * 128 G | 1 台 * 32 核 | | ODP | 1 台 * 128 G | 1 台 * 32 核 | - 租户规格 ```unknow CREATE RESOURCE UNIT if not exists fu_xymysql MAX_CPU 80, MAX_MEMORY 500000000000, MAX_IOPS 128, MAX_DISK_SIZE 1000000000, MAX_SESSION_NUM 64, MIN_CPU=80, MIN_MEMORY=500000000000, MIN_IOPS=128; CREATE RESOURCE POOL if not exists f_pool_xymysql UNIT= 'fu_xymysql', UNIT_NUM=1, ZONE_LIST= ('z1','z2','z3'); CREATE TENANT xymysql RESOURCE_POOL_LIST=('f_pool_xymysql'), PRIMARY_ZONE=RANDOM, LOCALITY='F@z1,F@z2,F@z3' SET ob_compatibility_mode='mysql',ob_tcp_invited_nodes='%'; ``` > **说明** > > > - 您需根据自身数据库的硬件配置进行动态调整。详细操作，参考 [修改租户](https://www.oceanbase.com/docs/enterprise-oceanbase-database-cn-10000000000355948)。 > - 进行性能测试时，租户需配置最大可用资源。 - 软件版本 | 项目类型 | 软件版本及型号 | | --- | --- | | OceanBase 数据库 | V3.2.3 | | ODP | V3.1.0 | | BenchMarkSQL | V5.0 | | JDBC | mysql-connector-java-5.1.47 | | OS | CentOS Linux release 7.9.2009 (Core) | | CPU | Intel(R) Xeon(R) Platinum 8369B CPU @ 2.70GHz | ## 环境信息 ### 操作系统信息 ```unknow $cat /etc/redhat-release CentOS Linux release 7.9.2009 (Core) ``` ```unknow $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 信息 ```unknow $lscpu Architecture: x86_64 CPU op-mode(s): 32-bit, 64-bit Byte Order: Little Endian CPU(s): 32 On-line CPU(s) list: 0-31 Thread(s) per core: 2 Core(s) per socket: 16 座： 1 NUMA 节点： 1 厂商 ID： GenuineIntel CPU 系列： 6 型号： 106 型号名称： Intel(R) Xeon(R) Platinum 8369B CPU @ 2.70GHz 步进： 6 CPU MHz： 2699.998 BogoMIPS： 5399.99 超管理器厂商： KVM 虚拟化类型： 完全 L1d 缓存： 48K L1i 缓存： 32K L2 缓存： 1280K L3 缓存： 49152K NUMA 节点0 CPU： 0-31 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 eagerfpu pni pclmulqdq monitor ssse3 fma cx16 pcid sse4_1 sse4_2 x2apic movbe popcnt aes xsave avx f16c rdrand hypervisor lahf_lm abm 3dnowprefetch invpcid_single ibrs_enhanced fsgsbase tsc_adjust bmi1 avx2 smep bmi2 erms invpcid avx512f avx512dq rdseed adx smap avx512ifma clflushopt clwb avx512cd sha_ni avx512bw avx512vl xsaveopt xsavec xgetbv1 arat avx512vbmi avx512_vbmi2 gfni vaes vpclmulqdq avx512_vnni avx512_bitalg avx512_vpopcntdq arch_capabilities ``` ### 内存信息 ```unknow $free -g total used free shared buff/cache available Mem: 123 41 2 0 79 81 Swap: 0 0 0 ``` ### 软件信息 #### OBServer ```unknow $./observer -V observer (OceanBase 3.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. ``` #### OBProxy ```unknow $./obproxy -V 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 ``` #### JDBC Driver ```unknow mysql-connector-java-5.1.47.jar ``` ## 测试内容 ### 测试配置 #### OBServer 启动参数 ```unknow ALTER SYSTEM SET datafile_size='1T'; ALTER SYSTEM SET memory_limit='100g'; ALTER SYSTEM SET system_cpu_quota=10; ALTER SYSTEM SET net_thread_count=12; ALTER SYSTEM SET syslog_io_bandwidth_limit='2048M'; ALTER SYSTEM SET major_freeze_duty_time='disable'; ALTER SYSTEM SET memory_limit_percentage=90; 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;enable_syslog_wf=false; ALTER SYSTEM SET __min_full_resource_pool_memory=1073741824; ALTER SYSTEM SET system_memory=15G; ALTER SYSTEM SET _ob_clog_disk_buffer_cnt=1000; ``` **OBProxy** 启动参数 ```unknow ALTER PROXYCONFIG SET enable_strict_kernel_release=false; ALTER PROXYCONFIG SET work_thread_num=16; ALTER PROXYCONFIG SET automatic_match_work_thread=false; ALTER PROXYCONFIG SET enable_compression_protocol=false; ALTER PROXYCONFIG SET syslog_level=info; ALTER PROXYCONFIG SET enable_async_log=true; ALTER PROXYCONFIG SET enable_metadb_used='false'; ``` #### kernel 配置 ```unknow kernel.sched_migration_cost_ns = 0 ``` #### 集群配置 请使用 sys 租户登录，并执行以下命令： ```unknow ALTER SYSTEM SET enable_sql_audit=false; ALTER SYSTEM SET enable_early_lock_release=false tenant=all; ALTER SYSTEM SET enable_auto_leader_switch=false; ALTER SYSTEM SET memory_chunk_cache_size=0; ALTER SYSTEM SET clog_transport_compress_all=false; ALTER SYSTEM SET trx_try_wait_lock_timeout='0ms'; 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 builtin_db_data_verify_cycle=0; ALTER SYSTEM SET enable_merge_by_turn=False; ALTER SYSTEM SET minor_merge_concurrency=30; ALTER SYSTEM SET system_memory='30G'; ALTER SYSTEM SET enable_pg=true; ALTER SYSTEM SET memory_limit_percentage=85; ALTER SYSTEM SET memstore_limit_percentage=70; ALTER SYSTEM SET freeze_trigger_percentage=60; ALTER SYSTEM SET micro_block_merge_verify_level=0; ALTER SYSTEM SET max_kept_major_version_number=1; ALTER SYSTEM SET sys_bkgd_io_high_percentage=90; ALTER SYSTEM SET sys_bkgd_io_low_percentage=70; ALTER SYSTEM SET merge_thread_count=20; ALTER SYSTEM SET large_query_threshold='200s'; ALTER SYSTEM SET cpu_quota_concurrency=4; ALTER SYSTEM SET minor_warm_up_duration_time=0; ALTER SYSTEM SET minor_freeze_times=1; ALTER SYSTEM SET minor_compact_trigger=0; ALTER SYSTEM SET minor_merge_concurrency=20; ALTER SYSTEM SET trace_log_slow_query_watermark='10s'; ALTER SYSTEM SET gts_refresh_interval='500us'; ALTER SYSTEM SET server_permanent_offline_time='36000s'; ALTER SYSTEM SET weak_read_version_refresh_interval=0; ALTER SYSTEM SET _ob_get_gts_ahead_interval='1ms'; ALTER SYSTEM SET bf_cache_priority=10; ALTER SYSTEM SET user_block_cache_priority=5; ALTER SYSTEM SET merge_stat_sampling_ratio=1; ALTER SYSTEM SET syslog_level='PERF'; ALTER SYSTEM SET enable_perf_event=false; ALTER SYSTEM SET _cache_wash_interval='1m'; ALTER SYSTEM SET cache_wash_threshold='10G'; ALTER SYSTEM SET plan_cache_evict_interval='30s'; ALTER SYSTEM SET bf_cache_miss_count_threshold=1; ALTER SYSTEM SET __easy_memory_limit='20G'; ALTER SYSTEM SET _clog_aggregation_buffer_amount=4 tenant=all; ALTER SYSTEM SET _flush_clog_aggregation_buffer_timeout='1ms' tenant=all; ALTER SYSTEM SET _enable_defensive_check=false; SET GLOBAL binlog_row_image='MINIMAL'; ``` #### 租户配置 请使用测试租户登录，并执行以下命令： ```unknow ALTER SYSTEM SET enable_monotonic_weak_read=false; ALTER SYSTEM SET ob_enable_batched_multi_statement=false; SET GLOBAL ob_query_timeout=3600000000; SET GLOBAL ob_trx_timeout=3600000000; SET GLOBAL max_allowed_packet=67108864; SET GLOBAL ob_sql_work_area_percentage=100; SET GLOBAL parallel_servers_target=600; SET GLOBAL binlog_row_image='MINIMAL'; ``` #### OBProxy 配置 ```unknow ALTER PROXYCONFIG SET proxy_mem_limited='2G'; ALTER PROXYCONFIG SET slow_proxy_process_time_threshold='500ms'; ALTER PROXYCONFIG SET syslog_level='ERROR'; ALTER PROXYCONFIG SET enable_prometheus=false; ALTER PROXYCONFIG SET enable_compression_protocol=false; ``` #### bmsql 配置 ```unknow driver=com.mysql.jdbc.Driver conn=jdbc:mysql://IP:PORT/test?rewriteBatchedStatements=true&allowMultiQueries=true&useLocalSessionState=true&useUnicode=true&characterEncoding=utf-8&socketTimeout=30000000&useSSL=false user=test@tt3 password=*********** warehouses=1000 loadWorkers=60 ``` ### 测试表结构 ```unknow CREATE TABLE bmsql_config ( cfg_name varchar(30) primary key, cfg_value varchar(50) ); CREATE TABLEGROUP tpcc_group BINDING true PARTITION BY HASH PARTITIONS 128; CREATE TABLE bmsql_warehouse ( w_id integer not null, w_ytd decimal(12,2), w_tax decimal(4,4), w_name varchar(10), w_street_1 varchar(20), w_street_2 varchar(20), w_city varchar(20), w_state char(2), w_zip char(9), primary key(w_id) ) tablegroup='tpcc_group' partition by hash(w_id) partitions 128; CREATE TABLE bmsql_district ( d_w_id integer not null, d_id integer not null, d_ytd decimal(12,2), d_tax decimal(4,4), d_next_o_id integer, d_name varchar(10), d_street_1 varchar(20), d_street_2 varchar(20), d_city varchar(20), d_state char(2), d_zip char(9), PRIMARY KEY (d_w_id, d_id) ) tablegroup='tpcc_group' partition by hash(d_w_id) partitions 128; CREATE TABLE bmsql_customer ( c_w_id integer not null, c_d_id integer not null, c_id integer not null, c_discount decimal(4,4), c_credit char(2), c_last varchar(16), c_first varchar(16), c_credit_lim decimal(12,2), c_balance decimal(12,2), c_ytd_payment decimal(12,2), c_payment_cnt integer, c_delivery_cnt integer, c_street_1 varchar(20), c_street_2 varchar(20), c_city varchar(20), c_state char(2), c_zip char(9), c_phone char(16), c_since timestamp, c_middle char(2), c_data varchar(500), PRIMARY KEY (c_w_id, c_d_id, c_id) ) tablegroup='tpcc_group' partition by hash(c_w_id) partitions 128; CREATE TABLE bmsql_history ( hist_id integer auto_increment, h_c_id integer, h_c_d_id integer, h_c_w_id integer, h_d_id integer, h_w_id integer, h_date timestamp, h_amount decimal(6,2), h_data varchar(24) ) tablegroup='tpcc_group' partition by hash(h_w_id) partitions 128; CREATE TABLE bmsql_new_order ( no_w_id integer not null , no_d_id integer not null, no_o_id integer not null, PRIMARY KEY (no_w_id, no_d_id, no_o_id) ) TABLE_MODE='QUEUING' tablegroup='tpcc_group' partition by hash(no_w_id) partitions 128; CREATE TABLE bmsql_oorder ( o_w_id integer not null, o_d_id integer not null, o_id integer not null, o_c_id integer, o_carrier_id integer, o_ol_cnt integer, o_all_local integer, o_entry_d timestamp, PRIMARY KEY (o_w_id, o_d_id, o_id) ) tablegroup='tpcc_group' partition by hash(o_w_id) partitions 128; CREATE TABLE bmsql_order_line ( ol_w_id integer not null, ol_d_id integer not null, ol_o_id integer not null, ol_number integer not null, ol_i_id integer not null, ol_delivery_d timestamp, ol_amount decimal(6,2), ol_supply_w_id integer, ol_quantity integer, ol_dist_info char(24), PRIMARY KEY (ol_w_id, ol_d_id, ol_o_id, ol_number) ) tablegroup='tpcc_group' partition by hash(ol_w_id) partitions 128; CREATE TABLE bmsql_item ( i_id integer not null, i_name varchar(24), i_price decimal(5,2), i_data varchar(50), i_im_id integer, PRIMARY KEY (i_id) ) duplicate_scope='cluster'; CREATE TABLE bmsql_stock ( s_w_id integer not null, s_i_id integer not null, s_quantity integer, s_ytd integer, s_order_cnt integer, s_remote_cnt integer, s_data varchar(50), s_dist_01 char(24), s_dist_02 char(24), s_dist_03 char(24), s_dist_04 char(24), s_dist_05 char(24), s_dist_06 char(24), s_dist_07 char(24), s_dist_08 char(24), s_dist_09 char(24), s_dist_10 char(24), PRIMARY KEY (s_w_id, s_i_id) ) tablegroup='tpcc_group' partition by hash(s_w_id) partitions 128; create index bmsql_customer_idx1 on bmsql_customer (c_w_id, c_d_id, c_last, c_first) local; create index bmsql_oorder_idx1 on bmsql_oorder(o_w_id, o_d_id, o_carrier_id, o_id) local; ``` ## 测试过程 1. 创建表。 ```unknow sh runCreatetable.sh; ``` 2. 导入数据，并创建索引。 ```unknow sh runLoader.sh; ``` 3. 执行数据库合并。 ```unknow alter system major freeze; ``` 4. 执行 workload。 ```unknow sh runBenchmark.sh; ``` ## 测试结果 ```unknow [Thread-230] INFO jTPCC : Term-00, Measured tpmC (NewOrders) = 371208.93 [Thread-230] INFO jTPCC : Term-00, Measured tpmTOTAL = 824222.95 [Thread-230] INFO jTPCC : Term-00, Transaction Count = 4122460 ``` 上一篇 下一篇 ![有帮助](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) 咨询热线