Fully mastering the implementation of MySQL connection pool effect in PHP

Loops get the connection from the MySQL connection pool and do not need to create a new connection repeatedly. This article mainly introduces the PHP code to achieve the MySQL connection pool effect, the need for friends can refer to, hope to help everyone.

Reference configuration changes: You can refer to the following article

Prevents excessive traffic and fills 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::D b;    $username = Self::username;    $password = self::P assword;    $host = Self::host;    Persistent connection $presistent = Array (pdo::attr_persistent = true); for ($i =0; $i < self::P oolsize; $i + +) {$connection = new PDO ("Mysql:dbname= $db; host= $host", $username, $password      );      Sleep (3);    Array_push ($this->_pools, $connection); }}//Gets a database link resource from the database connection pool 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> no linked resource in the database connection pool, please retry later!</mark>" ); }}//Put the Used database link resource back into the database connection pool public function release ($conn) {echo ' release '. COUNT ($this->_pools).    "<br>"; if (count ($this->_pools) >= self::P oolsize) {throw new Errorexception ("<mark> database connection Pool 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 }  }}

In another file such as this call

<?php require_once ' db.php '; $sql = ' select * from user '; $dbhelper = new Dbhelper;for ($i =0; $i <; $i + +) {   $re s = $dbhelper->query ($sql);  Var_dump ($res). Php_eol;}

thinkphp How to connect to MySQL using connection pooling

Because of a small bug that caused the project to send a large number of connection requests to MySQL at one night, and the DNS of the MySQL server was having problems, the inverse solution timed out. Finally, the MySQL server was trailed down alive.

Finally, the bug was fixed and the method of adding MySQL connection pool was studied.

After two days of searching, I found that there are no related documents of connection pool in thinkphp document. So I researched the code.

First of all: PHP common MySQL Extension library has three kinds: MySQL, mysqli, Pdo_mysql.

* Mysqli is not supported for connection pooling.
* Pdo_mysql Support, however, the thinkphp PDO extension does not support MySQL, only support: ' MSSQL ', ' ORACLE ', ' Ibase ', ' OCI '. (see Pdo.class.php on line 59th)

* MySQL support, by 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 ' and ' = ' Custom ',    ' db_params ' = = Array (' persist ' = = true),  );}

If you think it's okay to configure this, it's a big mistake.

2 MySQL-my.cnf modify configuration:
[Mysqld]

Interactive_timeout = 60//The expiration time of the interactive connection (mysql-client).
Wait_timeout = 30//long connection expiration time. This must be changed! The default is 8 hours. If the request volume is large, the number of connections will soon be full.
max_connections = 100//maximum number of connections, which can be considered as the size of the connection pool

3 php.ini Modification:
[MYSQL]
Mysql.allow_persistent = On
Mysql.max_persistent = 99//To be less than the maximum number of connections for MySQL configuration
Mysql.max_links = 99

4 webserver If it is Apache, you need to enable keep-alive. Otherwise, long connections can no longer be reused once the request exits.
Webserver is the case of Nginx:
PM = dynamic//default starts some child processes for processing HTTP requests.
Pm.max_children//maximum number of child processes. This configuration is smaller than the MySQL max_connections.

5 If the discovery is still not available, check that the operating system keepalive is enabled.

Review:

Requires keep-alive and database long connections to be enabled at the same time, otherwise the long connection back to the white space of the MySQL connection resources, and can not be reused.
For Nginx + PHP-FPM, in fact, it is to keep the PHP-FPM child process with MySQL long connection. The front-end HTTP request is assigned to which PHP-FPM child process, which reuses its long connection to MySQL.