SQLServer提高事務(wù)復(fù)制效率優(yōu)化教程

1、SQL Server提高事務(wù)復(fù)制效率優(yōu)化教程總體概述隨著公司業(yè)務(wù)的發(fā)展，數(shù)據(jù)量增長迅速，在解決Scale Out的同時(shí)，還要考慮到主從的復(fù)制延遲 問題，盡量降到1s以內(nèi)滿足線上業(yè)務(wù)，如果不調(diào)整，SQL Server默認(rèn)的配置可能平均要3s左 右。生產(chǎn)的復(fù)制架構(gòu)采用的是推送方式進(jìn)行事務(wù)復(fù)制，發(fā)布服務(wù)器下面有4個(gè)從節(jié)點(diǎn)，兩兩指向同 一虛擬IP，構(gòu)成負(fù)載均衡，服務(wù)于不同的線上業(yè)務(wù)。對于4個(gè)節(jié)點(diǎn)，發(fā)布庫和分法庫的壓力都很 大，訂閱庫每秒I/O能達(dá)到5M，高峰時(shí)能達(dá)到數(shù)十兆。研究并試驗(yàn)了一些時(shí)間，給出一些優(yōu)化建 議：硬件、數(shù)據(jù)庫設(shè)計(jì)：使用SSD磁盤，有錢的可以使用PCI-e卡，SSD做RAID10，所有

2、節(jié)點(diǎn)部署在內(nèi)網(wǎng)。更改事務(wù)隔離級別為READ_COMMITTED_SNAPSHOT。說實(shí)話，SQL Server默認(rèn)事務(wù) 隔離級別是讀已提交，但是沒有多版本的概念，需要設(shè)置為 READ_COMMITTED_SNAPSHOT才是我們理解上的讀已提交。如果不設(shè)置這個(gè)，照樣會 讀寫互相阻塞。在創(chuàng)建訂閱之前，先將目標(biāo)數(shù)據(jù)庫的恢復(fù)模式改成簡單，能夠減少日志ALTER DATABASE 數(shù)據(jù)庫名稱SET RECOVERY SIMPLE WITH NO_WAIT發(fā)布、訂閱設(shè)計(jì)：僅發(fā)布需要的數(shù)據(jù)，對于將日志寫入數(shù)據(jù)庫而且還要同步復(fù)制的，DBA可直接拒絕。表中存在大量非聚集索引，發(fā)布時(shí)可以不勾選復(fù)制非聚集索引選項(xiàng)

3、。經(jīng)過測試，雖然數(shù)據(jù)庫 引擎是在應(yīng)用完快照最后創(chuàng)建索引的，但創(chuàng)建時(shí)間很長，有時(shí)甚至?xí)鲥e(cuò)?？梢栽诤笃谑止?建立。我們采用導(dǎo)出腳本方式，然后只保留非聚集索引的腳本在訂閱庫中建立。如果資源允許，單獨(dú)建立一個(gè)分發(fā)服務(wù)器。發(fā)布和分發(fā)服務(wù)器在一個(gè)服務(wù)器上會增加性能損 耗，尤其是分發(fā)服務(wù)器下面有多個(gè)訂閱服務(wù)器時(shí)。分發(fā)庫默認(rèn)是distribution庫，會有專門的作業(yè)對該庫進(jìn)行清理，名字就叫“分發(fā)清除： distribution”。但這個(gè)進(jìn)程清除分發(fā)庫的效率太低了，會導(dǎo)致分發(fā)庫積累大量數(shù)據(jù)，需要修 改運(yùn)行間隔和每次清楚的數(shù)據(jù)量。詳見之前的隨筆：distribution庫過大的問題合理使用訂閱方式，如果選擇推

