sysbench压测时遇到max_prepared_stmt_count相关错误

参考文章：https://zhuanlan.zhihu.com/p/67188414

问题现象：报错 FATAL: MySQL error: 1461 "Can't create more than max_prepared_stmt_count statements (current value: 16382)

sysbench压测异常终止

问题引入

在做压力测试的时候，我使用了sysbench 1.1.0-faaff4f版本，测试命令如下：

sysbench oltp_read_only.lua --mysql-host=*.*.*.* --mysql-port=3306 --mysql-user=root --mysql-password=1234 --tables=10 --table-size=10000000 --time=600 --report-interval=10 --threads=1024 prepare

问题主要出现在这个--threads身上，我的压测线程数增长是从 1，2，4，8，16，32，64，128，256，512，1024，在1024前压测都是没有问题了，但当线程数增长到了1024后，sysbench 报错了。

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

这个问题很好解决，上网一查就知道怎么解决，登录mysql，调整这个变量值的大小即可。

mysql> show global status like 'com_stmt%';

查看如下3个参数值：

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（取值范围：0 - 1048576，默认16382）即可解决

mysql> set global max_prepared_stmt_count=500000;

那这里就容易再次引发一个问题，我们设置max_prepared_stmt_count为50万，但是我们其实现在是知其然不知其所以然，max_prepared_stmt_count是干什么的，为什么会超过默认值这个才是我们真正应该关心的。

max_prepared_stmt_count基本介绍

这个参数是MySQL的一个基本参数，其是用来限制一个session内最多可以有多少条预编译语句，什么是sql的预编译，下面这篇文章讲得很好，网上也有很多参考，大家可以直接查。

https://www.cnblogs.com/geaozhang/p/9891338.html

那为什么我们会超过max_prepared_stmt_count的默认大小16382呢？下面我们就先来介绍一下sysbench工具中关于压测数据库的lua脚本。

sysbench 压测数据库的lua脚本个人理解

sysbench官方默认帮我们配置了默认压测数据库的脚本：

这几个脚本里面的功能我们简单说明一下：

oltp_common.lua 脚本是提供给其他脚本如oltp_read_only.lua调用的，是基本的一系列函数。oltp_read_only.lua 主要的工作根据压测需求来调用oltp_common.lua中的函数。

sysbench 的基本工作流程：

1. prepare:
   sysbench oltp_read_only.lua --mysql-host=x.x.x.x --mysql-port=3306 --mysql-user=root --mysql-password=password --tables=10 --table-size=10000000 --threads=1024 prepare
   这个语句的基本作用就是调用建表语句，建立十张表，每张表大小1千万行。
2. run:
   sysbench oltp_read_only.lua --mysql-host=10.191.1.235 --mysql-port=6446 --mysql-user=root --mysql-password=vm000 --tables=10 --table-size=10000000 --threads=1024 --time=600 --report_interval=10 run
   这个的过程实际是：建立数据库连接-->预编译SQL语句-->执行预编译语句
3. cleanup:
   sysbench oltp_read_only.lua --mysql-host=x.x.x.x --mysql-port=3306 --mysql-user=root --mysql-password=password --tables=10 --table-size=10000000 --threads=1024 cleanup
   这里的实际过程是：直接将数据库的表drop掉。

从第二步run 我们就可以知道我们为什么会出现超过max_prepared_stmt_count的默认大小16382的报错了。我们先来看一下oltp_read_only.lua代码内容：

这里面函数 function prepare_statements()中的两个if判断结果均为true，这个是因为在默认情况下oltp_common.lua中已经指定skip_trx=false, range_selects=true

oltp_common.lua

所以这里一共需要调用7个预编译函数：

prepare_point_selects()

prepare_begin()

prepare_commit()

prepare_simple_ranges()

prepare_sum_ranges()

prepare_order_ranges()

prepare_distinct_ranges()

因为我们一共有10张表，相对于每一张表都需要执行7个预编译语句，所以我们可以计算：

在512个线程并发下，每一个线程我们可以视之为一个用户，每一个用户需要在每一张表上预编译7条SQL语句，所以一共prepare的语句是：

Com_stmt_prepare = 512 * 10 * 7 = 35840 > 16382(max_prepared_stmt_count statements 默认16382)

所以就出现上面的错误：

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

这个是一个大概的推测值，因为实际执行时还会有出入。这个数字可以方便我们在出现上面问题后，设置max_prepared_stmt_count_statements的值有一个参考的方向，而不是盲目地设置成最大。

以上仅是个人的见解，如果有什么出错的，请大佬们指正，谢谢。