【swoole.2.07】协程化mysql的连接池

前言

在上一篇中介绍了在协程中并发处理耗时操作,如第三方api接口,mysql等。在最后提出了一个问题,因为协程mysql虽然是通过io阻塞时挂起执行权限减少了php的时间成本,但是碰到mysql响应时间较慢,并发较多的情况下会导致mysql连接数过大,这时候是将后台的负担全部压到了数据库上。我们研究swoole的目的是利用协程进行性能调优,更好的利用服务器的资源而不是单纯的将后台的压力转移。所以在使用协程化mysql的时候需要引入连接池来处理。先来看看错误示范,也就是将所有压力都转移到mysql中。

错误情景

情景描述

一位新入手swoole的phper在发现协程处理这么快后,毅然决然决定将所有数据库操作全部协程化,这里是他的代码

服务器
require_once __DIR__ . '/Reactor.php';

$mysqlConfig = require_once __DIR__ . '/mysql.config.php';

$reactor = new Reactor($mysqlConfig);

$server = new Swoole\Http\Server('127.0.0.1', 9001);

$server->set(['worker' => 1]);

$server->on('request', [$reactor, 'request']);

$server->start();
服务器行为驱动器reactor
class Reactor
{
    protected $mysqlConfig = null;

    public function __construct($mysqlConfig)
    {
        $this->mysqlConfig = $mysqlConfig;
    }

    public function request(\Swoole\Http\Request $request, \Swoole\Http\Response $response)
    {
        $sql = 'select sleep(5)';

        $recv = [];
        for ($i = 0; $i < 10; $i++) {
            $recv[] = $this->query($sql);
        }

        foreach ($recv as $v) {
            $v->recv();
        }

        $response->end('test');
    }

    public function query($sql): \Swoole\Coroutine\MySQL
    {
        $mysql = new \Swoole\Coroutine\MySQL();
        $mysql->connect($this->mysqlConfig);
        $mysql->setDefer(true);
        $mysql->query($sql);
        return $mysql;
    }
}

代码分析

这段代码非常简单,在服务器中开启了一个Swoole\Http\Server。至于所有请求这里模拟了一次请求会起若干个协程化的mysql去处理sql,并且做了延迟收包处理。请求一次这些sql需要执行5x10=50秒,但是因为是放在协程mysql客户端中,并且在io阻塞时挂起了程序做了延迟收包的处理,实际只需要5秒响应。先来请求一次看看。

[root@iZbp1acp86oa3ixxw4n1dpZ ~]# ab -n1 127.0.0.1:9001/
......完整输出请看附录
Time per request:       5003.034 [ms] (mean)
Time per request:       5003.034 [ms] (mean, across all concurrent requests)
......完整输出请看附录
Connection Times (ms)
              min  mean[+/-sd] median   max
Connect:        0    0   0.0      0       0
Processing:  5003 5003   0.0   5003    5003
Waiting:     5003 5003   0.0   5003    5003
Total:       5003 5003   0.0   5003    5003

耗时5秒,看起来是没问题的,那么在服务器完全负载的情况下mysql到底是怎么样的呢?(这里响应过程的ab测试就不展示了)

空闲情况下的mysql

mysql> show processlist;
+-------+------+-----------------+------+---------+------+-------+------------------+
| Id    | User | Host            | db   | Command | Time | State | Info             |
+-------+------+-----------------+------+---------+------+-------+------------------+
| 49701 | root | localhost:44364 | NULL | Query   |    0 | init  | show processlist |
+-------+------+-----------------+------+---------+------+-------+------------------+
1 row in set (0.00 sec)

在worker满负荷情况下的mysql

