OSDL-DBT-2

OSDL-DBT-2 is a fair-usage implementation of the TPC-H benchmark spec. It simulates a warehouse-inventory workload.

OSDL-DBT2 is actively maintained by a number of developers and users. OSDL-DBT-2

Requirements

Postgresql version >= 8.0
- Most prepackaged postgres installations are < 8.0, which does not implement some of the features that dbt2 expects.
Optional
- iostat
- vmstat

Configuration

Postgresql

You will need root access for the following.
Create a user postgres.
Get the latest postgresql source.
Unpack postgresql

cd into the top level source dirextory cd postgres-<version>

  sudo adduser --home /opt/postgres-<version> postgres 
  ./configure --prefix=/opt/postgres-<version> --enable-thread-safety
  make
  make check # optional
  make install
  chown -R postgres /opt/postgres-<version>
  
  # the following requires bison and flex.
  cd contrib/
  make
  make install
  sudo su - postgres
  /opt/postgres/bin/postmaster -D /opt/postgres/data& 
  /opt/postgres-<version>/bin/initdb \
    -D /opt/postgres-<version>/data
  /opt/postgres-<version>/bin/createuser
  # Add the user that will run the benchmarks

Note*: Be sure to remove any previous installations of postgres since the dbt2 build may pick the old installation for its source.

dbt2

Compiling

cd into the top level dbt2 source directory

  export PATH=$PATH:/opt/postgres-<version>/bin
  export CFLAGS="-I/opt/postgres-<version>/include/server \
                 -I/opt/postgres-<version>/include"
  export LDFLAGS="-L/opt/postgres-<version>/lib"
  #
  # If you have cloned a bk tree you will need to run
  # './init.sh' from the top of the dbt2 source tree
  # before the following ./configure command
  #
  ./configure --with-postgresql=/opt/postgres-<version>
  make
  #
  # currently it is not recommended to run
  # 'make install' for dbt2 
  #

Building the Benchmark database

From the top level dbt2 source directory (see README-POSTGRESQL for sample configurations).

  export DB=DB.big
  mkdir $DB
  ./src/datagen ./src/datagen --pgsql -w 20 \
    -d $DB -c 300 -i 10000 -o 300 -n 90

cd into the scripts directory cd scripts/pgsql/

Edit the pgsql_profile file and modify that values for:

PGDATA, The local dbt2 database files

DBDATA, The data generated bu datagen

  PGDATA=<path to DB.big>/data; export PGDATA
  DBDATA=<path to DB.big>; export DBDATA
  #
  # I had to give the full path, a relative
  # path failed to find the data files.
  #

Create the database tables

  . ./init_env.sh
  ./create_db.sh
  vi ../../DB.big/data/postgresql.conf
  #
  # set stats_row_level = true
  #
  ./stop_db.sh
  ./start_db.sh
  ./load_db.sh
  #
  # keep an eye on errors,
  #   the following messages can be ignored:
  #   - psql:create_tables.sql:9: NOTICE:  CREATE TABLE / PRIMARY KEY will \
  #     create implicit index "pk_new_order" for table "new_order"
  #
  #   the following are due to a DROP function
  #   when the function does not exist, so are OK.
  #   - psql:/home/dsw/USENIX/dbt2/scripts/pgsql/../../storedproc/pgsql/c/new_order.sql:7:
  #      ERROR:  type "new_order_info" does not exist
  #

Running

It has been recommended to manually run the client and driver.
From the scripts directory above:

Terminal one

  export OUTPUT=../../DB.big/output/0
  mkdir ../../DB.big/output
  mkdir $OUTPUT

  ../../src/client -d localhost -c 2 -l 5432 -o $OUTPUT

Terminal two

  export OUTPUT=../../DB.big/output/0
  ../../src/driver -d localhost -l 360 -wmin 1 -wmax 20 -w 20 \
  -c 3 -i 10 -o 3 -n 9 \
  -ktd 0 -ktn 0 -kto 0 -ktp 0 -kts 0 \
  -ttd 0 -ttn 0 -tto 0 -ttp 0 -tts 0 -tpw 80 -outdir $OUTPUT

Tuning

When dbt2 and postgresql are run on a large memory machine, it is important to stress the available memory of the machine. This requires several parameters to be set in dbt2, postgresql and the kernel.

Kernel Parameters

For postgresql to allow more connections you will need to increase the values of some runtime system parameters. This can be achieved without a reboot through the proc or sysctl interface. I have adjusted the following kernel defaults in /etc/sysctl.conf.
```
  kernel.msgmni = 512
  kernel.sem=250 128000 32 512
  kernel.shmall=4294967296
  kernel.shmmax=4294967296
```

Postgresql Parameters

Postgresql can now be configured to allow a higher number of connections, which requires the buffer size to be increased, from postgrsql.conf.
```
  ...
  max_connections = 6000
  ...
  shared_buffers = 12000
  ...
```

Dbt2 parameters

On a system with 16 CPUs and 32GB of memory I chose the following amounts.
- Datagen:
```
 ./src/datagen --pgsql -d /opt/DB.big -w 300 \
 -c 6000 -i 100000 -o 1000 -n 900
```
  - This equates to a total of #GB of data.
- Client:
- Driver:

Problems

When a client program starts it looks for the postgresql socket file .s.PGSQL.5432 in /var/run/postgres, when it is actually in /tmp
- A quick work around here is to add a symbolic link /var/run/postgres -> /tmp
NEW_ORDER_4 failed