在本博客中，我們將和大家討論下 MySQL 數(shù)據(jù)庫安裝后，建議調(diào)整的十個性能設(shè)置參數(shù)。

通常情況下，當我們需要進行 MySQL 性能審計時，我們將審查 MySQL 配置并提出改進建議。在大多數(shù)情況下，我們只建議安裝后更改一些核心的 MySQL 性能調(diào)優(yōu)參數(shù)，即使有數(shù)百個選項可用。這篇文章的目的是給你列出一些最關(guān)鍵的參數(shù)設(shè)置，并告訴你如何去調(diào)整它們。

在開始調(diào)整之前

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

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

基礎(chǔ)設(shè)置

這里主要講解 3 個非常重要的 MySQL 性能配置項，你應(yīng)該經(jīng)常會看到這些參數(shù)。如果你沒有調(diào)整，很可能會遇到問題。

innodb_buffer_pool_size：

這是任何使用 InnoDB 存儲引擎的 MySQL 在安裝后第一個應(yīng)該要查看的配置。Buffer pool 是用來緩存數(shù)據(jù)和索引的，應(yīng)該分配盡可能大的內(nèi)存，以確保在進行大多數(shù)讀取操作時是讀內(nèi)存而不是讀磁盤。典型的設(shè)置值為 5-6GB(8GB RAM)，20-25G(32GB RAM)，100-120GB(128GB RAM)。

innodb_log_file_size：

這個選項是設(shè)置 redo 日志（重做日志）的大小。redo 日志是用來確保寫入的數(shù)據(jù)能夠快速地寫入，并且持久化，還可以用于崩潰恢復(fù)(crash recovery)。MySQL 5.1 之前，這個選項很難去進行調(diào)整，因為你既想要加大 redo 日志來提高性能，又想要減小 redo 日志來進行快速的崩潰恢復(fù)。幸運的是，自 MySQL 5.5 之后，崩潰恢復(fù)的性能有了很大的提高，現(xiàn)在你可以擁有快速寫入性能的同時，還能滿足快速崩潰恢復(fù)。一直到 MySQL 5.5，redo 日志的總大小被限制在 4GB (默認有 2 個日志文件)。這個在 MySQL 5.6 中被增加了。 

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

max_connections：

如果你經(jīng)常遇到 "Too many connections" 的錯誤，是因為 max_connections 太小了。這個錯誤很常見到，因為應(yīng)用程序沒有正確地關(guān)閉與數(shù)據(jù)庫的連接，你需要設(shè)置連接數(shù)為比默認 151 更大的值。max_connections 設(shè)置過高（如 1000 或更高）的一個主要缺點是當服務(wù)器運行 1000 個或者更多的事務(wù)時，會響應(yīng)緩慢甚至沒有響應(yīng)。在應(yīng)用程序端使用連接池或者在 MySQL 端使用線程池有助于解決這個問題。

InnoDB 設(shè)置

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

innodb_file_per_table：

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

在 MySQL 5.6 及之后的版本中，這個配置項是默認開啟的，因此多數(shù)情況下，你無需操作。對于早期的 MySQL 版本，需要在啟動前把它設(shè)置成 ON ，因為它只對新創(chuàng)建的表有影響。

innodb_flush_log_at_trx_commit：

默認值為 1，表示 InnoDB 完全支持 ACID 特性。例如在在一個主節(jié)點上，你主要關(guān)注數(shù)據(jù)安全性，這是最好的設(shè)置值。然而它會對速度緩慢的磁盤系統(tǒng)造成很大的開銷，因為每次將改變刷新到 redo 日志的時候，都需要額外的 fsync 操作。設(shè)置為 2，可靠性會差一點，因為已提交的事務(wù)只會 1 秒鐘刷新一次到 redo 日志，但在某些情況下，對一個主節(jié)點而言，這仍然是可以接受的，而且對于復(fù)制關(guān)系的從庫來說，這是一個很好的值。設(shè)置為 0，速度更快，但是在遇到崩潰的時候很可能會丟失一些數(shù)據(jù)，這只對從庫是一個好的設(shè)置值。

