MySQL數(shù)據(jù)庫(kù)架構(gòu)優(yōu)化方案

1、 MySQL數(shù)據(jù)庫(kù)架構(gòu)優(yōu)化方案目 錄 TOC o 1-3 h z u HYPERLINK l _Toc533532619 一、引入讀寫(xiě)分離，優(yōu)化初見(jiàn)成效 PAGEREF _Toc533532619 h 3 HYPERLINK l _Toc533532620 二、引入列式存儲(chǔ)，優(yōu)化統(tǒng)計(jì)性能 PAGEREF _Toc533532620 h 10 HYPERLINK l _Toc533532621 三、引入動(dòng)態(tài)調(diào)度，解決統(tǒng)計(jì)延遲問(wèn)題 PAGEREF _Toc533532621 h 16 HYPERLINK l _Toc533532622 四、引入業(yè)務(wù)路由，平滑支持業(yè)務(wù)擴(kuò)容 PAGEREF _Toc5

2、33532622 h 21最近有一個(gè)業(yè)務(wù)庫(kù)的負(fù)載比往常高了很多，最直觀的印象就是原來(lái)的負(fù)載最高是100%，現(xiàn)在不是翻了幾倍或者指數(shù)級(jí)增長(zhǎng)，而是突然翻了100倍，導(dǎo)致業(yè)務(wù)后端的數(shù)據(jù)寫(xiě)入劇增，產(chǎn)生了嚴(yán)重的性能阻塞。一、引入讀寫(xiě)分離，優(yōu)化初見(jiàn)成效這類(lèi)問(wèn)題引起了我的興趣和好奇心，經(jīng)過(guò)和業(yè)務(wù)方溝通了解，這個(gè)業(yè)務(wù)是記錄回執(zhí)數(shù)據(jù)的，簡(jiǎn)單來(lái)說(shuō)就好比你發(fā)送了一條微博，想看看有多少人已讀，有多少人留言等。所以這類(lèi)場(chǎng)景不存在事務(wù)，會(huì)有數(shù)據(jù)的密集型寫(xiě)入，會(huì)有明確的統(tǒng)計(jì)需求。目前的統(tǒng)計(jì)頻率是每7分鐘做一次統(tǒng)計(jì)，會(huì)有幾類(lèi)統(tǒng)計(jì)場(chǎng)景，目前基本都是全表掃描級(jí)別的查詢語(yǔ)句。當(dāng)前數(shù)據(jù)庫(kù)的架構(gòu)很簡(jiǎn)單，是一個(gè)主從，外加MHA高可用。問(wèn)

3、題的改進(jìn)方向是減少主庫(kù)的壓力，分別是讀和寫(xiě)的壓力。寫(xiě)入的壓力來(lái)自于業(yè)務(wù)的并發(fā)寫(xiě)入壓力，而讀的壓力來(lái)自于于全表掃描的壓力，對(duì)于CPU和IO壓力都很大。這兩個(gè)問(wèn)題的解決還是存在優(yōu)先級(jí)，首先統(tǒng)計(jì)的SQL導(dǎo)致了系統(tǒng)資源成為瓶頸，結(jié)果原本簡(jiǎn)單的Insert也成為了慢日志SQL，相比而言，寫(xiě)入需求是硬需求，而統(tǒng)計(jì)需求是輔助需求，所以在這種場(chǎng)景下和業(yè)務(wù)方溝通，快速的響應(yīng)方式就是把主庫(kù)的統(tǒng)計(jì)需求轉(zhuǎn)移到從庫(kù)端。轉(zhuǎn)移了讀請(qǐng)求的負(fù)載，寫(xiě)入壓力得到了極大緩解，后來(lái)也經(jīng)過(guò)業(yè)務(wù)方的應(yīng)用層面的優(yōu)化，整體的負(fù)載情況就相對(duì)樂(lè)觀了：主庫(kù)的監(jiān)控負(fù)載如下，可以看到有一個(gè)明顯降低的趨勢(shì)，CPU負(fù)載從原來(lái)的90%以上降到了不到10%。I

