使用aiomysql+anyio并行查询MySQL时单查询耗时随任务数上升

我在异步应用中使用Python的aiomysql查询MySQL数据库，同时借助anyio的create_task_group运行并行任务，目的是加速分块获取数据，但发现单查询耗时随任务数量增加而上升。

测试代码 #

import os
import aiomysql
from time import time
from anyio import run, create_task_group
from dotenv import load_dotenv

diffs = []  # 存储单次查询耗时
tot_reqs = 0  # 统计总请求数


async def measure(pool):
    global diffs
    global tot_reqs

    async with pool.acquire() as conn:
        start_time = time()
        while True:
            t1 = time()
            cur = await conn.cursor()
            sql = "select * from ttable limit 10000"
            await cur.execute(query=sql)
            await cur.close()

            diffs.append(time() - t1)
            tot_reqs += 1
            if len(diffs) > 9:  # 每10次查询打印平均耗时
                print(
                    f"[{time()-start_time:.2f}s 已启动] 平均查询耗时: {sum(diffs)/len(diffs)} (总请求数: {tot_reqs})"
                )
                diffs = []


async def case():
    db_env_prefix = "SOME_MYSQL"
    pool = await aiomysql.create_pool(
        host=os.getenv(db_env_prefix + "_HOST"),
        port=int(os.getenv(db_env_prefix + "_PORT") or 3306),
        user=os.getenv(db_env_prefix + "_USERNAME"),
        password=os.getenv(db_env_prefix + "_PASSWORD"),
        db=os.getenv(db_env_prefix + "_DB"),
        maxsize=10,
        autocommit=True,
        pool_recycle=600,
    )
    async with create_task_group() as tg:
        for _ in range(10):
            tg.start_soon(measure, pool)
    pool.close()
    await pool.wait_closed()


if __name__ == "__main__":
    load_dotenv()
    run(case)

测试结果 #

• 10个并行任务：约13秒完成100次查询，单查询平均耗时1.3秒
• 1个任务：同样13秒完成100次查询，单查询平均耗时0.13秒，速度快10倍

排查情况 #

• 场景仅涉及IO请求，无CPU密集型任务
• 尝试过更换查询表、调整MySQL配置（如innodb_thread_concurrency、innodb_read_io_threads等），问题仍存在
• 奇怪的是，在多个终端同时运行相同代码时，单查询耗时并未受影响
• 多进程可解决问题，但存在弊端，而IO异步任务本不应出现阻塞现象

补充信息（MariaDB进程查询结果） #

MariaDB [(none)]> SELECT 'SLEEPING MDB Ram use', COUNT(*),SUM(time),SUM(memory_used),SUM(max_memory_used) FROM information_schema.processlist WHERE command="Sleep";
+----------------------+----------+-----------+------------------+----------------------+
| SLEEPING MDB Ram use | COUNT(*) | SUM(time) | SUM(memory_used) | SUM(max_memory_used) |
+----------------------+----------+-----------+------------------+----------------------+
| SLEEPING MDB Ram use |        1 |       309 |            79632 |               162472 |
+----------------------+----------+-----------+------------------+----------------------+

内容的提问来源于stack exchange，提问作者Alex Monik