BenchmarkSQL is a JDBC-based database benchmarking tool that incorporates TPC-C test scripts and supports multiple databases including PostgreSQL, Oracle, and MySQL. This guide covers installation, configuration, and execution of TPC-C benchmarks using BenchmarkSQL on CentOS 7.

1. Prerequisites

• Operating System: CentOS 7
• Java: BenchmarkSQL is written in Java; ensure JDK is installed
• Database: Tested with PostgreSQL and MySQL
• Ant: For compiling BenchmarkSQL
• EPEL repository: For additional packages
• R Language: For generating graphical reports
• Required dependencies for the above software

2. Installation Steps

2.1 Install Apache Ant

yum -y install ant

2.2 Add EPEL Repository

su -c 'rpm -Uvh https://download.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm'
yum -y update

2.3 Install R Language

yum -y install R

2.4 Install and Build BenchmarkSQL

Download and Extract

wget https://example.com/benchmarksql-5.0.zip  # replace with actual URL
unzip benchmarksql-5.0.zip
cd benchmarksql-5.0

Initial Build

ant

This creates dist/BenchmarkSQL-5.0.jar, but it lacks MySQL support by default. The following modifications enable MySQL TPC-C testing.

Patch Source Code for MySQL Support

File: src/client/jTPCC.java Add MySQL database type handling:

if (iDB.equals("firebird"))
    dbType = DB_FIREBIRD;
else if (iDB.equals("oracle"))
    dbType = DB_ORACLE;
else if (iDB.equals("postgres"))
    dbType = DB_POSTGRES;
else if (iDB.equals("mysql"))
    dbType = DB_UNKNOWN;
else {
    log.error("unknown database type '" + iDB + "'");
    return;
}

File: src/client/jTPCCConnection.java Modify SQL subquery to include alias AS L:

default:
  stmtStockLevelSelectLow = dbConn.prepareStatement(
      "SELECT count(*) AS low_stock FROM (" +
      "    SELECT s_w_id, s_i_id, s_quantity " +
      "        FROM bmsql_stock " +
      "        WHERE s_w_id = ? AND s_quantity < ? AND s_i_id IN (" +
      "            SELECT ol_i_id " +
      "                FROM bmsql_district " +
      "                JOIN bmsql_order_line ON ol_w_id = d_w_id " +
      "                 AND ol_d_id = d_id " +
      "                 AND ol_o_id >= d_next_o_id - 20 " +
      "                 AND ol_o_id < d_next_o_id " +
      "                WHERE d_w_id = ? AND d_id = ? " +
      "        ) " +
      "    )AS L");
  break;

Rebuild

ant

The resulting dist/BenchmarkSQL-5.0.jar now supports MySQL.

Configure Scripts for MySQL

File: run/funcs.sh

• Add MySQL to the setCP() function:

function setCP()
{
  case "$(getProp db)" in
  firebird)
    cp="../lib/firebird/*:../lib/*"
    ;;
  oracle)
    cp="../lib/oracle/*"
    if [ ! -z "${ORACLE_HOME}" -a -d ${ORACLE_HOME}/lib ] ; then
      cp="${cp}:${ORACLE_HOME}/lib/*"
    fi
    cp="${cp}:../lib/*"
    ;;
  postgres)
    cp="../lib/postgres/*:../lib/*"
    ;;
  mysql)
    cp="../lib/mysql/*:../lib/*"
    ;;
  esac
  myCP=".:${cp}:../dist/*"
  export myCP
}

• Update database type validation:

case "$(getProp db)" in
  firebird|oracle|postgres|mysql)
  ;;
  "") echo "ERROR: missing db= config option in ${PROPS}" >&2
  exit 1
  ;;
  *)  echo "ERROR: unsupported database type 'db=$(getProp db)' in ${PROPS}" >&2
  exit 1
  ;;
esac

Add MySQL JDBC Driver

mkdir -p lib/mysql
# Download appropriate driver (e.g., mysql-connector-java-8.0.18.jar)
cp mysql-connector-java-8.0.18.jar lib/mysql/

File: run/runDatabaseBuild.sh Remove extraHistID from the load sequence:

# Original
AFTER_LOAD="indexCreates foreignKeys extraHistID buildFinish"
# Modified
AFTER_LOAD="indexCreates foreignKeys buildFinish"

2.5 Configuration Parameters

Copy and edit configuration files:

cd run
cp props.pg postgres.properties
cp props.pg mysql.properties

Key configuration options:

• db: Database type (postgres, mysql)
• driver: JDBC driver class (e.g., com.mysql.cj.jdbc.Driver for MySQL 8+)
• conn: JDBC connection URL (adjust host, port, database name)
• user: Database user (suggested: benchmarksql)
• password: User password (suggested: PWbmsql)
• warehouses: Number of warehouses (determines data volume; adjust based on memory)
• loadWorkers: Number of threads for data loading (default 4)
• terminals: Number of concurrent virtual users (typically 2-6x CPU threads)
• runTxnsPerTerminal: Transactions per terminal (set runMins=0 if using this)
• runMins: Test duration in minutes (set runTxnsPerTerminal=0 if using this)
• limitTxnsPerMin: Maximum transactions per minute (default 300)
• terminalWarehouseFixed: Bind terminals to warehouses (default true)
• Weights (must sum to 100): newOrderWeight=45, paymentWeight=43, orderStatusWeight=4, deliveryWeight=4, stockLevelWeight=4
• resultDirectory: Output directory template (e.g., my_result_%tY-%tm-%td_%tH%tM%tS)
• osCollectorScript: Path to system performance collector script
• osCollectorInterval: Collection interval in seconds (default 1)
• osCollectorDevices: Network and block devices to monitor (e.g., net_eth0 blk_sda)

2.6 Running the Benchmark

Ensure the database user and database exist with proper permissions before proceeding.

Initialize the test database:

./runDatabaseBuild.sh my_postgres.properties

Run the benchmark:

./runBenchmark.sh my_postgres.properties

Note: For MySQL, use mysql.properties instead of my_postgres.properties.

2.7 Generating Reports

After the test completes, a result directory (e.g., my_result_2018-09-30_133047) will be created under run/. Generate an HTML report with charts using:

./generateReport.sh my_result_2018-09-30_133047

This creates an HTML file that visualizes the benchmark results.