4、O的壓力也從原來(lái)的近100%降到了25%左右。從庫(kù)的監(jiān)控負(fù)載如下，可以看到壓力有了明顯的提升。CPU層面的體現(xiàn)不夠明顯，主要的壓力在于IO層面，即全表數(shù)據(jù)的掃描代價(jià)極高。這個(gè)算是優(yōu)化的第一步改進(jìn)，在這個(gè)基礎(chǔ)上，開(kāi)始做索引優(yōu)化，但是通過(guò)對(duì)比，發(fā)現(xiàn)效果很有限。因?yàn)閺膸?kù)端的是統(tǒng)計(jì)需求，添加的索引只能從全表掃描降級(jí)為全索引掃描，對(duì)于系統(tǒng)整體的負(fù)載改進(jìn)卻很有限，所以我們需要對(duì)已有的架構(gòu)做一些改進(jìn)和優(yōu)化。方案1：考慮到資源的成本和使用場(chǎng)景，所以我們暫時(shí)把架構(gòu)調(diào)整為如下的方式：即添加兩個(gè)數(shù)據(jù)節(jié)點(diǎn)，然后打算啟用中間件的方式來(lái)做分布式的架構(gòu)設(shè)計(jì)。對(duì)于從庫(kù)，暫時(shí)為了節(jié)省成本，就對(duì)原來(lái)的服務(wù)器做了資源擴(kuò)容，即單機(jī)多

5、實(shí)例的模式，這樣一來(lái)寫(xiě)入的壓力就可以完全支撐住了。但是這種方式有一個(gè)潛在的隱患，那就是從庫(kù)的中間件層面來(lái)充當(dāng)數(shù)據(jù)統(tǒng)計(jì)的角色，一旦出現(xiàn)性能問(wèn)題，對(duì)于中間件的壓力極大，很可能導(dǎo)致原本的統(tǒng)計(jì)任務(wù)會(huì)阻塞。同時(shí)從庫(kù)端的資源瓶頸除了磁盤(pán)空間外就是IO壓力，目前通過(guò)空間擴(kuò)容解決不了這個(gè)硬傷。在和業(yè)務(wù)同學(xué)進(jìn)一步溝通后，發(fā)現(xiàn)他們對(duì)于這一類(lèi)表的創(chuàng)建是動(dòng)態(tài)配置的方式，在目前的中間件方案中很難以落實(shí)。而且對(duì)于業(yè)務(wù)來(lái)說(shuō)，統(tǒng)計(jì)需求變得更加不透明了。方案2：一種行之有效的改進(jìn)方式就是從應(yīng)用層面來(lái)做數(shù)據(jù)路由，比如有10個(gè)業(yè)務(wù)：業(yè)務(wù)1、業(yè)務(wù)2在第一個(gè)節(jié)點(diǎn)，業(yè)務(wù)3、業(yè)務(wù)5在第二個(gè)節(jié)點(diǎn)等等，按照這種路由的配置方式來(lái)映射數(shù)據(jù)源，相對(duì)

