MySQL安裝後需要調整什麼(轉)

MySQL安裝後需要調整什麼(轉)[@more@]面對MySQL的DBA或者做MySQL效能相關的工作的人，我最喜歡問的問題是，在MySQL伺服器安裝後，需要調整什麼，假設是以預設的設定安裝的。

我很驚訝有非常多的人沒有合理的回答，很多的MySQL伺服器都在預設的配置下執行。

儘管你可以調整非常多的MySQL伺服器變數，但是在通常情況下只有少數的變數是真正重要的。在你設定完這些變數以後，其他變數的改動通常只能帶來相對有限的效能改善。

key_buffer_size ---- 非常重要，如果你使用MyISAM表。如果只使用MyISAM表，那麼把它的值設定為可用記憶體的30%到40%。恰當的大小依賴索引的數量、資料量和負載 ----記住MyISAM使用作業系統的cache去快取資料，所以你也需要為它留出記憶體，而且資料通常比索引要大很多。然而需要檢視是否所有的 key_buffer總是在被使用 ---- key_buffer為4G而.MYI檔案只有1G的情況並不罕見。這樣就有些浪費了。如果只是使用很少的MyISAM表，你希望它的值小一些，但是仍然至少要設成16到32M，用於臨時表（佔用硬碟的）的索引。

innodb_buffer_pool_size ---- 非常重要，如果使用Innodb表。相對於MyISAM表而言，Innodb表對buffer size的大小更敏感。在處理大的資料集（data set）時，使用預設的key_buffer_size和innodb_buffer_pool_size，MyISAM可能正常工作，而Innodb可能就是慢得像爬一樣了。同時Innodb buffer pool快取了資料和索引頁，因此你不需要為作業系統的快取留空間，在只用Innodb的資料庫伺服器上，可以設成佔記憶體的70%到80%。上面 key_buffer的規則也同樣適用 ---- 如果只有小的資料集，而且也不會戲劇性地增大，那麼不要把innodb_buffer_pool_size設得過大。因為你可以更好地使用多餘的記憶體。

innodb_additional_pool_size ---- 這個變數並不太影響效能，至少在有像樣的（decent）記憶體分配的作業系統中是這樣。但是仍然需要至少設為20MB（有時候更大），是Innodb分配出來用於處理一些雜事的。

innodb_log_file_size ---- 對於以寫操作為主的負載(workload)非常重要，特別是資料集很大的時候。較大的值會提高效能，但增加恢復的時間。因此需要謹慎。我通常依據伺服器的大小（server size）設定為64M到512M。

innodb_log_buffer_size ---- 預設值在中等數量的寫操作和短的事務的大多數負載情況下是夠用的。如果有大量的UPDATE或者大量地使用blob，你可能需要增加它的值。不要把它的值設得過多，否則會浪費記憶體--log buffer至少每秒重新整理一次，沒有必要使用超過一秒鐘所需要的記憶體。8MB到16MB通常是足夠的。小一些的安裝應該使用更小的值。

innodb_flush_logs_at_trx_commit ---- 為Innodb比MyISAM慢100倍而哭泣？你可能忘記了調整這個值。預設值是1，即每次事務提交時都會把日誌重新整理到磁碟上，非常耗資源，特別是沒有電池備份的cache時。很多應用程式，特別是那些從MyISAM表移植過來的，應該把它設成2。意味著只把日誌重新整理到作業系統的cache，而不重新整理到磁碟。此時，日誌仍然會每秒一次重新整理到磁碟上，因此通常你不會丟失超過1到2秒的更新。設成0會更快一些，但安全性差一些，在MySQL服務崩潰的時候，會丟失事務。設成2只會在作業系統崩潰的時候丟失資料。

table_cache ---- 開啟表是昂貴的（耗資源）。例如，MyISAM表在MYI檔案頭做標記以標明哪些表正在使用。你不會希望這樣的操作頻繁發生，通常最好調整你的cache 大小，使其能夠滿足大多數開啟的表的需要。它使用了一些作業系統的資源和記憶體，但是對於現代的硬體水平來說通常不是問題。對於一個使用幾百個表的應用， 1024是一個合適的值（注意每個連線需要各自的快取）。如果有非常多的連線或者非常多的表，則需要增大它的值。我曾經看到過使用超過100000的值。

thread_cache ---- 執行緒建立/銷燬是昂貴的，它在每次連線和斷開連線時發生。我通常把這個值至少設成16。如果應用有時會有大量的併發連線，並且可以看到 threads_created變數迅速增長，我就把它的值調高。目標是在通常的操作中不要有執行緒的建立。

query_cache ---- 如果你的應用是以讀為主的，並且你沒有應用級的快取，那麼它會有很大幫助。不要把它設得過大，因為它的維護可能會導致效能下降。通常會設定在32M到 512M之間。設定好後，經過一段時間要進行檢查，看看是否合適。For certain workloads cache hit ratio is lower than would justify having it enabled.（這句不會翻譯）

注意：就像你看到的，上面所說的都是全域性變數。這些變數依賴硬體和儲存引擎的使用，而會話級的變數（per session variables）則與特定的訪問量(workload)相關。如果只是一些簡單的查詢，就沒有必要增加sort_buffer_size，即使有 64G的記憶體讓你去浪費。而且這樣做還可能降低效能。我通常把調整會話級的變數放在第二步，在我分析了訪問量（或負載）之後。

此外在MySQL分發版中包含了一些my.cnf檔案的例子，可以作為非常好的模板去使用。如果你能夠恰當地從中選擇一個，通常會比預設值要好。