分享MySQL生產(chǎn)庫內(nèi)存異常增高的排查過程
近期頻繁收到一個MySQL實例的內(nèi)存使用率高的報警，今天我們花時間排查一下問題出在哪里。
修改performance_schema
因為公司生產(chǎn)環(huán)境使用的阿里云RDS，修改參數(shù)相對方便，performance_schema默認為0，此次修改為1。修改之后提交參數(shù)，數(shù)據(jù)庫會進行重啟，建議在業(yè)務(wù)低峰進行。
打開內(nèi)存監(jiān)控
登錄MySQL數(shù)據(jù)庫，執(zhí)行如下SQL，打開內(nèi)存監(jiān)控。
update performance_schema.setup_instruments set enabled = 'yes' where name like 'memory%';
打開之后驗證一下。
select * from performance_schema.setup_instruments where name like 'memory%innodb%' limit 5;
**注意：**該命令是在線打開內(nèi)存統(tǒng)計，所以只會統(tǒng)計打開后新增的內(nèi)存對象，打開前的內(nèi)存對象不會統(tǒng)計，建議您打開后等待一段時間再執(zhí)行后續(xù)步驟，便于找出內(nèi)存使用高的線程。
查找內(nèi)存消耗
統(tǒng)計事件消耗內(nèi)存
select event_name,
SUM_NUMBER_OF_BYTES_ALLOC
from performance_schema.memory_summary_global_by_event_name
order by SUM_NUMBER_OF_BYTES_ALLOC desc
LIMIT 10;
+---------------------------------------+-------------------------------------+
| event_name | SUM_NUMBER_OF_BYTES_ALLOC |
+---------------------------------------+-------------------------------------+
| memory/sql/Filesort_buffer::sort_keys | 763523904056 |
| memory/memory/HP_PTRS | 118017336096 |
| memory/sql/thd::main_mem_root | 114026214600 |
| memory/mysys/IO_CACHE | 59723548888 |
| memory/sql/QUICK_RANGE_SELECT::alloc | 14381459680 |
| memory/sql/test_quick_select | 12859304736 |
| memory/innodb/mem0mem | 7607681148 |
| memory/sql/String::value | 1405409537 |
| memory/sql/TABLE | 1117918354 |
| memory/innodb/btr0sea | 984013872 |
+---------------------------------------+-------------------------------------+
可以看到內(nèi)存消耗最高的event是Filesort_buffer，根據(jù)經(jīng)驗，這個應(yīng)該是排序有關(guān)。
統(tǒng)計線程消耗內(nèi)存
select thread_id,
event_name,
SUM_NUMBER_OF_BYTES_ALLOC
from performance_schema.memory_summary_by_thread_by_event_name
order by SUM_NUMBER_OF_BYTES_ALLOC desc
limit 10;
+---------------------+---------------------------------------+-------------------------------------+
| thread_id | event_name | SUM_NUMBER_OF_BYTES_ALLOC |
+---------------------+---------------------------------------+-------------------------------------+
| 105 | memory/memory/HP_PTRS | 69680198792 |
| 183 | memory/sql/Filesort_buffer::sort_keys | 49210098808 |
| 154 | memory/sql/Filesort_buffer::sort_keys | 43304339072 |
| 217 | memory/sql/Filesort_buffer::sort_keys | 37752275360 |
| 2773 | memory/sql/Filesort_buffer::sort_keys | 31460644712 |
| 218 | memory/sql/Filesort_buffer::sort_keys | 31128994280 |
| 2331 | memory/sql/Filesort_buffer::sort_keys | 28763981248 |
| 106 | memory/memory/HP_PTRS | 27938197584 |
| 191 | memory/sql/Filesort_buffer::sort_keys | 27701610224 |
| 179 | memory/sql/Filesort_buffer::sort_keys | 25624723968 |
+---------------------+---------------------------------------+-------------------------------------+
可以看到內(nèi)存消耗多的線程都跟Filesort_buffer相關(guān)。
定位具體SQL
根據(jù)前邊我們查到的thread_id去日志里查找對應(yīng)的SQL，阿里云RDS審計日志相對還是比較強大的。我們直接根據(jù)thread_id直接檢索。
我們在日志里看到大量這樣的SQL，掃描行數(shù)在幾千到幾萬不等。雖然每次查詢時間并不長，大概在幾十到幾百毫秒，但是并發(fā)量很大。
跟開發(fā)同學(xué)核實之后，這個查詢沒有做分頁，取到的數(shù)據(jù)有很多行，而且最后要做排序，并且排序字段并沒有合適的索引。到此，這次內(nèi)存使用率出現(xiàn)異常的罪魁禍首已經(jīng)找到。
到此這篇關(guān)于分享MySQL生產(chǎn)庫內(nèi)存異常增高的排查過程的文章就介紹到這了,更多相關(guān)MySQL生產(chǎn)庫內(nèi)存異常增高內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
mysql分區(qū)相對于mysql分庫分表便利很多,可以對現(xiàn)有的mysql大表添加分區(qū),也可以對已有分區(qū)的表擴充分區(qū),下面這篇文章主要給大家介紹了關(guān)于MySql創(chuàng)建分區(qū)的相關(guān)資料,需要的朋友可以參考下2022-04-04
這篇文章主要給大家介紹了關(guān)于MYSQL中解析json格式數(shù)據(jù)的相關(guān)資料,JSON是一種輕量級的數(shù)據(jù)交換格式,采用了獨立于語言的文本格式,類似XML,但是比XML簡單,易讀并且易編寫,需要的朋友可以參考下2023-08-08
這篇文章主要介紹了mysql刪除重復(fù)行的實現(xiàn)方法，非常不錯，具有一定的參考借鑒價值,需要的朋友可以參考下2018-06-06
MySQL出現(xiàn)this?is?incompatible?with?sql_mode=only_full_grou
