MySQL安裝後需要調整什(shén)麽?

面對(duì)MySQL的(de)DBA或者做(zuò)MySQL性能相關的(de)工作的(de)人(rén)，我最喜歡問的(de)問題是，在MySQL服務器安裝後，需要調整什(shén)麽，假設是以缺省的(de)設置安裝的(de)。

我很驚訝有非常多(duō)的(de)人(rén)沒有合理(lǐ)的(de)回答(dá)，很多(duō)的(de)MySQL服務器都在缺省的(de)配置下(xià)運行。

盡管可(kě)以調整非常多(duō)的(de)MySQL服務器變量，但是在通(tōng)常情況下(xià)隻有少數的(de)變量是真正重要的(de)。在設置完這(zhè)些變量以後，其他(tā)變量的(de)改動通(tōng)常隻能帶來(lái)相對(duì)有限的(de)性能改善。

key_buffer_size ---- 非常重要，如果使用(yòng)MyISAM表。如果隻使用(yòng)MyISAM表，那麽把它的(de)值設置爲可(kě)用(yòng)内存的(de)30%到40%。恰當的(de)大(dà)小依賴索引的(de)數量、數據量和(hé)負載 ----記住MyISAM使用(yòng)操作系統的(de)cache去緩存數據，所以也(yě)需要爲它留出内存，而且數據通(tōng)常比索引要大(dà)很多(duō)。然而需要查看是否所有的(de) key_buffer總是在被使用(yòng) ---- key_buffer爲4G而.MYI文件隻有1G的(de)情況并不罕見。這(zhè)樣就有些浪費了(le)。如果隻是使用(yòng)很少的(de)MyISAM表，希望它的(de)值小一些，但是仍然至少要設成16到32M，用(yòng)于臨時(shí)表（占用(yòng)硬盤的(de)）的(de)索引。

innodb_buffer_pool_size ---- 非常重要，如果使用(yòng)Innodb表。相對(duì)于MyISAM表而言，Innodb表對(duì)buffer size的(de)大(dà)小更敏感。在處理(lǐ)大(dà)的(de)數據集（data set）時(shí)，使用(yòng)缺省的(de)key_buffer_size和(hé)innodb_buffer_pool_size，MyISAM可(kě)能正常工作，而Innodb可(kě)能就是慢(màn)得(de)像爬一樣了(le)。同時(shí)Innodb buffer pool緩存了(le)數據和(hé)索引頁，因此不需要爲操作系統的(de)緩存留空間，在隻用(yòng)Innodb的(de)數據庫服務器上，可(kě)以設成占内存的(de)70%到80%。上面 key_buffer的(de)規則也(yě)同樣适用(yòng) ---- 如果隻有小的(de)數據集，而且也(yě)不會戲劇性地增大(dà)，那麽不要把innodb_buffer_pool_size設得(de)過大(dà)。因爲可(kě)以更好地使用(yòng)多(duō)餘的(de)内存。

innodb_additional_pool_size ---- 這(zhè)個(gè)變量并不太影(yǐng)響性能，至少在有像樣的(de)（decent）内存分(fēn)配的(de)操作系統中是這(zhè)樣。但是仍然需要至少設爲20MB（有時(shí)候更大(dà)），是Innodb分(fēn)配出來(lái)用(yòng)于處理(lǐ)一些雜(zá)事的(de)。

innodb_log_file_size ---- 對(duì)于以寫操作爲主的(de)負載(workload)非常重要，特别是數據集很大(dà)的(de)時(shí)候。較大(dà)的(de)值會提高(gāo)性能，但增加恢複的(de)時(shí)間。因此需要謹慎。我通(tōng)常依據服務器的(de)大(dà)小（server size）設置爲64M到512M。

innodb_log_buffer_size ---- 缺省值在中等數量的(de)寫操作和(hé)短的(de)事務的(de)大(dà)多(duō)數負載情況下(xià)是夠用(yòng)的(de)。如果有大(dà)量的(de)UPDATE或者大(dà)量地使用(yòng)blob，可(kě)能需要增加它的(de)值。不要把它的(de)值設得(de)過多(duō)，否則會浪費内存--log buffer至少每秒刷新一次，沒有必要使用(yòng)超過一秒鐘(zhōng)所需要的(de)内存。8MB到16MB通(tōng)常是足夠的(de)。小一些的(de)安裝應該使用(yòng)更小的(de)值。

