MySQL全文索引觸發(fā)OOM案例分析
引言
業(yè)務(wù)監(jiān)控告警內(nèi)存不足，筆者進(jìn)行了全面系統(tǒng)的故障分析并給出解決方案。
MySQL 版本 5.7.34
故障現(xiàn)象
某業(yè)務(wù)監(jiān)控報警內(nèi)存不足，發(fā)現(xiàn) mysqld 進(jìn)程由于內(nèi)存不足被 kill 自動重啟了。
[root@xxxxxx ~]# ps -ef|grep mysqld root 17117 62542 0 20:26 pts/1 00:00:00 grep --color=auto mysqld mysql 27799 1 7 09:54 ? 00:48:32 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid [root@xxxxxx ~]# # 操作系統(tǒng)日志記錄 MySQL 被 OOM Dec 8 09:54:42 xxxxxx kernel: Out of memory: Kill process 22554 (mysqld) score 934 or sacrifice child Dec 8 09:54:42 xxxxxx kernel: Killed process 22554 (mysqld), UID 27, total-vm:11223284kB, anon-rss:7444620kB, file-rss:0kB, shmem-rss:0kB
故障分析
機器總內(nèi)存 8G，還有其他應(yīng)用占用了少許內(nèi)存。盡管 MySQL 重啟了，使用內(nèi)存依然很高，內(nèi)存監(jiān)控數(shù)據(jù)如下：
有效內(nèi)存并不是一點一點的減少，而是突然下降的。內(nèi)存監(jiān)控數(shù)據(jù)是 5 分鐘采集一次，MySQL 在 09:54 重啟后，09:55:54 采樣有效內(nèi)存是 2869899264 字節(jié)，10:00:54 采集降低至 56885248 字節(jié)。
2023-12-08 10:20:54
963796992
2023-12-08 10:15:54
93224960
2023-12-08 10:10:54
111407104
2023-12-08 10:05:54
113987584
2023-12-08 10:00:54
56885248
2023-12-08 09:55:54
2869899264
InnoDB Buffer 分配的內(nèi)存為 1G：
| innodb_buffer_pool_size | 1073741824 |
top 查看 MySQL 當(dāng)前使用了 6GB 內(nèi)存：
27799 mysql 20 0 8888376 6.1g 6120 S 26.2 80.5 30:19.01 mysqld
通過 gdb 調(diào)用 malloc_stats() 函數(shù)查看內(nèi)存分配情況：
gdb -ex "call (void) malloc_stats()" --batch -p $(pidof mysqld)
查看 MySQL 日志：
MALLOC: 853070392 ( 813.6 MiB) Bytes in use by application MALLOC: + 6369394688 ( 6074.3 MiB) Bytes in page heap freelist MALLOC: + 9771872 ( 9.3 MiB) Bytes in central cache freelist MALLOC: + 863232 ( 0.8 MiB) Bytes in transfer cache freelist MALLOC: + 25216616 ( 24.0 MiB) Bytes in thread cache freelists MALLOC: + 25559040 ( 24.4 MiB) Bytes in malloc metadata MALLOC: ------------ MALLOC: = 7283875840 ( 6946.4 MiB) Actual memory used (physical + swap) MALLOC: + 329924608 ( 314.6 MiB) Bytes released to OS (aka unmapped) MALLOC: ------------ MALLOC: = 7613800448 ( 7261.1 MiB) Virtual address space used MALLOC: MALLOC: 12564 Spans in use MALLOC: 52 Thread heaps in use MALLOC: 8192 Tcmalloc page size ------------------------------------------------ Call ReleaseFreeMemory() to release freelist memory to the OS (via madvise()). Bytes released to the OS take up virtual address space but no physical memory.
MySQL 當(dāng)前使用 Tcmalloc 內(nèi)存分配器，Bytes in page heap freelist 使用了將近 6GB 內(nèi)存，猜測是有什么操作比較吃內(nèi)存，操作完后 MySQL 釋放了內(nèi)存。但是 Tcmalloc 并沒有將內(nèi)存釋放給操作系統(tǒng)，那到底是什么操作比較吃內(nèi)存呢？分析相關(guān)時間段慢 SQL，發(fā)現(xiàn)有一個使用全文索引 SQL 比較可疑：
# Time: 2023-12-08T01:52:23.084854Z
# User@Host: xxxxxx @ [x.x.x.x] Id: 259892877
# Query_time: 1.436714 Lock_time: 0.000049 Rows_sent: 1 Rows_examined: 0
SET timestamp=1702000343;
SELECT count(*) FROM `xx` inner JOIN (select id from xx_content where MATCH(content) AGAINST('\"Elasticsearch Cluster in 1 state\"' IN BOOLEAN MODE)) al ON xx.id = al.id WHERE (xx.handle_status in ('pending','processing','completed')) AND `xx`.`sub_type` = 1;
# Time: 2023-12-08T01:52:24.540847Z
# User@Host: xxxxxx @ [x.x.x.x] Id: 259892879
# Query_time: 1.454352 Lock_time: 0.000052 Rows_sent: 0 Rows_examined: 0
SET timestamp=1702000344;
SELECT xx.*,SUBSTRING(xx.content, 1, 1024) as content,SUBSTRING(xx.sub_content, 1, 1024) as sub_content FROM `xx` inner JOIN (select id from xx_content where MATCH(content) AGAINST('\"Elasticsearch Cluster in 1 state\"' IN BOOLEAN MODE)) al ON xx.id = al.id WHERE (xx.handle_status in ('pending','processing','completed')) AND `xx`.`sub_type` = 1 ORDER BY xx.sub_time DESC LIMIT 50;
# Time: 2023-12-08T01:53:26.546353Z
# User@Host: xxxxxx @ [x.x.x.x] Id: 259893335
# Query_time: 44.198100 Lock_time: 0.000041 Rows_sent: 1 Rows_examined: 48
SET timestamp=1702000406;
SELECT count(*) FROM `xx` inner JOIN (select id from xx_content where MATCH(content) AGAINST('\"Elasticsearch Cluster in \"' IN BOOLEAN MODE)) al ON xx.id = al.id WHERE (xx.handle_status in ('pending','processing','completed')) AND `xx`.`sub_type` = 1;
73
# Time: 2023-12-08T01:56:35.790820Z
# User@Host: xxxxxx @ [x.x.x.x] Id: 1671
# Query_time: 29.259303 Lock_time: 0.000042 Rows_sent: 1 Rows_examined: 48
SET timestamp=1702000595;
SELECT count(*) FROM `xx` inner JOIN (select id from xx_content where MATCH(content) AGAINST('\"Elasticsearch Cluster in \"' IN BOOLEAN MODE)) al ON xx.id = al.id WHERE (xx.handle_status = 'pending') AND `xx`.`sub_type` = 1;
# Time: 2023-12-08T01:56:36.350983Z
# User@Host: xxxxxx @ [x.x.x.x] Id: 1672
# Query_time: 28.870504 Lock_time: 0.000050 Rows_sent: 1 Rows_examined: 48
SET timestamp=1702000596;
SELECT count(*) FROM `xx` inner JOIN (select id from xx_content where MATCH(content) AGAINST('\"Elasticsearch Cluster in \"' IN BOOLEAN MODE)) al ON xx.id = al.id WHERE (xx.handle_status in ('pending','processing','completed')) AND `xx`.`sub_type` = 1;表結(jié)構(gòu)及數(shù)據(jù)量如下：
root@3306 xxxxxx> show create table xx_content\G
*************************** 1. row ***************************
Table: xx_content
Create Table: CREATE TABLE `xx_content` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`content` longtext,
PRIMARY KEY (`id`),
FULLTEXT KEY `ngram_content` (`content`) /*!50100 WITH PARSER `ngram` */
) ENGINE=InnoDB AUTO_INCREMENT=100377976 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)
root@3306 xxxxxx> select count(*) from xx_content;
+----------+
| count(*) |
+----------+
| 360215 |
+----------+
1 row in set (0.11 sec)全文索引相關(guān)參數(shù)均采用默認(rèn)配置：
root@3306 (none)> show variables like '%ft%'; +---------------------------------+----------------+ | Variable_name | Value | +---------------------------------+----------------+ | ft_boolean_syntax | + -><()~*:""&| | | ft_max_word_len | 84 | | ft_min_word_len | 4 | | ft_query_expansion_limit | 20 | | ft_stopword_file | (built-in) | | innodb_ft_aux_table | | | innodb_ft_cache_size | 8000000 | | innodb_ft_enable_diag_print | OFF | | innodb_ft_enable_stopword | ON | | innodb_ft_max_token_size | 84 | | innodb_ft_min_token_size | 3 | | innodb_ft_num_word_optimize | 2000 | | innodb_ft_result_cache_limit | 2000000000 | | innodb_ft_server_stopword_table | | | innodb_ft_sort_pll_degree | 2 | | innodb_ft_total_cache_size | 640000000 | | innodb_ft_user_stopword_table | | +---------------------------------+----------------+ 17 rows in set (0.01 sec)
SQL 分別在 9:53 和 9:56 執(zhí)行，正好在 MySQL 自動重啟前后，和內(nèi)存監(jiān)控數(shù)據(jù)比較吻合（9:53 執(zhí)行，9:54 機器內(nèi)存不足 MySQL 被 OOM；9:56 執(zhí)行后，10:00:54 采集降低至 56885248 字節(jié)）。這個環(huán)境還有一個從庫，從庫未承載業(yè)務(wù)，將 SQL 拿到從庫執(zhí)行，觀察 MySQL 內(nèi)存使用變化，開 2 個窗口，1 個窗口執(zhí)行 SQL：
root@3306 xxxxxx> SELECT count(*) FROM `xx` inner JOIN (select id from xx_content where MATCH(content) AGAINST('\"Elasticsearch Cluster in 1 state\"' IN BOOLEAN MODE)) al ON xx.id = al.id WHERE (xx.handle_status in ('pending','processing','completed')) AND `xx`.`sub_type` = 1;
+----------+
| count(*) |
+----------+
| 3 |
+----------+
1 row in set (1 min 9.31 sec)另一個窗口觀察 mysqld 進(jìn)程內(nèi)存使用情況：
[root@xxxxxx ~]# ps aux|grep mysqld|grep -v grep|awk '{print $6}'
3453980
[root@xxxxxx ~]# while true;do ps aux|grep mysqld|grep -v grep|awk '{print $6}';sleep 1;done;
3453980
3453980
3453980
3453980
3453980
3453980
3453980
3453980
3453980
3453980
3453980
3453980
3453980
3453980
3453980
3453980
3453980
3453980
3453980
3453980
3453980
3453980
3453980
3453980
3453980
3453980
3453980
3453980
3453980
3453980
3453980
3453980
3453980
3453980
3453980
3453980
3459064
3617600
3822828
3969212
4128056
4533612
4677028
4756868
4844452
5011176
5070292
5123844
5188556
5263880
5410368
5410368
5412200
5412200
5412200
5412200
5412200
5412200
5412200
5412200
5412200
5412200
5412200
5412200
5412200
5412200
5412200
5412200
5412200
5412200
5412200
5412200
5412200可以觀察到 SQL 執(zhí)行過程中，內(nèi)存不斷上漲，SQL 執(zhí)行完后內(nèi)存從 3453980 KB 漲到 5412200KB，但是 Tcmalloc 并沒有將內(nèi)存釋放給操作系統(tǒng)。
到目前為止，總算定位到了問題，MySQL 并不擅長全文索引，可以交給 ElasticSearch 等數(shù)據(jù)庫去做，那在業(yè)務(wù)不調(diào)整情況下，怎么解決問題呢？不妨換 Jemalloc 內(nèi)存分配器試試：
[root@xxxxxx ~]# yum install -y jemalloc [root@xxxxxx ~]# cat /etc/sysconfig/mysql #LD_PRELOAD=/usr/lib64/libtcmalloc.so LD_PRELOAD=/usr/lib64/libjemalloc.so.1 [root@xxxxxx ~]# systemctl restart mysqld
果然有驚喜，SQL 執(zhí)行完后會釋放內(nèi)存，從 822948KB 漲到 2738040KB，最終回落到 916400KB：
[root@xxxxxx ~]# while true;do ps aux|grep mysqld|grep -v grep|awk '{print $6}';sleep 1;done;
822948
822948
822948
822948
822948
822948
822948
822948
822948
822948
822948
822948
822948
822948
874216
1057240
1273684
1443820
1662924
1873304
2177760
2502488
2738040
1296604
899580
900636
902412
903680
904384
......
914492
914492
915020
915284
915736
916524
916524
916524
916524
916524
916400
916400
916400
916400
916400
916400
916400
916400
916400總結(jié)
線上 MySQL 都是使用 Tcmalloc 內(nèi)存分配器一直很穩(wěn)定，并未出現(xiàn)服務(wù)器內(nèi)存不足問題。本次出現(xiàn)服務(wù)器內(nèi)存不足，是因為使用了全文索引這種極少使用的場景觸發(fā)，換成 Jemalloc 后，內(nèi)存使用整體上得到了控制。
以上就是MySQL全文索引觸發(fā)OOM案例分析的詳細(xì)內(nèi)容，更多關(guān)于MySQL全文索引觸發(fā)OOM的資料請關(guān)注腳本之家其它相關(guān)文章！
相關(guān)文章
MySQL將版本由5.7.37更新到5.7.39的實現(xiàn)方式
這篇文章主要介紹了MySQL將版本由5.7.37更新到5.7.39的實現(xiàn)方式,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教2023-12-12
這篇文章給大家分享8個不得不說的MySQL陷阱，非常不錯，具有參考借鑒價值，需要的朋友參考下吧2018-03-03
MySQL 自動備份與數(shù)據(jù)庫被破壞后的恢復(fù)方法
當(dāng)數(shù)據(jù)庫服務(wù)器建立好以后，我們首先要做的不是考慮要在這個支持?jǐn)?shù)據(jù)庫的服務(wù)器運行哪些受MySQL提攜的程序，而是當(dāng)數(shù)據(jù)庫遭到破壞后，怎樣安然恢復(fù)到最后一次正常的狀態(tài)，使得數(shù)據(jù)的損失達(dá)到最小。2010-03-03
WIN10下cmd如何查看編碼方式,命令行窗口修改UTF-8編碼
這篇文章主要介紹了WIN10下cmd如何查看編碼方式,命令行窗口修改UTF-8編碼,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教2023-09-09
windows server2016安裝MySQL5.7.19解壓縮版教程詳解
本篇文章給大家記錄了MySQL 5.7.19 winx64解壓縮版安裝教程，非常不錯，具有參考借鑒價值，需要的的朋友參考下吧2017-08-08
觸發(fā)器，就是一種特殊的存儲過程。觸發(fā)器和存儲過程一樣是一個能夠完成特定功能、存儲在數(shù)據(jù)庫服務(wù)器上的SQL片段。本文將通過簡單的實力介紹一下觸發(fā)器的操作，需要的可以參考一下2023-02-02
這篇文章主要為大家詳細(xì)介紹了mysql5.7.17 zip 解壓安裝詳細(xì)過程，具有一定的參考價值，感興趣的小伙伴們可以參考一下2017-02-02
這篇文章主要介紹了Mysql判斷表字段或索引是否存在的相關(guān)資料,非常不錯具有參考借鑒價值，需要的朋友可以參考下2016-06-06
阿里云服務(wù)器安裝Mysql數(shù)據(jù)庫的詳細(xì)教程
這篇文章主要介紹了阿里云服務(wù)器安裝Mysql數(shù)據(jù)庫的詳細(xì)教程,本文給大家介紹的非常詳細(xì)，對大家的學(xué)習(xí)或工作具有一定的參考借鑒價值，需要的朋友可以參考下2020-11-11
Windows 8.1下MySQL5.7 忘記root 密碼的解決方法
最近學(xué)習(xí)碰到了一件挺令人尷尬的事情，我把MySQL的密碼給忘記了，所以MySQL登錄不進(jìn)去。在網(wǎng)上找的解決方案都不靠譜，下面小編給大家分享Windows 8.1下MySQL5.7 忘記root 密碼的解決方法，需要的朋友一起看看吧2017-07-07