编程 1461 报错先算 16382 ÷ 连接数：Node.js 驱动层 prepared statement 配额怎么定位

2026-11-03 00:03:47

1461 报错先算 16382 ÷ 连接数：Node.js 驱动层 prepared statement 配额怎么定位

Can't create more than max_prepared_stmt_count statements (current value: 16382)

SQLSTATE 42000，符号 ER_MAX_PREPARED_STMT_COUNT_REACHED。看到这条先别急着调上限，算一下：16382 ÷ 当前连接数 = 每连接平均还能缓存几条。50 个连接是 327 条，200 个连接只剩 81 条。而 mysql2 的 maxPreparedStatements 默认值是 16000——单连接的上限，比全局配额除以十还大。两个变量不在一个量级，先分清。

全局配额与单连接缓存

max_prepared_stmt_count 默认 16382（不是 16384），有效范围 0~1048576，动态生效，无需重启。每条 server-side prepared statement 至少占 8KB 内存。它限制的是 mysqld 上所有连接合计的语句数，不是单连接、不是单用户、不是单 schema。

mysql2 的 maxPreparedStatements 是 per connection：connection.execute() 的语句缓存在 lru-cache 里，默认 16000 条，被 LRU 淘汰的语句会被 close。连接不知道其他连接的状态，各算各的。

Prisma 的 statement_cache_size 底层默认值是 1000，不是直觉上的几十。对比之下，MySQL Connector/J 的 prepared statements 默认只有 25 个。

服务器上的 PS 总数 ≈ 连接数 × 每连接唯一 SQL 文本数。连接之间不能共享 prepared statement，所以连接一多，两边就撞上。

SHOW VARIABLES LIKE 'max_prepared_stmt_count';
SHOW GLOBAL STATUS LIKE 'Prepared_stmt_count';
SHOW GLOBAL STATUS LIKE 'Com_stmt_prepare';
SHOW GLOBAL STATUS LIKE 'Com_stmt_close';
SHOW VARIABLES LIKE 'wait_timeout';

execute()、query() 与手动 prepare 的边界

connection.execute()：走二进制协议，会缓存 prepared statement，用完用 connection.unprepare() 移除缓存。
connection.prepare()：手动 prepare 的语句不带 LRU，必须用 statement.close() 关闭，unprepare() 对它无效。
connection.query()：文本协议，不产生 server-side prepared statement。

SQL 文本是否唯一，直接决定缓存条数：

// 每次都 prepare 一条新语句，唯一文本无限增长
await conn.execute('select ' + Date.now() + ' as timestamp');

// 只第一次 prepare，之后复用
await conn.execute('select ? as timestamp', [Date.now()]);

定位：先看是谁在吃配额

performance_schema.prepared_statements_instances 每条已分配语句一行，OWNER_THREAD_ID 指向创建它的线程；语句被 COM_STMT_CLOSE / DEALLOCATE PREPARE 释放时对应行删除。

按线程聚合：

SELECT t.PROCESSLIST_ID, t.PROCESSLIST_USER,
       SUBSTRING_INDEX(t.PROCESSLIST_HOST, ':', 1) AS ip,
       t.PROCESSLIST_TIME AS idle_sec,
       COUNT(ps.STATEMENT_ID) AS stmt_count
FROM performance_schema.threads t
LEFT JOIN performance_schema.prepared_statements_instances ps
       ON t.THREAD_ID = ps.OWNER_THREAD_ID
WHERE t.TYPE = 'FOREGROUND' AND t.PROCESSLIST_USER IS NOT NULL
GROUP BY t.THREAD_ID, t.PROCESSLIST_ID, t.PROCESSLIST_USER,
         t.PROCESSLIST_HOST, t.PROCESSLIST_TIME
HAVING stmt_count > 50
ORDER BY stmt_count DESC;

全局用量：

SELECT @@max_prepared_stmt_count AS max_allowed,
       (SELECT VARIABLE_VALUE FROM performance_schema.global_status
        WHERE VARIABLE_NAME = 'Prepared_stmt_count') AS current_count;

按来源 IP 归并，判断是哪几个服务：

SELECT SUBSTRING_INDEX(t.PROCESSLIST_HOST, ':', 1) AS client_ip,
       COUNT(DISTINCT t.PROCESSLIST_ID) AS connections,
       SUM(CASE WHEN t.PROCESSLIST_COMMAND='Sleep' AND t.PROCESSLIST_TIME>300 THEN 1 ELSE 0 END) AS zombies,
       SUM(prep.stmt_count) AS total_ps,
       ROUND(AVG(prep.stmt_count),1) AS avg_per_conn
