Postgres Pro
Downloads
Home   >   mailing lists

Thread: Connection Pooling Problems

Connection Pooling Problems

From
"James M Doherty PGADMIN"
Date:
I have an application that uses the ODBC interface from a window XP machine. I have set connection pooling on the pgsql driver to on with
a timeout of 120 seconds. This is being called from an Excel spread sheet. When the user refreshes the spread sheet I get hundreds of
connections logged in /var/log/messages from the user. There are about 200 queries which try to execute at close to the same time.
 
As a temporary measure I have increased the number of simultaneous connections to 500 to support this, however it does not look like
connection pooling is working correctly. Any ideas on how I might diagnose this, or is there a known fix for this type of problem.
 
I am running 7.4 and 7.03.01.00 of the ODBC driver.
 
Thanks for any help you can give me !
 
Jim
 
 
 
James M Doherty
jim@jdoherty.net
Georgetown, TX 78626
"There is no luck without discipline" IRISH PROVERB
Attachment

Re: Connection Pooling Problems Dave Page

From
"James M Doherty PGADMIN"
Date:
 
DOES ANYONE HAVE ANY EXPERIENCE WITH THIS .... DAVE PAGE? ANYONE...
 
Jim
 
 
----- Original Message -----
Sent: Wednesday, February 16, 2005 12:18 PM
Subject: [ODBC] Connection Pooling Problems

I have an application that uses the ODBC interface from a window XP machine. I have set connection pooling on the pgsql driver to on with
a timeout of 120 seconds. This is being called from an Excel spread sheet. When the user refreshes the spread sheet I get hundreds of
connections logged in /var/log/messages from the user. There are about 200 queries which try to execute at close to the same time.
 
As a temporary measure I have increased the number of simultaneous connections to 500 to support this, however it does not look like
connection pooling is working correctly. Any ideas on how I might diagnose this, or is there a known fix for this type of problem.
 
I am running 7.4 and 7.03.01.00 of the ODBC driver.
 
Thanks for any help you can give me !
 
Jim
 
 
 
James M Doherty
jim@jdoherty.net
Georgetown, TX 78626
"There is no luck without discipline" IRISH PROVERB
Attachment

Re: Connection Pooling Problems Dave Page

From
Jeff Eckermann
Date:
--- James M Doherty PGADMIN <jimmyd@jdoherty.net>
wrote:

>
> DOES ANYONE HAVE ANY EXPERIENCE WITH THIS .... DAVE
> PAGE? ANYONE...

More information would be good.  Relevant code,
relevant extracts from log files.  What is the
application actually doing, when the problem occurs?

>
> Jim
>
>
>   ----- Original Message -----
>   From: James M Doherty PGADMIN
>   To: pgsql-odbc@postgresql.org
>   Sent: Wednesday, February 16, 2005 12:18 PM
>   Subject: [ODBC] Connection Pooling Problems
>
>
>   I have an application that uses the ODBC interface
> from a window XP machine. I have set connection
> pooling on the pgsql driver to on with
>   a timeout of 120 seconds. This is being called
> from an Excel spread sheet. When the user refreshes
> the spread sheet I get hundreds of
>   connections logged in /var/log/messages from the
> user. There are about 200 queries which try to
> execute at close to the same time.
>
>   As a temporary measure I have increased the number
> of simultaneous connections to 500 to support this,
> however it does not look like
>   connection pooling is working correctly. Any ideas
> on how I might diagnose this, or is there a known
> fix for this type of problem.
>
>   I am running 7.4 and 7.03.01.00 of the ODBC
> driver.
>
>   Thanks for any help you can give me !
>
>   Jim
>
>
>
>   James M Doherty
>   jim@jdoherty.net
>   Georgetown, TX 78626
>   "There is no luck without discipline" IRISH
PROVERB




__________________________________
Do you Yahoo!?
Yahoo! Mail - now with 250MB free storage. Learn more.
http://info.mail.yahoo.com/mail_250

Re: Connection Pooling Problems Dave Page

From
"James M Doherty PGADMIN"
Date:
Well,

