最近有一個業(yè)務(wù)庫的負(fù)載比往常高了很多，最直觀的印象就是原來的負(fù)載最高是100%，現(xiàn)在不是翻了幾倍或者指數(shù)級增長，而是突然翻了100倍，導(dǎo)致業(yè)務(wù)后端的數(shù)據(jù)寫入劇增，產(chǎn)生了嚴(yán)重的性能阻塞。

一、引入讀寫分離，優(yōu)化初見成效

這類問題引起了我的興趣和好奇心，經(jīng)過和業(yè)務(wù)方溝通了解，這個業(yè)務(wù)是記錄回執(zhí)數(shù)據(jù)的，簡單來說就好比你發(fā)送了一條微博，想看看有多少人已讀，有多少人留言等。所以這類場景不存在事務(wù)，會有數(shù)據(jù)的密集型寫入，會有明確的統(tǒng)計(jì)需求。

目前的統(tǒng)計(jì)頻率是每7分鐘做一次統(tǒng)計(jì)，會有幾類統(tǒng)計(jì)場景，目前基本都是全表掃描級別的查詢語句。當(dāng)前數(shù)據(jù)庫的架構(gòu)很簡單，是一個主從，外加MHA高可用。

問題的改進(jìn)方向是減少主庫的壓力，分別是讀和寫的壓力。寫入的壓力來自于業(yè)務(wù)的并發(fā)寫入壓力，而讀的壓力來自于于全表掃描的壓力，對于CPU和IO壓力都很大。

這兩個問題的解決還是存在優(yōu)先級，首先統(tǒng)計(jì)的SQL導(dǎo)致了系統(tǒng)資源成為瓶頸，結(jié)果原本簡單的Insert也成為了慢日志SQL，相比而言，寫入需求是硬需求，而統(tǒng)計(jì)需求是輔助需求，所以在這種場景下和業(yè)務(wù)方溝通，快速的響應(yīng)方式就是把主庫的統(tǒng)計(jì)需求轉(zhuǎn)移到從庫端。

轉(zhuǎn)移了讀請求的負(fù)載，寫入壓力得到了極大緩解，后來也經(jīng)過業(yè)務(wù)方的應(yīng)用層面的優(yōu)化，整體的負(fù)載情況就相對樂觀了：

•  主庫的監(jiān)控負(fù)載如下，可以看到有一個明顯降低的趨勢，CPU負(fù)載從原來的90%以上降到了不到10%。IO的壓力也從原來的近100%降到了25%左右。

•  從庫的監(jiān)控負(fù)載如下，可以看到壓力有了明顯的提升。CPU層面的體現(xiàn)不夠明顯，主要的壓力在于IO層面，即全表數(shù)據(jù)的掃描代價(jià)極高。

這個算是優(yōu)化的第一步改進(jìn)，在這個基礎(chǔ)上，開始做索引優(yōu)化，但是通過對比，發(fā)現(xiàn)效果很有限。因?yàn)閺膸於说氖墙y(tǒng)計(jì)需求，添加的索引只能從全表掃描降級為全索引掃描，對于系統(tǒng)整體的負(fù)載改進(jìn)卻很有限，所以我們需要對已有的架構(gòu)做一些改進(jìn)和優(yōu)化。

方案1：

考慮到資源的成本和使用場景，所以我們暫時把架構(gòu)調(diào)整為如下的方式：即添加兩個數(shù)據(jù)節(jié)點(diǎn)，然后打算啟用中間件的方式來做分布式的架構(gòu)設(shè)計(jì)。對于從庫，暫時為了節(jié)省成本，就對原來的服務(wù)器做了資源擴(kuò)容，即單機(jī)多實(shí)例的模式，這樣一來寫入的壓力就可以完全支撐住了。

但是這種方式有一個潛在的隱患，那就是從庫的中間件層面來充當(dāng)數(shù)據(jù)統(tǒng)計(jì)的角色，一旦出現(xiàn)性能問題，對于中間件的壓力極大，很可能導(dǎo)致原本的統(tǒng)計(jì)任務(wù)會阻塞。同時從庫端的資源瓶頸除了磁盤空間外就是IO壓力，目前通過空間擴(kuò)容解決不了這個硬傷。

