数据库基准测试1：HammerDB使用入门（DB2）

0.说在前面的话

因为项目需要，最近在做数据库的迁移，思索要对前后两个不同的环境做基准测试，可惜我这边的数据库有IBM的DB2，网上能找到的开源的TPC-C基准测试工具很少能支持DB2的，而5.0版本的HammerDB似乎可以满足我的要求，但在试验的过程中还是有挺多坑的，故聊以记录下来。

1.HammerDB的安装（For DB2）

1.1.安装环境说明

操作系统版本为Redhat8.3
HammerDB的版本为5.0

1.2.安装包的下载

https://www.hammerdb.com/

1.3.安装步骤

（1）挂载本地yum源

按Redhat挂载本地yum源的方法操作即可；
因为安装db2 client或hammerdb时会提示某些软件包的缺失，使用本地yum可以快速进行修复；

（2）安装HammerDB

使用以下命令安装下载的rpm包，如果安装过程中提示需要其他包，则按要求安装即可：

rpm -ivh ./hammerdb-5.0-1.el9.x86_64.rpm

安装后HammerDB的主目录在/opt下

（3）安装DB2 Client

参考其他安装教程即可

1.4.DB2的librarycheck

使用hammerdbcli命令进入命令模式，再输入librarycheck命令查看是否符合DB2的使用环境，如果提示not found则安装对应的包即可。

[root@localhost HammerDB-5.0]# ./hammerdbcli 
HammerDB CLI v5.0
Copyright © HammerDB Ltd hosted by tpc.org 2019-2025
Type "help" for a list of commands
Initialized Jobs on-disk database /tmp/hammer.DB using existing tables (69,632 bytes)
hammerdb>librarycheck
Checking database library for Oracle
Error: failed to load Oratcl - can't read "env(ORACLE_HOME)": no such variable
Ensure that Oracle client libraries are installed and the location in the LD_LIBRARY_PATH environment variable
Checking database library for MSSQLServer
Error: failed to load tdbc::odbc - couldn't load file "libiodbc.so": libiodbc.so: cannot open shared object file: No such file or directory
Ensure that MSSQLServer client libraries are installed and the location in the LD_LIBRARY_PATH environment variable
Checking database library for Db2
Error: failed to load db2tcl - couldn't load file "/tmp/tcl_xlaoci": libdb2.so.1: cannot open shared object file: No such file or directory
Ensure that Db2 client libraries are installed and the location in the LD_LIBRARY_PATH environment variable
Checking database library for MySQL
Error: failed to load mysqltcl - couldn't load file "/tmp/tcl_Hzfz65": libmysqlclient.so.24: cannot open shared object file: No such file or directory
Ensure that MySQL client libraries are installed and the location in the LD_LIBRARY_PATH environment variable
Checking database library for PostgreSQL
Success ... loaded library Pgtcl for PostgreSQL
Checking database library for MariaDB
Success ... loaded library mariatcl for MariaDB

hammerdb>

具体DB2的环境配置如以下步骤：

新建DB2实例

使用db2client中的命令创建一个db2实例，如实例的路径为/db2data/inst01/userhome

修改.bash_profile

在.bash_profile中添加以下内容：

# The following three lines have been added by IBM DB2 instance utilities.
if [ -f /db2data/inst01/userhome/sqllib/db2profile ]; then
    . /db2data/inst01/userhome/sqllib/db2profile
fi

修改完成后，重新登录会话，使用env命令查看是否有以下内容：

[root@localhost ~]# env | grep LD_
LD_LIBRARY_PATH=/db2data/inst01/userhome/sqllib/lib64:/db2data/inst01/userhome/sqllib/lib64/gskit:/db2data/inst01/userhome/sqllib/lib32
[root@localhost ~]#

重新进行librarycheck

