How to handle too many database connection in PHP without an increase to max connections?

mysql too many connections solution
mysql max connections recommended
mysql max connections per user
php persistent connection
error 1040 (hy000): too many connections
mysql persistent connections vs non-persistent
how many sql connections is too many
how many concurrent queries can mysql handle

There is a file called function.php and I am calling it into all my website's pages with require_once. This file contains more than hundred functions.

I get max_user_connections error from mysqli_connect() and right after that

mysqli_query() expects parameter 1 to be mysqli, boolean given

error shown when there are too many visitors or search engine bots are crawling fast.

The function file is like this:

<?php 
function db_connect(){
    $link=mysqli_connect("host","dbuser","dbpass","dbname");
    if (mysqli_connect_errno()) {
      echo "Failed to connect to MySQL: " . mysqli_connect_error();
    }
    mysqli_query($link,"set names 'utf8'");
}

db_connect();

function func_1(){
    $result=mysqli_query($link,"select * from ...");
    ...
}

function func_2(){
    $result=mysqli_query($link,"select * from ...");
    ...
}

.
.
.

function func_100(){
    $result=mysqli_query($link,"select * from ...");
    ...
}
?>

In the end I close the database connection. As I have checked max connection per user for my database is 50 and host provider will not change it (It's enough, they said).

So, generally how can I handle this problem ? (simple PHP or OOP)

You're obviously opening more than one connection to your MySQL server in the process of handling each web page request.

Right after the definition of your db_connect() function in your code file, you call that function. That probably results in it being called more often than necessary. Each time you call it it consumes scarce resources from the connection pool.

Instead, do some work to make sure that function is called at most one time by the code processing each page. It's better to manage MySQL connections as part of your processing logic than to rely on require_once to do it for you. Make sure to close the open connections when you're done using them.

When you're sure you have at most one MySQL connection open per page being processed, then you may need to reduce the number of php documents your web server can handle simultaneously. That can be done by tuning the apache web server parameters MaxClients and / or MaxRequestsPerChild. The other popular web server, nginx, has similar parameters. Reducing this capacity won't harm your users, because the web servers handle page requests from a queue.

MySQL Error: Too many connections, How do I avoid too many connections error in MySQL? Often, it has been seen that a sudden increase in website traffic and intermittent ‘Too many connections’ errors denote a flooding attack to the web server. If the connection limit is increased without noticing or preventing the attack, it will eventually crash your server, as a flooding attack is aimed at exhausting server resources with too many requests.

If the number of connections is too many for an invalid reason, then fix it. Take a look at when the connection is closed and how. Take a look at the persistency of the connection. If even making sure these are okay you have too many connections, then you might want to develop a multithreaded standalone application for your server in an environment which supports it and use maybe 10 threads of persistent connections there. This application would use a queue to know what needs to be asked from the database and where is the response to be sent back and whenever a worker thread finishes the process, get its result, send it to the requester and get on to the next item from the queue.

MySQL Error “Too many connections” and how to resolve it – The , clients that may be connected to the server has been reached. Before you increase the connections limit, you will want to ensure that the machine on which the database is housed can handle the additional workload. The maximum number of connections that can be supported depends on the following variables: The available RAM – The system will need to have enough RAM to handle the additional workload.

I'm going to be blunt. There's not a coding solution here because of this

As I have checked max connection per user for my database is 50 and host provider will not change it (It's enough, they said).

I suspect you're on some cheap/free hosting solution and they can keep it cheap/free because they throttle you. 50 is fine for, say, a personal Wordpress site with moderate traffic (say 1000 visits a day). But it's really easy to outstrip that. PHP is making more than 50 concurrent connections. That's a resource problem and you'll only solve it by increasing the number of connections available.

Since your current host won't do that, you need to move to one that can. Either that or accept that some of your visitors won't get the webpage they were expecting.

How to Check and Update max_connections value in MySQL, as per your operating system. By default you can find this at /etc/my. Log on to the datbase as root and use "SHOW PROCESSLIST" to see who's connected. Ideally hook this into your monitoring system to view how many connections there are over time and alert if there are too many. The maximum database connections can be configured in my.cnf, but watch out for running out of memory or address space.

Use a singleton Either for just $link or all function.php.

Error message: Too many connections, What does it mean when a website says too many connections? For example, if your visitor visits index.php, and it makes a database query to get some user details, that request might live for 250ms. You can limit how long those MySQL connections live even further by opening and closing them only when you are querying, instead of leaving it open for the duration of the script.

I would add a method getLink that you use in all query calls:

function getLink() {
    static $link;

    if(! $link) 
        $link = db_onnection() ;

    return $link;
} 

And then in your queries:

mysqli_query(getLink(), "SELECT id,...");

Also, this is a variation of the singleton pattern which is a anti-pattern!

Persistent Database Connections - Manual, attempts. If this is the case, your site and your business may have outgrown shared web hosting. If you are getting “too many connections” errors in MySQL you can change the max_connections setting to allow more connections, assuming you have enough RAM to handle the increased number. This post looks at how to update the max_connections setting in MySQL. The default setting for max_connections is 100.

Solve a MySQL/MariaDB "Too many connections" error, PHP is making more than 50 concurrent connections. That's a resource problem and you'll only solve it by increasing the number of  But our problem is that 95% of the times the no of connections to the database is below 30. And our server is all powerful to handle the same. But the remaining periods when our bloggers work at admin side, the max connections shoot to 312 aka our upper ceiling. Updating to a bigger mysql server would costs us around three times more,

How do I increase the MySQL connections for my server?, Examining the MySQL error "Too many connections" and some advice on running to get rid of the “Too many connections” error without restarting MySQL. MySQL permits one extra connection on top of the max_connections limit which is reserved for the database user having SUPER privilege in order  Also, when there are too many connections, it blocks until one can be established. If you can't use pooling, then do what you need to do to allow more connections: reduce the number of Apache processes, add memory to the DB server machine, increase the DB limit on 'processes' (see the "Configuring the Database For Testing" section in the

B.4.2.6 Too many connections, When a persistent connection is requested, PHP checks if there's already an If your database has a limit of 16 simultaneous connections, and in the course of a It may (and probably will) change the efficiency of the script, but not its behavior​! Even on my testing server, I was having problems with too many connections​  The permitted number of connections is controlled by the max_connections system variable. The default value is 151 to improve performance when MySQL is used with the Apache Web server. To support more connections, set max_connections to a larger value. mysqld actually permits max_connections + 1 client connections.

Comments
  • I don't know what's going wrong - I suspect the error is in some part of the code you're not showing us -, but your db_connect function is assigning a connection to a local variable, and then never passing it out. That's gonna burn you somewhere!
  • Sorry I forgot. I'm using " global $link " in the code where is needed. It's usually works fine but sometimes I get this error.
  • They can use something like if (!$link) for avoid creating multiple connections, but, if they are really using only require_once, that's not the problem. But that's a nice point of better coding, i'm adding to my response.
  • You're right, but it's not very cheap host (not for me!!). I just looking for best solution for the current situation.
  • FYI, you can have 10,000 connects and 9,999 disconnects within 5 minutes all happen within 50 db connections limit quite easily.
  • global is a terrible solution
  • Why a -1? Maybe global is not the better option, but, at least are a solution without redoing all the code. If you have a better option, please publish it. I can learn with it. But with a despective and non-constructive critic, I can't learn anymore or solve this problem.
  • Because you're propagating a terrible way to code. What if someone uses $link somewhere else? Your code suddenly stops working. You've also suggested something else bad: persistent connections. Speaking from experience, a persistent isn't released on termination, which is a bad thing for a website. You'll actually exacerbate the problem by doing that because PHP will still be creating new connections on top of the persistent ones.