基本配置

你必要經常察看以下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設置或讀文檔找出哪些設置對你來說很重要上.