关于数据库的压力测试,之前写过3篇Blog:
数据库基准测试(Database Benchmarking) 说明
数据库压力测试工具 -- Hammerdb 使用说明
数据库压力测试工具 -- Swingbench 使用说明
今天看下BenchmarkSQL 工具的使用。
1BenchmarkSQL 说明
BenchmarkSQL 可以从sourceforge上直接下载:
对于BenchmarkSQL,官方的解释如下:
An easy to use JDBC benchmark that closely resembles the TPC-C standard for OLTP. DB's supported include PostgreSQL/EnterpriseDB, DB2, Oracle, SQLSvr, MySQL.
简单点说,就是一个通过JDBC 测试OLTP 的TPC-C。 支持PostgreSQL/EnterpriseDB, DB2, Oracle, SQLSvr, MySQL.
在安装文件中中,有一个HOW TO RUN的说明文档,里面描述了具体的使用方法:
Instructions for running
------------------------
Use of JDK7 is required. Sample JDBC Connection Property files are provided as follows:
props.pg : for PostgreSQL/EnterpriseDB
props.ora : for Oracle
0. As the user postgres, create the benchmarksql user with correct permissions.
postgres=# CREATE USER benchmarksql WITH SUPERUSER PASSWORD 'password';
postgres=# GRANT ALL PRIVILEGES ON DATABASE postgres TO benchmarksql;
1. Go to the 'run' directory, edit the appropriate "props.???"
file to point to the database instance you'd like to test.
2. Run the "sqlTableCreates" to create the base tables.
$ ./runSQL.sh props.pg sqlTableCreateshttps://www.cndba.cn/dave/article/115
3. Run the Loader command file to load all of the default data for a benchmark:
A.) Approximately half a million rows (per Warehouse) will be loaded across 9 tables.
$ ./runLoader.sh props.pg numWarehouses 1
NOTE: You should run the sqlTableTruncates scripts if your tables are not already empty.
B.) Alternatively, for PostgreSQL you may choose to generate the load data out to CSV files where it can be efficiently
bulk loaded into the database as many times as required by your testing.
$ ./runLoader.sh props.pg numWarehouses 1 fileLocation /tmp/csv/
These CSV files can be bulk loaded as follows:
$ ./runSQL.sh props.pg sqlTableCopies
You may truncate the data via:
$ ./runSQL.sh props.pg sqlTableTruncates
4. Run the "runSQL" command file to execute the SQL script "sqlIndexCreates" to create the primary keys & other indexes on the tables.https://www.cndba.cn/dave/article/115
$ ./runSQL.sh props.pg sqlIndexCreates
5. Run the "runBenchmark" command file to test the database. This command will create terminals and automatically start the transaction based on the parameters set in "props".
$ ./runBenchmark.sh props.pg
2BenchmarkSQL 使用示例
上节复制的how to run 文本内容有具体的使用方法,我们这里已Oracle 数据库为例,记录测试方法。https://www.cndba.cn/dave/article/115
2.1 安装BenchmarkSQL
下载后直接解压缩:
[root@cndba.cn ~]# unzip benchmarksql-4.1.0.zip
[root@cndba.cn benchmarksql-4.1.0]# pwd
/root/benchmarksql-4.1.0
[root@cndba.cn benchmarksql-4.1.0]# ls
build build.xml dist HOW-TO-RUN.txt lib README.txt run src
[root@cndba.cn benchmarksql-4.1.0]#
修改目录权限:
[root@cndba.cn ~]# chown -R oracle:oinstall benchmarksql-4.1.0
2.2 修改变量信息
进入run目录,修改数据库实例的信息:
[root@cndba.cn run]# pwd
/root/benchmarksql-4.1.0/run
[root@cndba.cn run]# ls
clean.sh log4j.xml props.pg runLoader.sh sqlIndexCreates sqlTableCopies sqlTableDrops
log props.ora runBenchmark.sh runSQL.sh sqlIndexDrops sqlTableCreates sqlTableTruncates
[root@cndba.cn run]#
我们测试的是Oracle 数据库,对应的文件是props.ora。 如果是postgresql,那么对应的文件是:props.pg。
创建测试用户benchmarksql:
SQL> create user benchmarksql identified by benchmarksql;
User created.
SQL> grant connect,resource,dba to benchmarksql;
Grant succeeded.
修改props.ora文件:
[oracle@cndba.cn run]$ cat props.ora
driver=oracle.jdbc.driver.OracleDriver
conn=jdbc:oracle:thin:@localhost:1521:cndba
user=benchmarksql
password=benchmarksql
warehouses=1
terminals=1
//To run specified transactions per terminal- runMins must equal zero
runTxnsPerTerminal=10
//To run for specified minutes- runTxnsPerTerminal must equal zero
runMins=0
//Number of total transactions per minute
limitTxnsPerMin=300
//The following five values must add up to 100
newOrderWeight=45
paymentWeight=43
orderStatusWeight=4
deliveryWeight=4
stockLevelWeight=4
2.3 创建基表
还在run命令下runSQL.sh 加sqlTableCreates选项来创建基表。
在运行之前,还需要先修改一下runSQL.sh 脚本,修改jar包类型,BenchmarkSQL 默认只有postgresql 的jar包。
[oracle@cndba.cn lib]$ pwd
/u01/app/oracle/product/11.1.0/db_1/jdbc/lib
[oracle@cndba.cn lib]$ ls
ojdbc6dms_g.jar ojdbc6_g.jar ojdbc7dms_g.jar ojdbc7_g.jar simplefan.jar
ojdbc6dms.jar ojdbc6.jar ojdbc7dms.jar ojdbc7.jar
把Oracle 目录下的jar包复制到BenchMarkSQL 的lib下。
[oracle@cndba.cn lib]$ cp ojdbc6.jar /u01/benchmarksql-4.1.0/lib/
[oracle@ cndba.cn lib]$
修改runSQL.sh:
https:/