Re: [Sqlrelay-discussion] Unknown prepared statement handler?
Brought to you by:
mused
Menu
▾
▴
Re: [Sqlrelay-discussion] Unknown prepared statement handler?
|
From: <ni...@sk...> - 2009-04-14 16:17:42
|
Some more diagnostic stuff that may help:
sqlr-status outout
------------------
sqlr-status -id mysqltest -config <absolute path
root>/sqlrelay-0.40/config_file/conf.xml
Warning: using default connectionid.
Open Server Connections: 55
Opened Server Connections: 3400
Open Client Connections: 0
Opened Client Connections: 22107625
Open Server Cursors: 1375
Opened Server Cursors: 85000
Times New Cursor Used: 0
Times Cursor Reused: 22619095
Total Queries: 22619090
Total Errors: 0
Forked Listeners: 0
Number of actual open DB connections
------------------------------------
I noticed that the number of open database connections had dropped to
zero, as shown by the MySQL command 'show processlist'.
Thanks,
Nick
> Hi Folks,
>
> I've recently been evaluating SqlRelay and have been impressed by what
> it's been able to do for my problem. Thanks for making it available!
>
> What's going on?
> ----------------
>
> Before deploying it, I ran a soak test to investigate it's ability to
> handle sustained load. The actual soak test went very well, but I did
> encounter one problem in this situation:
>
> * SqlRelay is configured to have between 50 and 100 client connections
> * SqlRelay starts up fine
> * Load test runs successfully and completes OK
> * SqlRelay ramps down the number of open client connections to 0
> * A number of hours pass...
> * Subsequent accesses to SqlRelay fail with the following error message:
>
> Unknown prepared statement handler (322816) given to mysql_stmt_close
>
> This appears to be repeatable, and only occurs after the SqlRelay process
> has been idle for some time.
>
> Has anyone experienced this, and did you manage to fix it?
>
>
> Thanks in advance for your help,
>
>
> Nick
>
>
> ============
> System setup
> ============
>
> Source and OS versions
> ----------------------
>
> Sql Relay version : 0.40, built from source
> Rudiments version : 0.32, built from source
>
> SqlRelay host OS version : Redhat Enterprise Linux 4
>
> Database : MySQL enterprise 5.0.48-enterprise-gpl-log
> Database host OS version : Redhat linux 5
>
> Perl version : 5.8.8, 64 bit compile
>
> Config file setup
> -----------------
>
> <?xml version="1.0"?>
> <!DOCTYPE instances SYSTEM "sqlrelay.dtd">
> <instances>
>
> <!--
> This is some junk to make SQLrelay parse its XML correctly.
> Without this, one part of the system doesn't start up properly.
>
> It isn't actually used.
> -->
>
> <instance id="dummy" port="12009" socket="/tmp/sybase-dummy.socket"
> dbase="freetds" connections="5" maxconnections="20" maxqueuelength="5"
> growby="1" ttl="60" endofsession="commit" sessiontimeout="600"
> runasuser="studma" runasgroup="users" cursors="10" authtier="listener"
> handoff="pass" deniedips="" allowedips="" debug="none"
> maxquerysize="65536" maxstringbindvaluelength="4000"
> maxlobbindvaluelength="71680" idleclienttimeout="-1" maxlisteners="-1"
> listenertimeout="0" reloginatstart="false">
> <users>
> <user user="user" password="pw"/>
> </users>
> <connections>
> <connection connectionid="sybase-ba1"
> string="user=sa;password=;server=SYBTEST;db=ba31;charset=iso_1;"
> metric="1" behindloadbalancer="no"/>
> </connections>
> </instance>
>
> <!-- This is the configuration which I actually use -->
>
> <instance id="mysqltest" port="9000" socket="/tmp/mysqltest.socket"
> dbase="mysql" connections="50" maxconnections="100" maxqueuelength="0"
> growby="5" ttl="60" endofsession="commit" sessiontimeout="600"
> runasuser="npavey" runasgroup="design" cursors="25" authtier="listener"
> handoff="pass" idleclientimeout="10" maxlisteners="100"
> listenertimeout="120">
>
> <users>
> <user user="mysqltest" password="mysqltest"/>
> </users>
>
> <connections>
> <connection connectionid="mysqltest" string="user=redbase_ad
> password=nezjJGDG;db=trs_benchmarking;host=
> bd-midir005;port=3306" metric="1"/>
> </connections>
>
> </instance>
>
> </instances>
>
>
> Command line used
> -----------------
>
> sqlr-start -id mysqltest -config <absolute path
> root>/sqlrelay-0.40/config_file/conf.xml
>
>
> client side code being issued
> ------------------------------
>
> use SQLRelay::Connection;
> use SQLRelay::Cursor;
>
> my $sqlr_con =
> SQLRelay::Connection->new($host,$port,"",$user,$password,0,1);
> my $sqlr_cur = SQLRelay::Cursor->new($sqlr_con);
>
> my $sql = "SELECT MIN(test_id) FROM test_table";
> my $start_tid = select_scalar($sqlr_cur, $dat_file, $sql);
>
>
> sub select_scalar {
> my ($sqlr_cur, $dat_file, $sql) = @_;
>
> my $limit = 5;
> my $count = 0;
>
> for ($count; $count < $limit; $count++) {
> my $rtn = $sqlr_cur->sendQuery($sql);
>
> if ($rtn == 0) {
> my $err_msg = $sqlr_cur->errorMessage();
>
> # These errors are generally caused by too much
> # traffic at the SQLrelay. They should be recoverable
> # provided the traffic doesn't stay high for very long periods.
> SWITCH : {
>
> if ($err_msg =~ /Too many listeners/ ) {
> print "\nEncountered 'too many listeners' error - backing off and
> retrying\n";
> last SWITCH;
> }
>
> if ($err_msg =~ /Failed to authenticate/ ) {
> print "\nEncountered 'failed to authenticate' error - backing off and
> retrying\n";
> last SWITCH;
> }
>
> # If we didn't match the error, then throw a fatal error.
> write_cx_error($sqlr_cur->errorMessage(), $dat_file);
>
> } # end of SWITCH
>
> my $delay = int (rand (15) );
>
> print " current retry : $count\n";
> print " retry limit : $limit\n";
> print " backoff length : $delay\n\n";
>
> sleep $delay;
>
> } else {
>
> # We got a success code, so we don't need to repeat the query
> # drop out of the FOR loop
> last;
> }
>
> } # end of FOR loop
>
> if ($count == $limit) {
> my $msg = "Retry limit reached.\n";
> $msg .= "$sql\n";
>
> die "$msg\n";
> }
>
> my $data_val = $sqlr_cur->getField(0,0);
>
> return $data_val;
> }
>
> Investigation so far
> --------------------
>
> I did check whether the system is still running. I checked the contents of
> the 'pids' directory and things appear to be OK. The listener, scalar,
> cachemanager and connection processes are still running.
>
> I also ran 'strace' on the listener when I ran the SQL which caused the
> problem:
>
> strace -p 5788
> Process 5788 attached - interrupt to quit
> select(8, [3 4 5 6 7], NULL, NULL, NULL) = 1 (in [6])
> accept(6, {sa_family=AF_INET, sin_port=htons(59954),
> sin_addr=inet_addr("165.204.20.21")}, [3668744855036624912]) = 58
> setsockopt(58, SOL_TCP, TCP_NODELAY, [4294967297], 4) = 0
> semop(2031623, 0x4, 5340896) = 0
> clone(child_stack=0,
> flags=CLONE_CHILD_CLEARTID|CLONE_CHILD_SETTID|SIGCHLD,
> child_tidptr=0x2a95c438b0) = 31717
> close(58) = 0
> select(8, [3 4 5 6 7], NULL, NULL, NULL) = ? ERESTARTNOHAND (To be
> restarted)
> --- SIGCHLD (Child exited) @ 0 (0) ---
> wait4(-1, NULL, WNOHANG, NULL) = 31717
> wait4(-1, NULL, WNOHANG, NULL) = -1 ECHILD (No child processes)
> rt_sigreturn(0xffffffffffffffff) = -1 EINTR (Interrupted system
> call)
> select(8, [3 4 5 6 7], NULL, NULL, NULL <unfinished ...>
>
> And I also ran strace on the scaler:
>
> strace -p 6877
> Process 6877 attached - interrupt to quit
> semop(2031623, 0x8, 5313712) = 0
> semop(2031623, 0x8, 5313488) = 0
> semop(2031623, 0x8, 5313552) = 0
> semop(2031623, 0x8, 5313904) = 0
> semop(2031623, 0x8, 5313712 <unfinished ...>
>
>
>
>
>
> ------------------------------------------------------------------------------
> This SF.net email is sponsored by:
> High Quality Requirements in a Collaborative Environment.
> Download a free trial of Rational Requirements Composer Now!
> http://p.sf.net/sfu/www-ibm-com
> _______________________________________________
> Sqlrelay-discussion mailing list
> Sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion
>
|
