excuting sql costs a lots of time occasionally.

[prettygirl20231]

enviroment:
mysql8.0
pymysql
python3.13

metersphere_db_pool = PooledDB(
    creator=pymysql,  # 使用链接数据库的模块
    mincached=2,  # 初始化时，链接池中至少创建的链接，0表示不创建
    maxcached=10,
    maxconnections=10,  # 连接池允许的最大连接数，0和None表示不限制连接数
    blocking=False,  # 连接池中如果没有可用连接后，是否阻塞等待。True，等待；False，不等待然后报错
    host='52.83.162.13',
    port=3306,
    ping=0,
    database='metersphere_dev'
)

def metersphere_query(sql: str, *args) -> list[dict]:
    """查询数据"""
    try:
        conn = metersphere_db_pool.connection()
        cursor = conn.cursor(pymysql.cursors.DictCursor)  # 表示读取的数据为字典类型
        start_time = time.time()
        cursor.execute(sql, args)
        end_time = time.time()
        print(f"execute耗时{end_time - start_time}")
        result = cursor.fetchall()
    finally:
        cursor.close()
        conn.close()
    return result

cursor.execute(sql, args) cost a lots of time(about 20s) occasionally.
This problem often occurs when the query has not been used for a period of time.

[Cito]

Does this happen for complex queries? I can imagine that if a connection is idle for a long time, the query cache and buffer pool needs to be re-fetched.

It could also happen when you are using too many connections and set blocking to True, but since you set it to False, you should get an error in this case.

It could be also some kind of MySQL quirk, like the ones reported here, here or here.

Without further information or reproducible code, I cannot help you with this.

Personally, I'm using PostgreSQL and never experienced problems like this.