MySQL之—新安裝的MySQL必須調整的10項配置的詳細介紹

 還在為新安裝的mysql服務，不知道修改哪些預設配置而發愁嗎?mysql可調整參數有100多個，到底要立即!馬上!調整哪些最重要的參數?

#這篇文章主要介紹了MySQL優化必須調整的10項配置,使用這些方法可以讓你快速地獲得一個穩健的MySQL配置,需要的朋友可以參考下：

#當我們被人當僱用來監測MySQL效能時，人們希望我們能夠檢視MySQL配置然後給出一些提高建議。許多人事後都非常驚訝，因為我們建議他們只改動幾個設置，即使是這裡有好幾百個配置。這篇文章的目的在於給你一個非常重要的配置清單。

我們已經在幾年前在部落格裡給了這樣的建議，但是MySQL的世界變化實在太快了!

         寫在開始前…

      是經驗老道的人也會犯錯，會造成很多困擾。所以在盲目的運用這些推薦之前，請記住下面的內容：

       一次只改變一個設定!這是測試改變是否有益的唯一方法。

      大多數設定能在運作時使用SET GLOBAL改變。這是非常便捷的方法它能讓你在出問題後快速撤銷變更。但是，要永久生效你需要在設定檔裡做出改變。

       一個變更即使重新啟動了MySQL也沒起作用?

       請確定你使用了正確的設定檔。請確定你把配置放在了正確的區域內(所有這篇文章提到的配置都屬於[mysqld])

        伺服器在改動一個配置後啟不來了：請確定你使用了正確的單位。

        例如，innodb_buffer_pool_size的單位是MB而max_connection是沒有單位的。

        不要在一個設定檔裡出現重複的設定檔。如果你想追蹤改動，請使用版本控制。

不要用天真的計算方法，例如」現在我的伺服器的記憶體是之前的2倍，所以我得把所有數值都改成之前的2倍「。

一、‍基本配置

你需要經常察看以下3個設定項。不然，可能很快就會出問題。

innodb_buffer_pool_size:

這是你安裝完InnoDB後第一個應該設定的選項。

緩衝池是資料和索引快取的地方：這個值越大越好，這能保證你在大多數的讀取操作時使用的是記憶體而不是硬碟。典型的值是5-6GB(8GB記憶體)，20-25GB(32GB記憶體)，100-120GB(128GB記憶體)。

innodb_log_file_size：

這是redo日誌的大小。 redo日誌被用來確保寫入操作快速且可靠並且在崩潰時恢復。

一直到MySQL 5.1，它都很難於調整，因為一方面你想讓它更大來提高效能，另一方面你想讓它更小來使得崩潰後更快恢復。幸運的是從MySQL 5.5之後，崩潰復原的效能的到了很大提升，這樣你就可以同時擁有較高的寫入效能和崩潰復原效能了。一直到MySQL 5.5，redo日誌的總尺寸被限定在4GB(預設可以有2個log檔)。這在MySQL 5.6裡被提高。

一開始就把innodb_log_file_size設定成512M(這樣有1GB的redo日誌)會使你有充裕的寫入操作空間。如果你知道你的應用程式需要頻繁的寫入資料並且你使用的時MySQL 5.6，你可以一開始就把它這是成4G。

max_connections:

如果你常看到‘Too many connections'錯誤，是因為max_connections的值太低了。這非常常見因為應用程式沒有正確的關閉資料庫連接，你需要比預設的 151連接數更大的值。 max_connection值被設高了(例如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或rebuild表時回收磁碟空間。這對於一些高階特性也是有必要的，例如資料壓縮。但是它不會帶來任何性能收益。你不想讓每張表一個文件的主要場景是：有非常多的表(例如10k+)。

MySQL 5.6中，這個屬性預設值是ON，因此大部分情況下你什麼都不需要做。對於之前的版本你必需在載入資料之前將這個屬性設為ON，因為它只對新建立的表有影響。

innodb_flush_log_at_trx_commit：

預設值為1，表示InnoDB完全支援ACID特性。當你的主要關注點是資料安全的時候這個值是最合適的，例如在一個主節點上。但對於磁碟(讀寫)速度較慢的系統，它會帶來很大的開銷，因為每次將改變flush到redo日誌都需要額外的fsyncs。將它的值設為2會導致不太可靠(reliable)因為提交的事務僅每秒才flush一次到redo日誌，但對於某些場景是可以接受的，例如對於主節點的備份節點這個值是可以接受的。如果值為0速度就更快了，但在系統崩潰時可能會遺失一些資料：只適用於備份節點。

innodb_flush_method:

這項設定決定了資料和日誌寫入硬碟的方式。一般來說，如果你有硬體RAID控制器，並且其獨立緩存採用write-back機制，並有著電池斷電保護，那麼應該設置配置為O_DIRECT;否則，大多數情況下應將其設為fdatasync(默認值)。 sysbench是一個可以幫助你決定這個選項的好工具。

innodb_log_buffer_size:

這項配置決定了尚未執行的交易分配的快取。其預設值(1MB)一般來說已經夠用了，但是如果你的事務中包含有二進位大物件或大文字欄位的話，這點快取很快就會被填滿並觸發額外的I/O操作。看看Innodb_log_waits狀態變量，如果它不是0，增加innodb_log_buffer_size。

三、其他設定

query_cache_size:

query cache(查詢快取)是一個眾所周知的瓶頸，甚至在並發並不多的時候也是如此。

最佳選項是將其從一開始就停用，設定query_cache_size = 0(現在MySQL 5.6的預設值)並利用其他方法加速查詢：最佳化索引、增加拷貝分散負載或啟用額外的緩存(例如memcache或redis)。如果你已經為你的應用程式啟用了query cache並且還沒有發現任何問題，query cache可能對你有用。這是如果你想停用它，那就得小心了。

log_bin：

如果你想讓資料庫伺服器充當主節點的備份節點，那麼開啟二進位日誌是必須的。如果這麼做了之後，別忘了設定server_id為一個唯一的值。就算只有一個伺服器，如果你想做基於時間點的資料恢復，這(開啟二進位日誌)也是很有用的：從你最近的備份中恢復(全量備份)，並應用二進位日誌中的修改(增量備份)。二進制日誌一旦建立就將永久保存。所以如果你不想讓磁碟空間耗盡，你可以用 PURGE BINARY LOGS 來清除舊文件，或者設定 expire_logs_days 來指定過多少天日誌將被自動清除。

記錄二進位日誌不是沒有開銷的，所以如果你在一個非主節點的複製節點上不需要它的話，那麼建議關閉這個選項。

skip_name_resolve：

當客戶端連接資料庫伺服器時，伺服器會進行主機名稱解析，並且當DNS很慢時，建立連線也會很慢。因此建議在啟動伺服器時關閉 skip_name_resolve選項而不進行DNS查找。唯一的限制是之後GRANT語句中只能使用IP位址了，因此在新增這項設定到一個已有系統中必須格外小心。

總結

當然還有其他的設定可以起作用，取決於你的負載或硬體：在慢記憶體和快磁碟、高並發和寫密集型負載情況下，你將需要特殊的調整。然而這裡的目標是使得你可以快速地獲得一個穩健的MySQL配置，而不用花太多時間在調整一些無關緊要的MySQL設定或讀取文件找出哪些設定對你來說很重要。

以上是MySQL之—新安裝的MySQL必須調整的10項配置的詳細介紹的詳細內容。更多資訊請關注PHP中文網其他相關文章！