4

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?

Dharman
  • 26,923
  • 21
  • 73
  • 125
Demirramon
  • 43
  • 7
  • 1
    How much traffic do you get? If you frequently have 15 concurrent users, I imagine `max_user_connections` isn't the only issue you're experiencing on a low-end hosting plan. Now, suppose a single script call takes 0.33sec from start to finish, or for the duration the DB is being accessed. It means you'd have 45 unique users per second (assuming users don't load pages every second!). This is heavy traffic. If this isn't the case, it's possible that you are running [persistent connections](https://www.php.net/manual/en/features.persistent-connections.php) that hogs up your connection quota. – Markus AO Dec 04 '20 at 12:12
  • 1
    In any case, closing and reopening the database link for each query would be massive overhead and would probably make the total database usage time time per user per call more than it is when the DB link is simply open for the duration. Yes, you could always capture a failed connection attempt, have the script sleep for a moment, and retry. If nothing else helps, as a last resort sort of thing. Just for the record, how many queries on average are you running per page call? Also: Ensure you don't have hordes of AJAX calls per page load that cause unique database connections. – Markus AO Dec 04 '20 at 12:16
  • 1
    15 is anything but low. it should be fine with a site with thousands visitors. – Your Common Sense Dec 04 '20 at 13:57
  • reconnect every few seconds for a few more times is a disaster. it means your script will be running "a few seconds" instead of few milliseconds, making it unable to connect for other clients. connect to web-server I mean. – Your Common Sense Dec 04 '20 at 14:00
  • 1
    connecting in the every function is also a disaster. Connection is relatively slow an operation and your script will be executing longer, again consuming server resources. Keep to the recommendations from the answer below and you'll be fine. Great question btw. – Your Common Sense Dec 04 '20 at 14:03

1 Answers1

5

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.

Dharman
  • 26,923
  • 21
  • 73
  • 125