Using swoole to create MySQL connection pool

Time:2020-1-14

1、 What is MySQL connection pool

Scenario: there are 1000 concurrent connections per second, but this MySQL can only handle 400 connections at the same time, and MySQL will go down.

Solution: connection pool. This connection pool establishes 200 connections with MySQL. These 1000 concurrent connections share 200 connections in the connection pool in sequence.
This connection pool can bring additional performance improvement, because the process of establishing a connection with MySQL consumes a lot, and you only need to connect mysql once to use the connection pool.

Connection pool definition: never disconnect, our program is a memory resident program. Connection pooling is a program start
Establish enough database connections in real time, and make these connections into a connection pool. The program dynamically applies, uses and releases the connections in the pool.

2、 Small case

Look up the latest 3 members registered in the user table database?

(1) Small prompt show processlist – view the number of connections in MySQL

(2) Create 10 MySQL connection sample codes

/**
		* Created by PhpStorm.
		* User: Administrator
		* Date: 2018/11/20
		* Time: 14:12
		*/
		//Write MySQL connection pool. This class can only be instantiated once (single example)
		class MysqlConnectionPool
		{
		Private static $instance; // singleton object
		Private $connection_num = 10; // number of connections
		private $connection_obj = [];
		//Construct method to connect mysql and create 20mysql connection
		private function __construct()
		{        
		for($i=0;$iconnection_num;$i++){
			$dsn = "mysql:host=127.0.0.1;dbnane=swoole";
			$this->connection_obj[] =  new Pdo($dsn,'root','rootmysql123');
			}
			}
			private function __clone()
			{
			// TODO: Implement __clone() method.
			}    
			public static function getInstance()
			{
			if(is_null(self::$instance))
			{
			self::$instance = new self();        
			}
			}
			}
			MysqlConnectionPool::getInstance();
			//Create the HTTP server object of swool
			$serv = new swoole_http_server('0.0.0.0',8000);
			//When the browser links to the HTTP server, send HelloWorld to the browser
			$serv->on('request', function($request,$response){
			//$request contains all the information of the request, such as parameters
			//$response contains all the information returned to the browser, such as HelloWorld
			//(2.3) send HelloWorld to browser
			$response->end("hello world");
			});
			//Start HTTP server
			$serv->start();

                                                                                                                                                                                                                                        (3) effect

 

 

(4) Improve MySQL connection pool

/**
* Created by PhpStorm.
* User: Administrator
* Date: 2018/11/20
* Time: 14:12
*/
//Write MySQL connection pool. This class can only be instantiated once (single example)
class MysqlConnectionPool
{
Private static $instance; / / singleton object
Private $connection_num = 20; / / number of connections
private $connection_obj = [];
Private $avil_connection_num = 20; / / available connection

//Construct method to connect mysql and create 20mysql connection
private function __construct()
{
for($i=0;$iconnection_num;$i++){
$dsn = “mysql:host=127.0.0.1;dbname=swoole”;
$this->connection_obj[] = new Pdo($dsn,’root’,’rootmysql123′);
}
}
private function __clone()
{
// TODO: Implement __clone() method.
}
public static function getInstance()
{
if(is_null(self::$instance)){
self::$instance = new self();
}
return self::$instance;
}

//Execute SQL operation
public function query($sql)
{
if($this->avil_connection_num==0){
Throw new exception (“there is no connection available at this time, please wait”);
}
//Execute SQL statement
$pdo = array_pop($this->connection_obj);
//Number of available connections minus 1
$this->avil_connection_num –;
//Use the MySQL connection taken from the connection pool to execute the query, and take the data into the associated array
$rows = $pdo->query($sql)->fetchAll(PDO::FETCH_ASSOC);
//Put MySQL connection back to connection pool, number of available connections + 1
array_push($this->connection_obj,$pdo);
$this->avil_connection_num ++;
return $rows;
}
}
//Create the HTTP server object of swool
$serv = new swoole_http_server(‘0.0.0.0’,8000);
//When the browser links to the HTTP server, send HelloWorld to the browser
$serv->on(‘request’, function($request,$response){
//$request contains all the information of the request, such as parameters
//$response contains all the information returned to the browser, such as HelloWorld
//Send HelloWorld to browser
$stop = false;
while (!$stop){
try{
$sql = “SELECT * FROM user ORDER BY id DESC LIMIT 5”;
$rows = MysqlConnectionPool::getInstance()->query($sql);
$response->end(json_encode($rows));
$stop = true;
}catch (Exception $e){
usleep(100000);
}
});

//Start HTTP server$$serv>start();

   

 

 

                                                                                                                                

Recommended Today

The basic syntax and function of triggers in SQLSEVER

What is a trigger? Triggers are stored procedures that are executed automatically when a table is inserted, updated, or deleted. A special stored procedure that is automatically executed when a trigger inserts, updates, or deletes a table. Triggers are generally used for more complex check constraints. The difference between a trigger and a normal stored […]