benchmarksql在postgresql上的安装、使用

1.创建用户和数据库[postgres#localhost ~] $ psql postgrespsql (9.5.2)Type "help" for help.postgres=# CREATE USER benchmarksql WITH ENCRYPTED PASSWORD 'changeme';postgres=# CREATE DATABASE benchmarksql OWNER benchmarksql;postgres=# \q2.解压[postgres@localhost run]$unzip benchmarksql-5.0.zip [postgres@localhost ~] $ cd benchmarksql-5.0[postgres@localhost benchmarksql-5.0]$ antbash: ant: command not found...解决方法：安装ant[root@localhost etc]# yum install ant[postgres@localhost benchmarksql-5.0]$ antBuildfile: /tmp/benchmarksql-5.0/build.xmlinit:    [mkdir] Created dir: /tmp/benchmarksql-5.0/buildcompile:    [javac] Compiling 11 source files to /tmp/benchmarksql-5.0/builddist:    [mkdir] Created dir: /tmp/benchmarksql-5.0/dist      [jar] Building jar: /tmp/benchmarksql-5.0/dist/BenchmarkSQL-5.0.jarBUILD SUCCESSFULTotal time: 2 seconds3.修改文件[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 LoadDatadriver=org.postgresql.jdbc.Driverconn=jdbc:postgresql://localhost:5866/benchmarksqluser=benchmarksqlpassword=***********warehouses=1loadWorkers=4fileLocation (not defined)csvNullValue (not defined - using default 'NULL')Worker 000: Loading ITEMWorker 001: Loading Warehouse      1Worker 000: Loading ITEM doneWorker 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 ExecJDBCError: 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 ExecJDBCError: 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]$ antBuildfile: build.xml does not exist!Build failed4.运行配置[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.properties6.生成图表用run目录下的generateReport.sh脚本去执行压力测试得到的结果目录 my_result_xxx如：[postgres@localhost run]$ ./generateReport.sh my_result_2017-04-18_111400/执行的时候是需要提前安装R语言环境的。7.R语言环境的安装解压./configuremake make过程中可能出现报错，几乎就是缺少对应的安装程序，用yum安装就好 。如X11 则要安装yum  install yum install libX* 总之，缺什么就安装什么就好make install安装完成后进入R环境[postgres@localhost run]$ RR version 3.0.0 (2013-04-03) -- "Masked Marvel"Copyright (C) 2013 The R Foundation for Statistical ComputingPlatform: 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 localeR 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 ... OKGenerating my_result_2017-04-18_132736//latency.png ... OKGenerating my_result_2017-04-18_132736//cpu_utilization.png ... OKGenerating my_result_2017-04-18_132736//dirty_buffers.png ... OKGenerating my_result_2017-04-18_132736//blk_sda_iops.png ... OKGenerating my_result_2017-04-18_132736//blk_sda_kbps.png ... OKGenerating my_result_2017-04-18_132736//net_enp3s0f0_iops.png ... OKGenerating my_result_2017-04-18_132736//net_enp3s0f0_kbps.png ... OKGenerating my_result_2017-04-18_132736//report.html ... OK  就可以看到目录下生成的图片和网页了，根据里面的内容来分析数据。[postgres@localhost my_result_2017-04-18_132736]$ lsblk_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_111724Generating my_result_2017-04-18_111724/tpm_nopm.png ... Error in .External2(C_X11, paste("png::", filename, sep = ""), g$width,  :   unable to start device PNGCalls: pngIn addition: Warning message:In png("tpm_nopm.png", width = 1200, height = 400) :  unable to open connection to X11 display ''Execution haltedERRORR version 3.0.0 (2013-04-03) -- "Masked Marvel"Copyright (C) 2013 The R Foundation for Statistical ComputingPlatform: 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 localeR 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 directorygrep: data/tx_summary.csv: No such file or directorygrep: data/tx_summary.csv: No such file or directorygrep: data/tx_summary.csv: No such file or directoryOK因为你的R语言环境没有按照好，导致出现这中问题，建议安装好对应的包后，重新编译安装R语言。