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

[Jason Brown]

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/20121010/633894c6/attachment-0001.html>