Stuck Queries Killing the Server
Sometimes queries are executed which seem to hang forever. That's no so bad. The problem is that all of queries that come after it are also hanging until that first query is killed
We have been having issues on both an OS X box runing the latest 4.1X release and we also tried a RedHat Box running the same release too
In the example below, the query that started this issue in this instance is indeed the first one listed, it was a simple update to a single record and a single field
we have other instances where we are doing a select query on a large number of records, and it also locks every other query out
as you can see below, it shouldn't take 421 seconds to update one field in one record
if we kill the first process, all the rest finish up as normal in a matter of seconds. but until we kill the stuck query, all of the rest just pile up, even if they are on different tables! the stuck queries seem to only occur on our larger tables (2 GB or so in size, 1 million records)
note: most of the tables we have in the system are currently MyIsam tables, although we have a few InnoDB tables, they are not part of this problem shown below (at least in this case)
we just updated to the very latest build of 4.1.X and reimported all of the data fresh from a backup, and the problme is still there
these are on servers with 2GB of RAM, RAID storage, and 2GHZ dual processors
421 - update CustomerData set ReferralSource='Active.com ' where RecordID=907668
421 - select * from CustomerData where (Phone1AreaCode='103' or Phone2AreaCode='103') order by LastName lim
416 - select * from CustomerData where (Phone1AreaCode='103' or Phone2AreaCode='103') order by LastName lim
393 - SELECT ep.links.redirectURL, maintables.CustomerData.CustomerNumber FROM ep.links, maintables.co
374 - select * from CustomerData where CustomerNumber=800435
360 - SHOW TABLE STATUS FROM `maintables`
333 - SELECT ep.links.redirectURL, maintables.CustomerData.CustomerNumber FROM ep.links, maintables.co
332 - select * from CustomerData where CustomerNumber=800435
295 - select MapEMailAddress from CustomerData, CustomerEMailMaps where CustomerData.recordid=CustomerEMailMaps.Ma
292 - Select FirstName,LastName,CustomerNumber,NewWorldSalesRep from CustomerData where RecordID=1066652
285 - select distinct(OrderFormRecordID),CustomerNumber,FirstName,LastName,City,State,NewWorldStepOfSale,N
265 - SELECT ep.SPCustomerMap.SPMapID, maintables.CustomerData.RecordID, maintables.CustomerData.CustomerNumber, maintables.Co
245 - select * from CustomerData where (CustomerNumber='123456') order by LastName limit 50
240 - SELECT ep.links.redirectURL, maintables.CustomerData.CustomerNumber FROM ep.links, maintables.co
218 - select MapEMailAddress from CustomerData, CustomerEMailMaps where CustomerData.recordid=CustomerEMailMaps.Ma
216 - select * from CustomerData where CustomerNumber=800435
211 - select * from CustomerData where (CustomerNumber='987654') order by LastName limit 50
180 - SELECT ep.links.redirectURL, maintables.CustomerData.CustomerNumber FROM ep.links, maintables.co
173 - select * from CustomerData where (LastName like 'murdock%') order by LastName limit 50
112 - select * from CustomerData where CustomerNumber=979513