Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

We have an application that has been deployed to 50+ websites. Across these sites we have noticed a piece of strange behaviour, we have now tracked this to one specific query. Very occasionally, once or twice a day usually, one of our debugging scripts reports

2006 : MySQL server has gone away

I know there are a number of reasons this error can be thrown but the thing that is most strange is that every single time it is thrown it happens from the same SQL query being run. There is nothing strange or complex about this query, it looks like this:

SELECT `advert_only` FROM `products` WHERE `id` = '6197'

This query must run tens of thousands of times a day, for various different product IDs so it certainly doesnt fail each time. It fails randomly on seemingly random sites across our 4 servers. There is seemingly no commonality, one small thing we have noticed is that it sometimes will happen on 2 or 3 page loads in a row for 1 specific person as we also track the IP of the person it has happened to.

This is on CentOS 5 servers running MySQL 5.0.81

share|improve this question
    
What exactly is your question? –  Scoregraphic Nov 23 '09 at 10:49
    
sorry i thought that was obvious, question is why does this query randomly cause the mysql error shown above –  seengee Nov 23 '09 at 10:54

3 Answers 3

Damn and BLAST my low reputation! I can't post a question/comment above. So I'm sorry this isn't an answer, but hopefully it might lead you in the right direction.

Do you update the products table? Do you see any correlation? Are you able to run the SELECT without locks, reading uncommitted rows?

If you don't need to restrict your queries to committed data, you should include this statement before your select to avoid locking issues:

set transaction isolation level READ UNCOMMITTED;

Also, just in case the "gone away" message is performance related, do you have an index on the 'id' column?

share|improve this answer
    
we do have an index on ID. Not too familiar with the concept of committed and uncommitted though, i'll have to look into that. –  seengee Nov 23 '09 at 13:15

I think, the error is only partly related to that query. Maybe the products table is extremly large and a timeout happens (??). Maybe you should check if the indices are fragmented.

See this link for possible causes: http://dev.mysql.com/doc/refman/5.1/en/gone-away.html See this link for defragmentation: http://dev.mysql.com/doc/refman/5.1/en/innodb-file-defragmenting.html

share|improve this answer
    
its a MyISAM table so not sure the second link is relevant. I've had a look at that other list before and i'm pretty sure that none of those reasons relate to our issue. –  seengee Nov 23 '09 at 11:22

I expect it's crashing or you have a network problem between your app server and mysql server.

If the server is crashing, you'll see lots of messages on the log. Do you see anything on the log?

If you're running a 32-bit box, the crash is probably caused by running out of address space as you haven't taken into account address space in your tuning. Do not tune it such that the virtual memory used is > 2G, or you're in trouble.

Don't run 32-bit MySQL if you can avoid it.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.