4

MySQL has a feature that enforces a maximum number of connections, max_user_connections.

When connecting from PHP (and other languages), in the event you exceed that number, my understanding is that your connection will fail with an error stating that you have exceeded the maximum number of connections. Correct?

My preference would be to instead have the connect call wait (up to some timeout limit) for a connection to become available. However, I have not been able to determine a way to do this from reading through the MySQL docs and searching the web.

My concern is that, in the event we have a flood of traffic to our web app that resulted in a large number of concurrent MySQL connection attempts, some of our users scripts will end with an error. Naturally, we could modify the calling code to try to re-connect up to a certain number of times, but it would much cleaner if we could modify the connection attempt itself to handle this, instead of having to wrap every "connect" call in a loop. Additionally, a loop would not result in a real FIFO queue, because each individual calling thread would wait a small period of time and try again, and whether it got a connection would depend on whether, at that particular moment, a connection was available. It would then wait again, and while it was waiting a connection might open and a different thread "further back in line" might grab it.

So, when using any of the PHP APIs to connect to MySQL, is there a way to attempt the connection in "wait until a connection is available" fashion?

Community
  • 1
  • 1
Josh
  • 7,232
  • 8
  • 48
  • 75
  • I have seen this problem dozens of times over the years. I think your approach is flawed. The cause is most likely an inefficiency in the programming, like queries are taking way too long (so optimize the offending queries or optimize your table indexing, etc.) or you are going to the database far too often and getting data that should be cached. If you really do need more connections (which is very doubtful) just make a new user and split up the connections between two users. – dataskills Apr 26 '14 at 16:26
  • I appreciate the architectural feedback, but remember that in the web world, requests can occur simultaneously. If you get hit with ten concurrent requests, your max_user_connections is four, and you are only permitted one user (this is the case with the lowest ClearDB tier, btw), then you've got six connections too many. Your queries might only take 25ms to complete, but if the ten requests were truly concurrent, you had six requests fail. – Josh Apr 26 '14 at 23:31
  • Also, this is entirely hypothetical. I'm not currently experiencing a "max_user_connections limit exceeded" error. I'm trying to figure out the best strategies to avoid one, and allowing connection requests to wait seems like *one* strategy. The others you mentioned, like caching data where appropriate, are certainly valid, but not what my question is about. – Josh Apr 26 '14 at 23:33
  • http://stackoverflow.com/questions/9736188/mysql-persistent-connection-vs-connection-pooling – Cypher May 07 '14 at 21:56
  • http://en.wikipedia.org/wiki/Connection_pool – Cypher May 07 '14 at 21:57
  • @Cypher, thank you for your comment, but it does not address my question about how to get connection attempt to wait (up until some timeout period) for a connection to become available. If you have a max_user_connections limit of 4, it doesn't matter if you're pooling or not--once there are four open connections, the fifth connection attempt will fail. This is clearly articulated in the links you provided, e.g., "There is no way to send two requests on the same connection" and "If all the connections are being used, a new connection is made." Did you read the content at either location? – Josh May 09 '14 at 13:11
  • By the way, what MySQL calls "connection pooling" is perhaps better described as "persistent connections." Read one blogger's distinction here: databasesoup.com/2013/07/connection-pooling-vs-persistent.html – Josh May 09 '14 at 13:11
  • @Josh, they aren't answers at all that's why their comments. Sometimes people land on these questions from a google search, and these links may help them. Your questions reads as a lack of understanding of connection pooling (however you want to describe it) works, I can only assume that others who stumble on this question may be in the same boat. – Cypher May 09 '14 at 16:04
  • @Josh, I apologize if my assumptions about your understand of the subject is wrong, that's just how I interpreted the question. :) – Cypher May 09 '14 at 16:05

2 Answers2

2

MySQL doesn't have any feature to do what you're describing. That is, MySQL has no FIFO (queue) of connections when you hit max_connections. All connections get an error if they can't get connected immediately.

The closest related thing built into MySQL is the config variable back_log, which only configures the limit on the number of outstanding connections. That is, the main MySQL thread takes a small amount of time to check for incoming connections, and these stack up in the listen queue, which is before the socket even completes and before MySQL determines if we've got more than max_connections threads. In fact, you could get a bunch of outstanding connections in the listen queue even if the server is currently far below max_connections threads.

http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_back_log

Another option is to add HA-Proxy into the mix, because HA-Proxy does have provide queue feature. Here's a blog that explains this more: http://flavio.tordini.org/a-more-stable-mysql-with-haproxy

Frankly, most sites just increase max_connections until it can handle your typical spikes in traffic, and from there just try to optimize database activity, so connections are short in duration and therefore you have good throughput. It also helps to code your application to connect as late as possible and disconnect as promptly as possible once all queries are done.

If you have an atypical spike in traffic that exceeds max_connections, then your application will receive an error indicating that. You should code your app to handle such errors gracefully. That is, display as nicely as you can a message like "We're sorry, our load is too great to handle your request at the moment, please try back in a minute." That's at least better than an abrupt white screen, or a stack trace or something.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
-2

Try add below php code in all php page in footer:

  mysql_close();

Normally your error message is the mysql connection without close and keep hold the connections in server side. However, it just my stupid coding error in long time ago. If close the mysql connection also cannot fix your problem. I think you need to login to server to change some setting of php.ini to increase the connection user value. I wish my answer can help you to resolve problem.

Brad Wong
  • 1
  • 3
  • 1
    Thank you for your attempt at providing an answer, but my question has nothing to do with missing mysql_close() calls. – Josh May 09 '14 at 13:08