| innodb_buffer_pool_size | 占用內(nèi)存最大的參數(shù) |
| innodb_additional_mem_pool_size | 額外內(nèi)存，mysql5.7以后移除 |
| innodb_log_buffer_size | 重做日志緩存大小 |
| key_buffer_size | 只用于MyISAM引擎，不需要太大 |
| tmp_table_size | 臨時(shí)表緩存大小 |
| query_cache_size | 查詢緩存，建議關(guān)閉 |
| max_connections | 最大連接數(shù) |
| read_buffer_size read_rnd_buffer_size sort_buffer_size join_buffer_size binlog_cache_size thread_stack | 這些參數(shù)都跟線程有關(guān)，所占內(nèi)存為這些參數(shù)的和*最大連接數(shù)。連接數(shù)越多占用內(nèi)存也就越多，建議不超過(guò)512K，binlog_cache_size采用系統(tǒng)默認(rèn)32K，thread_stack默認(rèn)256K即可 |
需要給mysql分配多大內(nèi)存，直接跟以上參數(shù)有關(guān)。太大會(huì)導(dǎo)致內(nèi)存不足，太小會(huì)影響性能，如何分配合理值，還需根據(jù)業(yè)務(wù)情況來(lái)定。但業(yè)務(wù)場(chǎng)景較多，每個(gè)業(yè)務(wù)配置都不一樣，會(huì)造成運(yùn)維成本較高。所以能定制出一套適用于絕大多數(shù)場(chǎng)景的配置模板就可以了。
1、如果mysql分配的內(nèi)存比系統(tǒng)內(nèi)存大
比如系統(tǒng)內(nèi)存128G，mysql分配的內(nèi)存已經(jīng)大于128G，但是系統(tǒng)本身和其它程序也需要內(nèi)存，甚至mysqldump同樣需要內(nèi)存，所以這樣就很容易造成系統(tǒng)內(nèi)存不足，從而導(dǎo)致OOM。這時(shí)我們要查出哪些參數(shù)設(shè)置比較大，適當(dāng)降低內(nèi)存分配。
innodb_buffer_pool在mysql中占有最大內(nèi)存，將innodb_buffer_pool_size調(diào)小可以有效降低OOM問(wèn)題。但如果設(shè)置太小會(huì)導(dǎo)致內(nèi)存刷臟頁(yè)頻率增加，IO增多，從而降低性能。通常我們認(rèn)為innodb_buffer_pool_size為系統(tǒng)內(nèi)存的60%~75%最優(yōu)。
查看buffer_pool的使用情況：
MYSQL >select POOL_ID,POOL_SIZE,FREE_BUFFERS,DATABASE_PAGES,OLD_DATABASE_PAGES,MODIFIED_DATABASE_PAGES,PAGES_MADE_YOUNG,PAGES_NOT_MADE_YOUNG from information_schema.INNODB_BUFFER_POOL_STATS;+---------+-----------+--------------+----------------+--------------------+-------------------------+------------------+----------------------+| POOL_ID | POOL_SIZE | FREE_BUFFERS | DATABASE_PAGES | OLD_DATABASE_PAGES | MODIFIED_DATABASE_PAGES | PAGES_MADE_YOUNG | PAGES_NOT_MADE_YOUNG |+---------+-----------+--------------+----------------+--------------------+-------------------------+------------------+----------------------+| 0 | 611669 | 1024 | 610644 | 225393 | 0 | 309881 | 0 || 1 | 611669 | 1024 | 610645 | 225393 | 0 | 309816 | 0 || 2 | 611669 | 1024 | 610645 | 225393 | 0 | 309756 | 0 |+---------+-----------+--------------+----------------+--------------------+-------------------------+------------------+----------------------+
可以看出buffer_pool分了3個(gè)實(shí)例，POOL_SIZE為每個(gè)實(shí)例大小，這里為頁(yè)個(gè)數(shù)，我們知道m(xù)ysql頁(yè)的默認(rèn)大小為16K，所以單個(gè)實(shí)例的真正大小為611669*16K，5.6以后要求FREE_BUFFERS至少保留1024個(gè)頁(yè)，少于1024個(gè)頁(yè)時(shí)會(huì)強(qiáng)制刷臟數(shù)據(jù)，后面的值可以看出臟頁(yè)情況。另外如果PAGES_MADE_YOUNG遠(yuǎn)大于PAGES_NOT_MADE_YOUNG頁(yè)數(shù)，那么此時(shí)內(nèi)存使用就可能比較大，可以適當(dāng)降低innodb_buffer_pool_size大小。
另一篇文章也有對(duì)buffer_pool的介紹：一條命令解讀InnoDB存儲(chǔ)引擎—show engine innodb status
如果innodb_buffer_pool_size不是很大，但內(nèi)存還是很高，也可能是由于并發(fā)線程太多導(dǎo)致，需要確認(rèn)是不是應(yīng)用異常，還是需要調(diào)整max_connections最大連接。如果連接太多，每個(gè)連接也會(huì)占用獨(dú)立的內(nèi)存，read、sort、join緩存都是session級(jí)別，連接越多需要內(nèi)存就越多，所以這些參數(shù)也不能設(shè)置太大。
需要注意的是一些參數(shù)不支持動(dòng)態(tài)修改，只能先修改配置文件然后重啟mysql才能生效，所以在mysql啟動(dòng)之前，一定要把參數(shù)值確認(rèn)好。
2、如果mysql分配的內(nèi)存比系統(tǒng)內(nèi)存小
如果mysql參數(shù)設(shè)置都比較合理，但是仍然出現(xiàn)oom，那么可能是由于mysql在系統(tǒng)層面所需內(nèi)存不足導(dǎo)致，因?yàn)閙ysql讀取表時(shí)，如果同時(shí)有多個(gè)session引用一個(gè)表則會(huì)創(chuàng)建多個(gè)表對(duì)象，這樣雖然減少了內(nèi)部表鎖的爭(zhēng)用，但是會(huì)加大內(nèi)存使用量。
首先，可以通過(guò)lsof -p pid查看進(jìn)程打開(kāi)的系統(tǒng)文件數(shù)，pid為mysqld的進(jìn)程號(hào)。
[root@localhost ~]# ps -ef | grep mysqld[root@localhost ~]# lsof -p 3455COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAMEmysqld 30012 mysql cwd DIR 8,3 12288 58982404 /mysql/datamysqld 30012 mysql mem REG 8,1 599392 272082 /lib64/libm-2.12.somysqld 30012 mysql mem REG 8,1 91096 272089 /lib64/libz.so.1.2.3mysqld 30012 mysql mem REG 8,1 93320 272083 /lib64/libgcc_s-4.4.7-20120601.so.1mysqld 30012 mysql mem REG 8,1 43392 272095 /lib64/libcrypt-2.12.somysqld 30012 mysql 10uW REG 8,3 536870912 59015176 /mysql/data/ib_logfile0mysqld 30012 mysql 11uW REG 8,3 536870912 59015177 /mysql/data/ib_logfile1mysqld 30012 mysql 12uW REG 8,3 536870912 59015178 /mysql/data/ib_logfile2mysqld 30012 mysql 13uW REG 8,3 675282944 59001816 /mysql/data/test/table6.ibdmysqld 30012 mysql 14uW REG 8,3 2155872256 58985613 /mysql/data/test/table487.ibdmysqld 30012 mysql 15u REG 8,3 0 58982414 /mysql/tmp/ibhNDzPM (deleted)mysqld 30012 mysql 16uW REG 8,3 2306867200 58983861 /mysql/data/test/table327.ibdmysqld 30012 mysql 17uW REG 8,3 4169138176 58985467 /mysql/data/test/table615.ibdmysqld 30012 mysql 18uW REG 8,3 79691776 59020641 /mysql/data/test/table_v199_20170920.ibdmysqld 30012 mysql 19uW REG 8,3 67108864 59015043 /mysql/data/test/table_v39_20170920.ibdmysqld 30012 mysql 20uW REG 8,3 75497472 59014992 /mysql/data/test/table_v7_20170920.ibdmysqld 30012 mysql 21uW REG 8,3 83886080 59019735 /mysql/data/test/table_v167_20170920.ibdmysqld 30012 mysql 22uW REG 8,3 1367343104 58997684 /mysql/data/popfin6/table_uuid6.ibdmysqld 30012 mysql 23uW REG 8,3 1275068416 58984491 /mysql/data/test/table_uuid7.ibd...[root@localhost ~]# lsof -p 3455 |grep ibd|wc -l54869
查看mysql服務(wù)打開(kāi)文件數(shù)限制：
MySQL >show global variables like 'open_files_limit';+------------------+-------+| Variable_name | Value |+------------------+-------+| open_files_limit | 65535 |+------------------+-------+
查看操作系統(tǒng)打開(kāi)文件數(shù)限制：
[root@localhost ~]# ulimit -amax memory size (kbytes, -m) unlimitedopen files (-n) 65535
如果此時(shí)打開(kāi)的文件很多，內(nèi)存也會(huì)占用很多。
其次，還需看一下table_open_cache，當(dāng)打開(kāi)一個(gè)表后會(huì)把這個(gè)表的文件描述符緩存下來(lái)。
MYSQL >show global variables like 'table_open_cache';+------------------+-------+| Variable_name | Value |+------------------+-------+| table_open_cache | 16384 |+------------------+-------+MYSQL >show global status like '%open%tables%';+------------------------+--------+| Variable_name | Value |+------------------------+--------+| Open_tables | 16384 || Opened_tables | 401374 |+------------------------+--------+
通過(guò)以上兩個(gè)值來(lái)判斷 table_open_cache 是否到達(dá)瓶頸。
當(dāng)緩存中的值open_tables 臨近到了 table_open_cache 值的時(shí)候，說(shuō)明表緩存池快要滿了，但Opened_tables 還在一直有新的增長(zhǎng)，這說(shuō)明還有很多未被緩存的表。
用show open tables from schema命令，可以查看table_open_cache中緩存的表，重復(fù)打開(kāi)的表僅顯示一個(gè)
MYSQL >show open tables from sysbenchtest;+--------------+----------+--------+-------------+| Database | Table | In_use | Name_locked |+--------------+----------+--------+-------------+| sysbenchtest | sbtest1 | 1 | 0 || sysbenchtest | sbtest2 | 0 | 0 || sysbenchtest | sbtest3 | 0 | 0 || sysbenchtest | sbtest4 | 0 | 0 || sysbenchtest | sbtest5 | 0 | 0 |
In_use顯示當(dāng)前正在使用此表的線程數(shù)，如果大于0也意味著此表被鎖。
Name_locked只適用于DROP和RENAME，在執(zhí)行DROP或RENAME時(shí)，table_open_cache中的表文件描述符會(huì)被移除，所以不會(huì)看到除0以外的其他值。
一般在庫(kù)表比較多的情況下（分庫(kù)分表）很容易出現(xiàn)內(nèi)存占用較大的情況。如果要解決根源，還是需要對(duì)庫(kù)表進(jìn)行拆分。
3、MYSQL內(nèi)部其他內(nèi)存
information_schema下的表都使用的都是MEMORY存儲(chǔ)引擎，數(shù)據(jù)只在內(nèi)存中保留，啟動(dòng)時(shí)加載，關(guān)閉后釋放。
查看除系統(tǒng)庫(kù)外是否有MEMORY引擎表：
MySQL >select * from information_schema.tables where engine='MEMORY' and TABLE_SCHEMA !='information_schema';
如果業(yè)務(wù)有使用MEMORY存儲(chǔ)引擎的，盡量改成innodb引擎。
4、MYSQL事件內(nèi)存指標(biāo)
從MySQL5.7開(kāi)始，在performance_schema中會(huì)記錄內(nèi)存分配。
查看哪些指標(biāo)啟動(dòng)了內(nèi)存收集功能：
MySQL >select * from performance_schema.setup_instruments where NAME LIKE 'memory/%';
啟動(dòng)需要收集內(nèi)存的指標(biāo)：
MySQL >UPDATE performance_schema.setup_instruments SET ENABLED = 'YES' WHERE NAME LIKE 'memory/%';
指標(biāo)的內(nèi)存收集結(jié)果會(huì)匯總到到sys庫(kù)下的視圖中：
MySQL root@[sys]>show tables like 'memory%';+-----------------------------------+| Tables_in_sys (memory%) |+-----------------------------------+| memory_by_host_by_current_bytes || memory_by_thread_by_current_bytes || memory_by_user_by_current_bytes || memory_global_by_current_bytes || memory_global_total |+-----------------------------------+
這些視圖總結(jié)了內(nèi)存使用情況，按事件類(lèi)型分組，默認(rèn)降序排列：
MySQL >select event_name,current_count,current_alloc,high_alloc from sys.memory_global_by_current_bytes where current_count > 0;+--------------------------------------------------------------------------------+---------------+---------------+-------------+| event_name | current_count | current_alloc | high_alloc |+--------------------------------------------------------------------------------+---------------+---------------+-------------+| memory/performance_schema/table_handles | 10 | 90.62 MiB | 90.62 MiB || memory/performance_schema/events_statements_summary_by_thread_by_event_name | 3 | 26.01 MiB | 26.01 MiB || memory/performance_schema/memory_summary_by_thread_by_event_name | 3 | 16.88 MiB | 16.88 MiB || memory/performance_schema/events_statements_history_long | 1 | 13.66 MiB | 13.66 MiB || memory/performance_schema/events_statements_history | 3 | 10.49 MiB | 10.49 MiB || memory/performance_schema/events_statements_current | 3 | 10.49 MiB | 10.49 MiB |...
總結(jié)：
通過(guò)以上排查能大體知道哪些占用內(nèi)存較多，針對(duì)內(nèi)存占用較多的地方再做具體優(yōu)化。正像文章開(kāi)頭所說(shuō)的，內(nèi)存溢出已經(jīng)是軟件開(kāi)發(fā)歷史上存在了近40年的“老大難”問(wèn)題，更何況數(shù)據(jù)庫(kù)環(huán)境更加復(fù)雜，SQL語(yǔ)法、數(shù)據(jù)類(lèi)型、數(shù)據(jù)大小等這些因素都與內(nèi)存有關(guān)，所以在設(shè)計(jì)使用上更要多想內(nèi)存溢出問(wèn)題。
以上就是MySQL OOM（內(nèi)存溢出）的解決思路的詳細(xì)內(nèi)容，更多關(guān)于MySQL OOM（內(nèi)存溢出）的解決的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！
標(biāo)簽：日照 合肥 貴州 鎮(zhèn)江 臺(tái)灣 北京 鷹潭 阜新
巨人網(wǎng)絡(luò)通訊聲明：本文標(biāo)題《MySQL OOM（內(nèi)存溢出）的解決思路》，本文關(guān)鍵詞 MySQL,OOM,內(nèi)存,溢出,的,解決,；如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問(wèn)題，煩請(qǐng)?zhí)峁┫嚓P(guān)信息告之我們，我們將及時(shí)溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò)，涉及言論、版權(quán)與本站無(wú)關(guān)。
