作者：龔唐杰，愛(ài)可生 DBA 團(tuán)隊(duì)成員，主要負(fù)責(zé) MySQL 技術(shù)支持，擅長(zhǎng) MySQL、PG、國(guó)產(chǎn)數(shù)據(jù)庫(kù)。

1問(wèn)題現(xiàn)象

一臺(tái)從庫(kù)服務(wù)器的內(nèi)存使用率持續(xù)上升，最終導(dǎo)致 MySQL 服務(wù)被 kill 了。

內(nèi)存監(jiān)控視圖如下：

圖片

內(nèi)存使用率 92.76%

從圖中可以看出，在 00:00 左右觸發(fā)了 kill，然后又被 mysqld_safe 進(jìn)程拉起，然后內(nèi)存又會(huì)持續(xù)上升。

2排查過(guò)程

基本信息

• 數(shù)據(jù)庫(kù)版本：MySQL 5.7.32
• 操作系統(tǒng)版本：Ubuntu 20.04
• 主機(jī)配置：8C64GB
• innodb_buffer_pool_size：8G

由于用戶(hù)環(huán)境未打開(kāi)內(nèi)存相關(guān)的監(jiān)控，所以在 my.cnf 配置文件中配置如下：

performance-schema-instrument = 'memory/% = COUNTED'

打開(kāi)內(nèi)存監(jiān)控等待運(yùn)行一段時(shí)間后，相關(guān)視圖查詢(xún)?nèi)缦拢?/p>

圖片

圖片

從上述截圖可以看到，MySQL 的 buffer pool 大小分配正常，但是 memory/sql/sp_head::main_mem_root 占用了 8GB 內(nèi)存。

查看 源代碼[1] 的介紹：

圖片

sp_head：sp_head represents one instance of a stored program.It might be of any type (stored procedure, function, trigger, event).

根據(jù)源碼的描述可知，sp_head 表示一個(gè)存儲(chǔ)程序的實(shí)例，該實(shí)例可能是存儲(chǔ)過(guò)程、函數(shù)、觸發(fā)器或者定時(shí)任務(wù)。

查詢(xún)當(dāng)前環(huán)境存儲(chǔ)過(guò)程與觸發(fā)器數(shù)量：

圖片

圖片

當(dāng)前環(huán)境存在大量的觸發(fā)器與存儲(chǔ)過(guò)程。

查詢(xún) MySQL 相關(guān) bug[2]，這里面提到一句話(huà)：

圖片

Tried to tweak table_open_cache_instances to affect this?

查詢(xún)此參數(shù)描述：

圖片

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.

根據(jù)官方的解釋可以了解到，如果有許多大的觸發(fā)器，參數(shù) table_open_cache_instances 的默認(rèn)值可能會(huì)造成內(nèi)存使用過(guò)多。

比如 table_open_cache_instances 設(shè)置為 16，那么表緩存會(huì)劃分為 16 個(gè) table instance。當(dāng)并發(fā)訪問(wèn)大時(shí)，最多的情況下一個(gè)表的緩存信息會(huì)出現(xiàn)在每一個(gè) table instance

再有每次將表信息放入表緩存時(shí)，所有關(guān)聯(lián)的觸發(fā)器都被放入 memory/sql/sp_head::main_mem_root 中，table_open_cache_instances 設(shè)置的越大其所占內(nèi)存也就越大，以及存儲(chǔ)過(guò)程也會(huì)消耗更多的內(nèi)存，所以導(dǎo)致內(nèi)存一直上升最終導(dǎo)致 OOM。

下面簡(jiǎn)單驗(yàn)證一下觸發(fā)器對(duì)內(nèi)存的影響。

當(dāng) table_open_cache_instances 為 8 時(shí)：

#清空緩存

mysql> flush tables;
Query OK, 0 rows affected (0.00 sec)

[root@test ~]# cat test.sh
for i in `seq 1 1 8`
do
mysql -uroot -p test -e "select * from test;"
done

[root@test ~]# sh test.sh

mysql> 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_name='memory/sql/sp_head::main_mem_root';
+---------------+
| current_alloc |
+---------------+
| 119.61 KiB |
+---------------+
1 row in set (0.00 sec)

在該表上創(chuàng)建一個(gè)觸發(fā)器。

