Thread: Enabling connection pooling by default
Users can install 08.03.0200 ODBC driver in Windows using official msi package. "Set connection spooling attributes" window in Windows Vista ODBC Data Source Administrator dialog shows that connection pooling is turned off by default. How to turn it on during default installation or programmatically so that user intraction is not required ? Is it possible to usie some connection string option to turn pooling on ? For SQL and Oracle ODBC drivers connection pooling is turned on by default. Andrus.
On Wed, Oct 15, 2008 at 7:16 AM, Andrus <kobruleht2@hot.ee> wrote: > How to turn it on during default installation or programmatically so that > user intraction is not required ? > Is it possible to usie some connection string option to turn pooling on ? This is the official list of options that can be passed to the driver. Unfortunately I didn't see any options for connection pooling: http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/~checkout~/psqlodbc/psqlodbc/docs/config-opt.html?rev=1.1&content-type=text/plain -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug
Richard, thank you. Maybe connection pooling is ODBC driver manager function, not ODBC driver itself. In this case ODBC driver manager should have some special setting in connection string like Driver= which applies for all drivers. Why connection pooling is not turned ON by msi installer ? Maybe there is some command line option or is it possible to write to registry to turn in on. I do'nt like the idea to force end users to set this. How to check whether conn pooling is active and issue warning to user if not ? Andrus. ----- Original Message ----- > This is the official list of options that can be passed to the driver. > Unfortunately I didn't see any options for connection pooling: > > http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/~checkout~/psqlodbc/psqlodbc/docs/config-opt.html?rev=1.1&content-type=text/plain
Hello, I have discovered this problem a little. It seems to me installer has to set "CPTimeout" registry key to some integer value. This will enable pooling by default. Here is this registry key: HKEY_LOCAL_MACHINE\Software\Odbc\Odbcinst.ini\ODBC_driver_name\CPTimeout Andrus wrote: > Richard, > > thank you. > > Maybe connection pooling is ODBC driver manager function, not ODBC > driver itself. > In this case ODBC driver manager should have some special setting in > connection string like Driver= > which applies for all drivers. > > Why connection pooling is not turned ON by msi installer ? > Maybe there is some command line option or is it possible to write to > registry to turn in on. > I do'nt like the idea to force end users to set this. > How to check whether conn pooling is active and issue warning to user > if not ? > > Andrus. > > > ----- Original Message ----- > > This is the official list of options that can be passed to the driver. >> Unfortunately I didn't see any options for connection pooling: >> >> http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/~checkout~/psqlodbc/psqlodbc/docs/config-opt.html?rev=1.1&content-type=text/plain >> > > -- Andrei Kovalevski PostgreSQL Replication, Consulting, Custom Development, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, ODBCng - http://www.commandprompt.com/
Thank you. So the program flow should be 1. Read registry key HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\PostgreSQL Unicode\CPTimeout 2. It it does not exists or is zero, application requires registry write access and wrote this key with value 60 Is this best solution ? Or is there some ODBC api function which can used for this ? I'm bit confused by the fact that pooling is not turned on by default. So almost every PostgreSQL ODBC application does not use pooling. Is connection pooling tested and works OK with unicode driver ? Can pooling turned on by default in next driver release ? Andrus. ----- Original Message ----- > I have discovered this problem a little. It seems to me installer has to > set "CPTimeout" registry key to some integer value. This will enable > pooling by default. Here is this registry key: > > HKEY_LOCAL_MACHINE\Software\Odbc\Odbcinst.ini\ODBC_driver_name\CPTimeout
On Wed, Oct 15, 2008 at 8:21 AM, Andrus <kobruleht2@hot.ee> wrote: > Is connection pooling tested and works OK with unicode driver ? > Can pooling turned on by default in next driver release ? This is a good question. Until now, I was implementing my own connection pooling mechanism in my client application. I have one question for you Andrus. Are you trying to create a three tier application that uses ODBC on the second tier to handle connections for multiple third tier clients? This would be a good use for connection pooling. In my case, I use a two tier approach. When using only two tiers, I feel it is better if the client use only one connection that is opened and maintained throughout the client apps life-span. In this scenario to many connections is only going to waste PG server memory resources but will not give the client any benefits that beyond what a single connection could due. -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug
Richard,
thank you.
>> Is connection pooling tested and works OK with unicode driver ?
>> Can pooling turned on by default in next driver release ?
>
> This is a good question. Until now, I was implementing my own
> connection pooling mechanism in my client application.
Why you re-invented the wheel?
ODBC driver manager in Windows has built-in pool manager probably.
> I have one question for you Andrus. Are you trying to create a three
> tier application that uses ODBC on the second tier to handle
> connections for multiple third tier clients? This would be a good
> use for connection pooling.
I have existing two-tier Windows GUI internet POS application which uses
single connection.
Currently I open connection at appl startup and close it if user exits
application.
If application is idle for a some time, ADSL modems or something other in
wire closes connection automatically.
To prevent this I think I need to close connection after every major
transaction is finished in my application.
In some cases major transactions are called rapidly. In this case automatic
connection pool with 60 sec time should be useful to eliminate time
to establish new connection.
Otherwize my code will be much more complicated: unit-of work cannot close
connection. There must be some analyzer which closes connection
only after everything is completed. This requires much more refactoring of
existing code. so I'm loooking for pool usage.
> In my case, I use a two tier approach. When using only two tiers, I
> feel it is better if the client use only one connection that is opened
> and maintained throughout the client apps life-span. In this scenario
> to many connections is only going to waste PG server memory resources
> but will not give the client any benefits that beyond what a single
> connection could due.
My POS appl life-span can be 24/7 and due to the connection auto-break issue
holding connection open all this time seems to be not possible.
Currently I create automatically new connection if this occurs. This seems
to cause 40 sec delay since appl tries to use dead connection first.
I have also one major issue with close connection approach: I need to
determine how many clients are "logged-on" to current database at any
moment.
Currently I use query
select usename,client_addr,MIN(backend_start) AS backend_start, COUNT(*) as
cnt from
pg_stat_activity where datname=?mydatabase
GROUP BY 1,2 ORDER BY 2
I have no idea how to implement this with multiple connections.
Maybe to create login table. When user log-in appl inserts record to it,
log-off removes record.
Major issue is that if client exists without logging off, wrong record
remains in this table.
It is not possible to use some timeout since some clients may be logged-on
for 24 hours.
Andrus.
On Wed, Oct 15, 2008 at 9:58 AM, Andrus <kobruleht2@hot.ee> wrote: > Why you re-invented the wheel? This is a good point. I guess that you are right. Normally I open one connection per application (Using a "singleton pattern") and then pass it to any functions that need it to create or execute a (prepared) statement. Perhaps ODBC connection pulling would eliminate this problem, But I want to be sure that I only open one connection per application. > If application is idle for a some time, ADSL modems or something other in > wire closes connection automatically. > > There must be some analyzer which closes connection > only after everything is completed. This requires much more refactoring of > existing code. so I'm loooking for pool usage. Aren't pulled connections going to suffer from the same disconnection problems as nonpulled connections? Maybe a better solution would be to wrap your connection in a class that periodically checks if it is alive. If it isn't alive, then replace the stale connection with a new connection. Also, when wrapping your connection object you could also have it check and optionally recreate the dead connection when an operation is requested from it. Of course, this won't fix the problem with long running transactions that might fail when the connection dies, but at least you know that your connections will always be open. However, using this approach, you could raise a helpful error message when a connection needs to be created while a transaction was in process. -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug
> Aren't pulled connections going to suffer from the same disconnection > problems as nonpulled connections? No. Only idle connections seems to be killed automatically by ADSL modem. Maybe TCP_KEEPALIVE can fix this but I havent found any inforamtion about its usage with PostgreSQL. Andrus.
Andrus wrote: > If application is idle for a some time, ADSL modems or something other > in wire closes connection automatically. > To prevent this I think I need to close connection after every major > transaction is finished in my application. > In some cases major transactions are called rapidly. In this case > automatic connection pool with 60 sec time should be useful to eliminate > time > to establish new connection. The automatic close is probably a TCP/IP connection timeout or (much more likely) a home user's NAT router expiring connection tracking entries. Continued activity on the connection should prevent this. See these configuration directives, which you can set in postgresql.conf or per-connection using the SET command: tcp_keepalives_idle = 0 # TCP_KEEPIDLE, in seconds; tcp_keepalives_interval = 0 # TCP_KEEPINTVL, in seconds; tcp_keepalives_count = 0 # TCP_KEEPCNT; Of course, your code must still be prepared to handle broken connections where it re-establishes the connection and retries the transaction. You need to be prepared for transaction failures due to other transient errors, too. > I have no idea how to implement this with multiple connections. > Maybe to create login table. When user log-in appl inserts record to it, > log-off removes record. This won't work, because an unexpected connection break won't remove the record. You could use an advisory lock on some imaginary resource, but that'd be pretty ugly. -- Craig Ringer
Craig, >> Maybe TCP_KEEPALIVE can fix this but I havent found any inforamtion about >> its usage with PostgreSQL. > > http://www.postgresql.org/docs/current/static/runtime-config-connection.html Thank you for reply. I'm sorry I was not clear. The part of "On systems that support the*" in referenced documentation confuces me. Windows clients use Windwses starting at Windows 98. Servers are modern Linux, Free/NetBSD and Windows computers starting at Windows XP. Connections are made over public internet, clients are connecting to 5432 port in server. Will TCP_KEEP* options work wihout issure in this configuraton?. If I create production application which relies on TCP_KEEP* settings will this work OK? Can TCP_KEEP* blocked by ISP or by routers/ADSL/GPRS modems ? I'm asking so because there was recently message in npgsql mailing list that TCP_KEEP* are not working. I tried google little but havent found any information about using those options to resolve idle connection kill issue in configuraton above. Andrus.
On Wed, Oct 15, 2008 at 2:47 PM, Andrus <kobruleht2@hot.ee> wrote: >> Aren't pulled connections going to suffer from the same disconnection >> problems as nonpulled connections? > > No. > Only idle connections seems to be killed automatically by ADSL modem. > Maybe TCP_KEEPALIVE can fix this but I havent found any inforamtion about > its usage with PostgreSQL. If your ADSL modem is also your router, and you are using NAT, then it is not killing your connection. It is merely removing it from active connection queue after 3 min or so of no activity. If your ADSL modem is not a router, and "disconnects" your connection, then the ADSL modem is broken. If you have a NAT outside the modem, then it is the NAT that is timing out, not the modem. TCP_KEEPALIVE messages are only useful when you have a non-public IP and/or using NAT and/or want PostgreSQL to close connections where the client has disappeared. - Adam
Andrei, > I have discovered this problem a little. It seems to me installer has to > set "CPTimeout" registry key to some integer value. This will enable > pooling by default. Here is this registry key: > > HKEY_LOCAL_MACHINE\Software\Odbc\Odbcinst.ini\ODBC_driver_name\CPTimeout According to http://www.15seconds.com/issue/970531.htm this only changes pool timeout, it does not turn pooling on. Andrus.
Hello, Thank you for this link. There is no special option to enable connection pooling for the driver in the ODBC driver manager's level. Try to set some value and you'll see that windows recognizes this feature as enabled. I haven't actually test this, did you? Andrus wrote: > Andrei, > >> I have discovered this problem a little. It seems to me installer has >> to set "CPTimeout" registry key to some integer value. This will >> enable pooling by default. Here is this registry key: >> >> HKEY_LOCAL_MACHINE\Software\Odbc\Odbcinst.ini\ODBC_driver_name\CPTimeout > > According to http://www.15seconds.com/issue/970531.htm > this only changes pool timeout, it does not turn pooling on. > > Andrus. > -- Andrei Kovalevski PostgreSQL Replication, Consulting, Custom Development, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, ODBCng - http://www.commandprompt.com/
On Mon, Oct 20, 2008 at 1:59 PM, Andrei Kovalevski <andyk@commandprompt.com> wrote: > Thank you for this link. There is no special option to enable connection > pooling for the driver in the ODBC driver manager's level. Try to set some > value and you'll see that windows recognizes this feature as enabled. I > haven't actually test this, did you? http://support.microsoft.com/kb/169470 -- Jonah H. Harris, Senior DBA myYearbook.com
Adam, > If your ADSL modem is also your router, and you are using NAT, then it > is not killing your connection. It is merely removing it from active > connection queue after 3 min or so of no activity. > > If your ADSL modem is not a router, and "disconnects" your connection, > then the ADSL modem is broken. If you have a NAT outside the modem, > then it is the NAT that is timing out, not the modem. > > TCP_KEEPALIVE messages are only useful when you have a non-public IP > and/or using NAT and/or want PostgreSQL to close connections where the > client has disappeared. Thank you. I have the following typical configuration: Server: Windows computer inside LAN. Router (Linksys, SpeedTouch etc) DHCP has programmed to assign fixed IP address to server or fixed local IP address is assigned to server manually. Router has public IP address assigned by ISP. Router has programmed to forward 5432 port to server into LAN Client: There is similar one router in LAN which is used also to share public internet connection. Client router has dynamic public IP address. Client OS is Windows. Is it possible to use this: 1. Configure something to remove 3 min NAT active connection timeout ? Should I configure client or server ? I havent found such option in routers. 2. Use TCP_KEELALIVEs 3. Use ping -t or some external program or website which simulates activity 4. Close and re-open connection. Which is the best way to prevent connection loss in this case if there is no activity for a long time ? Andrus.