[轉載] 新浪微博MySQL最佳化的小結和反思

標籤：

原文: http://mp.weixin.qq.com/s?__biz=MzA4Nzg5Nzc5OA==&mid=206762682&idx=1&sn=1233ed1496d7fd059d247329f3d3a183&scene=1&key=c76941211a49ab587d35d0d840a84ff2e3948510bca7698783e134b95c3e8ad0a30d1f83897e9c764e289a1011c565db&ascene=0&uin=Mjk1ODMyNTYyMg%3D%3D&devicetype=iMac+MacBookPro9%2C2+OSX+OSX+10.10.3+build(14D136)&version=11020012&pass_ticket=GJi1zStfd3iFy8nPiSm73eDDCYHehF3pnzgmnpzERbyqm7LlmMscbVsBrAMC%2FPt3

本文根據高效營運系列群的一次精彩分享整理而成。“高效營運”公眾號作為本系列群的官方唯一公眾號，原創並獨家首發。

 

歡迎關注“高效營運”公眾號，以關注並免費參加「營運講壇」每月一次的線下交流活動；並搶先賞閱乾貨滿滿的各種原創文章（詳情參見文末）。

編輯

• 高浩淼-北京、徐文慧@21v （文章整理）

• 高浩淼-北京 （發布）

嘉賓介紹

楊尚剛，原新浪進階DBA，現在在美圖負責資料庫，微博zolker。2011年加入新浪，早期主要負責新浪微博核心資料庫的架構設計，後期主要負責資料庫平台的軟硬體最佳化。

主題簡介

MySQL資料庫效能最佳化方面的一些策略

MySQL效能最佳化

MySQL效能最佳化的幾點常規最佳化策略：

• 讀寫分離

• sharding

• 參數最佳化

• 索引最佳化

• 系統最佳化

• 硬體最佳化

讀寫分離

讀寫分離是一種比較常規的最佳化策略，這個也很容易理解和實現，方案的主要目的還是做到讀寫的隔離，減少相互幹擾，互連網的大多數情境都是讀多寫少的業務，所以這種策略都是沒問題的。

Sharding

sharding拆分是一種通過一定的策略把資料重新分配的策略，主要解決單一實例寫入壓力或容量過大的問題。但是sharding帶來的問題也是很多的，比如營運管理成本增加和業務訪問的複雜度增加。

sharding這種策略在微博早期壓力大時候用的還是非常多的，天天各種拆分搞的很嗨，但是其實後來發現拆分很沒意思，冷靜下來發現其實很多拆分並不是那麼緊迫或者沒必要。

天天忙於拆分帶來的問題就是導致沒有時間做更深入最佳化和自動化方面的工作，所以拆分一定要做到適時適度。

sharding拆分主要分成兩個維度，垂直分割和水平分割，水平分割主要是在不改變schema的前提下重新分配資料，而垂直分割主要是在業務層面解耦。基本上兩者要結合使用。

所以控制拆分粒度是非常重要的 ，從現在的硬體水平上，單個sharding控制在2-3億以內都是沒問題的。

微博當時最大的單表在60億＋，單表容量過T，DBA有的時候還要“懶”一些的。

主從延遲最佳化

其實MySQL另外一個比較容易讓人詬病的就是主從複製延時問題，這個主要還是早期MySQL複製單線程設計的問題。

而造成延時的原因其實主要是兩點，主庫寫入過大導致從庫SQL單線程效能跟不上，或者從庫讀壓力過大影響了SQL單線程。當然現在MySQL 5.6也開始引入並行複製，但是粒度依然很大，還是基於庫的複製，所以提升有限。

剛從這兩點原因的主要誘發原因還是IO瓶頸的因素居多，所以解決延時問題首選的還是使用高效能IO儲存，次之使用並行複製方案，再次之採用sharding拆分。

Schema最佳化

再講一下 schema最佳化 ，主要考慮的是表結構的欄位設計和索引設計合理性，以及分表策略是否合理。對後期管理最佳化都是非常重要的。

列類型夠用就好,越小越好，簡單就好，資料類型越簡單越好，能用整型盡量不用字串，比如存時間和字串 ，避免Null，主鍵儘可能使用自增，不建議使用字串，尤其是innodb單表索引數不要超過五個(5.6有待測試) 索引設計的差異性，避免冗餘索引，字元集選擇，盡量UTF8，emoji字元選擇UTF8mb4。

參數最佳化

然後是MySQL的一些參數最佳化策略，主要是InnoDB層面的參數最佳化，MySQL Server層參數最佳化效能提升有限

innodb_file_per_table
innodb_buffer_pool_size
innodb_flush_log_at_trx_commit= 0 1 2 （和資料安全有關）
innodb_log_file_size
innodb_page_size
sync_binlog（和資料安全有關）

尤其要注意
innodb_flush_log_at_trx_commit和sync_binlog的設定，如果對資料安全要求高，建議都設定為1

硬體最佳化主要是，NUMA、大記憶體和SSD相關的使用和最佳化

NUMA在當前這種多核架構下，理論最佳化是有價值的，不過實際測試結果提升有限，而且還增加了管理成本。所以像Twitter的MySQL分支也建議關閉NUMA。之所以NUMA不起作用主要還是因為這種跨Node的訪問對MySQL整體latency影響有限。

而SSD更是在MySQL效能提升的利器。微博知名大V曾說過“近十年真正改變資料庫技術的就是快閃記憶體技術”，而且像阿里的去IOE也是離不開的SSD的應用的。

