Thread: Mutli-threading and performance of ODBC

Dear pgsql-odbc support

I am currently working on a pilot for porting an application to
PostgreSQL. In the pilot I am examining the two interfaces ODBC and
ECPG, and evaluating their performance. I am aware that there is a
performance penalty for using ODBC, and I would like to pose a few
questions, hoping that I can further improve the performace using ODBC:

1. Studing the postgreSQL log, it is apparent that the the ODBC driver
switches off the Genetic Query Optimiser (geqo) and tries to switch on
another optimiser called (ksqo) which fails. What is the ksqo optimiser?
Why does it switch to a different optimiser? And why does the switch to
the ksqo optimiser fail?

2. Also from the postgreSQL log, it is apparent that with the ODBC
driver, client threads tend to get "serialised", while with ECPG threads
execute more concurrently (context switches occur more often). I have
created two test harnesses (see the attached .cc files) one using ODBC
via OTL4 and the other using ECPG. Specifically, ECPG tends to
"multitask" threads to the degree that some threads get to be active
twice, inserting some rows the first time and the rest the second time.
This is more noticable for a small number of threads with say 10 inserts
each; in such case thread activity with ODBC is strictly serialised,
while with ECPG multitasking is still evident. Does this serialisation
have any performance implications? (Is this maybe related to the
switched-off query optimiser mentioned above?).

3. Also, using ODBC, in the log file of PostgreSQL, it is seen that a
lot of blank statements are generated. Can these blank statements affect
the performance?

My system specifications are:
Solaris 8 (5.8) with the 113261-02 patch applied (as required by
unixODBC)
PostgreSQL 7.4
unixODBC 2.2.8 using the PostgreSQL driver that comes with it
(libodbcpsql.so.2.0.0, libodbcpsqlS.so.1.0.0)
OTL Version 4

I would appreciate your comments/help.

Regards,

Stergios Zisakis

NMS Department
Intracom
Athens
GREECE.

The attached file contains the following items:
insert_ecpg.pgc           #the ECPG test harness
insert_odbc.cc            #the ODBC test harness
insert_ecpg_1000rows.log  #the PGSQL log using ECPG with 10 threads each
                          #inserting 100 rows.
insert_odbc_1000rows.log  #the PGSQL log using ODBC with 10 threads each
                          #inserting 100 rows.
threads_ecpg.stats        #log extracts demonstrating thread competition
                          #in terms of how many rows are inserted
threads_odbc.stats        #each time a thread gets active.

"Stergios Zissakis" <szis@intranet.gr> writes:
> 1. Studing the postgreSQL log, it is apparent that the the ODBC driver
> switches off the Genetic Query Optimiser (geqo) and tries to switch on
> another optimiser called (ksqo) which fails. What is the ksqo optimiser?

ksqo was a hack that disappeared several releases back.  I do not know
why ODBC still has a reference to it (or why it tried to force it on
in the first place; IMHO a driver has no business making that kind of
decision).

I would recommend removing both of those SETs from the driver code.

> 3. Also, using ODBC, in the log file of PostgreSQL, it is seen that a
> lot of blank statements are generated. Can these blank statements affect
> the performance?

The backend won't spend much time on a blank statement, but if a
separate network round trip is being incurred for each one, that could
get a tad expensive ...

            regards, tom lane

> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Sent: 09 March 2004 15:57
> To: Stergios Zissakis
> Cc: pgsql-odbc@postgresql.org; Kostas Lykiardopoulos;
> Dimitris Pantermalis
> Subject: Re: [ODBC] Mutli-threading and performance of ODBC
>
> "Stergios Zissakis" <szis@intranet.gr> writes:
> > 1. Studing the postgreSQL log, it is apparent that the the
> ODBC driver
> > switches off the Genetic Query Optimiser (geqo) and tries
> to switch on
> > another optimiser called (ksqo) which fails. What is the
> ksqo optimiser?
>
> ksqo was a hack that disappeared several releases back.  I do
> not know why ODBC still has a reference to it (or why it
> tried to force it on in the first place; IMHO a driver has no
> business making that kind of decision).
>
> I would recommend removing both of those SETs from the driver code.

I removed KSQO long ago for servers >= 7.3 - for < 7.3 it is a config
option. GEQO is set via a config option - it is not forced on.

