從云數(shù)據(jù)遷移服務(wù)看MySQL大表抽取模式的原理解析

摘要：MySQL JDBC抽取到底應(yīng)該采用什么樣的方式，且聽(tīng)小編給你娓娓道來(lái)。

小編最近在云上的一個(gè)遷移項(xiàng)目中被MySQL抽取模式折磨的很慘。一開(kāi)始爆內(nèi)存被客戶懟，再后來(lái)遷移效率低下再被懟。MySQL JDBC抽取到底應(yīng)該采用什么樣的方式，且聽(tīng)小編給你娓娓道來(lái)。

1.1 Java-JDBC通信原理

JDBC與數(shù)據(jù)庫(kù)之間的通信是通過(guò)socket完，大致流程如下圖所示。Mysql Server ->內(nèi)核Socket Buffer -> 客戶端Socket Buffer ->JDBC所在的JVM

1.2 JDBC讀取數(shù)據(jù)的三種模式

1.2.1 方式1：使用JDBC默認(rèn)參數(shù)讀取數(shù)據(jù)

主要分為以下幾步：

1）Mysql Server通過(guò)OuputStream 向 Socket Server 本地Kennel Buffer 寫(xiě)入數(shù)據(jù)，這里是一次內(nèi)存拷貝。

2）當(dāng)Socket Server 本地Kennel Buffer 有數(shù)據(jù)，就會(huì)通過(guò)TCP鏈路把數(shù)據(jù)傳輸?shù)絊ocket Client 所在機(jī)器的Kennel Buffer。

3）JDBC 所在JVM利用InputSream讀取本地Kennel Buffer 數(shù)據(jù)到JVM內(nèi)存，沒(méi)有數(shù)據(jù)時(shí)，則讀取被阻塞。

接下來(lái)就是不斷重復(fù)1，2，3的過(guò)程。 問(wèn)題 是，Socket Client 端的JVM在默認(rèn)模式下讀取Kennel Buffer是沒(méi)有考慮本機(jī)內(nèi)存大小的，有多少讀多少。如果數(shù)據(jù)太大，就會(huì)造成FULL GC，緊接著內(nèi)存溢出。

參考 JDBC API docs，默認(rèn)模式 Java demo 代碼如下

1.2.2 方式2：游標(biāo)查詢

為了解決方式1爆內(nèi)存的問(wèn)題，JDBC提供了一個(gè)游標(biāo)參數(shù)，在建立jdbc連接時(shí)加上useCursorFetch=true。設(shè)置游標(biāo)后，JDBC 每次會(huì)告訴Server端每次抽取的數(shù)據(jù)量，避免爆內(nèi)存。通信過(guò)程如下圖所示。

​ 方式2游標(biāo)查詢雖然解決了內(nèi)存溢出的問(wèn)題，方式2極大的依賴網(wǎng)絡(luò)質(zhì)量。當(dāng)網(wǎng)絡(luò)時(shí)延增大，假設(shè)每次通信增加10ms，10萬(wàn)次通信就會(huì)多出1000s。這里僅僅是每次發(fā)請(qǐng)求的RT，TCP每次發(fā)送報(bào)文，都要求反饋ACK保證數(shù)據(jù)可靠性。client每取100行（請(qǐng)求行數(shù)可配置），就會(huì)有多次通信，進(jìn)一步放大時(shí)延增加導(dǎo)致的效率問(wèn)題。此外，游標(biāo)查詢下，Mysql無(wú)法預(yù)知查詢的結(jié)束時(shí)延，為了應(yīng)對(duì)自身的DML操作會(huì)在本地建立一個(gè)臨時(shí)空間存放要抽取的數(shù)據(jù)。因此，游標(biāo)查詢時(shí)會(huì)有以下幾個(gè)現(xiàn)象發(fā)生

a. IOPS飆升，Mysql將數(shù)據(jù)寫(xiě)入到臨時(shí)空間，數(shù)據(jù)傳輸時(shí)從臨時(shí)空間讀取數(shù)據(jù)，這都會(huì)引發(fā)大量IO操作。

b. 磁盤(pán)空間飆升，臨時(shí)空間生命周期存在于整個(gè)JDBC讀取階段，直到客戶端發(fā)起Result.close()時(shí)才會(huì)被Mysql回收。

c. CPU和內(nèi)存有一定比例上升。

有關(guān)游標(biāo)查詢的原理可參考博客MySQL JDBC StreamResult通信原理淺析以及JDBC源碼，本文不在贅述。

參考 JDBC API docs，游標(biāo)模式 Java demo 代碼如下

1.2.3 方式3： Stream讀取數(shù)據(jù)

