[Answered] Mysql Too Many Connections

[firstclass]

Hi all,

 

my game is having a nice little test with ~30 users but we are regularly suffering from errors with "Too many connections" to mySQL... this is happening when I am the only user and so I don't believe can be a result of our site activity... "SHOW PROCESSLIST" shows no running processes.

 

Is there a global stevie wide connection limit that is regularly getting exceeded? If not any ideas why we are getting this

[Sove]

Are you sure all your mysql connections are properly closed when they are not needed? Failing to do that is the most common reason for that error.

[firstclass]

I don't think that can be the problem, they are closed in the php files, but should auto-close at the end of the PHP anyhow.

 

NOTHING shows up when using "SHOW PROCESSLIST" except that command itself.

[Krydos]

Are you using persistent mysql connections? It's always a good idea to explicitly close any mysql connections as soon as you no longer need them. Relying on php garbage collection to close the connections for you can result in wildly varying results.

[firstclass]

As I said I believe ALL connections are closed by our scripts, I've tried the switch to persistent connections but I expect the same result.

 

I'm still left suspicious:

Is there a global stevie wide connection limit that is regularly getting exceeded?

[Shinryuu]

I've tried the switch to persistent connections

 

Persistent connections are bad, please refrain from using them. Krydos asked if you were using them because they tend to be the cause of such problems, not the solution.

[firstclass]

So any more suggestions? I'm guessign I'm right and it's a global connection limit that's getting exceeded.

[Krydos]

What server are you on?

[firstclass]

See first post, Stevie.

 

I've made a change today that I hope will make a difference... time will tell.

[Krydos]

Is there a global stevie wide connection limit that...

Wow, I've read that post probably ten times and always read it as "global server wide" each and every time. Here's to seeing what you expect to see instead of what is actually there. With over 4000 posts I admit I probably skim over things a bit faster than I did when I had less than 100 posts.

 

The reason I'm hesitant to change any settings is because the settings are the way they are to help prevent abuse from one account. If we let one account go nuts and overuse mysql then the other several thousand accounts on the same server would have poor performance. What makes the one account so much more important that the other thousands? We all pay the same amount for the hosting here.

 

Furthermore, I have raised the limits before, but usually when the limits need to be raised we have 2-20 people on here at the same time complaining about the same errors. Remember what I said about thousands of accounts? Why are all of those other accounts, the majority of which use mysql too, not having any issues with the current settings, but you (and only you) are? Sure, some people are never going to report a single error with their website, and some people report an error each time they sneeze and there is a spot on their screen, but those are outliers on the bell curve.

 

Anyways, let us know if your most recent change solves things or if it doesn't. I'm willing to tweak the settings for you a little, but it's best to make sure you aren't doing something odd first before potentially breaking the whole server.

[firstclass]

I've not seen it for a day or 2. And I found a bug in the code that potentially opened multiple (never more than 3) connections and only closed one of them (they all had the same identifier!) That can't happen now, hopefully that was the cause of the issues.

 

But this forum isn't the most active place, it's difficult to know If I'm alone with the errors, particularly when they last for ~10 seconds (even if regularly), if no-one spots, or reports or does so here.

[firstclass]

Happened a couple of times again today. Again while the server was very slow, even when I have no other online users.

[Gourlaykid]

My site has being going down frequently recently with the error from MySQL "Too many connections", also I noted that there was a high load on Steve and I wondered if this was the problem

[firstclass]

Having spent a month or so without seeing this ugly error, its back with a vengeance today.

 

All connections are closed, none are permanent.

 

Back to the same old suspicions - this is not my site.

[Krydos]

I noted that there was a high load on Steve and I wondered if this was the problem

Probably. High load affects mysql and ftp more than other services like http.