MySQL 分析查询与来源机器

当前分析针对版本：MariaDB 10.5

线上出现报错：can’t create more than max_prepared_stmt_count statements。造成这个错误的直接原因就是同时开启了 prepare 句柄太多导致的，所以比较直接的方式是调大参数，首先查看设置的值：

show global variables like 'max_prepared_stmt_count';

如果这个值比较小，我们需要手动调大：

set global max_prepared_stmt_count=1048576;

或者也可以修改配置文件，不过需要重启。

配置好之后我们注意观察下面几个参数的变化：

show global status like 'com_stmt%';

主要涉及下面的 3 个参数：

Com_stmt_close 表示 prepare 语句关闭的次数
Com_stmt_execute 表示 prepare 语句执行的次数
Com_stmt_prepare 表示语句 prepare 的次数

注意这些参数，正常来说 3 者应该差不多，如果打开的和关闭的差距越来越大，那说明可能存在很多 statement 没有关闭，如果一直增长直到出现上面的报错，那说明肯定有程序存在 bug，忘记的关闭 statement 最终导致超出了限制。

如果程序比较少，应该很容易排查，但是如果操作当前 MySQL 实例的程序比较多，就需要从数据库角度来分析了。

第一种方法可以开启日志追踪打印所有 SQL 执行的情况：

set global general_log = 'ON';

这样所有的 SQL 执行情况都会打印出来，我们需要从日志中找到没有 close 的 prepare 从而定位问题，但是如果 SQL 特别多同时日志中有没有客户端来源，看日志会非常麻烦，不容易分析问题。

然后第二种方法就是开启性能追踪，首先需要确认性能追踪模式是否开启：

SHOW VARIABLES LIKE 'performance_schema';

如果没有开启，这个参数是无法直接动态开启的，因为这个是只读的变量，必须通过配置文件修改：

[mysqld]
performance_schema=ON

保存配置后，确定在不影响生产环境的前提下重启数据库：

systemctl restart mariadb

重启后我们需要开启下面这些性能追踪相关的表：

use performance_schema;
show tables like '%statement%';

我们主要用到下面这几个表：

events_statements_current 当前正在执行查询的表
events_statements_history 最近的10个完成的查询，通过 performance_schema_events_statements_history_size 可以调整大小，限制在 1024 之内
events_statements_history_long 最近的1万个完成的查询，具体通过参数 performance_schema_events_statements_history_long_size 配置大小，限制在 1048576 之内

表结构参考：https://mariadb.com/kb/en/performance-schema-events_statements_current-table/

我们需要确认 setup_instruments 和 setup_consumers 中是否开启采集：

select * from setup_instruments where name like "statement/%";
-- 如果 enabled 是 NO 则可以开启
update setup_instruments set enabled="YES" where name like "statement/%";

确认 setup_consumers 中是否开启：

select * from setup_consumers;
-- 开启参数
update setup_consumers set enabled='YES' where name in ('events_statements_current', 'events_statements_history', 'events_statements_history_long');

表结构参考：https://mariadb.com/kb/en/performance-schema-setup_consumers-table/

开启后如果有查询我们在对应的事件表中应该可以查询到结果，也可以编写一个程序执行 prepare 单不要关闭，此时我们就可以通过 events_statements_current 表查询到具体的 SQL。

那么最后我们应该怎么找到是哪个客户端发起的查询呢？我们注意到上面的查询事件表中都有 THREAD_ID 字段，如果想看到具体正在进行的查询可以查询 processlist 表，那么这时候 threads 表就可以将 THREAD_ID 和 PROCESSLIST_ID 给关联起来，也就是说我们此时应该通过 threads 表通过 THREAD_ID 查询得到 PROCESSLIST_ID。

threads 表结构参考：https://mariadb.com/kb/en/performance-schema-threads-table/

然后我们查询具体的任务来源：

show processlist;
select * from information_schema.processlist where ID=<PROCESSLIST_ID>;

这样就可以找到具体查询来源的客户端了，processlist 表结构参考：https://mariadb.com/kb/en/information-schema-processlist-table/

我们可以通过 join 实现组合查询，快速找到出现问题的 SQL 来源，然后就可以基于机器和端口号进一步找到客户端进程的具体位置，从而方面定位问题。

Reference:

monchickey

2023/08/28