Golang使用sqlx报错max_prepared_stmt

文章目录

- 背景
- mysql的预处理
- - 查看实例预处理详情
  - - com_stmt_prepare
    - 开启performance_schema
  - 本地查看预处理语句
- 预处理语句飙升的原因
- - 生成预处理语句但是不close
  - 执行sql过程中发生错误
- go服务分析
- - 抓包分析发送给mysql的包
  - debug查看预处理细节
  - - sqlx发送statement command指令
    - sqlx关闭stmt的close
  - 怎么才能不使用预处理语句
- 结论
- 解决方案

背景

线上的跑的go服务操作mysql突然报错导致服务不可用，错误信息如下：

MySQL error: 1461 "Can't create more than max_prepared_stmt_count statements (current value: 16382)"

max_prepared_stmt_count是MySQL的一个基本参数，其是用来限制一个session内最多可以有多少条预处理语句，默认大小限制是16382。

mysql> show variables like 'max_prepared_stmt_count';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| max_prepared_stmt_count | 16382 |
+-------------------------+-------+

从报错信息上来看，是当前实例的预处理语句达到了16382的上限导致。

go服务使用的sql库是sqlx，数据库版本是mysql5.7.4

mysql的预处理

预处理把sql语句和参数区分开，编译是针对sql语句的编译，配合参数进行实际的sql操作。

一次编译、多次运行，提升性能
防止sql注入，参数不参与sql语句的编译
防止ddos攻击，上限16382

参考：如何定位和处理预编译语句（prepared statements）数量超限的问题 - 掘金

查看实例预处理详情

com_stmt_prepare

com_xx是mysql中的语句计数器变量，指每个语句已执行的次数。只要执行准备语句 API 调用（例如 mysql_stmt_prepare()、 **mysql_stmt_execute()**等），它们的值就会增加。

Com_stmt_close prepare语句关闭的次数
Com_stmt_execute prepare语句执行的次数
Com_stmt_prepare prepare语句创建的次数

Com_stmt_prepare 减去 Com_stmt_close 大于 max_prepared_stmt_count 就会出现这种错误。最简单的解决的方案就是调大max_prepared_stmt_count的值，但治标不治本。
参考：max_prepared_stmt_count 问题与Sysbench 工具简介

而且计数器只能看到数量，看不到细节。max_prepared_stmt_count是数据库实例级别的变量，会影响到所有用到这个数据库实例的服务。如果要看具体哪个sql语句哪个客户端导致的预处理语句飙升，那么就要看下面的prepared_statements_instances表了。

线上数据库的com_stmt_prepare

可以看到prepare 和close的值是一致的，代表执行的prepare语句都被close掉了,虽然com_stmt_prepare的值超过了16382，但实际上并不会抛出错误。

注：

每次在RDS上执行sql也会导致com_stmt_prepare和com_stmt_close的值提升。
GLOBAL STATUS除非重新启动，否则无法重置计数器。重启服务也不会重置计数器。

开启performance_schema

performance_schema是mysql的一个系统库，主要记录资源的消耗，资源等待等记录。performance_schema的prepared_statements_instances表中也记录了预处理语句。

1、show variables like 'performance_schema';
开启需要设置成ON，参考：https://blog.csdn.net/weixin_41275260/article/details/125461160
2、设置 performance_schema_max_prepared_statements_instances
通过这个参数控制表的大小，可以设置成<=16283

注：目前线上服务没有开启performance_schema，看不到故障现场的预处理语句情况。

本地查看预处理语句

手动执行prepare，发现prepare表中存在了该记录

此时查看prepare和close的值，发现这个prepare语句没有被close掉，因为正在被使用。

预处理语句飙升的原因

生成预处理语句但是不close

模拟只生成prepare语句，但是不close的情况。模拟结果也是mysql抛出错误，无法再创建预处理语句。

mysql> show global status like 'Com_stmt_%';
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| Com_stmt_execute        | 2437392 |
| Com_stmt_close          | 2437392 |
| Com_stmt_fetch          | 0       |
| Com_stmt_prepare        | 2453774 |
| Com_stmt_reset          | 0       |
| Com_stmt_send_long_data | 0       |
| Com_stmt_reprepare      | 0       |
+-------------------------+---------+Com_stmt_prepare - Com_stmt_close = 16382

执行sql过程中发生错误

模拟15次sql语句语法错误，此时查看Com_stmt_prepare如下：

mysql> SHOW global STATUS LIKE 'Com_stmt_%';
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| Com_stmt_execute        | 23 |
| Com_stmt_close          | 23 |
| Com_stmt_fetch          | 0       |
| Com_stmt_prepare        | 38 |
| Com_stmt_reset          | 0       |
| Com_stmt_send_long_data | 0       |
| Com_stmt_reprepare      | 0       |
+-------------------------+---------+prepare - close = 15

