database workload simulation hammerdb tpc - ghdrako/doc_snipets GitHub Wiki

supporting Microsoft SQL Server, IBM Db2, Oracle, PostgreSQL, MariaDB and MySQL on-premis and cloud

HammerDB has graphical and command line interfaces for the Windows and Linux operating systems.

HammerDB supports 2 workloads derived from TPC specifications to test these different requirements, namely TPROC-C derived from TPC-C for OLTP and TPROC-H derived from TPC-H for OLAP.

By default HammerDB implements a cached vs a scaled workload but can implement both types of benchmark

Docker

https://www.hammerdb.com/blog/uncategorized/how-to-deploy-hammerdb-cli-fast-with-docker/

hammerdb@REDPOLL:~$docker pull tpcorg/hammerdb
hammerdb@REDPOLL:~$ sudo docker run --network=host -it --name hammerdb tpcorg/hammerdb bash
## inside container
root@REDPOLL:/home/hammerdb/HammerDB-4.6#
root@REDPOLL:/home/hammerdb/HammerDB-4.6# ./hammerdbcli

Usage

https://www.hammerdb.com/blog/uncategorized/hammerdb-cli-101/

Postgres

https://www.hammerdb.com/blog/uncategorized/how-to-analyze-postgresql-benchmark-performance-with-hammerdb/ HammerDB PostgreSQL metrics is based on a superb tool called pgSentinel that brings active session history functionality to PostgreSQL. An active session history allows us to look back at a database workload and see the workload being run over time.

TPC

https://docs.verdictdb.org/documentation/step_by_step_tutorial/tpch_load_data/

$ cd /home/username/workspace
$ curl http://dbgroup-internal.eecs.umich.edu/projects/verdictdb/tpch1g.zip -o tpch1g.zip
$ unzip tpch1g.zip
$ psql
postgres=# create schema tpch1g;
postgres=# set search_path to tpch1g;

-- nation
CREATE TABLE IF NOT EXISTS "nation" (
  "n_nationkey"  INT,
  "n_name"       CHAR(25),
  "n_regionkey"  INT,
  "n_comment"    VARCHAR(152),
  "n_dummy"      VARCHAR(10),
  PRIMARY KEY ("n_nationkey"));

-- region
CREATE TABLE IF NOT EXISTS "region" (
  "r_regionkey"  INT,
  "r_name"       CHAR(25),
  "r_comment"    VARCHAR(152),
  "r_dummy"      VARCHAR(10),
  PRIMARY KEY ("r_regionkey"));

-- supplier
CREATE TABLE IF NOT EXISTS "supplier" (
  "s_suppkey"     INT,
  "s_name"        CHAR(25),
  "s_address"     VARCHAR(40),
  "s_nationkey"   INT,
  "s_phone"       CHAR(15),
  "s_acctbal"     DECIMAL(15,2),
  "s_comment"     VARCHAR(101),
  "s_dummy"       VARCHAR(10),
  PRIMARY KEY ("s_suppkey"));

-- customer
CREATE TABLE IF NOT EXISTS "customer" (
  "c_custkey"     INT,
  "c_name"        VARCHAR(25),
  "c_address"     VARCHAR(40),
  "c_nationkey"   INT,
  "c_phone"       CHAR(15),
  "c_acctbal"     DECIMAL(15,2),
  "c_mktsegment"  CHAR(10),
  "c_comment"     VARCHAR(117),
  "c_dummy"       VARCHAR(10),
  PRIMARY KEY ("c_custkey"));

-- part
CREATE TABLE IF NOT EXISTS "part" (
  "p_partkey"     INT,
  "p_name"        VARCHAR(55),
  "p_mfgr"        CHAR(25),
  "p_brand"       CHAR(10),
  "p_type"        VARCHAR(25),
  "p_size"        INT,
  "p_container"   CHAR(10),
  "p_retailprice" DECIMAL(15,2) ,
  "p_comment"     VARCHAR(23) ,
  "p_dummy"       VARCHAR(10),
  PRIMARY KEY ("p_partkey"));

-- partsupp
CREATE TABLE IF NOT EXISTS "partsupp" (
  "ps_partkey"     INT,
  "ps_suppkey"     INT,
  "ps_availqty"    INT,
  "ps_supplycost"  DECIMAL(15,2),
  "ps_comment"     VARCHAR(199),
  "ps_dummy"       VARCHAR(10),
  PRIMARY KEY ("ps_partkey"));

-- orders
CREATE TABLE IF NOT EXISTS "orders" (
  "o_orderkey"       INT,
  "o_custkey"        INT,
  "o_orderstatus"    CHAR(1),
  "o_totalprice"     DECIMAL(15,2),
  "o_orderdate"      DATE,
  "o_orderpriority"  CHAR(15),
  "o_clerk"          CHAR(15),
  "o_shippriority"   INT,
  "o_comment"        VARCHAR(79),
  "o_dummy"          VARCHAR(10),
  PRIMARY KEY ("o_orderkey"));

-- lineitem
CREATE TABLE IF NOT EXISTS "lineitem"(
  "l_orderkey"          INT,
  "l_partkey"           INT,
  "l_suppkey"           INT,
  "l_linenumber"        INT,
  "l_quantity"          DECIMAL(15,2),
  "l_extendedprice"     DECIMAL(15,2),
  "l_discount"          DECIMAL(15,2),
  "l_tax"               DECIMAL(15,2),
  "l_returnflag"        CHAR(1),
  "l_linestatus"        CHAR(1),
  "l_shipdate"          DATE,
  "l_commitdate"        DATE,
  "l_receiptdate"       DATE,
  "l_shipinstruct"      CHAR(25),
  "l_shipmode"          CHAR(10),
  "l_comment"           VARCHAR(44),
  "l_dummy"             VARCHAR(10));

\copy "region"     from '/home/username/workspace/tpch1g/region/region.tbl'        DELIMITER '|' CSV;
\copy "nation"     from '/home/username/workspace/tpch1g/nation/nation.tbl'        DELIMITER '|' CSV;
\copy "customer"   from '/home/username/workspace/tpch1g/customer/customer.tbl'    DELIMITER '|' CSV;
\copy "supplier"   from '/home/username/workspace/tpch1g/supplier/supplier.tbl'    DELIMITER '|' CSV;
\copy "part"       from '/home/username/workspace/tpch1g/part/part.tbl'            DELIMITER '|' CSV;
\copy "partsupp"   from '/home/username/workspace/tpch1g/partsupp/partsupp.tbl'    DELIMITER '|' CSV;
\copy "orders"     from '/home/username/workspace/tpch1g/orders/orders.tbl'        DELIMITER '|' CSV;
\copy "lineitem"   from '/home/username/workspace/tpch1g/lineitem/lineitem.tbl'    DELIMITER '|' CSV;