[root@iZbp1acp86oa3ixxw4n1dpZ ~]# ab -n100 -c10 127.0.0.1:9001/
mysql> show processlist;
+-------+------+-----------------+------+---------+------+------------+------------------+
| Id    | User | Host            | db   | Command | Time | State      | Info             |
+-------+------+-----------------+------+---------+------+------------+------------------+
| 49701 | root | localhost:44364 | NULL | Query   |    0 | init       | show processlist |
| 51009 | root | localhost:47440 | test | Query   |    3 | User sleep | select sleep(5)  |
| 51010 | root | localhost:47442 | test | Query   |    3 | User sleep | select sleep(5)  |
......完整输出请查看附录
101 rows in set (0.00 sec)

mysql连接数增加到了101个,这就是问题所在了。在传统的nginx+php-fpm的架构中,一个请求到nginx,nginx让php-fpm一个线程执行脚本处理请求。这个时候一个合格的框架会保证这一次请求中有且只有一个mysql实例,也就是一次请求,不管需要操作数据库多少次,都仅连接数据库一次,只会出现一个连接,一次请求创建了连接数只有0次和1次,当然,请求结束在销毁数据的时候会断开连接。这也就保证了mysql连接数不会变得太大,可能服务器开启了64个php-fpm进程,那mysql同一时间最多也就只有64个连接。

但是在协程化编程的情况下,事情变得不可控起来了。曾经一条慢的sql会阻塞另一条慢sql,时间压力都在php-fpm这里,系统资源利用不到位,但是现在不阻塞了后php开始放飞自我了,一个worker进程就有可能会出现64个连接。来自php的时间成本是没有了,但是潜在的风险都转移到了数据库上,这是不能接受的。所以这里我们需要用到连接池来处理这个问题。

连接池

所谓连接池,就是在服务器启动的时候就创建若干个数据库连接,当worker需要执行数据库处理相关操作的时候,从连接池中取走连接;当worker处执行完数据库处理操作之后,将连接归还到连接池。

这样做的好处有很多

  1. 简化了创建数据库操作实例的过程,在编程过程中只需要关心怎么从连接池中取出一个协程化mysql实例,而不需要关心如果创建此实例以及如何连接等。
  2. 符合设计模式中的单一职责原则,既数据库操作相关处理放在数据库操作类中即可,在其他场景下只需要考虑使用。
  3. 减少了重复建立,断开数据库连接的网络IO成本
  4. 使用连接池能有效的控制mysql连接数

实现思路

通过通道来保存预先创建好的mysql实例,在worker执行中如果需要处理数据库连接,就从连接池中获取预警连接好的mysql实例进行操作即可,操作完后将连接归还至连接池。

最简单的连接池类

通过上面的分析可以总结出,一个最基础的连接池类需要做到的事情有以下几点

  1. 实例化时创建好连接
  2. 控制连接总数
  3. 向外提供一个获取连接实例的方法
  4. 向外提供一个归还连接实例的方法
interface PoolInterface
{
    /**
     * 向外提供一个获取连接实例的方法
     * @return mixed
     */
    public static function get();

    /**
     * 向外提供一个归还连接实例的方法
     * @param $one
     * @return mixed
     */
    public static function back($one);
}
Class MysqlPool implements PoolInterface
{
    protected static $connectNumber = 10;   //  连接池实例数量
    protected static $channel = null;   //  连接池使用的通道
    protected static $mysqlConfig = null;   //  mysql配置
    protected static $instance = null;  //  连接池实例

    /**
     * 配置
     * @param $mysqlConfig
     */
    public static function init($mysqlConfig)
    {
        self::$channel = new \Swoole\Coroutine\Channel(self::$connectNumber);
        self::$mysqlConfig = $mysqlConfig;
        self::createConnection();
    }

    /**
     * 创建连接
     */
    private static function createConnection()
    {
        for ($i = 0; $i < self::$connectNumber; $i++) {
            $mysql = new Swoole\Coroutine\Mysql();
            $mysql->connect(self::$mysqlConfig);
            self::$channel->push($mysql);
        }
    }

    /**
     * @return \Swoole\Coroutine\Mysql
     */
    public static function get(): Swoole\Coroutine\Mysql
    {
        return self::$channel->pop();
    }

    /**
     * @param $mysql
     * @return mixed|void
     * @throws Exception
     */
    public static function back($mysql)
    {
        if (!($mysql instanceof Swoole\Coroutine\Mysql)) {
            throw new Exception('Please![Swoole\Coroutine\Mysql] only!');
        }
        self::$channel->push($mysql);
    }
}
$mysqlConfig = require_once __DIR__ . '/mysql.config.php';