在和業(yè)務(wù)同學(xué)進(jìn)一步溝通后，發(fā)現(xiàn)他們對于這一類表的創(chuàng)建是動態(tài)配置的方式，在目前的中間件方案中很難以落實(shí)。而且對于業(yè)務(wù)來說，統(tǒng)計(jì)需求變得更加不透明了。

方案2：

一種行之有效的改進(jìn)方式就是從應(yīng)用層面來做數(shù)據(jù)路由，比如有10個業(yè)務(wù)：業(yè)務(wù)1、業(yè)務(wù)2在第一個節(jié)點(diǎn)，業(yè)務(wù)3、業(yè)務(wù)5在第二個節(jié)點(diǎn)等等，按照這種路由的配置方式來映射數(shù)據(jù)源，相對可控，更容易擴(kuò)展，所以架構(gòu)方式改為了這種：

而整個的改進(jìn)中，最關(guān)鍵的一環(huán)是對于統(tǒng)計(jì)SQL性能的改進(jìn)，如果SQL統(tǒng)計(jì)性能的改進(jìn)能夠初見成效，后續(xù)的架構(gòu)改進(jìn)就會更加輕松。

二、引入列式存儲，優(yōu)化統(tǒng)計(jì)性能

后續(xù)有開始有了業(yè)務(wù)的爆發(fā)式增長，使得統(tǒng)計(jì)需求的優(yōu)化成為本次優(yōu)化的關(guān)鍵所在。

原來的主庫讀寫壓力都很大，通過讀寫分離，使得讀節(jié)點(diǎn)的壓力開始激增，而且隨著業(yè)務(wù)的擴(kuò)展，統(tǒng)計(jì)查詢的需求越來越多。比如原來是有10個查詢，現(xiàn)在可能變成了30個，這樣一來統(tǒng)計(jì)壓力變大，導(dǎo)致系統(tǒng)響應(yīng)降低，從而導(dǎo)致從庫的延遲也開始變大。最大的時候延遲有3個小時，按照這種情況，統(tǒng)計(jì)的意義其實(shí)已經(jīng)不大了。

對此我做了幾個方面的改進(jìn)：

•  首先是和業(yè)務(wù)方進(jìn)行了細(xì)致的溝通，對于業(yè)務(wù)的場景有了一個比較清晰的認(rèn)識，其實(shí)這個業(yè)務(wù)場景是蠻適合Redis之類的方案來解決的，但是介于成本和性價(jià)比選擇了關(guān)系型的MySQL，結(jié)論：暫時保持現(xiàn)狀。
•  對于讀壓力，目前不光支撐不了指數(shù)級壓力，連現(xiàn)狀都讓人擔(dān)憂。業(yè)務(wù)的每個統(tǒng)計(jì)需求涉及5個SQL，要對每個場景做優(yōu)化都需要取舍，最后達(dá)到的一個初步效果是字段有5個，索引就有3個，而且不太可控的是一旦某個表的數(shù)據(jù)量太大導(dǎo)致延遲，整個系統(tǒng)的延遲就會變大，從而造成統(tǒng)計(jì)需求都整體垮掉，所以添加索引來解決硬統(tǒng)計(jì)需求算是心有力而力不足。結(jié)論：索引優(yōu)化效果有限，需要尋求其他可行解決方案。
•  對于寫壓力，后續(xù)可以通過分片的策略來解決，這里的分片策略和我們傳統(tǒng)認(rèn)為的邏輯不同，這是基于應(yīng)用層面的分片，應(yīng)用端來做這個數(shù)據(jù)路由。這樣分片對于業(yè)務(wù)的爆發(fā)式增長就很容易擴(kuò)展了。有了這一層保障之后，業(yè)務(wù)的統(tǒng)計(jì)需求遷移到從庫，寫壓力就能夠平滑的對接了，目前來看寫壓力的空余空間很大，完全可以支撐指數(shù)級的壓力。結(jié)論：業(yè)務(wù)數(shù)據(jù)路由在統(tǒng)計(jì)壓力減緩后再開始改進(jìn)。

為了快速改進(jìn)現(xiàn)狀，我寫了一個腳本自動采集和管理，會定時殺掉超時查詢的會話。但是延遲還是存在，查詢依舊是慢，很難想象在指數(shù)級壓力的情況下，這個延遲會有多大。

