MySQL 效能優化之快取引數優化

神諭丶發表於2015-03-25
MySql優化快取

在平時被問及最多的問題就是關於 MySQL 資料庫效能優化方面的問題，所以最近打算寫一個MySQL資料庫效能優化方面的系列文章，希望對初中級 MySQL DBA 以及其他對 MySQL 效能優化感興趣的朋友們有所幫助。

資料庫屬於 IO 密集型的應用程式，其主要職責就是資料的管理及儲存工作。而我們知道，從記憶體中讀取一個資料庫的時間是微秒級別，而從一塊普通硬碟上讀取一個IO是在毫秒級別，二者相差3個數量級。所以，要優化資料庫，首先第一步需要優化的就是 IO，儘可能將磁碟IO轉化為記憶體IO。本文先從 MySQL 資料庫IO相關引數（快取引數）的角度來看看可以通過哪些引數進行IO優化：

  • query_cache_size/query_cache_type （global）

    • Query cache 作用於整個 MySQL Instance，主要用來快取 MySQL 中的 ResultSet，也就是一條SQL語句執行的結果集，所以僅僅只能針對select語句。當我們開啟了 Query Cache 功能，MySQL在接受到一條select語句的請求後，如果該語句滿足Query Cache的要求（未顯式說明不允許使用Query Cache，或者已經顯式申明需要使用Query Cache），MySQL 會直接根據預先設定好的HASH演算法將接受到的select語句以字串方式進行hash，然後到Query Cache 中直接查詢是否已經快取。也就是說，如果已經在快取中，該select請求就會直接將資料返回，從而省略了後面所有的步驟（如 SQL語句的解析，優化器優化以及向儲存引擎請求資料等），極大的提高效能。

    當然，Query Cache 也有一個致命的缺陷，那就是當某個表的資料有任何任何變化，都會導致所有引用了該表的select語句在Query Cache 中的快取資料失效。所以，當我們的資料變化非常頻繁的情況下，使用Query Cache 可能會得不償失。

    Query Cache的使用需要多個引數配合，其中最為關鍵的是 query_cache_size 和 query_cache_type ，前者設定用於快取 ResultSet 的記憶體大小，後者設定在何場景下使用 Query Cache。在以往的經驗來看，如果不是用來快取基本不變的資料的MySQL資料庫，query_cache_size 一般 256MB 是一個比較合適的大小。當然，這可以通過計算Query Cache的命中率（Qcache_hits/(Qcache_hits+Qcache_inserts)*100)）來進行調整。query_cache_type可以設定為0(OFF)，1(ON)或者2(DEMOND)，分別表示完全不使用query cache，除顯式要求不使用query cache（使用sql_no_cache）之外的所有的select都使用query cache，只有顯示要求才使用query cache（使用sql_cache）。

  • binlog_cache_size （global）

    • Binlog Cache 用於在開啟了二進位制日誌（binlog）記錄功能的環境，是 MySQL 用來提高binlog的記錄效率而設計的一個用於短時間內臨時快取binlog資料的記憶體區域。

    一般來說，如果我們的資料庫中沒有什麼大事務，寫入也不是特別頻繁，2MB～4MB是一個合適的選擇。但是如果我們的資料庫大事務較多，寫入量比較大，可與適當調高binlog_cache_size。同時，我們可以通過binlog_cache_use 以及 binlog_cache_disk_use來分析設定的binlog_cache_size是否足夠，是否有大量的binlog_cache由於記憶體大小不夠而使用臨時檔案（binlog_cache_disk_use）來快取了。

  • key_buffer_size （global）

    • Key Buffer 可能是大家最為熟悉的一個 MySQL 快取引數了，尤其是在 MySQL 沒有更換預設儲存引擎的時候，很多朋友可能會發現，預設的 MySQL 配置檔案中設定最大的一個記憶體引數就是這個引數了。key_buffer_size 引數用來設定用於快取 MyISAM儲存引擎中索引檔案的記憶體區域大小。如果我們有足夠的記憶體，這個快取區域最好是能夠存放下我們所有的 MyISAM 引擎表的所有索引，以儘可能提高效能。

    此外，當我們在使用MyISAM 儲存的時候有一個及其重要的點需要注意，由於 MyISAM 引擎的特性限制了他僅僅只會快取索引塊到記憶體中，而不會快取表資料庫塊。所以，我們的 SQL 一定要儘可能讓過濾條件都在索引中，以便讓快取幫助我們提高查詢效率。

  • bulk_insert_buffer_size （thread）

    • 和key_buffer_size一樣，這個引數同樣也僅作用於使用 MyISAM儲存引擎，用來快取批量插入資料的時候臨時快取寫入資料。當我們使用如下幾種資料寫入語句的時候，會使用這個記憶體區域來快取批量結構的資料以幫助批量寫入資料檔案：

    insert … select …
    insert … values (…) ,(…),(…)…
    load data infile… into… (非空表)

  • innodb_buffer_pool_size（global）

    • 當我們使用InnoDB儲存引擎的時候，innodb_buffer_pool_size 引數可能是影響我們效能的最為關鍵的一個引數了，他用來設定用於快取 InnoDB 索引及資料塊的記憶體區域大小，類似於 MyISAM 儲存引擎的 key_buffer_size 引數，當然，可能更像是 Oracle 的 db_cache_size。簡單來說，當我們操作一個 InnoDB 表的時候，返回的所有資料或者去資料過程中用到的任何一個索引塊，都會在這個記憶體區域中走一遭。

    和key_buffer_size 對於 MyISAM 引擎一樣，innodb_buffer_pool_size 設定了 InnoDB 儲存引擎需求最大的一塊記憶體區域的大小，直接關係到 InnoDB儲存引擎的效能，所以如果我們有足夠的記憶體，儘可將該引數設定到足夠打，將盡可能多的 InnoDB 的索引及資料都放入到該快取區域中，直至全部。

    我們可以通過 (Innodb_buffer_pool_read_requests – Innodb_buffer_pool_reads) / Innodb_buffer_pool_read_requests * 100% 計算快取命中率，並根據命中率來調整 innodb_buffer_pool_size 引數大小進行優化。

  • innodb_additional_mem_pool_size（global）

    • 這個引數我們平時調整的可能不是太多，很多人都使用了預設值，可能很多人都不是太熟悉這個引數的作用。innodb_additional_mem_pool_size 設定了InnoDB儲存引擎用來存放資料字典資訊以及一些內部資料結構的記憶體空間大小，所以當我們一個MySQL Instance中的資料庫物件非常多的時候，是需要適當調整該引數的大小以確保所有資料都能存放在記憶體中提高訪問效率的。

    這個引數大小是否足夠還是比較容易知道的，因為當過小的時候，MySQL 會記錄 Warning 資訊到資料庫的 error log 中，這時候你就知道該調整這個引數大小了。

  • innodb_log_buffer_size （global）

    • 這是 InnoDB 儲存引擎的事務日誌所使用的緩衝區。類似於 Binlog Buffer，InnoDB 在寫事務日誌的時候，為了提高效能，也是先將資訊寫入 Innofb Log Buffer 中，當滿足 innodb_flush_log_trx_commit 引數所設定的相應條件（或者日誌緩衝區寫滿）之後，才會將日誌寫到檔案（或者同步到磁碟）中。可以通過 innodb_log_buffer_size 引數設定其可以使用的最大記憶體空間。
    注：innodb_flush_log_trx_commit 引數對 InnoDB Log 的寫入效能有非常關鍵的影響。該引數可以設定為0，1，2，解釋如下：

    0：log buffer中的資料將以每秒一次的頻率寫入到log file中，且同時會進行檔案系統到磁碟的同步操作，但是每個事務的commit並不會觸發任何log buffer 到log file的重新整理或者檔案系統到磁碟的重新整理操作；
    1：在每次事務提交的時候將log buffer 中的資料都會寫入到log file，同時也會觸發檔案系統到磁碟的同步；
    2：事務提交會觸發log buffer 到log file的重新整理，但並不會觸發磁碟檔案系統到磁碟的同步。此外，每秒會有一次檔案系統到磁碟同步操作。

    此外，MySQL文件中還提到，這幾種設定中的每秒同步一次的機制，可能並不會完全確保非常準確的每秒就一定會發生同步，還取決於程式排程的問題。實際上，InnoDB 能否真正滿足此引數所設定值代表的意義正常 Recovery 還是受到了不同 OS 下檔案系統以及磁碟本身的限制，可能有些時候在並沒有真正完成磁碟同步的情況下也會告訴 mysqld 已經完成了磁碟同步。

  • innodb_max_dirty_pages_pct （global）

    • 這個引數和上面的各個引數不同，他不是用來設定用於快取某種資料的記憶體大小的一個引數，而是用來控制在 InnoDB Buffer Pool 中可以不用寫入資料檔案中的Dirty Page 的比例（已經被修但還沒有從記憶體中寫入到資料檔案的髒資料）。這個比例值越大，從記憶體到磁碟的寫入操作就會相對減少，所以能夠一定程度下減少寫入操作的磁碟IO。

    但是，如果這個比例值過大，當資料庫 Crash 之後重啟的時間可能就會很長，因為會有大量的事務資料需要從日誌檔案恢復出來寫入資料檔案中。同時，過大的比例值同時可能也會造成在達到比例設定上限後的 flush 操作“過猛”而導致效能波動很大。

