Table of Contents
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 firstname.lastname@example.org.
Prerequisites: basic Unix, Linux or MacOS X familiarity (creating directories and users, using an editor, etc).
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.
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:email@example.com:/cvsroot/odbc-bench login bash$ cvs -qz3 -d:pserver:firstname.lastname@example.org:/cvsroot/odbc-bench co odbc-bench
In the latter case, you also need to generate the configure script:
bash$ sh bootstrap.sh
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'
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 **
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.
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:
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.
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 <email@example.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