在做了大量的對比測試之后，按照單表3500萬的數(shù)據(jù)量，8張同樣數(shù)據(jù)量的表，5條統(tǒng)計(jì)SQL，做完統(tǒng)計(jì)大約需要17~18分鐘左右，平均每個表需要大約2分多鐘。

因?yàn)椴皇菦]有事務(wù)關(guān)聯(lián)，所以這個場景的延遲根據(jù)業(yè)務(wù)場景和技術(shù)實(shí)現(xiàn)來說是肯定存在的，我們的改進(jìn)方法是提高統(tǒng)計(jì)的查詢效率，同時保證系統(tǒng)的壓力在可控范圍內(nèi)。

一種行之有效的方式就是借助于數(shù)據(jù)倉庫方案，MySQL原生不支持?jǐn)?shù)據(jù)庫倉庫，但是有第三方的解決方案：一類是ColumStore，是在InfiniDB的基礎(chǔ)上改造的；一類是Infobright，除此之外還有其他大型的解決方案，比如Greenplum的MPP方案，ColumnStore的方案有點(diǎn)類似于這種MPP方案，需要的是分布式節(jié)點(diǎn)，所以在資源和架構(gòu)上Infobright更加輕量一些。

我們的表結(jié)構(gòu)很簡單，字段類型也是基本類型，而且在團(tuán)隊(duì)內(nèi)部也有大量的實(shí)踐經(jīng)驗(yàn)。

改進(jìn)之后的整體架構(gòu)如下，原生的主從架構(gòu)不受影響：

需要在此基礎(chǔ)上擴(kuò)展一個數(shù)據(jù)倉庫節(jié)點(diǎn)，數(shù)據(jù)量可以根據(jù)需要繼續(xù)擴(kuò)容。

表結(jié)構(gòu)如下： 

CREATE TABLE `receipt_12149_428` (  
  `id` int(11)  NOT NULL COMMENT '自增主鍵',  
  `userid` int(11)  NOT NULL DEFAULT '0' COMMENT '用戶ID',  
  `action` int(11)  NOT NULL DEFAULT '0' COMMENT '動作',  
  `readtimes` int(11)  NOT NULL DEFAULT '0' COMMENT '閱讀次數(shù)',  
  `create_time` datetime NOT NULL  COMMENT '創(chuàng)建時間'  
)   ; 

導(dǎo)出的語句類似于： 

select *from ${tab_name} where create_time between xxx and xxxx  into outfile '/data/dump_data/${tab_name}.csv' FIELDS TERMINATED BY ' ' ENCLOSED BY '\"';  

Infobright社區(qū)版是不支持DDL和DML的，后期Infobright官方宣布：不再發(fā)布ICE社區(qū)版，將專注于IEE的開發(fā)，所以后續(xù)的支持力度其實(shí)就很有限了。對于我們目前的需求來說是游刃有余。

來簡單感受下Infobright的實(shí)力： 

>select count( id) from testxxx where id>2000;  
+------------+  
| count( id) |  
+------------+  
|  727686205 |  
+------------+  
1 row in set (6.20 sec)  
>select count( id) from testxxxx where id<2000;  
+------------+  
| count( id) |  
+------------+  
|   13826684 |  
+------------+  
1 row in set (8.21 sec)  
>select count( distinct id) from testxxxx where id<2000;  
+---------------------+  
| count( distinct id) |  
+---------------------+  
|                1999 |  
+---------------------+  
1 row in set (10.20 sec) 

所以對于幾千萬的表來說，這都不是事兒。

我把3500萬的數(shù)據(jù)導(dǎo)入到Infobright里面，5條查詢語句總共的執(zhí)行時間維持在14秒，相比原來的2分鐘多已經(jīng)改進(jìn)很大了。我跑了下批量的查詢，原本要18分鐘，現(xiàn)在只需要不到3分鐘。

三、引入動態(tài)調(diào)度，解決統(tǒng)計(jì)延遲問題

通過引入Infobright方案對已有的統(tǒng)計(jì)需求可以做到完美支持，但是隨之而來的一個難點(diǎn)就是對于數(shù)據(jù)的流轉(zhuǎn)如何平滑支持。我們可以設(shè)定流轉(zhuǎn)頻率，比如10分鐘等或者半個小時，但是目前來看，這個是需要額外的腳本或工具來做的。

在具體落地的過程中，發(fā)現(xiàn)有一大堆的事情需要提前搞定。

其一：

