Benchmarking Druid

benchmark-druid.sh

# ./benchmark-druid.R <host> [datasource] [outputname] [count]
./benchmark-druid.R <compute_node> tpch_lineitem_small results/druid-m3-2xlarge-1x.tsv
./benchmark-druid.R <compute_node> tpch_lineitem results/100gb-druid-m3-2xlarge-1x.tsv 20
./benchmark-druid.R <broker_node> tpch_lineitem results/100gb-druid-m3-2xlarge-6x.tsv 20

benchmark-mysql.sh

# ./benchmark-mysql.sh <host> <user> <password> [query] [count]
./benchmark-mysql.sh $host $user $password > results/mysql-m3-2xlarge-ssd-myisam.tsv
./benchmark-mysql.sh $host $user $password 5 > results/100gb-mysql-m3-2xlarge-ssd-myisam.tsv

download-data.sh

# 1GB data set
curl -O http://static.druid.io/data/benchmarks/tpch/1/lineitem.tbl.gz

# 100GB data set
for i in $(seq 1 100) ; do curl -O http://static.druid.io/data/benchmarks/tpch/100/lineitem.tbl.$i.gz ; done

load-druid.sh

# 1GB data set
./load-druid.sh <indexingservice>:<port> lineitem_small.task.json

# 100GB data set
./load-druid.sh <indexingservice>:<port> lineitem.task.json

load-mysql.sh

host="<mysqlhost>"; user="me"; password="secret"
# 1GB
mysql --local-infile --host $host --user $user --password=$password -Dtpch < load-mysql-myisam.sql
# 100GB
mysql --local-infile --host $host --user $user --password=$password -Dtpch < load-mysql-myisam-100.sql

queries.sql

-- count_star_interval
SELECT COUNT(*) FROM LINEITEM WHERE L_SHIPDATE BETWEEN '1992-01-03' AND '1998-11-30';

-- sum_price
SELECT SUM(L_EXTENDEDPRICE) FROM LINEITEM;

-- sum_all
SELECT SUM(L_EXTENDEDPRICE), SUM(L_DISCOUNT), SUM(L_TAX), SUM(L_QUANTITY) FROM LINEITEM;

-- sum_all_year
SELECT YEAR(L_SHIPDATE), SUM(L_EXTENDEDPRICE), SUM(L_DISCOUNT), SUM(L_TAX), SUM(L_QUANTITY)
  FROM LINEITEM GROUP BY YEAR(L_SHIPDATE);

-- sum_all_filter
SELECT SUM(L_EXTENDEDPRICE), SUM(L_DISCOUNT), SUM(L_TAX), SUM(L_QUANTITY)
  FROM LINEITEM WHERE L_SHIPMODE LIKE '%AIR%';

-- top_100_parts
SELECT L_PARTKEY, SUM(L_QUANTITY) FROM LINEITEM
  GROUP BY L_PARTKEY ORDER BY SUM(L_QUANTITY) DESC LIMIT 100;

-- top_100_parts_details
SELECT L_PARTKEY, SUM(L_QUANTITY), SUM(L_EXTENDEDPRICE), MIN(L_DISCOUNT), MAX(L_DISCOUNT)
  FROM LINEITEM GROUP BY L_PARTKEY ORDER BY SUM(L_QUANTITY) DESC LIMIT 100;

-- top_100_parts_filter
SELECT L_PARTKEY, SUM(L_QUANTITY), SUM(L_EXTENDEDPRICE), MIN(L_DISCOUNT), MAX(L_DISCOUNT)
  FROM LINEITEM WHERE L_SHIPDATE BETWEEN '1996-01-15' AND '1998-03-15'
  GROUP BY L_PARTKEY ORDER BY SUM(L_QUANTITY) DESC LIMIT 100;

-- top_100_commitdate
SELECT L_COMMITDATE, SUM(L_QUANTITY) FROM LINEITEM
  GROUP BY L_COMMITDATE ORDER BY SUM(L_QUANTITY) DESC LIMIT 100;

table-structure.sql

CREATE TABLE LINEITEM (
  L_ORDERKEY      INTEGER NOT NULL,
  L_PARTKEY       INTEGER NOT NULL,
  L_SUPPKEY       INTEGER NOT NULL,
  L_LINENUMBER    INTEGER NOT NULL,
  L_QUANTITY      FLOAT NOT NULL,
  L_EXTENDEDPRICE FLOAT NOT NULL,
  L_DISCOUNT      FLOAT NOT NULL,
  L_TAX           FLOAT NOT NULL,
  L_RETURNFLAG    CHAR(1) NOT NULL,
  L_LINESTATUS    CHAR(1) NOT NULL,
  L_SHIPDATE      DATE NOT NULL,
  L_COMMITDATE    DATE NOT NULL,
  L_RECEIPTDATE   DATE NOT NULL,
  L_SHIPINSTRUCT  CHAR(25) NOT NULL,
  L_SHIPMODE      CHAR(10) NOT NULL,
  L_COMMENT       VARCHAR(44) NOT NULL
);