mysql> \d|
mysql> CREATE TRIGGER trigger_test BEFORE INSERT ON test FOR EACH ROW BEGIN SIGNAL SQLSTATE '45000' SET message_text='Very long string. MySQL stores table descriptors in a special memory buffer, calle
'> at holds how many table descriptors MySQL should store in the cache and table_open_cache_instances t
'> hat stores the number of the table cache instances. So with default values of table_open_cache=4000
'> and table_open_cache_instances=16, you will have 16 independent memory buffers that will store 250 t
'> able descriptors each. These table cache instances could be accessed concurrently, allowing DML to u
'> se cached table descriptors without locking each other. If you use only tables, the table cache doe
'> s 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 u
'> p to 4000 x 4K = 16MB in the cache, 100.000 tables will take up to 390MB that is also not quite a hu
'> ge number for this number of open tables. However, if your tables have triggers, it changes the gam
'> e.'; END|
Query OK, 0 rows affected (0.00 sec)

#清空緩存

mysql> flush tables;
Query OK, 0 rows affected (0.00 sec)

然后訪問(wèn)表，查看緩存。

[root@test ~]# cat test.sh
for i in `seq 1 1 8`
do
mysql -uroot -p test -e "select * from test;"
done

[root@test ~]# sh test.sh

mysql> SELECT current_alloc FROM sys.memory_global_by_current_bytes WHERE event_name='memory/sql/sp_head::main_mem_root';
+---------------+
| current_alloc |
+---------------+
| 438.98 KiB |
+---------------+
1 row in set (0.00 sec)

可以發(fā)現(xiàn) memory/sql/sp_head::main_mem_root 明顯增長(zhǎng)較大。如果有很多大的觸發(fā)器，那么所占內(nèi)存就不可忽視（現(xiàn)場(chǎng)環(huán)境觸發(fā)器里面很多是調(diào)用了存儲(chǔ)過(guò)程）。

當(dāng) table_open_cache_instances 為 1 時(shí)：

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_name='memory/sql/sp_head::main_mem_root';
+---------------+
| current_alloc |
+---------------+
| 119.61 KiB |
+---------------+
1 row in set (0.00 sec)

mysql> #訪問(wèn)表

mysql> system sh test.sh

mysql> SELECT current_alloc FROM sys.memory_global_by_current_bytes WHERE event_name='memory/sql/sp_head::main_mem_root';
+---------------+
| current_alloc |
+---------------+
| 159.53 KiB |
+---------------+
1 row in set (0.00 sec)

可以發(fā)現(xiàn) memory/sql/sp_head::main_mem_root 所占內(nèi)存增長(zhǎng)較小。

由于大量觸發(fā)器會(huì)導(dǎo)致表緩存和 memory/sql/sp_head::main_mem_root 占用更多的內(nèi)存，根據(jù)實(shí)際環(huán)境，嘗試把該從庫(kù)的 table_open_cache_instances 修改為 1 后觀察情況。

圖片

可以看到內(nèi)存值趨于穩(wěn)定，未再次出現(xiàn)內(nèi)存使用率異常的問(wèn)題。

3總結(jié)

1. MySQL 中不推薦使用大量的觸發(fā)器以及復(fù)雜的存儲(chǔ)過(guò)程。
2. table_open_cache_instances 設(shè)置為 1 時(shí)，在高并發(fā)下會(huì)影響 SQL 的執(zhí)行效率。本案例的從庫(kù)并發(fā)量不高，其他場(chǎng)景請(qǐng)根據(jù)實(shí)際情況進(jìn)行調(diào)整。
3. 觸發(fā)器越多會(huì)導(dǎo)致 memory/sql/sp_head::main_mem_root 占用的內(nèi)存越大，存儲(chǔ)過(guò)程所使用的內(nèi)存也會(huì)越大。
4. 本文只是給出了解決內(nèi)存溢出的一個(gè)方向，具體的底層原理請(qǐng)自行探索。

參考資料

[1]sp_head: https://dev.mysql.com/doc/dev/mysql-server/latest/classsp__head.html#details

[2]86821: https://bugs.mysql.com/bug.php?id=86821

本文關(guān)鍵字：#MySQL# #內(nèi)存# #觸發(fā)器# #OOM#

關(guān)于 SQLE

SQLE 是一款全方位的 SQL 質(zhì)量管理平臺(tái)，覆蓋開(kāi)發(fā)至生產(chǎn)環(huán)境的 SQL 審核和管理。支持主流的開(kāi)源、商業(yè)、國(guó)產(chǎn)數(shù)據(jù)庫(kù)，為開(kāi)發(fā)和運(yùn)維提供流程自動(dòng)化能力，提升上線效率，提高數(shù)據(jù)質(zhì)量。