•  比如第一個頭疼的問題就是全量的同步，第一次同步肯定是全量的，這么多的數(shù)據(jù)怎么同步到Infobright里面。 
•  第二個問題，也是更為關(guān)鍵的，那就是同步策略是怎么設(shè)定的，是否可以支持的更加靈活。
•  第三個問題是基于現(xiàn)有的增量同步方案，需要在時間字段上添加索引。對于線上的操作而言又是一個巨大的挑戰(zhàn)。

其二：

從目前的業(yè)務(wù)需求來說，最多能夠允許一個小時的統(tǒng)計(jì)延遲，如果后期要做大量的運(yùn)營活動，需要更精確的數(shù)據(jù)支持，要得到半個小時的統(tǒng)計(jì)數(shù)據(jù)，按照現(xiàn)有的方案是否能夠支持。

這兩個主要的問題，任何一個解決不了，數(shù)據(jù)流轉(zhuǎn)能夠落地都是難題，這個問題留給我的時間只有一天。所以我準(zhǔn)備把前期的準(zhǔn)備和測試做得扎實(shí)一些，后期接入的時候就會順暢得多。

部分腳本實(shí)現(xiàn)如下：

腳本的輸入?yún)?shù)有兩個，一個是起始時間，一個是截止時間。第一次全量同步的時候，可以把起始時間給的早一些，這樣截止時間是固定的，邏輯上就是全量的。另外全量同步的時候一定要確保主從延遲已經(jīng)最低或者暫時停掉查詢業(yè)務(wù)，使得數(shù)據(jù)全量抽取更加順利。

所以需要對上述腳本再做一層保證，通過計(jì)算當(dāng)前時間和上一次執(zhí)行的時間來得到任務(wù)可執(zhí)行的時間。這樣腳本就不需要參數(shù)了，這是一個動態(tài)調(diào)度的迭代過程。

考慮到每天落盤的數(shù)據(jù)量大概在10G左右，日志量在30G左右，所以考慮先使用客戶端導(dǎo)入Infobright的方式來操作。

從實(shí)踐來看，涉及的表有600多個，我先導(dǎo)出了一個列表，按照數(shù)據(jù)量來排序，這樣小表就可以快速導(dǎo)入，大表放在最后，整個數(shù)據(jù)量有150G左右，通過網(wǎng)絡(luò)傳輸導(dǎo)入Infobright，從導(dǎo)出到導(dǎo)入完成，這個過程大概需要1個小時。

而導(dǎo)入數(shù)據(jù)到Infobright之后的性能提升也是極為明顯的。原來的一組查詢持續(xù)時間在半個小時，現(xiàn)在在70秒鐘即可完成。對于業(yè)務(wù)的體驗(yàn)來說大大提高。完成了第一次同步之后，后續(xù)的同步都可以根據(jù)實(shí)際的情況來靈活控制。所以數(shù)據(jù)增量同步暫時是“手動擋”控制。 

從整個數(shù)據(jù)架構(gòu)分離之后的效果來看，從庫的壓力大大降低，而效率也大大提高。

四、引入業(yè)務(wù)路由，平滑支持業(yè)務(wù)擴(kuò)容

前面算是對現(xiàn)狀做到了最大程度的優(yōu)化，但是還有一個問題，目前的架構(gòu)暫時能夠支撐密集型數(shù)據(jù)寫入，但是不能夠支持指數(shù)級別的壓力請求，而且存儲容量很難以擴(kuò)展。

從我的理解中，業(yè)務(wù)層面來做數(shù)據(jù)路由是最好的一種方式，而且從擴(kuò)展上來說，也更加友好。

所以再進(jìn)一層的改進(jìn)方案如下：

通過數(shù)據(jù)路由來達(dá)到負(fù)載均衡，從目前來看效果是很明顯的，而在后續(xù)要持續(xù)的擴(kuò)容時，對于業(yè)務(wù)來說也是一種可控的方式。以下是近期的一些優(yōu)化時間段里從庫的IO的壓力情況：

經(jīng)過陸續(xù)幾次地解決問題、補(bǔ)充并跟進(jìn)方案，我們完成了從最初的故障到落地成功，MySQL性能擴(kuò)展的架構(gòu)優(yōu)化分享也已經(jīng)基本了結(jié)。如有更好的實(shí)現(xiàn)方式，歡迎大家在留言區(qū)交流分享！