• OSDL-DBT-1

Table of Contents

OSDL DBT-1 home page

OSDL-DBT-1 home page.

OSDL-DBT-1 simulates a web server to give results similar to the proprietary TPC-W benchmark.

Assumptions

• OSDL-DBT-1 assumes that the tools sar (from the sysstat package), iostat and vmstat are installed on the benchmark system.

• OSDL-DBT-1 assumes that DBT-1 source is compiled and run as user pgsql.

• Not really an assumption, more a gotcha:

  • There are several problems with different versions of automake, so be sure that the version of automake is >= 1.9

• The version postgres >= 7.4, this is due to the syntax used in the scripts that build the database.

Configuration

• There are two alternate configuration methods required depending on the database you wish to benchmark against.

SAPDB

• Initial requirements to compile OSDL-DBT-1:

  • SAPDB: Now known as MaxDB, for debian users

    apt-cache search maxdb

  • How to setup MaxDB.

  • SAPDB ODBC: Known as SQLDBC, for debian users

    apt-cache search libsqlod
    ....
    apt-get install libsqlod7.5.00 libsqlod7.5.00-dev

Postgresql

Required packages

• postgresql, the database.
• postgresql-dev, the header files.
• postgresql-client, interface to the database.
• libpq, libraries that the benchmark links against.

Compiling you own Postgrsql

• Before compiling postgresql, if you are going to run dbt2 then check the README-POSTGRESQL file in that benchmark's source, so that you can set the correct parameters for the compilation.

Setting Up the Database

• After installing postgresql a user account needs to be created that the benchmark will run under. the following steps are required.
  • Create a user and group on the benchmark device. Found in the ODSL-DBT-1 documentation.
  • Create a database user with the same name as above. Found in the Postgresql documentation.
  • You will need to have Super User privileges or have your systems administrator create the accounts.

    #> groupadd pgsql
    #> useradd -c "PGSQL user" -d <users home dir> -g 0 -G pgsql pgsql

    #> su -
    #> su - postgres
    #> createuser pgsql
    #> exit

Environment Variables

• In order to use the postgresql header files the postgresql includes directory needs to be exported in the CFLAGS environment variable passed to ./configure see below.

Getting the source

• The OSDL-DB-1 source is maintained in a Bitkeeper tree,

  • bk clone bk://developer.osdl.org/dbt1
    cd dbt1
    bk -r edit

Compiling

• In the source directory doc/pgsql you can find, in OpenOffice format, an users manual. If you do not have access to X or OpenOffice a brief description will be given here, though you should always refer to the user manual if the following instructions do not work.

• Patch the source.

  • The patches found here were required to compile OSDL-DBT-1. These will be submitted to the Benchmark mail list so may already be applied.

• You will need to set a common directory for the benchmark database in a couple of places, I will refer to this directory as $DBPATH.

• Edit the file scripts/pgsql/set_run_env.sh.in, and set the environment variables to suit your installation; the following is taken from the user manual.

  • SID1: the database name, recommend DBT1
  • PATH: make sure that pgsql bin is in the path, if pgsql is installed using the default value, no change is needed.
  • PGDATA: points to the directory where you want to put the database. This has to be changed according to the test environment.
  • PGUSER: PostgreSQL user name. Recommend pgsql
  • DBT1_PERL_MODULE: do not change it.
  This is what I set for a Debian installed Postgresql DB.

  export SID1=DBT1
  export PATH=/usr/lib/postgresql/bin/:$PATH
  export PGDATA=$DBPATH
  export PGUSER=pgsql
  export DBT1_PERL_MODULE=@TOPDIR@/perlmodules

• In the ODSL-DBT-1 source directory run the following

  • #> aclocal
    #> automake
    #> autoconf
    #> autoheader
    #> export CFLAGS=-I<path-to-postgresql-headers>
    #> export CPPFLAGS=-I<path-to-postgresql-headers>
    #> export LDFLAGS=-L<path-to-postgres-libaries>
    #> ./configure
    #> make
    #> make install

  • For debian postgresql installed packages:

    • path-to-postgresql-headers: /usr/include/postgresql

    • path-to-postgresql-libraries: /usr/lib/postgresql

  • If the postgresql headers and libraries are aleady in the relevent search paths, these exports can be omitted.

Setup the benchmark database

