Re: Mutli-threading and performance of ODBC

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(at)postgresql(dot)org
[mailto:pgsql-odbc-owner(at)postgresql(dot)org]On Behalf Of Dave Page
Sent: Tuesday, March 09, 2004 6:21 PM
To: Tom Lane; Stergios Zissakis
Cc: pgsql-odbc(at)postgresql(dot)org; Kostas Lykiardopoulos; Dimitris Pantermalis
Subject: Re: [ODBC] Mutli-threading and performance of ODBC

> -----Original Message-----
> From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
> Sent: 09 March 2004 15:57
> To: Stergios Zissakis
> Cc: pgsql-odbc(at)postgresql(dot)org; Kostas Lykiardopoulos;
> Dimitris Pantermalis
> Subject: Re: [ODBC] Mutli-threading and performance of ODBC
>
> "Stergios Zissakis" <szis(at)intranet(dot)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