14.6.3.1 The InnoDB Buffer Pool

14.6.3.1 The InnoDB Buffer PoolInnoDB 保持一個存儲區域被稱為buffer pool 用于cache數據和索引在內存里,知道InnoDB buffer pool 如何工作,利用它來保持頻繁訪問的數據在內存里,是MYSQL 調優的一個重要方面。你可以配置InnoDB buffer pool的各個方面來改善性能:理想情況下,你設置buffer pool的大小為實際最大值,留下足夠的內存用于其他進程在服務器上運行而沒有產生分頁。buffer pool越大, InnoDB 表位越像是內存數據庫,從磁盤讀取一次然后從內存訪問數據在隨后的讀操作。Buffer pool size  是使用 innodb_buffer_pool_size 配置選項2.  隨著64位系統使用大的內存,你可以把buffer 分成多個部分，以盡量減少內存結果的沖突在并發操作期間。3.你可以保持頻繁訪問的數據在內存里 盡管突然的高峰活動對于備份或者報告操作4.你可以控制何時如何InnoDB 執行魚肚請求來預獲取Pages異步到buffer pool,預計 pages 馬上被需要5. 你可以控制當background刷新舊的pages發生,是否InnoDB 動態調整 基于負載的刷新頻率。6.你可以配置InnoDB 來保持當前的buffer pool 狀態來避免一個漫長的準備期在一個server 重啟后。你也可以保留當前的buffer pool 狀態 當server 在運行時InnoDB Buffer Pool LRU AlgorithmInnoDB 管理buffer pool 作為一個列表,使用一個變化的LRU算法。當room 是被需要增加一個新的page 到pool,InnoDB 驅逐最少使用的pages增加新的pages到列表的中間。這個 中間插入的策略 對待列表作為兩個清單1.在頭部,  新的（或者年輕的）的一個子列表 被頻繁訪問2.在尾部, 一個老的pages 的一個子列表很少被訪問這個算法保持pages  大量使用通過查詢在新的子列表老的子列表包含更少使用的頁, 這些是候選為被驅逐LRU 算法操作默認如下:1.buffer pool的3/8 是分給old sublist2. 列表的中間是邊界新的sublist的尾部和 old sublist的開頭3.當InnoDB 讀取一個Page到buffer pool,他初始插入在中間(old sublist的頭部).一個page 可以讀進來 因為它是被需要用于一個用戶指定的操作比如SQL查詢,或者 作為預讀的一部分被InnoDB自動執行4.訪問一個page 在old sublist 讓這個Page變的年輕,移動到 buffer pool的head（new sublist的頭部）如果page 因為需要被讀取, 第一次訪問立即發生 page變的yong.如果page 被讀進來由于預讀, 第一訪問不立即發生5.在進行數據庫操作時, pages在buffer pool不被訪問 變老移動到列表的尾部。Pages 在new和old 子列表 老化其他pages變成新的。6.默認的, pages被查詢讀取立即移動到new sublist,意味著他們會在buffer pool中停留很長時間。一個表掃描（比如執行一個mysqldump操作),或者一個SELECT 語句沒有帶WHERE條件)可以帶來大量的數據到buffer pool ，驅逐等量的older數據,即使新的數據不會被再次使用。類似的,pages是通過后臺線程加載的 只訪問一次 移動到new list的head.這些情況回把平凡使用的pages移動到old sublist.InnoDB Buffer Pool Configuration Options  InnoDB Buffer Pool 配置選項幾個配置選項影響InnoDB buffer pool的不同方面:1.innodb_buffer_pool_size指定buffer pool的大小, 如果buffer pool是太小了你有足夠的內存,讓buffer pool變大能改善性能通過降低磁盤I/O的總量zabbix:/root/Esx# cat /etc/my.cnf  | grep innodb_buffer_pool_size
innodb_buffer_pool_size=4G2.innodb_buffer_pool_instances把buffer pool 分成一個用戶指定的單獨的區域, 每個有它自己的LRU list和相關的數據結構,降低競爭在并發內存讀取和寫操作。這個選項只有當innodb_buffer_pool_size 值大于1GB才生效。總的buffer pool的代銷是被分成多個buffer pool.為了最好的性能, 指定innodb_buffer_pool_instances and innodb_buffer_pool_size的組合這樣每個buffer pool 實例有至少1GBmysql> show variables like '%innodb_buffer_pool%';
+-------------------------------------+----------------+
| Variable_name                       | Value          |
+-------------------------------------+----------------+
| innodb_buffer_pool_dump_at_shutdown | OFF            |
| innodb_buffer_pool_dump_now         | OFF            |
| innodb_buffer_pool_filename         | ib_buffer_pool |
| innodb_buffer_pool_instances        | 8              |
| innodb_buffer_pool_load_abort       | OFF            |
| innodb_buffer_pool_load_at_startup  | OFF            |
| innodb_buffer_pool_load_now         | OFF            |
| innodb_buffer_pool_size             | 4294967296     |
+-------------------------------------+----------------+
8 rows in set (0.01 sec)innodb_old_blocks_pct;指定 buffer pool近似的比例 InnoDB 使用old block sublist.范圍是從5到95,默認是37（3/8的pool大小）mysql> show variables like '%innodb_old_blocks_pct%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_old_blocks_pct | 37    |
+-----------------------+-------+
1 row in set (0.00 sec)innodb_old_blocks_time:指定多久單位毫秒一個page 插入到old sublist 必須留在那個在他第一次訪問后在他可以被移動到new sublist如果值是0, 一個page 插入到old sublist 立即移動到新的sublist  當它第一次被訪問后。如果值大于0, pages 停留在old sublist 知道一個訪問發生至少多少毫秒后第一次被訪問。比如,值為1000 導致pages停留在old sublist 1秒鐘 在首次訪問后 在它們變的合格移動到new sublistmysql> show variables like '%innodb_old_blocks_time%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| innodb_old_blocks_time | 1000  |
+------------------------+-------+
1 row in set (0.00 sec)設置 innodb_old_blocks_time 大于0防止一次表掃描充滿new sublist 記錄在一個Page讀進來后一個掃描可以訪問很多次,但是page是不能使用在那以后。如果 innodb_old_blocks_time 是設置一個值大于處理page的時間,page停留在old sublist,老化到列表的尾部 被快速淘汰。這種方式, pages只使用一次掃描不影響大量使用的pages在new sublistinnodb_old_blocks_time  可以設置在運行時間,這樣你可以臨時改變 當操作需要表掃描或者dumpsinnodb_read_ahead_threshold控制  預讀的靈敏度,InnoDB 使用預取pages到buffer poolinnodb_random_read_ahead讓隨機的預讀技術用于預取pages到buffer pool.mysql> show variables like '%innodb_random_read_ahead%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_random_read_ahead | OFF   |
+--------------------------+-------+
1 row in set (0.00 sec)innodb_adaptive_flushing