4、送訂閱，勢必加大分發(fā)服務(wù)器的壓力，但延遲低；如果選擇 請求訂閱，代理服務(wù)是在訂閱服務(wù)器上，減少了分發(fā)服務(wù)器壓力，但延遲會增加?？煺沾淼纳?。對于生成幾百GB數(shù)據(jù)庫的快照，采用SSD盤可能在10分鐘左右。但會 造成很高的性能損耗，要在業(yè)務(wù)非高峰期生成快照?？煺沾砦募惨旁赟SD盤上，如果 資源允許，單獨(dú)一個(gè)驅(qū)動盤也可以。如果已經(jīng)有備份了，那么可以通過備份直接來初始化訂閱，省去了生成快照和應(yīng)用快照的步 驟。新發(fā)布新的表對象，對于發(fā)布庫很大的情況下重新初始化是不實(shí)際的，可以增量發(fā)布，詳見 sqlserver同步后在不重新初始化快照的情況下新增表對于已發(fā)布的表有批處理更新，一種方法是將批量更新分

5、成若干小批量執(zhí)行，減小事務(wù)處理 的數(shù)據(jù)量；另一種方法是通過存儲過程執(zhí)行更改，并將存儲過程發(fā)布。推薦第一種將項(xiàng)目分布在多個(gè)發(fā)不上，這樣相當(dāng)于變向的實(shí)現(xiàn)了多線程并行發(fā)布，當(dāng)然也可以通過參數(shù) 實(shí)現(xiàn)并行，下面會講到參數(shù)優(yōu)化：降低復(fù)制代理的詳細(xì)級別，在初始測試、監(jiān)視或調(diào)試期間除外。減小分發(fā)代理或合并代理的 -HistoryVerboseLevel參數(shù)和-OutputVerboseLevel參數(shù)。這樣可以減少為跟蹤代理 歷史記錄和輸出而插入的新行數(shù)。相反，具有相同狀態(tài)的以前的歷史記錄消息將更新為新的 歷史記錄信息。提高測試、監(jiān)視和調(diào)試的詳細(xì)級別，這樣就可以獲得有關(guān)代理活動的盡可能 多的信息。在系統(tǒng)開銷不是

6、很大的情況下，建議使用默認(rèn)值1,本身這點(diǎn)對性能消耗可以忽 略。增大快照代理的BcpBatchSize參數(shù)，-BcpBatch參數(shù)表示一個(gè)批事務(wù)的行數(shù)。將快照代理的-MaxNetworkOptimization設(shè)置為1,減少將不必要的信息發(fā)送到訂閱服務(wù) 器上。使用快照代理、分發(fā)代理的-MaxBcpThreads參數(shù)（指定的線程數(shù)不應(yīng)超過計(jì)算機(jī)上的 處理器數(shù)）。此參數(shù)指定創(chuàng)建和應(yīng)用快照時(shí)可以并行執(zhí)行的大容量復(fù)制操作的數(shù)目。為分發(fā)代理使用 -Subscriptionstreams參數(shù)。-Subscriptionstreams參數(shù)可以顯著 提高聚合復(fù)制吞吐量。它使到一臺訂閱服務(wù)器的多個(gè)連接可以并行應(yīng)用

7、批量更改，同時(shí)在使 用單線程時(shí)保持多個(gè)事務(wù)特征的存在。如果有一個(gè)連接無法執(zhí)行或提交，則所有連接將中止 當(dāng)前批處理，而且代理將用單獨(dú)的流重試失敗的批處理。在重試階段完成之前，訂閱服務(wù)器 上會存在臨時(shí)事務(wù)不一致。失敗的批處理成功提交后，訂閱服務(wù)器將恢復(fù)到事務(wù)一致狀態(tài)。 官方文檔有這個(gè)參數(shù)，但增加這個(gè)參數(shù)報(bào)錯(cuò)。增大分發(fā)代理的-CommitBatchSize和CommitBatchThreshold參數(shù)的值。提交一組事 務(wù)的開銷是固定的；通過不經(jīng)常提交大量事務(wù)，就可以將開銷分散在大量數(shù)據(jù)上。但是，增 大此參數(shù)的優(yōu)勢因應(yīng)用更改的開銷由其他因素（例如包含日志的最大磁盤I/O）限制而降低。另外，需要考慮以下

