318
微博MySQL優化之路 - 肖鵬
編輯手記：MySQL作為左手歡迎的開源數據庫，一直是廣大數據庫技術愛好者所關注的。為了給大家提供更好的學習分享平台，我們將會在後續適當分享MySQL的內容。感謝廣大朋友們的支持。
肖鵬老師對於開源數據庫尤其是MySQL的研究特別深入，今天我們來一起聽他分享自己對MySQL數據庫的優化經驗！
作者簡介
肖鵬
微博研發中心數據庫技術負責人，主要負責微博數據庫（MySQL/Reids/HBase/Memcached）相關的業務保障，性能優化，架構設計以及周邊的自動化係統建設。10年互聯網數據庫架構和管理經驗，專注於數據庫的高性能和高可用技術保障方向。
正文
數據庫是所有架構中不可缺少的一環，一旦數據庫出現性能問題，那對整個係統都回來帶災難性的後果。並且數據庫一旦出現問題，由於數據庫天生有狀態（分主從）帶數據（一般還不小），所以出問題之後的恢複時間一般不太可控，所以，對數據庫的優化是需要我們花費很多精力去做的。接下來就給大家介紹一下微博數據庫這些年的一點經驗，希望可以對大家有幫助。
1硬件層優化
這一層最簡單，最近幾年相信大家對SSD這個名詞並不陌生，其超高的IOPS在剛出現在大家視野中的時候就讓人驚豔了一把，而隨著最近價格的不斷下調，已經非常具有性價比，目前微博已經把SSD服務器作為數據庫類服務的標配。
我們來看下我們早些年自己對SSD的OLTP的性能測試：
可以看到OLTP的qps可以達到2.7w左右，配合1m2s的架構可以支持5w的qps，在一些簡單場景下，甚至可以不必配置cache層來做緩存。
ps:硬件測試最好自己進行實測，官方數據僅能作為一個參考值，因為很多時候性能要嚴重依賴於場景，細化到不同的SQL會得到相差很大的結論，故最好自行測試。
微博在12年的時候使用PCIE-FLASH支撐了feed係統在春晚3.5w的qps，在初期很好的支撐了業務的發展，為架構優化和改造爭取了非常多的時間。並且大家可以看到，目前很多的雲廠商的物理機基本全都是SSD設備，AWS更是虛機都提供SSD盤來提供IO性能，可以預見未來IO將不會在是數據庫遇到的最大瓶頸點。
經驗：如果公司不差錢，最好直接投入SSD or PCIE-FLASH設備，而且投入的時間越早越好
2係統層優化
配合SSD硬件之後，係統層原有的一些設計就出現了問題，比如IO scheduler，係統默認的為CFQ，主要針對的是機械硬盤進行的優化，由於機械硬盤需要通過懸臂尋道，所以CFQ是非常適合的。
Complete Fair Queuing
該算法為每一個進程分配一個時間窗口，在該時間窗口內，允許進程發出IO請求。通過時間窗口在不同進程間的移動，保證了對於所有進程而言都有公平的發出IO請求的機會。同時CFQ也實現了進程的優先級控製，可保證高優先級進程可以獲得更長的時間窗口。
但是由於SSD盤已經沒有了尋道而是基於電子的擦除，所以CFQ算法已經明顯的不合適了，一般情況下網上都推薦使用NOOP算法，但是我個人更推薦DEADLINE算法。我們看下這2種算法的特點。
NOOP算法隻擁有一個等待隊列，每當來一個新的請求，僅僅是按FIFO的思路將請求插入到等待隊列的尾部，默認認為 I/O不會存在性能問題，比較節省CPU資源。
DEADLINE調度算法通過降低性能而獲得更短的等待時間，它使用輪詢的調度器,簡潔小巧,提供了最小的讀取延遲和尚佳的吞吐量,特別適合於讀取較多的環境。
從算法的特點看，NOOP確實更適合SSD介質，非常的簡單，但是由於數據庫型服務有很多複雜查詢，簡單的FIFO可能會造成一些事務很難拿到資源從而一直處於等待狀態，所以個人更推薦使用DEADLINE。
ps：更主要的是因為對這2個算法的壓測顯示性能並沒有太明顯的區別。
以下是我們自己在線上業務調整之後的效果：
除了以上這點之外，還有一些小地方也許要調整，雖然收益不會看上去這麼明顯，但是聚沙成塔，積少成多，還是非常值得優化的。
-
使用EXT4 or XFS
-
在mount的時候加上 noatime屬性
-
raid卡的讀寫策略改為write back
-
使用jemalloc替換現有的Glibc
經驗：重點放在針對IO的優化上，數據庫尤其是MySQL是IO密集型服務，解決IO的問題會減少不必要的問題。
3MySQL自身的優化
我們先說說有那些參數可以帶來性能的改變
-
innodb_max_dirty_pages_pct
爭議比較大，一般來說都是在75-90之間，主要控製BP中的髒數據刷盤的時機，如果太小會頻繁刷盤造成IO上升，如果太大會導致MySQL正常關閉的時候需要很長的時間才能normal shutdown，具體需要看實際場景，個人推薦90
-
innodb_io_capacity
磁盤IO吞吐，具體為緩衝區落地的時候，可以刷髒頁的數量，默認200，由於使用了SSD硬盤，所以推薦設置到3000-5000
-
innodb_read_io_threads
-
innodb_write_io_threads
增加後台處理線程的數目，默認為4，推薦改成8
-
sync_binlog
-
innodb_flush_log_at_trx_commit
著名的雙1參數，對性能影響非常的大
sync_binlog控製刷binlog的策略，MySQL在每寫N次 二進製日誌binary log時，會使用fdatasync()函數將它的寫二進製日誌binary log同步到磁盤中去。
innodb_flush_log_at_trx_commit控製log buffer刷log file的策略，設置為0的時候每秒刷新一次，設置為1的時候每次commit都會刷新。
從上述描述就可以看出如果追求數據的安全性，那麼設置雙一是最安全的，如果追求性能最大化，那麼雙0最合適，這中間可以相差至少2倍的性能。
-
innodb_log_file_size
innodb redo log的size大小，5.5最大4G，5.6最大256G，這個越大可以提升寫的性能，大部分時候不需要等待checkpoint覆蓋就可以一直write。
-
query_cache_type
看上去很美的東西，但是在實際生產環境中，多次給我們帶來了故障，由於每次表的更新都會清空buffer，並且對於sql的匹配是逐個字符效驗實際效果很長，大部分時間並沒有得到cache的效果，反而得到了很多wait for query cache lock。建議關閉。
以上，僅針對MySQL 5.5，目前我們還在摸索5.6和5.7由於還沒有大規模線上使用，所以還談不上有什麼經驗。
經驗：如果有人力可以投入，可以學習BAT針對數據庫進行二次開發，通過path的方式獲得更高的性能和穩定性。如果沒有人力，隻要深入了解MySQL自身參數的影響也可以滿足業務的需求，不用一味的追源碼級別的開發改造。
4業務優化
所謂的業務優化其實說白了很多時候就是index的優化，我們DBA常說一條慢SQL就能將上麵所有的優化都付之一炬，CPU直接打滿，RT全都都飆升到500ms甚至1s以上。
優化慢查有三寶：
-
pt-query-digest
-
explain
-
show profiling
首先，使用pt-query-digest可以定位到定位影響最中的慢查是哪條。
然後通過explain具體分析慢查曉的問題所在
重點查看type，rows和extra這三個字段。
其中type的順序如下：
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
最後，如果問題還是比較嚴重，可以通過show profiling來定位一下到底是那個環節出現的問題。
可以看到sending data最消耗時間，這時候就需要找到底為什麼在sending上消耗了這麼多的時間，是結果集太大，還是io性能不夠了，諸如此類
以下就是一個複雜語句的優化結果，可以從rows那裏明顯的看出減少了很多查詢的開銷。
經驗：最好建立慢查詢監控係統，每天都花時間在慢查的優化上，避免一條SQL引發的血案之類的事情發生。
4架構優化
最後，也就是終極手段了，那就是架構優化，其實很多時候，當我們將上麵幾個方向都做了之後發現還沒有很好的效果，那就必須找開發同學一起聊一下了。ps：當然找PM同學聊一下人生會更有效果。
記得有一次，我們找開發聊了一下，最後開發決定將這個功能改掉，這個時候你會突然發現無論什麼優化手段都比不上「不做」這個優化手段，簡直無敵了。
根據我個人的經驗來說架構層的優化有如下幾個普適原則：
-
cache為王
熱點數據必須使用Redis或者mc之類的cache抗量，讓MySQL抗流量是不明智的。
-
使用隊列消峰
眾所周知MySQL的異步同步機製是單線程的，所有主庫上的並發到從庫上都是通過io-thread來慢慢做的，即使主庫寫入速度再快，從庫延遲了，整個集群還是不可用，所以最好采用隊列來進行一定的寫入消峰，使寫入維持在一個較為均衡的水平。
-
適度的過度設計
很多產品最開始的時候比較小，但是有可能上線之後廣受好評一下用活躍度就上來了，這個時候如果數據庫出現瓶頸需要拆分需要開發、DBA、架構師等等一起配合來做，而且很有可能沒有時間。所以在產品初期進行一定的過度設計會為未來這種情況打好鋪墊。最明顯的就是拆庫拆表，最好在一開始就對業務進行適度的垂直拆分和比較過度的水平拆分，以便應對業務的高速增長。
舉一個例子：
1、通過mcq降低對MySQL的寫入性能的要求。
2、通過mc和Redis來承擔用戶的實際訪問，90%的量依靠cache層承載和屏蔽。
3、MySQL作為最終的數據落地，存儲全量的數據，但是僅支撐部分業務查詢，小於10%。
經驗：讓合適的軟件做適合的事情，不要光從技術層麵思考優化方案，也要從需求方麵去分解。
5總結中的總結
轉一篇很經典的數據庫優化漏鬥法則，很多年前就看到過，現在再看依然覺得適用，大家共勉。
唯一不適用的就是最下的增加資源，SSD真是個好東西，誰用誰知道。
本文出自數據和雲公眾號，原文鏈接
最後更新：2017-07-18 10:33:35