安裝完MySQL後必須要調整的10項配置

來源:互聯網
上載者:User

標籤:style   http   使用   os   io   檔案   資料   2014   

2014年02月10日11:11 來源:開源中國 BoydWang, 美好的2014, DrZ 編輯:徐志遠

標籤: 配置 , mysql , 資料庫

  【IT168 評論】當我們被人雇來監測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會導致不太可靠(unreliable)因為提交的事務僅僅每秒才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設定或讀文檔找出哪些設定對你來說很重要上。

 

相關文章

聯繫我們

該頁面正文內容均來源於網絡整理,並不代表阿里雲官方的觀點,該頁面所提到的產品和服務也與阿里云無關,如果該頁面內容對您造成了困擾,歡迎寫郵件給我們,收到郵件我們將在5個工作日內處理。

如果您發現本社區中有涉嫌抄襲的內容,歡迎發送郵件至: info-contact@alibabacloud.com 進行舉報並提供相關證據,工作人員會在 5 個工作天內聯絡您,一經查實,本站將立刻刪除涉嫌侵權內容。

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.