[Chugalug] damned if you do, damned if you don't

[Ed King]

cool query ;)  thanks

Usually by this time of the day (our mysql db is busiest between 8am and noon) 
we would have experienced a lock-wait or two or three.  Haven't seen a single 
lock-wait today.   I think the db knows I'm watching it, so its on its best 
behavior





________________________________
From: Jason Brown <lists at masterforge.com>
To: Chattanooga Unix Gnu Android Linux Users Group <chugalug at chugalug.org>
Sent: Wed, October 10, 2012 4:07:46 PM
Subject: Re: [Chugalug] damned if you do, damned if you don't


Since that initial discussion I have       used innotop to track down locks and 
long running queries, (turned       out to be bad code).

Glad you finally got it running:

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.

#!/bin/bash
#Kill any queries running more than 15 minutes.
date '+%F %R'
mysql -s -e 'select id from information_schema.processlist where       info is 
not null and time > 900;'
mysql -s -e 'select id from information_schema.processlist where       info is 
not null and time > 900;' |  sed -E 's/^/mysqladmin       kill /' | bash


--Jason


On 10/10/2012 04:50 PM, Ed King wrote:

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).
>
>
>
>
>
________________________________
From: Jason Brown <lists at masterforge.com>
>To: chugalug at chugalug.org
>Sent: Fri,               August 17, 2012 12:05:37 PM
>Subject: Re: [Chugalug] damned if you do, damned if you don't
>
>
>The default timeout is 50               seconds, thats a pretty long time for an 
>insert or update.               Are you sure you can't trim that?
>You might be better served tracking down the locking               culprit and 
>fixing the root issue, that or upgrading to               faster hardware.
>This looks useful, 
>http://www.xaprb.com/blog/2007/09/18/how-to-debug-innodb-lock-waits/                
>I have not used that exact method yet but now that I have               found it 
>I plan to.
>
>Anyway, here are some lines from some of my server configs               that 
>affect innodb performance.
>
>innodb_flush_method = O_DIRECT
>This one is very hardware dependent, may help, may hurt: 
>http://dev.mysql.com/doc/refman/5.1/en/innodb-parameters.html#sysvar_innodb_flush_method
> (We use to avoid double buffering as the article says).
>
>innodb_flush_log_at_trx_commit = 2
>Depends on your situation, reduces disk write load by a               tiny 
>amount.
>
>--Jason
>
>
>
>On 08/17/2012 12:50 PM, Ed King wrote:
>
>----- 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... 
>_______________________________________________ chugalug at chugalug.org 
>http://www.chugalug.org/cgi-bin/mailman/listinfo/chugalug 
>

>
>
>This body part will be downloaded on demand.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://chugalug.org/pipermail/chugalug/attachments/20121011/2d09c737/attachment.html>