php - What is the best way to minimize number of connections?

Question

My host has a really, really low number of max connections for a database user. This is the error my users are getting:

User 'username_here' has exceeded the 'max_user_connections' resource (current value: 15).

I don't think it's in my power to raise that value, unless I upgrade to a much more expensive plan, so I'm looking for a way to use these 15 connections effectively. My way of handling connections is the following: I connect to the database in a script I load at the start of every page load and then I pass that connection to a function that runs the queries. I thought I could minimize the time a connection is open by opening the connection inside the query function and closing it right after the return statement, is that fine or am I making things more complicated for no reason?

As a last resort, I was thinking of putting the connection inside of a try/catch and attempt to reconnect every few seconds for a few more times. Would that be something wise to do, or is it even worse?

Answer 1

Here's how you can optimize the number of connections:

• Make sure that you are not using persistent connection anywhere. This is the easiest way to lose track of open connections and the most common reason for running out of available connections. In mysqli the persistent connection is opened by prepending p: to the hostname when connecting.
• Make sure that you are only opening a single connection on each HTTP request. Don't open and close them as this can quickly get out of hand and will have bad performance impact on your application. Have single global connection that you pass around to functions that need it.
• Optimize your queries so that they are processed faster and free up the connection quicker. This also applies to optimizing indexes and getting rid of the N+1 problem. (From experience I can say that PDO helps a lot in refactoring your code to avoid poorly designed queries.)
• If you need to perform some other time-demanding task in the same process, do all your SQL operations first and then close the connection. Same applies to opening the connection. Open it only when you know you will need it.

If you find yourself running into a problem of exceeding the 'max_user_connections' limit then it means that your web server is not configured properly. In an ideal scenario the MySQL connection would be unlimited, but on shared hosting this limitation has to be put in place to protect against resource abuse (either accidental or on purpose). However, the number of available MySQL connection should match the number of available server threads. This can be a very opinionated topic, but I would say that if your application needs to perform some SQL operation on every request then the number of available server connections should not exceed the number of available MySQL connections. On apache, you can calculate the number of possible connections as shown in this link.

On a reasonably designed application even with 15 concurrent MySQL connections, you should still be able to handle a satisfactory amount of requests per second. For example, if each request takes 100ms to complete, you could handle 150 requests per second.