Detailed explanation of mysql connection pool implementation based on swoole

swoole frameworkThe column introduces the method of swoole to implement mysql connection pool

Recommendation (free): swoole framework

Preface

For the traditional nginx FPM mode PHP program, every time the worker requests FPM, it will connect to mysql once, and then the request will end. Disconnect. For applications with small concurrency, this will not be a problem, but for applications with high concurrency, the database will become a bottleneck if the connection is frequently established and the connection is destroyed. I believe many people have also encountered to many connections. Mysql reports an error.

Advantages of connection pool

The connection pool adopts the long connection mode, which will always maintain the connection with MySQL, and will be put back into the connection pool after use, thus It saves the consumption of establishing and disconnecting connections, greatly reduces the consumption of system IO, and improves the concurrency performance of the program to a certain extent. If the connection pool is free, a connection is allocated from the connection pool, otherwise, the request will be added to the waiting queue.

Implementation

We use swoole to implement mysql connection pool

Connection pool class

<?php

require_once "MysqlDB.php";class MysqlPool{
    private static $instance;
    private $pool;
    private $config;
    private $pool_get_timeout;

    /**
     * 获取mysql进程池单例
     * @param null $config
     * @return MysqlPool
     */
    public static function getInstance($config = null)
    {
        if (empty(self::$instance)) {
            if (empty($config)) {
                throw new RuntimeException("mysql config is empty");
            }
            self::$instance = new static($config);
        }
        return self::$instance;
    }

    public function __construct($config)
    {
        if (empty($this->pool)) {
            $this->config = $config;
            $this->pool = new \Swoole\Coroutine\Channel($config['pool_size']);
            for ($i = 0; $i < $config[&#39;pool_size&#39;]; $i++) {
                \go(function() use ($config) {
                    $mysql = new MysqlDB();
                    $res = $mysql->connect($config['mysql']);
                    if ($res === false) {
                        throw new RuntimeException("Failed to connect mysql server");
                    } else {
                        $this->pool->push($mysql);
                    }
                });
            }
        }
    }

    public function get()
    {
        if ($this->pool->length() > 0) {
            $mysql = $this->pool->pop($this->config['pool_get_timeout']);
            if (false === $mysql) {
                throw new RuntimeException("Pop mysql timeout");
            }
            return $mysql;
        } else {
            throw new RuntimeException("Pool length <= 0");
        }
    }

    public function recycle(MysqlDB $mysql){
        $this->pool->push($mysql);
    }

    /**
     * 获取连接池长度
     * @return mixed
     */
    public function getPoolSize(){
        return $this->pool->length();
    }}

Database DB class

<?phpclass MysqlDB{
    private $connection;

    public function connect($config)
    {
        $connection = new \Swoole\Coroutine\MySQL();
        $res = $connection->connect($config);
        if ($res === false) {
            throw new RuntimeException($connection->connect_error, $connection->errno);
        } else {
            $this->connection = $connection;
        }
        return $res;
    }


    public function query($sql){
        $result = $this->connection->query($sql);
        return $result;
    }}

Create a connection pool in the HTTP coroutine server

<?php
require_once "MysqlPool.php";\Co\run(function () {
    $server = new \Co\Http\Server("0.0.0.0", 9501, false);
    $pool = MysqlPool::getInstance([
        &#39;pool_size&#39;=>5,
        'pool_get_timeout'=>1,
        'timeout'=>1,
        'charset'=>'utf8',
        'strict_type'=>false,
        'fetch_mode'=>true,
        'mysql'=>[
            'host'=>'127.0.0.1',
            'port'=>'3306',
            'user'=>'homestead',
            'password'=>'secret',
            'database'=>'blog',
        ]
    ]);
    $server->handle('/', function ($request, $response) use ($pool){
        $mysql = $pool->get();
        $res = $mysql->query("select id,phone,username from user limit 1");
        var_dump($res);
        $pool->recycle($mysql);
        $response->end("<h1>Test</h1>");
    });
    $server->handle('/test', function ($request, $response) {
        $response->end("<h1>Test</h1>");
    });
    $server->handle('/stop', function ($request, $response) use ($server) {
        $response->end("<h1>Stop</h1>");
        $server->shutdown();
    });
    $server->start();});

Source code address swoole-mysql-pool

Related free learning recommendations: mysql video tutorial

The above is the detailed content of Detailed explanation of mysql connection pool implementation based on swoole. For more information, please follow other related articles on the PHP Chinese website!