當時在新浪的應用模式是2U伺服器，10塊SSD做Raid5 ，效能還是不錯的。

echo noop/deadline >/sys/block/[device]/queue/scheduler（效能提升最明顯）
echo 2 > /sys/block/[device]/queue/rq_affinity (CentOS 6.4以上)
echo 0 > /sys/block/[device]/queue/add_random (關閉檔案系統barrier)

針對SSD的系統參數最佳化：

針對SSD的MySQL參數最佳化在5.5以上，提高innodb_write_io_threads和innodb_read_io_threads，innodb_io_capacity需要調大，記錄檔和redo放到機械硬碟，undo放到SSD atomic write，不需要Double Write，Buffer InnoDB壓縮，降低SSD壽命磨損，單機多執行個體+cgroup。

FAQ

問題一：關於amazon的aurora的實現難度
從amazon披露的資訊來看，難度還是比較大的。首先從上層資料庫層面已經和原生MySQL差距很大了，應該也是自己重寫或改寫的，這個之前微博上也有過很多討論，另外在檔案系統和硬體儲存上，amazon也都是深度定製的，相當於是把原來InnoDB的一些資料容錯安全性原則下防到檔案系統和儲存層面實現。

問題二：現在用PCI-E卡嗎？
美圖現在不用 ，原來新浪用了不少的，單從iops是要高的，實際跑業務差距不大，除非你的讀寫量超大。

問題三：除了剛才講到的提高IO，降低主從延時有哪些建議？
那就是並行複製和拆分有效了，之前也有一些從庫預讀方案，預讀Relay log，提升不大。

問題四：為什麼建議把redo放在機械盤上呢？
檔案讀寫特性有關，redo主要是順序寫為主，SSD更擅長隨機寫，實際我們也是都放SSD的，其實目前SSD壽命都還不錯了，放在一起不區分也沒什麼問題。

問題五：什麼情境不需要拆分？
就是看你資料量和訪問量。從一般情境來說 單個sharding控制在2億以內都是比較合理的，還是要根據自己的情境 判斷瓶頸到底是不是只能sharding解決 ，能不用就不用吧。一般大表加欄位用pt-online-schema也可以。不過像我們當時的那個60億大表，估計要加欄位就確實很麻煩了。

問題六：對原版MHA修改主要修改的哪些方面？galera有人用嗎？
我們當時做的主要重寫的他的切換邏輯部分。galera原生應該沒人用，大部分用的還是percona基於galera封裝的percona xtradb cluster。據我所知搜狐和去哪兒都在用，坑還是不少的，需要對代碼底層有把控力的更好一些。

問題七：你們修改的MHA部分可以分享一下嗎？你們用MariaDB嗎，這個可以並行複製？你們用觸發器嗎？
我們修改的部分主要還是Python重寫的核心切換邏輯，Tlog還是原生的。MariaDB可以並行複製，我們不要MariaDB，實際線上使用國內也不多。盡量不線上上使用觸發器。

問題八：能講講SQL最佳化嘛？
SQL最佳化涉及到的東西就比較雜了，MySQL等值查詢最好，儘可能好的利用索引，善用explain和pt-query-digest。

問題九：你們用MHA遇到了哪些問題，MHA自動切換如果丟了資料，如何恢複？PXC和MHA生產中如何選擇，另外您提到您這邊部分手動，部分自動，是什麼原因？
MHA只是儘可能保證不丟，如果切換完資料補全完還有問題，只能看看業務日誌是否可以恢複。PXC的限制還是很多的，比如跨IDC和叢集節點數量等，營運成本也遠高於原版。目的是全部自動，一些核心重要的業務可能需要人工介入判斷，會有連接埠分級的策略。

問題十：官方的fabric怎樣？
目前還在lab階段，離生產還有距離。

問題十一：讀寫分離，你們是程式員寫的不同IP嗎？還是用的啥代理？
DNS網域名稱。當時也開發過幾個版本中介軟體，各種原因，線上使用的也不是很廣泛。其實當時微博使用的也是類似TDDL的做法，proxy封裝在前端。

問題十二：說說備份，冷備，mysqldump、xtrabackup您什麼情況下用什麼軟體，說說原因，給點建議
一般使用xtrabackup就可以了，除非你要做邏輯備份，使用mysqldump。

如何一起愉快地發展

這是一個新的時代！每個人都有自己的聲音，值得被尊重，並且有機會被尊重。

高效營運系列群是國內高端營運圈子、營運行業垂直社交的典範。現有會員800餘名，其中營運總監及以上層級會員300多名。

 

“高效營運”公眾號值得您的關注，作為高效營運系列群的唯一官方公眾號，每周發表多篇乾貨滿滿的原創好文：來自於系列群的討論精華、營運講壇線上/線下活動精彩分享及部分群友原創。“高效營運”也是互連網專欄《高效營運最佳實務》及營運2.0官方公眾號。

 

來吧朋友，共襄盛舉。

【提示】目前高效營運兩個群均已滿員，如您願意，可添加蕭田國個人號 xiaotianguo 為好友，並申請加入我們聊天群（技術討論+部分水貼，已有來自1群和2群眾多喜歡熱鬧的朋友）。

重要提示：除非事先獲得授權，請在本公眾號發布2天后，才能轉載本文。尊重知識，請必須全文轉載，並包括本行及如下二維碼。

[轉載] 新浪微博MySQL最佳化的小結和反思