innodb_flush_logs_at_trx_commit ---- 爲Innodb比MyISAM慢(màn)100倍而哭泣？可(kě)能忘記了(le)調整這(zhè)個(gè)值。缺省值是1，即每次事務提交時(shí)都會把日志刷新到磁盤上，非常耗資源，特别是沒有電池備份的(de)cache時(shí)。很多(duō)應用(yòng)程序，特别是那些從MyISAM表移植過來(lái)的(de)，應該把它設成2。意味著(zhe)隻把日志刷新到操作系統的(de)cache，而不刷新到磁盤。此時(shí)，日志仍然會每秒一次刷新到磁盤上，因此通(tōng)常不會丢失超過1到2秒的(de)更新。設成0會更快(kuài)一些，但安全性差一些，在MySQL服務崩潰的(de)時(shí)候，會丢失事務。設成2隻會在操作系統崩潰的(de)時(shí)候丢失數據。

table_cache ---- 打開表是昂貴的(de)（耗資源）。例如，MyISAM表在MYI文件頭做(zuò)标記以标明(míng)哪些表正在使用(yòng)。您不會希望這(zhè)樣的(de)操作頻(pín)繁發生，通(tōng)常最好調整cache 大(dà)小，使其能夠滿足大(dà)多(duō)數打開的(de)表的(de)需要。它使用(yòng)了(le)一些操作系統的(de)資源和(hé)内存，但是對(duì)于現代的(de)硬件水(shuǐ)平來(lái)說通(tōng)常不是問題。對(duì)于一個(gè)使用(yòng)幾百個(gè)表的(de)應用(yòng)， 1024是一個(gè)合适的(de)值（注意每個(gè)連接需要各自的(de)緩存）。如果有非常多(duō)的(de)連接或者非常多(duō)的(de)表，則需要增大(dà)它的(de)值。我曾經看到過使用(yòng)超過100000的(de)值。

thread_cache ---- 線程創建/銷毀是昂貴的(de)，它在每次連接和(hé)斷開連接時(shí)發生。我通(tōng)常把這(zhè)個(gè)值至少設成16。如果應用(yòng)有時(shí)會有大(dà)量的(de)并發連接，并且可(kě)以看到 threads_created變量迅速增長(cháng)，我就把它的(de)值調高(gāo)。目标是在通(tōng)常的(de)操作中不要有線程的(de)創建。

query_cache ---- 如果應用(yòng)是以讀爲主的(de)，并且沒有應用(yòng)級的(de)緩存，那麽它會有很大(dà)幫助。不要把它設得(de)過大(dà)，因爲它的(de)維護可(kě)能會導緻性能下(xià)降。通(tōng)常會設置在32M到 512M之間。設置好後，經過一段時(shí)間要進行檢查，看看是否合适。For certain workloads cache hit ratio is lower than would justify having it enabled.（這(zhè)句不會翻譯）

注意：就像看到的(de)，上面所說的(de)都是全局變量。這(zhè)些變量依賴硬件和(hé)存儲引擎的(de)使用(yòng)，而會話(huà)級的(de)變量（per session variables）則與特定的(de)訪問量(workload)相關。如果隻是一些簡單的(de)查詢，就沒有必要增加sort_buffer_size，即使有 64G的(de)内存讓您去浪費。而且這(zhè)樣做(zuò)還(hái)可(kě)能降低性能。我通(tōng)常把調整會話(huà)級的(de)變量放在第二步，在我分(fēn)析了(le)訪問量（或負載）之後。此外在MySQL分(fēn)發版中包含了(le)一些my.cnf文件的(de)例子，可(kě)以作爲非常好的(de)模闆去使用(yòng)。如果能夠恰當地從中選擇一個(gè)，通(tōng)常會比缺省值要好。