1、InnoDB buffer pool設置 Buffer pool是內存中用來緩存數據和索引的存儲區域，其是MySQL性能調優的重要一環。 理想情況下，設置的size越大，則緩存到內存的數據越多，InnoDB就越像是內存數據庫。 Buffer pool的底層是一個列表，通過LRU算法進行數據頁的換進換出操作。當空間原因導致新頁的加入需要換出一頁時，InnoDB取出最近最少使用的頁并將這個新的數據頁加入到列表的中央。從方向上看，列表的頭部是最常使用的數據頁，而在尾部則是最少使用的數據頁。 Buffer pool中3/8的部分是保存最少使用的數據頁，而中央部分其實是經常使用和最少使用的結合點。當在最少使用中保存的數據頁被訪問時，則數據頁就會被移動到列表的頭部變成最常使用的。 配置大小 InnoDB buffer pool的大小可以在啟動時配置，也可以在啟動之后配置。 增加和減少buffer pool的大小都是以大塊的方式，塊的大小由參數innodb_buffer_pool_chunk_size決定，默認為128M。 Innodb_buffer_pool_size的大小可以自行設定，但必須是innodb_buffer_pool_chunk_size 乘以 innodb_buffer_pool_instances的整數倍，如果不是，則buffer pool會被調整成大于設定值且最接近的一個值，如下例： shell> mysqld --innodb_buffer_pool_size=9G --innodb_buffer_pool_instances=16 ? mysql> SELECT @@innodb_buffer_pool_size/1024/1024/1024; +------------------------------------------+ | @@innodb_buffer_pool_size/1024/1024/1024 | +------------------------------------------+ | 10.000000000000 | +------------------------------------------+ Innodb_buffer_pool_chunk_size可以自行設定，且增加和減少都要以M為單位，并只能在啟動前修改，修改后的值*innodb_buffer_pool_instances不能大于buffer pool的大小，否則修改無效。 innodb_buffer_pool_instances可以開啟多個內存緩沖池，把需要緩沖的數據hash到不同的緩沖池中，這樣可以并行的內存讀寫。 innodb_buffer_pool_instances 參數顯著的影響測試結果，特別是非常高的 I/O 負載時。 實驗環境下， innodb_buffer_pool_instances=8 在很小的 buffer_pool 大小時有很大的不同，而使用大的 buffer_pool 時，innodb_buffer_pool_instances=1 的表現最棒。 [mysqld] innodb_buffer_pool_chunk_size=134217728 buffer pool的大小可以動態修改，用set語句直接修改，當語句發起時，會一直等到當前所有的事務結束后才執行，而一旦執行則執行過程中的其他事務如果要訪問buffer pool就會等待語句執行完畢。 mysql> SET GLOBAL innodb_buffer_pool_size=402653184; 當執行online的調整大小時，可以通過error log或者innodb_buffer_pool_resize_status查看進度 mysql> SHOW STATUS WHERE Variable_name='InnoDB_buffer_pool_resize_status'; +----------------------------------+----------------------------------+ | Variable_name | Value | +----------------------------------+----------------------------------+ | Innodb_buffer_pool_resize_status | Resizing also other hash tables. | +----------------------------------+----------------------------------+ 2、配置多個buffer pool實例 當buffer pool的大小是GB級別時，將一個buffer pool分割成幾個獨立的實例能降低多個線程同時讀寫緩存頁的競爭性而提高并發性。通過innodb_buffer_pool_instances參數可以調整實例個數。如果有多個實例，則緩存的數據頁會隨機放置到任意的實例中，且每個實例都有獨立的buffer pool所有的特性。 Innodb_buffer_pool_instances的默認值是1，最大可以調整成64。 3、Making the Buffer Pool Scan Resistant 新讀取的數據頁被插入到buffer pool的LRU列表的中間位置，默認位置是從尾部開始算起的3/8的位置。當被放入buffer pool的頁被第一次訪問時就開始往列表的前方移動，而這樣列表的后部就是不經常訪問的頁甚至是從不訪問的頁。 通過參數innodb_old_blocks_pct可以控制列表中”old”數據頁所占的百分比，默認是37%，等同于3/8，取值范圍是5~95。 Innodb_old_blocks_time參數默認是1000毫秒，指定了頁面讀取到buffer pool后但沒有移動到經常被訪問列表位置的時間窗口。 4、InnoDB buffer pool預存取（read-ahead） Read ahead是異步地預先獲取多個數據頁到buffer pool的IO操作，這些數據頁都是假定會隨后被用到的。InnoDB通過兩種read-ahead算法提高IO性能： 線性read ahead：預測哪些頁會被順序訪問。通過innodb_read_ahead_threshold參數調整順序數據頁的數量。當從一個區中順序讀取的頁數量大于等于innodb_read_ahead_threshold時，innodb會觸發異步read ahead操作將真個區都讀到buffer pool中。該參數的默認值是56，取值范圍是0~64。 隨機read ahead：通過已經在buffer pool中的數據頁來預測哪些頁會被隨后訪問到。如果13個連續的處于相同區的頁存在于buffer pool中，則InnoDB會把同一個區的其它頁都讀取進來。通過設置innodb_random_read_ahead=ON來開啟此方式。 通過執行show engine innodb status命令顯示的三個參數判斷read-ahead算法的有效性： Innodb_buffer_pool_read_ahead Innodb_buffer_pool_read_ahead_evicted Innodb_buffer_pool_read_ahead_rnd 5、InnoDB buffer pool flushing配置 Innodb會在后臺將buffer pool中的臟頁(已經修改但沒有寫到數據文件)flush掉。當buffer pool中的臟頁所占百分比達到innodb_max_dirty_pages_pct_lvm會觸發flush，當所占比例達到innodb_max_dirty_pages_pct時，則innodb會“強烈”的flush。 針對數據修改操作頻繁的系統，flush可能會嚴重滯后導致有大量的buffer pool內存占用，有一些參數專門針對修改繁忙的系統可以調整： Innodb_adaptive_flushing_lwm：為防止redo log被填滿，此參數設置一個閾值，如果redo log的容量超過此閾值，則執行adaptive flush操作。 Innodb_max_drity_pages_pct_lwm Innodb_io_capacity_max Innodb_flushing_avg_loops 6、重置buffer pool狀態 InnoDB可以通過配置innodb_buffer_pool_dump_at_shutdown參數來確保在mysql正常重啟時部分經常使用的數據頁能直接加載到buffer pool中，通過批量加載的方式，以節省重啟mysql導致的warmup時間（原先在buffer pool中的數據頁要從磁盤再次加載到內存中）。 Buffer pool的狀態可以在任意時刻被保存，而重置狀態也可以恢復任意保存的副本。 在數據庫運行期間動態配置buffer pool數據頁保留占比的方式是： SET GLOBAL innodb_buffer_pool_dump_pct=40; 而在配置文件中的配置方法為： [mysqld] innodb_buffer_pool_dump_pct=40 配置當服務器關閉時保存buffer pool的當前狀態的方法是： SET GLOBAL innodb_buffer_pool_dump_at_shutdown=ON; 當服務器開啟時重新加載buffer pool的方法是： mysqld --innodb_buffer_pool_load_at_startup=ON; 默認情況下innodb_buffer_pool_dump_at_shutdown和innodb_buffer_pool_load_at_startup兩個配置是開啟狀態 在關閉MySQL時，會把內存中的熱數據保存在磁盤里ib_buffer_pool文件中，位于數據目錄下。 數據庫運行期間保存和重新加載buffer pool的方法是： SET GLOBAL innodb_buffer_pool_dump_now=ON; SET GLOBAL innodb_buffer_pool_load_now=ON; 查看buffer pool保存和重新加載的進度的方法是： mysql> SHOW STATUS LIKE 'Innodb_buffer_pool_dump_status'; 監控buffer pool的狀態情況 通過show engine innodb status命令可以查看buffer pool的運行情況