MySQL Forums
Forum List  »  General

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?
August 23, 2010 04:04PM


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.