$reactor = new Reactor($mysqlConfig);

$server = new Swoole\Http\Server('127.0.0.1', 9001);

$server->set(['worker' => 1]);

$server->on('request', [$reactor, 'request']);

$server->on('workerStart', function () use ($mysqlConfig) {
    MysqlPool::init($mysqlConfig);
});

$server->start();
class Reactor
{
    protected $mysqlConfig = null;

    public function __construct($mysqlConfig)
    {
        $this->mysqlConfig = $mysqlConfig;
    }

    public function request(\Swoole\Http\Request $request, \Swoole\Http\Response $response)
    {
        $sql = 'select sleep(1)';

        for ($i = 0; $i < 2; $i++) {
            $mysql = MysqlPool::get();
            $mysql->query($sql);
            MysqlPool::back($mysql);
        }
        var_dump('协程id为' . Co::getcid());

        $response->end('test');
    }

}

创建一个拥有获取连接和归还连接方法的对象,在需要操作数据库时获取连接,操作结束后归还即可。这里直接将连接扔到了通道中,因为通道中没有数据外部执行pop弹出数据时会阻塞挂起,不需要做其他操作。至于连接检测,断开重连,动态伸缩等操作可以参考之前的【swoole.2.04】多进程示例:使用swoole实现多进程处理费时任务--弹性伸缩子程序,这里就不再说一遍基本的理念了。

结果

mysql> show processlist;
+-------+------+-----------------+------+---------+------+------------+------------------+
| Id    | User | Host            | db   | Command | Time | State      | Info             |
+-------+------+-----------------+------+---------+------+------------+------------------+
| 52636 | root | localhost:54774 | NULL | Query   |    0 | init       | show processlist |
| 52637 | root | localhost:54916 | test | Query   |    0 | User sleep | select sleep(1)  |
| 52638 | root | localhost:54918 | test | Query   |    0 | User sleep | select sleep(1)  |
| 52639 | root | localhost:54920 | test | Query   |    0 | User sleep | select sleep(1)  |
| 52640 | root | localhost:54922 | test | Query   |    0 | User sleep | select sleep(1)  |
| 52641 | root | localhost:54924 | test | Query   |    0 | User sleep | select sleep(1)  |
| 52642 | root | localhost:54926 | test | Query   |    0 | User sleep | select sleep(1)  |
| 52643 | root | localhost:54928 | test | Query   |    0 | User sleep | select sleep(1)  |
| 52644 | root | localhost:54930 | test | Query   |    0 | User sleep | select sleep(1)  |
| 52645 | root | localhost:54932 | test | Query   |    0 | User sleep | select sleep(1)  |
| 52646 | root | localhost:54934 | test | Query   |    0 | User sleep | select sleep(1)  |
+-------+------+-----------------+------+---------+------+------------+------------------+
11 rows in set (0.00 sec)

mysql中一直只有10个连接,一定程度上控制了mysql端的压力和风险。

附录

完整的输出

错误示范的请求
[root@iZbp1acp86oa3ixxw4n1dpZ ~]# ab -n1 127.0.0.1:9001/
This is ApacheBench, Version 2.3 <$Revision: 1430300 $>
Copyright 1996 Adam Twiss, Zeus Technology Ltd, http://www.zeustech.net/
Licensed to The Apache Software Foundation, http://www.apache.org/

Benchmarking 127.0.0.1 (be patient).....done


Server Software:        swoole-http-server
Server Hostname:        127.0.0.1
Server Port:            9001

