MySQL Forums
Forum List  »  Performance

Stuck Queries Killing the Server
Posted by: Steven Weintraut
Date: August 30, 2006 03:50PM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Stuck Queries Killing the Server
1801
August 30, 2006 03:50PM
988
December 14, 2006 03:40PM


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.