Benchmark results - Mailing list pgsql-odbc

I've run some quick benchmark tests of the libpq driver vs. the
pre-libpq version, using OpenLink's free ODBC Benchmark program which
supports TPC-A style benchmarks on PostgreSQL. Primarily this was
inteded to stress the driver, but also to get a rough idea if there were
any major performance bottlenecks. I thought I'd share the results for
interests sake:

The backend server is a dual 3GHz Xeon with 4GB RAM, running Slackware
Linux. It has 2 RAID1 arrays of 147GB U320 disks on a Megaraid
controller with 256MB cache (logs on one array, data on the other). FS
is Reiser, kernel 2.4.30. Currently hosting other databases that are in
active use.

The client is a 2GHz Pentium-M laptop running XP Pro SP2, with 1GB RAM.
There is a gigabit connection to the server, via 3 switches and a Linux
based firewall with separate gigabit NICs on each network. DSN settings
are all default.

The data set contains 100 branches, 1000 tellers and 10000 accounts.

10 Thread run
=============

Connecting to psqlODBC-libpq : DSN=<psqlodbc-libpq> UID=<postgres>
Driver : 08.01.0001 (PSQLODBC.DLL)
Connection to psqlODBC-libpq opened psqlODBC-libpq -
PostgreSQL(PSQLODBC.DLL) - 10 TPC-A Threads ended with no errors.
Calculating statistics:
    SQL options used:                10
Threads/SQLText
    Transaction time:                61.000000
    Environmental overhead:            -1.000000
    Total transactions:            44746
    Transactions per second:        733.540955
    % less than 1 second:            100.000000
    % 1 < n < 2 seconds:            0.000000
    Average processing time:        0.001363
Connection to psqlODBC-libpq closed


Connecting to psqlODBC-std : DSN=<psqlodbc-std> UID=<postgres>
Driver : 08.00.0103 (PSQLODBC.DLL)
Connection to psqlODBC-std opened psqlODBC-std -
PostgreSQL(PSQLODBC.DLL) - 10 TPC-A Threads ended with no errors.
Calculating statistics:
    SQL options used:                10
Threads/SQLText
    Transaction time:                61.000000
    Environmental overhead:            -1.000000
    Total transactions:            52363
    Transactions per second:        858.409851
    % less than 1 second:            100.000000
    % 1 < n < 2 seconds:            0.000000
    Average processing time:        0.001165
Connection to psqlODBC-std closed

Non-threaded run
================

Connecting to psqlODBC-libpq : DSN=<psqlodbc-libpq> UID=<postgres>
Driver : 08.01.0001 (PSQLODBC.DLL)
Connection to psqlODBC-libpq opened psqlODBC-libpq -
PostgreSQL(PSQLODBC.DLL) - 10 TPC-A Threads ended with no errors.
Calculating statistics:
    SQL options used:                SQLText
    Transaction time:                61.000000
    Environmental overhead:            -1.000000
    Total transactions:            8394
    Transactions per second:        137.6065525
    % less than 1 second:            100.000000
    % 1 < n < 2 seconds:            0.000000
    Average processing time:        0.007267
Connection to psqlODBC-libpq closed


Connecting to psqlODBC-std : DSN=<psqlodbc-std> UID=<postgres>
Driver : 08.00.0103 (PSQLODBC.DLL)
Connection to psqlODBC-std opened psqlODBC-std -
PostgreSQL(PSQLODBC.DLL) - 10 TPC-A Threads ended with no errors.
Calculating statistics:
    SQL options used:                SQLText
    Transaction time:                61.000000
    Environmental overhead:            -1.000000
    Total transactions:            1081
    Transactions per second:        180.166672
    % less than 1 second:            100.000000
    % 1 < n < 2 seconds:            0.000000
    Average processing time:        0.005550
Connection to psqlODBC-std closed


These results seem fairly consistently reproducable. Using the server on
my laptop, I get slightly faster results for non-threaded runs with both
drivers. With 10 threads, the tps is about 1/3rd that of the Linux
server for both drivers, however, in both the non non-threaded and 10
thread tests on the laptop, the older version of the driver remained
consistently slightly faster.

However, remember that my main reason for doing this was to stress the
new port of the driver and make sure it didn't fall over. I think it's
impressive that the first version of the code seems so stable, and only
seems to have a minor performance penalty so early in it's life.
Incidently, in some /very/ unscientifc benchmarks of simple selects
using ADO/ASP/IIS, hammered by Apache Bench last night, I saw no
reproducable performance differences between the 2 drivers at all.

Kudos to Annop and his colleagues at Pervasive for their work.

Regards, Dave.