Document Path:          /
Document Length:        4 bytes

Concurrency Level:      1
Time taken for tests:   5.003 seconds
Complete requests:      1
Failed requests:        0
Write errors:           0
Total transferred:      151 bytes
HTML transferred:       4 bytes
Requests per second:    0.20 [#/sec] (mean)
Time per request:       5003.034 [ms] (mean)
Time per request:       5003.034 [ms] (mean, across all concurrent requests)
Transfer rate:          0.03 [Kbytes/sec] received

Connection Times (ms)
              min  mean[+/-sd] median   max
Connect:        0    0   0.0      0       0
Processing:  5003 5003   0.0   5003    5003
Waiting:     5003 5003   0.0   5003    5003
Total:       5003 5003   0.0   5003    5003
错误演示下慢负荷server情况下mysql的情况
mysql> show processlist;
+-------+------+-----------------+------+---------+------+------------+------------------+
| Id    | User | Host            | db   | Command | Time | State      | Info             |
+-------+------+-----------------+------+---------+------+------------+------------------+
| 49701 | root | localhost:44364 | NULL | Query   |    0 | init       | show processlist |
| 51009 | root | localhost:47440 | test | Query   |    3 | User sleep | select sleep(5)  |
| 51010 | root | localhost:47442 | test | Query   |    3 | User sleep | select sleep(5)  |
| 51011 | root | localhost:47444 | test | Query   |    3 | User sleep | select sleep(5)  |
| 51012 | root | localhost:47446 | test | Query   |    3 | User sleep | select sleep(5)  |
| 51013 | root | localhost:47458 | test | Query   |    3 | User sleep | select sleep(5)  |
| 51014 | root | localhost:47460 | test | Query   |    3 | User sleep | select sleep(5)  |
| 51015 | root | localhost:47462 | test | Query   |    3 | User sleep | select sleep(5)  |
| 51016 | root | localhost:47464 | test | Query   |    3 | User sleep | select sleep(5)  |
| 51017 | root | localhost:47466 | test | Query   |    3 | User sleep | select sleep(5)  |
| 51018 | root | localhost:47470 | test | Query   |    3 | User sleep | select sleep(5)  |
| 51019 | root | localhost:47472 | test | Query   |    3 | User sleep | select sleep(5)  |
| 51020 | root | localhost:47474 | test | Query   |    3 | User sleep | select sleep(5)  |
| 51021 | root | localhost:47476 | test | Query   |    3 | User sleep | select sleep(5)  |
| 51022 | root | localhost:47478 | test | Query   |    3 | User sleep | select sleep(5)  |
| 51023 | root | localhost:47480 | test | Query   |    3 | User sleep | select sleep(5)  |
| 51024 | root | localhost:47482 | test | Query   |    3 | User sleep | select sleep(5)  |
| 51025 | root | localhost:47484 | test | Query   |    3 | User sleep | select sleep(5)  |
| 51026 | root | localhost:47486 | test | Query   |    3 | User sleep | select sleep(5)  |
| 51027 | root | localhost:47488 | test | Query   |    3 | User sleep | select sleep(5)  |
| 51028 | root | localhost:47490 | test | Query   |    3 | User sleep | select sleep(5)  |
| 51029 | root | localhost:47492 | test | Query   |    3 | User sleep | select sleep(5)  |
| 51030 | root | localhost:47494 | test | Query   |    3 | User sleep | select sleep(5)  |
| 51031 | root | localhost:47496 | test | Query   |    3 | User sleep | select sleep(5)  |
| 51032 | root | localhost:47498 | test | Query   |    3 | User sleep | select sleep(5)  |
| 51033 | root | localhost:47500 | test | Query   |    3 | User sleep | select sleep(5)  |
| 51034 | root | localhost:47502 | test | Query   |    3 | User sleep | select sleep(5)  |
| 51035 | root | localhost:47504 | test | Query   |    3 | User sleep | select sleep(5)  |
| 51036 | root | localhost:47506 | test | Query   |    3 | User sleep | select sleep(5)  |
| 51037 | root | localhost:47508 | test | Query   |    3 | User sleep | select sleep(5)  |
| 51038 | root | localhost:47510 | test | Query   |    3 | User sleep | select sleep(5)  |
| 51039 | root | localhost:47512 | test | Query   |    3 | User sleep | select sleep(5)  |
| 51040 | root | localhost:47514 | test | Query   |    3 | User sleep | select sleep(5)  |
| 51041 | root | localhost:47516 | test | Query   |    3 | User sleep | select sleep(5)  |
| 51042 | root | localhost:47518 | test | Query   |    3 | User sleep | select sleep(5)  |
| 51043 | root | localhost:47520 | test | Query   |    3 | User sleep | select sleep(5)  |
| 51044 | root | localhost:47522 | test | Query   |    3 | User sleep | select sleep(5)  |
| 51045 | root | localhost:47524 | test | Query   |    3 | User sleep | select sleep(5)  |
| 51046 | root | localhost:47526 | test | Query   |    3 | User sleep | select sleep(5)  |
| 51047 | root | localhost:47528 | test | Query   |    3 | User sleep | select sleep(5)  |
| 51048 | root | localhost:47530 | test | Query   |    3 | User sleep | select sleep(5)  |
| 51049 | root | localhost:47532 | test | Query   |    3 | User sleep | select sleep(5)  |
| 51050 | root | localhost:47534 | test | Query   |    3 | User sleep | select sleep(5)  |
| 51051 | root | localhost:47536 | test | Query   |    3 | User sleep | select sleep(5)  |
| 51052 | root | localhost:47538 | test | Query   |    3 | User sleep | select sleep(5)  |
| 51053 | root | localhost:47540 | test | Query   |    3 | User sleep | select sleep(5)  |
| 51054 | root | localhost:47542 | test | Query   |    3 | User sleep | select sleep(5)  |
| 51055 | root | localhost:47544 | test | Query   |    3 | User sleep | select sleep(5)  |
| 51056 | root | localhost:47546 | test | Query   |    3 | User sleep | select sleep(5)  |
| 51057 | root | localhost:47548 | test | Query   |    3 | User sleep | select sleep(5)  |
| 51058 | root | localhost:47550 | test | Query   |    3 | User sleep | select sleep(5)  |
| 51059 | root | localhost:47552 | test | Query   |    3 | User sleep | select sleep(5)  |
| 51060 | root | localhost:47554 | test | Query   |    3 | User sleep | select sleep(5)  |
| 51061 | root | localhost:47556 | test | Query   |    3 | User sleep | select sleep(5)  |
| 51062 | root | localhost:47558 | test | Query   |    3 | User sleep | select sleep(5)  |
| 51063 | root | localhost:47560 | test | Query   |    3 | User sleep | select sleep(5)  |
| 51064 | root | localhost:47562 | test | Query   |    3 | User sleep | select sleep(5)  |
| 51065 | root | localhost:47564 | test | Query   |    3 | User sleep | select sleep(5)  |
| 51066 | root | localhost:47566 | test | Query   |    3 | User sleep | select sleep(5)  |
| 51067 | root | localhost:47568 | test | Query   |    3 | User sleep | select sleep(5)  |
| 51068 | root | localhost:47570 | test | Query   |    3 | User sleep | select sleep(5)  |
| 51069 | root | localhost:47572 | test | Query   |    3 | User sleep | select sleep(5)  |
| 51070 | root | localhost:47574 | test | Query   |    3 | User sleep | select sleep(5)  |
| 51071 | root | localhost:47576 | test | Query   |    3 | User sleep | select sleep(5)  |
| 51072 | root | localhost:47578 | test | Query   |    3 | User sleep | select sleep(5)  |
| 51073 | root | localhost:47580 | test | Query   |    3 | User sleep | select sleep(5)  |
| 51074 | root | localhost:47582 | test | Query   |    3 | User sleep | select sleep(5)  |
| 51075 | root | localhost:47584 | test | Query   |    3 | User sleep | select sleep(5)  |
| 51076 | root | localhost:47586 | test | Query   |    3 | User sleep | select sleep(5)  |
| 51077 | root | localhost:47588 | test | Query   |    3 | User sleep | select sleep(5)  |
| 51078 | root | localhost:47590 | test | Query   |    3 | User sleep | select sleep(5)  |
| 51079 | root | localhost:47592 | test | Query   |    3 | User sleep | select sleep(5)  |
| 51080 | root | localhost:47594 | test | Query   |    3 | User sleep | select sleep(5)  |
| 51081 | root | localhost:47596 | test | Query   |    3 | User sleep | select sleep(5)  |
| 51082 | root | localhost:47598 | test | Query   |    3 | User sleep | select sleep(5)  |
| 51083 | root | localhost:47600 | test | Query   |    3 | User sleep | select sleep(5)  |
| 51084 | root | localhost:47602 | test | Query   |    3 | User sleep | select sleep(5)  |
| 51085 | root | localhost:47604 | test | Query   |    3 | User sleep | select sleep(5)  |
| 51086 | root | localhost:47606 | test | Query   |    3 | User sleep | select sleep(5)  |
| 51087 | root | localhost:47608 | test | Query   |    3 | User sleep | select sleep(5)  |
| 51088 | root | localhost:47610 | test | Query   |    3 | User sleep | select sleep(5)  |
| 51089 | root | localhost:47612 | test | Query   |    3 | User sleep | select sleep(5)  |
| 51090 | root | localhost:47614 | test | Query   |    3 | User sleep | select sleep(5)  |
| 51091 | root | localhost:47616 | test | Query   |    3 | User sleep | select sleep(5)  |
| 51092 | root | localhost:47618 | test | Query   |    3 | User sleep | select sleep(5)  |
| 51093 | root | localhost:47620 | test | Query   |    3 | User sleep | select sleep(5)  |
| 51094 | root | localhost:47622 | test | Query   |    3 | User sleep | select sleep(5)  |
| 51095 | root | localhost:47624 | test | Query   |    3 | User sleep | select sleep(5)  |
| 51096 | root | localhost:47626 | test | Query   |    3 | User sleep | select sleep(5)  |
| 51097 | root | localhost:47628 | test | Query   |    3 | User sleep | select sleep(5)  |
| 51098 | root | localhost:47630 | test | Query   |    3 | User sleep | select sleep(5)  |
| 51099 | root | localhost:47632 | test | Query   |    3 | User sleep | select sleep(5)  |
| 51100 | root | localhost:47634 | test | Query   |    3 | User sleep | select sleep(5)  |
| 51101 | root | localhost:47636 | test | Query   |    3 | User sleep | select sleep(5)  |
| 51102 | root | localhost:47638 | test | Query   |    3 | User sleep | select sleep(5)  |
| 51103 | root | localhost:47640 | test | Query   |    3 | User sleep | select sleep(5)  |
| 51104 | root | localhost:47642 | test | Query   |    3 | User sleep | select sleep(5)  |
| 51105 | root | localhost:47644 | test | Query   |    3 | User sleep | select sleep(5)  |
| 51106 | root | localhost:47646 | test | Query   |    3 | User sleep | select sleep(5)  |
| 51107 | root | localhost:47648 | test | Query   |    3 | User sleep | select sleep(5)  |
| 51108 | root | localhost:47650 | test | Query   |    3 | User sleep | select sleep(5)  |
+-------+------+-----------------+------+---------+------+------------+------------------+
101 rows in set (0.00 sec)

程序幼儿员-龚学鹏
请先登录后发表评论
  • latest comments
  • 总共0条评论