mysql中INNODB_BUFFER_POOL_SIZE:設定最佳記憶體值

什麼是INNODB BUFFER POOL

電腦使用它們的大部分記憶體來提升對經常訪問的資料的效能。這就是我們所知的緩衝,是系統的一個非常重要的組成部分，因為訪問硬碟的資料可能會慢到100到100000倍，這取決你訪問的資料量。
MyISAM是使用作業系統的檔案系統快取來緩衝那些經常被查詢的資料。然而InnoDB使用的是一種非常不同的方法。
不依賴作業系統的緩衝，InnoDB自己在InnoDB Buffer Pool處理緩衝。經過這篇文章你會學到它是如何工作的，為什麼以那種方式來實施是一個不錯的想法。

InnoDB緩衝池不僅僅是一個緩衝

InnoDB緩衝池實際上用於多個目的，它用來：
* 資料緩衝 – 這絕對是它的最重要的目的
* 索引緩衝 – 這使用是的同一個緩衝池
* 緩衝 – 更改的資料(通常稱為髒資料)在被重新整理到硬碟之前先存放到緩衝
* 儲存內部結構 – 一些結構如自適應雜湊索引或者行鎖也都儲存在InnoDB緩衝池
下面是一個經典的把innodb-buffer-pool-size設定為62G的InnoDB緩衝池頁的分布情況:
innodb-buffer-pool-size-pic1
正如你所看到的，Buffer Pool大多是用於普通的InnoDB頁面，但大約10%用作其它目的。
這張表的單位是InnoDB頁。單個頁面大小實際上是16K，所以你可以乘以16,384來得到以位元組為單位更直觀的使用方式。

InnoDB緩衝池的大小

那麼innodb-buffer-pool-size的大小應該設定為什麼呢？下面我們就開始談到這個。

獨立伺服器

在一個獨立的只使用InnoDB引擎的MySQL伺服器中，根據經驗，推薦設定innodb-buffer-pool-size為伺服器總可用記憶體的80%。
為什麼不是90%或者100%呢？
因為其它的東西也需要記憶體：
* 每個查詢至少需要幾K的記憶體(有時候是幾M)
* 有各種其它內部的MySQL結構和緩衝
* InnoDB有一些結構是不用緩衝池的記憶體的(字典緩衝，檔案系統，鎖系統和頁雜湊表等)
* 也有一些MySQL檔案是在OS緩衝裡的(binary日誌，relay日誌,innodb交易記錄等)
* 此處，你也必須為作業系統留出些記憶體

共用伺服器

如果你的MySQL伺服器與其它應用共用資源，那麼上面80%的經驗就不那麼適用了。
在這樣的環境下，設定一個對的數字有點難度。
首先讓我們來統計一下InnoDB表的實際佔用大小。執行如下查詢：

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緩衝池設定多少記憶體合適。
不過大多數情況你不需要那樣做，你只需要緩衝你經常使用的資料集。
設定好之後，我們來看看如何檢查InnoDB緩衝池大小是否設定足夠。
在終端中，執行如下命令：

$ mysqladmin ext -ri1 | grep Innodb_buffer_pool_reads
| Innodb_buffer_pool_reads                 | 1832098003     |
| Innodb_buffer_pool_reads                 | 595            |
| Innodb_buffer_pool_reads                 | 915            |
| Innodb_buffer_pool_reads                 | 734            |
| Innodb_buffer_pool_reads                 | 622            |
| Innodb_buffer_pool_reads                 | 710            |
| Innodb_buffer_pool_reads                 | 664            |
| Innodb_buffer_pool_reads                 | 987            |
| Innodb_buffer_pool_reads                 | 1287           |
| Innodb_buffer_pool_reads                 | 967            |
| Innodb_buffer_pool_reads                 | 1181           |
| Innodb_buffer_pool_reads                 | 949            |

你所看到的是從硬碟讀取資料到緩衝池的次數(每秒)。上面的資料已經相當高了(幸運的是，這個伺服器的IO裝置能處理每秒4000的IO操作)，如果這個是OLTP系統，我建議提高innodb緩衝池的大小和如果必要增加伺服器記憶體。

更改InnoDB緩衝池

最後，介紹如何更改innodb-buffer-pool-size。
如果你啟動並執行是MySQL 5.7，那麼非常幸運，你可以線上更改這個變數，只需要以root身份執行如下查詢：

mysql> SET GLOBAL innodb_buffer_pool_size=size_in_bytes;
這還沒完，你仍然需要更改my.cnf檔案，不過至少你不需要重啟伺服器讓它生效。從mysql的錯誤記錄檔中我們可以看到它生效的過程：

[Note] InnoDB: Resizing buffer pool from 134217728 to 21474836480. (unit=134217728)
[Note] InnoDB: disabled adaptive hash index.
[Note] InnoDB: buffer pool 0 : 159 chunks (1302369 blocks) were added.
[Note] InnoDB: buffer pool 0 : hash tables were resized.
[Note] InnoDB: Resized hash tables at lock_sys, adaptive hash index, dictionary.
[Note] InnoDB: Completed to resize buffer pool from 134217728 to 21474836480.
[Note] InnoDB: Re-enabled adaptive hash index.

在更早的mysql版本就需要重啟了，所以：
1. 在my.cnf中設定一個innodb_buffer_pool_size合適的值
2.重啟mysql伺服器