> > 3. Also, using ODBC, in the log file of PostgreSQL, it is
> seen that a
> > lot of blank statements are generated. Can these blank statements
> > affect the performance?
>
> The backend won't spend much time on a blank statement, but
> if a separate network round trip is being incurred for each
> one, that could get a tad expensive ...

There is an empty connection sent during connection setup to check that
the database is valid. I can't see any others in a commlog here though.

Regards, Dave.

> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Sent: 09 March 2004 16:35
> To: Dave Page
> Cc: Stergios Zissakis; pgsql-odbc@postgresql.org; Kostas
> Lykiardopoulos; Dimitris Pantermalis
> Subject: Re: [ODBC] Mutli-threading and performance of ODBC
>
> "Dave Page" <dpage@vale-housing.co.uk> writes:
> > I removed KSQO long ago for servers >= 7.3 - for < 7.3 it
> is a config
> > option. GEQO is set via a config option - it is not forced on.
>
> I just looked through the unixODBC 2.2.8 sources, and I can't
> see anywhere that the behavior for KSQO is made
> version-dependent.  It seems to be there and default to ON
> regardless of backend version.

It's not in unixODBC, it's in psqlODBC (and the above should have read
7.1).

From:
http://gborg.postgresql.org/project/psqlodbc/cvs/co.php/psqlodbc/connect
ion.c?r=1.67

    /* KSQO (not applicable to 7.1+ - DJP 21/06/2002) */
    if (ci->drivers.ksqo && PG_VERSION_LT(self, 7.1))
    {
        result = PGAPI_ExecDirect(hstmt, "set ksqo to 'ON'",
SQL_NTS);
        if ((result != SQL_SUCCESS) && (result !=
SQL_SUCCESS_WITH_INFO))
            status = FALSE;

        mylog("%s: result %d, status %d from set ksqo\n", func,
result, status);

    }

It's in tip as well...

Regards, Dave.

"Dave Page" <dpage@vale-housing.co.uk> writes:
> It's not in unixODBC, it's in psqlODBC (and the above should have read
> 7.1).

Um.  The complainant was saying he was using unixODBC, which I take to
mean unixODBC's embedded Postgres driver.  I had not realized that the
latter is out of date.  We ought to see what can be done about syncing
up, or perhaps removing the embedded driver from unixODBC altogether.

            regards, tom lane

"Dave Page" <dpage@vale-housing.co.uk> writes:
> I removed KSQO long ago for servers >= 7.3 - for < 7.3 it is a config
> option. GEQO is set via a config option - it is not forced on.

I just looked through the unixODBC 2.2.8 sources, and I can't see
anywhere that the behavior for KSQO is made version-dependent.  It seems
to be there and default to ON regardless of backend version.

            regards, tom lane

> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Sent: 09 March 2004 16:58
> To: Dave Page
> Cc: Stergios Zissakis; pgsql-odbc@postgresql.org; Kostas
> Lykiardopoulos; Dimitris Pantermalis
> Subject: Re: [ODBC] Mutli-threading and performance of ODBC
>
> "Dave Page" <dpage@vale-housing.co.uk> writes:
> > It's not in unixODBC, it's in psqlODBC (and the above
> should have read
> > 7.1).
>
> Um.  The complainant was saying he was using unixODBC, which
> I take to mean unixODBC's embedded Postgres driver.  I had
> not realized that the latter is out of date.  We ought to see
> what can be done about syncing up, or perhaps removing the
> embedded driver from unixODBC altogether.

I thought unixODBC was merely the driver manager - didn't realise they
had some drivers as well, hence why I assumed the problem was with
psqlODBC.

Regards, Dave.

"Dave Page" <dpage@vale-housing.co.uk> writes:
> I thought unixODBC was merely the driver manager - didn't realise they
> had some drivers as well, hence why I assumed the problem was with
> psqlODBC.

No, they've got a pile of embedded drivers:

$ du unixODBC-2.2.8/Drivers
132     unixODBC-2.2.8/Drivers/MySQL/samples
1928    unixODBC-2.2.8/Drivers/MySQL
994     unixODBC-2.2.8/Drivers/PostgreSQL
1054    unixODBC-2.2.8/Drivers/Postgre7.1
88      unixODBC-2.2.8/Drivers/txt/doc
736     unixODBC-2.2.8/Drivers/txt
22      unixODBC-2.2.8/Drivers/odbc
436     unixODBC-2.2.8/Drivers/template
516     unixODBC-2.2.8/Drivers/MiniSQL
570     unixODBC-2.2.8/Drivers/nn
6292    unixODBC-2.2.8/Drivers