6、可控，更容易擴(kuò)展，所以架構(gòu)方式改為了這種：而整個(gè)的改進(jìn)中，最關(guān)鍵的一環(huán)是對(duì)于統(tǒng)計(jì)SQL性能的改進(jìn)，如果SQL統(tǒng)計(jì)性能的改進(jìn)能夠初見(jiàn)成效，后續(xù)的架構(gòu)改進(jìn)就會(huì)更加輕松。二、引入列式存儲(chǔ)，優(yōu)化統(tǒng)計(jì)性能后續(xù)有開(kāi)始有了業(yè)務(wù)的爆發(fā)式增長(zhǎng)，使得統(tǒng)計(jì)需求的優(yōu)化成為本次優(yōu)化的關(guān)鍵所在。原來(lái)的主庫(kù)讀寫(xiě)壓力都很大，通過(guò)讀寫(xiě)分離，使得讀節(jié)點(diǎn)的壓力開(kāi)始激增，而且隨著業(yè)務(wù)的擴(kuò)展，統(tǒng)計(jì)查詢的需求越來(lái)越多。比如原來(lái)是有10個(gè)查詢，現(xiàn)在可能變成了30個(gè)，這樣一來(lái)統(tǒng)計(jì)壓力變大，導(dǎo)致系統(tǒng)響應(yīng)降低，從而導(dǎo)致從庫(kù)的延遲也開(kāi)始變大。最大的時(shí)候延遲有3個(gè)小時(shí)，按照這種情況，統(tǒng)計(jì)的意義其實(shí)已經(jīng)不大了。對(duì)此我做了幾個(gè)方面的改進(jìn)：首先是和業(yè)務(wù)方

7、進(jìn)行了細(xì)致的溝通，對(duì)于業(yè)務(wù)的場(chǎng)景有了一個(gè)比較清晰的認(rèn)識(shí)，其實(shí)這個(gè)業(yè)務(wù)場(chǎng)景是蠻適合Redis之類(lèi)的方案來(lái)解決的，但是介于成本和性價(jià)比選擇了關(guān)系型的MySQL，結(jié)論：暫時(shí)保持現(xiàn)狀。對(duì)于讀壓力，目前不光支撐不了指數(shù)級(jí)壓力，連現(xiàn)狀都讓人擔(dān)憂。業(yè)務(wù)的每個(gè)統(tǒng)計(jì)需求涉及5個(gè)SQL，要對(duì)每個(gè)場(chǎng)景做優(yōu)化都需要取舍，最后達(dá)到的一個(gè)初步效果是字段有5個(gè)，索引就有3個(gè)，而且不太可控的是一旦某個(gè)表的數(shù)據(jù)量太大導(dǎo)致延遲，整個(gè)系統(tǒng)的延遲就會(huì)變大，從而造成統(tǒng)計(jì)需求都整體垮掉，所以添加索引來(lái)解決硬統(tǒng)計(jì)需求算是心有力而力不足。結(jié)論：索引優(yōu)化效果有限，需要尋求其他可行解決方案。對(duì)于寫(xiě)壓力，后續(xù)可以通過(guò)分片的策略來(lái)解決，這里的分片策

8、略和我們傳統(tǒng)認(rèn)為的邏輯不同，這是基于應(yīng)用層面的分片，應(yīng)用端來(lái)做這個(gè)數(shù)據(jù)路由。這樣分片對(duì)于業(yè)務(wù)的爆發(fā)式增長(zhǎng)就很容易擴(kuò)展了。有了這一層保障之后，業(yè)務(wù)的統(tǒng)計(jì)需求遷移到從庫(kù)，寫(xiě)壓力就能夠平滑的對(duì)接了，目前來(lái)看寫(xiě)壓力的空余空間很大，完全可以支撐指數(shù)級(jí)的壓力。結(jié)論：業(yè)務(wù)數(shù)據(jù)路由在統(tǒng)計(jì)壓力減緩后再開(kāi)始改進(jìn)。為了快速改進(jìn)現(xiàn)狀，我寫(xiě)了一個(gè)腳本自動(dòng)采集和管理，會(huì)定時(shí)殺掉超時(shí)查詢的會(huì)話。但是延遲還是存在，查詢依舊是慢，很難想象在指數(shù)級(jí)壓力的情況下，這個(gè)延遲會(huì)有多大。在做了大量的對(duì)比測(cè)試之后，按照單表3500萬(wàn)的數(shù)據(jù)量，8張同樣數(shù)據(jù)量的表，5條統(tǒng)計(jì)SQL，做完統(tǒng)計(jì)大約需要1718分鐘左右，平均每個(gè)表需要大約2分多鐘。