8、權(quán)衡問題：任何導(dǎo)致分發(fā)代理重新開始的失敗必須回滾并重新應(yīng)用 大量事務(wù)。對于不可靠的網(wǎng)絡(luò)，越小的值導(dǎo)致失敗的幾率越小，如果導(dǎo)致失敗，將回滾并重 新應(yīng)用較少量事務(wù)。增大日志讀取器代理的-ReadBatchSize參數(shù)的值。-ReadBatchSize表示日志讀取器代 理和分發(fā)代理支持事務(wù)讀取和提交操作的批大小。批大小的默認(rèn)值為500個(gè)事務(wù)。日志讀 取器代理從日志中讀取特定數(shù)量的事務(wù)，而不管這些事務(wù)是否標(biāo)記為復(fù)制。將大量事務(wù)寫入 發(fā)布數(shù)據(jù)庫，而其中只有一小部分標(biāo)記為復(fù)制時(shí)，則應(yīng)使用-ReadBatchSize參數(shù)增加日志 讀取器代理的讀取批大小。此參數(shù)不適用于Oracle發(fā)布服務(wù)器。.為日志讀取器代

9、理使用-MaxCmdsInTran參數(shù)。-MaxCmdsInTran參數(shù)指定日志讀 取器向分發(fā)數(shù)據(jù)庫寫入命令時(shí)組成一個(gè)事務(wù)的語句的最大數(shù)量。使用此參數(shù)能夠使日志讀取 器代理和分發(fā)代理在訂閱服務(wù)器上應(yīng)用命令時(shí)將發(fā)布服務(wù)器上的大事務(wù)（由許多命令組成） 分成若干個(gè)較小的事務(wù)。指定此參數(shù)可以減少分發(fā)服務(wù)器的爭用問題并縮短發(fā)布服務(wù)器與訂 閱服務(wù)器之間的滯后時(shí)間。由于初始事務(wù)是以較小的單元應(yīng)用的，訂閱服務(wù)器可以在初始事 務(wù)結(jié)束之前訪問一個(gè)較大的邏輯發(fā)布服務(wù)器事務(wù)的行，因而會破壞事務(wù)的原子性。默認(rèn)值為 0，這將保持發(fā)布服務(wù)器的事務(wù)邊界。官方文檔有該參數(shù)，但實(shí)際設(shè)置里面沒有。.減小日志讀取器代理和分發(fā)代理的-

10、PollingInterval參數(shù)的值。-PollingInterval參數(shù)指 定為要復(fù)制的事務(wù)查詢已發(fā)布數(shù)據(jù)庫的事務(wù)日志的頻率。默認(rèn)值為5秒。如果減小此值，日 志的輪詢將更加頻繁，這會使事務(wù)從發(fā)布數(shù)據(jù)庫傳遞到分發(fā)數(shù)據(jù)庫的滯后時(shí)間較低。但是， 應(yīng)該對較低滯后時(shí)間的需要和因頻繁輪詢導(dǎo)致的服務(wù)器上增加的負(fù)荷之間進(jìn)行平衡。快照初始化優(yōu)化測試數(shù)據(jù)表量1500w+，使用初始化默認(rèn)的快照代理參數(shù)，復(fù)制的三個(gè)過程包括快照初始化，訂閱 初始化和數(shù)據(jù)修改復(fù)制，主要對快照代理、分發(fā)代理、日志讀取代理分別作了參數(shù)優(yōu)化，并給出優(yōu) 化前后的對照實(shí)驗(yàn)測試。初始化快照.在復(fù)制監(jiān)視器中找到快照代理位置，右鍵，選擇代理配置文件

11、：Ft&帝目美的1町功作業(yè)冒:丁狀態(tài)-kii上玄巖司.F、_H厲豎時(shí)目上一操作:從未啟剝）快瞪代理=1藝胸機(jī)津代珪打舊皿二1E:E:坨33:33:39傳遍L個(gè)半公，凄止1 . . .查看默認(rèn)配置:初始化快照時(shí)間:所有訂閱跟蹤令牌代理1警告1與此發(fā)布相關(guān)的代理和作業(yè)壓）：狀態(tài)作業(yè)上次啟動時(shí)間持續(xù)時(shí)間上一操作的：已完成快照代坦2016/10/11 20:22:0200:02:38100%已生成 正在運(yùn)行日志讀刖器代理2016/10/11 20:22:5500:03:06傳遞了 1個(gè)事優(yōu)化參數(shù)測試新建一個(gè)快照代理配置文件，命名為“快照代理優(yōu)化參數(shù)”查找這個(gè)代理配置文件的prouse msdbSELE

