Start Schema Benchmark

Star Schema Benchmark is variation of TPC-H with star-schema database design.

• Benchmark specification
• Files (external link)

In this run I use scale factor 1000, which gives about 610GB of raw text files with data

InfoBright version is 3.2.0-ICE

InfoBright query Q3.1 finished in 36 hours with error, that query can not be executed by InfoBright optimizer.

InfiniDB version 0.9.5-alpha

InfiniDB vmstat 5

procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu------
 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st
 3 13   4980 112264 202040 3699992    0    0 128614     2 1324 19552  9  1 56 34  0
 0 13   4980 112148 202048 3699988    0    0 130253     4 1330 20318  8  1 53 37  0
 0 13   4980 112172 202048 3699996    0    0 128614     0 1344 20604  9  1 51 39  0
 1 15   4980 111968 202056 3699996    0    0 126157     2 1322 19618  8  1 60 31  0
 1 15   4980 111820 202064 3699996    0    0 125338     2 1338 17428  8  1 67 24  0
 0 12   4980 111892 202072 3699996    0    0 131072     2 1326 22453  9  1 55 34  0
 0 15   4980 111892 202080 3699992    0    0 132710     2 1345 18963  9  1 59 31  0
 0 14   4980 111784 202088 3699996    0    0 133530     2 1329 19589  9  1 65 25  0
 1 13   4980 111868 202088 3699996    0    0 124518     0 1338 20013  8  1 58 33  0

LucidDB version is 0.9.2 LucidDB notes

MonetDB version is Nov 2009

Problem with MonetDB is that needed indexes require a lot of memory, much more than available on box. MonetDB team suggest to setup swap partition 128GB, but I can't do that in current server disk partitions layout.

2x QuadCore CPU

vendor_id       : GenuineIntel
cpu family      : 6
model           : 23
model name      : Intel(R) Xeon(R) CPU           E5405  @ 2.00GHz
stepping        : 6
cpu MHz         : 2000.042
cache size      : 6144 KB

Software RAID 0 over two disks

Linux  2.6.18-164.el5 #1 SMP Thu Sep 3 03:28:30 EDT 2009 x86_64 x86_64 x86_64 GNU/Linux
CentOS release 5.4 (Final)

select 
    sum(lo_extendedprice*lo_discount) as revenue
from 
    lineorder, dates
where 
    lo_orderdate = d_datekey
    and d_year = 1993
    and lo_discount between 1 and 3
    and lo_quantity < 25;

select 
    sum(lo_extendedprice*lo_discount) as revenue
from 
    lineorder, dates
where 
    lo_orderdate = d_datekey
    and d_yearmonthnum = 199401
    and lo_discount between 4 and 6
    and lo_quantity between 26 and 35;

select 
    sum(lo_extendedprice*lo_discount) as revenue
from 
    lineorder, dates
where 
    lo_orderdate = d_datekey
    and d_weeknuminyear = 6
    and d_year = 1994
    and lo_discount between 5 and 7
    and lo_quantity between 26 and 35;

select 
    sum(lo_revenue), d_year, p_brand
from 
    lineorder, dates, part, supplier
where 
    lo_orderdate = d_datekey
    and lo_partkey = p_partkey
    and lo_suppkey = s_suppkey
    and p_category = 'MFGR#12'
    and s_region = 'AMERICA'
group by 
    d_year, p_brand
order by 
    d_year, p_brand;

select 
    sum(lo_revenue), d_year, p_brand
from 
    lineorder, dates, part, supplier
where 
    lo_orderdate = d_datekey
    and lo_partkey = p_partkey
    and lo_suppkey = s_suppkey
    and p_brand between 'MFGR#2221' and 'MFGR#2228'
    and s_region = 'ASIA'
group by 
    d_year, p_brand
order by 
    d_year, p_brand;

select 
    sum(lo_revenue), d_year, p_brand
from 
    lineorder, dates, part, supplier
where 
    lo_orderdate = d_datekey
    and lo_partkey = p_partkey
    and lo_suppkey = s_suppkey
    and p_brand = 'MFGR#2239'
    and s_region = 'EUROPE'
