数据库基准测试3：HammerDB使用入门（for Oracle）

1.前期准备

  • 安装好HammerDB（参考其他文档）；
  • 在压测机上安装好Oracle Client；（参考其他文档）
  • 准备好一个可用于压力测试的oracle数据库；

2.librarycheck

使用hammerdbcli命令进入命令模式，再输入librarycheck命令查看结果是否有Success ... loaded library Oratcl for Oracle的提示，如果提示not found则安装对应的包即可，结果如以下：

hammerdb>librarycheck
Checking database library for Oracle
Success ... loaded library Oratcl for Oracle
Checking database library for MSSQLServer
Success ... loaded library tdbc::odbc for MSSQLServer
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_n3TbP2": 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

oracle的连接配置不像DB那么麻烦，后续步骤进行说明。

2.准备Oracle测试库

  • 目标测试数据库

所准备的oracle数据库的连接信息如下：

IP：192.168.1.62
Port：1521
SID：testdb
User：hammerdb
Password：my_passwd
  • 建立表空间

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

CREATE TABLESPACE tpcc
DATAFILE '/oracle/app/oradata/aic/tpcc01.dbf' 
SIZE 1G
AUTOEXTEND ON NEXT 1G
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;

3.进行TPCC的基准测试

3.1.配置测试库的信息

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

  • 设置数据库类型

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

hammerdb>dbset db ora
Database set to Oracle

hammerdb>print db
Database Oracle 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 Oracle

hammerdb>print bm
Benchmark set to TPC-C
  • 配置负载信息

使用print dict可以查看到全部的配置信息（以下是配置好后的参数）。

hammerdb>print dict
Dictionary Settings for Oracle
connection {
 system_user     = hammerdb
 system_password = my_passwd
 instance        = 192.168.1.62:1521/testdb
 rac             = 0
}
tpcc       {
 count_ware       = 10
 num_vu           = 5
 tpcc_user        = hammerdb
 tpcc_pass        = my_passwd
 tpcc_def_tab     = tpcc
 tpcc_ol_tab      = tpcc
 tpcc_def_temp    = temp
 partition        = true
 hash_clusters    = false
 tpcc_tt_compat   = false
 total_iterations = 10000000
 raiseerror       = false
 keyandthink      = false
 checkpoint       = false
 ora_driver       = timed
 rampup           = 2
 duration         = 5
 allwarehouse     = false
 ora_timeprofile  = false
 async_scale      = false
 async_client     = 10
 async_verbose    = false
 async_delay      = 1000
 connect_pool     = false
}

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

# 连接数据库的用户名
diset connection system_user hammerdb
# 数据库的密码
diset connection system_password my_passwd
# 连接地址
diset connection instance 192.168.1.62:1521/testdb
# 也是数据库的连接用户名
diset tpcc tpcc_user hammerdb
# 密码
diset tpcc tpcc_pass my_passwd
# 灌的压测仓库数量
diset tpcc count_ware 10
# 创建的虚拟用试
diset tpcc num_vu 5
# 以下为指定的表空间及临时表空间
diset tpcc tpcc_def_tab tpcc
diset tpcc tpcc_ol_tab tpcc
diset tpcc tpcc_def_temp temp
# 开启分区表的特性
diset tpcc partition true

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

目前存在两个问题：（1）tpcc_def_tab无论如何设置，始终是使用默认的users表空间；（2）如果是使用脚本的方式执行buildschema，需要在脚本中设置 rampup、duration这两个参数，当vu数量较大时，否则无法在buildschema时无法创建monitor thread。

3.2.测试前的数据清理

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

  • tables
  • views

3.3.加载测试数据(buildschema)

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

hammerdb>diset tpcc count_ware 5

hammerdb>diset tpcc num_vu 5

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

