benchmarksql在postgresql上的安装、使用

1.创建用户和数据库
[postgres#localhost ~] $ psql postgres
psql (9.5.2)
Type "help" for help.

postgres=# CREATE USER benchmarksql WITH ENCRYPTED PASSWORD 'changeme';
postgres=# CREATE DATABASE benchmarksql OWNER benchmarksql;
postgres=# \q
2.解压
[postgres@localhost run]$unzip benchmarksql-5.0.zip 
[postgres@localhost ~] $ cd benchmarksql-5.0
[postgres@localhost benchmarksql-5.0]$ ant
bash: ant: command not found...
解决方法：
安装ant
[root@localhost etc]# yum install ant
[postgres@localhost benchmarksql-5.0]$ ant
Buildfile: /tmp/benchmarksql-5.0/build.xml

init:
    [mkdir] Created dir: /tmp/benchmarksql-5.0/build

compile:
    [javac] Compiling 11 source files to /tmp/benchmarksql-5.0/build

dist:
    [mkdir] Created dir: /tmp/benchmarksql-5.0/dist
      [jar] Building jar: /tmp/benchmarksql-5.0/dist/BenchmarkSQL-5.0.jar

BUILD SUCCESSFUL
Total time: 2 seconds
3.修改文件
[postgres@localhost benchmarksql] $ cd run
[postgres@localhost run] $ cp props.pg my_postgres.properties
[postgres@localhost run] $ vi my_postgres.properties
[postgres@localhost run] $
4.构建模式和初始数据库加载
[postgres@localhost run]$ ./runDatabaseBuild.sh my_postgres.properties
# ------------------------------------------------------------
# Loading SQL file ./sql.common/tableCreates.sql
# ------------------------------------------------------------
create table bmsql_config (
cfg_name    varchar(30) primary key,
cfg_value   varchar(50)
);

Starting BenchmarkSQL LoadData

driver=org.postgresql.jdbc.Driver
conn=jdbc:postgresql://localhost:5866/benchmarksql
user=benchmarksql
password=***********
warehouses=1
loadWorkers=4
fileLocation (not defined)
csvNullValue (not defined - using default 'NULL')

Worker 000: Loading ITEM
Worker 001: Loading Warehouse      1
Worker 000: Loading ITEM done
Worker 001: Loading Warehouse      1 done
# ------------------------------------------------------------
# Loading SQL file ./sql.common/indexCreates.sql
# ------------------------------------------------------------

# ------------------------------------------------------------
# Loading SQL file ./sql.postgres/extraHistID.sql
# ------------------------------------------------------------
-- ----

# ------------------------------------------------------------
# Loading SQL file ./sql.postgres/buildFinish.sql
# ------------------------------------------------------------
-- ----
-- Extra commands to run after the tables are created, loaded,
-- indexes built and extra's created.
-- PostgreSQL version.
-- ----
vacuum analyze;
如果这一步出现这样的错误说明你没有安装ant，先安装ant
[postgres@localhost run]$ ./runDatabaseBuild.sh my_postgres.properties
# ------------------------------------------------------------
# Loading SQL file ./sql.common/tableCreates.sql
# ------------------------------------------------------------
Error: Could not find or load main class ExecJDBC
Error: Could not find or load main class LoadData
# ------------------------------------------------------------
# Loading SQL file ./sql.common/indexCreates.sql
# ------------------------------------------------------------
Error: Could not find or load main class ExecJDBC
# ------------------------------------------------------------
# Loading SQL file ./sql.common/foreignKeys.sql
# ------------------------------------------------------------
Error: Could not find or load main class ExecJDBC
# ------------------------------------------------------------
# Loading SQL file ./sql.postgres/extraHistID.sql
# ------------------------------------------------------------
Error: Could not find or load main class ExecJDBC
# ------------------------------------------------------------
# Loading SQL file ./sql.postgres/buildFinish.sql
# ------------------------------------------------------------
Error: Could not find or load main class ExecJDBC
[postgres@localhost run]$ ./runDatabaseBuild.sh my_postgres.properties
# ------------------------------------------------------------
# Loading SQL file ./sql.common/tableCreates.sql
# ------------------------------------------------------------
Error: Could not find or load main class ExecJDBC
Error: Could not find or load main class LoadData
# ------------------------------------------------------------
# Loading SQL file ./sql.common/indexCreates.sql
# ------------------------------------------------------------
Error: Could not find or load main class ExecJDBC
# ------------------------------------------------------------
# Loading SQL file ./sql.common/foreignKeys.sql
# ------------------------------------------------------------
Error: Could not find or load main class ExecJDBC
# ------------------------------------------------------------
# Loading SQL file ./sql.postgres/extraHistID.sql
# ------------------------------------------------------------
Error: Could not find or load main class ExecJDBC
# ------------------------------------------------------------
# Loading SQL file ./sql.postgres/buildFinish.sql
# ------------------------------------------------------------
Error: Could not find or load main class ExecJDBC
[postgres@localhost run]$ ant
Buildfile: build.xml does not exist!
Build failed
4.运行配置
[postgres@localhost run]$ ./runBenchmark.sh my_postgres.properties
 The benchmark should run for the number of configured concurrent
    connections (terminals) and the duration or number of transactions.

    The end result of the benchmark will be reported like this:

    01:58:09,081 [Thread-1] INFO   jTPCC : Term-00,
    01:58:09,082 [Thread-1] INFO   jTPCC : Term-00, Measured tpmC (NewOrders) = 179.55
    01:58:09,082 [Thread-1] INFO   jTPCC : Term-00, Measured tpmTOTAL = 329.17
    01:58:09,082 [Thread-1] INFO   jTPCC : Term-00, Session Start     = 2016-05-25 01:58:07
    01:58:09,082 [Thread-1] INFO   jTPCC : Term-00, Session End       = 2016-05-25 01:58:09
    01:58:09,082 [Thread-1] INFO   jTPCC : Term-00, Transaction Count = 10
到这一步已经安装完成了，只要修改my_postgres.properties的参数就可以进行压力测试了
5.重建运行数据库的方法（如果你修改了配置文件中的warehouse或者load的值都需要重建数据库）
[postgres@localhost run]$ ./runDatabaseDestroy.sh my_postgres.properties    
[postgres@localhost run]$ ./runDatabaseBuild.sh my_postgres.properties
6.生成图表
用run目录下的generateReport.sh脚本去执行压力测试得到的结果目录 my_result_xxx
如：[postgres@localhost run]$ ./generateReport.sh my_result_2017-04-18_111400/
执行的时候是需要提前安装R语言环境的。
7.R语言环境的安装
解压
./configure
make 
make过程中可能出现报错，几乎就是缺少对应的安装程序，用yum安装就好 。如X11 则要安装yum  install yum install libX* 
总之，缺什么就安装什么就好
make install
安装完成后
进入R环境
[postgres@localhost run]$ R

R version 3.0.0 (2013-04-03) -- "Masked Marvel"
Copyright (C) 2013 The R Foundation for Statistical Computing
Platform: x86_64-unknown-linux-gnu (64-bit)

R is free software and comes with ABSOLUTELY NO WARRANTY.
You are welcome to redistribute it under certain conditions.
Type 'license()' or 'licence()' for distribution details.

  Natural language support but running in an English locale

R is a collaborative project with many contributors.
Type 'contributors()' for more information and
'citation()' on how to cite R or R packages in publications.

Type 'demo()' for some demos, 'help()' for on-line help, or
'help.start()' for an HTML browser interface to help.
Type 'q()' to quit R.

> capabilities()
    jpeg      png     tiff    tcltk      X11     aqua http/ftp  sockets 
    TRUE     TRUE     TRUE    FALSE    FALSE    FALSE     TRUE     TRUE 
  libxml     fifo   cledit    iconv      NLS  profmem    cairo 
    TRUE     TRUE     TRUE     TRUE     TRUE    FALSE     TRUE 
> 
如果像上述显示的结果 png 为true x11位true就可以了
执行文件生成图表数据
[postgres@localhost run]$ ./generateReport.sh my_result_2017-04-18_132736/
Generating my_result_2017-04-18_132736//tpm_nopm.png ... OK
Generating my_result_2017-04-18_132736//latency.png ... OK
Generating my_result_2017-04-18_132736//cpu_utilization.png ... OK
Generating my_result_2017-04-18_132736//dirty_buffers.png ... OK
Generating my_result_2017-04-18_132736//blk_sda_iops.png ... OK
Generating my_result_2017-04-18_132736//blk_sda_kbps.png ... OK
Generating my_result_2017-04-18_132736//net_enp3s0f0_iops.png ... OK
Generating my_result_2017-04-18_132736//net_enp3s0f0_kbps.png ... OK
Generating my_result_2017-04-18_132736//report.html ... OK  
就可以看到目录下生成的图片和网页了，根据里面的内容来分析数据。
[postgres@localhost my_result_2017-04-18_132736]$ ls
blk_sda_iops.png  blk_sda_kbps.png  cpu_utilization.png  data  dirty_buffers.png  latency.png  net_enp3s0f0_iops.png  net_enp3s0f0_kbps.png  report.html  run.properties  tpm_nopm.png
执行生成文件出现以下错误
[postgres@localhost run]$ ./generateReport.sh my_result_2017-04-18_111724
Generating my_result_2017-04-18_111724/tpm_nopm.png ... Error in .External2(C_X11, paste("png::", filename, sep = ""), g$width,  : 
  unable to start device PNG
Calls: png
In addition: Warning message:
In png("tpm_nopm.png", width = 1200, height = 400) :
  unable to open connection to X11 display ''
Execution halted
ERROR

R version 3.0.0 (2013-04-03) -- "Masked Marvel"
Copyright (C) 2013 The R Foundation for Statistical Computing
Platform: x86_64-unknown-linux-gnu (64-bit)

R is free software and comes with ABSOLUTELY NO WARRANTY.
You are welcome to redistribute it under certain conditions.
Type 'license()' or 'licence()' for distribution details.

  Natural language support but running in an English locale

R is a collaborative project with many contributors.
Type 'contributors()' for more information and
'citation()' on how to cite R or R packages in publications.

Type 'demo()' for some demos, 'help()' for on-line help, or
'help.start()' for an HTML browser interface to help.
Type 'q()' to quit R.

> # ----
> # R graph to show tpmC and tpmTOTAL.
> # ----
> 
> # ----
> # Read the runInfo.csv file.
> # ----
> runInfo <- read.csv("data/runInfo.csv", head=TRUE)
> 
> # ----
> # Determine the grouping interval in seconds based on the
> # run duration.
> # ----
> xmax <- runInfo$runMins
> for (interval in c(1, 2, 5, 10, 20, 60, 120, 300, 600)) {
+     if ((xmax * 60) / interval <= 1000) {
+         break
+     }
+ }
> idiv <- interval * 1000.0
> 
> # ----
> # Read the result.csv and then filter the raw data
> # for != DELIVERY_BG and == NEW_ORDER transactions.
> # ----
> data1 <- read.csv("data/result.csv", head=TRUE)
> total1 <- data1[data1$ttype != 'DELIVERY_BG', ]
> neworder1 <- data1[data1$ttype == 'NEW_ORDER', ]
> 
> # ----
> # Aggregate the counts of both data sets grouped by second.
> # ----
> countTotal <- setNames(aggregate(total1$latency, list(elapsed=trunc(total1$elapsed / idiv) * idiv), NROW),
+ 		   c('elapsed', 'count'));
> countNewOrder <- setNames(aggregate(neworder1$latency, list(elapsed=trunc(neworder1$elapsed / idiv) * idiv), NROW),
+ 		   c('elapsed', 'count'));
> 
> # ----
> # Determine the ymax by increasing in sqrt(2) steps until the
> # maximum of tpmTOTAL fits, then make sure that we have at least
> # 1.2 times that to give a little head room for the legend.
> # ----
> ymax_count <- max(countTotal$count) * 60.0 / interval
> ymax <- 1
> sqrt2 <- sqrt(2.0)
> while (ymax < ymax_count) {
+     ymax <- ymax * sqrt2
+ }
> if (ymax < (ymax_count * 1.2)) {
+     ymax <- ymax * 1.2
+ }
> 
> 
> 
> # ----
> # Start the output image.
> # ----
> png("tpm_nopm.png", width=1200, height=400)
Generating my_result_2017-04-18_111724/report.html ... ./generateReport.sh: line 161: data/tx_summary.csv: No such file or directory
grep: data/tx_summary.csv: No such file or directory
grep: data/tx_summary.csv: No such file or directory
grep: data/tx_summary.csv: No such file or directory
OK
因为你的R语言环境没有按照好，导致出现这中问题，建议安装好对应的包后，重新编译安装R语言。