<html>
<head>
<meta content="text/html; charset=ISO-8859-1"
http-equiv="Content-Type">
</head>
<body bgcolor="#FFFFFF" text="#000000">
<div class="moz-cite-prefix">Since that initial discussion I have
used innotop to track down locks and long running queries, (turned
out to be bad code).<br>
<br>
Glad you finally got it running:<br>
<br>
While doing that debugging I wrote this little shell script to
kill some of the stupid long queries causing locks that someone
might find useful.<br>
<br>
#!/bin/bash<br>
#Kill any queries running more than 15 minutes.<br>
date '+%F %R'<br>
mysql -s -e 'select id from information_schema.processlist where
info is not null and time > 900;'<br>
mysql -s -e 'select id from information_schema.processlist where
info is not null and time > 900;' | sed -E 's/^/mysqladmin
kill /' | bash<br>
<br>
<br>
--Jason<br>
<br>
<br>
On 10/10/2012 04:50 PM, Ed King wrote:<br>
</div>
<blockquote
cite="mid:1349902237.71862.YahooMailRC@web185004.mail.gq1.yahoo.com"
type="cite">
<style type="text/css"><!-- DIV {margin:0px;} --></style>
<div style="font-family:Courier
New,courier,monaco,monospace,sans-serif;font-size:10pt">
<div>I'm such a slacker... it took me 2 months to install
"innotop". I just finished the installation and got it to
connect. Looks like the default screen shows db uptime,
queries per second, and current sql statement executing on the
main screen. I hit "L" to get to the show-lock screen and
wouldn't you know it, I don't have any locks right now (not
complaining).<br>
<br>
</div>
<div style="font-family:Courier New, courier, monaco, monospace,
sans-serif;font-size:10pt"><br>
<div style="font-family:times new roman, new york, times,
serif;font-size:12pt"><font face="Tahoma" size="2">
<hr size="1"><b><span style="font-weight: bold;">From:</span></b>
Jason Brown <a class="moz-txt-link-rfc2396E" href="mailto:lists@masterforge.com"><lists@masterforge.com></a><br>
<b><span style="font-weight: bold;">To:</span></b>
<a class="moz-txt-link-abbreviated" href="mailto:chugalug@chugalug.org">chugalug@chugalug.org</a><br>
<b><span style="font-weight: bold;">Sent:</span></b> Fri,
August 17, 2012 12:05:37 PM<br>
<b><span style="font-weight: bold;">Subject:</span></b>
Re: [Chugalug] damned if you do, damned if you don't<br>
</font><br>
<div class="moz-cite-prefix">The default timeout is 50
seconds, thats a pretty long time for an insert or update.
Are you sure you can't trim that?<br>
You might be better served tracking down the locking
culprit and fixing the root issue, that or upgrading to
faster hardware.<br>
This looks useful, <a moz-do-not-send="true"
rel="nofollow" target="_blank"
href="http://www.xaprb.com/blog/2007/09/18/how-to-debug-innodb-lock-waits/">http://www.xaprb.com/blog/2007/09/18/how-to-debug-innodb-lock-waits/</a>
I have not used that exact method yet but now that I have
found it I plan to.<br>
<br>
Anyway, here are some lines from some of my server configs
that affect innodb performance.<br>
<br>
innodb_flush_method = O_DIRECT<br>
This one is very hardware dependent, may help, may hurt: <a
moz-do-not-send="true" rel="nofollow" target="_blank"
href="http://dev.mysql.com/doc/refman/5.1/en/innodb-parameters.html#sysvar_innodb_flush_method">http://dev.mysql.com/doc/refman/5.1/en/innodb-parameters.html#sysvar_innodb_flush_method</a>
(We use to avoid double buffering as the article says).<br>
<br>
innodb_flush_log_at_trx_commit = 2<br>
Depends on your situation, reduces disk write load by a
tiny amount.<br>
<br>
--Jason<br>
<br>
<br>
<br>
On 08/17/2012 12:50 PM, Ed King wrote:<br>
</div>
<blockquote type="cite">
<pre>----- Original Message ----
sooooooo... what magical incantations do you folks use in your my.cnf file to
avoid innodb locks yet still maintain transactional integrity?
did I stump the experts? ;) I've tried increasing the lock wait timeout and I
even turned off the locks completely (or so I thought) but I still get an
occasional lock-wait timeout. I don't see how I can make my transactions any
smaller than what they already are.
I was visiting my nephew last night when I asked if I
could borrow a newspaper.
"This is the 21st century." he said.
"We don't waste money on newspapers.
Here, you can borrow my iPad."
I can tell you, that bloody fly never knew what hit it...
_______________________________________________
<a moz-do-not-send="true" rel="nofollow" class="moz-txt-link-abbreviated" ymailto="mailto:chugalug@chugalug.org" target="_blank" href="mailto:chugalug@chugalug.org">chugalug@chugalug.org</a>
<a moz-do-not-send="true" rel="nofollow" class="moz-txt-link-freetext" target="_blank" href="http://www.chugalug.org/cgi-bin/mailman/listinfo/chugalug">http://www.chugalug.org/cgi-bin/mailman/listinfo/chugalug</a>
</pre>
</blockquote>
<br>
</div>
</div>
</div>
<br>
<fieldset class="mimeAttachmentHeader"></fieldset>
<br>
<pre wrap="">This body part will be downloaded on demand.</pre>
</blockquote>
<br>
</body>
</html>