LucidDB notes for SSB benchmark

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 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;

(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”;

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