the application works fine for the time being, however on the server log
reveals:
SMALL
EXTRACT--------------------------------------------------------------------------------------------------
Feb  7 09:17:13 ccbankdb pg[14878]: [2] LOG:  connection received:
host=10.10.124.193 port=1306
Feb  7 09:17:13 ccbankdb pg[14878]: [3] LOG:  connection authorized:
user=sdoherty database=ccbank
Feb  7 09:17:20 ccbankdb pg[14879]: [2] LOG:  connection received:
host=10.10.124.193 port=1307
Feb  7 09:17:20 ccbankdb pg[14879]: [3] LOG:  connection authorized:
user=sdoherty database=ccbank
Feb  7 09:17:21 ccbankdb pg[14880]: [2] LOG:  connection received:
host=10.10.124.193 port=1308
Feb  7 09:17:21 ccbankdb pg[14880]: [3] LOG:  connection authorized:
user=sdoherty database=ccbank
Feb  7 09:17:22 ccbankdb pg[14881]: [2] LOG:  connection received:
host=10.10.124.193 port=1309
Feb  7 09:17:22 ccbankdb pg[14881]: [3] LOG:  connection authorized:
user=sdoherty database=ccbank
Feb  7 09:17:22 ccbankdb pg[14882]: [2] LOG:  connection received:
host=10.10.124.193 port=1310
Feb  7 09:17:22 ccbankdb pg[14882]: [3] LOG:  connection authorized:
user=sdoherty database=ccbank
Feb  7 09:17:23 ccbankdb pg[14883]: [2] LOG:  connection received:
host=10.10.124.193 port=1311
Feb  7 09:17:23 ccbankdb pg[14883]: [3] LOG:  connection authorized:
user=sdoherty database=ccbank
Feb  7 09:17:24 ccbankdb pg[14884]: [2] LOG:  connection received:
host=10.10.124.193 port=1312
Feb  7 09:17:24 ccbankdb pg[14884]: [3] LOG:  connection authorized:
user=sdoherty database=ccbank
Feb  7 09:17:24 ccbankdb pg[14885]: [2] LOG:  connection received:
host=10.10.124.193 port=1313
Feb  7 09:17:24 ccbankdb pg[14885]: [3] LOG:  connection authorized:
user=sdoherty database=ccbank
Feb  7 09:17:25 ccbankdb pg[14886]: [2] LOG:  connection received:
host=10.10.124.193 port=1314
Feb  7 09:17:25 ccbankdb pg[14886]: [3] LOG:  connection authorized:
user=sdoherty database=ccbank
Feb  7 09:17:26 ccbankdb pg[14887]: [2] LOG:  connection received:
host=10.10.124.193 port=1315
Feb  7 09:17:26 ccbankdb pg[14887]: [3] LOG:  connection authorized:
user=sdoherty database=ccbank

Since there are cells in EXCEL each containing a query, The server gets hit
with a connection request per query. This
in turn originally exceeded the maximum connections supported by my
configuration on the server. I raised the number of
connections supported to 500, however this seems like a work around which
may resurface again soon.

I have verified that POOLING is turned on in the ODBC driver from the
ODBC-ADMIN tool.

I am looking for ideas on how to further determine what is actually not
working. Is it something in the driver ? or Is it something to do with
EXCEL's handling of queries.

Jim

----- Original Message -----
From: "Jeff Eckermann" <jeff_eckermann@yahoo.com>
To: "James M Doherty PGADMIN" <jimmyd@jdoherty.net>;
<pgsql-odbc@postgresql.org>
Sent: Wednesday, February 23, 2005 10:11 AM
Subject: Re: [ODBC] Connection Pooling Problems Dave Page


> --- James M Doherty PGADMIN <jimmyd@jdoherty.net>
> wrote:
>
>>
>> DOES ANYONE HAVE ANY EXPERIENCE WITH THIS .... DAVE
>> PAGE? ANYONE...
>
> More information would be good.  Relevant code,
> relevant extracts from log files.  What is the
> application actually doing, when the problem occurs?
>
>>
>> Jim
>> >>
>>   ----- Original Message -----
>>   From: James M Doherty PGADMIN
>>   To: pgsql-odbc@postgresql.org
>>   Sent: Wednesday, February 16, 2005 12:18 PM
>>   Subject: [ODBC] Connection Pooling Problems
>>
>>
>>   I have an application that uses the ODBC interface
>> from a window XP machine. I have set connection
>> pooling on the pgsql driver to on with
>>   a timeout of 120 seconds. This is being called
>> from an Excel spread sheet. When the user refreshes
>> the spread sheet I get hundreds of
>>   connections logged in /var/log/messages from the
>> user. There are about 200 queries which try to
>> execute at close to the same time.
>>
>>   As a temporary measure I have increased the number
>> of simultaneous connections to 500 to support this,
>> however it does not look like
>>   connection pooling is working correctly. Any ideas
>> on how I might diagnose this, or is there a known
>> fix for this type of problem.
>>
>>   I am running 7.4 and 7.03.01.00 of the ODBC
>> driver.
>>
>>   Thanks for any help you can give me !
>>
>>   Jim
>>
>>
>>
>>   James M Doherty
>>   jim@jdoherty.net
>>   Georgetown, TX 78626
>>   "There is no luck without discipline" IRISH
> PROVERB
>
>
>
>
> __________________________________
> Do you Yahoo!?
> Yahoo! Mail - now with 250MB free storage. Learn more.
> http://info.mail.yahoo.com/mail_250
>