9、因?yàn)椴皇菦](méi)有事務(wù)關(guān)聯(lián)，所以這個(gè)場(chǎng)景的延遲根據(jù)業(yè)務(wù)場(chǎng)景和技術(shù)實(shí)現(xiàn)來(lái)說(shuō)是肯定存在的，我們的改進(jìn)方法是提高統(tǒng)計(jì)的查詢效率，同時(shí)保證系統(tǒng)的壓力在可控范圍內(nèi)。一種行之有效的方式就是借助于數(shù)據(jù)倉(cāng)庫(kù)方案，MySQL原生不支持?jǐn)?shù)據(jù)庫(kù)倉(cāng)庫(kù)，但是有第三方的解決方案：一類(lèi)是ColumStore，是在InfiniDB的基礎(chǔ)上改造的；一類(lèi)是Infobright，除此之外還有其他大型的解決方案，比如Greenplum的MPP方案，ColumnStore的方案有點(diǎn)類(lèi)似于這種MPP方案，需要的是分布式節(jié)點(diǎn)，所以在資源和架構(gòu)上Infobright更加輕量一些。我們的表結(jié)構(gòu)很簡(jiǎn)單，字段類(lèi)型也是基本類(lèi)型，而且在團(tuán)隊(duì)內(nèi)部也有大量的實(shí)

10、踐經(jīng)驗(yàn)。改進(jìn)之后的整體架構(gòu)如下，原生的主從架構(gòu)不受影響：需要在此基礎(chǔ)上擴(kuò)展一個(gè)數(shù)據(jù)倉(cāng)庫(kù)節(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 動(dòng)作, readtimes int(11) NOT NULL DEFAULT 0 COMMENT 閱讀次數(shù), create_time datetime N

11、OT NULL COMMENT 創(chuàng)建時(shí)間) ;導(dǎo)出的語(yǔ)句類(lèi)似于：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ū)版，將專(zhuān)注于IEE的開(kāi)發(fā)，所以后續(xù)的支持力度其實(shí)就很有限了。對(duì)于我們目前的需求來(lái)說(shuō)是游刃有余。來(lái)簡(jiǎn)單感受下Infobright的實(shí)力：select cou

12、nt( id) from testxxx where id2000;+| count( id) |+| 727686205 |+1 row in set (6.20 sec)select count( id) from testxxxx where idselect count( distinct id) from testxxxx where id2000;+| count( distinct id) |+| 1999 |+1 row in set (10.20 sec)所以對(duì)于幾千萬(wàn)的表來(lái)說(shuō)，這都不是事兒。我把3500萬(wàn)的數(shù)據(jù)導(dǎo)入到Infobright里面，5條查詢語(yǔ)句總共的執(zhí)行時(shí)間維持在

13、14秒，相比原來(lái)的2分鐘多已經(jīng)改進(jìn)很大了。我跑了下批量的查詢，原本要18分鐘，現(xiàn)在只需要不到3分鐘。三、引入動(dòng)態(tài)調(diào)度，解決統(tǒng)計(jì)延遲問(wèn)題通過(guò)引入Infobright方案對(duì)已有的統(tǒng)計(jì)需求可以做到完美支持，但是隨之而來(lái)的一個(gè)難點(diǎn)就是對(duì)于數(shù)據(jù)的流轉(zhuǎn)如何平滑支持。我們可以設(shè)定流轉(zhuǎn)頻率，比如10分鐘等或者半個(gè)小時(shí)，但是目前來(lái)看，這個(gè)是需要額外的腳本或工具來(lái)做的。在具體落地的過(guò)程中，發(fā)現(xiàn)有一大堆的事情需要提前搞定。其一：比如第一個(gè)頭疼的問(wèn)題就是全量的同步，第一次同步肯定是全量的，這么多的數(shù)據(jù)怎么同步到Infobright里面。第二個(gè)問(wèn)題，也是更為關(guān)鍵的，那就是同步策略是怎么設(shè)定的，是否可以支持的更加靈活。第

