Thread: PGDG ODBC, ODBCng, DBD::Pg
Hello, I was running some basic benchmarks in comparison of the three aforementioned products. I thought I would drop a link here for people to compare: http://projects.commandprompt.com/public/odbcng/wiki/Performance One thing of note that is very positive IMHO is that PGDG ODBC and ODBCng both are showing that they can readily keep up with DBD::Pg in terms of basic performance. Oddly, the ODBCng buffering option doesn't seem to offer as much benefit as we thought it would but I need to test that with larger (wider) data sets. The versions tested were: PGDG: 08.01.0200-2 ODBCng: Rev 76 DBD::Pg: 1.49 The machine was: AMD X2 3800 2 Gig of Ram PostgreSQL 8.1.4 Each test is ran in isolation, meaning that we create our connection, run our cleanup, run our test and then disconnect. For each test, thus no caching is occurring from PostgreSQL. Anyway, these test clearly show that both ODBC drivers for PostgreSQL have shown drastic improvement over the last 12 months. Sincerely, Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/
> -----Original Message----- > From: pgsql-odbc-owner@postgresql.org > [mailto:pgsql-odbc-owner@postgresql.org] On Behalf Of Joshua D. Drake > Sent: 28 September 2006 23:26 > To: pgsql-odbc@postgresql.org > Subject: [ODBC] PGDG ODBC, ODBCng, DBD::Pg > > Hello, > > I was running some basic benchmarks in comparison of the three > aforementioned products. I thought I would drop a link here for people > to compare: > > http://projects.commandprompt.com/public/odbcng/wiki/Performance > > One thing of note that is very positive IMHO is that PGDG ODBC and > ODBCng both are showing that they can readily keep up with DBD::Pg in > terms of basic performance. > > Oddly, the ODBCng buffering option doesn't seem to offer as > much benefit > as we thought it would but I need to test that with larger > (wider) data > sets. > > The versions tested were: > > PGDG: 08.01.0200-2 > ODBCng: Rev 76 > DBD::Pg: 1.49 Any chance of running the same test again against psqlODBC 08.02.0100? It's a totally different architecture to the 08.01 series. You might also try something like odbc-bench for some more realistic tests. Cheers, Dave.
>>
>> The versions tested were:
>>
>> PGDG: 08.01.0200-2
>> ODBCng: Rev 76
>> DBD::Pg: 1.49
>
> Any chance of running the same test again against psqlODBC 08.02.0100?
> It's a totally different architecture to the 08.01 series.
Be happy to.
>
> You might also try something like odbc-bench for some more realistic
> tests.
You bet.
Sincerely,
Joshua D. Drake
>
> Cheers, Dave.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/
>> The versions tested were:
>>
>> PGDG: 08.01.0200-2
>> ODBCng: Rev 76
>> DBD::Pg: 1.49
>
> Any chance of running the same test again against psqlODBC 08.02.0100?
> It's a totally different architecture to the 08.01 series.
>
> You might also try something like odbc-bench for some more realistic
> tests.
Either I am cranked, or odbc-bench doesn't appear to support
postgresql... do you a compatible schema for them?
Joshua D. Drake
>
> Cheers, Dave.
>
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/
Joshua D. Drake wrote:
>>> The versions tested were:
>>>
>>> PGDG: 08.01.0200-2
>>> ODBCng: Rev 76
>>> DBD::Pg: 1.49
>> Any chance of running the same test again against psqlODBC 08.02.0100?
>> It's a totally different architecture to the 08.01 series.
>>
>> You might also try something like odbc-bench for some more realistic
>> tests.
>
> Either I am cranked, or odbc-bench doesn't appear to support
> postgresql... do you a compatible schema for them?
Dave per your request, PostgreSQL ODBC versus ODBCng. Unfortunately I
couldn't test with 08.02 because I am on 64bit. I ran 6 tests. The tests
are identical except:
Async:
ODBCng
ODBCng Buffering mode
PostgreSQL ODBC
Not Async:
ODBCng
ODBCng Buffering mode
PostgreSQL ODBC
jd@scratch:~/ODBCtest$ ./testodbc.pl
==========================================================
Initializing ODBC-Bench
Done
ODBCng Buffering Async
==========================================================
Connecting to CommandLine : DSN=<odbcng_buffer> UID=<postgres>
Driver : 00.99.0067 (libmodbc.so)
Connection to CommandLine opened
Connection to CommandLine closed
Connecting to CommandLine : DSN=<odbcng_buffer> UID=<postgres>
Driver : 00.99.0067 (libmodbc.so)
Connection to CommandLine opened
CommandLine - PostgreSQL(libmodbc.so) - all 10 TPC-A Threads completed
successfully.
Calculating statistics:
SQL options used: 10 Threads/Async/Params
Transaction time: 61.000000
Environmental overhead: -1.000000
Total transactions: 66224
Transactions per second: 1085.639404
% less than 1 second: 99.983391
% 1 < n < 2 seconds: 0.016610
Average processing time: 0.000921
Connection to CommandLine closed
==========================================================
ODBCng No Buffering Async
==========================================================
Connecting to CommandLine : DSN=<odbcng_nobuffer> UID=<postgres>
Driver : 00.99.0067 (libmodbc.so)
Connection to CommandLine opened
Connection to CommandLine closed
Connecting to CommandLine : DSN=<odbcng_nobuffer> UID=<postgres>
Driver : 00.99.0067 (libmodbc.so)
Connection to CommandLine opened
CommandLine - PostgreSQL(libmodbc.so) - all 10 TPC-A Threads completed
successfully.
Calculating statistics:
SQL options used: 10 Threads/Async/Params
Transaction time: 61.000000
Environmental overhead: -1.000000
Total transactions: 63063
Transactions per second: 1033.819702
% less than 1 second: 99.976212
% 1 < n < 2 seconds: 0.023786
Average processing time: 0.000967
Connection to CommandLine closed
==========================================================
PostgreSQL ODBC Async
==========================================================
Connecting to CommandLine : DSN=<pgdg> UID=<postgres>
Driver : 08.01.0200 (libpsqlodbc.so)
Connection to CommandLine opened
Connection to CommandLine closed
Connecting to CommandLine : DSN=<pgdg> UID=<postgres>
Driver : 08.01.0200 (libpsqlodbc.so)
Connection to CommandLine opened
CommandLine - PostgreSQL(libpsqlodbc.so) - all 10 TPC-A Threads
completed successfully.
Calculating statistics:
SQL options used: 10 Threads/Async/Params
Transaction time: 61.000000
Environmental overhead: -1.000000
Total transactions: 37317
Transactions per second: 611.754089
% less than 1 second: 99.965164
% 1 < n < 2 seconds: 0.034837
Average processing time: 0.001635
Connection to CommandLine closed
==========================================================
ODBCng Buffering
==========================================================
Connecting to CommandLine : DSN=<odbcng_buffer> UID=<postgres>
Driver : 00.99.0067 (libmodbc.so)
Connection to CommandLine opened
Connection to CommandLine closed
Connecting to CommandLine : DSN=<odbcng_buffer> UID=<postgres>
Driver : 00.99.0067 (libmodbc.so)
Connection to CommandLine opened
CommandLine - PostgreSQL(libmodbc.so) - all 10 TPC-A Threads completed
successfully.
Calculating statistics:
SQL options used: 10 Threads/Params
Transaction time: 61.000000
Environmental overhead: -1.000000
Total transactions: 64843
Transactions per second: 1063.000000
% less than 1 second: 99.976868
% 1 < n < 2 seconds: 0.023133
Average processing time: 0.000941
Connection to CommandLine closed
==========================================================
ODBCng No Buffering
==========================================================
Connecting to CommandLine : DSN=<odbcng_nobuffer> UID=<postgres>
Driver : 00.99.0067 (libmodbc.so)
Connection to CommandLine opened
Connection to CommandLine closed
Connecting to CommandLine : DSN=<odbcng_nobuffer> UID=<postgres>
Driver : 00.99.0067 (libmodbc.so)
Connection to CommandLine opened
CommandLine - PostgreSQL(libmodbc.so) - all 10 TPC-A Threads completed
successfully.
Calculating statistics:
SQL options used: 10 Threads/Params
Transaction time: 61.000000
Environmental overhead: -1.000000
Total transactions: 63025
Transactions per second: 1033.196777
% less than 1 second: 99.979370
% 1 < n < 2 seconds: 0.020627
Average processing time: 0.000968
Connection to CommandLine closed
==========================================================
PostgreSQL ODBC
==========================================================
Connecting to CommandLine : DSN=<pgdg> UID=<postgres>
Driver : 08.01.0200 (libpsqlodbc.so)
Connection to CommandLine opened
Connection to CommandLine closed
Connecting to CommandLine : DSN=<pgdg> UID=<postgres>
Driver : 08.01.0200 (libpsqlodbc.so)
Connection to CommandLine opened
CommandLine - PostgreSQL(libpsqlodbc.so) - all 10 TPC-A Threads
completed successfully.
Calculating statistics:
SQL options used: 10 Threads/Params
Transaction time: 61.000000
Environmental overhead: -1.000000
Total transactions: 35435
Transactions per second: 580.901611
% less than 1 second: 99.932274
% 1 < n < 2 seconds: 0.067730
Average processing time: 0.001721
Connection to CommandLine closed
=========================================================
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/