mysql的從頭到腳最佳化之伺服器參數的調優

標籤：

一. 說到mysql的調優，有許多的點可以讓我們去做，因此梳理下，一些調優的策略，今天只是總結下伺服器參數的調優

　  其實說到，參數的調優，我的理解就是無非兩點：

• 如果是Innodb的資料庫，innodb_buffer_pool_size就開的儘可能大點，我一般都是開記憶體的80%左右
• 如果是MyISAM的資料庫，key_buffer_size就儘可能的開的大點。　　　　

　  我覺得這是非常重要的兩個參數，下面是重點介紹下，這兩個參數的作用：

　  innodb_buffer_pool_size：

　  該參數是用來快取資料索引以及資料區塊的資料，簡單的來說，當我們操作Innodb資料庫擷取資料的時候，都會在這個記憶體的地區塊中走一遭，擷取資料.

　　Innodb_buffer_pool_size 設定了Innodb儲存引擎的記憶體地區塊的大小，直接關係到Innodb的儲存引擎效能，因此我們如果有足夠大的記憶體，應該將儘可能多的記憶體設定，

　　從而將儘可能多的資料以及索引發到該記憶體資料區塊中.

　　我們可以通過 (Innodb_buffer_pool_read_requests – Innodb_buffer_pool_reads) / Innodb_buffer_pool_read_requests * 100% 計算快取命中率，並根據命中率

　　來調整 innodb_buffer_pool_size 參數大小進行最佳化

　　key_buffer_size:

　　該參數是用來快取資料的索引的，並不快取資料塊，也就是說，我們應該儘可能多的將SQL的過濾條件都在索引中，從而提高緩衝的命中率。

　　這個參數，也是直接關係到MyIsam的效能的，因此，我們也應該將儘可能多的記憶體配置給這個參數。以便儘可能高的提高效能。

　　下面的參數是MySQL的一些常用的參數，可以參考下：

　　query_cache_size/query_cache_type:

　　　　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，只有顯示要求才使用querycache（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）來緩衝了

　　　　

　　　　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 已經

完成了磁碟同步。

　　下面是轉載的一些常用參數的設定：

• 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

mysql的從頭到腳最佳化之伺服器參數的調優