Php implements the mysql connection pool effect implementation code, and php implements the mysql code

Php implements the mysql connection pool effect implementation code, and php implements the mysql code

Obtain connections from the mysql connection pool cyclically without creating new connections.

For more information, see the following document.

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: 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 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 exists in the Database Connection Pool. Please try again later! </Mark> ") ;}}// puts the used database link resources back to the database connection pool public function release ($ conn) {echo 'release '. count ($ this-> _ pools ). "<br>"; if (count ($ this-> _ pools)> = self: POOLSIZE) {throw new ErrorException ("<mark> the 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 (); $ something = $ conn-> prepare ($ SQL); $ something-> execute (); $ result = $ something-> 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 ThinkPHP to connect to MySQL connection pool

A small bug caused the project to send a large number of connection requests to mysql at a certain night, And the dns of the mysql server encountered a problem, resulting in reverse resolution timeout. Finally, the active mysql server is dragged down.

Finally, the bug is fixed, and the method of adding the mysql connection pool is studied.

After searching these two days, we found that the ThinkPHP document does not contain any connection pool documentation. So I studied the code myself.

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

* Mysqli does not support connection pools.
* Pdo_mysql support. However, the pdo extension of thinkPHP does not support mysql. It only supports: 'mssql', 'oracle ', 'ibase', and 'ocs '. (See Pdo. class. php 59th rows)

* Mysql support. Use mysql_pconnect. (for specific parameters, see the official php documentation)

1. Enable persistent connection in ThinkPHP:

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 configuration is all right, 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 // The expiration time of the persistent connection. This must be changed! The default value 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 // The maximum number of connections to be smaller than the mysql Configuration
Mysql. max_links = 99

4. If the webserver is apache, enable keep-alive. Otherwise, long connections cannot be reused once the request exits.
Webserver is nginx:
Pm = dynamic // some sub-processes are started by default to process http requests.
Pm. max_children // maximum number of child processes. This configuration is smaller than mysql's max_connections.

5. If it is still unavailable, check whether the keepalive of the operating system is enabled.

Summary:

You must enable both keep-alive and database persistent connections. Otherwise, the persistent connections will occupy the resources of mysql connections and cannot be reused.
For nginx + php-fpm, the persistent connection between the php-fpm sub-process and mysql is actually maintained. The php-fpm sub-process to which the front-end http request is allocated, the sub-process will reuse its persistent connection with mysql.

The above is the result of a whole day of research. If it is incomplete, please point out that I would like to thank you!