DM同步binlog到下游tidb,发现insert和update很慢

为提高效率,请提供以下信息,问题描述清晰能够更快得到解决:

【TiDB 版本】
v4.0.6
【问题描述】
从上游MySQL同步下来的binlog,解析出来的insert和update这些DML操作,发现cost time很长,经常需要1s多,有时候会到3s以上。一般这种情况,是从哪里入手去排查问题呢?

[2021/03/08 09:20:42.031 +08:00] [WARN] [db.go:264] [“execute transaction”] [task=dm-mide
azy-prod] [unit=“binlog replication”] [query="[INSERT INTO midea_mss.midea_sd_message_ queue (id,create_date,create_user,is_locked,lock_date,lock_key,lock_user,md5,tenant_id,update_date,update_user,version,message_body,message_id,messa ge_key,type) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) UPDATE midea_mss.midea_sd_cha nge_application SET id = ?, create_date = ?, create_user = ?, is_locked = ?, lo ck_date = ?, lock_key = ?, lock_user = ?, md5 = ?, tenant_id = ?, update_date
= ?, update_user = ?, version = ?, actual_amount_total = ?, approve_date = ?, ap prove_status_code = ?, base_flag = ?, base_price = ?, base_price_spread_deal_price
= ?, building_id = ?, change_code = ?, code = ?, creater = ?, deal_price = ?,
deal_price_with_decoration = ?, decoration_amount = ?, decoration_merge_flag = ?, decoration_moneymanage = ?, decoration_not_tax = ?, decoration_note = ?, decoration _sta_price = ?, decoration_sta_unit = ?, decoration_standard_config_id = ?, `decorat
io…"] [argument="[[09d34c11e4914d97bfee97f7051781c3 2021-03-08 09:20:35.860000 SYS_USER
_SELECT a5a59a0d709c4a233af5da5fac8dda06 2021-03-08 09:20:3
5.860000 SYS_USER_SELECT 1 [123 34 112 117 98 108 105 115 104 95 116 105 109 101 34 58 34
50 48 50 49 45 48 51 45 48 56 32 48 57 58 50 48 58 51 53 34 44 34 102 114 111 109 95 115
121 115 116 101 109 34 58 34 77 83 83 34 44 34 112 117 98 108 105 115 104 95 100 97 116
97 34 58 91 123 34 111 114 100 101 114 67 111 110 116 114 97 99 116 84 114 97 110 115 97
99 116 105 111 110 34 58 123 34 104 111 117 115 101 70 108 111 111 114 65 114 101 97 85 1
10 105 116 80 114 105 99 34 58 49 50 53 54 48 46 53 50 44 34 104 111 117 115 101 73 100 3
4 58 34 48 99 56 101 57 51 54 48 45 50 97 97 56 45 52 98 100 56 45 56 97 55 57 45 49 97 9
7 101 97 53 53 49 50 100 100 56 34 44 34 100 101 99 111 114 97 116 105 111 110 65 109 111
117 110 116 34 58 48 46 48 44 34 100 101 99 111 114 97 116 105 111 110 86 97 116 67 111
100 101 34 58 34 88 53 34 44 34 104 111 117 115 101 80 114 105 9…"] [“cost time”=1.7184
72574s]

[2021/03/08 09:20:42.094 +08:00] [WARN] [db.go:264] [“execute transaction”] [task=dm-mide
azy-prod] [unit=“binlog replication”] [query="[UPDATE midea_mss.midea_sd_order_contrac t_transaction SET id = ?, create_date = ?, create_user = ?, is_locked = ?, lock _date = ?, lock_key = ?, lock_user = ?, md5 = ?, tenant_id = ?, update_date =
?, update_user = ?, version = ?, area_unit_code = ?, company_code = ?, currency_ code = ?, customer_situation = ?, deal_floor_area = ?, deal_floor_area_unit_pric =
?, deal_price = ?, deal_price_not_tax = ?, deal_price_tax = ?, deal_price_with_de coration = ?, deal_unit = ?, deal_usable_floor_area = ?, deal_usable_floor_area_uni t_pric = ?, decoration_amount = ?, decoration_deliver_date = ?, decoration_merge_fl ag = ?, decoration_moneymanage = ?, decoration_not_tax = ?, decoration_note = ?, decoration_sta_price = ?, decoration_sta_unit = ?, decoration_standard_config_id = ?
, decoration_tax = ?, decoration_unit = ?, decoration_vat_code = ?, depayment_meth od_id = ?, discount_mode = ?, employees_buy_flag = ?, full_name = ?, `house_floor_
ar…"] [argument="[[1d7fb516babb43bb8a6f7ec8bd9a2c61 2021-02-20 13:23:26.566000 1b815945
a5044512859836ac1f8ebb52 true 2021-03-08 09:20:36.478000 e6fcb3d6-f5df-4fb6-9534-ab252d8a
bd95 1b815945a5044512859836ac1f8ebb52 2ed7517b16774d80b1b711fef6364fde 2021-03-05 11:41:
07.407000 1b815945a5044512859836ac1f8ebb52 5 MTK 1313 CNY 95.38 12464 1188816 1090656.88
98159.12 0 12464 79.29 14993.27 0 0 0 0 0 0 X5 0 0 南昌保利美的云筑
-一期-9号楼-1单元-1503 12464 3c37acad-f530-419b-b4ca-e889801dd369 1188816 1090656.88 9815
9.12 14993.27 cae25ab80f2d42a7b1331b7fb8e4e177 cd1e12b0-f3ae-11e9-b100-d3fc9e521195 SD040
401 1 1289411 13518.67 X5 f7e8abc0-f7b6-11e9-a8c9-8f781b27e1b4 2022-10-30
SD100303 2 SD110101 0 全民营销-全民经纪
人 1d7fb516babb43bb8a6f7ec8bd9a2c61] [b2a072e4ec9841c4a96e75e13385244b 2021-03-08 09:20:3 6.981000 anonymousUser <nil> <nil> <nil> <nil> b95f46d611ab40aa5d38cc4535c15c76 <nil> 202 1-03-08 09:20:36.981000 anonymousUser 1 \ufffd…"] [“cost time”=2.974345337s]


若提问为性能优化、故障排查类问题,请下载脚本运行。终端输出的打印结果,请务必全选并复制粘贴上传。

dm work 日志看到 cost time 较大超过了 1s,其实是执行较慢。可以按照如下手册排查相关 写慢的原因

大体方向
1.网络延迟
2.TiDB 配置是否达到推荐配置
3.查看慢语句并进行分析
4.跟进慢语句查看 TiDB server 状态
5.根据慢语句查看 TiKV server 状态