Re: Connection Pooling Problems Dave Page

From
Jeff Eckermann
Date:
--- James M Doherty PGADMIN <jimmyd@jdoherty.net>
wrote:

>
> Since there are cells in EXCEL each containing a
> query, The server gets hit
> with a connection request per query. This
> in turn originally exceeded the maximum connections
> supported by my
> configuration on the server. I raised the number of
> connections supported to 500, however this seems
> like a work around which
> may resurface again soon.

You might want to rethink your design.  Using
something like ADO, it's fairly easy to get your data
in a single query, and assign the values to cells
however you want.

>
> I have verified that POOLING is turned on in the
> ODBC driver from the
> ODBC-ADMIN tool.
>
> I am looking for ideas on how to further determine
> what is actually not
> working. Is it something in the driver ? or Is it
> something to do with
> EXCEL's handling of queries.
>
> Jim
>
> ----- Original Message -----
> From: "Jeff Eckermann" <jeff_eckermann@yahoo.com>
> To: "James M Doherty PGADMIN" <jimmyd@jdoherty.net>;
>
> <pgsql-odbc@postgresql.org>
> Sent: Wednesday, February 23, 2005 10:11 AM
> Subject: Re: [ODBC] Connection Pooling Problems Dave
> Page
>
>
> > --- James M Doherty PGADMIN <jimmyd@jdoherty.net>
> > wrote:
> >
> >>
> >> DOES ANYONE HAVE ANY EXPERIENCE WITH THIS ....
> DAVE
> >> PAGE? ANYONE...
> >
> > More information would be good.  Relevant code,
> > relevant extracts from log files.  What is the
> > application actually doing, when the problem
> occurs?
> >
> >>
> >> Jim
> >> >>
> >>   ----- Original Message -----
> >>   From: James M Doherty PGADMIN
> >>   To: pgsql-odbc@postgresql.org
> >>   Sent: Wednesday, February 16, 2005 12:18 PM
> >>   Subject: [ODBC] Connection Pooling Problems
> >>
> >>
> >>   I have an application that uses the ODBC
> interface
> >> from a window XP machine. I have set connection
> >> pooling on the pgsql driver to on with
> >>   a timeout of 120 seconds. This is being called
> >> from an Excel spread sheet. When the user
> refreshes
> >> the spread sheet I get hundreds of
> >>   connections logged in /var/log/messages from
> the
> >> user. There are about 200 queries which try to
> >> execute at close to the same time.
> >>
> >>   As a temporary measure I have increased the
> number
> >> of simultaneous connections to 500 to support
> this,
> >> however it does not look like
> >>   connection pooling is working correctly. Any
> ideas
> >> on how I might diagnose this, or is there a known
> >> fix for this type of problem.
> >>
> >>   I am running 7.4 and 7.03.01.00 of the ODBC
> >> driver.
> >>
> >>   Thanks for any help you can give me !
> >>
> >>   Jim
> >>
> >>
> >>
> >>   James M Doherty
> >>   jim@jdoherty.net
> >>   Georgetown, TX 78626
> >>   "There is no luck without discipline" IRISH
> > PROVERB
> >
> >
> >
> >
> > __________________________________
> > Do you Yahoo!?
> > Yahoo! Mail - now with 250MB free storage. Learn
> more.
> > http://info.mail.yahoo.com/mail_250
> >
>
>




__________________________________
Do you Yahoo!?
Yahoo! Mail - Helps protect you from nasty viruses.
http://promotions.yahoo.com/new_mail