最近有一個業(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)計需求。

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

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

這兩個問題的解決還是存在優(yōu)先級，首先統(tǒng)計的 SQL 導(dǎo)致了系統(tǒng)資源成為瓶頸，結(jié)果原本簡單的 Insert 也成為了慢日志 SQL，相比而言，寫入需求是硬需求。

而統(tǒng)計需求是輔助需求，所以在這種場景下和業(yè)務(wù)方溝通，快速的響應(yīng)方式就是把主庫的統(tǒng)計需求轉(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ù)的掃描代價極高。

這個算是優(yōu)化的***步改進(jìn)，在這個基礎(chǔ)上，開始做索引優(yōu)化，但是通過對比，發(fā)現(xiàn)效果很有限。

因?yàn)閺膸於说氖墙y(tǒng)計需求，添加的索引只能從全表掃描降級為全索引掃描，對于系統(tǒng)整體的負(fù)載改進(jìn)卻很有限，所以我們需要對已有的架構(gòu)做一些改進(jìn)和優(yōu)化。

方案 1

考慮到資源的成本和使用場景，所以我們暫時把架構(gòu)調(diào)整為如下的方式：即添加兩個數(shù)據(jù)節(jié)點(diǎn)，然后打算啟用中間件的方式來做分布式的架構(gòu)設(shè)計。

對于從庫，暫時為了節(jié)省成本，就對原來的服務(wù)器做了資源擴(kuò)容，即單機(jī)多實(shí)例的模式，這樣一來寫入的壓力就可以完全支撐住了。

但是這種方式有一個潛在的隱患，那就是從庫的中間件層面來充當(dāng)數(shù)據(jù)統(tǒng)計的角色，一旦出現(xiàn)性能問題，對于中間件的壓力極大，很可能導(dǎo)致原本的統(tǒng)計任務(wù)會阻塞。

同時從庫端的資源瓶頸除了磁盤空間外就是 IO 壓力，目前通過空間擴(kuò)容解決不了這個硬傷。

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

方案 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)計 SQL 性能的改進(jìn)，如果 SQL 統(tǒng)計性能的改進(jìn)能夠初見成效，后續(xù)的架構(gòu)改進(jìn)就會更加輕松。

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

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

原來的主庫讀寫壓力都很大，通過讀寫分離，使得讀節(jié)點(diǎn)的壓力開始激增，而且隨著業(yè)務(wù)的擴(kuò)展，統(tǒng)計查詢的需求越來越多。

比如原來是有 10 個查詢，現(xiàn)在可能變成了 30 個，這樣一來統(tǒng)計壓力變大，導(dǎo)致系統(tǒng)響應(yīng)降低，從而導(dǎo)致從庫的延遲也開始變大。

***的時候延遲有 3 個小時，按照這種情況，統(tǒng)計的意義其實(shí)已經(jīng)不大了。

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

• 首先是和業(yè)務(wù)方進(jìn)行了細(xì)致的溝通，對于業(yè)務(wù)的場景有了一個比較清晰的認(rèn)識，其實(shí)這個業(yè)務(wù)場景是蠻適合 Redis 之類的方案來解決的，但是介于成本和性價比選擇了關(guān)系型的 MySQL，結(jié)論：暫時保持現(xiàn)狀。
• 對于讀壓力，目前不光支撐不了指數(shù)級壓力，連現(xiàn)狀都讓人擔(dān)憂。業(yè)務(wù)的每個統(tǒng)計需求涉及 5 個 SQL，要對每個場景做優(yōu)化都需要取舍。

***達(dá)到的一個初步效果是字段有 5 個，索引就有 3 個，而且不太可控的是一旦某個表的數(shù)據(jù)量太大導(dǎo)致延遲，整個系統(tǒng)的延遲就會變大，從而造成統(tǒng)計需求都整體垮掉。

所以添加索引來解決硬統(tǒng)計需求算是心有力而力不足。結(jié)論：索引優(yōu)化效果有限，需要尋求其他可行解決方案。

• 對于寫壓力，后續(xù)可以通過分片的策略來解決，這里的分片策略和我們傳統(tǒng)認(rèn)為的邏輯不同，這是基于應(yīng)用層面的分片，應(yīng)用端來做這個數(shù)據(jù)路由。這樣分片對于業(yè)務(wù)的爆發(fā)式增長就很容易擴(kuò)展了。

有了這一層保障之后，業(yè)務(wù)的統(tǒng)計需求遷移到從庫，寫壓力就能夠平滑的對接了，目前來看寫壓力的空余空間很大，完全可以支撐指數(shù)級的壓力。結(jié)論：業(yè)務(wù)數(shù)據(jù)路由在統(tǒng)計壓力減緩后再開始改進(jìn)。

為了快速改進(jìn)現(xiàn)狀，我寫了一個腳本自動采集和管理，會定時殺掉超時查詢的會話。

但是延遲還是存在，查詢依舊是慢，很難想象在指數(shù)級壓力的情況下，這個延遲會有多大。

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

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

一種行之有效的方式就是借助于數(shù)據(jù)倉庫方案，MySQL 原生不支持?jǐn)?shù)據(jù)庫倉庫，但是有第三方的解決方案：

• 一類是 ColumnStore，是在 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)計延遲問題

通過引入 Infobright 方案對已有的統(tǒng)計需求可以做到***支持，但是隨之而來的一個難點(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)計延遲，如果后期要做大量的運(yùn)營活動，需要更精確的數(shù)據(jù)支持，要得到半個小時的統(tǒng)計數(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ù)全量抽取更加順利。

所以需要對上述腳本再做一層保證，通過計算當(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ū)交流分享!

作者：楊建榮

簡介：競技世界資深 DBA，前搜狐暢游數(shù)據(jù)庫專家，Oracle ACE，YEP 成員。擁有近十年數(shù)據(jù)庫開發(fā)和運(yùn)維經(jīng)驗(yàn)，目前專注于開源技術(shù)、運(yùn)維自動化和性能調(diào)優(yōu)。擁有 Oracle 10g OCP、OCM、MySQL OCP 認(rèn)證，對 Shell、Java 有一定功底。每天通過微信、 博客進(jìn)行技術(shù)分享，已連續(xù)堅持 1800 多天。