MySQL全文索引觸發(fā)OOM案例分析
引言
業(yè)務(wù)監(jiān)控告警內(nèi)存不足，筆者進(jìn)行了全面系統(tǒng)的故障分析并給出解決方案。
MySQL 版本 5.7.34
故障現(xiàn)象
某業(yè)務(wù)監(jiān)控報(bào)警內(nèi)存不足，發(fā)現(xiàn) mysqld 進(jìn)程由于內(nèi)存不足被 kill 自動(dòng)重啟了。
[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
故障分析
機(jī)器總內(nèi)存 8G，還有其他應(yīng)用占用了少許內(nèi)存。盡管 MySQL 重啟了，使用內(nèi)存依然很高，內(nèi)存監(jiān)控?cái)?shù)據(jù)如下：
有效內(nèi)存并不是一點(diǎn)一點(diǎn)的減少，而是突然下降的。內(nèi)存監(jiān)控?cái)?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
通過(guò) 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)存，猜測(cè)是有什么操作比較吃?xún)?nèi)存，操作完后 MySQL 釋放了內(nèi)存。但是 Tcmalloc 并沒(méi)有將內(nèi)存釋放給操作系統(tǒng)，那到底是什么操作比較吃?xún)?nèi)存呢？分析相關(guān)時(shí)間段慢 SQL，發(fā)現(xiàn)有一個(gè)使用全文索引 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 自動(dòng)重啟前后，和內(nèi)存監(jiān)控?cái)?shù)據(jù)比較吻合（9:53 執(zhí)行，9:54 機(jī)器內(nèi)存不足 MySQL 被 OOM；9:56 執(zhí)行后，10:00:54 采集降低至 56885248 字節(jié)）。這個(gè)環(huán)境還有一個(gè)從庫(kù)，從庫(kù)未承載業(yè)務(wù)，將 SQL 拿到從庫(kù)執(zhí)行，觀(guān)察 MySQL 內(nèi)存使用變化，開(kāi) 2 個(gè)窗口，1 個(gè)窗口執(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)另一個(gè)窗口觀(guān)察 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可以觀(guān)察到 SQL 執(zhí)行過(guò)程中，內(nèi)存不斷上漲，SQL 執(zhí)行完后內(nèi)存從 3453980 KB 漲到 5412200KB，但是 Tcmalloc 并沒(méi)有將內(nèi)存釋放給操作系統(tǒng)。
到目前為止，總算定位到了問(wèn)題，MySQL 并不擅長(zhǎng)全文索引，可以交給 ElasticSearch 等數(shù)據(jù)庫(kù)去做，那在業(yè)務(wù)不調(diào)整情況下，怎么解決問(wèn)題呢？不妨換 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í)行完后會(huì)釋放內(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é)
線(xiàn)上 MySQL 都是使用 Tcmalloc 內(nèi)存分配器一直很穩(wěn)定，并未出現(xiàn)服務(wù)器內(nèi)存不足問(wèn)題。本次出現(xiàn)服務(wù)器內(nèi)存不足，是因?yàn)槭褂昧巳乃饕@種極少使用的場(chǎng)景觸發(fā)，換成 Jemalloc 后，內(nèi)存使用整體上得到了控制。
以上就是MySQL全文索引觸發(fā)OOM案例分析的詳細(xì)內(nèi)容，更多關(guān)于MySQL全文索引觸發(fā)OOM的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！
相關(guān)文章
MySQL InnoDB 鎖的相關(guān)總結(jié)
這篇文章主要介紹了MySQL InnoDB 鎖的相關(guān)知識(shí)總結(jié)，幫助大家更好的理解和使用MySQL，感興趣的朋友可以了解下2021-02-02
MySQL前綴索引導(dǎo)致的慢查詢(xún)分析總結(jié)
前綴索引，并不是一個(gè)萬(wàn)能藥，他的確可以幫助我們對(duì)一個(gè)寫(xiě)過(guò)長(zhǎng)的字段上建立索引。但也會(huì)導(dǎo)致排序（order by ，group by）查詢(xún)上都是無(wú)法使用前綴索引的2013-05-05
MySQL中二進(jìn)制與重做日志文件的基本概念學(xué)習(xí)教程
這篇文章主要介紹了MySQL中二進(jìn)制日志文件與重做日志文件的基本概念學(xué)習(xí)教程,講到了一些重做日志與二進(jìn)制日志的區(qū)別,需要的朋友可以參考下2015-11-11
SQL實(shí)現(xiàn)Excel的10個(gè)常用功能的示例詳解
SQL，數(shù)據(jù)分析崗的必備技能，你可以不懂Python，R，不懂可視化，不懂機(jī)器學(xué)習(xí)。但SQL，你必須懂。本文為大家總結(jié)了SQL實(shí)現(xiàn)Excel的10個(gè)常用功能的示例代碼，感興趣的可以了解一下2022-07-07
Django連接本地mysql數(shù)據(jù)庫(kù)(pycharm)的步驟
這篇文章主要介紹了Django連接本地mysql數(shù)據(jù)庫(kù)(pycharm)的步驟,本文分步驟給大家介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值，需要的朋友可以參考下2020-09-09
這篇文章主要介紹了MySQL慢查詢(xún)SQL優(yōu)化方式,具有很好的參考價(jià)值,希望對(duì)大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教2025-04-04
MySQL授權(quán)命令grant的使用方法小結(jié)
這篇文章主要介紹了MySQL授權(quán)命令grant的使用方法,本文實(shí)例，運(yùn)行于?MySQL?5.0?及以上版本，介紹了MySQL?賦予用戶(hù)權(quán)限命令的簡(jiǎn)單格式，本文給大家介紹的非常詳細(xì)，需要的朋友參考下吧2021-12-12
這篇文章主要介紹了mysql獲取group by總記錄行數(shù)的方法,主要通過(guò)group by SQL_CALC_FOUND_ROWS語(yǔ)句來(lái)實(shí)現(xiàn)該功能,具有一定的實(shí)用價(jià)值,需要的朋友可以參考下2014-10-10
使用MySQL的LAST_INSERT_ID來(lái)確定各分表的唯一ID值
MySQL數(shù)據(jù)表結(jié)構(gòu)中，一般情況下，都會(huì)定義一個(gè)具有‘AUTO_INCREMENT’擴(kuò)展屬性的‘ID’字段，以確保數(shù)據(jù)表的每一條記錄都可以用這個(gè)ID唯一確定2011-08-08
mysql搭建主從復(fù)制的實(shí)現(xiàn)步驟
在MySQL集群中,主庫(kù)更新會(huì)同步到從庫(kù),但從庫(kù)更新不同步到主庫(kù),主從復(fù)制能分?jǐn)倝毫?本文就來(lái)介紹一下mysql搭建主從復(fù)制的實(shí)現(xiàn)步驟,感興趣的可以了解一下2024-11-11