方式1會(huì)導(dǎo)致JVM內(nèi)存溢出，方式2雖然不會(huì)FULL GC但是通信效率較低，而且也會(huì)導(dǎo)致Mysql服務(wù)端IOPS飆升，消耗磁盤(pán)空間等問(wèn)題。因此，我們介紹Stream讀取數(shù)據(jù) ，流式需要在讀取Result前設(shè)置

方式3在通信前不會(huì)做任何Server-Cient的交互操作，避免通信效率低下。服務(wù)端準(zhǔn)備好數(shù)據(jù)寫(xiě)入Server的Kennel Buffer中，這些數(shù)據(jù)通過(guò)TCP鏈路傳輸?shù)紺lient的Kennel Buffer中，緊接著client端inputStream.read()方法被喚醒去讀取數(shù)據(jù)，與方式1不同，client每次只會(huì)讀取一個(gè)package大小的數(shù)據(jù)，如果一個(gè)package不滿一行則會(huì)再讀取一個(gè)package。當(dāng)client消費(fèi)數(shù)據(jù)的速度不及數(shù)據(jù)傳輸速率時(shí)，client端kennel區(qū)的數(shù)據(jù)就會(huì)被堆滿，緊接著Server端的kennel數(shù)據(jù)也會(huì)堆滿進(jìn)而阻塞了OuputStream。這樣，JDBC在Stream模式下就像一個(gè)水管連接兩個(gè)蓄水池，Client和Server達(dá)到一個(gè)平衡。

​對(duì)于JDBC客戶端，由于每次都是從kennel讀取數(shù)據(jù)，效率會(huì)比方式2高很多，每次讀取一小部分?jǐn)?shù)據(jù)也不會(huì)導(dǎo)致JVM內(nèi)存溢出。對(duì)于服務(wù)端，Mysql每次都是往kennel寫(xiě)數(shù)據(jù)，無(wú)需建立臨時(shí)空間，不涉及IO讀取，服務(wù)端壓力也變小了。當(dāng)然，方式3也有自己的問(wèn)題，例如Stream流式時(shí)無(wú)法cancel，cancel不阻塞等等。

參考 JDBC API docs，網(wǎng)上很多教程需要設(shè)置useCursorFetch=trueResultSet.FETCH_REVERSE等，其實(shí)小編研究完JDBC驅(qū)動(dòng)源碼后發(fā)現(xiàn)，只需要設(shè)fetchSize=Integer. MIN_VALUE，其他配置均和默認(rèn)配置保持一致即可。游標(biāo)模式 Java demo 代碼如下

1.3 云數(shù)據(jù)遷移服務(wù)在三種模式下的調(diào)優(yōu)

云數(shù)據(jù)遷移服務(wù)（Cloud Data Migration, CDM）是華為云上一個(gè)遷移工具，詳見(jiàn) CDM官網(wǎng) ,小編則通過(guò)CDM介紹如何切換三種模式抽取數(shù)據(jù)。CDM默認(rèn)使用的是方式3，流式抽取數(shù)據(jù)，如果需要切換方式1，方式2需額外配置。

1.3.1 配置方式1：默認(rèn)讀取

新建Mysql連接器，建立方法詳見(jiàn)官網(wǎng)，在高級(jí)屬性中增加useCursorFetch=false和adopt.stream=false

1.3.2 配置方式2：游標(biāo)查詢

編輯Mysql連接器，在高級(jí)屬性中增加useCursorFetch=true和adopt.stream=false。游標(biāo)查詢的大小可通過(guò)界面上的Fetch Size調(diào)整，默認(rèn)1000。

1.3.3 配置方式3：流式

CDM默認(rèn)走的流式，無(wú)需額外配置。注意Stream模式下，界面上的 Fetch Size 是不起作用的，原因參考上一節(jié)。

1.3.4 性能對(duì)比

新建Mysql2Hive的CDM遷移作業(yè)，源表101個(gè)字段，100萬(wàn)行數(shù)據(jù)，配置如下

方式1：寫(xiě)入100萬(wàn)行數(shù)據(jù)耗時(shí)1m22s

方式2：同樣寫(xiě)入100萬(wàn)行，調(diào)整fetchSzie分別為1，10，100，100，最低耗時(shí)2m1s

方式3：同樣寫(xiě)入100萬(wàn)行，耗時(shí)1m5s

小編還測(cè)試了100萬(wàn)的小表，明顯方式1和方式3的速率要遠(yuǎn)遠(yuǎn)高于方式2，另外小編還測(cè)試了1000萬(wàn)的大表，方式1爆內(nèi)存，方式2正常遷移但耗時(shí)20分鐘以上，而方式3仍然可以在15分鐘內(nèi)跑完。

到此這篇關(guān)于從云數(shù)據(jù)遷移服務(wù)看MySQL大表抽取模式的原理解析的文章就介紹到這了,更多相關(guān)MySQL大表抽取內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！