Swoole4: How to create a Mysql connection pool

What is mysql connection pool

Scenario: 1000 concurrent connections per second, but this mysql can only handle 400 connections at a time, mysql will break down.

Solution: connection pool, this connection pool establishes 200 connections to mysql, and these 1000 concurrent connections share the 200 connections in the pool sequentially.

This connection pool provides an additional performance boost because the process of establishing a connection with mysql is expensive and requires only one connection to mysql.

Connection pool definition: never disconnect, requires that our program is a resident memory program. Database Connection pooling is a program enabler

Dynamic establish enough database connections, and these connections constitute a connection pool, by the program to dynamically apply for the pool of connections, use, release.

Two. Small cases

Find the latest 3 members registered in the user table database?

(1) Tips

Show processList #mysql

(2) create 10 mysql connection sample code

      
/** * Created by PhpStorm. * User: Luke * Date: 2019/10/30 * Time: 14:12 */
// Write a mysql connection pool. This class can only be instantiated once (singleton)
class MysqlConnectionPool
{
    private static $instance;// Singleton object
    private $connection_num = 10;// Number of connections
    private $connection_obj = [];
 
    // create a 20mysql connection
    private function __construct()
    {
        for($i=0;$i<$this->connection_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(a); } } } MysqlConnectionPool::getInstance();// Create an HTTP server object for Swool
$serv = new swoole_http_server('0.0.0.0'.8000);
// When the browser links to the HTTP server, it sends helloWorld to the browser
$serv->on('request'.function($request.$response){
    //$request contains all information about the request, such as parameters
    //$response contains all the information returned to the browser, such as helloWorld
 
    //(2.3) Send helloWorld to the browser
    $response->end("hello world");
});
// Start HTTP server
$serv->start();
Copy the code

(3) Effect

(4) Improve mysql connection pool

      
/** * Created by PhpStorm. * User: Luke * Date: 2019/10/30 * Time: 14:12 */
// Write a mysql connection pool. This class can only be instantiated once (singleton)
class MysqlConnectionPool
{
    private static $instance;// Singleton object
    private $connection_num = 20;// Number of connections
    private $connection_obj = [];
    private $avil_connection_num = 20;// The connection is available
 
    // create a 20mysql connection
    private function __construct()
    {
        for($i=0;$i<$this->connection_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(a); }return self: :$instance;
    }
 
    // Perform SQL operations
    public function query($sql)
    {
        if($this->avil_connection_num==0) {throw new Exception("No connection available at the moment, please wait.");
        }
        // Execute the SQL statement
        $pdo = array_pop($this->connection_obj);
        // The number of available connections is reduced by 1
        $this->avil_connection_num --;
        // Perform the query using the mysql connection fetched from the connection pool and fetch the data into an associative array
        $rows = $pdo->query($sql)->fetchAll(PDO::FETCH_ASSOC);
        // Put the mysql connection back into the connection pool
        array_push($this->connection_obj,$pdo);
        $this->avil_connection_num ++;
        return $rows; }}// Create an HTTP server object for Swool
$serv = new swoole_http_server('0.0.0.0'.8000);
// When the browser links to the HTTP server, it sends helloWorld to the browser
$serv->on('request'.function($request.$response){
    //$request contains all information about the request, such as parameters
    //$response contains all the information returned to the browser, such as helloWorld
    // Send helloWorld to the 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();
Copy the code

The above content hopes to help you, more free PHP factory PDF, PHP advanced architecture video materials, PHP wonderful good article can be wechat search concerns: PHP open source community

2021 Jinsanyin four big factory interview real questions collection, must see!

Four years of PHP technical articles collation collection – PHP framework

A collection of four years’ worth of PHP technical articles – Microservices Architecture

Distributed Architecture is a four-year collection of PHP technical articles

Four years of PHP technical essays – High Concurrency scenarios

Four years of elite PHP technical article collation collection – database