[root@localhost HammerDB-5.0]# ./hammerdbcli 
HammerDB CLI v5.0
Copyright © HammerDB Ltd hosted by tpc.org 2019-2025
Type "help" for a list of commands
Initialized Jobs on-disk database /tmp/hammer.DB using existing tables (69,632 bytes)
hammerdb>librarycheck
Checking database library for Oracle
Error: failed to load Oratcl - can't read "env(ORACLE_HOME)": no such variable
Ensure that Oracle client libraries are installed and the location in the LD_LIBRARY_PATH environment variable
Checking database library for MSSQLServer
Error: failed to load tdbc::odbc - couldn't load file "libiodbc.so": libiodbc.so: cannot open shared object file: No such file or directory
Ensure that MSSQLServer client libraries are installed and the location in the LD_LIBRARY_PATH environment variable
Checking database library for Db2
Success ... loaded library db2tcl for Db2
Checking database library for MySQL
Error: failed to load mysqltcl - couldn't load file "/tmp/tcl_Wm6I4Y": libmysqlclient.so.24: cannot open shared object file: No such file or directory
Ensure that MySQL client libraries are installed and the location in the LD_LIBRARY_PATH environment variable
Checking database library for PostgreSQL
Success ... loaded library Pgtcl for PostgreSQL
Checking database library for MariaDB
Success ... loaded library mariatcl for MariaDB

当提示Checking database library for Db2即表示db2的环境配置成功。

2.准备DB2测试库

Server端建立一个测试的数据库

db2 create database testdb using codeset utf-8 territory cn pagesize 4096

测试库的连接信息如下：

IP：192.168.1.62
Port：51100
DB_name：testdb
User：hammerdb
Password：my_passwd

建立check_temp_w的表空间

如果不建立此临时表空间，则checkschema命令会失败。

create user temporary tablespace check_temp_w_a managed by AUTOMATIC storage;

其他优化配置
- 建立一个大于4K的表空间，并赋权给上述用户（待定，不一定必须）
- 修改数据库的活动日志的容量
添加数据库编目

在hammerdb的机器上的db2实例中catalog上述测试DB，并测试连通性即可

db2 catalog tcpip node TDB62_11 remote 10.110.175.62 port 51100
db2 catalog db tpcc at node TDB62_11

3.进行TPCC的基准测试

3.1.配置测试库的信息

使用hammerdbcli命令进入命令窗口，按以下步骤添加测试库的信息：

设置数据库类型

使用dbset db命令设置压测数据库的类型，print db查看设置的结果

hammerdb>dbset db db2
Database set to Db2

hammerdb>print db
Database Db2 set.
To change do: dbset db prefix, one of:
Oracle = ora MSSQLServer = mssqls Db2 = db2 MySQL = mysql PostgreSQL = pg MariaDB = maria

设置基准测试类型

使用dbset bm命令设置压测数据库的类型，print bm查看设置的结果

hammerdb>dbset bm TPC-C
Benchmark set to TPC-C for Db2

hammerdb>print bm
Benchmark set to TPC-C

配置负载信息

使用print dict可以查看到全部的配置信息。

hammerdb>print dict
Dictionary Settings for Db2
connection {
 db2_def_user  = inst01
 db2_def_pass  = ibmdb2
 db2_def_dbase = db2
}
tpcc       {
 db2_count_ware       = 1
 db2_num_vu           = 1
 db2_user             = db2inst1
 db2_pass             = ibmdb2
 db2_dbase            = tpcc
 db2_def_tab          = USERSPACE1
 db2_tab_list         = C "" D "" H "" I "" W "" S "" NO "" OR "" OL ""
 db2_partition        = false
 db2_total_iterations = 10000000
 db2_raiseerror       = false
 db2_keyandthink      = false
 db2_driver           = timed
 db2_rampup           = 2
 db2_duration         = 5
 db2_monreport        = 0
 db2_allwarehouse     = false
 db2_timeprofile      = false
 db2_async_scale      = false
 db2_async_client     = 10
 db2_async_verbose    = false
 db2_async_delay      = 1000
 db2_connect_pool     = false
}

按上述内容配置必须的参数，设置数据库连接及负载的设置使用diset命令：

hammerdb>diset connection db2_def_user hammerdb
Changed connection:db2_def_user from inst01 to hammerdb for Db2

