小編給大家分享一下MySQL中內(nèi)存問題的示例分析，希望大家閱讀完這篇文章之后都有所收獲，下面讓我們一起去探討吧！

成都創(chuàng)新互聯(lián)公司專注于昆玉企業(yè)網(wǎng)站建設(shè),成都響應(yīng)式網(wǎng)站建設(shè),購(gòu)物商城網(wǎng)站建設(shè)。昆玉網(wǎng)站建設(shè)公司,為昆玉等地區(qū)提供建站服務(wù)。全流程按需網(wǎng)站策劃，專業(yè)設(shè)計(jì)，全程項(xiàng)目跟蹤，成都創(chuàng)新互聯(lián)公司專業(yè)和態(tài)度為您提供的服務(wù)

首先一個(gè)MYSQL系統(tǒng)中的內(nèi)存大致分為，這里僅僅討論僅僅提供MYSQL服務(wù)的服務(wù)器。

1  系統(tǒng)使用內(nèi)存包含內(nèi)核運(yùn)行，系統(tǒng)的緩存等

2  MYSQL 本身系統(tǒng)固定使用的內(nèi)存，innodb_buffer_pool   query cache 等等

3   MYSQL workload ，例如連接，每個(gè)查詢的 buffers  join  buffer sort buffer等等

4   MYSQL replication  and log 使用的內(nèi)存 例如 binary log cache ,replicatiton connection  等等

問題 1  內(nèi)存泄漏或因?yàn)閮?nèi)存不足造成的 OOM 

一般來來說，判斷內(nèi)存是不是泄漏不是DB 應(yīng)該做的事情，但有的時(shí)候知道的多一點(diǎn)沒有壞處

內(nèi)存泄漏的公式 :   centos 6.x  used - buffers - cached 的值 跟 used 的值作比較 

                           centos 7.x  available - free 的值 跟 buff/cache 的值作比較 

可以看到，根據(jù)上面的額公式 3019 - 819 = 2200  對(duì)比 buff/cache  2684比較 并沒有特別大的差異，說明內(nèi)存并沒有泄漏，一般來說不超過10% 與  buffer/cache 相比的來說都不算存在內(nèi)存泄漏的跡象。

查看SWAP ，這里面就有爭(zhēng)論了，有的企業(yè)是直接將SWAP 禁用了，這樣的企業(yè)一般都會(huì)給系統(tǒng)分配比較大的內(nèi)存，如果當(dāng)內(nèi)存耗盡，系統(tǒng)OOM的時(shí)候也不大會(huì)怕 KILL 消耗資源最大的進(jìn)程。另一部分企業(yè)還是使用了SWAP 怕的就是OOM ，但不好的地方就是如果用到了 SWAP模擬內(nèi)存，則MYSQL的性能會(huì)急轉(zhuǎn)直下，所以要不要用SWAP 那就看你怎么選擇了。

內(nèi)存不足的主要原因刨除因?yàn)楸旧硐到y(tǒng)并發(fā)或者本身資源不足的情況，大部分情況還是要看看語(yǔ)句的方面，是不是已經(jīng)優(yōu)化了，或者存在的問題較少。

并且由于很多系統(tǒng)不是自研，所以一般遇到這樣的問題，除了本單位有能優(yōu)化的系統(tǒng)的人以外，大概率的可能都是添加內(nèi)存。

問題2 到底我的innodb_buffer_pool_size 該怎么設(shè)置

大部分DB們可能認(rèn)為這都不是一個(gè)問題，你給我多大的機(jī)器，我就按照60-80% 來設(shè)置innodb_buffer_pool_size 就可以了。

實(shí)際上這已經(jīng)上了一個(gè)套，首先我們需要知道給我們的機(jī)器大致能承載多大的工作量，如果超過機(jī)器能承受的工作量，則就需要和相關(guān)的人員談?wù)劻恕?/p>

而不是到了后面在去談，雖然可以亡羊補(bǔ)牢，但在領(lǐng)導(dǎo)的心里，你屬于后知后覺，而不是未卜先知。

另外如果系統(tǒng)已經(jīng)運(yùn)行了一段時(shí)間，則我們?cè)趺粗纈nnodb_buffer_pool_size 是合理的

SELECT engine,

    ->   count(*) as TABLES,

    ->   concat(round(sum(table_rows)/1000000,2),'M') rows,

    ->   concat(round(sum(data_length)/(1024*1024*1024),2),'G') DATA,

    ->   concat(round(sum(index_length)/(1024*1024*1024),2),'G') idx,

    ->   concat(round(sum(data_length+index_length)/(1024*1024*1024),2),'G') total_size,

    ->   round(sum(index_length)/sum(data_length),2) idxfrac

    -> FROM information_schema.TABLES

    -> WHERE table_schema not in ('mysql', 'performance_schema', 'information_schema')

    -> GROUP BY engine

    -> ORDER BY sum(data_length+index_length) DESC LIMIT 10;

