鳌江网站建设,闽侯县住房和城乡建设局官方网站,鞍山黄殿满,网站活动打造BenchmarkSQL是一个用于评估数据库性能的开源工具。它模拟TPC-C#xff08;Transaction Processing Performance Council#xff09;基准测试场景#xff0c;该场景主要用于衡量数据库在处理大量并发事务时的能力。TPC-C测试场景模拟了一个典型的批发分销商的业务环境#…BenchmarkSQL是一个用于评估数据库性能的开源工具。它模拟TPC-CTransaction Processing Performance Council基准测试场景该场景主要用于衡量数据库在处理大量并发事务时的能力。TPC-C测试场景模拟了一个典型的批发分销商的业务环境包括新订单、支付、库存查询等操作。
openEuler版本openEuler 22.03 (LTS-SP4)
安装依赖
JDK,ANT,R语言
其中安装JDK不在此说明网上很多教程
安装ANT步骤如下
将ANT安装包上传至服务器然后解压
#tar -xvzf apache-ant-1.10.15-bin.tar.gz
设置环境变量
编辑/etc/profile文件追以下内容
export ANT_HOME/opt/software/ant/apache-ant-1.10.15 export PATH$PATH:$ANT_HOME/bin
执行
#source /etc/profile
使环境变量设置生效
验证安装结果
#ant -version R语言安装步骤如下
1.上传压缩包R-4.4.2.tar.gz到服务器
2.解压R-4.4.2.tar.gz
#tar -xvf R-4.4.2.tar.gz
3.进入解压目录进行编译安装时间有点长请耐心等待
#./configure make make install 4.验证安装结果 #R --version 安装编译R语言过程中可能会出现下面3个错误
报错1configure: error: --with-xyes (default) and X11 headers/libs are not available
解决方法yum -y install xorg-x11-server-devel libX11-devel libXt-devel 报错2configure: error: liblzma library and headers are required
解决方法yum install xz-devel.x86_64 报错3configure: error: libcurl 7.28.0 library and headers are required with support for https
解决方法yum install libcurl-devel 安装 BenchmarkSQL:
创建数据库和用户
create user benchmarksql with password Benchmark123;
create database benchmarksql owner benchmarksql; 给用户授予权限有建表权限创建索引权限等等为了方便修改用户为管理员
alter user benchmarksql with SYSADMIN;
解压benchmarksql
上传benchmark压缩包到服务器,然后解压
#unzip benchmarksql-5.0.zip 编译benchmarksql
进入benchmark解压目录
执行命令
#ant 创建配置文件
进入run目录
#cd run
#cp props.pg my_postgres.properties 修改配置文件
#vi my_postgres.properties
修改内容
dbpostgres
driverorg.postgresql.Driver
connjdbc:postgresql://localhost:15000/benchmarksql
userbenchmarksql
passwordBenchmark123 osCollectorDevicesnet_ens33 blk_sda 配置文件详解 dbpostgres //数据库类型postgresmysqldriverorg.postgresql.Driver //驱动mysql8.0以上为com.mysql.cj.jdbc.Driverconnjdbc:postgresql://localhost:5432/postgres //PG数据库连接字符串正常情况下需要更改localhost为对应PG服务IP、5432位对应PG服务端口、postgres为对应测试数据库名userbenchmarksql //数据库用户名通常建议用默认这就需要我们提前在数据库中建立benchmarksql用户passwordPWbmsql //如上用户密码warehouses1 //仓库数量数量根据实际服务器内存配置每个仓库 约 100MB代表一个独立业务单元TPC-C 标准loadWorkers4 //用于在数据库中初始化数据的加载进程数量默认为4实际使用过程中可以根据实际情况调整加载速度会随worker数量的增加而有所提升terminals1 //终端数即并发客户端数量通常设置为CPU线程总数的26倍runTxnsPerTerminal10 //每个终端terminal运行的固定事务数量例如如果该值设置为10意味着每个terminal运行10个事务如果有32个终端那整体运行320个事务后测试结束。该参数配置为非0值时下面的runMins参数必须设置为0runMins0 //要测试的整体时间单位为分钟如果runMins设置为60那么测试持续1小时候结束。该值设置为非0值时runTxnsPerTerminal参数必须设置为0。这两个参数不能同时设置为正整数如果设置其中一个另一个必须为0主要区别是runMins定义时间长度来控制测试时间runTxnsPerTerminal定义事务总数来控制时间。limitTxnsPerMin300 //每分钟事务总数限制该参数主要控制每分钟处理的事务数事务数受terminals参数的影响如果terminals数量大于limitTxnsPerMin值意味着并发数大于每分钟事务总数该参数会失效想想也是如此如果有1000个并发同时发起那每分钟事务数设置为300就没意义了上来就是1000个并发所以要让该参数有效可以设置数量大于并发数或者让其失效测试过程中目前采用的是默认300。terminalWarehouseFixedtrue //终端和仓库的绑定模式设置为true时可以运行4.x兼容模式意思为每个终端都有一个固定的仓库。设置为false时可以均匀的使用数据库整体配置。TPCC规定每个终端都必须有一个绑定的仓库所以一般使用默认值true。//下面五个值的总和必须等于100默认值为45, 43, 4, 4 4 与TPC-C测试定义的比例一致实际操作过程中可以调整比重来适应各种场景。 newOrderWeight45 paymentWeight43 orderStatusWeight4 deliveryWeight4 stockLevelWeight4resultDirectorymy_result_%tY-%tm-%td_%tH%tM%tS //测试数据生成目录默认无需修改默认生成在run目录下面名字形如my_result_xxxx的文件夹。osCollectorScript./misc/os_collector_linux.py //操作系统性能收集脚本默认无需修改需要操作系统具备有python环境osCollectorInterval1 //操作系统收集操作间隔默认为1秒//osCollectorSSHAddruserdbhost //操作系统收集所对应的主机如果对本机数据库进行测试该参数保持注销即可如果要对远程服务器进行测试请填写用户名和主机名。osCollectorDevicesnet_ens33 blk_sda //操作系统中被收集服务器的网卡名称和磁盘名称例如使用ifconfig查看操作系统网卡名称找到测试所走的网卡名称为ens33那么下面网卡名设置为net_ens33net_前缀固定使用df -h查看数据库数据目录名称为/dev/sdb 33T 18T 16T 54% /hgdata那么下面磁盘名设置为blk_sdbblk_前缀固定
创建数据库模式和加载初始数据
在run目录下执行
./runDatabaseBuild.sh my_postgres.properties
执行成功后输出信息
[ommhostName1 run]$ ./runDatabaseBuild.sh my_postgres.properties
# ------------------------------------------------------------
# Loading SQL file ./sql.common/tableCreates.sql
# ------------------------------------------------------------
create table bmsql_config (
cfg_name varchar(30) primary key,
cfg_value varchar(50)
);
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)
);
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)
);
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)
);
create sequence bmsql_hist_id_seq;
create table bmsql_history (
hist_id integer,
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)
);
create table bmsql_new_order (
no_w_id integer not null,
no_d_id integer not null,
no_o_id integer not null
);
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
);
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)
);
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
);
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)
);
Starting BenchmarkSQL LoadDatadriverorg.postgresql.Driver
connjdbc:postgresql://localhost:15000/benchmarksql
userbenchmarksql
password***********
warehouses10
loadWorkers4
fileLocation (not defined)
csvNullValue (not defined - using default NULL)Worker 000: Loading ITEM
Worker 001: Loading Warehouse 1
Worker 002: Loading Warehouse 2
Worker 003: Loading Warehouse 3
Worker 000: Loading ITEM done
Worker 000: Loading Warehouse 4
Worker 002: Loading Warehouse 2 done
Worker 002: Loading Warehouse 5
Worker 001: Loading Warehouse 1 done
Worker 001: Loading Warehouse 6
Worker 003: Loading Warehouse 3 done
Worker 003: Loading Warehouse 7
Worker 000: Loading Warehouse 4 done
Worker 000: Loading Warehouse 8
Worker 002: Loading Warehouse 5 done
Worker 002: Loading Warehouse 9
Worker 001: Loading Warehouse 6 done
Worker 001: Loading Warehouse 10
Worker 003: Loading Warehouse 7 done
Worker 000: Loading Warehouse 8 done
Worker 002: Loading Warehouse 9 done
Worker 001: Loading Warehouse 10 done
# ------------------------------------------------------------
# Loading SQL file ./sql.common/indexCreates.sql
# ------------------------------------------------------------
alter table bmsql_warehouse add constraint bmsql_warehouse_pkey
primary key (w_id);
alter table bmsql_district add constraint bmsql_district_pkey
primary key (d_w_id, d_id);
alter table bmsql_customer add constraint bmsql_customer_pkey
primary key (c_w_id, c_d_id, c_id);
create index bmsql_customer_idx1
on bmsql_customer (c_w_id, c_d_id, c_last, c_first);
alter table bmsql_oorder add constraint bmsql_oorder_pkey
primary key (o_w_id, o_d_id, o_id);
create unique index bmsql_oorder_idx1
on bmsql_oorder (o_w_id, o_d_id, o_carrier_id, o_id);
alter table bmsql_new_order add constraint bmsql_new_order_pkey
primary key (no_w_id, no_d_id, no_o_id);
alter table bmsql_order_line add constraint bmsql_order_line_pkey
primary key (ol_w_id, ol_d_id, ol_o_id, ol_number);
alter table bmsql_stock add constraint bmsql_stock_pkey
primary key (s_w_id, s_i_id);
alter table bmsql_item add constraint bmsql_item_pkey
primary key (i_id);
# ------------------------------------------------------------
# Loading SQL file ./sql.common/foreignKeys.sql
# ------------------------------------------------------------
alter table bmsql_district add constraint d_warehouse_fkey
foreign key (d_w_id)
references bmsql_warehouse (w_id);
alter table bmsql_customer add constraint c_district_fkey
foreign key (c_w_id, c_d_id)
references bmsql_district (d_w_id, d_id);
alter table bmsql_history add constraint h_customer_fkey
foreign key (h_c_w_id, h_c_d_id, h_c_id)
references bmsql_customer (c_w_id, c_d_id, c_id);
alter table bmsql_history add constraint h_district_fkey
foreign key (h_w_id, h_d_id)
references bmsql_district (d_w_id, d_id);
alter table bmsql_new_order add constraint no_order_fkey
foreign key (no_w_id, no_d_id, no_o_id)
references bmsql_oorder (o_w_id, o_d_id, o_id);
alter table bmsql_oorder add constraint o_customer_fkey
foreign key (o_w_id, o_d_id, o_c_id)
references bmsql_customer (c_w_id, c_d_id, c_id);
alter table bmsql_order_line add constraint ol_order_fkey
foreign key (ol_w_id, ol_d_id, ol_o_id)
references bmsql_oorder (o_w_id, o_d_id, o_id);
alter table bmsql_order_line add constraint ol_stock_fkey
foreign key (ol_supply_w_id, ol_i_id)
references bmsql_stock (s_w_id, s_i_id);
alter table bmsql_stock add constraint s_warehouse_fkey
foreign key (s_w_id)
references bmsql_warehouse (w_id);
alter table bmsql_stock add constraint s_item_fkey
foreign key (s_i_id)
references bmsql_item (i_id);
# ------------------------------------------------------------
# Loading SQL file ./sql.postgres/extraHistID.sql
# ------------------------------------------------------------
-- ----
-- Extra Schema objects/definitions for history.hist_id in PostgreSQL
-- ----
-- ----
-- This is an extra column not present in the TPC-C
-- specs. It is useful for replication systems like
-- Bucardo and Slony-I, which like to have a primary
-- key on a table. It is an auto-increment or serial
-- column type. The definition below is compatible
-- with Oracle 11g, using a sequence and a trigger.
-- ----
-- Adjust the sequence above the current max(hist_id)
select setval(bmsql_hist_id_seq, (select max(hist_id) from bmsql_history));
-- Make nextval(seq) the default value of the hist_id column.
alter table bmsql_history
alter column hist_id set default nextval(bmsql_hist_id_seq);
-- Add a primary key history(hist_id)
alter table bmsql_history add primary key (hist_id);
# ------------------------------------------------------------
# Loading SQL file ./sql.postgres/buildFinish.sql
# ------------------------------------------------------------
-- ----
-- Extra commands to run after the tables are created, loaded,
-- indexes built and extras created.
-- PostgreSQL version.
-- ----
vacuum analyze; 创建的表和初始化的数据量 运行基准测试
在run目录下执行
./runBenchmark.sh my_postgres.properties
执行成功后输出信息
19:27:06,712 [main] INFO jTPCC : Term-00,
19:27:06,715 [main] INFO jTPCC : Term-00, -------------------------------------------------------------
19:27:06,715 [main] INFO jTPCC : Term-00, BenchmarkSQL v5.0
19:27:06,715 [main] INFO jTPCC : Term-00, -------------------------------------------------------------
19:27:06,716 [main] INFO jTPCC : Term-00, (c) 2003, Raul Barbosa
19:27:06,716 [main] INFO jTPCC : Term-00, (c) 2004-2016, Denis Lussier
19:27:06,718 [main] INFO jTPCC : Term-00, (c) 2016, Jan Wieck
19:27:06,718 [main] INFO jTPCC : Term-00, -------------------------------------------------------------
19:27:06,718 [main] INFO jTPCC : Term-00,
19:27:06,719 [main] INFO jTPCC : Term-00, dbpostgres
19:27:06,719 [main] INFO jTPCC : Term-00, driverorg.postgresql.Driver
19:27:06,719 [main] INFO jTPCC : Term-00, connjdbc:postgresql://localhost:15000/benchmarksql
19:27:06,719 [main] INFO jTPCC : Term-00, userbenchmarksql
19:27:06,719 [main] INFO jTPCC : Term-00,
19:27:06,719 [main] INFO jTPCC : Term-00, warehouses1
19:27:06,719 [main] INFO jTPCC : Term-00, terminals1
19:27:06,723 [main] INFO jTPCC : Term-00, runTxnsPerTerminal10
19:27:06,723 [main] INFO jTPCC : Term-00, limitTxnsPerMin300
19:27:06,723 [main] INFO jTPCC : Term-00, terminalWarehouseFixedtrue
19:27:06,724 [main] INFO jTPCC : Term-00,
19:27:06,724 [main] INFO jTPCC : Term-00, newOrderWeight45
19:27:06,724 [main] INFO jTPCC : Term-00, paymentWeight43
19:27:06,724 [main] INFO jTPCC : Term-00, orderStatusWeight4
19:27:06,724 [main] INFO jTPCC : Term-00, deliveryWeight4
19:27:06,724 [main] INFO jTPCC : Term-00, stockLevelWeight4
19:27:06,724 [main] INFO jTPCC : Term-00,
19:27:06,725 [main] INFO jTPCC : Term-00, resultDirectorymy_result_%tY-%tm-%td_%tH%tM%tS
19:27:06,725 [main] INFO jTPCC : Term-00, osCollectorScript./misc/os_collector_linux.py
19:27:06,725 [main] INFO jTPCC : Term-00,
19:27:06,742 [main] INFO jTPCC : Term-00, copied my_postgres.properties to my_result_2025-01-04_192706/run.properties
19:27:06,743 [main] INFO jTPCC : Term-00, created my_result_2025-01-04_192706/data/runInfo.csv for runID 2
19:27:06,743 [main] INFO jTPCC : Term-00, writing per transaction results to my_result_2025-01-04_192706/data/result.csv
19:27:06,744 [main] INFO jTPCC : Term-00, osCollectorScript./misc/os_collector_linux.py
19:27:06,744 [main] INFO jTPCC : Term-00, osCollectorInterval1
19:27:06,744 [main] INFO jTPCC : Term-00, osCollectorSSHAddrnull
19:27:06,744 [main] INFO jTPCC : Term-00, osCollectorDevicesnet_ens33 blk_sda
19:27:06,819 [main] INFO jTPCC : Term-00,
19:27:06,915 [main] INFO jTPCC : Term-00, C value for C_LAST during load: 246
19:27:06,915 [main] INFO jTPCC : Term-00, C value for C_LAST this run: 143
19:27:06,916 [main] INFO jTPCC : Term-00, Te19:27:08,952 [Thread-1] INFO jTPCC : Term-00, nt tpmTOTAL: 84 Memory Usage: 18MB / 234MB
19:27:08,953 [Thread-1] INFO jTPCC : Term-00,
19:27:08,953 [Thread-1] INFO jTPCC : Term-00, Measured tpmC (NewOrders) 89.1
19:27:08,953 [Thread-1] INFO jTPCC : Term-00, Measured tpmTOTAL 326.73
19:27:08,953 [Thread-1] INFO jTPCC : Term-00, Session Start 2025-06-04 19:27:06
19:27:08,953 [Thread-1] INFO jTPCC : Term-00, Session End 2025-06-04 19:27:08
19:27:08,953 [Thread-1] INFO jTPCC : Term-00, Transaction Count 10测试结束后run目录下会生成一个新目录它的命名格式为 my_result_%tY-%tm-%td_%tH%tM%tS。
使用 generateReport.sh my_result_* 脚本创建具有图形的 HTML 文件
例如
#./generateReport.sh my_result_2025-05-30_133047
使用generateReport.sh需要安装R语言 生成的报告怎么看
1整体性能
Overall tpmC: 124.67
Overall tpmTotal: 280.67
tpmC 是核心事务NEW_ORDER每分钟处理的事务数。
tpmTotal 是所有类型的事务包括NEW_ORDER、Payment、Order Status、Delivery和Stock Level每分钟的总事务数。
这里的tpmC为124.67意味着每分钟大约处理124.67个NEW_ORDER事务这是TPC-C基准测试中最为关键的性能指标。 2理论最大值
理论最大值12.86 NEW_ORDER 事务/分钟/仓库
TPC-C规范指出在理想的条件下每个仓库的NEW_ORDER事务理论最大值为12.86。要达到这个最大值需要
完美的负载分配45%的事务是NEW_ORDER。
系统在零响应时间下运行。
实际上由于系统的延迟和负载不平衡很难达到这个理论最大值。 3实际性能 vs 理论性能
124.67 tpmC 是理论最大值的 969.414%
这意味着系统的实际性能是理论最大值的约9.7倍。也就是说相较于理想状态当前的系统性能远超预期表明该数据库配置在特定负载下的表现非常好。考虑到系统的实际响应时间和负载这种超出理论最大值的表现通常表明系统在某些方面如硬件配置、数据库优化、并行处理等非常高效。 4生成的两张图 每分钟事务数量。 事务延迟。 TPCC表结构 TPC-C测试系统数据库由9张表组成它们的关系如下图所示表框里的数字表示该表将要存放多少条记录仓库数W的调整在测试中能够体现数据库所能够支持的数据规模的能力 Warehouse仓库表W表示仓数能够体现数据库所能够支持的数据规模 District区域表W*10表每个仓为10个销售点供货 Customer用户表W*30k表示每个供货点为3000个客户提供服务 Stock库存表W*100k表示每个仓库维护10w种商品库存记录 Order订单表 New-Order新订单表 Item商品表表固定大小为10w Order-Line订单行 History历史表
TPCC事务模型
TPC-C需要处理的交易事务主要为以下几种
新订单New-Order占比45% 客户输入一笔新的订货交易 支付操作Payment占比43% 更新客户帐户余额以反映其支付状况 发货Delivery占比4% 发货模拟批处理交易 订单状态查询Order-Status占比4% 查询客户最近交易的状态 库存状态查询Stock-Level占比4% 查询仓库库存状况以便能够及时补货。 核心指标解读
