新浪愛彩票DBA總監:為什麼你的MySQL跑得很慢?

Home / MySQL / 新浪愛彩票DBA總監:為什麼你的MySQL跑得很慢?

其實這是一個老問題了:

為什麼會覺得數據庫比較慢呢?

再換種問法:

數據庫優化要從哪些方面入手?


1第一點,硬件太老

硬件我們這裡主要從CPU、內存、磁盤三個方面來說下,還有一些因素比如網卡,機房網絡等因為文章篇幅關係,就不一一介紹了,以後還有機會可以聊。

首先我們來看下MySQL對CPU的利用特點:

  • 5.1可以利用4個核,5.5可以利用到24個核,5.6可以利用到64個核

比如MySQL5.6能用到48個CORE以上,跑得好的,64個CORE都能用到(48CORE-64CORE之間,官方公佈48個CORE,我實際測試能跑到64個CORE)。

MySQL 5.6 可以用到48 core+

MySQL 5.1 前最多可以用到4個核 

現在一般的生產環境服務器,都是32CORE以上。

所以我這裡都推薦大家盡量得去用MySQL5.5或MySQL5.6, 除非你們公司的服務器一直用的很老舊的服務器,只有4個核,或1個核。

因為5.1以前(5.0一樣)都是在內部代碼裡寫死了,是基於innobase的存儲引擎,數據庫對硬件的利用率較差。後面演進為了InnoDB引擎後,好了很多。

  • 每個連接一個是一個線程(非thread pool),每個query只能使用到一個核

另外,在MySQL每個query只能用到一個CPU。

Oracle裡面用並行SQL,並行查詢,這類功能在MySQL裡是不存在的。

  • 無執行計劃緩存(無SQL執行計劃預編譯)

其次,MySQL內部沒有SQL預編譯。因此不存在像Oracle內存結構裡的library cache(庫緩存)這類結構體。所以,MySQL只有硬解析,不存在什麼軟解析,更不存在什麼軟軟解析。

  • MySQL隨著連接數上升會出現性能下降

這個也是MySQL的一個硬傷,但是隨著MySQL的版本演進,還是出現了很多解決方法。

比如:官方推出的thread pool(線程池),簡稱TP。就是為了解決並發連接數過高的問題,不過這屬於MySQL額外的組件,官方的TP是需要額外花錢購買的。

另外,國內有個叫樓方鑫的,開發了一個OneSQL的中間件,也是解決類似問題的。

  • 有Result緩存,但比較雞肋

MySQL裡也有類似Oracle裡的結果緩存,叫Query Cache,但屬於比較雞肋的功能,很少使用。

因為大部分實際的生產環境都是OLTP系統,存在頻繁的更新修改操作,這個Query Cache用在數據頻繁更新修改的環境裡,會使MySQL的性能嚴重下降,因此,一般很少使用。

現在用MySQL,基本都是用InnoDB存儲引擎,以前的MyISAM這些引擎也用得很少了。(什麼是存儲引擎?這個不知道的話,你可以gg了)

InnoDB引擎是完全沒有必要去開啟這個Query Cache的,因為本身就是一個事務型的存儲引擎,用InnoDB就是用它的事務處理能力,肯定會發生頻繁的數據更新和修改嘛。

再次來看下MySQL對內存利用特點

  • 64位操作系統的服務器可利用內存((2^64-1)/1024/1024/1024)G
  • 在高速並發環境,基本是靠內存緩存來減少對磁盤的IO衝擊
  •  通常內存按實際數據的15%-20%規劃,如果特別熱的數據,需要考慮更大的比例來緩存數據

這15%-20%的數據我們通常又叫做熱數據。(這也是通常的一個經驗值)

比如你評估出你這台MySQL數據總量大概在500G左右,那MySQL要給到的內存可能就是75G(500*0.15),那你可能需要一台128G左右內存的服務器。

另外有些業務還會存在特別熱、大量熱的數據(大大超出15%-20%這個區間,也是有可能的),比如:QQ農場。