group by 
    d_year, p_brand
order by 
    d_year, p_brand;

select 
    c_nation, s_nation, d_year,
    sum(lo_revenue) as revenue
from 
    customer, lineorder, supplier, dates
where 
    lo_custkey = c_custkey
    and lo_suppkey = s_suppkey
    and lo_orderdate = d_datekey
    and c_region = 'ASIA'
    and s_region = 'ASIA'
    and d_year >= 1992 and d_year <= 1997
group by 
    c_nation, s_nation, d_year
order by 
    d_year asc, revenue desc;

select 
    c_city, s_city, d_year, sum(lo_revenue) as revenue
from 
    customer, lineorder, supplier, dates
where 
    lo_custkey = c_custkey
    and lo_suppkey = s_suppkey
    and lo_orderdate = d_datekey
    and c_nation = 'UNITED STATES'
    and s_nation = 'UNITED STATES'
    and d_year >= 1992 and d_year <= 1997
group by 
    c_city, s_city, d_year
order by 
    d_year asc, revenue desc;

select 
    c_city, s_city, d_year, sum(lo_revenue) as revenue
from 
    customer, lineorder, supplier, dates
where 
    lo_custkey = c_custkey
    and lo_suppkey = s_suppkey
    and lo_orderdate = d_datekey
    and (c_city='UNITED KI1' or c_city='UNITED KI5')
    and (s_city='UNITED KI1' or s_city='UNITED KI5')
    and d_year >= 1992 and d_year <= 1997
group by 
    c_city, s_city, d_year
order by 
    d_year asc, revenue desc;

select 
    c_city, s_city, d_year, sum(lo_revenue) as revenue
from 
    customer, lineorder, supplier, dates
where 
    lo_custkey = c_custkey
    and lo_suppkey = s_suppkey
    and lo_orderdate = d_datekey
    and (c_city='UNITED KI1' or c_city='UNITED KI5')
    and (s_city='UNITED KI1' or s_city='UNITED KI5')
    and d_yearmonth = 'Dec1997'
group by 
    c_city, s_city, d_year
order by 
    d_year asc, revenue desc;

select 
    d_year, c_nation,
    sum(lo_revenue - lo_supplycost) as profit
from 
    DATES, CUSTOMER, SUPPLIER, PART, LINEORDER
where 
    lo_custkey = c_custkey
    and lo_suppkey = s_suppkey
    and lo_partkey = p_partkey
    and lo_orderdate = d_datekey
    and c_region = 'AMERICA'
    and s_region = 'AMERICA'
    and (p_mfgr = 'MFGR#1' or p_mfgr = 'MFGR#2')
group by 
    d_year, c_nation
order by 
    d_year, c_nation;

select 
    d_year, s_nation, p_category,
    sum(lo_revenue - lo_supplycost) as profit
from 
    DATES, CUSTOMER, SUPPLIER, PART, LINEORDER
where 
    lo_custkey = c_custkey
    and lo_suppkey = s_suppkey
    and lo_partkey = p_partkey
    and lo_orderdate = d_datekey
    and c_region = 'AMERICA'
    and s_region = 'AMERICA'
    and (d_year = 1997 or d_year = 1998)
    and (p_mfgr = 'MFGR#1' or p_mfgr = 'MFGR#2')
group by 
    d_year, s_nation, p_category
order by 
    d_year, s_nation, p_category;

select 
    d_year, s_city, p_brand,
    sum(lo_revenue - lo_supplycost) as profit
from 
    DATES, CUSTOMER, SUPPLIER, PART, LINEORDER
where 
    lo_custkey = c_custkey
    and lo_suppkey = s_suppkey
    and lo_partkey = p_partkey
    and lo_orderdate = d_datekey
    and s_nation = 'UNITED STATES'
    and (d_year = 1997 or d_year = 1998)
    and p_category = 'MFGR#14'
group by 
    d_year, s_city, p_brand
order by 
    d_year, s_city, p_brand;