网站网上推广,移动互联网服务管理中心官网,WordPress文章查询插件,新乡个人网站建设由触发器导致得 OOM 案例分析过程和解决方式。 作者#xff1a;龚唐杰#xff0c;爱可生 DBA 团队成员#xff0c;主要负责 MySQL 技术支持#xff0c;擅长 MySQL、PG、国产数据库。 爱可生开源社区出品#xff0c;原创内容未经授权不得随意使用#xff0c;转载请联系小编…由触发器导致得 OOM 案例分析过程和解决方式。 作者龚唐杰爱可生 DBA 团队成员主要负责 MySQL 技术支持擅长 MySQL、PG、国产数据库。 爱可生开源社区出品原创内容未经授权不得随意使用转载请联系小编并注明来源。 本文约 1500 字预计阅读需要 5 分钟。 问题现象
一台从库服务器的内存使用率持续上升最终导致 MySQL 服务被 kill 了。
内存监控视图如下 从图中可以看出在 00:00 左右触发了 kill然后又被 mysqld_safe 进程拉起然后内存又会持续上升。
排查过程
基本信息
数据库版本MySQL 5.7.32操作系统版本Ubuntu 20.04主机配置8C64GBinnodb_buffer_pool_size8G
由于用户环境未打开内存相关的监控所以在 my.cnf 配置文件中配置如下
performance-schema-instrument memory/% COUNTED
打开内存监控等待运行一段时间后相关视图查询如下 从上述截图可以看到MySQL 的 buffer pool 大小分配正常但是 memory/sql/sp_head::main_mem_root 占用了 8GB 内存。
查看 源代码 的介绍 sp_headsp_head represents one instance of a stored program.It might be of any type (stored procedure, function, trigger, event).
根据源码的描述可知sp_head 表示一个存储程序的实例该实例可能是存储过程、函数、触发器或者定时任务。
查询当前环境存储过程与触发器数量 当前环境存在大量的触发器与存储过程。
查询 MySQL 相关 bug这里面提到一句话 Tried to tweak table_open_cache_instances to affect this?
查询此参数描述 A value of 8 or 16 is recommended on systems that routinely use 16 or more cores. However, if you have many large triggers on your tables that cause a high memory load, the default setting for table_open_cache_instances might lead to excessive memory usage. In that situation, it can be helpful to set table_open_cache_instances to 1 in order to restrict memory usage.
根据官方的解释可以了解到如果有许多大的触发器参数 table_open_cache_instances 的默认至可能会造成内存使用过多。
比如 table_open_cache_instances 设置为 16那么表缓存会划分为 16 个 table instance*。当并发访问大时最多的情况下一个表的缓存信息会出现在每一个 *table instance 里面。
再由于每次将表信息放入表缓存时所有关联的触发器都被放入 memory/sql/sp_head::main_mem_root 中table_open_cache_instances 设置的越大其所占内存也就越大以及存储过程也会消耗更多的内存所以导致内存一直上升最终导致 OOM。
下面简单验证一下触发器对内存的影响。
当 table_open_cache_instances 为 8 时
#清空缓存mysql flush tables;
Query OK, 0 rows affected (0.00 sec)[roottest ~]# cat test.sh
for i in seq 1 1 8
do
mysql -uroot -p test -e select * from test;
done[roottest ~]# sh test.shmysql show variables like %table_open_cache_instances%;
-----------------------------------
| Variable_name | Value |
-----------------------------------
| table_open_cache_instances | 8 |
-----------------------------------
1 row in set (0.00 sec)mysql SELECT current_alloc FROM sys.memory_global_by_current_bytes WHERE event_namememory/sql/sp_head::main_mem_root;
---------------
| current_alloc |
---------------
| 119.61 KiB |
---------------
1 row in set (0.00 sec)
在该表上创建一个触发器。
mysql \d|
mysql CREATE TRIGGER trigger_test BEFORE INSERT ON test FOR EACH ROW BEGIN SIGNAL SQLSTATE 45000 SET message_textVery long string. MySQL stores table descriptors in a special memory buffer, calleat holds how many table descriptors MySQL should store in the cache and table_open_cache_instances that stores the number of the table cache instances. So with default values of table_open_cache4000and table_open_cache_instances16, you will have 16 independent memory buffers that will store 250 table descriptors each. These table cache instances could be accessed concurrently, allowing DML to use cached table descriptors without locking each other. If you use only tables, the table cache does not require a lot of memory, because descriptors are lightweight, and even if you significantly increased the value of table_open_cache, it would not be so high. For example, 4000 tables will take up to 4000 x 4K 16MB in the cache, 100.000 tables will take up to 390MB that is also not quite a huge number for this number of open tables. However, if your tables have triggers, it changes the game.; END|
Query OK, 0 rows affected (0.00 sec)#清空缓存mysql flush tables;
Query OK, 0 rows affected (0.00 sec)
然后访问表查看缓存。
[roottest ~]# cat test.sh
for i in seq 1 1 8
do
mysql -uroot -p test -e select * from test;
done[roottest ~]# sh test.shmysql SELECT current_alloc FROM sys.memory_global_by_current_bytes WHERE event_namememory/sql/sp_head::main_mem_root;
---------------
| current_alloc |
---------------
| 438.98 KiB |
---------------
1 row in set (0.00 sec)
可以发现 memory/sql/sp_head::main_mem_root* 明显增长较大。如果有很多大的触发器那么所占内存就不可忽视现场环境触发器里面很多是调用了存储过程。
当 table_open_cache_instances 为 1 时
mysql flush tables;
Query OK, 0 rows affected (0.00 sec)mysql show variables like %table_open_cache_instances%;
-----------------------------------
| Variable_name | Value |
-----------------------------------
| table_open_cache_instances | 1 |
-----------------------------------
1 row in set (0.00 sec)SELECT current_alloc FROM sys.memory_global_by_current_bytes WHERE event_namememory/sql/sp_head::main_mem_root;
---------------
| current_alloc |
---------------
| 119.61 KiB |
---------------
1 row in set (0.00 sec)mysql #访问表mysql system sh test.shmysql SELECT current_alloc FROM sys.memory_global_by_current_bytes WHERE event_namememory/sql/sp_head::main_mem_root;
---------------
| current_alloc |
---------------
| 159.53 KiB |
---------------
1 row in set (0.00 sec)
可以发现 memory/sql/sp_head::main_mem_root 所占内存增长较小。
由于大量触发器会导致表缓存和 memory/sql/sp_head::main_mem_root 占用更多的内存根据实际环境尝试把该从库的 table_open_cache_instances 修改为 1 后观察情况。 可以看到内存值趋于稳定未再次出现内存使用率异常的问题。
总结
MySQL 中不推荐使用大量的触发器以及复杂的存储过程。table_open_cache_instances 设置为 1 时在高并发下会影响 SQL 的执行效率。本案例的从库并发量不高其他场景请根据实际情况进行调整。触发器越多会导致 memory/sql/sp_head::main_mem_root 占用的内存越大存储过程所使用的内存也会越大。本文只是给出了解决内存溢出的一个方向具体的底层原理请自行探索。
先清空缓存再访问表查看缓存
更多技术文章请访问https://opensource.actionsky.com/
关于 SQLE
SQLE 是一款全方位的 SQL 质量管理平台覆盖开发至生产环境的 SQL 审核和管理。支持主流的开源、商业、国产数据库为开发和运维提供流程自动化能力提升上线效率提高数据质量。
SQLE 获取
类型地址版本库https://github.com/actiontech/sqle文档https://actiontech.github.io/sqle-docs/发布信息https://github.com/actiontech/sqle/releases数据审核插件开发文档https://actiontech.github.io/sqle-docs/docs/dev-manual/plugins/howtouse
