MySQL在高記憶體、IO利用率上的幾個最佳化點

MySQL在高記憶體、IO利用率上的幾個最佳化點

以下最佳化都是基於CentOS系統下的一些MySQL最佳化整理，有不全或有爭議的地方望繼續補充完善。

一、mysql層面最佳化

1. innodb_flush_log_at_trx_commit 設定為2

設定0是事務log（ib_logfile0、ib_logfile1）每秒寫入到log buffer,1是時時寫，2是先寫檔案系統的緩衝，每秒再刷進磁碟，和0的區別是選2即使mysql崩潰也不會丟資料。

2. innodb_write_io_threads=16(該參數需要在設定檔中添加，重啟mysql執行個體起效)

髒頁寫的線程數，加大該參數可以提升寫入效能.mysql5.5以上才有。

3. innodb_max_dirty_pages_pct 最大髒頁百分數

當系統中 髒頁 所佔百分比超過這個值，INNODB就會進行寫操作以把頁中的已更新資料寫入到磁碟檔案中。預設75，一般現在流行的SSD硬碟很難達到這個比例。可依據實際情況在75-80之間調節

4. innodb_io_capacity=5000

從緩衝區重新整理髒頁時，一次重新整理髒頁的數量。根據磁碟IOPS的能力一般建議設定如下：

SAS 200
SSD 5000
PCI-E 10000-50000

5. innodb_flush_method=O_DIRECT(該參數需要重啟mysql執行個體起效)

控制innodb資料檔案和redo log的開啟、刷寫入模式。有三個值：fdatasync(預設)，O_DSYNC，O_DIRECT。

• fdatasync模式：寫資料時，write這一步並不需要真正寫到磁碟才算完成（可能寫入到作業系統buffer中就會返回完成），真正完成是flush操作，buffer交給作業系統去flush,並且檔案的中繼資料資訊也都需要更新到磁碟。
• O_DSYNC模式：寫日誌操作是在write這步完成，而資料檔案的寫入是在flush這步通過fsync完成。
• O_DIRECT模式：資料檔案的寫入操作是直接從mysql innodb buffer到磁碟的，並不用通過作業系統的緩衝，而真正的完成也是在flush這步,日誌還是要經過OS緩衝。

三種模式如： 通過圖可以看出O_DIRECT相比fdatasync的優點是避免了雙緩衝，本身innodb buffer pool就是一個緩衝區，不需要再寫入到系統的buffer，但是有個缺點是由於是直接寫入到磁碟，所以相比fdatasync的順序讀寫的效率要低些。
在大量隨機寫的環境中O_DIRECT要比fdatasync效率更高些，順序寫多的話，還是預設的fdatasync更高效。

6. innodb_adaptive_flushing  設定為 ON （使重新整理髒頁更智能）

影響每秒重新整理髒頁的數目。規則由原來的“大於innodb_max_dirty_pages_pct時重新整理100個髒頁到磁碟”變為 “通過buf_flush_get_desired_flush_reate函數判斷重做日誌產生速度確定需要重新整理髒頁的最合適數目”；即使髒頁比例小於 innodb_max_dirty_pages_pct時也會重新整理一定量的髒頁。

7. innodb_adaptive_flushing_method  設定為 keep_average  

影響checkpoint，更平均的計算調整刷髒頁的速度，進行必要的flush.（該變數為mysql衍生版本Percona Server下的一個變數，原生mysql不存在）

8. innodb_stats_on_metadata=OFF   

關掉一些訪問information_schema庫下表而產生的索引統計。

當重啟mysql執行個體後，mysql會隨機的io取資料遍曆所有的表來取樣來統計資料，這個實際使用中用的不多，建議關閉.

9. innodb_change_buffering=all  

當更新/插入的非叢集索引的資料所對應的頁不在記憶體中時（對非叢集索引的更新操作通常會帶來隨機IO），會將其放到一個insert buffer中，當隨後頁面被讀到記憶體中時，會將這些變化的記錄merge到頁中。當伺服器比較空閑時，後台線程也會做merge操作。

由於主要用到merge的優勢來降低io，但對於一些情境並不會對固定的資料進行多次修改，此處則並不需要把更新/插入操作開啟change_buffering，如果開啟只是多餘佔用了buffer_pool的空間和處理能力。這個參數要依據實際業務環境來配置。

10. innodb_old_blocks_time=1000

使Block在old sublist中停留時間長為1s，不會被轉移到new sublist中,避免了Buffer Pool被汙染BP可以被認為是一條長鏈表。被分成young 和 old兩個部分，其中old預設佔37%的大小（由innodb_old_blocks_pct 配置）。靠近頂端的Page表示最近被訪問。靠近尾端的Page表示長時間未被訪問。而這兩個部分的交匯處成為midpoint。每當有新的Page需要載入到BP時，該page都會被插入到midpoint的位置，並聲明為old-page。當old部分的page，被訪問到時，該page會被提升到鏈表的頂端，標識為young。

由於table scan的操作是先load page，然後立即觸發一次訪問。所以當innodb_old_blocks_time =0 時，會導致table scan所需要的page不讀的作為young page被添加到鏈表頂端。而一些使用較為不頻繁的page就會被擠出BP，使得之後的SQL會產生磁碟IO，從而導致響應速度變慢。

這時雖然mysqldump訪問的page會不斷載入在LRU頂端，但是高頻度的熱點資料訪問會以更快的速度把page再次搶佔到LRU頂端。從而導致mysqldump載入入的page會被迅速刷下，並立即被evict（淘汰）。因此，time=0或1000對這種壓力環境下的訪問不會造成很大影響，因為dump的資料根本搶佔不過熱點資料。不只dump，當大資料操作的時候也是如此。