12、CT * FROM msdb.dbo .MSagent_profiles WHERE agent_type = 1或者EXEC sp_help_agent_pro 1I這里的agent_type注解如下代理的類型：1=快照代理2=日志讀取器代理3 =分發(fā)代理4 =合并代理9 =隊(duì)列讀取器代理. 修改配置文件，這里新增了 MaxBcpThreads，MaxNetworkOptimization參數(shù)，修改了 BcpBatchSize,HistoryVerboseLevel參數(shù)，對于性能提高最大的參數(shù)就是MaxBcpThreads，其他參數(shù)調(diào)整對性能調(diào)高不大，但MaxNetworkOptimizat

13、ion參數(shù)一 定要調(diào)整到1，可以減少傳輸?shù)接嗛喎?wù)器上不必要的操作。USE msdbEXEC sp_add_agent_parameter pro = 17,parameter_name = MaxBcpThreads, parameter_value = 4EXEC sp_add_agent_parameter pro = 17,parameter_name = MaxNetworkOptimization, parameter_value = 1EXEC sp_change_agent_parameter pro = 17,parameter_name = BcpBatchSize, pa

14、rameter_value = 200000EXEC sp_change_agent_parameter pro = 17,parameter_name = HistoryVerboseLevel, parameter_value = 1.調(diào)整后的參數(shù)：優(yōu)化后測試結(jié)果，時(shí)間大大減少。但是如果在有業(yè)務(wù)負(fù)載同時(shí)生成快照，那么資源消耗很 大，建議合理調(diào)整參數(shù)，不要設(shè)置太大并發(fā)，用默認(rèn)配置已經(jīng)有很高的I/O 了。際有訂刖制梟漕代理催告I與此是個(gè)相美的代理和作丑卻：1_上悔作LOOS.已生成i個(gè)項(xiàng)|作丑 快照代理|二次局可時(shí)間ZO1&/1O/1Z 14 U5|恃讀時(shí)可00：0D：E5I二次局可時(shí)問恃讀時(shí)

15、可I上博作快照代理LOOS.已生成i個(gè)項(xiàng).ZO1&/1O/1Z 14 05：53訂閱初始化優(yōu)化初始化訂閱主要是由分發(fā)代理分發(fā)和應(yīng)用快照代理之前生成的快照，所以優(yōu)化的主體是分發(fā)代理。1、初始化訂閱首先在本地創(chuàng)建一個(gè)訂閱，發(fā)布服務(wù)器、分發(fā)服務(wù)器和訂閱服務(wù)器都在同一臺服務(wù)器上，僅 為了測試生產(chǎn)環(huán)境不要這樣做觸擊成以版仃卜刊程性*建訕可。從岌布明務(wù)器“虹H HABSLCC1C02創(chuàng)建對發(fā)布的訂尋證下可訂禱抵*囂上創(chuàng)建；閩m mutsuKicaz*廠閱蝴露jplL”.-勺主互置片忘匪努羔卻里I劃：連續(xù)M行,代理程幃戶：兩15廿作吐呻1:扇努帳戶,乓分發(fā)版洛器的連垸摸也也程株戶MiT弟陽*器白1諄特 住干

16、晉呆壬七L刁好舊五田分發(fā)服務(wù)器參數(shù)配置ELECT * FROM m3db.Ibc.MSagent_piofiles WHERE aqent_type=3 SELECT * FROM MSagent_parametetb WHERE profile_id=4|rrrpruiilejdprofile_nameagent _typetypedescti ptiondef jnofile110 1V/indsws SyicIronizartiDn Manager profile 3CWindows同步駕坦器明月的生宣劃?！癚2默認(rèn)代理斷置又件3NULL135任細(xì)萬兄記錄代理陞置玄件3用于詳期歷史記錄日