FROM performance_schema.threads t
LEFT JOIN (SELECT OWNER_THREAD_ID, COUNT(*) stmt_count
           FROM performance_schema.prepared_statements_instances
           GROUP BY OWNER_THREAD_ID) prep
       ON t.THREAD_ID = prep.OWNER_THREAD_ID
WHERE t.TYPE='FOREGROUND' AND t.PROCESSLIST_USER IS NOT NULL
GROUP BY client_ip ORDER BY total_ps DESC;

OWNER_OBJECT_TYPE IS NOT NULL 的行表示语句由存储过程/函数创建，用来抓存储程序里没释放的语句：

SELECT OWNER_OBJECT_TYPE, OWNER_OBJECT_SCHEMA, OWNER_OBJECT_NAME, STATEMENT_NAME, SQL_TEXT
FROM performance_schema.prepared_statements_instances
WHERE OWNER_OBJECT_TYPE IS NOT NULL;

计数与连接：threads 对 processlist，KILL 后归零

Prepared_stmt_count 是 GLOBAL STATUS，不能 FLUSH 重置，只能等连接释放、被杀，或重启。performance_schema.threads.PROCESSLIST_ID 就是 information_schema.processlist.ID。

SELECT ID, USER, HOST, COMMAND, TIME, STATE
FROM information_schema.processlist
WHERE COMMAND = 'Sleep' AND TIME > 300
ORDER BY TIME DESC;

KILL 1234567;
SELECT VARIABLE_VALUE FROM performance_schema.global_status
WHERE VARIABLE_NAME = 'Prepared_stmt_count';

某站点计数卡在 16382，processlist 里筛出 192.168.2.23 的两个 Sleep 连接，KILL 掉这两个 ID 后计数直接回落到 0。Com_stmt_prepare 与 Com_stmt_close 的差值长期为正，说明计数一直在涨。

抓包拿 SQL 原文

tcpdump -i any -s 65535 dst port 3306 -w cap.pcap

Wireshark 打开，过滤协议 mysql，能看到 COM_STMT_PREPARE 携带的 SQL 文本，例如 SELECT id FROM news WHERE source=? AND url LIKE ?，再回代码里搜这段 SQL。哪一行业务代码在重复 prepare，到这一步基本能落点。

调小配额还是换协议：看条件

调小 maxPreparedStatements 能消除报错，但有代价。实测 max_prepared_stmt_count=20、connectionLimit=4：maxPreparedStatements=4（= 4×4）时峰值 16，LRU 正常刷新不报错；调到 5（= 5×4）后峰值 20，下一次 prepare 就报错。差一条的边界疑似 <= 与 < 的实现或并发竞态，需自行验证。更关键的是，配额压低后通信会退化——每次都要 prepare → execute → close，比非 PS 的 query() 还低效。

execute() 换 query() 能绕开配额，但不走二进制协议，丢类型处理和二进制编码，取回的数字、日期类型表现会变。值不值得换，取决于这条 SQL 是否高频、结果类型是否敏感。

手动 prepare() 的场景，记得用 statement.close()，unprepare() 管不到它。

JDBC 侧有个对照：useServerPrepStmts=true 但缺 cachePrepStmts=true 时，每次 prepareStatement 都会重建一条服务端语句——驱动层没开 LRU，句柄就复用不了。

生产修复后的数字

某个案例调整后：prepared stmt 总数 15000+ → 约 400；单连接最多 2000+ → 约 20；僵尸连接 200+ → 0；连接寿命 30 分钟 → 5 分钟；配额使用率 97% → 3%。兜底加了 SET GLOBAL wait_timeout=300、interactive_timeout=300。

Prisma 案例里，先减少查询的 include 表数量（每个 include 生成一条 PS，4 个 include → 4 条，改成视图后 1 条）；加机器后连接数翻倍又触发。最终落到连接串参数：

DATABASE_URL=mysql://${USER}:${PASSWORD}@${HOST}:${PORT}/${DATABASE}?connection_limit=10&statement_cache_size=7

估算式：缓存上限 ≈ connection_limit × statement_cache_size × 实例数，10×7=70。连接越多，每连接应缓存越少；多实例、多 schema、多用户要一起算进 16382 里。

应急手段两种，都是买时间：临时 SET GLOBAL max_prepared_stmt_count=50000，泄漏仍在；或用 MySQL EVENT 定时 KILL 空闲超过 10 分钟的连接（案例里 600 秒阈值，每小时杀 50~100 个连接，释放上千条语句），代价是被杀连接上的应用会看到连接错误。

与站内其他几篇的边界

#8184 讲引擎侧全局配额的分型与 sysbench 公式；#8334 是 Connector/J 的 25/256 默认值；#8336 是 Go sql.Stmt 的句柄管理。这篇只看 Node 驱动层：mysql2 的 per-connection LRU 与 Prisma 的 statement_cache_size，以及怎么把泄漏钉到具体连接。