MySQL設定檔my.cnf中文詳解附mysql效能最佳化方法分享

下面先說我的伺服器的硬體以及論壇情況，
CPU: 2顆四核Intel Xeon 2.00GHz
記憶體: 4GB DDR
硬碟: SCSI 146GB
論壇：線上會員 一般在 5000 人左右 – 最高記錄是 13264.
下面，我們根據以上硬體設定結合一份已經做過一次最佳化的my.cnf進行分析說明：有些參數可能還得根據論壇的變化情況以及程式員的程式進行再調整。
[mysqld]
port = 3306
serverid = 1
socket = /tmp/mysql.sock
skip-locking # 避免MySQL的外部鎖定，減少出錯幾率增強穩定性。

skip-name-resolve

禁止MySQL對外部串連進行DNS解析，使用這一選項可以消除MySQL進行DNS解析的時間。但需要注意，如果開啟該選項，則所有遠程主機串連授權都要使用IP地址方式，否則MySQL將無法正常處理串連請求!
back_log = 500
要求 MySQL 能有的串連數量。當主要MySQL線程在一個很短時間內得到非常多的串連請求，這就起作用，然後主線程花些時間(儘管很短)檢查串連並且啟動一個新線程。
back_log值指出在MySQL暫時停止回答新請求之前的短時間內多少個請求可以被存在堆棧中。只有如果期望在一個短時間內有很多串連，你需要增加它，換句話說，這值對到來的TCP/IP串連的偵聽隊列的大小。你的作業系統在這個隊列大小上有它自己的限制。試圖設定back_log高於你的作業系統的限制將是無效的。當你觀察你的主機處理序列表，發現大量 264084 | unauthenticated user | xxx.xxx.xxx.xxx | NULL | Connect | NULL | login | NULL 的待串連進程時，就要加大 back_log 的值了。預設數值是50，我把它改為500。
key_buffer_size = 384M
# key_buffer_size指定用於索引的緩衝區大小，增加它可得到更好處理的索引(對所有讀和多重寫)，到你能負擔得起那樣多。如果你使它太大，系統將開始換頁並且真的變慢了。對於記憶體在4GB左右的伺服器該參數可設定為384M或512M。通過檢查狀態值Key_read_requests和 Key_reads,可以知道key_buffer_size設定是否合理。比例key_reads / key_read_requests應該儘可能的低，至少是1:100，1:1000更好(上述狀態值可以使用SHOW STATUS LIKE ‘key_read%'獲得)。注意：該參數值設定的過大反而會是伺服器整體效率降低!
max_allowed_packet = 32M
增加該變數的值十分安全，這是因為僅當需要時才會分配額外記憶體。例如，僅當你發出長查詢或mysqld必須返回大的結果行時mysqld才會分配更多記憶體。該變數之所以取較小預設值是一種預防措施，以捕獲用戶端和伺服器之間的錯誤資訊包，並確保不會因偶然使用大的資訊包而導致記憶體溢出。
table_cache = 512
table_cache指定表快取的大小。每當MySQL訪問一個表時，如果在表緩衝區中還有空間，該表就被開啟並放入其中，這樣可以更快地訪問表內容。通過檢查峰值時間的狀態值Open_tables和Opened_tables，可以決定是否需要增加table_cache的值。如果你發現 open_tables等於table_cache，並且opened_tables在不斷增長，那麼你就需要增加table_cache的值了(上述狀態值可以使用SHOW STATUS LIKE ‘Open%tables'獲得)。注意，不能盲目地把table_cache設定成很大的值。如果設定得太高，可能會造成檔案描述符不足，從而造成效能不穩定或者串連失敗。
sort_buffer_size = 4M
查詢排序時所能使用的緩衝區大小。注意：該參數對應的分配記憶體是每串連獨佔!如果有100個串連，那麼實際分配的總共排序緩衝區大小為100 × 4 = 400MB。所以，對於記憶體在4GB左右的伺服器推薦設定為4-8M。
read_buffer_size = 4M
讀查詢操作所能使用的緩衝區大小。和sort_buffer_size一樣，該參數對應的分配記憶體也是每串連獨享!
join_buffer_size = 8M
聯集查詢操作所能使用的緩衝區大小，和sort_buffer_size一樣，該參數對應的分配記憶體也是每串連獨享!
myisam_sort_buffer_size = 64M
MyISAM表發生變化時重新排序所需的緩衝
query_cache_size = 64M
指定MySQL查詢緩衝區的大小。可以通過在MySQL控制台執行以下命令觀察：
# > SHOW VARIABLES LIKE ‘%query_cache%'; # > SHOW STATUS LIKE ‘Qcache%'; # 如果Qcache_lowmem_prunes的值非常大，則表明經常出現緩衝不夠的情況;
如果Qcache_hits的值非常大，則表明查詢緩衝使用非常頻繁，如果該值較小反而會影響效率，那麼可以考慮不用查詢緩衝;Qcache_free_blocks，如果該值非常大，則表明緩衝區中片段很多。
thread_cache_size = 64
可以複用的儲存在中的線程的數量。如果有，新的線程從緩衝中取得，當中斷連線的時候如果有空間，客戶的線置在緩衝中。如果有很多新的線程，為了提高效能可以這個變數值。通過比較 Connections 和 Threads_created 狀態的變數，可以看到這個變數的作用
tmp_table_size = 256M
max_connections = 1000
指定MySQL允許的最大串連進程數。如果在訪問論壇時經常出現Too Many Connections的錯誤提示，則需要增大該參數值。
max_connect_errors = 10000000
對於同一主機，如果有超出該參數值個數的中斷錯誤串連，則該主機將被禁止串連。如需對該主機進行解禁，執行：FLUSH HOST;。
wait_timeout = 10
指定一個請求的最大連線時間，對於4GB左右記憶體的伺服器可以設定為5-10。
thread_concurrency = 8
該參數取值為伺服器邏輯CPU數量×2，在本例中，伺服器有2顆物理CPU，而每顆物理CPU又支援H.T超執行緒，所以實際取值為4 × 2 = 8
skip-networking
開啟該選項可以徹底關閉MySQL的TCP/IP串連方式，如果WEB伺服器是以遠端連線的方式訪問MySQL資料庫伺服器則不要開啟該選項!否則將無法正常串連!
long_query_time = 10
log-slow-queries =
log-queries-not-using-indexes
開啟慢查詢日誌( slow query log )
慢查詢日誌對於跟蹤有問題的查詢非常有用。它記錄所有查過long_query_time的查詢，如果需要，還可以記錄不使用索引的記錄。下面是一個慢查詢日誌的例子：
開啟慢查詢日誌，需要設定參數log_slow_queries、long_query_times、log-queries-not-using-indexes。
log_slow_queries指定記錄檔，如果不提供檔案名稱，MySQL將自己產生預設檔案名稱。long_query_times指定慢查詢的閾值，預設是10秒。log-queries-not-using-indexes是4.1.0以後引入的參數，它指示記錄不使用索引的查詢。設定 long_query_time=10
外附上使用show status命令查看mysql狀態相關的值及其含義：
使用show status命令
含義如下:
aborted_clients 用戶端非法中斷串連次數
aborted_connects 串連mysql失敗次數
com_xxx xxx命令執行次數,有很多條
connections 串連mysql的數量
Created_tmp_disk_tables 在磁碟上建立的暫存資料表
Created_tmp_tables 在記憶體裡建立的暫存資料表
Created_tmp_files 臨時檔案數
Key_read_requests The number of requests to read a key block from the cache
Key_reads The number of physical reads of a key block from disk
Max_used_connections 同時使用的串連數
Open_tables 開放的表
Open_files 開放的檔案
Opened_tables 開啟的表
Questions 提交到server的查詢數
Sort_merge_passes 如果這個值很大,應該增加my.cnf中的sort_buffer值
Uptime 伺服器已經工作的秒數
提升效能的建議:
1.如果opened_tables太大,應該把my.cnf中的table_cache變大

2.如果Key_reads太大,則應該把my.cnf中key_buffer_size變大.可以用Key_reads/Key_read_requests計算出cache失敗率

3.如果Handler_read_rnd太大,則你寫的SQL語句裡很多查詢都是要掃描整個表,而沒有發揮索引的鍵的作用

4.如果Threads_created太大,就要增加my.cnf中thread_cache_size的值.可以用Threads_created/Connections計算cache命中率

5.如果Created_tmp_disk_tables太大,就要增加my.cnf中tmp_table_size的值,用基於記憶體的暫存資料表代替基於磁碟的

===================================================================
儲存引擎是什嗎？MySQL中的資料用各種不同的技術儲存在檔案(或者內 正確的編譯方法固然重要，但它只是提高MySQL伺服器效能工作的一部分。MySQL伺服器的許多參數會影響伺服器的效能表現，而且我們可以把這些參數儲存到設定檔，使得每次MySQL伺服器啟動時這些參數都自動發揮作用。這個設定檔就是my.cnf。
MySQL伺服器提供了my.cnf檔案的幾個樣本，它們可以在/usr/local/mysql/share/mysql/目錄下找到，名字分別為 my-small.cnf、my-medium.cnf、my-large.cnf以及my-huge.cnf。檔案名稱字中關於規模的說明描述了該設定檔適用的系統類別型。例如，如果運行MySQL伺服器的系統記憶體不多，而且MySQL只是偶爾使用，那麼使用my-small.cnf設定檔最為理想，這個設定檔告訴mysqld daemon使用最少的系統資源。反之，如果MySQL伺服器用於支援一個大規模的線上商場，系統擁有2G的記憶體，那麼使用mysql-huge.cnf 最為合適。
要使用上述樣本設定檔，我們應該先複製一個最適合要求的設定檔，並把它命名為my.cnf。這個複製得到的設定檔可以按照如下三種方式使用：
全域：把這個my.cnf檔案複製到伺服器的/etc目錄，此時檔案中所定義的參數將全域有效，即對該伺服器上啟動並執行所有MySQL資料庫伺服器都有效。
局部：把這個my.cnf檔案複製到[MYSQL-INSTALL-DIR]/var/將使該檔案只對指定的伺服器有效，其中[MYSQL-INSTALL-DIR]表示安裝MySQL的目錄。
使用者：最後，我們還可以把該檔案的作用範圍局限到指定的使用者，這隻需把my.cnf檔案複製到使用者的根目錄即可。
那麼，如何設定my.cnf檔案中的參數呢？或者進一步說，哪些參數是我們可以設定的呢？所有這些參數都對MySQL伺服器有著全域性的影響，但同時每一個參數都和MySQL的特定部分關係較為密切。例如，max_connections參數屬於mysqld一類。那麼，如何才能得知這一點呢？這隻需執行如下命令：

% >/usr/local/mysql/libexec/mysqld –help
該命令將顯示出和mysqld有關的各種選項和參數。要尋找這些參數非常方便，因為這些參數都在“Possible variables for option –set-variable (-O) are”這行內容的後面。找到這些參數之後，我們就可以在my.cnf檔案中按照如下方式設定所有這些參數：

set-variable = max_connections=100

這行代碼的效果是：同時串連MySQL伺服器的最大串連數量限制為100。不要忘了在my.cnf檔案[mysqld]小節加上一個set-variable指令，具體請參見設定檔中的樣本。