Table of Contents

Introduction

The following is a HOWTO document for using ODBC-Bench to benchmark your ODBC data-sources. To send feedback / comments / suggestions, please email the maintainer at iodbc@openlinksw.com.

Prerequisites: basic Unix, Linux or MacOS X familiarity (creating directories and users, using an editor, etc).

Installing form pre-built binaries

Binary distributions can be downloaded from OpenLink or the sourceforge.net project file-list page for a variety of OS/platforms.

Installing from Source

Requirements

To build ODBC-Bench, you must have:

  • development tools: gcc (and dependencies), make etc; on MacOS X, this means having Xcode installed.
  • an ODBC driver manager such as iODBC or unixODBC installed
  • libxml2 (greater than version 2.4.0), available from XMLsoft .

MacOS X users please note: the version provided with MacOS X Jaguar is libxml-2.2.x, which is not uptodate enough to build odbc-bench. You should install a newer version, either from xmlsoft or with Fink or Darwinports . Users of Panther and Tiger already have sufficient versions with their OS as standard.

Optionally, you can also build odbc-bench with support for a GUI, requiring

  • GTK, available from GTK.org , complete with its requirements for the X window system etc.

Note: on the Mac, use the version provided by Fink or Darwinports . There is also a project to port GTK to Carbon directly, thereby obviating the need to use X windows, at gtk-osx , but experiments show it to be in need of greater development before odbc-bench will run with it, so this is not currently recommended.

These can normally also be obtained from your unix vendor, or as packages or ports for GNU/Linux or FreeBSD distributions.

Downloading

You can either download the latest tarball (odbc-bench-1.0.0.tar.gz) or check out the sources from CVS hosted on SourceForge.

To unpack the tarball, run

bash$ zcat odbc-bench-0.99.21.tar.gz | tar xvf -

or to check out sources from CVS, run

bash$ cvs -d:pserver:anonymous@cvs.sourceforge.net:/cvsroot/odbc-bench login
bash$ cvs -qz3 -d:pserver:anonymous@cvs.sourceforge.net:/cvsroot/odbc-bench co odbc-bench

In the latter case, you also need to generate the configure script:

bash$ sh bootstrap.sh

Building

Linux / Unix

Building odbc-bench is simple. From the directory created in the above, run configure:

bash$ ./configure --prefix=/usr/local/odbc-bench \
--with-iodbc=/usr/local/iODBC

In this case, we favour using /usr/local/odbc-bench/ to keep all the files together: you can omit the `--prefix' option and it will use /usr/local/ by default. If you only want the commandline version to be installed, now would be the time to specify `--disable-gui' in the above command as well.

Now run make, become root by your favoured method, and run make install:

bash$ make
bash$ su root -c 'make install'

Mac OS X

The above configure and make method does not work with GTK on MacOS X, so we recommend you build a native Carbon app version instead:

bash$ tar xvpfz odbc-bench-1.0.0.tar.gz
...
bash$ cd odbc-bench-1.0.0
bash$ cd mac
bash$ make 
bash$ make
xcodebuild -buildstyle Deployment
=== BUILDING NATIVE TARGET ODBC Bench  USING BUILD STYLE Deployment ===

PBXCp "build/ODBC Bench.app/Contents/Info.plist" "build/odbc-bench.build/ODBC Bench.build/Info.plist"
mkdir -p "/Volumes/Firewire/Users/tim/C/odbc-bench-1.0.0/mac/build/ODBC Bench.app/Contents"
cd /Volumes/Firewire/Users/tim/C/odbc-bench-1.0.0/mac
/System/Library/PrivateFrameworks/DevToolsCore.framework/Resources/pbxcp -exclude .DS_Store -exclude CVS 
...
   * * BUILD SUCCEEDED **
bash$ sudo make install
Password:
sudo xcodebuild install -buildstyle Deployment install DSTROOT=/
=== BUILDING NATIVE TARGET ODBC Bench  USING BUILD STYLE Deployment ===

PBXCp "/Applications/ODBC Bench.app/Contents/Info.plist" "build/odbc-bench.build/ODBC Bench.build/Info.plist"                                           
...
SetMode a-w,a+rX "/Applications/ODBC Bench.app"
cd /Volumes/Firewire/Users/tim/C/odbc-bench-1.0.0/mac
/bin/chmod -RH a-w,a+rX "/Applications/ODBC Bench.app"
   * * BUILD SUCCEEDED **

=== BUILDING NATIVE TARGET ODBC Bench  USING BUILD STYLE Deployment ===
   * * BUILD SUCCEEDED **

Running odbc-bench

This section guides you through setting ODBC-Bench to connect to a datasource, configure a test and run it. You have the option of using the GTK or Carbon GUIs or running it all from the commandline.

Using the GUIs

