My ODBC concurrent Queries

--00163631094fa2b7f4046aba5ff8
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

Hi,
In my application, the mysql server is connected via ODBC and we run
multiple threads executing multiple queries with the mysql server. There is
an issue in our application when we execute a bulk query (more than 100,000
rows in resultset) and when the query takes a longer time for execute, all
the other threads seem to just hang on their db operations.

We are even unable to get past the 'use database' query in most cases from
other threads. Is this a limitation on the ODBC implementation in linux or
have we failed to turn-on any options while building our ODBC drivers, do
let us know.

In Windows, we are able to execute simultaneous queries using ODBC. This
test environment uses all pre-compiled binaries from MySQL website.

In Linux, we are facing the above issues

Linux Flavor - Cent OS 5.2
MySQL Server version - 5.1.30
unixODBC version - 2.2.11
myODBC version - 3.51.27

All Linux binaries are compiled in the same system as they are running.

When we execute two queries as separate processes (same executable started
as different processes), we are able to do so. But, when we create two
separate threads within the same process, then the above hang up arises. Is
there any particular option in building our application we have missed, do
let us know,

Regards,
Jayavasanthan J

--00163631094fa2b7f4046aba5ff8--

RE: My ODBC concurrent Queries

SGksDQp0ZWxsIHVzIGEgZmV3IG1vcmUgaW1wbGVtZW50YXRpb24gZGV0YWls cyBvbiB5b3VyIGFw
cDogDQpJbiBwYXJ0aWN1bGFyLCBkbyB5b3VyIG4gdGhyZWFkcyBzaGFyZSBv bmUgY29tbW9uIGRi
LWNvbm5lY3Rpb24/DQpEbyB5b3UgdXNlIHRoZSBzYW1lIGNvZGViYXNlIGZv ciB3aW4gJiBsaW51
eD8NCg0KYXJtaW4uDQoNCg0KPiAtLS0tLU9yaWdpbmFsIE1lc3NhZ2UtLS0t LQ0KPiBGcm9tOiBK
IEpheWF2YXNhbnRoYW4gW21haWx0bzpqYXlhdmFzYW50aGFuQGdtYWlsLmNv bV0NCj4gU2VudDog
TW9uZGF5LCBNYXkgMjUsIDIwMDkgMTI6NTAgUE0NCj4gVG86IG15b2RiY0Bs aXN0cy5teXNxbC5j
b20NCj4gU3ViamVjdDogTXkgT0RCQyBjb25jdXJyZW50IFF1ZXJpZXMNCj4g DQo+IEhpLA0KPiBJ
biBteSBhcHBsaWNhdGlvbiwgdGhlIG15c3FsIHNlcnZlciBpcyBjb25uZWN0 ZWQgdmlhIE9EQkMg
YW5kIHdlIHJ1bg0KPiBtdWx0aXBsZSB0aHJlYWRzIGV4ZWN1dGluZyBtdWx0 aXBsZSBxdWVyaWVz
IHdpdGggdGhlIG15c3FsIHNlcnZlci4NCj4gVGhlcmUgaXMNCj4gYW4gaXNz dWUgaW4gb3VyIGFw
cGxpY2F0aW9uIHdoZW4gd2UgZXhlY3V0ZSBhIGJ1bGsgcXVlcnkgKG1vcmUg dGhhbg0KPiAxMDAs
MDAwDQo+IHJvd3MgaW4gcmVzdWx0c2V0KSBhbmQgd2hlbiB0aGUgcXVlcnkg dGFrZXMgYSBsb25n
ZXIgdGltZSBmb3IgZXhlY3V0ZSwNCj4gYWxsDQo+IHRoZSBvdGhlciB0aHJl YWRzIHNlZW0gdG8g
anVzdCBoYW5nIG9uIHRoZWlyIGRiIG9wZXJhdGlvbnMuDQo+IA0KPiBXZSBh cmUgZXZlbiB1bmFi
bGUgdG8gZ2V0IHBhc3QgdGhlICd1c2UgZGF0YWJhc2UnIHF1ZXJ5IGluIG1v c3QgY2FzZXMNCj4g
ZnJvbQ0KPiBvdGhlciB0aHJlYWRzLiBJcyB0aGlzIGEgbGltaXRhdGlvbiBv biB0aGUgT0RCQyBp
bXBsZW1lbnRhdGlvbiBpbiBsaW51eA0KPiBvcg0KPiBoYXZlIHdlIGZhaWxl ZCB0byB0dXJuLW9u
IGFueSBvcHRpb25zIHdoaWxlIGJ1aWxkaW5nIG91ciBPREJDIGRyaXZlcnMs DQo+IGRvDQo+IGxl
dCB1cyBrbm93Lg0KPiANCj4gSW4gV2luZG93cywgd2UgYXJlIGFibGUgdG8g ZXhlY3V0ZSBzaW11
bHRhbmVvdXMgcXVlcmllcyB1c2luZyBPREJDLg0KPiBUaGlzDQo+IHRlc3Qg ZW52aXJvbm1lbnQg
dXNlcyBhbGwgcHJlLWNvbXBpbGVkIGJpbmFyaWVzIGZyb20gTXlTUUwgd2Vi c2l0ZS4NCj4gDQo+
IEluIExpbnV4LCB3ZSBhcmUgZmFjaW5nIHRoZSBhYm92ZSBpc3N1ZXMNCj4g DQo+IExpbnV4IEZs
YXZvciAtIENlbnQgT1MgNS4yDQo+IE15U1FMIFNlcnZlciB2ZXJzaW9uIC0g NS4xLjMwDQo+IHVu
aXhPREJDIHZlcnNpb24gLSAyLjIuMTENCj4gbXlPREJDIHZlcnNpb24gLSAz LjUxLjI3DQo+IA0K
PiBBbGwgTGludXggYmluYXJpZXMgYXJlIGNvbXBpbGVkIGluIHRoZSBzYW1l IHN5c3RlbSBhcyB0
aGV5IGFyZSBydW5uaW5nLg0KPiANCj4gV2hlbiB3ZSBleGVjdXRlIHR3byBx dWVyaWVzIGFzIHNl
cGFyYXRlIHByb2Nlc3NlcyAoc2FtZSBleGVjdXRhYmxlDQo+IHN0YXJ0ZWQN Cj4gYXMgZGlmZmVy
ZW50IHByb2Nlc3NlcyksIHdlIGFyZSBhYmxlIHRvIGRvIHNvLiBCdXQsIHdo ZW4gd2UgY3JlYXRl
IHR3bw0KPiBzZXBhcmF0ZSB0aHJlYWRzIHdpdGhpbiB0aGUgc2FtZSBwcm9j ZXNzLCB0aGVuIHRo
ZSBhYm92ZSBoYW5nIHVwDQo+IGFyaXNlcy4gSXMNCj4gdGhlcmUgYW55IHBh cnRpY3VsYXIgb3B0
aW9uIGluIGJ1aWxkaW5nIG91ciBhcHBsaWNhdGlvbiB3ZSBoYXZlIG1pc3Nl ZCwNCj4gZG8NCj4g
bGV0IHVzIGtub3csDQo+IA0KPiBSZWdhcmRzLA0KPiBKYXlhdmFzYW50aGFu IEoNCg==