hammerdb>diset connection db2_def_pass Ghac.cn@123
Changed connection:db2_def_pass from ibmdb2 to Ghac.cn@123 for Db2

hammerdb>diset connection db2_def_dbase testdb
Changed connection:db2_def_dbase from db2 to testdb for Db2

hammerdb>diset tpcc db2_user hammerdb
Changed tpcc:db2_user from db2inst1 to hammerdb for Db2

hammerdb>diset tpcc db2_pass Ghac.cn@123
Changed tpcc:db2_pass from ibmdb2 to Ghac.cn@123 for Db2

hammerdb>diset tpcc db2_dbase testdb
Changed tpcc:db2_dbase from tpcc to testdb for Db2

hammerdb>diset tpcc db2_partition true
Changed tpcc:db2_partition from false to true for Db2

hammerdb>diset tpcc db2_count_ware 5
Changed tpcc:db2_count_ware from 1 to 5 for Db2

hammerdb>diset tpcc db2_num_vu 5
Changed tpcc:db2_num_vu from 1 to 5 for Db2

配置完成后再使用print dict命令查看修改后的结果，已经修改完成。

hammerdb>print dict
Dictionary Settings for Db2
connection {
 db2_def_user  = hammerdb
 db2_def_pass  = Ghac.cn@123
 db2_def_dbase = testdb
}
tpcc       {
 db2_count_ware       = 5
 db2_num_vu           = 5
 db2_user             = hammerdb
 db2_pass             = Ghac.cn@123
 db2_dbase            = testdb
 db2_def_tab          = USERSPACE1
 db2_tab_list         = C "" D "" H "" I "" W "" S "" NO "" OR "" OL ""
 db2_partition        = true
 db2_total_iterations = 10000000
 db2_raiseerror       = false
 db2_keyandthink      = false
 db2_driver           = timed
 db2_rampup           = 2
 db2_duration         = 5
 db2_monreport        = 0
 db2_allwarehouse     = false
 db2_timeprofile      = false
 db2_async_scale      = false
 db2_async_client     = 10
 db2_async_verbose    = false
 db2_async_delay      = 1000
 db2_connect_pool     = false
}

💡 提示：其他参数请参考官网中的说明进行设置

3.2.测试前的数据清理

hammerdb在生成数据时，必须清理指定schema中以下全部对象（如果之前有加载过测试数据的）：

tables
views
dataType

3.3.加载测试数据(buildschema)

执行加载之前，先要确定要加载多少个仓库的数据，及确定执行的虚拟用户数据，如以下的设置，这里创建了10个仓库的数据，用户也使用10个用户，但hammerdb在执行时会多生成一个用户用于监视进程的。

hammerdb>diset tpcc db2_count_ware 5
Changed tpcc:db2_count_ware from 1 to 5 for Db2

hammerdb>diset tpcc db2_num_vu 5
Changed tpcc:db2_num_vu from 1 to 5 for Db2

再使用bulidschema命令开始执行加载测试数据命令，hammerdb会根据print dict中的配置加载测试数据，如下：

