Thread: Connections sitting idle...
Hello all,
I've been having a lot of trouble with connections sitting around
idle for a long time after a query has executed. I'm building a web
app which uses ADO on a windows computer to talk to the ODBC driver
(version 7.02.0004, installed with pgAdmin II), which then talks to
PostgreSQL on a Linux machine.
I've been looking at the psqlodbc-xxxxx.log and basically, what is
happening is that the query is executed as normal, then nothing
happens for about 80 seconds (during which I can see the idle backend
process on the linux server), and then suddenly the command
"PGAPI_Disconnect" appears in the log and the backend finally
terminates.
I've tried this (i) calling the ADO objects from an ASP script (see
[1] below) and (ii) calling the ADO objects from a COM DLL created in
Delphi (pretty much the same as in the ASP script) to be called from
an ASP script. In both cases, the backend hung around for about 80s,
even though the connection has been explicitly closed and the
connection object removed from memory. I've appended a typical set of
log entries at [2] below.
Interestingly, creating another Delphi app that talks to Postgres via
either the Zeos components (which don't use either ADO or ODBC)
causes the backed to die immediately once the connection is closed.
This seems to imply to me that either ADO or the ODBC driver itself
is for some reason keeping the connection open rather than closing it
immediately. This is causing me major grief, as when a lot of people
hit the web app together Postgres quickly runs out of available
connections and the server has to be rebooted.
I'm using PostgreSQL 7.2 on the production machine, 7.1 on my own
development machine, version 7.02.0004 of the ODBC driver, and ADO
2.5 (at least) and Windows 2000 Server with IIS5 on the web server.
Any help would be greatly appreciated.........before I lose all my
hair.............
Many thanks in advance.
--Ray O'Donnell
------------------------------
[1] The ASP script
<%@ language="vbscript" %>
<% option explicit %>
<!-- #include file="adovbs.asp" -->
<!-- #include file="connectionstuff.asp" -->
<%
dim strSQL
dim conn
dim rs
strSQL = "select uid from surveyowners where uid='rod'"
set conn = Server.CreateObject("ADODB.Connection")
conn.Open ConnectionStr
set rs = conn.Execute(strSQL, , adCmdText)
Response.Write rs("uid") & "<br>"
rs.Close
set rs = nothing
conn.Close
set conn = nothing
Response.Write("Done!")
Response.End
%>
------------------------------
[2] A typical pgsqlodbc-*****.log
DSN info:
DSN='gost',server='********',port='5432',dbase='gost',user='********',
passwd='********'
onlyread='0',protocol='6.4',showoid='0',fakeoidindex='0',showsystable=
'0'
conn_settings='',conn_encoding='OTHER'
translation_dll='',translation_option=''
conn = 69075064, PGAPI_Connect(DSN='gost', UID='********',
PWD='********')
Global Options: Version='07.02.0004', fetch=100, socket=4096,
unknown_sizes=0, max_varchar_size=254, max_longvarchar_size=8190
disable_optimizer=1, ksqo=1, unique_index=1,
use_declarefetch=0
text_as_longvarchar=1, unknowns_as_longvarchar=0,
bools_as_char=0 NAMEDATALEN=64
extra_systable_prefixes='dd_;', conn_settings=''
conn_encoding='OTHER'
conn=69075064, query=' '
conn=69075064, query='select version()'
[ fetched 1 rows ]
[ PostgreSQL version string = 'PostgreSQL 7.1.3 on i486-pc-linux-
gnu, compiled by GCC egcs-2.91.66' ]
[ PostgreSQL version number = '7.1' ]
conn=69075064, query='set DateStyle to 'ISO''
conn=69075064, query='set geqo to 'OFF''
conn=69075064, query='select oid from pg_type where typname='lo''
[ fetched 0 rows ]
conn=69075064, query='Show Client_Encoding'
NOTICE from backend during send_query: 'NOTICE: Current client
encoding is SQL_ASCII
'
[ Client encoding = 'SQL_ASCII' (code = 0) ]
conn=69075064, query='select * from surveyowners
'
[ fetched 1 rows ]
conn=69075064, PGAPI_Disconnect
------------------------------
-------------------------------------------------------------
Raymond O'Donnell http://www.galwaycathedral.org/recitals
rod@iol.ie Galway Cathedral Recitals
-------------------------------------------------------------
Raymond O'Donnell wrote:
>
> Hello all,
>
> I've been having a lot of trouble with connections sitting around
> idle for a long time after a query has executed. I'm building a web
> app which uses ADO on a windows computer to talk to the ODBC driver
> (version 7.02.0004, installed with pgAdmin II), which then talks to
> PostgreSQL on a Linux machine.
>
> I've been looking at the psqlodbc-xxxxx.log and basically, what is
> happening is that the query is executed as normal, then nothing
> happens for about 80 seconds (during which I can see the idle backend
>
> process on the linux server), and then suddenly the command
> "PGAPI_Disconnect" appears in the log and the backend finally
> terminates.
>
> I've tried this (i) calling the ADO objects from an ASP script (see
> [1] below) and (ii) calling the ADO objects from a COM DLL created in
>
> Delphi (pretty much the same as in the ASP script) to be called from
> an ASP script. In both cases, the backend hung around for about 80s,
> even though the connection has been explicitly closed and the
> connection object removed from memory. I've appended a typical set of
>
> log entries at [2] below.
>
> Interestingly, creating another Delphi app that talks to Postgres via
>
> either the Zeos components (which don't use either ADO or ODBC)
> causes the backed to die immediately once the connection is closed.
> This seems to imply to me that either ADO or the ODBC driver itself
> is for some reason keeping the connection open rather than closing it
> immediately.
Yes IIS uses the connection pooling mechanism by default.
Pooled connections would be re-used if possible for other
sessions without re-establishing new connections.
Anyway I can't see other sessions' info at all in your
example. How do other sessions behave ?
regards,
Hiroshi Inoue
http://w2422.nsk.ne.jp/~inoue/
> -----Original Message----- > From: Hiroshi Inoue [mailto:Inoue@tpf.co.jp] > Sent: 05 December 2002 02:53 > To: Raymond O'Donnell > Cc: pgsql-odbc@postgresql.org > Subject: Re: [ODBC] Connections sitting idle... > > > > Yes IIS uses the connection pooling mechanism by default. > Pooled connections would be re-used if possible for other > sessions without re-establishing new connections. I'm pretty sure this is the underlying ADO. It's for this reason that pgAdmin can't drop databases - no matter how you tell ADO to close the connection , it 'helpfully' keeps it open preventing you from ever dropping that database. Regards, Dave.
On 5 Dec 2002 at 11:53, Hiroshi Inoue wrote: > Yes IIS uses the connection pooling mechanism by default. > Pooled connections would be re-used if possible for other > sessions without re-establishing new connections. I should have mentioned that I turned off connection pooling for the PostgreSQL ODBC driver to see would it make a difference, but the backends hung around anyway. On the production system I will use pooling - if the backends are there, hopefully they'll be re-used rather than having new ones opened. I still have to try this out. > Anyway I can't see other sessions' info at all in your > example. How do other sessions behave ? The same thing happens consistently. ...and on 5 Dec 2002 at 8:06, Dave Page wrote: > I'm pretty sure this is the underlying ADO. It's for this reason that > pgAdmin can't drop databases - no matter how you tell ADO to close the > connection , it 'helpfully' keeps it open preventing you from ever > dropping that database. Does you mean that ADO keeps the connection open regardless of whether or not connection pooling is turned on? Is there any way to control this behaviour? I don't see anything in the properties of the ADO connection object to affect this, but maybe I just missed it at 2.00 am..... :) Many thanks for your replies! --Ray. ------------------------------------------------------------- Raymond O'Donnell http://www.galwaycathedral.org/recitals rod@iol.ie Galway Cathedral Recitals -------------------------------------------------------------
Raymond O'Donnell wrote:
>
> On 5 Dec 2002 at 11:53, Hiroshi Inoue wrote:
>
> > Yes IIS uses the connection pooling mechanism by default.
> > Pooled connections would be re-used if possible for other
> > sessions without re-establishing new connections.
>
> I should have mentioned that I turned off connection pooling for the
> PostgreSQL ODBC driver to see would it make a difference, but the
> backends hung around anyway.
IIS can use connection pooling by itself.
> On the production system I will use
> pooling - if the backends are there, hopefully they'll be re-used
> rather than having new ones opened. I still have to try this out.
>
> > Anyway I can't see other sessions' info at all in your
> > example. How do other sessions behave ?
>
> The same thing happens consistently.
I saw no other session's log in your example between
'select * from surveyowners' and the call for PGAPI_
Disconnect. I'm wondering how the number of connections
increased in such a situation.
regards,
Hiroshi Inoue
http://w2422.nsk.ne.jp/~inoue/
On 5 Dec 2002 at 19:09, Hiroshi Inoue wrote: > I saw no other session's log in your example between > 'select * from surveyowners' and the call for PGAPI_ > Disconnect. I'm wondering how the number of connections > increased in such a situation. I'll generate some this evening when I get home - computers aren't my day job!! - and will post them to this list. --Ray. ------------------------------------------------------------- Raymond O'Donnell http://www.galwaycathedral.org/recitals rod@iol.ie Galway Cathedral Recitals -------------------------------------------------------------
> -----Original Message----- > From: Raymond O'Donnell [mailto:rod@iol.ie] > Sent: 05 December 2002 09:53 > To: pgsql-odbc@postgresql.org > Subject: Re: [ODBC] Connections sitting idle... > > > ...and on 5 Dec 2002 at 8:06, Dave Page wrote: > > > I'm pretty sure this is the underlying ADO. It's for this > reason that > > pgAdmin can't drop databases - no matter how you tell ADO > to close the > > connection , it 'helpfully' keeps it open preventing you from ever > > dropping that database. > > Does you mean that ADO keeps the connection open regardless of > whether or not connection pooling is turned on? Is there any way to > control this behaviour? I don't see anything in the properties of the > ADO connection object to affect this, but maybe I just missed it at > 2.00 am..... :) Yes, that's the conclusion I came to. I've yet to find a way around the problem :-( Regards, Dave.
On 5 Dec 2002 at 19:09, Hiroshi Inoue wrote: > I saw no other session's log in your example between > 'select * from surveyowners' and the call for PGAPI_ > Disconnect. I'm wondering how the number of connections > increased in such a situation. I've been trying to send some new logs for the last few days, both as attachments and in the body of the email, but they've never appeared. Is there a size limit on mails sent to this list, and/or a ban on attachments? They total about 172K. --Ray. ------------------------------------------------------------- Raymond O'Donnell http://www.galwaycathedral.org/recitals rod@iol.ie Galway Cathedral Recitals -------------------------------------------------------------
> -----Original Message----- > From: Raymond O'Donnell [mailto:rod@iol.ie] > Sent: 10 December 2002 10:52 > To: pgsql-odbc@postgresql.org > Subject: Re: [ODBC] Connections sitting idle... > > > On 5 Dec 2002 at 19:09, Hiroshi Inoue wrote: > > > I saw no other session's log in your example between > > 'select * from surveyowners' and the call for PGAPI_ > Disconnect. I'm > > wondering how the number of connections increased in such a > situation. > > I've been trying to send some new logs for the last few days, both as > attachments and in the body of the email, but they've never appeared. > Is there a size limit on mails sent to this list, and/or a ban on > attachments? They total about 172K. I think its 40K (iirc). Can you zip/gzip the files? Regards, Dave.
On 10 Dec 2002 at 13:53, Dave Page wrote: > I think its 40K (iirc). Can you zip/gzip the files? Ah! - that would do it. Zipped files are attached, and the original covering email follows. --Ray. -------------------- I've attached two logs: the "single instance" one was generated by clicking my way through the web app in a single browser window, while the other was generated from three different browser windows at the same time. With three copies of the app running, I had at times nine idle backends on the database machine, which as far as I could tell hung around for about 80s each - I times two or three at random, and the rest seems subjectively to last about the same amount of time. This was with connection pooling turned *on* - no obvious reuse of connections - each page opened a new connection, even though the connection parameters are the same every time. This machine was running Windows Me and Personal Web Server. I'll be trying it out on a Win2K machine in the morning. --Ray. ------------------------------------------------------------- Raymond O'Donnell http://www.galwaycathedral.org/recitals rod@iol.ie Galway Cathedral Recitals ------------------------------------------------------------- The following section of this message contains a file attachment prepared for transmission using the Internet MIME message format. If you are using Pegasus Mail, or any another MIME-compliant system, you should be able to save it or view it from within your mailer. If you cannot, please ask your system administrator for assistance. ---- File information ----------- File: logs.zip Date: 10 Dec 2002, 14:11 Size: 5980 bytes. Type: ZIP-archive