Answer Diff [41]
This page describes our setup for DBT-3 tests. A very cogent resource on the [[http://askmonty.org/wiki/DBT3_benchmarkdbt3-automation-scripts|DBT3 Benchmark]] is also available.
¶
¶
== Get and compile DBT3 ¶
¶
* Get [[http://osdldbt.sourceforge.net/|DBT3]] ¶
* Make sure that you have pg_ctl and createdb of PostgreSQL installed. On ¶
Ubuntu look for postgresql and postgresql-client and make sure that pg_ctl is ¶
in your PATH, for instance: <<code lang=bash inline=false>>PATH=/usr/lib/postgresql/8.4/bin:$PATH<</code>> ¶
* Compile DBT3<<code lang=bash inline=false>>gunzip -c dbt3-1.9.tar.gz | tar xvf - ¶
cd dbt3-1.9 ¶
./configure ¶
make ¶
cd src/dbgen ¶
make ¶
<</code>> ¶
* Generate data:<<code lang=bash inline=false>>./dbgen -s30<</code>> ¶
¶
== DDL ¶
¶
Substitute <<code lang=bash inline=true>>${ENGINE}<</code>> with the storage engine you want to use: ¶
¶
<<code lang=mysql inline=false>> ¶
CREATE TABLE supplier ( ¶
s_suppkey INTEGER PRIMARY KEY, ¶
s_name CHAR(25), ¶
s_address VARCHAR(40), ¶
s_nationkey INTEGER, ¶
s_phone CHAR(15), ¶
s_acctbal REAL, ¶
s_comment VARCHAR(101)) Engine ${ENGINE}; ¶
¶
CREATE TABLE part ( ¶
p_partkey INTEGER PRIMARY KEY, ¶
p_name VARCHAR(55), ¶
p_mfgr CHAR(25), ¶
p_brand CHAR(10), ¶
p_type VARCHAR(25), ¶
p_size INTEGER, ¶
p_container CHAR(10), ¶
p_retailprice REAL, ¶
p_comment VARCHAR(23)) Engine ${ENGINE}; ¶
¶
CREATE TABLE partsupp ( ¶
ps_partkey INTEGER, ¶
ps_suppkey INTEGER, ¶
ps_availqty INTEGER, ¶
ps_supplycost REAL, ¶
ps_comment VARCHAR(199), ¶
PRIMARY KEY (ps_partkey, ps_suppkey)) Engine ${ENGINE}; ¶
¶
CREATE TABLE customer ( ¶
c_custkey INTEGER primary key, ¶
c_name VARCHAR(25), ¶
c_address VARCHAR(40), ¶
c_nationkey INTEGER, ¶
c_phone CHAR(15), ¶
c_acctbal REAL, ¶
c_mktsegment CHAR(10), ¶
c_comment VARCHAR(117)) Engine ${ENGINE}; ¶
¶
CREATE TABLE orders ( ¶
o_orderkey INTEGER primary key, ¶
o_custkey INTEGER, ¶
o_orderstatus CHAR(1), ¶
o_totalprice REAL, ¶
o_orderDATE DATE, ¶
o_orderpriority CHAR(15), ¶
o_clerk CHAR(15), ¶
o_shippriority INTEGER, ¶
o_comment VARCHAR(79)) Engine ${ENGINE}; ¶
¶
CREATE TABLE lineitem ( ¶
l_orderkey INTEGER, ¶
l_partkey INTEGER, ¶
l_suppkey INTEGER, ¶
l_linenumber INTEGER, ¶
l_quantity REAL, ¶
l_extendedprice REAL, ¶
l_discount REAL, ¶
l_tax REAL, ¶
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), ¶
PRIMARY KEY (l_orderkey, l_linenumber)) Engine ${ENGINE}; ¶
¶
CREATE TABLE nation ( ¶
n_nationkey INTEGER primary key, ¶
n_name CHAR(25), ¶
n_regionkey INTEGER, ¶
n_comment VARCHAR(152)) Engine ${ENGINE}; ¶
¶
CREATE TABLE region ( ¶
r_regionkey INTEGER primary key, ¶
r_name CHAR(25), ¶
r_comment VARCHAR(152)) Engine ${ENGINE}; ¶
¶
CREATE TABLE time_statistics ( ¶
task_name VARCHAR(40), ¶
s_time TIMESTAMP, ¶
e_time TIMESTAMP, ¶
int_time INTEGER) Engine ${ENGINE}; ¶
<</code>> ¶
¶
== Load data ¶
¶
Substitute <<code lang=bash inline=true>>${DATA_DIR}<</code>> with the path to your generated data. ¶
¶
<<code lang="bash" inline="false">> ¶
LOAD DATA LOCAL INFILE '${DATA_DIR}/supplier.tbl' into table supplier fields terminated by '|'; ¶
LOAD DATA LOCAL INFILE '${DATA_DIR}/part.tbl' into table part fields terminated by '|'; ¶
LOAD DATA LOCAL INFILE '${DATA_DIR}/partsupp.tbl' into table partsupp fields terminated by '|'; ¶
LOAD DATA LOCAL INFILE '${DATA_DIR}/customer.tbl' into table customer fields terminated by '|'; ¶
LOAD DATA LOCAL INFILE '${DATA_DIR}/orders.tbl' into table orders fields terminated by '|'; ¶
LOAD DATA LOCAL INFILE '${DATA_DIR}/lineitem.tbl' into table lineitem fields terminated by '|'; ¶
LOAD DATA LOCAL INFILE '${DATA_DIR}/nation.tbl' into table nation fields terminated by '|'; ¶
LOAD DATA LOCAL INFILE '${DATA_DIR}/region.tbl' into table region fields terminated by '|'; ¶
<</code>> ¶
¶
== Indexes we need ¶
¶
<<code lang=mysql inline=false>> ¶
ALTER TABLE lineitem ¶
ADD INDEX i_l_shipdate(l_shipdate), ¶
ADD INDEX i_l_suppkey_partkey (l_partkey, l_suppkey), ¶
ADD INDEX i_l_partkey (l_partkey), ¶
ADD INDEX i_l_suppkey (l_suppkey), ¶
ADD INDEX i_l_receiptdate (l_receiptdate), ¶
ADD INDEX i_l_orderkey (l_orderkey), ¶
ADD INDEX i_l_orderkey_quantity (l_orderkey, l_quantity), ¶
ADD INDEX i_l_commitdate (l_commitdate); ¶
¶
CREATE INDEX i_c_nationkey ON customer (c_nationkey); ¶
¶
ALTER TABLE orders ¶
ADD INDEX i_o_orderdate (o_orderdate), ¶
ADD INDEX i_o_custkey (o_custkey); ¶
¶
CREATE INDEX i_s_nationkey ON supplier (s_nationkey); ¶
¶
ALTER TABLE partsupp ¶
ADD INDEX i_ps_partkey (ps_partkey), ¶
ADD INDEX i_ps_suppkey (ps_suppkey); ¶
¶
CREATE INDEX i_n_regionkey ON nation (n_regionkey); ¶
<</code>> ¶
¶
== Analyze tables ¶
¶
<<code lang=mysql inline=false>> ¶
ANALYZE TABLE supplier; ¶
ANALYZE TABLE part; ¶
ANALYZE TABLE partsupp; ¶
ANALYZE TABLE customer; ¶
ANALYZE TABLE orders; ¶
ANALYZE TABLE lineitem; ¶
ANALYZE TABLE nation; ¶
ANALYZE TABLE region; ¶
<</code>> ¶
¶
¶
== Get and compile DBT3 ¶
¶
* Get [[http://osdldbt.sourceforge.net/|DBT3]] ¶
* Make sure that you have pg_ctl and createdb of PostgreSQL installed. On ¶
Ubuntu look for postgresql and postgresql-client and make sure that pg_ctl is ¶
in your PATH, for instance: <<code lang=bash inline=false>>PATH=/usr/lib/postgresql/8.4/bin:$PATH<</code>> ¶
* Compile DBT3<<code lang=bash inline=false>>gunzip -c dbt3-1.9.tar.gz | tar xvf - ¶
cd dbt3-1.9 ¶
./configure ¶
make ¶
cd src/dbgen ¶
make ¶
<</code>> ¶
* Generate data:<<code lang=bash inline=false>>./dbgen -s30<</code>> ¶
¶
== DDL ¶
¶
Substitute <<code lang=bash inline=true>>${ENGINE}<</code>> with the storage engine you want to use: ¶
¶
<<code lang=mysql inline=false>> ¶
CREATE TABLE supplier ( ¶
s_suppkey INTEGER PRIMARY KEY, ¶
s_name CHAR(25), ¶
s_address VARCHAR(40), ¶
s_nationkey INTEGER, ¶
s_phone CHAR(15), ¶
s_acctbal REAL, ¶
s_comment VARCHAR(101)) Engine ${ENGINE}; ¶
¶
CREATE TABLE part ( ¶
p_partkey INTEGER PRIMARY KEY, ¶
p_name VARCHAR(55), ¶
p_mfgr CHAR(25), ¶
p_brand CHAR(10), ¶
p_type VARCHAR(25), ¶
p_size INTEGER, ¶
p_container CHAR(10), ¶
p_retailprice REAL, ¶
p_comment VARCHAR(23)) Engine ${ENGINE}; ¶
¶
CREATE TABLE partsupp ( ¶
ps_partkey INTEGER, ¶
ps_suppkey INTEGER, ¶
ps_availqty INTEGER, ¶
ps_supplycost REAL, ¶
ps_comment VARCHAR(199), ¶
PRIMARY KEY (ps_partkey, ps_suppkey)) Engine ${ENGINE}; ¶
¶
CREATE TABLE customer ( ¶
c_custkey INTEGER primary key, ¶
c_name VARCHAR(25), ¶
c_address VARCHAR(40), ¶
c_nationkey INTEGER, ¶
c_phone CHAR(15), ¶
c_acctbal REAL, ¶
c_mktsegment CHAR(10), ¶
c_comment VARCHAR(117)) Engine ${ENGINE}; ¶
¶
CREATE TABLE orders ( ¶
o_orderkey INTEGER primary key, ¶
o_custkey INTEGER, ¶
o_orderstatus CHAR(1), ¶
o_totalprice REAL, ¶
o_orderDATE DATE, ¶
o_orderpriority CHAR(15), ¶
o_clerk CHAR(15), ¶
o_shippriority INTEGER, ¶
o_comment VARCHAR(79)) Engine ${ENGINE}; ¶
¶
CREATE TABLE lineitem ( ¶
l_orderkey INTEGER, ¶
l_partkey INTEGER, ¶
l_suppkey INTEGER, ¶
l_linenumber INTEGER, ¶
l_quantity REAL, ¶
l_extendedprice REAL, ¶
l_discount REAL, ¶
l_tax REAL, ¶
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), ¶
PRIMARY KEY (l_orderkey, l_linenumber)) Engine ${ENGINE}; ¶
¶
CREATE TABLE nation ( ¶
n_nationkey INTEGER primary key, ¶
n_name CHAR(25), ¶
n_regionkey INTEGER, ¶
n_comment VARCHAR(152)) Engine ${ENGINE}; ¶
¶
CREATE TABLE region ( ¶
r_regionkey INTEGER primary key, ¶
r_name CHAR(25), ¶
r_comment VARCHAR(152)) Engine ${ENGINE}; ¶
¶
CREATE TABLE time_statistics ( ¶
task_name VARCHAR(40), ¶
s_time TIMESTAMP, ¶
e_time TIMESTAMP, ¶
int_time INTEGER) Engine ${ENGINE}; ¶
<</code>> ¶
¶
== Load data ¶
¶
Substitute <<code lang=bash inline=true>>${DATA_DIR}<</code>> with the path to your generated data. ¶
¶
<<code lang="bash" inline="false">> ¶
LOAD DATA LOCAL INFILE '${DATA_DIR}/supplier.tbl' into table supplier fields terminated by '|'; ¶
LOAD DATA LOCAL INFILE '${DATA_DIR}/part.tbl' into table part fields terminated by '|'; ¶
LOAD DATA LOCAL INFILE '${DATA_DIR}/partsupp.tbl' into table partsupp fields terminated by '|'; ¶
LOAD DATA LOCAL INFILE '${DATA_DIR}/customer.tbl' into table customer fields terminated by '|'; ¶
LOAD DATA LOCAL INFILE '${DATA_DIR}/orders.tbl' into table orders fields terminated by '|'; ¶
LOAD DATA LOCAL INFILE '${DATA_DIR}/lineitem.tbl' into table lineitem fields terminated by '|'; ¶
LOAD DATA LOCAL INFILE '${DATA_DIR}/nation.tbl' into table nation fields terminated by '|'; ¶
LOAD DATA LOCAL INFILE '${DATA_DIR}/region.tbl' into table region fields terminated by '|'; ¶
<</code>> ¶
¶
== Indexes we need ¶
¶
<<code lang=mysql inline=false>> ¶
ALTER TABLE lineitem ¶
ADD INDEX i_l_shipdate(l_shipdate), ¶
ADD INDEX i_l_suppkey_partkey (l_partkey, l_suppkey), ¶
ADD INDEX i_l_partkey (l_partkey), ¶
ADD INDEX i_l_suppkey (l_suppkey), ¶
ADD INDEX i_l_receiptdate (l_receiptdate), ¶
ADD INDEX i_l_orderkey (l_orderkey), ¶
ADD INDEX i_l_orderkey_quantity (l_orderkey, l_quantity), ¶
ADD INDEX i_l_commitdate (l_commitdate); ¶
¶
CREATE INDEX i_c_nationkey ON customer (c_nationkey); ¶
¶
ALTER TABLE orders ¶
ADD INDEX i_o_orderdate (o_orderdate), ¶
ADD INDEX i_o_custkey (o_custkey); ¶
¶
CREATE INDEX i_s_nationkey ON supplier (s_nationkey); ¶
¶
ALTER TABLE partsupp ¶
ADD INDEX i_ps_partkey (ps_partkey), ¶
ADD INDEX i_ps_suppkey (ps_suppkey); ¶
¶
CREATE INDEX i_n_regionkey ON nation (n_regionkey); ¶
<</code>> ¶
¶
== Analyze tables ¶
¶
<<code lang=mysql inline=false>> ¶
ANALYZE TABLE supplier; ¶
ANALYZE TABLE part; ¶
ANALYZE TABLE partsupp; ¶
ANALYZE TABLE customer; ¶
ANALYZE TABLE orders; ¶
ANALYZE TABLE lineitem; ¶
ANALYZE TABLE nation; ¶
ANALYZE TABLE region; ¶
<</code>> ¶
