benchmarksql压测lightdb的oracle模式
一、编译安装BenchMarkSQL
1.下载源码，输入ant编译。
- 确保已经安装配置JDK环境，BenchMarkSQL是Java开发的。
cd benchmarksql/
benchmarksql]$ ant
二、创建BenchMarkSQL测试数据库和角色
[lightdb@linuxtestba65 ~]$ ltsql
ltsql (13.8-23.4)
compatible type: postgresql
Type "help" for help.
postgres=#
postgres=# CREATE USER benchmarksql WITH ENCRYPTED PASSWORD 'benchmarksql';
postgres=# CREATE DATABASE benchmarksql OWNER benchmarksql;
postgres=# \q
三、编辑配置文件
cd benchmarksql/run
vim props.pg---------------PostgreSQL模板配置文件
props.ora ---------------Oracle模板配置文件
props.mysql ---------------MySQL模板配置文件
lightdb以 props.pg为模板，复制一份命名为 my_postgres.properties
注意：如果benchmarksql的lib包里是没有lightdb的驱动，请去官网下载，然后放在lib/postgres目录下面
配置如下
//数据库类型
db=postgres
//数据库驱动
driver=org.postgresql.Driver
//数据库url
conn=jdbc:[postgresql://192.168.0.8:5432/benchmarksql2500]()
//数据库角色和密码
user=benchmarksql
password=benchmarksql
//数据库仓库数
warehouses=2500
//初始化数据加载进程数
loadWorkers=96
//并发客户端数
terminals=500
// To run specified transactions per terminal- runMins must equal zero
//运行模式，每个终端（terminal）运行的固定事务数量，与runMins二选一
runTxnsPerTerminal=0
// To run for specified minutes- runTxnsPerTerminal must equal zero
//运行时间
runMins=60
// Number of total transactions per minute
//每分钟事务总数限制
limitTxnsPerMin=10000000
// Set to true to run in 4.x compatible mode. Set to false to use the
// entire configured database evenly.
//终端和仓库的绑定模式，设置为true时可以运行4.x兼容模式，每个终端都有一个固定的仓库。设置为false时可以均匀的使用数据库整体配置。
terminalWarehouseFixed=false
// Set to true to use the stored procedure/function implementations. Not
// all of them exist for all databases and the use of stored procedures
// is strongly discouraged for comparing different database vendors as
// they may not have been implemented ideally for all of them. This is
// however useful to test how much network IO can be saved by using
// stored procedures.
useStoredProcedures=false
// The following five values must add up to 100
// The internal default percentages mathc the probabilities of a
// 23 Card Deck implementation, as described in the TPC-C Specs.
// The values below match the pre-5.1 defaults.
//五种事务数比例
//newOrderWeight=45
//paymentWeight=43
//orderStatusWeight=4
//deliveryWeight=4
//stockLevelWeight=4
// Directory name to create for collecting detailed result data.
// Comment this out to suppress.
//测试结果保存到指定目录，最后一步生成HTML报告需要用到
resultDirectory=lightdb_centosx86_218_3000w_500t-%tm-%td_%tH%tM%tS
osCollectorScript=./misc/os_collector_linux.py
osCollectorInterval=1
//SSH免密登录
//osCollectorSSHAddr=[lightdb@192.168.0.8](mailto:lightdb@192.168.0.8)
//指定网卡，可监控其资源情况
//osCollectorDevices=net_p7p1 blk_sda
四、建表初始化数据库
cd benchmarksql/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),
[...]
Starting BenchmarkSQL
LoadDatadriver=org.postgresql.Driver
conn=jdbc:[postgresql://192.168.0.8:5432/benchmarksql2500]
user=benchmarksql
password=***********
warehouses=2500
loadWorkers=500
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 99: Loading Warehouse 2499 done
Worker 100: Loading Warehouse 2500 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_pkeyprimary key (d_w_id, d_id);
[...]
vacuum analyze;
[lightdb@linuxtestba65 ~]$建表、建索引、主键、外键等语句见 benchmarksql/run/sql.common
五、运行测试
./runBenchmark.sh my_postgres.properties
The benchmark should run for the number of configured concurrent
connections (terminals) and the duration or number of transactions.
The end result of the benchmark will be reported like this:
01:58:09,081 [Thread-1] INFO jTPCC : Term-00,
01:58:09,082 [Thread-1] INFO jTPCC : Term-00, Measured tpmC (NewOrders) = 179.55
01:58:09,082 [Thread-1] INFO jTPCC : Term-00, Measured tpmTOTAL = 329.17
01:58:09,082 [Thread-1] INFO jTPCC : Term-00, Session Start = 2021-08-20 09:58:07
01:58:09,082 [Thread-1] INFO jTPCC : Term-00, Session End = 2021-08-20 09:58:09
01:58:09,082 [Thread-1] INFO jTPCC : Term-00, Transaction Count = 10
At this point you have a working setup.
六、清除数据重新造数据（根据需求清除）
//删除表数据，索引等
./runDatabaseDestroy.sh my_postgres.properties
//重新初始化数据**
./runDatabaseBuild.sh my_postgres.properties
Then run the benchmark again.
Rinse and repeat.
七、导出测试报告
1.首先安装R命令，已安装的忽略
yum install R
在my_postgres.properties配置文件已经配置了报告
目录如 lightdb_centosx86_218_3000w_500t-08-20_092129/
./generateReport.sh lightdb_centosx86_218_3000w_500t-08-20_092129/
会生成 lightdb_centosx86_218_3000w_500t-08-20_092129/report.html
HTML报告总览表：
