> 本文翻譯自 Percona 官方部落格，適用於 MySQL 5.6 及 5.7 版本。
> 作者：Stephane Combaudon
> 原文：
> [https://www.percona.com/blog/2014/01/28/10-mysql-performance-tuning-settings-after-installation/](https://www.percona.com/blog/2014/01/28/10-mysql-performance-tuning-settings-after-installation/)

在本部落格中，我們將和大家討論下 MySQL 資料庫安裝後，建議調整的十個效能設定引數。

通常情況下，當我們需要進行 MySQL 效能審計時，我們將審查 MySQL 配置並提出改進建議。在大多數情況下，我們只建議安裝後更改一些核心的 MySQL 效能調優引數，即使有數百個選項可用。這篇文章的目的是給你列出一些最關鍵的引數設定，並告訴你如何去調整它們。

#### 在開始調整之前

即使是有經驗的人也會犯一些會造成許多麻煩的錯誤。因此，在應用本文推薦的配置項之前，請牢記下面的幾項：

- 每次更改一個設定！這是驗證設定是否有效的唯一方法。
- 大多數配置項可以在執行時使用 SET GLOBAL 命令來修改。這種方式非常方便，並且如果修改後出現問題，還能馬上恢復原設定。但到最後，仍然需要把這個改變寫到配置檔案中，使之永久生效。
- 有時候即使 MySQL 重啟後，配置檔案中的引數也不生效。這時候你需要考慮：你使用正確的配置檔案了嗎？你把這個引數放在正確的地方了嗎？（在這篇文章中的所有配置都屬於[mysqld]部分）
- 如在更改配置後資料庫無法啟動，需要檢查是否使用正確的單位？例如， innodb_buffer_pool_size 的單位是 byte，而 max_connection 是沒有單位的。
- 在配置檔案中不允許重複設定。如果要跟蹤配置的更改，請使用版本控制。
- 不要做天真的數學演算法，比如“我的新伺服器的 RAM 是舊的 2 倍，因此可以把所有的配置項的值都設定成之前的 2 倍”。

#### 基礎設定

這裡主要講解 3 個非常重要的 MySQL 效能配置項，你應該經常會看到這些引數。如果你沒有調整，很可能會遇到問題。

**innodb_buffer_pool_size**：

這是任何使用 InnoDB 儲存引擎的 MySQL 在安裝後第一個應該要檢視的配置。Buffer pool 是用來快取資料和索引的，應該分配儘可能大的記憶體，以確保在進行大多數讀取操作時是讀記憶體而不是讀磁碟。典型的設定值為 5-6GB(8GB RAM)，20-25G(32GB RAM)，100-120GB(128GB RAM)。

**innodb_log_file_size**：

這個選項是設定 redo 日誌（重做日誌）的大小。redo 日誌是用來確保寫入的資料能夠快速地寫入，並且持久化，還可以用於崩潰恢復(crash recovery)。MySQL 5.1 之前，這個選項很難去進行調整，因為你既想要加大 redo 日誌來提高效能，又想要減小 redo 日誌來進行快速的崩潰恢復。幸運的是，自 MySQL 5.5 之後，崩潰恢復的效能有了很大的提高，現在你可以擁有快速寫入效能的同時，還能滿足快速崩潰恢復。一直到 MySQL 5.5，redo 日誌的總大小被限制在 4GB (預設有 2 個日誌檔案)。這個在 MySQL 5.6 中被增加了。

啟動的時候設定 innodb_log_file_size = 512M（也就是 1GB 大小的 redo 日誌），這樣可以提供充足的寫空間。如果你知道你的應用是頻繁寫入的，還可以再增大些。

**max_connections**：

如果你經常遇到 "Too many connections" 的錯誤，是因為 max_connections 太小了。這個錯誤很常見到，因為應用程式沒有正確地關閉與資料庫的連線，你需要設定連線數為比預設 151 更大的值。max_connections 設定過高（如 1000 或更高）的一個主要缺點是當伺服器執行 1000 個或者更多的事務時，會響應緩慢甚至沒有響應。在應用程式端使用連線池或者在 MySQL 端使用執行緒池有助於解決這個問題。

#### InnoDB 設定

從 MySQL 5.5 開始，InnoDB 成為了預設的儲存引擎，並且它的使用頻率比其他儲存引擎的要多得多。這就是要認真配置它的原因。

**innodb_file_per_table**：

這個配置項會決定 InnoDB 是使用共享表空間(innodb_file_per_table = OFF) 來儲存資料和索引，還是為每個表使用一個單獨的 ibd 檔案(innodb_file_per_table= ON)。對每個表使用一個檔案的方式，在 drop, truncate, 或者重建表的時候，會回收這個表空間。在一些高階特性，如壓縮的時候也需要開啟使用獨立表空間。然而這個選項卻不能帶來效能的提升。

在 MySQL 5.6 及之後的版本中，這個配置項是預設開啟的，因此多數情況下，你無需操作。對於早期的 MySQL 版本，需要在啟動前把它設定成 ON ，因為它只對新建立的表有影響。

**innodb_flush_log_at_trx_commit**：

預設值為 1，表示 InnoDB 完全支援 ACID 特性。例如在在一個主節點上，你主要關注資料安全性，這是最好的設定值。然而它會對速度緩慢的磁碟系統造成很大的開銷，因為每次將改變重新整理到 redo 日誌的時候，都需要額外的 fsync 操作。設定為 2，可靠性會差一點，因為已提交的事務只會 1 秒鐘重新整理一次到 redo 日誌，但在某些情況下，對一個主節點而言，這仍然是可以接受的，而且對於複製關係的從庫來說，這是一個很好的值。設定為 0，速度更快，但是在遇到崩潰的時候很可能會丟失一些資料，這隻對從庫是一個好的設定值。

**innodb_flush_method**：

這個設定項決定了資料和日誌重新整理到磁碟的方式。當伺服器硬體有 RAID 控制器、斷電保護、採取 write-back 快取機制的時候，最常用的值是 O_DIRECT；其他大多數場景使用預設值 fdatasync。sysbench 是一個幫助你在這兩個值之間做出選擇好工具。

**innodb_log_buffer_size**：

這個設定項用來設定快取還沒有提交的事務的緩衝區的大小。預設值(1MB) 一般是夠用的，但一旦事務之中帶有大 blob/text 欄位，這個緩衝區會被很快填滿，並引起額外的 I/O 負載。看看 innodb_log_waits 這個狀態變數的值，如果不是 0 的話，需要增加 innodb_log_buffer_size。

#### 其他設定

**query_cache_size**：

大家都知道查詢快取是一個瓶頸，即使在併發量不高的時候也會出現。 最好的設定就是在第一天使用時就禁用查詢快取(query_cache_size = 0)  ，該選項在 MySQL 5.6 後是預設禁用的，我們可以透過其他途徑來提高查詢速度: 設計好的索引，增長讀寫分離，或者使用額外的快取 (memcache or redis for instance)。如果您的 MySQL 已經啟用了查詢快取並且從沒有發現過問題， 那麼查詢快取可能是對你有益的，這個時候如果你想禁用它的時候應該小心操作。

**log_bin**：

如果要讓一個節點做為複製關係中的主節點，啟用二進位制日誌（binary log）是必須的。同時需要設定全域性唯一的 server_id。 如果是單例項資料庫，如果你要將資料恢復到之前時間點（使用最新的備份restore，然後使用binlog進行recover），那麼就需要二進位制日誌。二進位制日誌一旦建立，會被永久儲存，所以如果不想耗盡磁碟空間，應該使用 PURGE BINARY LOGS 清理舊的二進位制日誌檔案，或者設定 expire_logs_days 選項指定多少天之後，自動清理過期的二進位制日誌。

二進位制檔案記錄是需要消耗資源的，因此在主從複製環境中，如果備庫不需要 Binlog ，就可以禁用掉。

**skip_name_resolve**：

當一個客戶端連線上來的時候，服務端會執行主機名解釋操作，當 DNS 很慢時，建立的連線也會很慢。因此建議在啟動的時候設定 skip-name-resolve 來禁用 DNS 查詢。唯一的侷限是 GRANT 語句僅且僅能使用 IP 地址，所以，在已有系統中新增這個選項時需要格外小心。

#### 結論

當然，根據你的負載和硬體的實際情況，還有其他的設定能夠起到調優的作用：例如在小記憶體、高速磁碟，高併發，寫密集型的負載下，需要特定的調優。不過本文的目的是給出幾個 MySQL 的效能調優配置項，讓你快速配置一個合理的 MySQL 配置檔案，並且瞭解哪些引數對你很重要，而不需要花費大量時候去閱讀官方文件。

安裝MySQL後，需要調整的10個效能配置項

相關文章