innodb_flush_method：

這個設(shè)置項決定了數(shù)據(jù)和日志刷新到磁盤的方式。當服務(wù)器硬件有 RAID 控制器、斷電保護、采取 write-back 緩存機制的時候，最常用的值是 O_DIRECT；其他大多數(shù)場景使用默認值 fdatasync。sysbench 是一個幫助你在這兩個值之間做出選擇好工具。

innodb_log_buffer_size：

這個設(shè)置項用來設(shè)置緩存還沒有提交的事務(wù)的緩沖區(qū)的大小。默認值(1MB) 一般是夠用的，但一旦事務(wù)之中帶有大 blob/text 字段，這個緩沖區(qū)會被很快填滿，并引起額外的 I/O 負載。看看 innodb_log_waits 這個狀態(tài)變量的值，如果不是 0 的話，需要增加 innodb_log_buffer_size。

其他設(shè)置

query_cache_size：

大家都知道查詢緩存是一個瓶頸，即使在并發(fā)量不高的時候也會出現(xiàn)。最好的設(shè)置就是在第一天使用時就禁用查詢緩存(query_cache_size = 0)  ，該選項在 MySQL 5.6 后是默認禁用的，我們可以通過其他途徑來提高查詢速度: 設(shè)計好的索引，增長讀寫分離，或者使用額外的緩存 (memcache or redis for instance)。如果您的 MySQL 已經(jīng)啟用了查詢緩存并且從沒有發(fā)現(xiàn)過問題， 那么查詢緩存可能是對你有益的，這個時候如果你想禁用它的時候應(yīng)該小心操作。

log_bin：

如果要讓一個節(jié)點做為復(fù)制關(guān)系中的主節(jié)點，啟用二進制日志（binary log）是必須的。同時需要設(shè)置全局唯一的 server_id。如果是單實例數(shù)據(jù)庫，如果你要將數(shù)據(jù)恢復(fù)到之前時間點（使用最新的備份restore，然后使用binlog進行recover），那么就需要二進制日志。二進制日志一旦創(chuàng)建，會被永久保存，所以如果不想耗盡磁盤空間，應(yīng)該使用 PURGE BINARY LOGS 清理舊的二進制日志文件，或者設(shè)置 expire_logs_days 選項指定多少天之后，自動清理過期的二進制日志。

二進制文件記錄是需要消耗資源的，因此在主從復(fù)制環(huán)境中，如果備庫不需要 Binlog ，就可以禁用掉。

skip_name_resolve：

當一個客戶端連接上來的時候，服務(wù)端會執(zhí)行主機名解釋操作，當 DNS 很慢時，建立的連接也會很慢。因此建議在啟動的時候設(shè)置 skip-name-resolve 來禁用 DNS 查找。唯一的局限是 GRANT 語句僅且僅能使用 IP 地址，所以，在已有系統(tǒng)中添加這個選項時需要格外小心。

結(jié)論

當然，根據(jù)你的負載和硬件的實際情況，還有其他的設(shè)置能夠起到調(diào)優(yōu)的作用：例如在小內(nèi)存、高速磁盤，高并發(fā)，寫密集型的負載下，需要特定的調(diào)優(yōu)。不過本文的目的是給出幾個 MySQL 的性能調(diào)優(yōu)配置項，讓你快速配置一個合理的 MySQL 配置文件，并且了解哪些參數(shù)對你很重要，而不需要花費大量時候去閱讀官方文檔。

以上就是安裝MySQL后，需要調(diào)整的10個性能配置項的詳細內(nèi)容，更多關(guān)于MySQL 性能配置項的資料請關(guān)注腳本之家其它相關(guān)文章！

最新評論