Linux / Unix uers can run `odbc-bench-gtk' from the commandline to bring up the GUI; MacOS X users should open /Applications/ODBC Bench.app to start the application:

GUI main window

The idea of ODBC Bench is that you build pools of tests - you can configure combinations of TPC-A or TPC-C tests with various parameters and save them out for later. By default, you have an untitled session into which you can add tests to be run.

From here, under the Edit menu, select `new benchmark item'. You can choose a name for the test-run, and whether it's to use the TPC-A or TPC-C model:

Having chosen a name and model, under the Edit menu choose `Login Details', and specify a DSN, username and password to be used for the connection:

Now select Edit / Table Details to set up the schema:

Having chosen which tables to use and how many rows with which to populate them, proceed to connecting and creating them in the database with Action / Create tables & Procedures:

Next, configure the nature of the test to be run. You can specify all transactions to take place on one or several threads, whether to use array parameters, whether to use direct execution of SQL queries, prepared statements or stored procedures, whether to execute asynchronously, what transaction-isolation level to use and what cursor model:

From the Action menu, choose `Run Selected Tests', specify a duration and output file, and press Start. If you defined a pool of multiple tests, you can `Run All' instead:

As it progresses, you should see a completion-indicator for each thread:

Finally, the results are displayed:

You can build a list of tests - DSNs and test configurations - against which to run odbc-bench, and then select them for running in bulk.

Using the CLI

The command-line interface to odbc-bench is invoked with the odbc-bench-cmd command. As an overview of the options it takes, this is the `--help' output:

bash$ odbc-bench-cmd --help

OpenLink ODBC Benchmark Utility 0.99.22
Copyright (C) 2000-2003 OpenLink Software
Please report all bugs to <odbc-bench@openlinksw.com>

This utility is released under the GNU General Public License (GPL)

Disclaimer: The benchmarks in this application are loosely based
on the TPC-A and TPC-C standard benchmarks, but this application
does not claim to be a full or precise implementation, nor are
the results obtained by this application necessarily comparable
to the vendor's published results.

Usage :

-d -dsn   - login dsn
-u -uid   - user id
-p -pwd   - password
-r -runs  - number of runs (default 1)
-m -time  - duration of the run (mins) (default 1)
-t -threads    - number of threads (default 1)
-P -arrayparm  - size for used array of parameters (default 1) 
-s -test       - exec | proc | prepare | runall (default 'exec' test)
-x -txn        - use transactions (default 'don't use transactions')
-1 -query      - do 100 row query
-a -async      - asynchronous execution
-c -cursor_type - cursor type = forward | static | keyset | dynamic | 
mixed (default 'forward')
-i -txn_isolation  - transaction isolation level = uncommitted | committed 
| repeatable | serializable
(default 'Driver Default')
-S -rowset_size    - rowset size (default 1)
-K -keyset_size    - keyset size (default 1)
-T -trav_count     - traversal count (default 1)
-C -create_tables  - create the tables and procedures, then exit
-D -drop_tables    - drop the tables and procedures, then exit
-rcreate_table     - create the results table, then exit
-rdrop_table       - drop the results table, then exit
-rdsn   - login dsn for result table
-ruid   - user id for result table
-rpwd   - password for result table
-rfile  - output filename for results data
-v      - print info on stdout (error messages, test results,
messages about the creation and closing connections)
-V      - print the same info on stdout as for -v and the test progress info
-R      - doesn't do rollbacks on deadlock

A reasonable way to run it is as follows - corresponding to the above settings:

bash$ odbc-bench-cmd -d "pglocal" -u tim -p "" -D
bash$ odbc-bench-cmd -d "pglocal" -u tim -p "" -C
bash$ odbc-bench-cmd -d "pglocal" -u tim -p "" -r 1 -t 4 -s prepare \
-c keyset -S 5 -K 5 -v |& tee run.log

The first of these lines drops any pre-existing tables in the database. The second re-creates them, and the third performs the run with semi-verbose output tee'd into a logfile.

The output looks like:

Connecting to CommandLine : DSN=<pglocal> UID=<tim>
Driver : 07.02.0005 (libpsqlodbc.so)
Connection to CommandLine opened

Connection to CommandLine closed

Connecting to CommandLine : DSN=<pglocal> UID=<tim>
Driver : 07.02.0005 (libpsqlodbc.so)
Connection to CommandLine opened

CommandLine - PostgreSQL(libpsqlodbc.so) - all 4 TPC-A Threads completed
successfully.
Calculating statistics:
SQL options used:                       4 Threads/Params
Transaction time:                       61.000000
Environmental overhead:                 -1.000000
Total transactions:                     7835
Transactions per second:                128.442627
% less than 1 second:                   100.000000
% 1 < n < 2 seconds:                    0.000000
Average processing time:                0.007786
Connection to CommandLine closed

References

0