上面這幾個引數是 MySQL 中為了減少磁碟物理IO而設計的主要引數，對 MySQL 的效能起到了至關重要的作用。
—EOF—
按照 mcsrainbow 朋友的要求，這裡列一下根據以往經驗得到的相關引數的建議值：
  • query_cache_type : 如果全部使用innodb儲存引擎，建議為0，如果使用MyISAM 儲存引擎，建議為2，同時在SQL語句中顯式控制是否是喲你gquery cache
  • query_cache_size: 根據 命中率（Qcache_hits/(Qcache_hits+Qcache_inserts)*100)）進行調整，一般不建議太大，256MB可能已經差不多了，大型的配置型靜態資料可適當調大
  • binlog_cache_size: 一般環境2MB～4MB是一個合適的選擇，事務較大且寫入頻繁的資料庫環境可以適當調大，但不建議超過32MB
  • key_buffer_size: 如果不使用MyISAM儲存引擎，16MB足以，用來快取一些系統表資訊等。如果使用 MyISAM儲存引擎，在記憶體允許的情況下，儘可能將所有索引放入記憶體，簡單來說就是“越大越好”
  • bulk_insert_buffer_size: 如果經常性的需要使用批量插入的特殊語句（上面有說明）來插入資料，可以適當調大該引數至16MB～32MB，不建議繼續增大，某人8MB
  • innodb_buffer_pool_size: 如果不使用InnoDB儲存引擎，可以不用調整這個引數，如果需要使用，在記憶體允許的情況下，儘可能將所有的InnoDB資料檔案存放如記憶體中，同樣將但來說也是“越大越好”
  • innodb_additional_mem_pool_size: 一般的資料庫建議調整到8MB～16MB，如果表特別多，可以調整到32MB，可以根據error log中的資訊判斷是否需要增大
  • innodb_log_buffer_size: 預設是1MB，系的如頻繁的系統可適當增大至4MB～8MB。當然如上面介紹所說，這個引數實際上還和另外的flush引數相關。一般來說不建議超過32MB
  • innodb_max_dirty_pages_pct: 根據以往的經驗，重啟恢復的資料如果要超過1GB的話，啟動速度會比較慢，幾乎難以接受，所以建議不大於 1GB/innodb_buffer_pool_size(GB)*100 這個值。當然，如果你能夠忍受啟動時間比較長，而且希望儘量減少記憶體至磁碟的flush，可以將這個值調整到90，但不建議超過90

注：以上取值範圍僅僅只是我的根據以往遇到的資料庫場景所得到的一些優化經驗值，並不一定適用於所有場景，所以在實際優化過程中還需要大家自己不斷的調整分析，也歡迎大家隨時通過 Mail 與我聯絡溝通交流優化或者是架構方面的技術，一起探討相互學習。



作者：Sky.Jian | 可以任意轉載, 但轉載時務必以超連結形式標明文章原始出處 和 作者資訊 及 版權宣告 
連結：http://isky000.com/database/mysql-perfornamce-tuning-cache-parameter 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29773961/viewspace-1472861/，如需轉載，請註明出處，否則將追究法律責任。

相關文章