Re: My ODBC concurrent Queries

--0016e646058cb7ce8a046abb8f82
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

Hi Armin,
No, the n threads use different connections,

And Yes, the codebase is the same for Windows & Linux.

BTW, additional information, from windows client connecting Windows & Linux
MySQL Servers, this problem doesn't arise. But only with Linux clients
connecting to Windows or Linux MySQL Servers, this problem arises,

I am trying to prepare a testcase out of this, and will put it up shortly,
do let me know if I have missed something,

Regards,
Jay


2009/5/25 Armin Schöffmann

> Hi,
> tell us a few more implementation details on your app:
> In particular, do your n threads share one common db-connection?
> Do you use the same codebase for win & linux?
>
> armin.
>
>
> > -----Original Message-----
> > From: J Jayavasanthan [mailto:jayavasanthan@gmail.com]
> > Sent: Monday, May 25, 2009 12:50 PM
> > To: myodbc@lists.mysql.com
> > Subject: My ODBC concurrent Queries
> >
> > Hi,
> > In my application, the mysql server is connected via ODBC and we run
> > multiple threads executing multiple queries with the mysql server.
> > There is
> > an issue in our application when we execute a bulk query (more than
> > 100,000
> > rows in resultset) and when the query takes a longer time for execute,
> > all
> > the other threads seem to just hang on their db operations.
> >
> > We are even unable to get past the 'use database' query in most cases
> > from
> > other threads. Is this a limitation on the ODBC implementation in linux
> > or
> > have we failed to turn-on any options while building our ODBC drivers,
> > do
> > let us know.
> >
> > In Windows, we are able to execute simultaneous queries using ODBC.
> > This
> > test environment uses all pre-compiled binaries from MySQL website.
> >
> > In Linux, we are facing the above issues
> >
> > Linux Flavor - Cent OS 5.2
> > MySQL Server version - 5.1.30
> > unixODBC version - 2.2.11
> > myODBC version - 3.51.27
> >
> > All Linux binaries are compiled in the same system as they are running.
> >
> > When we execute two queries as separate processes (same executable
> > started
> > as different processes), we are able to do so. But, when we create two
> > separate threads within the same process, then the above hang up
> > arises. Is
> > there any particular option in building our application we have missed,
> > do
> > let us know,
> >
> > Regards,
> > Jayavasanthan J
>