相信大家都玩過以前那種偷菜的遊戲,QQ農場,開心農場之類的。(還有訂票的12306網站)。

這類業務在我們業界裡面都是屬於關注度很高的,這類業務的特點,數據熱的時候,基本100%都是熱數據,比如:QQ農場大家玩的時候,每天都上來玩的,每隔一會兒就上來偷把菜,很多人半夜起來上廁所起來都要偷一把菜。

所以這類業務的MySQL數據庫,內存配備還得加高。15-20%還不夠。

總結:一般的業 務15%-20%來規劃熱數據,比如:用戶中心,訂單之類的常見業務。另外一些特殊點的業務,具體情況具體分析。

  • 可以根據Query響應時間來做指導分配

我們在做這種大型在線架構-大型數據庫規劃設計的時候,

SQL查詢的響應時間也是一個非常重要的指標。

在這種大型系統裡面,要承載數百萬甚至千萬級別用戶同時在線進行業務,SQL查詢(query)的響應時間是必須去嚴格把控,必須把你這套系統的Query響應時間控制在多少時間以內。

比如我們的核心庫,我就要求Query的響應時間(平均響應)在30ms以下。超過30ms,我們就認為這個數據庫可能達到承載極限,需要對這個數據庫進行擴容了。

另外,要對這個Query響應時間進行長期的指標監控。

這個是核心庫,如果另外一些不太重要的輔助庫,比如放日誌的庫,或者說一些性能要求本身不是太高的庫,我們可以放寬點這個Query響應時間,放寬到1秒或2秒內。

根據業務的重要等級程度來定這個Query響應時間的閥值。

這是一個很重要的指導思想,根據Query響應時間來規劃你的性能容量。

容量分兩種:性能容量和空間容量。空間容量很簡單,就是放多少SIZE數據,幾個T。

性能容量是更重要的,決定能否接住你的業務壓力和承載。

大家要記住:你如果要抗的業務是百萬級別的活躍用戶,不是幾百個用戶的話,性能才是王道,性能上滿足業務的需求才是最重要的。

你功能再牛B,產品再好,性能抗不了,其他都是扯淡,幾百W人可能在幾秒鐘內就把你的整個系統和項目都搞掛掉,然後你們公司就抓瞎了。

苦心經營的用戶也會大量流失,損失就慘重了。

性能是基礎。性能能抗住,整個架構才有意義。性能抗不住,後面去考慮什麼高可用,這些都沒用。

MySQL對磁盤的利用特點

  • Binlog,redo log ,undo log順序IO

MySQL的IO類型多種多樣。

binlog,redolog,undolog,這些都是順序IO寫。

這一類東西沒太多必要放到SSD上,順序寫在傳統機械盤上也是很快的,放到SSD上有點暴殄天物,而且SSD存在寫損耗和寫壽命的問題,沒必要放到SSD上。放到傳統的SAS盤上就夠用了。沒必要放SSD。

SSD用來放datafile。因為datafile上發生的IO大部分是隨機IO,SSD跑隨機IO是非常有優勢的。SSD固態盤+傳統盤SAS盤一起混合存儲。另外,備份盤也不要用SSD。

  • Datafile隨機IO和順序IO相結合

順序IO永遠是更快的。在數據庫設計裡,決定你是不是牛B的DBA或牛B的架構師,就是看你能否把一個業務盡可能設計為順序IO,同時減少隨機IO。舉個例子:一個好友關係的業務,設計的時候希望一個query以順序IO把好友關係就拿出來,那麼怎麼設計呢?

那在MySQL的InnoDB裡面,我們可以利用InnoDB的一個特性:聚集索引表。(類似Oracle的IOT)。

利用這個特性,可以讓用戶的好友數據盡可能的聚集在一個page里或多個相鄰的page裡。那讀的時候一個順序讀IO就能搞定了,性能大大提高。

好友關係表結構如下(前提表是InnoDB引擎):

owner_id friend_id(好友id)

