淺談mysql innodb緩衝策略

標籤：

淺談mysql innodb緩衝策略：

 

 The InnoDB Buffer Pool

   Innodb 持有一個儲存地區叫做buffer pool是為了在記憶體中快取資料和索引，知道innodb bufferpool怎麼工作，和利用它讀取頻繁訪問的資料，是mysql最佳化重要的方面。

   理想狀況下，把bufferpool的大小調整到足夠大，留下足夠的記憶體空間給其他該伺服器上的進程（使其無缺頁即可）。bufferpool越大，innodb 月表現為記憶體型資料庫，從硬碟上一次讀取資料，之後並成了從記憶體中讀取資料。buffer pool甚至緩衝那些因為insert,update操作而改變的資料（insert buffer），所以隨機磁碟寫可以聚集在一塊得到更好的效能。

   innodb 把緩衝池作為鏈式管理，利用LRU（least recently used）演算法，當添加新block到pool中時（無空間），innodb 替換（驅逐）一個最近最少使用的block，然後把新的block添加到鏈表的中間，"midpoint insertion strategy"策略把鏈表看出兩條子鏈。

     1：在鏈表的頭部，是由一些“NEW”(or "young")block 組成的最近剛被訪問的子鏈；

     2：在鏈表的尾部，是由一些‘old‘ block組成的最近沒被訪問（或者最少被訪問的）的子鏈；

 

 該演算法使大量查詢 blocks 保持在  new sublist. old sublist 持有最少使用的 blocks;這些blocks將成為替換或驅逐的候選者。

     1：3/8 的buffer pool 的大小分配給old sublist

     2: 鏈表的 midpoint （中間插入點） 是new sublist 尾部和 old sublist頭部彙總的地方

     3：當 innodb 讀取一個block進 buffer pool時，插入到midpoint(old sublist 的頭部)，block被讀取發生在 用戶端操作,eg: sql查詢，或者innodb特性 readahead(預讀);

     4:當訪問在old sublist中一個 block時，使其變成‘young‘，把它移動到 buffer pool的頭部（new sublist的頭部），如果該block 被讀取是因為用戶端sql查詢，則第一次訪問立即發生，並且該block變成‘young‘。如果該block被讀取是因為read ahead,第一次唄訪問不會發生，並且有可能在該Block被替換之前根本不能發生）；

     5：隨著資料庫操作，在buffer pool 中的沒被訪問的blocks（年紀大的）被移動到鏈表的尾部.在old sublist中的blocks 比插入在midpoint上的block老，最終，一個Block一段長時間未被使用會到達old sublist的尾部會被替換。

     預設情況下，被讀取的blocks會立即移動到 NEW sublist 的 head,同時意味著他們待著buffer pool中很長一段時間。當掃表時（eg, mysqldump 操作，或者 沒有where語句的select操作 ）可以使大量的blocks  push into buffer pool中,並且驅逐大量的older 資料，即使那些所謂剛加入的 new blocks 不會再次被訪問，相同的，read ahead 後台線程一次載入大量的blocks  ，這些情況使經常被訪問的blocks push into 到 old sublist中，然後它們成為被驅逐的候選者。

  一些innodb 系統變數控制著buffer pool的大小和使你調整LRU演算法

    1：innodb buffer pool size

       指明Buffer pool的大小，如果你的buffer pool 空間小，並且有充足的空間，使pool大點可以減小磁碟IO的次數來提高效能；

    2： innodb buffer pool instances : 分成多個獨立的地區，各個地區相同，來減少在並發記憶體讀寫操作的競爭；

    3：innodb old blocks pct:預設3/8;

    4:  innodb old blocks time: 指定多長時間以毫秒為單位(ms),block插入到老子列表必須呆在那裡第一次訪問後多久,才能搬到新的子列表（解決預讀時，緩衝汙染問題）；

 

  檢查查詢快取是否存在於你的MySQL伺服器：

mysql> show variables like ‘have_query_cache‘;+------------------+-------+| Variable_name    | Value |+------------------+-------+| have_query_cache | YES   |+------------------+-------+1 row in set (0.00 sec)

監控查詢快取的效能,使用顯示狀態查看緩衝狀態變數:

mysql> show status like ‘qcache%‘;+-------------------------+----------+| Variable_name           | Value    |+-------------------------+----------+| Qcache_free_blocks      | 1        || Qcache_free_memory      | 16768376 || Qcache_hits             | 0        || Qcache_inserts          | 0        || Qcache_lowmem_prunes    | 0        || Qcache_not_cached       | 227      || Qcache_queries_in_cache | 0        || Qcache_total_blocks     | 1        |+-------------------------+----------+8 rows in set (0.00 sec)

　　

mysql> select count(*) from animals;  +----------+  | count(*) |  +----------+  |        6 |   +----------+  1 row in set (0.00 sec)   --Qcache_hits表示sql查詢在緩衝中命中的累計次數，是累加值。  mysql> SHOW STATUS LIKE ‘Qcache_hits‘;  +---------------+-------+  | Variable_name | Value |  +---------------+-------+  | Qcache_hits   | 0     |  --0次  +---------------+-------+  8 rows in set (0.00 sec)   mysql>  select count(*) from animals;  +----------+  | count(*) |  +----------+  |        6 |   +----------+  1 row in set (0.00 sec)   mysql>  SHOW STATUS LIKE ‘Qcache%‘;  +---------------+-------+  | Variable_name | Value |  +---------------+-------+  | Qcache_hits   | 1     | --表示sql在緩衝中直接得到結果，不需要再去解析  +---------------+-------+  8 rows in set (0.00 sec)   mysql> select count(*) from animals;  +----------+  | count(*) |  +----------+  |        6 |   +----------+  1 row in set (0.00 sec)   mysql> select count(*) from animals;  +----------+  | count(*) |  +----------+  |        6 |   +----------+  1 row in set (0.00 sec)   mysql> SHOW STATUS LIKE ‘Qcache_hits‘;  +---------------+-------+  | Variable_name | Value |  +---------------+-------+  | Qcache_hits   | 3     |    --上面的sql也是是從緩衝中直接取到結果  +---------------+-------+  1 row in set (0.00 sec)   mysql> insert into animals select 9,‘testsds‘ ; --插入資料後，跟這個表所有相關的sql緩衝就會被清空掉  Query OK, 1 row affected (0.00 sec)  Records: 1  Duplicates: 0  Warnings: 0   mysql> select count(*) from animals;  +----------+  | count(*) |  +----------+  |        7 |   +----------+  1 row in set (0.00 sec)   mysql> SHOW STATUS LIKE ‘Qcache_hits‘;  +---------------+-------+  | Variable_name | Value |  +---------------+-------+  | Qcache_hits   | 3    |  --還是等於3，說明上一條sql是沒有直接從緩衝中直接得到的  +---------------+-------+  1 row in set (0.00 sec)   mysql> select count(*) from animals;  +----------+  | count(*) |  +----------+  |        7 |   +----------+  1 row in set (0.00 sec)   mysql> SHOW STATUS LIKE ‘Qcache_hits‘;   +---------------+-------+  | Variable_name | Value |  +---------------+-------+  | Qcache_hits   | 4     |   +---------------+-------+  1 row in set (0.00 sec) 

 

  

淺談mysql innodb緩衝策略