17、志的代理配麥文件。C416用于OLEDB流式處理的分凌斷蓋文件3使用OLEDB流式處理丈處理UDB數(shù)據(jù)啟用的始發(fā)弋坦陞專文件。C514遇到數(shù)據(jù)一致怙錯(cuò)誤E寸繾續(xù)，3用于跳過數(shù)據(jù)一致性諸誤傕代理翻置文仁。它只能由SQLSsvef訂閱服務(wù)器使用。Cprofilejdpararneter_namevalue14-Eks-pDaftahSize2H74ZJ6472-Commit EotdiSizeICC34-Commit EatchTlTeshold1GCD44-HistoryVerbose Level154-KeepAlive bfessagelntaval30064-Login Timeout15

18、74-MaocBcpThrBads1S4-MiaKDeli/efi&dTransctbns94-Polling Irten/al5104-QueiyTimecut18W114-SkipErnora124-Transadions PerHistory10C初始化訂閱運(yùn)行時(shí)間際訖W.舌巾&操IFCD操怕貌|換作時(shí)i可已在 264E34 覲內(nèi)將快瞭仙 H unc l?HT-MJJ)SljGG1602_ErCP_REPL_TEST 3016 L 012191552 Un 子女件買中.2D 16/10/12 19:23:07需要注意幾點(diǎn):快照代理實(shí)際執(zhí)行的是BCP程序，生成快照文件:spidpn3gra

19、m_namecument_cmd1384Snapshot BCP thread #3 for publication repl jestselect from dbo. syncobj_k463244353731413D where I ID 1 盼142哥日 nd lID。+0963) order by ID /1482Snapshot BCP thread #1 for publication repl jestselect from dbo.syncobj_(k463244353731413D where (ID 4fl%S)and i：ID =：= S196461) order by

20、ID /1590Snapshot BCP thread #Dfor publication repl jestselect 4from dbo.syncObj_(k46S244S5S7314nD where iID 515461) and i：ID=：=煩網(wǎng)勘 order by ID /1687Snapshot BCP thread #2 for publication repl jestselect from dbo.syncobj_k43244353731413D where (ID is null) or (ID = 1864423 order by ID AS快照代理會在快照生成的初始

21、階段使用鎖，雖然時(shí)間很短，但是影響范圍較大。 在快照生成過程中，不能對表的結(jié)構(gòu)進(jìn)行更改。2.優(yōu)化參數(shù)測試新建一個(gè)分發(fā)代理配置文件，命名為“分發(fā)代理優(yōu)化參數(shù)” 查找這個(gè)代理配置文件的proSELECT * FROM msdb.dbo .MSagent_profiles WHERE agent_type =3或者EXEC sp_help_agent_pro 3修改配置文件EXEC sp_change_agent_parameter pro = 18,parameter_name = MaxBcpThreads, parameter_value = 4EXEC sp_change_agent_par

22、ameter pro = 18,parameter_name = BcpBatchSize, parameter_value = 100000EXEC sp_change_agent_parameter pro = 18,parameter_name = PollingInterval, parameter_value = 500000優(yōu)化后訂閱庫生成測試結(jié)果，從測試結(jié)果上看并沒有明顯的優(yōu)化。MaxBcpThreads并沒有 實(shí)現(xiàn)多線程的數(shù)據(jù)導(dǎo)入，后臺查詢也只有一個(gè)會話在執(zhí)行bulk in操作，BcpBatchSize對 參數(shù)影響較小，建議設(shè)置的大一些；另外兩個(gè)參數(shù)不影響訂閱數(shù)據(jù)庫的生成效率。

23、其實(shí)訂閱 庫生成是整個(gè)過程的性能瓶頸所在，如果能使用多線程還是不錯(cuò)的解決方法，希望看到博客 的同學(xué)有好方法提供一下。所詵會刊中質(zhì)握作H）：操作涓息|操1甘寸閂已在 264234 皇秒內(nèi)將快照從皿602_E7CF_FJEFL_TEST2D V5 子文牛夷中.2016/10/12 19:23:07修改數(shù)據(jù)同步過程優(yōu)化1、原理我說的數(shù)據(jù)修改同步過程指的是在快照生成完畢，分發(fā)代理將快照應(yīng)用于訂閱服務(wù)器完成訂 閱服務(wù)器初始化后，發(fā)布服務(wù)器后續(xù)的更改同步到訂閱服務(wù)器過程，這也就是我們常常關(guān)注的延遲。 此過程主要涉及兩個(gè)代理，分發(fā)代理和日志讀取代理，因?yàn)槲覀兪褂玫氖峭扑陀嗛?，所以這兩個(gè)代 理都工作在分發(fā)數(shù)據(jù)

