问题描述
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
