一、MySQL數(shù)據(jù)庫(kù)主從同步延遲產(chǎn)生的原因
MySQL的主從復(fù)制都是單線程的操作,主庫(kù)對(duì)所有DDL和DML產(chǎn)生的日志寫進(jìn)binlog,由于binlog是順序?qū)懀孕屎芨摺?br />
Slave的SQL Thread線程將主庫(kù)的DDL和DML操作事件在slave中重放。DML和DDL的IO操作是隨即的,不是順序的,成本高很多。另一方面,由于SQL Thread也是單線程的,當(dāng)主庫(kù)的并發(fā)較高時(shí),產(chǎn)生的DML數(shù)量超過(guò)slave的SQL Thread所能處理的速度,或者當(dāng)slave中有大型query語(yǔ)句產(chǎn)生了鎖等待那么延時(shí)就產(chǎn)生了。
常見原因:Master負(fù)載過(guò)高、Slave負(fù)載過(guò)高、網(wǎng)絡(luò)延遲、機(jī)器性能太低、MySQL配置不合理。
二、關(guān)于DDL和DML
SQL語(yǔ)言共分為以下幾大類:查詢語(yǔ)言DQL,控制語(yǔ)言DCL,操縱語(yǔ)言DML,定義語(yǔ)言DDL。事務(wù)控制TCL
DQL(Data QUERY Languages)語(yǔ)句:即數(shù)據(jù)庫(kù)定義語(yǔ)句,用來(lái)查詢SELECT子句,F(xiàn)ROM子句,WHERE子句組成的查詢塊,比如:select–from–where–grouop by–having–order by–limit
DDL(Data Definition Languages)語(yǔ)句:即數(shù)據(jù)庫(kù)定義語(yǔ)句,用來(lái)創(chuàng)建數(shù)據(jù)庫(kù)中的表、索引、視圖、存儲(chǔ)過(guò)程、觸發(fā)器等,常用的語(yǔ)句關(guān)鍵字有:CREATE,ALTER,DROP,TRUNCATE,COMMENT,RENAME。增刪改表的結(jié)構(gòu)
DML(Data Manipulation Language)語(yǔ)句:即數(shù)據(jù)操縱語(yǔ)句,用來(lái)查詢、添加、更新、刪除等,常用的語(yǔ)句關(guān)鍵字有:SELECT,INSERT,UPDATE,DELETE,MERGE,CALL,EXPLAIN PLAN,LOCK TABLE,包括通用性的增刪改查。增刪改表的數(shù)據(jù)
DCL(Data Control Language)語(yǔ)句:即數(shù)據(jù)控制語(yǔ)句,用于授權(quán)/撤銷數(shù)據(jù)庫(kù)及其字段的權(quán)限(DCL is short name of Data Control Language which includes commands such as GRANT and mostly concerned with rights, permissions and other controls of the database system.)。常用的語(yǔ)句關(guān)鍵字有:GRANT,REVOKE。
TCL(Transaction Control Language)語(yǔ)句:事務(wù)控制語(yǔ)句,用于控制事務(wù),常用的語(yǔ)句關(guān)鍵字有:COMMIT,ROLLBACK,SAVEPOINT,SET TRANSACTION。
三、主從延時(shí)排查方法
通過(guò)監(jiān)控 show slave status 命令輸出的Seconds_Behind_Master參數(shù)的值來(lái)判斷:
NULL,表示io_thread或是sql_thread有任何一個(gè)發(fā)生故障;
0,該值為零,表示主從復(fù)制良好;
正值,表示主從已經(jīng)出現(xiàn)延時(shí),數(shù)字越大表示從庫(kù)延遲越嚴(yán)重
四、解決方案
解決數(shù)據(jù)丟失的問(wèn)題:
半同步復(fù)制
從MySQL5.5開始,MySQL已經(jīng)支持半同步復(fù)制了,半同步復(fù)制介于異步復(fù)制和同步復(fù)制之間,主庫(kù)在執(zhí)行完事務(wù)后不立刻返回結(jié)果給客戶端,需要等待至少一個(gè)從庫(kù)接收到并寫到relay log中才返回結(jié)果給客戶端。相對(duì)于異步復(fù)制,半同步復(fù)制提高了數(shù)據(jù)的安全性,同時(shí)它也造成了一個(gè)TCP/IP往返耗時(shí)的延遲。
主庫(kù)配置sync_binlog=1,innodb_flush_log_at_trx_commit=1 sync_binlog的默認(rèn)值是0,MySQL不會(huì)將binlog同步到磁盤,其值表示每寫多少binlog同步一次磁盤。
innodb_flush_log_at_trx_commit為1表示每一次事務(wù)提交或事務(wù)外的指令都需要把日志flush到磁盤。
注意:將以上兩個(gè)值同時(shí)設(shè)置為1時(shí),寫入性能會(huì)受到一定限制,只有對(duì)數(shù)據(jù)安全性要求很高的場(chǎng)景才建議使用,比如涉及到錢的訂單支付業(yè)務(wù),而且系統(tǒng)I/O能力必須可以支撐!
4.1 解決從庫(kù)復(fù)制延遲的問(wèn)題:
架構(gòu)方面
業(yè)務(wù)的持久化層的實(shí)現(xiàn)采用分庫(kù)架構(gòu),mysql服務(wù)可平行擴(kuò)展,分散壓力。
單個(gè)庫(kù)讀寫分離,一主多從,主寫從讀,分散壓力。這樣從庫(kù)壓力比主庫(kù)高,保護(hù)主庫(kù)。
服務(wù)的基礎(chǔ)架構(gòu)在業(yè)務(wù)和mysql之間加入memcache或者redis的cache層。降低mysql的讀壓力。
不同業(yè)務(wù)的mysql物理上放在不同機(jī)器,分散壓力。
使用比主庫(kù)更好的硬件設(shè)備作為slave,mysql壓力小,延遲自然會(huì)變小。
硬件方面
采用好服務(wù)器,比如4u比2u性能明顯好,2u比1u性能明顯好。
存儲(chǔ)用ssd或者盤陣或者san,提升隨機(jī)寫的性能。
主從間保證處在同一個(gè)交換機(jī)下面,并且是萬(wàn)兆環(huán)境。
總結(jié),硬件強(qiáng)勁,延遲自然會(huì)變小。一句話,縮小延遲的解決方案就是花錢和花時(shí)間。
mysql主從同步加速
sync_binlog在slave端設(shè)置為0
–logs-slave-updates 從服務(wù)器從主服務(wù)器接收到的更新不記入它的二進(jìn)制日志。
直接禁用slave端的binlog
.slave端,如果使用的存儲(chǔ)引擎是innodb,innodb_flush_log_at_trx_commit =2
從文件系統(tǒng)本身屬性角度優(yōu)化
master端修改linux、Unix文件系統(tǒng)中文件的etime屬性, 由于每當(dāng)讀文件時(shí)OS都會(huì)將讀取操作發(fā)生的時(shí)間回寫到磁盤上,對(duì)于讀操作頻繁的數(shù)據(jù)庫(kù)文件來(lái)說(shuō)這是沒(méi)必要的,只會(huì)增加磁盤系統(tǒng)的負(fù)擔(dān)影響I/O性能。可以通過(guò)設(shè)置文件系統(tǒng)的mount屬性,組織操作系統(tǒng)寫atime信息,在linux上的操作為:打開/etc/fstab,加上noatime參數(shù)/dev/sdb1 /data reiserfs noatime 1 2然后重新mount文件系統(tǒng)#mount -oremount /data
同步參數(shù)調(diào)整主庫(kù)是寫,對(duì)數(shù)據(jù)安全性較高,比如sync_binlog=1,
innodb_flush_log_at_trx_commit = 1 之類的設(shè)置是需要的而slave則不需要這么高的數(shù)據(jù)安全,完全可以講sync_binlog設(shè)置為0或者關(guān)閉binlog,innodb_flushlog也可以設(shè)置為0來(lái)提高sql的執(zhí)行效率
4.2 MySql數(shù)據(jù)庫(kù)從庫(kù)同步其他問(wèn)題及解決方案
mysql主從復(fù)制存在的問(wèn)題:
主庫(kù)宕機(jī)后,數(shù)據(jù)可能丟失
從庫(kù)只有一個(gè)sql Thread,主庫(kù)寫壓力大,復(fù)制很可能延時(shí)
解決方法:
半同步復(fù)制—解決數(shù)據(jù)丟失的問(wèn)題
并行復(fù)制----解決從庫(kù)復(fù)制延遲的問(wèn)題
半同步復(fù)制mysql semi-sync(半同步復(fù)制)半同步復(fù)制:
5.5集成到mysql,以插件的形式存在,需要單獨(dú)安裝
確保事務(wù)提交后binlog至少傳輸?shù)揭粋€(gè)從庫(kù)
不保證從庫(kù)應(yīng)用完這個(gè)事務(wù)的binlog
性能有一定的降低,響應(yīng)時(shí)間會(huì)更長(zhǎng)
網(wǎng)絡(luò)異?;驈膸?kù)宕機(jī),卡主主庫(kù),直到超時(shí)或從庫(kù)恢復(fù)
主從復(fù)制–異步復(fù)制原理、半同步復(fù)制和并行復(fù)制原理比較
審核編輯:黃飛
-
服務(wù)器
+關(guān)注
關(guān)注
12文章
8711瀏覽量
84583 -
SSD
+關(guān)注
關(guān)注
20文章
2791瀏覽量
116672 -
數(shù)據(jù)庫(kù)
+關(guān)注
關(guān)注
7文章
3714瀏覽量
64033 -
Linu
+關(guān)注
關(guān)注
0文章
26瀏覽量
19794 -
MySQL
+關(guān)注
關(guān)注
1文章
789瀏覽量
26292
原文標(biāo)題:MySQL主從同步延遲原因與解決方案
文章出處:【微信號(hào):magedu-Linux,微信公眾號(hào):馬哥Linux運(yùn)維】歡迎添加關(guān)注!文章轉(zhuǎn)載請(qǐng)注明出處。
發(fā)布評(píng)論請(qǐng)先 登錄
相關(guān)推薦
評(píng)論