1. cd into the datagen directory and run datagen

   ./datagen  -d PGSQL -i 10000 -u 10 -p $DBPATH

   This will produce a small sample. You may want to increase the number of items and users in the system.

   • -i, has to be 1000, 10000 or 100000.

   • -u, can be any positive integer.

   • NOTE with values of -i 100000 and -u 10000, data generation will take considerable time and consume a lot of disk space, for example the following was produced on an 2 * i2 1500 MHz rx2600.

     pgsql@vanilla:~/dbt1-BK/scripts/pgsql$ ls -lh /tmp/DBT1-files/
     total 31G
     -rw-r--r--    1 pgsql    root         5.8G Aug 26 15:12 address.data
     -rw-r--r--    1 pgsql    root         8.9M Aug 26 14:59 author.data
     -rw-r--r--    1 pgsql    root         2.8G Aug 26 15:43 cc_xacts.data
     -rw-r--r--    1 pgsql    root          14G Aug 26 14:59 customer.data
     -rw-r--r--    1 pgsql    root          51M Aug 26 14:27 item.data
     -rw-r--r--    1 pgsql    root         6.3G Aug 26 15:43 order_line.data
     -rw-r--r--    1 pgsql    root         2.7G Aug 26 15:43 orders.data
     pgsql@vanilla:~/dbt1-BK/scripts/pgsql$
     
       real    75m34.662s
       user    72m56.411s
       sys     2m29.652s

2. cd into the scripts/pgsql and run the build_db.sh

   ./build_db.sh "-c tcpip_socket=[on|off]" 0 0

3. The -c flag above will depend on how the postmater server is started, see /var/log/pgsql/postgresql.conf for more details.

   The first argument is rather pointless unless you would like to pass options to the postmaster server --- see man postmaster.

   • pgsql@vanilla:~/dbt1-BK/scripts/pgsql$ ls -lh ../../../postgresDB/
     total 36M
     -rw-r--r--    1 pgsql    root         5.8M Aug 24 12:26 address.data
     -rw-r--r--    1 pgsql    root         894K Aug 24 12:26 author.data
     -rw-r--r--    1 pgsql    root         2.7M Aug 24 12:26 cc_xacts.data
     -rw-r--r--    1 pgsql    root          13M Aug 24 12:26 customer.data
     -rw-r--r--    1 pgsql    root         5.0M Aug 24 12:26 item.data
     -rw-r--r--    1 pgsql    root         6.1M Aug 24 12:26 order_line.data
     -rw-r--r--    1 pgsql    root         2.5M Aug 24 12:26 orders.data
     pgsql@vanilla:~/dbt1-BK/scripts/pgsql$
     
       real    1m39.830s
       user    0m0.236s
       sys     0m0.229s

Running the benchmark

• To run the benchmark there are two application that require instantiation.
• The Slave, takes no arguments and listens on port 2345.

    cd data_collect
    ./dbt1_slave.pl

• The Master, requires a number of arguments though can be passed in a file with the -f option.

    cd data_collect/pgsql
    ./dbt1_master.pl -f run.config

  • The run.config file is a good source for values that can be tuned for the system that the benchmark runs on.

• Gotcha the run.config file should no include any whitespace between tag:value.

• Three important values that need to be set in run.config are:

  1. items:<no of items given in the datagen -i option>

  2. gcustomers:2880 * <no of customers given in the datagen -u option>

  3. eus:<number of emulated users>

     • A positive value that you will need to adjust, to many and the benchmark will hang, too little and it will leave processes hanging around, i.e. appServer.

• When running the master you will need to remember a few of the options that were used in the build_db and datagen scripts.

  • datagen, -i the number of items and -u the number of users.

  • build_db, db_param, redirect_tmp, redirect_xlog.

• If you get a ulimit error on any of the scripts above, just set the ulimit on the command line before, or the user manual also suggests that is can be ignored. == Adjusting the Sample amount ==

  • To increase the number of samples, increase the zduration value in run.config.

    samples = (zduration + 120)/interval.

Run Times

Getting Results

• This is not en easy process either, though it is only one command.

• DBT1_PERL_MODULE environment variable must point to the perlmodules directory.

• There is a small bug in the code used to generate the figures.

• Move into data_collect/resulttools and run the following:

  • DBT1_PERL_MODULE=../../perlmodules ./wb_dbt1_report_pgsql.pl \
    -i <the out_dir from run.config> -o <a html output file of your choice>

Benefits

Problems

• OSDL-DBT-1 is a complex web of shell, perl and C source code, that makes debugging particularly interesting.

Results