14、三個(gè)問(wèn)題是基于現(xiàn)有的增量同步方案，需要在時(shí)間字段上添加索引。對(duì)于線上的操作而言又是一個(gè)巨大的挑戰(zhàn)。其二：從目前的業(yè)務(wù)需求來(lái)說(shuō)，最多能夠允許一個(gè)小時(shí)的統(tǒng)計(jì)延遲，如果后期要做大量的運(yùn)營(yíng)活動(dòng)，需要更精確的數(shù)據(jù)支持，要得到半個(gè)小時(shí)的統(tǒng)計(jì)數(shù)據(jù)，按照現(xiàn)有的方案是否能夠支持。這兩個(gè)主要的問(wèn)題，任何一個(gè)解決不了，數(shù)據(jù)流轉(zhuǎn)能夠落地都是難題，這個(gè)問(wèn)題留給我的時(shí)間只有一天。所以我準(zhǔn)備把前期的準(zhǔn)備和測(cè)試做得扎實(shí)一些，后期接入的時(shí)候就會(huì)順暢得多。部分腳本實(shí)現(xiàn)如下：腳本的輸入?yún)?shù)有兩個(gè)，一個(gè)是起始時(shí)間，一個(gè)是截止時(shí)間。第一次全量同步的時(shí)候，可以把起始時(shí)間給的早一些，這樣截止時(shí)間是固定的，邏輯上就是全量的。另外全量同步的時(shí)

15、候一定要確保主從延遲已經(jīng)最低或者暫時(shí)停掉查詢業(yè)務(wù)，使得數(shù)據(jù)全量抽取更加順利。所以需要對(duì)上述腳本再做一層保證，通過(guò)計(jì)算當(dāng)前時(shí)間和上一次執(zhí)行的時(shí)間來(lái)得到任務(wù)可執(zhí)行的時(shí)間。這樣腳本就不需要參數(shù)了，這是一個(gè)動(dòng)態(tài)調(diào)度的迭代過(guò)程?？紤]到每天落盤(pán)的數(shù)據(jù)量大概在10G左右，日志量在30G左右，所以考慮先使用客戶端導(dǎo)入Infobright的方式來(lái)操作。從實(shí)踐來(lái)看，涉及的表有600多個(gè)，我先導(dǎo)出了一個(gè)列表，按照數(shù)據(jù)量來(lái)排序，這樣小表就可以快速導(dǎo)入，大表放在最后，整個(gè)數(shù)據(jù)量有150G左右，通過(guò)網(wǎng)絡(luò)傳輸導(dǎo)入Infobright，從導(dǎo)出到導(dǎo)入完成，這個(gè)過(guò)程大概需要1個(gè)小時(shí)。而導(dǎo)入數(shù)據(jù)到Infobright之后的性能提升也是極為明顯的。原來(lái)的一組查詢持續(xù)時(shí)間在半個(gè)小時(shí)，現(xiàn)在在70秒鐘即可完成。對(duì)于業(yè)務(wù)的體驗(yàn)來(lái)說(shuō)大大提高。完成了第一次同步之后，后續(xù)的同步都可以根據(jù)實(shí)際的情況來(lái)靈活控制。所以數(shù)據(jù)增量同步暫時(shí)是“手動(dòng)擋”控制。從整個(gè)數(shù)據(jù)架構(gòu)分離之后的效果來(lái)看，從庫(kù)的壓力大大降低，而效率也大大提高。四、引入業(yè)務(wù)路由，平滑支持業(yè)務(wù)擴(kuò)容前面算是對(duì)現(xiàn)狀做到了最大程度的優(yōu)化，但是還有一個(gè)問(wèn)題，目前的架構(gòu)暫時(shí)能夠支撐密集型數(shù)據(jù)寫(xiě)入，但