mysql中INNODB_BUFFER_POOL_SIZE:設置最佳內存值

本文章來為各位介紹關於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服務器