数据库基准测试2：HammerDB自带脚本（DB2）
0.前面要说的话
上一章节简单介绍了一下hammerdbcli的使用方法，但通过命令行的方式不适合大批量数据基准测试（灌了3000仓库的tpcc数据在虚拟 机上耗时超过了1天多），实际hammerdb已经内置了多种数据库类型的基准测试脚本，以下以db2为例简单说明如何使用这些脚本。
1.基准测试脚本
HammerDB自带的DB2 TPC-C测试脚本的路径是/opt/HammerDB-5.0/scripts/tcl/db2/tprocc,主要内容有以下，为方便修改，复制一个/tprocc目录为/tprocc_1，方便后续修改。
[root@phgdrh1886pa tprocc]# pwd
/opt/HammerDB-5.0/scripts/tcl/db2/tprocc
[root@phgdrh1886pa tprocc]# ll
total 28
-rw-r--r-- 1 root root 693 May 26 16:07 db2_tprocc_buildschema.tcl
-rw-r--r-- 1 root root 362 Mar 30 22:04 db2_tprocc_checkschema.tcl
-rw-r--r-- 1 root root 362 Mar 30 22:04 db2_tprocc_deleteschema.tcl
-rw-r--r-- 1 root root 1010 Mar 30 22:04 db2_tprocc.ps1
-rw-r--r-- 1 root root 116 Mar 30 22:04 db2_tprocc_result.tcl
-rw-r--r-- 1 root root 538 Mar 30 22:04 db2_tprocc_run.tcl
-rw-r--r-- 1 root root 950 Jul 14 16:19 db2_tprocc.sh
首先看db2_tprocc.sh，这个脚本实际是将buildschema、checkschema、run、deleteschema、result几个过程放在一起执行，对于大测试而言，此脚本最好拆成多个执行。
2.测试脚本的修改
2.1.buildschema的修改
修改db2_tprocc_buildschema.tcl
参考以下中文注释进行修改，下同
#!/bin/tclsh
# maintainer: Pooja Jain
puts "SETTING CONFIGURATION"
dbset db db2
dbset bm TPC-C
diset connection db2_def_user hammerdb ## 修改为对应的数据库用户名
diset connection db2_def_pass my_passwd ## 修改为对应的数据库密码
diset connection db2_def_dbase testdb ## 修改为对应的数据库名称
#set vu [ numberOfCPUs ]
#set warehouse [ expr {$vu * 5} ]
set vu 16 ## 设置加载测试数据的并发用户数，建议=cpu的核心数
set warehouse 3000 ## 设置测试数据的仓库数，可以自己定一个标准，较大的负载为3000个仓库，1000个仓库约100GB数据
diset tpcc db2_count_ware $warehouse
diset tpcc db2_num_vu $vu
diset tpcc db2_user hammerdb ## 修改为对应的用户名称
diset tpcc db2_pass my_passwd ## 修改为对应的数据库密码
diset tpcc db2_dbase testdb ## 修改为对应的数据库名称
diset tpcc db2_def_tab USERSPACE1
diset tpcc db2_tab_list {C "" D "" H "" I "" W "" S "" NO "" OR "" OL ""}
if { $warehouse >= 10 } {
diset tpcc db2_partition true
} else {
diset tpcc db2_partition false
}
puts "SCHEMA BUILD STARTED"
buildschema
puts "SCHEMA BUILD COMPLETED"
2.2.checkschema的修改
修改db2_tprocc_checkschema.tcl
#!/bin/tclsh
# maintainer: Pooja Jain
puts "SETTING CONFIGURATION"
dbset db db2
dbset bm TPC-C
diset connection db2_def_user hammerdb ## 修改为对应的用户名称
diset connection db2_def_pass Ghac.cn@123 ## 修改为对应的数据库密码
diset connection db2_def_dbase testdb ## 修改为对应的数据库名称
diset tpcc db2_user hammerdb ## 修改为对应的用户名称
diset tpcc db2_pass Ghac.cn@123 ## 修改为对应的数据库密码
diset tpcc db2_dbase testdb ## 修改为对应的数据库名称
puts "CHECK SCHEMA STARTED"
checkschema
puts "CHECK SCHEMA COMPLETED"
2.3.run的修改
修改db2_tprocc_run.tcl
#!/bin/tclsh
# maintainer: Pooja Jain
set tmpdir $::env(TMP)
puts "SETTING CONFIGURATION"
dbset db db2
dbset bm TPC-C
diset tpcc db2_user hammerdb ## 修改为对应的用户名称
diset tpcc db2_pass Ghac.cn@123 ## 修改为对应的数据库密码
diset tpcc db2_dbase testdb ## 修改为对应的数据库名称
diset tpcc db2_driver timed
diset tpcc db2_rampup 2
diset tpcc db2_duration 5
diset tpcc db2_allwarehouse true
diset tpcc db2_timeprofile true
loadscript
puts "TEST STARTED"
# vuset vu vcpu
vuset vu 16
vucreate
tcstart
tcstatus
set jobid [ vurun ]
vudestroy
tcstop
puts "TEST COMPLETE"
set of [ open $tmpdir/db2_tprocc w ]
puts $of $jobid
close $of
2.4.delete及result的修改
- db2_tprocc_deleteschema.tcl脚本不建议执行，因为有多次执行的需要；
- db2_tprocc_result.tcl脚本不需要修改
2.5.db2_tprocc.sh的修改
db2_tprocc.sh脚本的内容整体如下：
export TMP=`pwd`/TMP
mkdir -p $TMP
home_dir=/opt/HammerDB-5.0 # 增加home_dir参数，方便在'tprocc_1'当前目录下执行脚本
cur_dir=tprocc_1 # 将默认的'tprocc'目录修改为'tprocc_1'目录，以下对应做修改
echo "BUILD HAMMERDB SCHEMA"
echo "+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-"
${home_dir}/hammerdbcli auto ./scripts/tcl/db2/${cur_dir}/db2_tprocc_buildschema.tcl
echo "+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-"
echo "CHECK HAMMERDB SCHEMA"
echo "+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-"
${home_dir}/hammerdbcli auto ./scripts/tcl/db2/${cur_dir}/db2_tprocc_checkschema.tcl
echo "+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-"
echo "RUN HAMMERDB TEST"
echo "+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-"
${home_dir}/hammerdbcli auto ./scripts/tcl/db2/${cur_dir}/db2_tprocc_run.tcl
echo "+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-"
echo "DROP HAMMERDB SCHEMA"
${home_dir}/hammerdbcli auto ./scripts/tcl/db2/${cur_dir}/db2_tprocc_deleteschema.tcl
echo "+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-"
echo "HAMMERDB RESULT"
${home_dir}/hammerdbcli auto ./scripts/tcl/db2/${cur_dir}/db2_tprocc_result.tcl
为便于操作，将上述脚本折成三个：
- 01.build.sh
export TMP=`pwd`/TMP
mkdir -p $TMP
home_dir=/opt/HammerDB-5.0
cur_dir=tprocc_1
echo "BUILD HAMMERDB SCHEMA"
echo "+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-"
${home_dir}/hammerdbcli auto ./scripts/tcl/db2/${cur_dir}/db2_tprocc_buildschema.tcl
echo "+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-"
echo "CHECK HAMMERDB SCHEMA"
echo "+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-"
${home_dir}/hammerdbcli auto ./scripts/tcl/db2/${cur_dir}/db2_tprocc_checkschema.tcl
echo "+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-"
- 02.check.sh
export TMP=`pwd`/TMP
mkdir -p $TMP
home_dir=/opt/HammerDB-5.0
cur_dir=tprocc_1
echo "CHECK HAMMERDB SCHEMA"
echo "+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-"
${home_dir}/hammerdbcli auto ./scripts/tcl/db2/${cur_dir}/db2_tprocc_checkschema.tcl
echo "+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-"
- 03.run.sh
export TMP=`pwd`/TMP
mkdir -p $TMP
home_dir=/opt/HammerDB-5.0
cur_dir=tprocc_1
echo "RUN HAMMERDB TEST"
echo "+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-"
${home_dir}/hammerdbcli auto ./scripts/tcl/db2/${cur_dir}/db2_tprocc_run.tcl
echo "+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-"
echo "HAMMERDB RESULT"
${home_dir}/hammerdbcli auto ./scripts/tcl/db2/${cur_dir}/db2_tprocc_result.tcl
3.执行基准测试
对HammerDB的脚本处理完成之后，按以下步骤执行基准测试：
- 执行01.build.sh，加载测试数据，这个过程十分漫长，建议使用nohup后台执行；
- 执行02.check.sh，检查数据是否有问题，有概率在上一步骤加载测试数据量不足，导致check失败；
- 执行03.rub.sh，按
print dict中tpc-c的基准测试，会启动虚拟用户执行共1000万个事务操作，在操作完成输出以下测试结果：
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-
HAMMERDB RESULT
HammerDB CLI v5.0
Copyright © HammerDB Ltd hosted by tpc.org 2019-2025
Type "help" for a list of commands
Usage: hammerdbcli [ tcl|python [ auto [ script_to_autoload.[ tcl|py ] ] ] ]
Initialized Jobs on-disk database /opt/HammerDB-5.0/scripts/tcl/db2/tprocc_1/TMP/hammer.DB using existing tables (21,528,576 bytes)
TRANSACTION RESPONSE TIMES
{
"NEWORD": {
"elapsed_ms": "422550.0",
"calls": "534990",
"min_ms": "5.48",
"avg_ms": "12.077",
"max_ms": "295.777",
"total_ms": "6461582.597",
"p99_ms": "38.842",
"p95_ms": "23.966",
"p50_ms": "10.004",
"sd": "6892.908",
"ratio_pct": "47.787"
},
"PAYMENT": {
"elapsed_ms": "422550.0",
"calls": "537522",
"min_ms": "4.145",
"avg_ms": "8.669",
"max_ms": "290.135",
"total_ms": "4659871.027",
"p99_ms": "39.027",
"p95_ms": "18.848",
"p50_ms": "6.55",
"sd": "6931.593",
"ratio_pct": "34.462"
},
"SLEV": {
"elapsed_ms": "422550.0",
"calls": "53684",
"min_ms": "3.502",
"avg_ms": "21.178",
"max_ms": "790.255",
"total_ms": "1136955.528",
"p99_ms": "207.493",
"p95_ms": "82.497",
"p50_ms": "4.095",
"sd": "43340.133",
"ratio_pct": "8.408"
},
"DELIVERY": {
"elapsed_ms": "422550.0",
"calls": "54054",
"min_ms": "7.007",
"avg_ms": "14.891",
"max_ms": "146.953",
"total_ms": "804921.454",
"p99_ms": "47.6",
"p95_ms": "28.509",
"p50_ms": "11.504",
"sd": "8377.1",
"ratio_pct": "5.953"
},
"OSTAT": {
"elapsed_ms": "422550.0",
"calls": "53798",
"min_ms": "3.959",
"avg_ms": "6.718",
"max_ms": "473.926",
"total_ms": "361443.545",
"p99_ms": "19.094",
"p95_ms": "11.394",
"p50_ms": "6.04",
"sd": "4680.572",
"ratio_pct": "2.673"
}
}
TRANSACTION COUNT
{"Db2 tpm": {
"2025-07-17 14:27:18": "0",
"2025-07-17 14:27:28": "113286",
"2025-07-17 14:27:38": "146202",
"2025-07-17 14:27:48": "171288",
"2025-07-17 14:27:58": "185664",
"2025-07-17 14:28:08": "177024",
"2025-07-17 14:28:18": "173898",
"2025-07-17 14:28:28": "195030",
"2025-07-17 14:28:38": "212862",
"2025-07-17 14:28:48": "215418",
"2025-07-17 14:28:58": "189684",
"2025-07-17 14:29:08": "201666",
"2025-07-17 14:29:18": "223284",
"2025-07-17 14:29:28": "226416",
"2025-07-17 14:29:38": "225492",
"2025-07-17 14:29:48": "227562",
"2025-07-17 14:29:58": "223902",
"2025-07-17 14:30:08": "198942",
"2025-07-17 14:30:18": "172146",
"2025-07-17 14:30:28": "183276",
"2025-07-17 14:30:38": "157728",
"2025-07-17 14:30:48": "151374",
"2025-07-17 14:30:58": "146904",
"2025-07-17 14:31:08": "143670",
"2025-07-17 14:31:18": "135030",
"2025-07-17 14:31:28": "61764",
"2025-07-17 14:31:38": "66630",
"2025-07-17 14:31:48": "99150",
"2025-07-17 14:31:58": "118314",
"2025-07-17 14:32:08": "125604",
"2025-07-17 14:32:18": "124164",
"2025-07-17 14:32:28": "134502",
"2025-07-17 14:32:38": "134886",
"2025-07-17 14:32:48": "157632",
"2025-07-17 14:32:58": "185616",
"2025-07-17 14:33:08": "182790",
"2025-07-17 14:33:18": "192768",
"2025-07-17 14:33:28": "209472",
"2025-07-17 14:33:38": "215172",
"2025-07-17 14:33:48": "192120",
"2025-07-17 14:33:58": "149550",
"2025-07-17 14:34:08": "110724",
"2025-07-17 14:34:18": "158910"
}}
HAMMERDB RESULT
[
"687897C319BD03E233632343",
"2025-07-17 14:27:15",
"16 Active Virtual Users configured",
"TEST RESULT : System achieved 36337 NOPM from 160255 Db2 TPM"
]
4.总结
最开始研究基于GUI的配置方式，但受限较多，且GUI很卡顿难以为继，通过修改HammerDB自带的脚本还是很方便进行基准测试的，修改参数也很方便，至于基准测试结果的解读，放到下次说明。
浙公网安备 33010602011771号