請問下現(xiàn)階段是否有必要使用異步mysql查詢？
問題描述
由于pdo查詢是阻塞的，我想著把pdo查詢改成異步查詢減少接口請求時間，然后在本地用單進(jìn)程分別試了下 pdo 跟 amphp/mysql，多次查詢時，異步查詢確實(shí)將接口請求時間顯著減少，但是上wrk壓測多次查詢數(shù)據(jù)庫時，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壓測接口，
環(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ù)庫：隨機(jī)生成50w條記錄的用戶表
-
單進(jìn)程，接口單次查詢數(shù)據(jù)庫的情況下，
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.85MBamp結(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ù)庫的情況下，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.55MBamp結(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，然后又把連接池數(shù)量改成900
new MysqlConnectionPool($mysqlConfig, 900);再次測試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測試結(jié)果不及預(yù)期，這結(jié)果感覺沒必要上異步查詢了，畢竟這還只是單進(jìn)程下開1000個連接池，多開進(jìn)程的時候數(shù)據(jù)庫連接數(shù)太多反而導(dǎo)致性能降低。
不知道是不是我姿勢不對，有沒有大佬指點(diǎn)一下？
2個回答
打賞
目前看不管是 amphp/mysql，還是swoole swow協(xié)程，cpu跑滿的情況下，它們的極限性能都沒有多進(jìn)程pdo阻塞性能高。
amphp/mysql應(yīng)該是php解析的mysql協(xié)議，PHP做協(xié)議解析會比c語言消耗很多cpu。所以cpu跑滿的情況下，amphp/mysql比阻塞pdo性能差更多。
如果是服務(wù)器負(fù)載不高的情況下，通過連接池并發(fā)查詢可以減少多個SQL的總體等待時間，表象就是響應(yīng)速度會很快。但是實(shí)際上這個快可以看作是以消耗更多cpu的代價換來的。當(dāng)請求量變大，則性能會急劇下降。
如果業(yè)務(wù)請求量不大，想要提高響應(yīng)速度，可以嘗試 amphp/mysql。但是相比 amphp/mysql ，使用swoole或者swow驅(qū)動科效果會更好，前提是你了解使用協(xié)程帶來的副作用，例如全局變量污染，上下文傳遞，還有考慮compsoer其他組件兼容問題。
我認(rèn)為,正確的方向,應(yīng)該是,通過 優(yōu)化SQL 來減少查詢時間
-
Jinson 2024-12-04是，有時候做統(tǒng)計，需要查詢幾個表比較慢，這個實(shí)驗(yàn)一下能否異步查詢減少耗時。不過現(xiàn)在統(tǒng)計基本都丟clickhouse里搞，那個支持異步
-
ngrok.cc內(nèi)網(wǎng)穿透 2024-12-13做統(tǒng)計，你可以不用全表統(tǒng)計，采用增量統(tǒng)計，就是每次統(tǒng)計不是統(tǒng)計所有的，而是累加的。這樣可以減少數(shù)據(jù)庫的消耗
相關(guān)問題
月貢獻(xiàn)榜
贊助商
熱門問答
webman限流器發(fā)布
DeepSeek 本地部署教程(極其簡單)
webman2.1 日志未兼容協(xié)程
Webman AI + DeepSeek本地訓(xùn)練，打造私有知識庫
請workerman官網(wǎng)登錄加強(qiáng)安全處理，謝謝
workerman發(fā)布v5版本
關(guān)于程序防破解的問題
?? Webman AI接入DeepSeek教程 [贈送50￥+ 20$額度]
物聯(lián)網(wǎng)平臺中的程序問題
queue redis 的 onConsumeFailure捕獲不到異常信息
