[Discussion] MySQL Pooling in PHP

[seanrom]

If I said that MySQL Pooling would be possible and efficient in a mutli-threaded CLI-PHP application.

Would someone disagree, and why? Any thoughts?

<?php
    // Application

    // Exceptions
    class MySQLWorkerException extends Exception {}
    class MySQLConnectionException extends Exception {}
    class MySQLQueryException extends Exception {}

    // Worker
    class SQLWorker extends Worker {
        // Class Fields
        protected   $ready;
        protected   $name;

        // MySQLi Object
        protected   $link;

        // MySQL Credentials
        private
                    $host,
                    $username,
                    $password,
                    $database,
                    $port;

        // Constructor
        function __construct($host, $username, $password, $database, $port = 3306) {
            // Set Credentials
            $this->host     = $host;
            $this->username = $username;
            $this->password = $password;
            $this->database = $database;
            $this->port     = $port;
        }

        // Methods
        public function run() {
            // Create MySQL link
            $this->link = new mysqli(
                $this->host,
                $this->username,
                $this->password,
                $this->database,
                $this->port
            );

            $this->link->query("SELECT 1");
            //var_dump($this->link);
            exit;

            // Check for connection error
            if($this->link->connect_errno) {
                throw new MySQLConnectionException('(' . $this->link->connect_errno . '): ' . $this->link->connect_error, E_WARNING);
            }

            //$this->name = sprintf("%s (%lu)", __CLASS__, $this->getThreadId());
            $this->isReady(true);
        }

        public function getConnection() {
            return $this->link;
        }

        protected function isReady($flag = null) { 
            if(is_null($flag)) {
                return ($this->ready);
            } else {
                $this->ready = $flag;
            }
        }
    }

    // MySQL Method Classes
    // STUB: class SQLQuery extends Stackable {}
    // STUB: class SQLResult extends Stackable {}
    // STUB: class SQLPrepare extends Stackable {}

    class SQLQuery extends Stackable {
        // Class Fields
        protected $complete = false;

        // SQL Query
        private $query;
        private $resultmode;

        // SQL Result
        private $result;

        // Constructor
        function __construct($query, $resultmode = MYSQLI_STORE_RESULT) {
            $this->query = $query;
            $this->resultmode = $resultmode;
        }

        // Methods
        public function run() {
            if($this->worker->isReady()) {
                if(!$this->result = mysqli_query($this->worker->link, $this->query, $this->resultmode)) {
                    throw new MySQLQueryException('(' . $this->worker->link->errno . '): ' . $this->worker->link->error, E_WARNING);
                }
            } else {
                throw new MySQLWorkerException('Worker is not ready', E_ERROR);
            }

            $this->complete = true;
            $this->notify();
        }

        public function isComplete() {
            return ($this->complete);
        }

        public function GetResults() {
            return $this->result;
        }

        public function ReadResults() {
            $response = null;
            while($row = $this->result->fetch_assoc()) {
                $response[] = $row;
            }

            if($translateRows) {
                if(count($response) == 1) {
                    $response = end($response);

                    if(count($response) == 1) {
                        $response = end($response);
                    }
                }
            }

            $this->result->close();
            return $response;
        }
    }


    // Program
    $config = [
        'host'      => '127.0.0.1',
        'username'  => 'root',
        'password'  => '',
        'database'  => 'testdatabase',
        'port'      => 3306
    ];

    // MySQL Worker
    $worker = new SQLWorker($config['host'], $config['username'], $config['password'], $config['database'], $config['port']);
    $worker->start();

    // Create Query
    $query = new SQLQuery("SELECT username FROM users WHERE id = 1 LIMIT 1");

    // Give the query to the worker
    // $worker->stack($query);

    /* In reality we would have done a lot of poop here while the query executes. */

    // If the query isnt complete, wait up.
    $query->wait();

    // Holy poop, lets var_dump this bad boy
    // var_dump($query);

    // Shutdown the Worker Thread. We don't want any memleaks!
    $worker->shutdown();

 

[TheAJ]

Check this out:

You must be registered to see links

In the host, add 'p:' infront of the address, and it should use a persistent connection.

Like in any situation where there's concurrent requests, I personally feel a pool of connections are better, rather than opening and closing a connection for each request.