上面這樣的兩個字段做一個主鍵,InnoDB的主鍵就是聚集索引,那讀取這兩個字段肯定順序IO就能搞定。

以前有什麼數據庫設計的書上,總說到,每個表上必須添加一個自增的主鍵的規範,其實規範死的,應對是活的,我上面舉例的好友關係就沒有用自增的主鍵,而是具有業務屬性讀取又頻繁的兩個業務字段作主鍵,反而性能更好。

因此,大家學習,不要去死記這些書上的什麼規範和章程,而是應該真正學懂一個東西的原理,比如學好InnoDB的內部原理,然後在實際工作中,有原理的支撐,用原理去舉一反三。

InnoDB的原理是很大的一塊知識,需要日積月累的學習。大家可以多留意我的公眾號,陸續會有InnoDB的一些文章推出來。

  • OLTP業務更多的需要隨機IO

可以利用內存做緩存,從而減少隨機IO

  • OLAP業務更多需要順序IO

內存緩存作用不大

MySQL5.6之前是不支持修改page的,默認就是16K。

MySQL5.6以後可以改了,這個參數是innodb_page_size,但是MySQL5.6也只能修改為8K或4K,不能調大,直到MySQL5.7以上才可以改大為32K或64K。

對OLAP系統來說,更大的page,對性能的提升會有所幫助,因為OLAP系統都是比較大的查詢,掃描的數據很多。

2第二點:數據庫設計不好

  • 比如用了很多的數據庫特性,像Trigger, 分區,非常多的存儲過程、函數等等。

我們經常說什麼,小而美,意思就是簡單才是最好的。你把數據庫的所有功能都用上了,數據庫的性能自然就會被拖慢,可能碰到的BUG,底層故障的機率也就增加了。

所以大家要明白,一個好的數據庫項目設計,是小而美,精而簡的。另外,數據庫也只是整體項目的一部分,像Trigger,存儲過程這些能實現的,在整體項目裡面肯定也可以用應用程序代碼來完成。

所以,我們用MySQL,就是用它厲害的地方,比如:表、索引、事務這些,而不是要它所有的功能都得用上。

另外有一點,在MySQL5.6之前,生產環境的主庫裡面是不允許使用子查詢的。

MySQL5.6之前子查詢的性能特別差。(語法上是支持的,但SQL性能非常差)。

比如大家現在如果是用Oracle,想把Oracle遷移到MySQL上的話,建議大家用MySQL5.6版本,MySQL5.6對子查詢的支持和性能上都做了較大的改善。

MySQL5.6跑子查詢的性能會大大提高。

3第三點:程序寫太爛

這個估計當過DBA的同學應該都是有體會的,中小型的公司,程序員水平參差不齊。

特別是碰到很多剛入行的程序員(剛畢業的),更有可能,這些剛入行的程序員手裡還接了一些進度非常趕的需求。那這種環境下開發出來的程序,想不爛都很難了。

當然,這也不怪我們的程序員,不能怪罪他們。

造成我上述現象的原因,主要還是國內的開發環境,也沒辦法,開發需求迫切(產品天天催活),程序員忙於趕工(長期加班),只能忙與實現業務程序,根本沒時間去優化程序。

當然,這種環境下,對於我們DBA來說就是機會了。程序員寫出來的爛SQL,複雜SQL,造成系統緩慢甚至崩潰,然後我們DBA出馬,對這些爛SQL,慢SQL進行優化改造後,系統恢復正常,並日趨穩定。這也是很有成就,並且也會受到同事和領導尊重的一件事。

同時,DBA們也可以加強對程序員的培訓,加強他們快速寫出好SQL的能力。讓他們花較少的時間,也能寫出性能比較好,更得順暢的SQL語句。這樣,也可以給DBA減輕負擔。

我本人就比較喜歡跟程序員講培訓,一來大家交流技術,都有收穫,二來搞好關係,工作上有什麼事以後需要協商的也好聊。這比請他們吃飯強。

我們針對程序寫得太爛,主要有下面幾個解決方向:

  • 要讓應用使用數據庫連接池,特別是像基於JAVA開發的大型高並發應用裡,一定要使用連接池。

