PHP Mysqli database connection pooling to avoid maximum user count

Question

I surfed on Internet for past two days to create the DB pooling by PHP, but not yet achieved. I'm using PHP and MySQLi. We bought the mysqli db with 15 Maximum user connection. I want to pool the db connection to avoid the new connection. I used persistent as well as mysqli_connect. but I don't feel much different both are creating the new connection since other user already logged in. I was trying this function to get DB connection.

 <?php
function getConnection(){
    if ($connect){
        return $connect;
    }else{
        $connect = new mysqli('p:xxxx','yyy','zzz','aaaa');
        return $connect;
        if(mysqli_connect_errno($connect))
        {
            echo "Server Busy";
        }
    }
}
 ?>

But above function only returns the else part. Please suggest me how to handle this. Thanks in advance. For now I'm killing the process which are in sleep mode to reduce the probability of increasing DB connection.

Your Common Sense · Accepted Answer · 2019-11-04 12:03:48Z

2

+50

There is no connection pooling in PHP.

The persistent connection you are trying to use (that "p:" bit) is the most sure way to hit the maximum connection number, so get rid of it immediately.

15 concurrent connections is, actually, A LOT. Simply optimize your queries, so a typical connection would last 0.1 second, which would mean 150 concurrently executed PHP scripts which is like 1500 users on-line.

Also, you need to add

static $connect;

as the first line in your function, or is will create the new connection every time it is called, which is the actual reason why you are getting this error.

<?php
function getConnection(){
    static $connect;

    if (!$connect){
        mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
        $connect = new mysqli('xxxx','yyy','zzz','aaaa');
        $connect->set_charset('utf8mb4');
    }
    return $connect;
}

Better yet, get rid of this function at all, have a separate file with the mysql connection code, and then use the $connect variable all around your code. See my article about mysqli connect for the details.

edited Nov 4, 2019 at 12:03

answered Nov 4, 2019 at 11:58

Your Common Sense

158k42 gold badges226 silver badges373 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Dharman Over a year ago

Could you explain a little more how you got to the 1500 number?

Your Common Sense Over a year ago

Thet's just quite a rough estimation. Assuming every user requests the site once every 10 seconds, it will make 150 requests per second.

Aroon Over a year ago

Hi My Common Sense, sry for the late reply. so by following this method do i need to close the connection? once the query got executed

Your Common Sense Over a year ago

@Aroon never. Just never close the connection. Php will close it for you all right.

Aroon Over a year ago

Thanks for the quick reply. I will try this and let you know.

|

Dharman · Accepted Answer · 2019-08-09 12:55:19Z

0

This is not how it works. See here. MySQLi will select one of available connections when you execute new mysqli('p:... The connection is persistent, not the PHP object.

function getConnection(){
    mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
    return (new mysqli('p:xxxx','yyy','zzz','aaaa'));
}

answered Aug 9, 2019 at 12:55

Dharman♦

33.9k27 gold badges106 silver badges157 bronze badges

8 Comments

Aroon Over a year ago

Thanks for the heads up... I will try that and let you know whether it is solved my problem or not

Aroon Over a year ago

I have a question. above is the all that I need to put in that file?

Dharman Over a year ago

Yes. A side comment: I do not think you need persistent connections at all. You should revalidate whether you really need to do it.

Aroon Over a year ago

I tried the above function. but for each user it is taking one connection

Dharman Over a year ago

What do you mean each user? Do you mean for each http request or are you using multiple DB users?

|

Collectives™ on Stack Overflow

PHP Mysqli database connection pooling to avoid maximum user count

2 Answers 2

6 Comments

8 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

8 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related