Re: Benchmark results - Mailing list pgsql-odbc

My initial testing 8.0.1.2(used a build Merlin provided)
I also did not see any major differences.
I tested with a Unicode database and did inserts, selects, updates, and
deletes.
I will be doing stress testing today and tomorrow, but I am glad to hear it
looks pretty close as far as response times.
I have not had any luck with log or cursor using the 8.0.1.1 STD lib driver,
but Dave gave me one that should work for logging.
If the libpq driver stands up during my tests I am going to try to use it
next week in production (going Unicode over the weekend.)
One of IIS servers died last night, so I am guessing it is a Unicode issue
(my French client is the only one working at night for the most part).
Does logging and cursors work on the new driver? I will definitely test that
too. Was getting a catastofic error when I turned on logging (debug=1) or
cursor fetch.

Joel Fradkin

Wazagua, Inc.
2520 Trailmate Dr
Sarasota, Florida 34243
Tel.  941-753-7111 ext 305

jfradkin@wazagua.com
www.wazagua.com
Powered by Wazagua
Providing you with the latest Web-based technology & advanced tools.
C 2004. WAZAGUA, Inc. All rights reserved. WAZAGUA, Inc
 This email message is for the use of the intended recipient(s) and may
contain confidential and privileged information.  Any unauthorized review,
use, disclosure or distribution is prohibited.  If you are not the intended
recipient, please contact the sender by reply email and delete and destroy
all copies of the original message, including attachments.




-----Original Message-----
From: pgsql-odbc-owner@postgresql.org
[mailto:pgsql-odbc-owner@postgresql.org] On Behalf Of Dave Page
Sent: Thursday, July 14, 2005 6:28 AM
To: pgsql-odbc@postgresql.org
Cc: Anoop Kumar
Subject: [ODBC] Benchmark results

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.



---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq