MySQL Forums :: General :: Why is my entire database getting locked for a slow select query?


Advanced Search

Why is my entire database getting locked for a slow select query?
Posted by: Derek Krzanowski ()
Date: August 23, 2010 04:04PM

I've been fighting this battle for a long time now and can't seem to figure out what is causing my entire server to come to a standstill every time a slow query is ran.. We are running what started out as a wordpress-mu install (not my choice) that is now highly customized. We started experiencing site "slow-downs" randomly a few months back and I found a bunch of horribly written queries that were the culprit so I fixed them and everything was great for a little while. Now we are getting more traffic and we have the exact same problem with queries that couldn't possibly be more optimized. We have our wp-mu install split into 257 databases and if a table in any one of them becomes locked for any reason, the entire system stalls until it either finishes or times out. It is not because our server can't handle it, we have an 8-core machine with 16gb of ram and nothing every gets maxed out except for the one core that has the offending query on it... I was never able to replicate this problem reliably until today. This time however, it's with an insert statement called from a trigger. I've tried changing the tables from myisam to innodb with no luck. It seems no matter what I do, every queries "locks" the entire system until it completes...

I know you want examples but because it happens with slow queries to any table whether it's a read or write, it's pretty pointless to give query examples since there are many thousands of possibilities... Is there some kind of config setting that could cause this? Any help given is GREATLY appreciated!!

Options: ReplyQuote


Subject Written By Posted
Why is my entire database getting locked for a slow select query? Derek Krzanowski 08/23/2010 04:04PM
Re: Why is my entire database getting locked for a slow select query? Derek Krzanowski 08/24/2010 02:00PM
Re: Why is my entire database getting locked for a slow select query? Shawn Taylor 08/24/2010 02:41PM
Re: Why is my entire database getting locked for a slow select query? Derek Krzanowski 08/24/2010 03:04PM
Re: Why is my entire database getting locked for a slow select query? Shawn Taylor 08/25/2010 07:39AM
Re: Why is my entire database getting locked for a slow select query? Derek Krzanowski 08/25/2010 02:02PM
Re: Why is my entire database getting locked for a slow select query? Shawn Taylor 08/25/2010 04:49PM
Re: Why is my entire database getting locked for a slow select query? Rick James 08/24/2010 11:30PM
Re: Why is my entire database getting locked for a slow select query? Derek Krzanowski 08/25/2010 05:21AM
Re: Why is my entire database getting locked for a slow select query? Shawn Taylor 08/25/2010 02:46PM
Re: Why is my entire database getting locked for a slow select query? Derek Krzanowski 08/25/2010 04:08PM
Re: Why is my entire database getting locked for a slow select query? Shawn Taylor 08/25/2010 04:58PM
Re: Why is my entire database getting locked for a slow select query? Rick James 08/25/2010 08:03PM
Re: Why is my entire database getting locked for a slow select query? Derek Krzanowski 08/26/2010 10:03AM
Re: Why is my entire database getting locked for a slow select query? Shawn Taylor 08/26/2010 10:59AM
Re: Why is my entire database getting locked for a slow select query? Derek Krzanowski 08/26/2010 11:30AM
Re: Why is my entire database getting locked for a slow select query? Shawn Taylor 08/26/2010 12:47PM
Re: Why is my entire database getting locked for a slow select query? Rick James 08/26/2010 08:15PM
Re: Why is my entire database getting locked for a slow select query? Shawn Taylor 08/26/2010 08:31PM
Re: Why is my entire database getting locked for a slow select query? Rick James 08/26/2010 10:17PM
Re: Why is my entire database getting locked for a slow select query? DBA Lead 09/07/2010 01:21PM
Re: Why is my entire database getting locked for a slow select query? Rick James 09/07/2010 08:16PM
Re: Why is my entire database getting locked for a slow select query? DBA Lead 09/07/2010 08:34PM
Re: Why is my entire database getting locked for a slow select query? Rick James 09/07/2010 10:51PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.