CentOS系統MySQL最佳化詳解

本文主要講解CentOS系統MySQL最佳化的問題，瞭解一下MySQL最佳化的一些基礎，MySQL的最佳化我分為兩個部分。伺服器物理硬體的最佳化和MySQL自身的最佳化。

CentOS系統的確很好用，但是還是很多地方需要我們進行正確的設定，進行最佳化的。在Apache, PHP, MySQL的體系架構中,MySQL對於效能的影響最大,也是關鍵的核心部分。對於Discuz!論壇程式也是如此,MySQL的設定是否合理最佳化,直接影響到論壇的速度和承載量!同時,MySQL也是最佳化難度最大的一個部分,不但需要理解一些MySQL專業知識,同時還需要長時間的觀察統計並且根據經驗進行判斷,然後設定合理的參數。下面我們就來對CentOS系統MySQL最佳化進行詳細解析、

下面我們瞭解一下MySQL最佳化的一些基礎,MySQL的最佳化我分為兩個部分,一是伺服器物理硬體的最佳化;二是MySQL自身(my.cnf)的最佳化。

(1) 伺服器硬體對MySQL效能的影響

a) 磁碟尋道能力(磁碟I/O),以目前高轉速SCSI硬碟(7200轉/秒)為例,這種硬碟理論上每秒尋道7200次,這是物理特性決定的,沒有辦法改變。 MySQL每秒鐘都在進行大量、複雜的查詢操作,對磁碟的讀寫量可想而知。所以,通常認為磁碟I/O是制約MySQL效能的最大因素之一,對於日均訪問量在100萬PV以上的Discuz!論壇,由於磁碟I/O的制約,MySQL的效能會非常低下!在CentOS系統中解決這一制約因素可以考慮以下幾種解決方案:使用RAID-0+1磁碟陣列,注意不要嘗試使用RAID-5,MySQL在RAID-5磁碟陣列上的效率不會像你期待的那樣快;
拋棄傳統的硬碟,使用速度更快的快閃記憶體式存放裝置。經過Discuz!公司技術工程的測試,使用快閃記憶體式存放裝置可比傳統硬碟速度高出6-10倍左右。

b) CPU 對於MySQL應用,推薦使用S.M.P.架構的多路對稱CPU,例如:可以使用兩顆Intel Xeon 3.6GHz的CPU。

c) 實體記憶體對於一台使用MySQL的Database Server來說,伺服器記憶體建議不要小於2GB,推薦使用4GB以上的實體記憶體。

(2) MySQL自身因素當解決了上述伺服器硬體制約因素後,讓我們看看MySQL自身的最佳化是如何操作的。對MySQL自身的最佳化主要是對其設定檔 my.cnf中的各項參數進行最佳化調整。下面我們介紹一些對效能影響較大的參數。

由於my.cnf檔案的最佳化設定是與伺服器硬體設定息息相關的,因而我們指定一個假想的伺服器硬體環境:
CPU: 2顆Intel Xeon 2.4GHz
記憶體: 4GB DDR
硬碟: SCSI 73GB

下面,我們根據以上硬體設定結合一份已經最佳化好的my.cnf進行說明:# vi /etc/my.cnf

以下只列出my.cnf檔案中[mysqld]段落中的內容,其他段落內容對MySQL運行效能影響甚微,因而姑且忽略。
[mysqld]
port = 3306
serverid = 1
socket = /tmp/mysql.sock
skip-locking

避免MySQL的外部鎖定,減少出錯幾率增強穩定性。
skip-name-resolve

禁止MySQL對外部串連進行DNS解析,使用這一選項可以消除MySQL進行DNS解析的時間。但需要注意,如果開啟該選項,則所有遠程主機串連授權都要使用IP地址方式,否則MySQL  將無法正常處理串連請求!
back_log = 384

指定MySQL可能的串連數量。當MySQL主線程在很短的時間內接收到非常多的串連請求,該參數生效,主線程花費很短的時間檢查串連並且啟動一個新線程。back_log  參數的值指出在MySQL暫時停止回應新請求之前的短時間內多少個請求可以被存在堆棧中。如果CentOS系統在一個短時間內有很多串連,則需要增大該參數的值,該參數值指定到來的TCP/IP串連的偵聽隊列的大小。不同的作業系統在這個隊列大小上有它自己的限制。試圖設定back_log高於你的CentOS系統的限制將是無效的。預設值為50。對於Linux系統推薦設定為小於512的整數。
key_buffer_size = 256M

key_buffer_size指定用於索引的緩衝區大小,增加它可得到更好的索引處理效能。對於記憶體在4GB左右的伺服器該參數可設定為256M或384M。注意:該參數值設定的過大反而會是伺服器整體效率降低!
max_allowed_packet = 4M
thread_stack = 256K
table_cache = 128K
sort_buffer_size = 6M

查詢排序時所能使用的緩衝區大小。注意:該參數對應的分配記憶體是每串連獨佔!如果有100個串連,那麼實際分配的總共排序緩衝區大小為100 × 6 = 600MB。所以,對於記憶體在4GB左右的伺服器推薦設定為6-8M。
read_buffer_size = 4M

讀查詢操作所能使用的緩衝區大小。和sort_buffer_size一樣,該參數對應的分配記憶體也是每串連獨享!
join_buffer_size = 8M

聯集查詢操作所能使用的緩衝區大小,和sort_buffer_size一樣,該參數對應的分配記憶體也是每串連獨享!
myisam_sort_buffer_size = 64M
table_cache = 512
thread_cache_size = 64
query_cache_size = 64M

指定CentOS系統MySQL查詢緩衝區的大小。可以通過在MySQL控制台執行以下命令觀察:
> SHOW VARIABLES LIKE '%query_cache%';
> SHOW STATUS LIKE 'Qcache%';
如果Qcache_lowmem_prunes的值非常大,則表明經常出現緩衝不夠的情況;
如果Qcache_hits的值非常大,則表明查詢緩衝使用非常頻繁,如果該值較小反而會影響效率,那麼可以考慮不用查詢緩衝;Qcache_free_blocks,如果該值非常大,則表明緩衝區中片段很多。
tmp_table_size = 256M
max_connections = 768

指定MySQL允許的最大串連進程數。如果在訪問論壇時經常出現Too Many Connections的錯誤提示,則需要增大該參數值。
max_connect_errors = 10000000
wait_timeout = 10

指定一個請求的最大連線時間,對於4GB左右記憶體的伺服器可以設定為5-10。
thread_concurrency = 8

該參數取值為伺服器邏輯CPU數量×2,在本例中,伺服器有2顆物理CPU,而每顆物理CPU又支援H.T超執行緒,所以實際取值為4 × 2 = 8skip-networking

開啟該選項可以徹底關閉MySQL的TCP/IP串連方式,如果CentOS系統WEB伺服器是以遠端連線的方式訪問MySQL資料庫伺服器則不要開啟該選項!否則將無法正常串連!

以上,我們對一份my.cnf做了簡單的說明,MySQL的最佳化是一項需要長期觀察,長期積累經驗,長期實驗的工作。有興趣的使用者可以邊查閱文檔資料邊做實驗,在實際應用中獲得更多的經驗的收穫。

在所有最佳化操作完成後,需要重新啟動CentOS系統MySQL服務.這樣，登陸後，我們會發現有很大的變化。