mysql官网解释如下：

即使预准备语句参数未知或执行期间发生错误，所有变量也会增加。换句话说，它们的值对应于发出的请求数，而不是成功完成的请求数。例如，由于状态变量是在每次服务器启动时初始化的，并且不会在重新启动后持续存在，因此跟踪和语句的和变量的值通常为零，但如果或语句已执行但失败，则可以为非零值

mysql官方文档地址：https://dev.mysql.com/doc/refman/8.0/en/server-status-variables.html#statvar_Com_xxx

正常的prepare语句可以随着连接释放而断开，因mysql错误导致的prepare语句没有被close掉，这种是释放不了的。

go服务分析

抓包分析发送给mysql的包

抓包发现每次sql会发送三个指令，一个是prepare包含sql语句，一个是exec执行命令，一个是close statement指令。如下所示：

debug查看预处理细节

go服务中使用的查询语句主要是Get()和Select()方法。

sqlx发送statement command指令

func (stmt *mysqlStmt) query(args []driver.Value) (*binaryRows, error) {if stmt.mc.closed.Load() {errLog.Print(ErrInvalidConn)return nil, driver.ErrBadConn}	
// Send commanderr := stmt.writeExecutePacket(args)if err != nil {return nil, stmt.mc.markBadConn(err)}mc := stmt.mc// Read ResultresLen, err := mc.readResultSetHeaderPacket()if err != nil {return nil, err}
}

sqlx关闭stmt的close


func (rs *Rows) close(err error) error {
// 忽略if rs.closeStmt != nil {rs.closeStmt.Close()}
// 忽略return err
}closeStmt哪来的？
func (db *DB) queryDC(){var si driver.Stmtvar err errorwithLock(dc, func() {si, err = ctxDriverPrepare(ctx, dc.ci, query)})if err != nil {releaseConn(err)return nil, err}// ds := &driverStmt{Locker: dc, si: si}rowsi, err := rowsiFromStatement(ctx, dc.ci, ds, args...)if err != nil {ds.Close()releaseConn(err)return nil, err}
}

func (stmt *mysqlStmt) Close() error {if stmt.mc == nil || stmt.mc.closed.Load() {// driver.Stmt.Close can be called more than once, thus this function// has to be idempotent.// See also Issue #450 and golang/go#16019.//errLog.Print(ErrInvalidConn)return driver.ErrBadConn}err := stmt.mc.writeCommandPacketUint32(comStmtClose, stmt.id)stmt.mc = nilreturn err
}

理论上来说，我们想要对重复的sql省去编译的时间，那么是不是不应该每次都close()？如果不主动close那么就只能等待连接释放来关闭prepare语句了?

go官方在14年回答过这个问题，需要执行stmt.close()来释放资源。https://groups.google.com/g/golang-nuts/c/ISh22XXze-s
go官方文档也说：确保stmt.Close在代码完成语句时调用它。这将释放可能与其关联的任何数据库资源（例如底层连接）。对于函数中仅是局部变量的语句，使用defer stmt.Close()就足够了。
https://go.dev/doc/database/prepared-statements
database/sql中也有一段话：由于语句在原始连接繁忙时会根据需要重新准备，因此数据库的高并发使用可能会导致大量连接繁忙，从而创建大量准备好的语句。这可能会导致明显的语句泄漏，准备和重新准备语句的频率比您想象的要高，甚至会遇到服务器端语句数量的限制。
Using Prepared Statements

怎么才能不使用预处理语句

func (mc *mysqlConn) query(query string, args []driver.Value) (*textRows, error) {// 这里判断参数是否为0，是否有占位符，没有占位符则发送给mysql的包没有stmt模版if len(args) != 0 {if !mc.cfg.InterpolateParams {return nil, driver.ErrSkip}// try client-side prepare to reduce roundtripprepared, err := mc.interpolateParams(query, args)if err != nil {return nil, err}query = prepared}
}

这么看起来，go官方的实现就是只要sql中有占位符就默认使用预处理的方式，然后close掉。如果sql语句不包含占位符，则直接发送sql语句给mysql服务器。

结论

sql语句执行失败、没有主动close stmt都会导致mysql实例中预处理语句数量的飙升
go服务中带占位符的sql会自动生成和关闭预处理语句，没有占位符的sql则不使用预处理语句
可通过Com_stmt_prepare - Com_stmt_close观测实例中的预处理语句数量

解决方案

通过报警提前发现
运维开启性能追踪库，可排查具体导致预处理语句飙升的sql以及数据库
服务的连接导致的预处理语句没close，可以重启服务解决。mysql server内部执行错误导致的预处理没close则需要重启数据库，并排查服务减少错误sql的产生
尽量不要太多服务使用一个数据库实例，如果都使用预处理语句的话，16382的上限也不算多