請(qǐng)問(wèn)下現(xiàn)階段是否有必要使用異步mysql查詢？

Jinson 2024-12-03

問(wèn)題描述

由于pdo查詢是阻塞的，我想著把pdo查詢改成異步查詢減少接口請(qǐng)求時(shí)間，然后在本地用單進(jìn)程分別試了下 pdo 跟 amphp/mysql，多次查詢時(shí)，異步查詢確實(shí)將接口請(qǐng)求時(shí)間顯著減少，但是上wrk壓測(cè)多次查詢數(shù)據(jù)庫(kù)時(shí)，amp 卻卡死了~

控制器代碼：

<?php

namespace app\admin\controller\system;

use app\bootstrap\AmpMysqlDb;
use support\basic\controller\BaseController;
use support\basic\db\AsyncQuery;
use support\Db;
use support\Request;
use function Amp\async;
use function Amp\Future\await;

class IndexController extends BaseController
{
    // pdo單次查詢
    public function simplePDO()
    {
        return Db::table('system_users')->find(random_int(1, 499500));
    }
    // amp單次查詢
    public function simpleAsync()
    {
        $pool      = AmpMysqlDb::getPool();
        $statement = $pool->prepare('SELECT * FROM wa_system_users WHERE id = :id');
        return $statement->execute(['id' => random_int(1, 499500)])->fetchRow();
    }

    //pdo查詢
    public function multiPDO(int $frequency = 10): array
    {
        $users = [];
        for ($i = 0; $i < $frequency; $i++) {
            $users[] = Db::table('system_users')->find(random_int(1, 499500));
        }
        return $users;
    }

    //異步查詢
    public function multiAsync(): array
    {
        $futures = [];
        for ($i = 0; $i < 10; $i++) {
            $futures[] = async(function () {
                $sql = 'SELECT * FROM wa_system_users WHERE id = :id';
                return AsyncQuery::raw($sql, ['id' => random_int(1, 499500)]);
            });
        }
        $data   = await($futures);
        $result = [];
        foreach ($data as $datum) {
            $result[] = $datum->fetchRow();
        }
        return $result;
    }

    public function index()
    {
        $result = $this->multiPDO();
        return $this->success('成功', $result);
    }
}

連接池代碼：

<?php
declare(strict_types=1);

namespace app\bootstrap;

use Amp\Mysql\MysqlConfig;
use Amp\Mysql\MysqlConnectionPool;
use Webman\Bootstrap;
use Workerman\Worker;

class AmpMysqlDb implements Bootstrap
{
    /** @var MysqlConnectionPool[] */
    private static array $pool = [];
    private static string $default = '';

    private const ALLOW_OPTIONS = [
        'host',
        'port',
        'user',
        'password',
        'db',
        'charset',
        'collate',
        'compression',
        'local-infile',

        'username',
        'database'
    ];

    public static function start(?Worker $worker): void
    {
        $config      = config('database', []);
        $connections = $config['connections'] ?? [];
        if (!$connections) {
            return;
        }

        $default = $config['default'] ?? false;
        foreach ($connections as $name => $config) {
            if ($config['driver'] == 'mysql') {
                if ($name == $default) {
                    self::$default = $name;
                }
                $dsn               = self::getDsn($config);
                $mysqlConfig       = MysqlConfig::fromString(trim($dsn));
                self::$pool[$name] = new MysqlConnectionPool($mysqlConfig, $config['pool_size'] ?? 10);
            }
        }
    }

    public static function getPool(string $connection = ''): MysqlConnectionPool
    {
        if (empty($connection)) {
            if (empty(self::$default)) {
                throw new \RuntimeException('Default database connection not configured.');
            }
            $connection = self::$default;
        }

        if (!isset(self::$pool[$connection])) {
            throw new \InvalidArgumentException("Database connection '{$connection}' not found.");
        }
        return self::$pool[$connection];
    }

    /**
     * @param array $config
     * @return string
     */
    protected static function getDsn(array $config): string
    {
        $dsn = '';
        foreach ($config as $key => $value) {
            if (in_array($key, static::ALLOW_OPTIONS, true)) {
                if (!$value) {
                    continue;
                }

                $key = match ($key) {
                    'username' => 'user',
                    'database' => 'db',
                    default => $key
                };
                $dsn .= "{$key}={$value} ";
            }
        }

        return $dsn;
    }

}