hammerdb>buildschema
Script cleared
Building 10 Warehouses with 6 Virtual Users, 5 active + 1 Monitor VU(dict value num_vu is set to 5)
Ready to create a 10 Warehouse Oracle TPROC-C schema
in database 192.168.1.62:1521/TESTDB under user HAMMERDB in tablespace TPCC?
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 DMPMON SCHEMA
Vuser 1:CREATING USER hammerdb
Vuser 1:ORA-01920: user name 'HAMMERDB' conflicts with another user or role name create user hammerdb identified by hammerdb default tablespace tpcc temporary tablespace temp
Vuser 1:CREATING TPCC TABLES
Vuser 1:Loading Item
Vuser 2:RUNNING
Vuser 2:Worker Thread
Vuser 2:Waiting for Monitor Thread...
Vuser 2:Loading 2 Warehouses start:1 end:2
Vuser 2:Start:Thu Oct 30 15:31:04 CST 2025
Vuser 2:Loading Warehouse
Vuser 2:Loading Stock Wid=1
Vuser 3:RUNNING
(略)
Vuser 4:Orders Done
Vuser 4:End:Thu Oct 30 15:32:31 CST 2025
Vuser 4:FINISHED SUCCESS
Vuser 5:Orders Done
Vuser 5:End:Thu Oct 30 15:32:31 CST 2025
Vuser 5:FINISHED SUCCESS
Vuser 6:Orders Done
Vuser 6:End:Thu Oct 30 15:32:31 CST 2025
Vuser 6:FINISHED SUCCESS
Vuser 1:Loading Items - 100000
Vuser 1:Item done
Vuser 1:Monitoring Workers...
Vuser 1:CREATING TPCC INDEXES
Vuser 1:CREATING TPCC STORED PROCEDURES
Vuser 1:GATHERING SCHEMA STATISTICS
Vuser 1:DMPMON SCHEMA COMPLETE
Vuser 1:FINISHED SUCCESS
ALL VIRTUAL USERS COMPLETE
Schema Build jobid=690314373B3D03E293332353

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 HAMMERDB Oracle TPROC-C schema
in the instance 10.110.175.65:1521/AIC?
Enter yes or no: replied yes
Vuser 1 created - WAIT IDLE
Vuser 1:RUNNING
Vuser 1:Checking hammerdb TPROC-C schema
Vuser 1:Check schema
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:hammerdb TPROC-C schema has been checked successfully
Vuser 1:FINISHED SUCCESS
ALL VIRTUAL USERS COMPLETE
Schema Check jobid=6903155C4CB903E263339363

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:DBVersion:11.2.0.4.0
Vuser 1:Beginning rampup time of 2 minutes
Vuser 2:RUNNING
Vuser 2:Processing 10000000 transactions with output suppressed...
Vuser 3:RUNNING
Vuser 3:Processing 10000000 transactions with output suppressed...
Vuser 4:RUNNING
Vuser 4:Processing 10000000 transactions with output suppressed...
Vuser 5:RUNNING
Vuser 5:Processing 10000000 transactions with output suppressed...
Vuser 6:RUNNING
Vuser 6:Processing 10000000 transactions with output suppressed...
Vuser 7:RUNNING
Vuser 7:Processing 10000000 transactions with output suppressed...
Vuser 8:RUNNING
Vuser 8:Processing 10000000 transactions with output suppressed...
Vuser 9:RUNNING
Vuser 9:Processing 10000000 transactions with output suppressed...
Vuser 10:RUNNING
Vuser 10:Processing 10000000 transactions with output suppressed...
Vuser 11:RUNNING
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 AWR snapshot.
Vuser 1:Start Snapshot 1228 taken at 30 OCT 2025 15:40 of instance aic (1) of database AIC (78078981)
Vuser 1:Timing test period of 5 in minutes
Vuser 1:1 ...,
Vuser 1:2 ...,
Vuser 1:3 ...,
Vuser 1:4 ...,
Vuser 1:5 ...,
Vuser 1:Test complete, Taking end AWR snapshot.
Vuser 1:End Snapshot 1229 taken at 30 OCT 2025 15:45 of instance aic (1) of database AIC (78078981)
Vuser 1:Test complete: view report from SNAPID 1228 to 1229
Vuser 1:10 Active Virtual Users configured
Vuser 1:TEST RESULT : System achieved 57798 NOPM from 122181 Oracle TPM
Vuser 4:FINISHED SUCCESS
Vuser 1:FINISHED SUCCESS
Vuser 6:FINISHED SUCCESS
Vuser 11:FINISHED SUCCESS
Vuser 10:FINISHED SUCCESS
Vuser 7:FINISHED SUCCESS
Vuser 5:FINISHED SUCCESS
Vuser 9:FINISHED SUCCESS
Vuser 2:FINISHED SUCCESS
Vuser 3:FINISHED SUCCESS
Vuser 8:FINISHED SUCCESS
ALL VIRTUAL USERS COMPLETE
Benchmark Run jobid=69031610576D03E233039333
  • 查看运行结果

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

Vuser 1:TEST RESULT : System achieved 57798 NOPM from 122181 Oracle TPM

4.总结说明

上述是使用hammerdbcli对一个Oracel数据库的测试的完整过程，本文仅介绍command的方式执行数据库基准测试的过程，至于使用HammerDB中自带的tcl脚本去执行压测，可以参考我之前的文章进行修改。与DB2的压测相比，Oracle在连接数据库方面会更加简单一点，过程类似，只是某些参数的名称不一致而已。

posted @ 2025-10-30 16:08  潇雨锁清秋  阅读(8)  评论(0)    收藏  举报