--=20
first me then home
first home then country
first country then world
fools always read inverse

--0016e646058cb7ce8a046abb8f82--

Re: My ODBC concurrent Queries

J Jayavasanthan wrote:
> Hi Armin,
> No, the n threads use different connections,
>
> And Yes, the codebase is the same for Windows & Linux.
>
> BTW, additional information, from windows client connecting Windows & Linux
> MySQL Servers, this problem doesn't arise. But only with Linux clients
> connecting to Windows or Linux MySQL Servers, this problem arises,
>
> I am trying to prepare a testcase out of this, and will put it up shortly,
> do let me know if I have missed something,
>
> Regards,
> Jay
>
>
> 2009/5/25 Armin Schöffmann
>
>> Hi,
>> tell us a few more implementation details on your app:
>> In particular, do your n threads share one common db-connection?
>> Do you use the same codebase for win & linux?
>>
>> armin.
>>
>>
>>> -----Original Message-----
>>> From: J Jayavasanthan [mailto:jayavasanthan@gmail.com]
>>> Sent: Monday, May 25, 2009 12:50 PM
>>> To: myodbc@lists.mysql.com
>>> Subject: My ODBC concurrent Queries
>>>
>>> Hi,
>>> In my application, the mysql server is connected via ODBC and we run
>>> multiple threads executing multiple queries with the mysql server.
>>> There is
>>> an issue in our application when we execute a bulk query (more than
>>> 100,000
>>> rows in resultset) and when the query takes a longer time for execute,
>>> all
>>> the other threads seem to just hang on their db operations.
>>>
>>> We are even unable to get past the 'use database' query in most cases
>>> from
>>> other threads. Is this a limitation on the ODBC implementation in linux
>>> or
>>> have we failed to turn-on any options while building our ODBC drivers,
>>> do
>>> let us know.
>>>
>>> In Windows, we are able to execute simultaneous queries using ODBC.
>>> This
>>> test environment uses all pre-compiled binaries from MySQL website.
>>>
>>> In Linux, we are facing the above issues
>>>
>>> Linux Flavor - Cent OS 5.2
>>> MySQL Server version - 5.1.30
>>> unixODBC version - 2.2.11
>>> myODBC version - 3.51.27
>>>
>>> All Linux binaries are compiled in the same system as they are running.
>>>
>>> When we execute two queries as separate processes (same executable
>>> started
>>> as different processes), we are able to do so. But, when we create two
>>> separate threads within the same process, then the above hang up
>>> arises. Is
>>> there any particular option in building our application we have missed,
>>> do
>>> let us know,
>>>
>>> Regards,
>>> Jayavasanthan J
>
>
>

Have you examined the threading options in unixODBC? i.e.,

Threading = N

for a driver in your odbcinst.ini file. You can read the possible values
from one of the source modules for unixODBC - I think it is _handles.c.

If the mysql ODBC driver is thread-safe then you can set Threading = 0
and unixODBC will do nothing to protect the ODBC driver but I cannot
remember right now what it defaults to - probably 1 or 2 for environment
or connection.

Also, you might want to look at whether you share the same environment
between threads even if they are using different connections (see
SQLAllocHandle for an environment).

Martin
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com

--
MySQL ODBC Mailing List
For list archives: http://lists.mysql.com/myodbc
To unsubscribe: http://lists.mysql.com/myodbc?unsub=gcdmo-myodbc@m.gmane.org

