Re: [Sqlrelay-discussion] Working instance becomes problematic w/o obvious reason

[David M. <dav...@fi...>]

Over the years, I've had sporadic reports of this but have rarely been
able to reproduce it reliably or narrow down what causes it.  In the few
cases that we were able to narrow it down, it was with Oracle databases
and we discovered that an individual connection daemon would get hung
trying to run a particular query.  Over a period of weeks that same
query would end up getting run on all of the connection daemons, hanging
each.  Eventually there weren't enough connection daemons running to
handle the traffic and the error you describe would occur.  In some
cases, Oracle had to be patched, in others SQL Relay had to be fixed.
In all cases though, there was a specific, offending query.  We
discovered the exact query by logging all queries and then replaying the
log through SQL Relay.  I'd recommend doing something like that to find
out exactly what query is causing the problem.  If you can narrow that
down, I can figure out a fix.

In theory, it's also possible for a PIX firewall to cause that problem,
though it usually presents itself differently.  See this page for more
info on that:
http://sqlrelay.sourceforge.net/sqlrelay/faq.html#firewallhang

David Muse
dav...@fi...


On Mon, 2008-06-30 at 10:15 +0200, Christian Hansel wrote:
> Hi all
> 
> 
> We are running sql-relay 0.39.4 with perl and mysql 5.0 with multiple
> server-connections with upwards 100K queries per day.
> 
> While everything works fine, we do have, from time to time, the problem
> that one of the connections simply hangs for whatever reason.
> 
> Once this problem occurs we get a "Couldnt connect to the listener"
> error message. After running sqlr-stop and sqlr-start the problem is
> solved again. 
> 
> We have spent weeks examining this problem but couldnt get any further.
> There simply is no obvious reason. The connection works fine and then
> suddenly the listener hangs it self. We first figured we created too
> much traffic, but the problem also occurs at times when there is less
> than 5 queries per minute. 
> 
> We also tried to create this problem manually to verify it or to examine
> it. However, we failed. We simply are not able to cause the
> listener/connection to hang it self and to report this error. Even after
> pushing the multiple amount of statements sqlr usually has to process.
> Nothing. We can not recreate the problem manually
> 
> 
> Today, for the first time, while being logged in to the server (console)
> and running sqlr-stop & sqlr-start on the specific instance after we had
> the problem, I got error messages dumped to the console. Unfortunately,
> I could not find any hint on the web when researching the error string:
> 
> handleQuery called with reexceute flag set to 0
> processQuery got ... [correct SQL Statement]
> 
> Excerpt from the sqlrelay.conf file :
> 
> <instance id="d02w" port="9022" socket="" dbase="mysql" connections="10"
> maxconnections="10" maxqueuelength="10" growby="2" ttl="10"
> endofsession="commit" sessiontimeout="600" runasuser="sqlrelay"
> runasgroup="sqlrelay" cursors="5" authtier="listener" handoff="pass"
> deniedips="" allowedips="127.0.0.1" addresses="127.0.0.1" debug="none"
> maxquerysize="65536" maxstringbindvaluelength="4000"
> maxlobbindvaluelength="71680" idleclienttimeout="-1" maxlisteners="-1"
> listenertimeout="2">
>      <users>
>              <user user="XXXXXXXXXX" password="XXXXXXXX"/>
>      </users>
>      <connections>
>         <connection connectionid="d02w"
> string="user=XXXXXX;password=XXXXX;db=flyocache4w;host=172.16.37.XXX;port=XXXX;" metric="10" behindloadbalancer="no"/>
>      </connections>
>      <router>
>      </router>    
> </instance>
> 
> 
> Anybody has any hints ?
> 
> TIA,
> 
> Chris
> 
> 
> 
> 
> -------------------------------------------------------------------------
> Check out the new SourceForge.net Marketplace.
> It's the best place to buy or sell services for
> just about anything Open Source.
> http://sourceforge.net/services/buy/index.php
> _______________________________________________
> Sqlrelay-discussion mailing list
> Sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion
> 
> __________________________________________________
> D O T E A S Y - "Join the web hosting revolution!"
>              http://www.doteasy.com



__________________________________________________
D O T E A S Y - "Join the web hosting revolution!"
             http://www.doteasy.com