php Implementation of mysql Connection Pool Effect Implementation Code

Loop to get connections from the mysql connection pool without repeatedly creating new connections.

Reference configuration modification: You can refer to the following article

Prevent too many visits and fill up the number of connections

<?php

/**
 * @author xuleyan
 * @title mysql Class 
 */

class DbHelper{
  // Connection pool 
  private $_pools = [];

  // Connection pool size 
  const POOLSIZE = 5;

  const USERNAME = "root";
  const PASSWORD = "root";
  const HOST = "127.0.0.1";
  const DB = "test";

  public function __construct()  
  {
    $db = self::DB;
    $username = self::USERNAME;
    $password = self::PASSWORD;
    $host = self::HOST;

    // Persistent connection 
    $presistent = array(PDO::ATTR_PERSISTENT => true);

    for ($i=0; $i < self::POOLSIZE; $i++) { 
      $connection = new PDO("mysql:dbname=$db;host=$host", $username, $password);
      // sleep(3);
      array_push($this->_pools, $connection);
    }
  }

  // Obtain from the database connection pool 1 Database linked resources 
  public function getConnection()
  {
    echo 'get' . count($this->_pools) . "<br>";
    if (count($this->_pools) > 0) {
      $one = array_pop($this->_pools);
      echo 'getAfter' . count($this->_pools) . "<br>";
      return $one;
    } else {
      throw new ErrorException ( "<mark> There are no linked resources in the database connection pool. Please try again later !</mark>" );
    }
  }

  // Put used database link resources back into the database connection pool 
  public function release($conn)
  {
    echo 'release' . count($this->_pools) . "<br>";
    if (count($this->_pools) >= self::POOLSIZE) {
      throw new ErrorException ( "<mark> Database connection pool is full !</mark>" );
    } else {
      array_push($this->_pools, $conn);
      // $conn = null;
      echo 'releaseAfter' . count($this->_pools) . "<br>";
    }
  }

  public function query($sql)
  {
    try {
      $conn = $this->getConnection();
      $res = $conn->query($sql);
      $this->release($conn);
      return $res;
    } catch (ErrorException $e) {
      print 'error:' . $e->getMessage();
      die;
    }
  }

  public function queryAll($sql)
  {
    try {
      $conn = $this->getConnection();
      $sth = $conn->prepare($sql);
      $sth->execute();
      $result = $sth->fetchAll();
      return $result;
    } catch (PDOException $e) {
      print 'error:' . $e->getMessage();
      die;
    }
  }
}

This is called in another file

<?php 

require_once 'db.php';
$sql = 'select * from user';

$dbhelper = new DbHelper;
for ($i=0; $i < 10; $i++) { 
  $res = $dbhelper->query($sql);
  // var_dump($res) . PHP_EOL;
}

How to Use Connection Pool for ThinkPHP Connection to MySQL

Because of a small bug, the project sent a large number of connection requests to mysql one night, and there was a problem with dns of mysql server, which caused the inverse solution to time out. Finally, the mysql server was dragged alive.

Finally, bug was repaired, and the method of increasing mysql connection pool under 1 was studied.

After searching for 1 time in these two days, it was found that there was no connection pool related document in ThinkPHP document. So I studied the code myself.

First, there are three kinds of mysql extension libraries commonly used in PHP: mysql, mysqli, pdo_mysql.

* mysqli does not support connection pooling.
* pdo_mysql is supported, however, the pdo extension of thinkPHP does not support mysql, only: 'MSSQL', 'ORACLE', 'Ibase', 'OCI'. (See line 59 of Pdo. class. php)

* mysql support, through the method: mysql_pconnect. (Specific parameters can be seen in the official php document)

1 The way to enable ThinkPHP for long connections is:

class BaseMysql extends Model {
  protected $connection = array(
    'db_type' => 'mysql',
    'db_user' => '***',
    'db_pwd' => '*******',
    'db_host' => '*******',
    'db_port' => '3306',
    'db_name' => 'custom',
    'db_params' => array('persist' => true),
  );
}

If you think that everything will be fine if you configure this, you are very wrong.

2 mysql - > my. cnf Modify configuration:
[mysqld]

interactive_timeout = 60//The expiration time of the interactive connection (mysql-client).
wait_timeout = 30//Expiration time for long connections. This 1 must be changed! The default is 8 hours. If the number of requests is large, the number of connections will soon be full.
max_connections = 100//Maximum number of connections, which can be considered the size of the connection pool

3 php. ini amendment:
[MySql]
mysql.allow_persistent = On
mysql. max_persistent = 99//Be less than the maximum number of connections configured for mysql
mysql.max_links = 99

4 webserver If it is apache, keep-alive needs to be enabled. Otherwise, 1 once the request exits, the long connection will no longer be reused.
webserver is the case of nginx:
pm = dynamic//By default, 1 child process is started to process http requests.
pm. max_children//Maximum number of child processes. This configuration is smaller than max_connections for mysql.

5 If it is found that it still cannot be used, please check whether keepalive of the operating system is enabled.

Summary:

keep-alive and database long connections are required to be enabled at the same time, otherwise long connections will occupy the connection number resources of mysql in vain and cannot be reused.
In the case of nginx + php-fpm, the long connection between php-fpm child process and mysql is actually maintained. The php-fpm child process to which the front-end http request is assigned reuses its own long connection to mysql.

The above is the research result of 1 whole day. Please point out the incompleteness. Thank you first!