Re: [Sqlrelay-discussion] Multiple sqlr-listener processes and no connections
Brought to you by:
mused
Menu
▾
▴
Re: [Sqlrelay-discussion] Multiple sqlr-listener processes and no connections
|
From: David M. <dav...@fi...> - 2013-08-01 20:17:03
|
Hi Ville,
Again, sorry for the long delay...
I'm not sure that a query could ever return that error, but you never
know, so I'll add 20 to the list for the 0.51 release.
As for the larger issue of exceeding the max number of processes...
I'm not sure that much can be done other than to set maxconnections low
enough that it can't exceed Oracle's max process limit.
Currently, when SQL Relay tries to launch a new connection, if it fails
to start, then it will wait a bit and try again, over and over.
Presumably, eventually, it would succeed, though in this case, it won't.
In any case, client connections will be left hanging until the new
connection is up and sqlr-listener processes will accumulate too.
There are maxlisteners and listenertimeout configuration parameters
though that can limit how many sqlr-listener processes that can
accumulate and limit how long they'll wait if a connection can't be
started. You might get good results if you play with those.
The client API's have recently added setConnectTimeout() and
setAuthenticationTimeout() methods too that allow the client to "give
up" after a while if the server is unresponsive. You might try setting
those too.
It might be possible to ask the database what its maximum connection
count is and limit maxconnections to that-35, but that would be
difficult and would only help if SQL Relay is the only program using the
database. Other than that, I'm open to suggestions on what to do to
address the issue.
Dave
dav...@fi...
On 3/5/2013 11:19 AM, Ville Silventoinen wrote:
> Hi Dave,
>
> I noticed the sqlrelay-0.50
> src/connections/oracle8/oracle8connection.cpp has following Oracle error
> codes:
>
> // check for dead connection or shutdown in progress
> switch (errcode) {
> case 22: // invalid session ID; access denied
> case 28: // your session has been killed
> case 604: // error occurred at recursive SQL level ...
> case 1012: // not logged on
> case 1033: // oracle init/shutdown in progress
> case 1041: // internal error. hostdef extension doesn't exist
> case 1089: // immediate shutdown in progress -
> // no operations are permitted
> case 2067: // transaction or savepoint rollback required
> case 3114: // not connected to ORACLE
> case 3113: // end-of-file on communication channel
> case 3135: // connection lost contact
> case 14452: // attempt to create, alter or drop an index on
> // temporary table already in use
> // (see note below)
> *liveconnection=false;
> break;
> default:
> *liveconnection=true;
> break;
> }
>
> I'm just wondering if this list should include "case 20" for ORA-00020,
> so the liveconnection is set to false?
>
> Thanks,
> Ville
>
> On 5 March 2013 15:11, Ville Silventoinen <vil...@gm...
> <mailto:vil...@gm...>> wrote:
>
> Hi Dave,
>
> Sorry it has taken so long to get back on this. I haven't been able
> to reproduce the problem reliably until now.
>
> Since last time, I've switched to Oracle 11g database. I noticed if
> Oracle has quite a low maximum number of processes (150) and if I
> configure too many max connections in SQL Relay, this same problem
> happens.
>
> If you want to reproduce it, you could deliberately set the
> processes in Oracle quite low (alter system set processes = N) and
> then configure SQL Relay to use max connections > N. My application
> is a FUSE-based filesystem that I implemented, which keeps the
> filesystem metadata in Oracle by using SQL Relay. When I run some
> stress tests (fs_racer from Linux Test Project package does this
> nicely when I run it for few minutes), I can easily get SQL Relay to
> reach the max numbers.
>
> What I'm seeing (with sqlrelay-0.50) is that after Oracle has reach
> the max number of processes,, the number of sqlr-listeners grows and
> doesn't back down (until I kill my application). You can see when
> Oracle has encountered the error from the alter log:
>
> # pwd
> /u01/app/oracle/diag/rdbms/unofsdev/UNOFSDEV/trace
>
> # tail alert_UNOFSDEV.log
> ...
> Tue Mar 05 11:16:24 2013
> ORA-00020: maximum number of processes (150) exceeded
> ORA-20 errors will not be written to the alert log for
> the next minute. Please look at trace files to see all
> the ORA-20 errors.
> Tue Mar 05 11:16:39 2013
> Process m000 submission failed with error = 20
>
> I'm not sure what exactly happens in sqlr-listener or
> sqlr-connection when they get ORA-00020 error (or if they even
> receive the error), I never see it in my application. From my point
> of view, my application just hangs when it tries to connect.
>
> The only fix I've found so far is to avoid ORA-00020 like plague by
> making sure the max SQL Relay connections never reaches the max
> Oracle processes. You also need to note that Oracle has a set of its
> own server processes that are included in this total (35 in my
> case), so if your Oracle has max 150 processes, you actually have
> 115 to use for the SQL Relay connections. I got my test working by
> configuring max 25 connections from 4 test machines (4 * 25 + 35 = 135).
>
> I just thought I'd report this in case anyone else has been
> struggling with similar issues. Here is the SQL Relay configuration
> I'm using:
>
> <instance id="unofs" port="9011"
> socket="/var/sqlrelay/tmp/sockets/unofs.socket" dbase="oracle8"
> connections="5" maxconnections="25" maxqueuelength="0" growby="1"
> ttl="60" endofsession="rollback" sessiontimeout="600"
> runasuser="w3secure" runasgroup="systems" cursors="5"
> authtier="listener" handoff="pass" debug="none">
> <users>
> <user user="unofs" password="secret"/>
> </users>
> <connections>
> <connection connectionid="unofsdev"
> string="user=USER;password=PWD;oracle_sid=UNOFSDEV;oracle_home=/usr/lib64/oracle/10.2.0.3/client
> <http://10.2.0.3/client>" metric="1"/>
> </connections>
> </instance>
>
> If anyone has any experiences with ORA-00020 and SQL Relay, and how
> to configure SQL Relay to cope with it, I'd be very grateful to
> learn more.
>
> Thanks,
> Ville
>
>
> On 26 September 2012 13:38, David Muse <dav...@fi...
> <mailto:dav...@fi...>> wrote:
>
> Hi Ville,
>
> Sorry for not responding earlier.
>
> SQL Relay should automatically re-log in if it loses connection
> to the
> database. I recently added a parameter to disable that
> behavior, but it
> should do it by default.
>
> It sounds like the sqlr-connection-mysql process is crashing for
> some
> reason but the semaphores are in such a state that the sqlr-scaler
> doesn't realize this and doesn't spawn any new processes.
>
> I've actually tried to reproduce this and gone through the code,
> looking
> for some semi-obvious thing that could cause it, but I can't find
> anything. My guess is that there is some particular query that
> crashes
> the process.
>
> The last time I encountered a problem like this, it was with
> oracle and
> the problem was that I misinterpreted characters for bytes and
> if OCI
> said "allocate 100 characters", I'd allocate a buffer of 100 bytes,
> which was fine until somebody started using unicode and the query
> returned multi-byte characters. Something like that. I looked
> again
> for that kind of thing, but I didn't see anything.
>
> Nevertheless, I suspect something like that is the problem. It
> would be
> helpful if you could find the query or condition that's causing the
> connections to crash, though I imagine that would be like
> looking for a
> needle in a haystack.
>
> I'll be out of town for the next few days, but I can look into
> it again
> when I get back.
>
> Dave
> dav...@fi... <mailto:dav...@fi...>
>
> On 09/25/2012 06:00 AM, Ville Silventoinen wrote:
> > Hi,
> >
> > Just few more details on this:
> >
> > - I run SQL Relay 0.46 with two different instances configured in
> > sqlrelay.conf; one for Oracle and one for MySQL.
> >
> > - When the problem happens, it's always with the MySQL
> connections. All
> > the sqlr-connection-mysql processes have stopped/crashed and
> there are
> > several sqlr-listener processes for the MySQL instance. They
> seem to be
> > unable to start any sqlr-connection-mysql processes.
> >
> > - Just guesswork, but could the problem be caused if for
> whatever reason
> > SQL Relay fails to login to MySQL? Would that stop the
> > sqlr-connection-mysql process? I've asked the MySQL admin,
> but we don't
> > log the failed login attempts. I could turn on the connection
> debug mode
> > in SQL Relay, but last time we did that, it filled the /var
> filesystem
> > very fast, so I'd need to find some disk space for that.
> >
> > - I'm waiting when the problem happens next time so I can run the
> > "sqlr-status -id INSTANCE" command to see what the raw
> semaphore numbers
> > are. I saw an old thread that seems quite similar to this,
> but I'm not
> > sure if it's related.
> >
> > Cheers,
> > Ville
> >
> > On 4 September 2012 12:15, Ville Silventoinen
> > <vil...@gm...
> <mailto:vil...@gm...>
> <mailto:vil...@gm...
> <mailto:vil...@gm...>>> wrote:
> >
> > Hi,
> >
> > I'm using SQL Relay 0.45 on RedHat 6. Sometimes SQL Relay
> goes into
> > a state where it starts several sqlr-listener processes,
> but there
> > are no sqlr-connection processes. The application that
> uses SQL
> > Relay hangs. There may be a problem with RedHat and the
> network card
> > we are using, which can cause temporary loss of network
> > connectivity. The only cure that I've found is to restart
> SQL Relay.
> >
> > Today I decided to try SQL Relay 0.46. As I was running
> tests, our
> > DBAs shutdown the test Oracle database (I wasn't aware of
> this).
> > Again, the same situation happened: Several sqlr-listener
> processes
> > and no connections.
> >
> > I'm looking for some insight into how I could configure
> SQL Relay so
> > it would automatically recover from the temporary loss of
> network
> > connectivity or database availability. Is this possible?
> Would it
> > help if I set maxlisteners to 0, listenertimeout to 60,
> or tweak
> > some other configuration variable?
> >
> > Thanks for any advice,
> > Ville
> >
> >
> >
> >
> >
> >
> ------------------------------------------------------------------------------
> > Live Security Virtual Conference
> > Exclusive live event will cover all the ways today's security and
> > threat landscape has changed and how IT managers can respond.
> Discussions
> > will include endpoint security, mobile security and the
> latest in malware
> > threats.
> http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/
> >
> > _______________________________________________________
> > Unlimited Disk, Data Transfer, PHP/MySQL Domain Hosting
> > http://www.doteasy.com
> >
> >
> >
> > _______________________________________________
> > Sqlrelay-discussion mailing list
> > Sql...@li...
> <mailto:Sql...@li...>
> > https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion
> >
> >
> > _______________________________________________________
> > Unlimited Disk, Data Transfer, PHP/MySQL Domain Hosting
> > http://www.doteasy.com
> >
>
> ------------------------------------------------------------------------------
> Live Security Virtual Conference
> Exclusive live event will cover all the ways today's security and
> threat landscape has changed and how IT managers can respond.
> Discussions
> will include endpoint security, mobile security and the latest
> in malware
> threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/
> _______________________________________________
> Sqlrelay-discussion mailing list
> Sql...@li...
> <mailto:Sql...@li...>
> https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion
>
>
>
>
>
> ------------------------------------------------------------------------------
> Everyone hates slow websites. So do we.
> Make your web apps faster with AppDynamics
> Download AppDynamics Lite for free today:
> http://p.sf.net/sfu/appdyn_d2d_feb
>
> _______________________________________________________
> Unlimited Disk, Data Transfer, PHP/MySQL Domain Hosting
> http://www.doteasy.com
>
>
>
> _______________________________________________
> Sqlrelay-discussion mailing list
> Sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion
>
>
> _______________________________________________________
> Unlimited Disk, Data Transfer, PHP/MySQL Domain Hosting
> http://www.doteasy.com
>
|
