安裝MySQL后，需要調(diào)整的10個(gè)性能配置項(xiàng)

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

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

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

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

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

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

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

innodb_buffer_pool_size：

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

innodb_log_file_size：

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

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

max_connections：

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

InnoDB 設(shè)置

從 MySQL 5.5 開(kāi)始，InnoDB 成為了默認(rèn)的存儲(chǔ)引擎，并且它的使用頻率比其他存儲(chǔ)引擎的要多得多。這就是要認(rèn)真配置它的原因。

innodb_file_per_table：

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

在 MySQL 5.6 及之后的版本中，這個(gè)配置項(xiàng)是默認(rèn)開(kāi)啟的，因此多數(shù)情況下，你無(wú)需操作。對(duì)于早期的 MySQL 版本，需要在啟動(dòng)前把它設(shè)置成 ON ，因?yàn)樗粚?duì)新創(chuàng)建的表有影響。

innodb_flush_log_at_trx_commit：

默認(rèn)值為 1，表示 InnoDB 完全支持 ACID 特性。例如在在一個(gè)主節(jié)點(diǎn)上，你主要關(guān)注數(shù)據(jù)安全性，這是最好的設(shè)置值。然而它會(huì)對(duì)速度緩慢的磁盤系統(tǒng)造成很大的開(kāi)銷，因?yàn)槊看螌⒏淖兯⑿碌?redo 日志的時(shí)候，都需要額外的 fsync 操作。設(shè)置為 2，可靠性會(huì)差一點(diǎn)，因?yàn)橐烟峤坏氖聞?wù)只會(huì) 1 秒鐘刷新一次到 redo 日志，但在某些情況下，對(duì)一個(gè)主節(jié)點(diǎn)而言，這仍然是可以接受的，而且對(duì)于復(fù)制關(guān)系的從庫(kù)來(lái)說(shuō)，這是一個(gè)很好的值。設(shè)置為 0，速度更快，但是在遇到崩潰的時(shí)候很可能會(huì)丟失一些數(shù)據(jù)，這只對(duì)從庫(kù)是一個(gè)好的設(shè)置值。

innodb_flush_method：

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

innodb_log_buffer_size：

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

其他設(shè)置

query_cache_size：

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

log_bin：

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

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

skip_name_resolve：

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

結(jié)論

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

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