上wrk壓測(cè)接口，

環(huán)境：

Workerman[start.php] status
Start worker in DEBUG mode.
---------------------------------------------------GLOBAL STATUS---------------------------------------------------------
Workerman/5.0.0-rc.3    PHP/8.2.23 (Jit on)           Linux/5.4.0-200-generic
start time:2024-12-03 17:04:11   run 0 days 0 hours   load average: 0.34, 0.25, 0.28
3 workers    3 processes
name                          event-loop     exit_status     exit_count
webman                        revolt         0               0               
monitor                       revolt         0               0               
plugin.webman.push.server     revolt         0               0               
---------------------------------------------------PROCESS STATUS--------------------------------------------------------
pid memory  listening                name                      connections send_fail timers  total_request qps    status
19314   2.52M   http://0.0.0.0:8787      webman                    0           0         1       0             0      [idle]
19315   2.6M    none                     monitor                   0           0         2       0             0      [idle]
19316   2.58M   websocket://0.0.0.0:3131 plugin.webman.push.server 0           0         3       0             0      [idle]
---------------------------------------------------PROCESS STATUS--------------------------------------------------------
Summary 7.7M    -                        -                         0           0         6       0             0      [Summary]

數(shù)據(jù)庫(kù)：隨機(jī)生成50w條記錄的用戶表

單進(jìn)程，接口單次查詢數(shù)據(jù)庫(kù)的情況下，amp（454.52 qps）只有 pdo（2824.80 qps）的七分之一左右

pdo結(jié)果：

[root@dev backend-webman]# wrk -c200 -t5 -d3s http://127.0.0.1:8787/admin/system/index
Running 3s test @ http://127.0.0.1:8787/admin/system/index
 5 threads and 200 connections
 Thread Stats   Avg      Stdev     Max   +/- Stdev
   Latency    94.22ms  238.98ms   1.98s    92.91%
   Req/Sec     0.91k   368.56     1.84k    69.07%
 8757 requests in 3.10s, 5.73MB read
 Socket errors: connect 0, read 0, write 0, timeout 25
Requests/sec:   2824.80
Transfer/sec:      1.85MB

amp結(jié)果：

[root@dev backend-webman]# wrk -c200 -t5 -d3s http://127.0.0.1:8787/admin/system/index
Running 3s test @ http://127.0.0.1:8787/admin/system/index
 5 threads and 200 connections
 Thread Stats   Avg      Stdev     Max   +/- Stdev
   Latency   408.22ms   80.73ms 527.62ms   87.97%
   Req/Sec   109.36     74.59   326.00     65.42%
 1372 requests in 3.02s, 0.90MB read
Requests/sec:    454.52
Transfer/sec:    304.74KB

單進(jìn)程，接口查詢10次數(shù)據(jù)庫(kù)的情況下，amp（卡死），pdo（344qps）
pdo結(jié)果：

[root@dev backend-webman]# wrk -c200 -t5 -d3s http://127.0.0.1:8787/admin/system/index
Running 3s test @ http://127.0.0.1:8787/admin/system/index
 5 threads and 200 connections
 Thread Stats   Avg      Stdev     Max   +/- Stdev
   Latency   119.01ms  179.82ms   1.99s    97.11%
   Req/Sec   159.37     62.02   290.00     76.27%
 938 requests in 3.01s, 4.65MB read
 Socket errors: connect 0, read 0, write 0, timeout 8
Requests/sec:    311.36
Transfer/sec:      1.55MB

amp結(jié)果：

root@dev backend-webman]# wrk -c200 -t5 -d3s http://127.0.0.1:8787/admin/system/index
Running 3s test @ http://127.0.0.1:8787/admin/system/index
 5 threads and 200 connections
 Thread Stats   Avg      Stdev     Max   +/- Stdev
   Latency   815.46ms  612.26ms   1.78s    58.33%
   Req/Sec     4.47      4.85    20.00     82.35%
 18 requests in 3.02s, 91.47KB read
 Socket errors: connect 0, read 0, write 0, timeout 6
Requests/sec:      5.95
Transfer/sec:     30.25KB
[root@dev backend-webman]# curl http://127.0.0.1:8787/admin/system/index
^C
[root@dev backend-webman]#

