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
  • 30,962
  • 25
  • 85
  • 135
Demirramon
  • 43
  • 8
  • 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
  • 30,962
  • 25
  • 85
  • 135
  • I'm new to sql. What is the N+1 problem, specifically? – user1934286 Jan 28 '23 at 17:16
  • PDO certainly looks interesting. Easier. Just started a look at the manual, where in the introduction [it contradicts your advice to never use persistent connections:](https://www.php.net/manual/en/pdo.connections.php) – user1934286 Jan 28 '23 at 17:51
  • *"Many web applications will benefit from making persistent connections to database servers. Persistent connections are not closed at the end of the script, but are cached and re-used when another script requests a connection using the same credentials. The persistent connection cache allows you to avoid the overhead of establishing a new connection every time a script needs to talk to a database, resulting in a faster web application."* – user1934286 Jan 28 '23 at 17:51
  • @user1934286 Comment section is not suitable for this question. It's not related to my answer and Google already points to existing SO questions about N+1 problem. – Dharman Jan 28 '23 at 18:42
  • Yeah, PHP manual will contradict common sense on many occasions. That's not to say persistent connections should never be used. It's just a very niche feature that 99.9999% of projects will never use or need. I will try to make the PHP manual clearer and edit that link. – Dharman Jan 28 '23 at 18:45
  • By the way I will delete these comments soon unless you read them and delete them yourself first. I don't think they add value to my answer. – Dharman Jan 28 '23 at 18:47