hammerdb>buildschema
Script cleared
Building 5 Warehouses with 6 Virtual Users, 5 active + 1 Monitor VU(dict value db2_num_vu is set to 5)
Ready to create a 5 Warehouse Db2 TPROC-C schema
under user HAMMERDB in database TESTDB?
Enter yes or no: replied yes
Vuser 1 created - WAIT IDLE
Vuser 2 created - WAIT IDLE
Vuser 3 created - WAIT IDLE
Vuser 4 created - WAIT IDLE
Vuser 5 created - WAIT IDLE
Vuser 6 created - WAIT IDLE
Vuser 1:RUNNING
Vuser 1:Monitor Thread
Vuser 1:CREATING DATABASE testdb
Vuser 1:DATABASE testdb already exists
Vuser 1:Connecting to database testdb
Vuser 2:RUNNING
Vuser 2:Worker Thread
Vuser 2:Waiting for Monitor Thread...
Vuser 3:RUNNING
Vuser 3:Worker Thread
Vuser 3:Waiting for Monitor Thread...
Vuser 4:RUNNING
Vuser 4:Worker Thread
...(略)
Vuser 3:FINISHED SUCCESS
Vuser 5:...3000
Vuser 5:Orders Done
Vuser 5:End:Tue Jul 15 09:49:12 CST 2025
Vuser 5:FINISHED SUCCESS
Vuser 1:Loading Items - 40000
Vuser 1:Loading Items - 50000
Vuser 1:Loading Items - 60000
Vuser 1:Loading Items - 70000
Vuser 1:Loading Items - 80000
Vuser 1:Loading Items - 90000
Vuser 1:Loading Items - 100000
Vuser 1:Item done
Vuser 1:Monitoring Workers...
Vuser 1:Connecting to database testdb
Vuser 1:Connection established
Vuser 1:CREATING TPCC INDEXES
Vuser 1:CREATING TPCC STORED PROCEDURES
Vuser 1:CREATING Db2 GLOBAL VARIABLES
Vuser 1:GATHERING SCHEMA STATISTICS
Vuser 1:1..
Vuser 1:2..
Vuser 1:3..
Vuser 1:4..
Vuser 1:5..
Vuser 1:1..
Vuser 1:2..
Vuser 1:3..
Vuser 1:4..
Vuser 1:Statistics Complete
Vuser 1:HAMMERDB SCHEMA COMPLETE
Vuser 1:FINISHED SUCCESS
ALL VIRTUAL USERS COMPLETE
Schema Build jobid=6875B308F31503E213739373

3.4.检查数据（checkschema）

在进行负载测试时，需要确定已加载的数据是否可用，这里要使用checkschema命令。

💡 提示：如果执行时会提示“Error: Cannot check schema with Virtual Users active, destroy Virtual Users first”，则需要使用vudestroy命令将上一步骤中未关闭的虚拟用户关闭掉。

hammerdb>checkschema
Script cleared
Checking schema with 1 Virtual User
Do you want to check the TESTDB Db2 TPROC-C schema
under user HAMMERDB?
Enter yes or no: replied yes
Vuser 1 created - WAIT IDLE
Vuser 1:RUNNING
Vuser 1:Checking testdb TPROC-C schema
Vuser 1:Connecting to database testdb
Vuser 1:Connection established
Vuser 1:Check database
Vuser 1:Check tables and indices
Vuser 1:Check procedures
Vuser 1:Check consistency 1
Vuser 1:Check consistency 2
Vuser 1:Check consistency 3
Vuser 1:Check consistency 4
Vuser 1:testdb TPROC-C Schema has been checked successfully.
Vuser 1:FINISHED SUCCESS
ALL VIRTUAL USERS COMPLETE
Schema Check jobid=6875B565172A03E223238313

3.5.运行负载(vurun)

创建虚拟用户

需要确定所需要的并发用户数（vu）,使用以下命令进行设置：

hammerdb>print vucreated
0 Virtual Users created

hammerdb>vuset vu 10

hammerdb>print vuconf
Virtual Users = 10
User Delay(ms) = 500
Repeat Delay(ms) = 500
Iterations = 1
Show Output = 1
Log Output = 0
Unique Log Name = 0
No Log Buffer = 0
Log Timestamps = 0

再使用vucreate命令创建用户，并查看用户的状态：

hammerdb>vucreate
Script loaded, Type "print script" to view
Vuser 1 created MONITOR - WAIT IDLE
Vuser 2 created - WAIT IDLE
Vuser 3 created - WAIT IDLE
Vuser 4 created - WAIT IDLE
Vuser 5 created - WAIT IDLE
Vuser 6 created - WAIT IDLE
Vuser 7 created - WAIT IDLE
Vuser 8 created - WAIT IDLE
Vuser 9 created - WAIT IDLE
Vuser 10 created - WAIT IDLE
Vuser 11 created - WAIT IDLE
11 Virtual Users Created with Monitor VU

hammerdb>vustatus
1  = WAIT IDLE
2  = WAIT IDLE
3  = WAIT IDLE
4  = WAIT IDLE
5  = WAIT IDLE
6  = WAIT IDLE
7  = WAIT IDLE
8  = WAIT IDLE
9  = WAIT IDLE
10 = WAIT IDLE
11 = WAIT IDLE

