MYSQL中內(nèi)存問題的示例分析

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

另外如果系統(tǒng)已經(jīng)運行了一段時間，則我們怎么知道innodb_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ù)量來得出一個恰當(dāng)?shù)?innodb_buffer_pool_size 的量。

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

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;

同時也可以關(guān)注一段系統(tǒng)狀態(tài)里面的 innodb_buffer_pool_reads 看看系統(tǒng)從磁盤中讀取數(shù)據(jù)的量在一個規(guī)定（業(yè)務(wù)繁忙）時間的情況，如果經(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)注以下幾個buffer

read_buffer_size

read_rnd_buffer_size

sort_buffer_size

join_buffer_size

以上幾個BUFFER 可以解決如下問題

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

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

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

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

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

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

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

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

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

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

看完了這篇文章，相信你對“MYSQL中內(nèi)存問題的示例分析”有了一定的了解，如果想了解更多相關(guān)知識，歡迎關(guān)注億速云行業(yè)資訊頻道，感謝各位的閱讀！