====== LucidDB notes for SSB benchmark ======
===== dba_system_parameters =====
0: jdbc:luciddb:http://localhost> select * from sys_root.dba_system_parameters;
+---------------------------------+-------------------------------------+
| PARAM_NAME | PARAM_VALUE |
+---------------------------------+-------------------------------------+
| fennelDisabled | false |
| userCatalogEnabled | false |
| codeCacheMaxBytes | 2000000 |
| checkpointInterval | 0 |
| serverRmiRegistryPort | -1 |
| serverSingleListenerPort | -1 |
| calcVirtualMachine | CALCVM_JAVA |
| javaCompilerClassName | org.eigenbase.javac.JaninoCompiler |
| connectionTimeoutMillis | 86400000 |
| serverHttpPort | -1 |
| databaseInitSize | 2000 |
| databaseIncrementSize | 1000 |
| databaseMaxSize | 0 |
| tempInitSize | 3000 |
| tempIncrementSize | 1000 |
| tempMaxSize | 0 |
| databaseShadowLogInitSize | 2000 |
| databaseShadowLogIncrementSize | 1000 |
| databaseTxnLogInitSize | 2000 |
| databaseTxnLogIncrementSize | 1000 |
| cachePagesMax | 131072 |
| cachePagesInit | 131072 |
| cachePageSize | 32768 |
| groupCommitInterval | 0 |
| resourceDir | ${FARRAGO_HOME}/catalog/fennel |
| jniHandleTraceFile | |
| expectedConcurrentStatements | 4 |
| cacheReservePercentage | 5 |
| deviceSchedulerType | aioLinux |
| freshmenPageQueuePercentage | 25 |
| pageHistoryQueuePercentage | 100 |
| prefetchPagesMax | 12 |
| prefetchThrottleRate | 10 |
| processorCacheBytes | -1 |
+---------------------------------+-------------------------------------+
34 rows selected (1.421 seconds)
===== Create table =====
CREATE TABLE SSB.CUSTOMER ( C_CUSTKEY INTEGER PRIMARY KEY,
C_NAME VARCHAR(25) NOT NULL,
C_ADDRESS VARCHAR(40) NOT NULL,
C_CITY VARCHAR(10) NOT NULL,
C_NATION VARCHAR(15) NOT NULL,
C_REGION VARCHAR(12) NOT NULL,
C_PHONE VARCHAR(15) NOT NULL,
C_MKTSEGMENT VARCHAR(10) NOT NULL);
CREATE TABLE SSB.DATES ( D_DATEKEY INTEGER PRIMARY KEY,
D_DATE VARCHAR(18) NOT NULL,
D_DAYOFWEEK VARCHAR(8) NOT NULL,
D_MONTH VARCHAR(9) NOT NULL,
D_YEAR INTEGER NOT NULL,
D_YEARMONTHNUM INTEGER,
D_YEARMONTH VARCHAR(7) NOT NULL,
D_DAYNUMINWEEK INTEGER,
D_DAYNUMINMONTH INTEGER,
D_DAYNUMINYEAR INTEGER,
D_MONTHNUMINYEAR INTEGER,
D_WEEKNUMINYEAR INTEGER,
D_SELLINGSEASON VARCHAR(12) NOT NULL,
D_LASTDAYINWEEKFL INTEGER,
D_LASTDAYINMONTHFL INTEGER,
D_HOLIDAYFL INTEGER,
D_WEEKDAYFL INTEGER);
CREATE TABLE SSB.PART ( P_PARTKEY INTEGER PRIMARY KEY,
P_NAME VARCHAR(22) NOT NULL,
P_MFGR VARCHAR(6) NOT NULL,
P_CATEGORY VARCHAR(7) NOT NULL,
P_BRAND VARCHAR(9) NOT NULL,
P_COLOR VARCHAR(11) NOT NULL,
P_TYPE VARCHAR(25) NOT NULL,
P_SIZE INTEGER NOT NULL,
P_CONTAINER VARCHAR(10) NOT NULL);
CREATE TABLE SSB.SUPPLIER ( S_SUPPKEY INTEGER PRIMARY KEY,
S_NAME VARCHAR(25) NOT NULL,
S_ADDRESS VARCHAR(25) NOT NULL,
S_CITY VARCHAR(10) NOT NULL,
S_NATION VARCHAR(15) NOT NULL,
S_REGION VARCHAR(12) NOT NULL,
S_PHONE VARCHAR(15) NOT NULL);
CREATE TABLE SSB.LINEORDER ( LO_ORDERKEY BIGINT,
LO_LINENUMBER BIGINT,
LO_CUSTKEY INTEGER NOT NULL,
LO_PARTKEY INTEGER NOT NULL,
LO_SUPPKEY INTEGER NOT NULL,
LO_ORDERDATE INTEGER NOT NULL,
LO_ORDERPRIOTITY VARCHAR(15) NOT NULL,
LO_SHIPPRIOTITY INTEGER,
LO_QUANTITY BIGINT,
LO_EXTENDEDPRICE BIGINT,
LO_ORDTOTALPRICE BIGINT,
LO_DISCOUNT BIGINT,
LO_REVENUE BIGINT,
LO_SUPPLYCOST BIGINT,
LO_TAX BIGINT,
LO_COMMITDATE INTEGER NOT NULL,
LO_SHIPMODE VARCHAR(10) NOT NULL)
!set force true
alter session set "errorMax" = 1000;
alter session set "degreeOfParallelism" = 10;
===== Tips to improve load =====
(by Nicholas Goodman)
Raise the ceiling:
alter system set "cachePagesMax" = 131072;
2. Restart LucidDB
3. Change cachePagesInit:
alter system set "cachePagesInit" = 131072;
I preallocated a 100GB db.dat (./lucidDbAlloc).
Second, make sure you have cachePagesMax and cachePagesInit set to a reasonable setting. I used 131072 for most server installations for both values.
I generated a 10GB dataset (to test the DDL I'm sending to you) tonight. In order to get parallel loading you have to split the file, and then create a view to UNION ALL it. I used the command line "split" to split the file into 8 parts, duplicated the .bcp files, and then create a view like:
create view ssb.lineorder_source as
select * from ssb."lineorder1" UNION ALL
select * from ssb."lineorder2" UNION ALL
select * from ssb."lineorder3" UNION ALL
select * from ssb."lineorder4" UNION ALL
select * from ssb."lineorder5" UNION ALL
select * from ssb."lineorder6" UNION ALL
select * from ssb."lineorder7" UNION ALL
select * from ssb."lineorder8";
===== Indexes =====
1/52 set schema 'ssb';
No rows affected (0.023 seconds)
2/52
3/52 ---------------------------
4/52 -- indices for foreign keys
5/52 ---------------------------
6/52
7/52 -- LINEORDER: LO_CUSTKEY, LO_PARTKEY, LO_SUPPKEY, LO_ORDERDATE, LO_COMMITDATE
8/52 CREATE INDEX LO_CUSTKEY_IDX ON LINEORDER(LO_CUSTKEY);
No rows affected (78601.04 seconds)
9/52 CREATE INDEX LO_PARTKEY_IDX ON LINEORDER(LO_PARTKEY);
No rows affected (126921.698 seconds)
10/52 CREATE INDEX LO_SUPPKEY_IDX ON LINEORDER(LO_SUPPKEY);
No rows affected (116387.907 seconds)
11/52 CREATE INDEX LO_ORDERDATE_IDX ON LINEORDER(LO_ORDERDATE);
No rows affected (10624.119 seconds)
12/52 CREATE INDEX LO_COMMITDATE_IDX ON LINEORDER(LO_COMMITDATE);
No rows affected (34395.341 seconds)
13/52
14/52 ----------------------------
15/52 -- indices for WHERE clauses
16/52 ----------------------------
17/52
18/52 -- CUSTOMER: C_REGION, C_NATION, C_CITY
19/52 CREATE INDEX C_REGION_IDX ON CUSTOMER(C_REGION);
No rows affected (12.742 seconds)
20/52 CREATE INDEX C_NAITON_IDX ON CUSTOMER(C_NATION);
No rows affected (23.238 seconds)
21/52 CREATE INDEX C_CITY_IDX ON CUSTOMER(C_CITY);
No rows affected (34.307 seconds)
22/52
23/52 -- DATES: D_YEAR, D_YEARMONTHNUM, D_WEEKNUMINYEAR, D_YEARMONTH
24/52 CREATE INDEX D_YEAR_IDX ON DATES(D_YEAR);
No rows affected (0.698 seconds)
25/52 CREATE INDEX D_YEARMONTHNUM_IDX ON DATES(D_YEARMONTHNUM);
No rows affected (0.672 seconds)
26/52 CREATE INDEX D_WEEKNUMINYEAR_IDX ON DATES(D_WEEKNUMINYEAR);
No rows affected (0.682 seconds)
27/52 CREATE INDEX D_YEARMONTH_IDX ON DATES(D_YEARMONTH);
No rows affected (0.699 seconds)
28/52
29/52 -- PART: P_CATEGORY, P_BRAND, P_MFGR
30/52 CREATE INDEX P_CATEGORY_IDX ON PART(P_BRAND);
No rows affected (4.071 seconds)
31/52 CREATE INDEX P_BRAND_IDX ON PART(P_BRAND);
No rows affected (3.888 seconds)
32/52 CREATE INDEX P_MFGR_IDX ON PART(P_BRAND);
No rows affected (4.53 seconds)
33/52
34/52 -- SUPPLIER: S_REGION, S_NATION, S_CITY
35/52 CREATE INDEX S_REGION_IDX ON SUPPLIER(S_REGION);
No rows affected (4.612 seconds)
36/52 CREATE INDEX S_NAITON_IDX ON SUPPLIER(S_NATION);
No rows affected (6.704 seconds)
37/52 CREATE INDEX S_CITY_IDX ON SUPPLIER(S_CITY);
No rows affected (12.992 seconds)
38/52
39/52 -- LINEORDER: LO_QUANTITY, LO_DISCOUNT,
40/52 CREATE INDEX LO_QUANTITY_IDX ON LINEORDER(LO_QUANTITY);
No rows affected (8407.526 seconds)
41/52 CREATE INDEX LO_DISCOUNT_IDX ON LINEORDER(LO_DISCOUNT);
No rows affected (4954.581 seconds)
42/52
43/52 -----------------
44/52 -- analyze tables
45/52 -----------------
46/52
47/52 ANALYZE TABLE CUSTOMER ESTIMATE STATISTICS FOR ALL COLUMNS;
No rows affected (13.094 seconds)
48/52 ANALYZE TABLE DATES ESTIMATE STATISTICS FOR ALL COLUMNS;
No rows affected (2.74 seconds)
49/52 ANALYZE TABLE PART ESTIMATE STATISTICS FOR ALL COLUMNS;
No rows affected (2.303 seconds)
50/52 ANALYZE TABLE SUPPLIER ESTIMATE STATISTICS FOR ALL COLUMNS;
No rows affected (4.179 seconds)
51/52 ANALYZE TABLE LINEORDER ESTIMATE STATISTICS FOR ALL COLUMNS;
No rows affected (3890.009 seconds)
52/52