Topic: MySQL errors

[ober]

One of my client's sites has a very query intensive index page.  And I get errors pretty often that indicate that the MySQL server has either 'gone away' or that it 'lost connection during query'.  I even recently reduced the number of queries at least in half and I'm still getting them.  I probably need to switch all of these queries over to the activerecord model that I have partially implemented or I need to determine why I keep losing the connection.  I don't understand why it would be happening.  It has even happened on other pages on that site where there aren't that many queries.  He has a decent amount of traffic but it's not that high.

Like I was trying to reproduce it and it happened to me so I checked the tracker I have on the site and there are 10 people currently on there and it's on a shared server, but surely that shouldn't be the problem.  The host has even moved the site to a different server and the frequency of the errors hasn't changed.

I'm just not clear on why it's happening and the host claims that it's not a problem with the server (which I believe), but I just can't find a specific reason with the code that would cause it.

[Mike]

Are you using persistent connections (mysql_pconnect vs mysql_connect)?

[ober]

My code uses mysql_connect.  I don't know what phpactiverecord uses.

[ober]

Coming back to this topic... I switched that site to use persistent connections and I still get 1-5 mysql drops per day.  Any other ideas??

[Perspective]

The db server limits the number of active connections at any given time. Do you know how many clients are connected when you get drops?

[Perspective]

http://major.io/2007/01/24/increase-mysql-connection-limit/

[ober]

Looking over the past month in Google Analytics, the peak is 143.  I think I tried using that in the past without any luck though.

I also noticed looking at the resource usage, that the site frequently pegs the CPU usage and it indicates that the 'site has been limited' in the past 24 hours so I'm wondering if it's just peaking and he needs more resources/needs to move to a bigger server.

[Mike]

The "MySQL has gone away" errors I've seen in the past have been caused by connections being left open, not getting traffic for a period of time, and the server closing the connection without the client knowing.  We used to get them all the time on our dev and testing server.

Probably the easiest way to deal with it is to use a wrapper that'll catch the error, reopen the connection, and reissue the query.

[ober]

Yeah, I thought about doing that, but that would require a ton of work.

I also just read this: http://club.orbisius.com/howto/web-development/change-mysqls-wait_timeout-interactive_timeout-variables/

The support ticket I submitted to the host this morning indicated that there were a lot of sleeping sessions so hopefully if I kill them off faster it will help.

[ober]

The host just knocked the wait_timeout value down to 30 so we'll see how that goes.

[ober]

Well fuck me... finally figured out that the cause of all of this was that the site was hitting the shared hosting resource limits.  We upgraded to a low level VPS package and shazaam.  Not a single error since!  I didn't even know cPanel showed you when you were hitting resource limits until a few days ago.  God it's good to have this behind me!