MYSQL中內存問題的示例分析

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

首先一個MYSQL系統中的內存大致分為，這里僅僅討論僅僅提供MYSQL服務的服務器。

1  系統使用內存包含內核運行，系統的緩存等

2  MYSQL 本身系統固定使用的內存，innodb_buffer_pool   query cache 等等

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

4   MYSQL replication  and log 使用的內存 例如 binary log cache ,replicatiton connection  等等

問題 1  內存泄漏或因為內存不足造成的 OOM 

一般來來說，判斷內存是不是泄漏不是DB 應該做的事情，但有的時候知道的多一點沒有壞處

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

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

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

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

內存不足的主要原因刨除因為本身系統并發或者本身資源不足的情況，大部分情況還是要看看語句的方面，是不是已經優化了，或者存在的問題較少。

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

問題2 到底我的innodb_buffer_pool_size 該怎么設置

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

實際上這已經上了一個套，首先我們需要知道給我們的機器大致能承載多大的工作量，如果超過機器能承受的工作量，則就需要和相關的人員談談了。

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

另外如果系統已經運行了一段時間，則我們怎么知道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;

通過上面的查詢，我們可以看到當前系統里面到大致的表的數量，有的建議里面說要根據總體的數據量來得出一個恰當的 innodb_buffer_pool_size 的量。

下面有的建議里面就給出了一個公式，通過下面的圖中的公式算出當前你的innodb_buffer_pool_size  應該設置的一個量級。

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;

同時也可以關注一段系統狀態里面的 innodb_buffer_pool_reads 看看系統從磁盤中讀取數據的量在一個規定（業務繁忙）時間的情況，如果經常大量的去讀，并且你的I/O系統也不怎么的情況下，建議還是加大innodb_buffer_pool_size ，盡量滿足系統的需求。

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

在排除innodb_buffer_pool_size 設置不當造成的性能問題后，就需要關注以下幾個buffer

read_buffer_size

read_rnd_buffer_size

sort_buffer_size

join_buffer_size

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

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

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

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

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

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

最后，MYSQL的內存除了上的一些東西，其實可以通過SYS 庫 或者 performance_schema 中的一些表來查看當前的內存情況，方便對當前的系統進行調節。

如大部分的MYSQL系統都打開了performance_schema 進行系統的性能信息的收集，而通過相關的信息收集時可以看到相關的內存的一些詳細的分配的情況。

下面就展示了一些當前的內存分配的情況

當然查看每個buffer pool 中的內存分配的情況，還是查看 show engine innodb status 會更快查看相關的明細。 

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

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