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

[Rafael 'D. S. <di...@im...>]

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?

-- 
True compassion is more than throwing a coin to a beggar. It demands of
our humanity that if we live in a society that produces beggars, we are
morally commanded to restructure that society.
http://stormwyrm.blogspot.com/