使用連接池的好處:就是可以限制應用的連接數,另外,不用再額外地去創建每個連接,MySQL創建連接的開銷也是較大的,因為創建一個新連接相當於MySQL創建了一個thread。

剛才我也提到,MySQL隨著連接數上升會出現性能下降。

有寫過程序代碼的同學,應該也知道,在我們一般的PC筆記本上(一般4CORE),你創建400個thread,每個thread就乾1+1+1+1+..簡單活,再sleep下,你看看你的PC電腦卡還是不卡。你會發現你PC電腦的CPU都快跑滿了。你要敢創建600個thread,那你的機器就快等著重啟吧。這就是因為thread的開銷,把CPU已經佔滿了。

  • 複雜的SQL語句

這個剛才也說了,程序員寫的SQL,一般都問題多多,他們畢竟太忙了,不會去考慮這個SQL的性能和運行情況。在一些情況下,程序員拼接的SQL,直接可以把整個系統幹跨掉。

我舉個簡單例子:我們一個應用對數據庫創建了10個連接(最大連接數=10),這10個連接每個連接都同時跑相同的一條複雜SQL,執行這個複雜SQL至少要10分鐘,那這10個連接在10分鐘以內都只能執行這個複雜SQL,其他後面的SQL全得堵著。

造成10分鐘大部分應用不可用了,對吧。而且有可能引起雪崩,造成系統崩潰。

複雜SQL的優化,也是DBA很重要的一個活,需要通過監控的手段找出這些複雜SQL、慢SQL、爛SQL,然後給出優化建議到程序員(DBA要進行性能對比測試),讓程序員改造下代碼,才能讓系統真正暢快並行地跑起來,像不堵車的高速公路一樣。

那有人會問了,我們公司的程序員就是牛B,打死不改SQL代碼,弄死了也不去優化,無法溝通。那我們該怎麼辦呢?

我們還是有辦法的,我們還可以構建一個專用的從庫(Slave庫)來處理,你換個庫查詢,總可以了吧。

比如舉我們公司的例子,我們的後台出報表的系統,就是連的從庫查詢,不給連主庫。

  • 無效邏輯
  • 全表掃描

比如:update t set a = a + 1 ; 忘加where條件了。

以你要想你的系統能支撐百萬級別的用戶在線,那還得加入SQL審核系統(SQL Review),杜絕無效邏輯的SQL,和這類全表掃描的SQL。

SQL經過DBA審核通過後,才能發布上線。

另外,這種大的update SQL應該分批更新,把大的SQL任務拆成小的任務來跑。在MySQL裡面來說,這是要特別注意的。

為什麼要分批更新呢?

  •  原因1. 上面說的,MySQL的一個query只能用到一個CORE。SQL事務太大,複雜度太高需要很久才能運行出來,容易造成擁堵。
  •  原因2.  線上環境,MySQL一般都是Master/Slave架構,如果Master發生100W行的大更新事務,很可能造成SLAVE卡在那裡,因為SLAVE是單線程結構,造成同步延遲。

MySQL寫SQL,干成小事務SQL,快速執行,快速提交。讓每個query完成得更快,讓連接更快地釋放出來。

最後,回到我們的標題,為什麼你會覺得MySQL跑得很慢,相信你應該多少有了點答案。

作者介紹 黃稚禹

  • 現任新浪愛彩票DBA總監,主要工作包括Oracle、MySQL運維以及GreenPlum數據倉庫的建設;
  • Oracle OCM,知名線上培訓機構老方塊培訓的合作夥伴以及資深講師;
  • 擅長大型項目的關係型數據庫運維和管理,顫長互聯網維度數據分析和數據挖掘。目前的主要精力在MySQL相關工作上。

原文出處:新浪愛彩票DBA總監:為什麼你的MySQL跑得很慢?

884 全部 1 今日

發表迴響

你的電子郵件位址並不會被公開。 必要欄位標記為 *

*