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: Ville S. <vil...@gm...> - 2013-03-05 16:19:58
|
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...>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" 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...> 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...
>>
>> 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...>>
>> 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...
>> > 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...
>> https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion
>>
>
>
|
