2. Installation and Configuration

Before creating a test schema you should ensure that your database is configured to process analytic workloads instead of transactional.

2.1. Oracle

When your database server is installed you should create a tablespace into which the test data will be installed allowing disk space according to the guide previously given in this document.

SQL> create tablespace tpchtab datafile size 20g;

Tablespace created.

When using parallel query ensure that the instance is configured for parallel execution, noting in particular the value for parallel_max_servers.

SQL> show parameter parallel
NAME                      TYPE        VALUE
------------------------------------ -----------
parallel_max_servers      integer    160
parallel_min_servers      integer    16
parallel_servers_target   integer    64
parallel_threads_per_cpu  integer    2

For testing purposes you can disable parallel execution in a particular environment by setting parallel_max_servers to a value of zero. An additional parameter that can provide significant benefit to the performance of parallel query workloads is optimizer_dynamic_sampling. By default this value is set to 2. Increasing this value to 4 has been shown to benefit query performance however testing the impact of changing this parameter should always be done during pre-testing as it may change between Oracle releases.

SQL> alter system set optimizer_dynamic_sampling=4;

System altered.

SQL> show parameter optimizer_dynamic

NAME                       TYPE       VALUE
------------------------------------ ----------- 
optimizer_dynamic_sampling integer    4

If using the In-Memory option ensure that the parameter inmemory_size has been configured and the database restarted.

SQL> show parameter inmemory

NAME                             TYPE        VALUE
-------------------------------------------- --------
inmemory_force                   string      DEFAULT
inmemory_max_populate_servers    integer     2
inmemory_query                   string      ENABLE
inmemory_size                    big integer 1500M
inmemory_trickle_repopulate      integer     1
optimizer_inmemory_aware         boolean     TRUE

Then alter the new tablespace containing the schema to be in-memory.

SQL> alter tablespace TPCHTAB default inmemory;
Tablespace altered.

As shown the objects created within the tablespace will now be configured to be in-memory.

SQL> select tablespace_name, def_inmemory from dba_tablespaces;

------------------------- --------
SYSTEM                    DISABLED
SYSAUX                    DISABLED
TEMP                      DISABLED
USER                      DISABLED
TPCCTAB                   DISABLED
TPCHTAB                   ENABLED

For larger schemas both partitioning and compression settings (both standard and in-memory) should be considered for query tuning.

2.2. SQL Server

For SQL Server ensure that the Max Degree of Parallelism is set to the maximum number of cores that you wish to process the queries.

Figure 12.1. Max Degree of Parallelism

Max Degree of Parallelism

2.3. Db2

If using DB2 BLU use db2set to set the parameter DB2_WORKLOAD to ANALYTICS.

[db2inst1 ~]$ db2set DB2_WORKLOAD=ANALYTICS
[db2inst1 ~]$ db2stop force
25/05/2016 10:08:22     0   0   SQL1064N  DB2STOP processing was successful.
SQL1064N  DB2STOP processing was successful.
[db2inst1 ~]$ db2start
05/25/2016 10:08:27     0   0   SQL1063N  DB2START processing was successful.
SQL1063N  DB2START processing was successful.

In your db2cli.ini set the following parameter for each of the databases that you plan to create to test the TPROC-H workload, this will prevent failure due to SQLSTATE 01003 “Null values were eliminated from the argument of a column function” when running the workload thereby preventing the query set from completing.

[db2inst1 cfg]$ more db2cli.ini 

When your database server is installed you should create a database into which the test data will be installed, for TPROC-H workloads a large pagesize is recommended.

[db2inst1 ~]$ db2 create database tpch pagesize 32 k
DB20000I  The CREATE DATABASE command completed successfully.

2.4. PostgreSQL

The PostgreSQL Server should be at a minimum level of 9.6 that supports Parallel Query. The postgresql.conf file should include parallelism specific parameters such as follows:

work_mem = 1000MB                              
max_worker_processes = 16
max_parallel_workers_per_gather = 16
force_parallel_mode = on

2.5. MySQL

MySQL does not support analytic workloads and therefore there are no configuration parameters to optimize the database for such a workload.

2.6. MariaDB

To test analytic workloads MariaDB columnstore is the solution that should be used. Note that this is a separate installation from a standard MariaDB installation rather than a pluggable storage engine. As MariaDB columnstore is based on a columnstore solution called InfiniDB the relevant parameters start with infinidb, for example:

# Enable compression by default on create, set to 0 to turn off
# Default for string table threshhold
# infinidb local query flag
# infinidb_local_query=1