通過上面的查詢，我們可以看到當(dāng)前系統(tǒng)里面到大致的表的數(shù)量，有的建議里面說要根據(jù)總體的數(shù)據(jù)量來得出一個(gè)恰當(dāng)?shù)?innodb_buffer_pool_size 的量。

下面有的建議里面就給出了一個(gè)公式，通過下面的圖中的公式算出當(dāng)前你的innodb_buffer_pool_size  應(yīng)該設(shè)置的一個(gè)量級(jí)。

SELECT CEILING(Total_InnoDB_Bytes*1.6/POWER(1024,3)) RIBPS_GB FROM     (SELECT SUM(data_length+index_length) Total_InnoDB_Bytes      FROM information_schema.tables WHERE engine='InnoDB') A;

同時(shí)也可以關(guān)注一段系統(tǒng)狀態(tài)里面的 innodb_buffer_pool_reads 看看系統(tǒng)從磁盤中讀取數(shù)據(jù)的量在一個(gè)規(guī)定（業(yè)務(wù)繁忙）時(shí)間的情況，如果經(jīng)常大量的去讀，并且你的I/O系統(tǒng)也不怎么的情況下，建議還是加大innodb_buffer_pool_size ，盡量滿足系統(tǒng)的需求。

問題 3 ,我的innodb_buffer_pool_size 設(shè)置的較高，但查詢還是很慢

在排除innodb_buffer_pool_size 設(shè)置不當(dāng)造成的性能問題后，就需要關(guān)注以下幾個(gè)buffer

read_buffer_size

read_rnd_buffer_size

sort_buffer_size

join_buffer_size

以上幾個(gè)BUFFER 可以解決如下問題

1  查詢的表中無合適的索引，或無法使用索引的情況下，會(huì)進(jìn)行全表掃描，全索引掃描，這種情況會(huì)將數(shù)據(jù)順序的讀入到 read_buffer_size 中，當(dāng)讀取的數(shù)據(jù)足以在 read_buffer_size 中保存，則讀取結(jié)束后，會(huì)將buffer的數(shù)據(jù)返回上層，加速這一類的查詢。一般例如主鍵（有序） 或者和日期相關(guān)的有序數(shù)據(jù)的提取，都會(huì)用到。

2  查詢中如果沒有順序查詢而是大量的隨機(jī)查詢，并且也沒有索引或有效的索引的情況下，則會(huì)直接進(jìn)行隨機(jī)數(shù)據(jù)的存儲(chǔ)

3  當(dāng)多個(gè)表進(jìn)行JOIN 的情況下，在沒有有效索引的情況下，為了減少與被驅(qū)動(dòng)表讀取的次數(shù)，將需要讀取的數(shù)據(jù)放入到 join_buffer 提高JOIN 的效率，而如果JOIN_buffer_size 不足的情況，則會(huì)在需要新的數(shù)據(jù)寫入后，清理掉之前寫入的數(shù)據(jù)，而這些數(shù)據(jù)如果也正在使用，則會(huì)在清理掉現(xiàn)在正在用的數(shù)據(jù)，造成查詢緩慢，多次訪問I/O。 

4  Sort_buffer_size 因?yàn)镸YSQL 5.X都不支持倒序，另外如果沒有索引的情況下，進(jìn)行排序也是要進(jìn)行filesort，而足夠大的sort 可以降低查詢?cè)谂判驎r(shí)和磁盤之間的交互，而在內(nèi)存中解決，所以對(duì)于排序操作多的系統(tǒng)，并且也么有什么優(yōu)化的情況下，大的sort_buffer_size 是很有用的。

所以如果你正在被垃圾SQL 摧殘還不能進(jìn)行改變的時(shí)候，可以提高這幾個(gè)位置的內(nèi)存設(shè)置，可能會(huì)給你帶來片刻的喘息。

最后，MYSQL的內(nèi)存除了上的一些東西，其實(shí)可以通過SYS 庫(kù) 或者 performance_schema 中的一些表來查看當(dāng)前的內(nèi)存情況，方便對(duì)當(dāng)前的系統(tǒng)進(jìn)行調(diào)節(jié)。

如大部分的MYSQL系統(tǒng)都打開了performance_schema 進(jìn)行系統(tǒng)的性能信息的收集，而通過相關(guān)的信息收集時(shí)可以看到相關(guān)的內(nèi)存的一些詳細(xì)的分配的情況。

下面就展示了一些當(dāng)前的內(nèi)存分配的情況

當(dāng)然查看每個(gè)buffer pool 中的內(nèi)存分配的情況，還是查看 show engine innodb status 會(huì)更快查看相關(guān)的明細(xì)。 

總之MYSQL 的內(nèi)存其實(shí)并不是innodb_buffer_pool_size 那么簡(jiǎn)單，隨著版本的更新，更多的內(nèi)存的信息的分析和查看將移交到 sys 庫(kù)和 preformance_schema 庫(kù)的相關(guān)表中。

看完了這篇文章，相信你對(duì)“MYSQL中內(nèi)存問題的示例分析”有了一定的了解，如果想了解更多相關(guān)知識(shí)，歡迎關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道，感謝各位的閱讀！