排查MySQL数据库报错 error 1461报错

数据运维 2023-04-12 Kariuki 手机阅读

问题描述

MySQL 出现如下报错：我该如何去排查并解决此问题

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

问题分析

使用sysbench进行压力测试：

[root@iv-38der6cb2agc9tvqv0qf ~]# sysbench --db-driver=mysql --mysql-host=rds-mysql-h28a****.rds.ivolces.com --mysql-port=3306 --mysql-user=*** --mysql-password=******** --mysql-db=sbtest1 --table_size=25000 --tables=10 --events=0 --time=3000  --threads=1000 oltp_read_write run
sysbench 1.1.0 (using bundled LuaJIT 2.1.0-beta3)

Running the test with following options:
Number of threads: 1000
Initializing random number generator from current time


Initializing worker threads...

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

如果我们将--threads 调整的小一些是没有问题的。出现这个报错与 "max_prepared_stmt_count[1]" 参数有关，该参数取值范围为0~1048576，默认为“16382”，该参数限制了同一时间在mysqld上所有session中prepared语句的上限。

MySQL [(none)]> show variables like 'max_prepared_stmt_count';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| max_prepared_stmt_count | 16382 |
+-------------------------+-------+
1 row in set (0.004 sec)

解决方案

您可以调整 max_prepared_stmt_count 参数的取值来规避此问题。

MySQL [(none)]> show global status like 'com_stmt%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Com_stmt_execute        | 0     |
| Com_stmt_close          | 0     |
| Com_stmt_fetch          | 0     |
| Com_stmt_prepare        | 16614 |
| Com_stmt_reset          | 0     |
| Com_stmt_send_long_data | 0     |
| Com_stmt_reprepare      | 0     |
+-------------------------+-------+
7 rows in set (0.002 sec)

会出现三个相关的状态：

  • 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即可解决

mysql> set global max_prepared_stmt_count=300000;

参考文档

[1] https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_max_prepared_stmt_count