24、庫上。首先，日志讀取代理讀取發(fā)布事務(wù)日志，并表示任何INSERT. UPDATE 以及DELETE語句，然后將這些事務(wù)定時(shí)將批量復(fù)制到分發(fā)服務(wù)器的分發(fā)數(shù)據(jù)庫中。分發(fā)庫中存 儲著事務(wù)和命令，每個(gè)事務(wù)以多條命令組成。分發(fā)代理順序分發(fā)分法庫中的事務(wù)，以邏輯命令（SQL 語句）方式在訂閱庫上執(zhí)行。流程圖見下：優(yōu)化這兩個(gè)代理主要考慮從兩方面考慮，一方面是縮短每個(gè)批次的間隔，一方面需要在小間隔 的情況下增加代理處理的性能，這兩個(gè)方面是相關(guān)聯(lián)的，所以要一起修改才能起作用。2.復(fù)制延遲測試查看日志讀取代理配置文件的默認(rèn)參數(shù)：SELECT * FROM rnscLb. dbQ.MSag?nt_FTcfiles

25、WHERE ag?nt_type 2SELECT x FROM MS a ge nt parame t er s WHERE prcfile id=2|二I結(jié)果消息profiejdpnjfilB_ndmeagent jypetypedescripliondef_pnafile12默認(rèn)代理甑置文件20NULL1Z 3詳細(xì)由撾記柔代哩酉置項(xiàng)件史用于詳細(xì)由史記錄目志的代理國置義件。0profiejdparameter_n;amevdue1 2-History 詭 rbose Level122-LDgnUmeQiJ：1532-LogScanThnBshdd42-Poling Interval552Qu

26、eryTimeout餉62-ReadBatchSze5W查看分發(fā)代理配置文件的默認(rèn)參數(shù):SELECT * FROM msdb.dbo .MSagent_profiles WHERE agent_type =3SELECT * FROM MSagent_parameters WHERE pro= 4SELECT * FROM mscib. dbo. NSagent profiles WHERE aget type=3SELECT * FROM M5snt parameters WHERE profile id=4rrr門結(jié)果 J消息pncfilejdprofilejiameagentjypety

27、pedescriptiondef_profie1Windows Synchronzaton Manager profile30Endows問去莒理囂所用白酒EW曳件。3IS夠發(fā)代埋伏化萎毓 概認(rèn)代埋時(shí)置義忤3;NULL;45詳紙歷史記錄齒坦甑蓋項(xiàng)件20用十詳沏歷史記我E志的代理況置艾.牛匚516用于OLEDB流式處坦的分發(fā)位置又件20使用OLE況洗寸處理為處哩心日數(shù)據(jù)啟用的專發(fā)代理斷走又件。6U遇到數(shù)據(jù)一致哇錯(cuò)誤時(shí)繼續(xù)。20用于跳過數(shù)提一斐性錯(cuò)誤的傳里呢置文件。它只能由SQLServer訂期膀器使隹。pnofilejdparamed:er_namevalue1:-;-BcpBiatchSzeCorrmitBiatGhSiEe-Commit BatchThrediold-HistoryVefbDseLevel-Keep All ve Message Intenrai-Login Timeojt-MaxBcpThneacS-yaocDeliveredTransactions-Polling Internal-QueryThiedut-SkjpEmors-Transactions PerHiistory21-17473647 1OT1300151D5 18W1M通過跟蹤令牌方式測試復(fù)制延遲時(shí)間代理參數(shù)優(yōu)化測試新建日志讀取代理參數(shù)配置文件，命名為“日志讀取代理參數(shù)優(yōu)化參