mysql benchmark sql_BenchmarkSQL安装使用介绍

1、安装配置

下载地址：http://sourceforge.net/projects/benchmarksql/?source=navbar

Required：JDK7

PostgreSQL Version：9.3.4

解压：

[postgres@localhost ~]$ unzip benchmarksql-4.1.0.zip

保留配置备份：

[postgres@localhost ~]$ cd benchmarksql-4.1.0/run/

[postgres@localhost run]$ cp props.pg props.pg.bak

修改数据库连接配置：

[postgres@localhost run]$ vi props.pg

driver=org.postgresql.Driver

conn=jdbc:postgresql://localhost:5432/benchmarksql

user=benchmarksql

password=password

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

//The default percentages of 45, 43, 4, 4 & 4 match the TPC-C spec

newOrderWeight=45

paymentWeight=43

orderStatusWeight=4

deliveryWeight=4

stockLevelWeight=4

创建测试库：

postgres=# create user benchmarksql with superuser password 'password';

CREATE ROLE

postgres=# create database benchmarksql owner benchmarksql;

CREATE DATABASE

创建测试表结构：

[postgres@localhost run]$ ./runSQL.sh props.pg sqlTableCreates

查看测试表：

[postgres@localhost run]$ psql benchmarksql benchmarksql

psql (9.3.4)

Type "help" for help.

benchmarksql=# \d

List of relations

Schema    |    Name     |   Type   |    Owner

--------------+-------------+----------+--------------

benchmarksql | customer    | table    | benchmarksql

benchmarksql | district    | table    | benchmarksql

benchmarksql | hist_id_seq | sequence | benchmarksql

benchmarksql | history     | table    | benchmarksql

benchmarksql | item        | table    | benchmarksql

benchmarksql | new_order   | table    | benchmarksql

benchmarksql | oorder      | table    | benchmarksql

benchmarksql | order_line  | table    | benchmarksql

benchmarksql | stock       | table    | benchmarksql

benchmarksql | warehouse   | table    | benchmarksql

(10 rows)

导入测试数据：

[postgres@localhost run]$ ./runLoader.sh props.pg numWarehouses 1

查看表：

benchmarksql=# \d+

List of relations

Schema    |    Name     |   Type   |    Owner     |    Size    | Description

--------------+-------------+----------+--------------+------------+-------------

benchmarksql | customer    | table    | benchmarksql | 18 MB      |

benchmarksql | district    | table    | benchmarksql | 8192 bytes |

benchmarksql | hist_id_seq | sequence | benchmarksql | 8192 bytes |

benchmarksql | history     | table    | benchmarksql | 2552 kB    |

benchmarksql | item        | table    | benchmarksql | 10192 kB   |

benchmarksql | new_order   | table    | benchmarksql | 416 kB     |

benchmarksql | oorder      | table    | benchmarksql | 2024 kB    |

benchmarksql | order_line  | table    | benchmarksql | 30 MB      |

benchmarksql | stock       | table    | benchmarksql | 34 MB      |

benchmarksql | warehouse   | table    | benchmarksql | 8192 bytes |

(10 rows)

benchmarksql=# select count(*) from customer ;

count

-------

30000

(1 row)

benchmarksql=# select * from warehouse;

w_id |   w_ytd   | w_tax  | w_name |     w_street_1      |  w_street_2  |    w_city    | w_state |   w_zip

------+-----------+--------+--------+---------------------+--------------+--------------+---------+-----------

1 | 300000.00 | 0.0445 | RfHUW  | HldcTUzsMXqRYwvseXe | TgpQzWZsRMCc | WgCgqczSIpzF | DT      | 123456789

(1 row)

为测试表创建索引：

[postgres@localhost run]$ ./runSQL.sh props.pg sqlIndexCreates

查看索引：

benchmarksql=# \di

List of relations

Schema    |        Name        | Type  |    Owner     |   Table

--------------+--------------------+-------+--------------+------------

benchmarksql | history_pkey       | index | benchmarksql | history

benchmarksql | ndx_customer_name  | index | benchmarksql | customer

benchmarksql | ndx_oorder_carrier | index | benchmarksql | oorder

benchmarksql | pk_customer        | index | benchmarksql | customer

benchmarksql | pk_district        | index | benchmarksql | district

benchmarksql | pk_item            | index | benchmarksql | item

benchmarksql | pk_new_order       | index | benchmarksql | new_order

benchmarksql | pk_oorder          | index | benchmarksql | oorder

benchmarksql | pk_order_line      | index | benchmarksql | order_line

benchmarksql | pk_stock           | index | benchmarksql | stock

benchmarksql | pk_warehouse       | index | benchmarksql | warehouse

(11 rows)

至此配置完成。

若需要重新导入数据，则无需删除表结构，只需在导入前执行：

./runSQL.sh props.pg sqlTableTruncates

2、测试

[postgres@localhost run]$ ./runBenchmark.sh props.pg

2014-05-13 06:58:56,125  INFO - Term-00,

2014-05-13 06:58:56,129  INFO - Term-00, +-------------------------------------------------------------+

2014-05-13 06:58:56,129  INFO - Term-00,      BenchmarkSQL v4.1

2014-05-13 06:58:56,129  INFO - Term-00, +-------------------------------------------------------------+

2014-05-13 06:58:56,129  INFO - Term-00,  (c) 2003, Raul Barbosa

2014-05-13 06:58:56,130  INFO - Term-00,  (c) 2004-2014, Denis Lussier

2014-05-13 06:58:56,132  INFO - Term-00, +-------------------------------------------------------------+

2014-05-13 06:58:56,133  INFO - Term-00,

2014-05-13 06:58:56,134  INFO - Term-00, driver=org.postgresql.Driver

2014-05-13 06:58:56,135  INFO - Term-00, conn=jdbc:postgresql://localhost:5432/benchmarksql

2014-05-13 06:58:56,136  INFO - Term-00, user=benchmarksql

2014-05-13 06:58:56,137  INFO - Term-00,

2014-05-13 06:58:56,138  INFO - Term-00, warehouses=1

2014-05-13 06:58:56,138  INFO - Term-00, terminals=1

2014-05-13 06:58:56,140  INFO - Term-00, runTxnsPerTerminal=10

2014-05-13 06:58:56,141  INFO - Term-00, limitTxnsPerMin=300

2014-05-13 06:58:56,141  INFO - Term-00,

2014-05-13 06:58:56,142  INFO - Term-00, newOrderWeight=45

2014-05-13 06:58:56,143  INFO - Term-00, paymentWeight=43

2014-05-13 06:58:56,144  INFO - Term-00, orderStatusWeight=4

2014-05-13 06:58:56,145  INFO - Term-00, deliveryWeight=4

2014-05-13 06:58:56,146  INFO - Term-00, stockLevelWeight=4

2014-05-13 06:58:56,146  INFO - Term-00,

2014-05-13 06:58:58,480  INFO - Term-00,     Current tpmTOTAL: 12    Memory Usage: 6MB / 15MB

2014-05-13 06:58:58,481  INFO - Term-00,

2014-05-13 06:58:58,481  INFO - Term-00, Measured tpmC (NewOrders) = 86.16

2014-05-13 06:58:58,482  INFO - Term-00, Measured tpmTOTAL = 315.94

2014-05-13 06:58:58,485  INFO - Term-00, Session Start     = 2014-05-13 06:58:56

2014-05-13 06:58:58,485  INFO - Term-00, Session End       = 2014-05-13 06:58:58

2014-05-13 06:58:58,487  INFO - Term-00, Transaction Count = 10

改变测试策略只需在执行测试前修改props.pg即可。