[Sqlrelay-discussion] sqlrelay TTL issue

[Jose S. <js...@si...>]

Hi,
=A0
My name is Jose and I'm having a problem with sqlrelay 0.36.2. I'm using =
Debian linux stable, PHP 4.3, and ORACLE 9.2.
=A0
The current sqlrelay configuration is:
=A0
<instance id=3D"test-con" port=3D"9090" socket=3D"/tmp/test-con.socket" d=
base=3D"oracle8" connections=3D"0" maxconnections=3D"25" maxqueuelength=3D=
"0" growby=3D"3" ttl=3D"60" endofsession=3D"commit" sessiontimeout=3D"600=
" runasuser=3D"oracle" runasgroup=3D"oracle" cursors=3D"10" authtier=3D"l=
istener" handoff=3D"pass">
       <users>
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 <user=
 user=3D"test" password=3D"test"/>
       </users>
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 <connections>
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 <conn=
ection connectionid=3D"test" string=3D"user=3Dtest;password=3Dtest;oracle=
_sid=3DJOSE.WORLD" metric=3D"1"/>
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 </connections>
</instance>
=A0
As you can see, we have a ttl of 60. Here is the problem I'm currently ha=
ving:
=A0
The benchmark environment is set up so the site gets 3 requests every 5-1=
0 seconds, which needs between 1 and 3 connections active. (Note that we =
have an average of 25 queries per PHP page)
=A0
If I run apache benchmark requesting 500 pages with a level of concurrenc=
y of 5, the number of connections in the pool increases to reach 12-15.
=A0
The problem comes after the apache benchmark is done. Since the volume of=
 requests goes down again, I was expecting to see the number of connectio=
ns in the pool going down after 60 seconds of inactivity, but this does n=
ot happen.
=A0
If I run the apache benchmark again (500 pages and 5 concurrency) the num=
ber of connections would go up again to reach 21-24. After the requests a=
re handled, the number of connections would not decrease.
=A0
This is a problem because it seems that those connections used during hig=
h volumes of requests are not available anymore once they have been inact=
ive more than 60 seconds, and instead of being released, they stay in the=
 pool and not available for more upcoming requests.
=A0
If I run the same experiment described above with a 15 TTL this problem d=
oes not happen. The number of connections goes down right after the conne=
ctions have been inactive more than that TTL.
=A0
Here is an excerpt of the debug statements found in the connections that =
have this problem (60 TTL):
=A0=A0
12/09/2005 00:36:10 GMT connection [18919] : done initializing session...
=A0
12/09/2005 00:36:10 GMT connection [18919] : announcing availability...
=A0
12/09/2005 00:36:10 GMT connection [18919] :=A0=A0=A0 acquiring announce =
mutex
=A0=A0
---- Stuck here ---
=A0
=A0
And this is what I got when using 15 TTL:
=A0
12/08/2005 14:28:34 PST connection [31305] : initializing session...
=A0
12/08/2005 14:28:34 PST connection [31305] : done initializing session...
=A0
12/08/2005 14:28:34 PST connection [31305] : announcing availability...
=A0
12/08/2005 14:28:34 PST connection [31305] :=A0=A0=A0 acquiring announce =
mutex
=A0
12/08/2005 14:29:34 PST connection [31305] : decrementing connection coun=
t...
=A0
---- Releases resources just fine ----


Do you have any ideas on why the 60 TTL is causing this problem?.
=A0
Also, if I go ahead with the 15 TTL, what would be the performance impact=
 in my application?
=A0
Any ideas/help would be much appreciated
=A0
Thanks in advance.=20