运行负载

使用vurun命令开始运行负载：

hammerdb>vurun
Vuser 1:RUNNING
Vuser 1:Connecting to database testdb
Vuser 2:RUNNING
Vuser 2:Connecting to database testdb
Vuser 3:RUNNING
Vuser 3:Connecting to database testdb
Vuser 4:RUNNING
Vuser 4:Connecting to database testdb
Vuser 5:RUNNING
Vuser 5:Connecting to database testdb
Vuser 2:Connection established
Vuser 2:Processing 10000000 transactions with output suppressed...
Vuser 1:Connection established
Vuser 5:Connection established
Vuser 3:Connection established
Vuser 4:Connection established
Vuser 1:DBVersion:11.5.7.0
Vuser 1:Beginning rampup time of 2 minutes
Vuser 5:Processing 10000000 transactions with output suppressed...
Vuser 4:Processing 10000000 transactions with output suppressed...
Vuser 3:Processing 10000000 transactions with output suppressed...
Vuser 6:RUNNING
Vuser 6:Connecting to database testdb
Vuser 6:Connection established
Vuser 6:Processing 10000000 transactions with output suppressed...
Vuser 7:RUNNING
Vuser 7:Connecting to database testdb
Vuser 7:Connection established
Vuser 7:Processing 10000000 transactions with output suppressed...
Vuser 8:RUNNING
Vuser 8:Connecting to database testdb
Vuser 8:Connection established
Vuser 8:Processing 10000000 transactions with output suppressed...
Vuser 9:RUNNING
Vuser 9:Connecting to database testdb
Vuser 9:Connection established
Vuser 9:Processing 10000000 transactions with output suppressed...
Vuser 10:RUNNING
Vuser 10:Connecting to database testdb
Vuser 10:Connection established
Vuser 10:Processing 10000000 transactions with output suppressed...
Vuser 11:RUNNING
Vuser 11:Connecting to database testdb
Vuser 11:Connection established
Vuser 11:Processing 10000000 transactions with output suppressed...
Vuser 1:Rampup 1 minutes complete ...
Vuser 1:Rampup 2 minutes complete ...
Vuser 1:Rampup complete, Taking start Transaction Count.
Vuser 1:1 ...,
Vuser 1:2 ...,
Vuser 1:3 ...,
Vuser 1:4 ...,
Vuser 1:5 ...,
Vuser 1:Test complete, Taking end Transaction Count.
Vuser 1:10 Active Virtual Users configured
Vuser 1:TEST RESULT : System achieved 29726 NOPM from 130770 Db2 TPM
Vuser 1:FINISHED SUCCESS
Vuser 7:FINISHED SUCCESS
Vuser 6:FINISHED SUCCESS
Vuser 10:FINISHED SUCCESS
Vuser 11:FINISHED SUCCESS
Vuser 8:FINISHED SUCCESS
Vuser 4:FINISHED SUCCESS
Vuser 5:FINISHED SUCCESS
Vuser 2:FINISHED SUCCESS
Vuser 9:FINISHED SUCCESS
Vuser 3:FINISHED SUCCESS
ALL VIRTUAL USERS COMPLETE
Benchmark Run jobid=6875BAF96C4803E213933353

查看运行结果

上述vurun中的内容简单列出了hammerdb的运行结果，本次压测的结果为130770 TPM。

Vuser 1:TEST RESULT : System achieved 29726 NOPM from 130770 Db2 TPM

4.总结说明

上述是使用hammerdbcli对一个DB2数据库的测试的完整过程，本文仅介绍command的方式执行数据库基准测试的过程，为什么不使用GUI的方式，因为生产环境使用堡垒机，很难调用出GUI的界面。针对大库的测试数据生成，或压力测试运行，后台执行更为合适一点。

上述仅稍展示过程，后续再阐述批量压测的过程。

posted @ 2025-07-15 16:31 潇雨锁清秋阅读(194) 评论(0) 收藏举报

刷新页面返回顶部