I have not tried to compare carefully, but judging by the snippet you
just posted versus the KSQO-related code I just grepped for, what's in
unixODBC is a rather distant ancestor of the present psqlODBC.  I am not
too sure what the MySQL driver is doing in there either ... I know there
is a separately maintained MyODBC package ...

            regards, tom lane

Dear all,

This is just a follow up to my previous post.
I built and installed psqlodbc-07-03-0200 which cured the blank statements
problem.
As you previously said GEQO is switched off by default; I figured this out
by looking at the soruce. What confused me a bit is that the odbc.ini entry
for
GEQO is called "Optimizer" and if one wants it on, they include the
following in thier odbc.ini: "Optimizer = 0". This is  a bit misleading as
someone can
interpret it as switching off an optimizer. The internal flag that
corresponds to this setting is called "disable_optimizer" and by setting
this to 0 will not
switch off the GEQO. Maybe you should consider changing the name of the
odbc.ini entry to something more specific; like "DisableOptemizer".

I also resolved the thread competition problem. This is an issue of the
unixODBC driver manager and it is controlled by the "Threading" entry in the
odbcinst.ini file. This should be set to 0 to allow the driver to take care
of multi-threading issues. Here are the comments of
<unixODBC_src>/DriverManaer/__handles.c:

/*
 * use just one mutex for all the lists, this avoids any issues
 * with deadlocks, the performance issue should be minimal, if it
 * turns out to be a problem, we can readdress this
 *
 * We also have a mutex to protect the connection pooling code
 *
 * If compiled with thread support the DM allows four different
 * thread strategies
 *
 * Level 0 - Only the DM internal structures are protected
 * the driver is assumed to take care of it's self
 *
 * Level 1 - The driver is protected down to the statement level
 * each statement will be protected, and the same for the connect
 * level for connect functions, note that descriptors are considered
 * equal to statements when it comes to thread protection.
 *
 * Level 2 - The driver is protected at the connection level. only
 * one thread can be in a particular driver at one time
 *
 * Level 3 - The driver is protected at the env level, only one thing
 * at a time.
 *
 * By default the driver open connections with a lock level of 3,
 * this can be changed by adding the line
 *
 * Threading = N
 *
 * to the driver entry in odbcinst.ini, where N is the locking level
 * (0-3)
 *
 */

I just thought I'd share all this with you and anyone else that might come
across such issues.

Thanks for your help.

Regards,
Stergios Zissakis


-----Original Message-----
From: pgsql-odbc-owner@postgresql.org
[mailto:pgsql-odbc-owner@postgresql.org]On Behalf Of Dave Page
Sent: Tuesday, March 09, 2004 6:21 PM
To: Tom Lane; Stergios Zissakis
Cc: pgsql-odbc@postgresql.org; Kostas Lykiardopoulos; Dimitris Pantermalis
Subject: Re: [ODBC] Mutli-threading and performance of ODBC




> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Sent: 09 March 2004 15:57
> To: Stergios Zissakis
> Cc: pgsql-odbc@postgresql.org; Kostas Lykiardopoulos;
> Dimitris Pantermalis
> Subject: Re: [ODBC] Mutli-threading and performance of ODBC
>
> "Stergios Zissakis" <szis@intranet.gr> writes:
> > 1. Studing the postgreSQL log, it is apparent that the the
> ODBC driver
> > switches off the Genetic Query Optimiser (geqo) and tries
> to switch on
> > another optimiser called (ksqo) which fails. What is the
> ksqo optimiser?
>
> ksqo was a hack that disappeared several releases back.  I do
> not know why ODBC still has a reference to it (or why it
> tried to force it on in the first place; IMHO a driver has no
> business making that kind of decision).
>
> I would recommend removing both of those SETs from the driver code.

I removed KSQO long ago for servers >= 7.3 - for < 7.3 it is a config
option. GEQO is set via a config option - it is not forced on.

> > 3. Also, using ODBC, in the log file of PostgreSQL, it is
> seen that a
> > lot of blank statements are generated. Can these blank statements
> > affect the performance?
>
> The backend won't spend much time on a blank statement, but
> if a separate network round trip is being incurred for each
> one, that could get a tad expensive ...

There is an empty connection sent during connection setup to check that
the database is valid. I can't see any others in a commlog here though.

Regards, Dave.

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
      joining column's datatypes do not match