二、mysql系統層面最佳化

1.關閉 numa=off,或修改策略為interleave(交織分配記憶體)防止意外的swap  

numa策略引入了node的概念，每個物理CPU都被視為一個node，而每個node都有一個local memory，相對這個node之外的其它node都屬於外部存取。

NUMA的記憶體配置策略有localalloc（預設）、preferred、membind、interleave。

• localalloc規定進程從當前node上請求分配記憶體；
• preferred比較寬鬆地指定了一個推薦的node來擷取記憶體，如果被推薦的node上沒有足夠記憶體，進程可以嘗試別的node。
• membind可以指定若干個node，進程只能從這些指定的node上請求分配記憶體。
• interleave規定進程從指定的若干個node上以Round-roll演算法交織地請求分配記憶體。

每個進程（或線程）都會分配一個優先node,對於系統預設的localalloc策略會有一個問題，對於mysql這種幾乎佔滿整個系統記憶體的應用來說，很容就把某個node的資源給佔滿，若Linux又把一個大的資源分派到這個已經佔滿資源的node時，會資源不足，造成記憶體資料於磁碟進行交換，或者摒棄buffer_pool裡的活躍資料。在實際測試中發現比如有node0、node1 兩個物理node，當系統負載很高的時候，node0資源被佔滿，node1雖然仍有部分空閑記憶體，但是系統即使進行記憶體到磁碟交換也不會去利用node1上的空閑資源。

因此建議對於像mysql這樣的單一實例的龐大複雜的進程來說，關閉numa或者設定策略為交織分配記憶體更合理。但對於一個機器上有多少個執行個體，可以每個執行個體綁定一個CPU核上。然後就可以充分利用numa的特性，更高效。

2.增加本地連接埠，以應對大量串連

echo ‘1024 65000′ > /proc/sys/net/ipv4/ip_local_port_range

該參數指定連接埠的分配範圍，該連接埠是向外訪問的限制。mysql預設監聽的3306連接埠即使有多個請求連結，也不會有影響。但是由於mysql是屬於高記憶體、高cpu、高io應用，不建議把多少應用於mysql混搭在同一台機器上。即使業務量不大，也可以通過降低單台機器的配置，多台機器共存來實現更好。

3.增加隊列的連結數

echo ‘1048576’ > /proc/sys/net/ipv4/tcp_max_syn_backlog

建立連結的隊列的數越大越好，但是從另一個角度想，實際環境中應該使用串連池更合適，避免重複建立連結造成的效能消耗。使用串連池，連結數會從應用程式層面更可控些。

4.設定連結逾時時間

echo ’10’ > /proc/sys/net/ipv4/tcp_fin_timeout

該參數主要為了降低TIME_WAIT佔用的資源時間長度。尤其針對http短連結的服務端或者mysql不採用串連池效果比較明顯。

三、其它層面最佳化的考慮

對於高DAU的業務mysql執行個體來說，建議不要吝惜記憶體，使用128G或更高記憶體，innodb很好的利用了記憶體的優勢來提高mysql的效能，我們就要給予他足夠的空間來發揮他的效能。磁碟IO效能遠不及記憶體的處理速度，這個無可厚非，所做的最佳化盡量的把需求IO的操作阻攔到記憶體直接返回給���戶端。

當然記憶體再高也不能把所有的資料都緩衝到記憶體中，在實際的大部分業務中還是依賴隨機IO居多，更如現今比較火的手遊，更是高隨機寫入的業務類型，各個雲廠商也都預設提供SSD甚至需求更高的PCIe Flash存放裝置。通過提高磁碟的IO效能也是其次的選擇。

另業務如果足夠大，單台機器必然無法支撐，就要考慮分庫，分表，大部分產品公司所做的還是按業務劃分庫。如果一項業務也大到單台機器無法承受，那就需要分表和分庫來操作了。其實不管業務是否會做大，當開始建立工程的時候都應該要做到支援可分庫、可分表（盡量避免使用表的自增ID作為業務ID使用）、可分業務(盡量避免事務操作，甚至允許在業務上有些可接受的犧牲，否則後期很難劃分業務)，否則只有面臨重構的尷尬場面。這樣做的好處是即使有些錯誤的設計預想不周，由於業務的各種原因無時間重構，也可以通過擴充、遷移的方式來降低單點造成的影響程度，然後後期再慢慢最佳化。

有一點一定要注意，“殺手級”sql語句的存在會讓以上所有最佳化全部作廢。比如上千萬表資料的無索引搜尋、排序、計算。所以必須開啟慢查詢日誌排查所有慢查詢語句。

以上為小弟的一些針對mysql實際使用的一些整理，有不足的地方還望提出補充，慢慢完善。

--------------------------------------分割線 --------------------------------------

Ubuntu 14.04下安裝MySQL

《MySQL權威指南(原書第2版)》清晰中文掃描版 PDF

Ubuntu 14.04 LTS 安裝 LNMP Nginx\PHP5 (PHP-FPM)\MySQL

Ubuntu 14.04下搭建MySQL主從伺服器

Ubuntu 12.04 LTS 構建高可用分布式 MySQL 叢集

Ubuntu 12.04下原始碼安裝MySQL5.6以及Python-MySQLdb

MySQL-5.5.38通用二進位安裝

--------------------------------------分割線 --------------------------------------

本文永久更新連結地址：