Php - Connection Pooling To Mysql

Question

I want to implement connection pooling in Php in a similar way that works in java.

Why I need this :

Let's consider a flow

Step1: Connection To Db --- Resource Id #12
Step2:  some computation... time taking .3 seconds
Step3: Query on Solr .... timing taking 2 seconds
Step4: Connection To Db --- Resource Id #12 (i am using same resource id) 
Step5: Exit

Though in step4 I am using the same DB resource as of step1. However, the connection will go in the sleep state for both step2 and step3 and therefore can't be used by any other PHP process (other clients) until exit.

Solution:

use mysql_close every time after query get fired: Drawback: need to connect every time and hence time-consuming
Create a java service to handle queries (possible but too time-consuming and I am looking for other solution where I need to migrate queries )
Need to explore SQL relay like the third party but I am not sure will that be a success and not many good companies have used it
mysql_pconnect is not solving my case.

Please suggest

Have you checked out multiquery? https://www.w3schools.com/php/func_mysqli_multi_query.asp — Will, Aug 28 '17 at 08:54
@Salketer how max connection can help me reusing the sleep connection. increasing max connection is worst opotion — chicharito, Aug 28 '17 at 09:02
@Will read the question I want the sleep connection to be reused by another client. — chicharito, Aug 28 '17 at 09:03
Well, a PHP script cannot reuse connection of another PHP script... It simply won't happen. The only reason you'd want to create the least amount of connections is because you are going over your limit, hence why I suggested that you increase it. — Salketer, Aug 28 '17 at 09:06
@Salketer I have heard sqlRelay as a solution, so before exploring more, I am looking for alternatives. — chicharito, Aug 28 '17 at 09:09
Alternatively, you could do everything with one connection per child process: http://php.net/manual/en/features.persistent-connections.php However, I advice against this. It is only to be used under certain extreme conditions. — KIKO Software, Aug 28 '17 at 09:09
I'd love to hear why you need to do that, I can not really see any real use case for it. There is nothing wrong with that... If you asked me, step 2 and 3 could certainly be done outside of your main PHP script, but even then... I've used long living connections on PHP scripts lasting for up to 6 hours without having any problems. So, what is the problem you are facing? — Salketer, Aug 28 '17 at 09:23

score 0 · Answer 1 · edited Oct 18 '18 at 10:37

One way that you can apply scalability techniques to this pool model is to allow on the fly changes to your pool distribution. If you have a particular permalink that is extremely popular for some reason, you could move slaves from the primary pool to the comments pool to help it out. By isolating load, you’ve managed to give yourself more flexibility. You can add slaves to any pool, move them between pools, and in the end dial-in the performance that you need at your current traffic level.

There’s one additional benefit that you get from MySQL database pooling, which is a much higher hit rate on your query cache. MySQL (and most database systems) have a query cache built into them. This cache holds the results of recent queries. If the same query is re-executed, the cached results can be returned quickly.

If you have 20 database slaves and execute the same query twice in a row, you only have a 1/20th chance of hitting the same slave and getting a cached result. But by sending certain classes of queries to a smaller set of servers you can drastically increase the chance of a cache hit and get greater performance.

You will need to handle database pooling within your code - a natural extension of the basic load balancing code in Part 1. Let’s look at how we might extend that code to handle arbitrary database pools:

<?php        
    class DB {
        // Configuration information:
        private static $user = 'testUser';
        private static $pass = 'testPass';
        private static $config = array(
            'write' =>
                array('mysql:dbname=MyDB;host=10.1.2.3'),
            'primary' =>
                array('mysql:dbname=MyDB;host=10.1.2.7',
                      'mysql:dbname=MyDB;host=10.1.2.8',
                      'mysql:dbname=MyDB;host=10.1.2.9'),
            'batch' =>
                array('mysql:dbname=MyDB;host=10.1.2.12'),
            'comments' =>
                array('mysql:dbname=MyDB;host=10.1.2.27',
                      'mysql:dbname=MyDB;host=10.1.2.28'),
            );

        // Static method to return a database connection to a certain pool
        public static function getConnection($pool) {
            // Make a copy of the server array, to modify as we go:
            $servers = self::$config[$pool];
            $connection = false;

            // Keep trying to make a connection:
            while (!$connection && count($servers)) {
                $key = array_rand($servers);
                try {
                    $connection = new PDO($servers[$key], 
                        self::$user, self::$pass);
                } catch (PDOException $e) {}

                if (!$connection) {
                    // Couldn’t connect to this server, so remove it:
                    unset($servers[$key]);
                }
            }

            // If we never connected to any database, throw an exception:
            if (!$connection) {
                throw new Exception("Failed Pool: {$pool}");
            }

            return $connection;
        }
    }
    // Do something Comment related
    $comments = DB::getConnection('comments');
    . . . 

    ?>

Php - Connection Pooling To Mysql

1 Answers1