damned if you do, damned if you don't

From: Ed King 
------------------------------------------------------
sooooooo...  what magical incantations do you folks use in your my.cnf file to 
avoid innodb locks yet still maintain transactional integrity?

=============================================================== From: Ed King ------------------------------------------------------ ----- 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...

=============================================================== From: Dee Holtsclaw ------------------------------------------------------ You're probably running into a deadlock condition. Going to have to have some form of locking else there's no transaction integrity (how else do you indicate "hands off" while updating?) I would suggest taking a hard look at the order you're updating things and make sure everything is modified in the same order. An alternative would be to actually set the time-outs quicker, trap the error, and redo everything when the error occurs. Funny.

=============================================================== From: Jason Brown ------------------------------------------------------ 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

=============================================================== From: Dee Holtsclaw ------------------------------------------------------ That is *incredibly* useful. Thanks for the link!!!

=============================================================== From: Ed King ------------------------------------------------------ From: Jason Brown 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. thanks for that link, it looks very useful indeed! I've googled and read a thousand webpages trying to find a solution to this problem and never saw that page in my searches if/when I find the solution to the problem I'll be sure to post my findings...

=============================================================== From: Mike Harrison ------------------------------------------------------ You mean like using "dirty reads" ? aka: "SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED" We only use actual locks in very very specific places. It's a database religion issue, there are many correct answers, and as long as you apply your theology consistently and understand it's conundrums and sins, you can achieve enlightenment. ONE issue is tables get "locked" when doing updates for many records. One answer is, don't do that. ie: Instead of: update geeks set geekscore = (select geekscore from edkingrating where id = geeks.id) Do something in php like: $q = "select id,geekscore from edkingrating order by id" ; $r = mysql

=============================================================== From: Ed King ------------------------------------------------------ ----- Original Message ---- I think a Chugalug meeting/session on MySQL tweaking and tuning would be a lot of edumacational fun. Willing to host at 701 Broad, Suite 201 next Wednesday the 22nd at 6pm. Or maybe at the Library 4th floor? great idea... and this would make a great 1st meeting for Library 4th floor count me in

=============================================================== From: Ed King ------------------------------------------------------ I dunno if the MySql tuning class is still a "go" for tonight but I'm gonna have to bail I must say, however, that using the "set session... uncommitted" directive coupled with a change in the order of write operations has decreased our lock-waits down to almost nothing. So.... thanks for that tip :) ----- Original Message ---- From: Mike Harrison To: CHUGALUG Sent: Fri, August 17, 2012 1:08:48 PM Subject: Re: [Chugalug] damned if you do, damned if you don't I You mean like using "dirty reads" ? aka: "SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED" We only use actual locks in very very specific places. It's a database religion issue, there are many correct answers, and as long as you apply your theology consistently and understand it's conundrums and sins, you can achieve enlightenment. ONE issue is tables get "locked" when doing updates for many records. One answer is, don't do that. ie: Instead of: update geeks set geekscore = (select geekscore from edkingrating where id = geeks.id) Do something in php like: $q = "select id,geekscore from edkingrating order by id" ; $r = mysql

=============================================================== From: Mike Harrison ------------------------------------------------------ It never went out as an official notice.. and I just got off the phone with meetings.. so.. uh.. that's a good thing. I think we'll need to do this soon though, I'm bumping into Nate at the Library again this week, I'd like to make this subject (MySQL Twicks) an early one at the library.... Just found out 'm travelling a lot next month.. WooHoo!

=============================================================== From: Ed King ------------------------------------------------------ ---- Original Message ---- It never went out as an official notice.. and I just got off the phone with meetings.. so.. uh.. that's a good thing. I think we'll need to do this soon though, I'm bumping into Nate at the Library again this week, I'd like to make this subject (MySQL Twicks) an early one at the library.... ------------------ ok just let us know "when" I'm interested in learning some mysql tuning altho there are plans to replace ours with postgresql asap ;-) ps: has Chugalug ever done a "lunch and learn" ? I don't know if that would help/hurt attendance...

=============================================================== From: Aaron welch ------------------------------------------------------ I would be down for creating a list of questions that people want answered at meetings. Limited to 5-10 for brevity sake, but voted on the mailing list before the meeting. Once I get back from Asia, I will try to host/call more lunch and evening meetings. Especially since we have so many cool spaces creeping up in Chatty. -AW

=============================================================== From: Ed King ------------------------------------------------------ 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 ------------------------------------------------------ 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

=============================================================== From: 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