Re: My ODBC concurrent Queries

--0016e64601d01a99f5046ae300a2
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

Sorry I forwarded this response only to Martin,
And I have found a resolution to the above problem with Threading =3D0/1/2,
but I am facing other problems related to connection pooling.

I am following up with that issue on the other thread, hopefully I will fin=
d
the resolution soon,

Once again I apologize for not closing down this thread.

Regards,
Jay



---------- Forwarded message ----------
From: J Jayavasanthan
Date: Tue, May 26, 2009 at 5:42 PM
Subject: Re: My ODBC concurrent Queries
To: Martin Evans


I think this resolves my query,

Thanks Martin, Thanks for your inputs
Regards,
Jay


On Tue, May 26, 2009 at 1:08 PM, Martin Evans wr=
ote:

> J Jayavasanthan wrote:
> > Hi Armin,
> > No, the n threads use different connections,
> >
> > And Yes, the codebase is the same for Windows & Linux.
> >
> > BTW, additional information, from windows client connecting Windows &
> Linux
> > MySQL Servers, this problem doesn't arise. But only with Linux clients
> > connecting to Windows or Linux MySQL Servers, this problem arises,
> >
> > I am trying to prepare a testcase out of this, and will put it up
> shortly,
> > do let me know if I have missed something,
> >
> > Regards,
> > Jay
> >
> >
> > 2009/5/25 Armin Schöffmann
> >
> >> Hi,
> >> tell us a few more implementation details on your app:
> >> In particular, do your n threads share one common db-connection?
> >> Do you use the same codebase for win & linux?
> >>
> >> armin.
> >>
> >>
> >>> -----Original Message-----
> >>> From: J Jayavasanthan [mailto:jayavasanthan@gmail.com]
> >>> Sent: Monday, May 25, 2009 12:50 PM
> >>> To: myodbc@lists.mysql.com
> >>> Subject: My ODBC concurrent Queries
> >>>
> >>> Hi,
> >>> In my application, the mysql server is connected via ODBC and we run
> >>> multiple threads executing multiple queries with the mysql server.
> >>> There is
> >>> an issue in our application when we execute a bulk query (more than
> >>> 100,000
> >>> rows in resultset) and when the query takes a longer time for execute=
,
> >>> all
> >>> the other threads seem to just hang on their db operations.
> >>>
> >>> We are even unable to get past the 'use database' query in most cases
> >>> from
> >>> other threads. Is this a limitation on the ODBC implementation in lin=
ux
> >>> or
> >>> have we failed to turn-on any options while building our ODBC drivers=
,
> >>> do
> >>> let us know.
> >>>
> >>> In Windows, we are able to execute simultaneous queries using ODBC.
> >>> This
> >>> test environment uses all pre-compiled binaries from MySQL website.
> >>>
> >>> In Linux, we are facing the above issues
> >>>
> >>> Linux Flavor - Cent OS 5.2
> >>> MySQL Server version - 5.1.30
> >>> unixODBC version - 2.2.11
> >>> myODBC version - 3.51.27
> >>>
> >>> All Linux binaries are compiled in the same system as they are runnin=
g.
> >>>
> >>> When we execute two queries as separate processes (same executable
> >>> started
> >>> as different processes), we are able to do so. But, when we create tw=
o
> >>> separate threads within the same process, then the above hang up
> >>> arises. Is
> >>> there any particular option in building our application we have misse=
d,
> >>> do
> >>> let us know,
> >>>
> >>> Regards,
> >>> Jayavasanthan J
> >
> >
> >
>
> Have you examined the threading options in unixODBC? i.e.,
>
> Threading =3D N
>
> for a driver in your odbcinst.ini file. You can read the possible values
> from one of the source modules for unixODBC - I think it is _handles.c.
>
> If the mysql ODBC driver is thread-safe then you can set Threading =3D 0
> and unixODBC will do nothing to protect the ODBC driver but I cannot
> remember right now what it defaults to - probably 1 or 2 for environment
> or connection.
>
> Also, you might want to look at whether you share the same environment
> between threads even if they are using different connections (see
> SQLAllocHandle for an environment).
>
> Martin
> --
> Martin J. Evans
> Easysoft Limited
> http://www.easysoft.com
>
>


--=20
first me then home
first home then country
first country then world
fools always read inverse

--0016e64601d01a99f5046ae300a2--