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

user_5458954 0
Viewed 3461

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)

mysql php
7 Answers

Here is the solution that I found when dealing with occasional high traffic. The key is to detect that the MySQL client has this error and wait to retry the connection.

It's not going to be as effective as reducing the number of connections required or increasing the connections, but in a pinch it should prevent the errors, assuming that connections are actually eventually being closed down. It will slow down the visitor experience (load times will suffer), but I expect it's better for users to wait a bit longer than get an error.

$sql = @new mysqli($db_host, $db_user, $db_pass, $db_name);
$iSqlAttempt = 1;
//Detect the maximum connection error.
while ($sql->connect_errno == 1040 && $iSqlAttempt < 10) {
  //Sleep 10ms then retry. The time doubles after each attempt.
  usleep(pow(2, $iSqlAttempt)*10000);
  $sql = @new mysqli($db_host, $db_user, $db_pass, $db_name);
  $iSqlAttempt ++;
}
if ($sql->connect_error) {
  die('Connect Error ('.$sql->connect_errno.') '.$sql->connect_error);
}

If the database still can't be reached after 10 attempts/10 seconds than it will fall back to reporting the error, but hopefully your situation is not as bad as that.

Hope it helps! Let me know if there are any questions.

user_19026160
Related