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:

1. use mysql_close every time after query get fired: Drawback: need to connect every time and hence time-consuming

2. 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 )

3. 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

4. mysql_pconnect is not solving my case.

Please suggest

Answer 1

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');
    . . . 

    ?>