? 卡死了，是連接池耗盡，將mysql最大連接數(shù)改成1000，然后又把連接池?cái)?shù)量改成900

new MysqlConnectionPool($mysqlConfig, 900);

再次測(cè)試amp結(jié)果：

[root@dev backend-webman]# wrk -c200 -t5 -d3s http://127.0.0.1:8787/admin/system/index
Running 3s test @ http://127.0.0.1:8787/admin/system/index
  5 threads and 200 connections
  Thread Stats   Avg      Stdev     Max   +/- Stdev
    Latency   572.83ms  400.34ms   1.98s    74.15%
    Req/Sec    44.92     23.62   140.00     67.86%
  381 requests in 3.01s, 1.89MB read
  Socket errors: connect 0, read 0, write 0, timeout 29
Requests/sec:    126.42
Transfer/sec:    642.39KB

測(cè)試結(jié)果不及預(yù)期，這結(jié)果感覺沒(méi)必要上異步查詢了，畢竟這還只是單進(jìn)程下開1000個(gè)連接池，多開進(jìn)程的時(shí)候數(shù)據(jù)庫(kù)連接數(shù)太多反而導(dǎo)致性能降低。

不知道是不是我姿勢(shì)不對(duì)，有沒(méi)有大佬指點(diǎn)一下？

 765  2 2

2個(gè)回答

walkor 2024-12-03

打賞

目前看不管是 amphp/mysql，還是swoole swow協(xié)程，cpu跑滿的情況下，它們的極限性能都沒(méi)有多進(jìn)程pdo阻塞性能高。

amphp/mysql應(yīng)該是php解析的mysql協(xié)議，PHP做協(xié)議解析會(huì)比c語(yǔ)言消耗很多cpu。所以cpu跑滿的情況下，amphp/mysql比阻塞pdo性能差更多。
如果是服務(wù)器負(fù)載不高的情況下，通過(guò)連接池并發(fā)查詢可以減少多個(gè)SQL的總體等待時(shí)間，表象就是響應(yīng)速度會(huì)很快。但是實(shí)際上這個(gè)快可以看作是以消耗更多cpu的代價(jià)換來(lái)的。當(dāng)請(qǐng)求量變大，則性能會(huì)急劇下降。

如果業(yè)務(wù)請(qǐng)求量不大，想要提高響應(yīng)速度，可以嘗試 amphp/mysql。但是相比 amphp/mysql ，使用swoole或者swow驅(qū)動(dòng)科效果會(huì)更好，前提是你了解使用協(xié)程帶來(lái)的副作用，例如全局變量污染，上下文傳遞，還有考慮compsoer其他組件兼容問(wèn)題。

Jinson 2024-12-03

感謝解答，如果要使用swoole或者swow就上hyperf了，就是比較喜歡webman，所以嘗試下workerman 5.0用revolt/event-loop。業(yè)務(wù)請(qǐng)求量不大，想要提高響應(yīng)速度，可以考慮用在后臺(tái)管理上，不過(guò)既然是后臺(tái)使用，速度稍微慢點(diǎn)倒也能接受
wudx8 2024-12-11

其實(shí)可以寫一些工具方法。在特定的場(chǎng)景里，檢測(cè)到安裝swoole擴(kuò)展就使用協(xié)程。用起來(lái)很方便的。

evilk 2024-12-04

我認(rèn)為,正確的方向,應(yīng)該是,通過(guò) 優(yōu)化SQL 來(lái)減少查詢時(shí)間

Jinson 2024-12-04

是，有時(shí)候做統(tǒng)計(jì)，需要查詢幾個(gè)表比較慢，這個(gè)實(shí)驗(yàn)一下能否異步查詢減少耗時(shí)。不過(guò)現(xiàn)在統(tǒng)計(jì)基本都丟clickhouse里搞，那個(gè)支持異步
ngrok.cc內(nèi)網(wǎng)穿透 2024-12-13

做統(tǒng)計(jì)，你可以不用全表統(tǒng)計(jì)，采用增量統(tǒng)計(jì)，就是每次統(tǒng)計(jì)不是統(tǒng)計(jì)所有的，而是累加的。這樣可以減少數(shù)據(jù)庫(kù)的消耗