Re: [Sqlrelay-discussion] Sqlrelay with Oracle and large numbers of connections with Ruby-DBI

[Firstworks/4access <dav...@fi...>]

Your config file looks good.

Are a bunch of sqlr-connection-oracle8 processes getting created, or a
bunch of sqlr-listener processes?

One sqlr-listener process gets created for each client that connects.
That sqlr-listener process dies off when the client has been handed off
to an sqlr-connection-oracle8 daemon.  If lots of sqlr-listener
processes are getting created, then it could mean that lots of clients
are connecting for some reason, or that the sqlr-connection-oracle8
processes have gotten hung up.

If a bunch of sqlr-connection-oracle8 processes are getting created,
then there could be a bug in the sqlr-scaler process which spawns them.

Let me know whether sqlr-listener or sqlr-connection-oracle8 processes
are getting created and we can proceed from there.

Dave
dav...@fi...

On Mon, 2005-09-26 at 15:30 +0800, Rafael 'Dido' Sevilla wrote:
> We have a Ruby application running on Oracle 9i and are using Sqlrelay
> as a middleware layer to improve our application's efficiency.  Using it
> has given us a 20%-25% speed boost in our project's efficiency over
> using the OCI8 Ruby DBI module directly.  However, we have been
> experiencing some strange problems that manifest during times that there
> is heavy database load.  When very many parallel connections are made to
> the Sqlrelay daemon, eventually, the number of Sqlrelay processes shoots
> up, way, way beyond the maxconnections limit that we imposed (sometimes
> reaching 500 processes or even more; we have it configured at 30), and
> when we terminate the application (if we don't get to in time this
> inevitably leads to a system crash as virtual memory is exhausted),
> Sqlrelay is totally unusable for connecting to the database: sqlrsh
> simply hangs indefinitely.  The system basically runs small stub
> programs from time to time to perform small database updates, so when it
> connects to Sqlrelay, it freezes, and so both our stub program instances
> and Sqlrelay connection instances proliferate rapidly.  Through all
> this, it remains possible to connect to the database directly using
> something like sqlplus.
> 
> We are using sqlrelay-0.36.1, Oracle Instant Client 10.1.0.3, Ruby
> 1.8.2, Ruby-DBI 0.0.21 on Red Hat Enterprise 3.0, kernel
> 2.4.21-27.ELsmp.  The Oracle is on Solaris 9, version 9.2.0.6.0.  Our
> sqlrelay.conf looks like this (with hostnames and passwords rubbed out):
> 
> <?xml version="1.0"?>
> <!DOCTYPE instances SYSTEM "sqlrelay.dtd">
> <instances>
> 
>         <instance id="pacer-oracle" port="9000"
> socket="/tmp/oracletest.socket" dbase="oracle8" connections="3"
> maxconnections="30" maxqueuelength="0" growby="1" ttl="60"
> endofsession="commit" sessiontimeout="600" runasuser="nobody"
> runasgroup="nobody" cursors="5" authtier="listener" handoff="pass">
>                 <users>
>                         <user user="foo" password="bar"/>
>                 </users>
>                 <connections>
>                         <connection connectionid="oracle-db"
> string="user=XXXXX;password=XXXX;oracle_sid=XXXXX;oracle_home=/usr/lib/oracle/10.1.0.3/client"
> metric="1"/>
>                 </connections>
>         </instance>
> 
> </instances>
> 
> Is there something fishy with our config?
>