【版本】DM v2.0.6,TiDB v5.2.3, 上游mysql 8.0.23
【问题】使用DM从上游MySQL 全量同步2张表,DUMP时发现卡在一个select上
SELECT MIN(session_id
),MAX(session_id
) FROM skyalarm
.oracle_active_sess
后续开始执行dump后发现使用上述SQL进行导出任务数据范围的分配,session_id字段属于主键列的第3个字段,没有该列的首列索引导致上述SQL执行很慢,同时导出任务以session_id作为条件导出,大量全表扫描导出速度很慢。导出SQL语句如下:
SELECT sample_time
,instance_number
,Instance_name
,session_id
,session_serial
,blocking_session
,session_type
,qc_session_id
,session_state
,sql_id
,event
,wait_class
,p1
,p2
,p3
,machine
,program
,module
,time_waited
,plsql_entry_object_id
,write_iops
,read_iops
,cpu_time
,write_iomb
,read_iomb
,net_io
,dbtime
,sql_plan_hash_value
,username
FROM skyalarm
.oracle_active_sess
WHERE (session_id
>= 262 AND session_id
< 271) ORDER BY sample_time
,Instance_name
,session_id
,session_serial
表结构如下:
| oracle_active_sess | CREATE TABLE oracle_active_sess
(
sample_time
datetime NOT NULL,
instance_number
int DEFAULT NULL,
Instance_name
varchar(16) NOT NULL,
session_id
bigint NOT NULL,
session_serial
bigint NOT NULL,
blocking_session
bigint DEFAULT NULL,
session_type
varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
qc_session_id
bigint DEFAULT NULL,
session_state
varchar(7) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
sql_id
varchar(13) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
event
varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
wait_class
varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
p1
bigint unsigned DEFAULT NULL,
p2
bigint unsigned DEFAULT NULL,
p3
bigint unsigned DEFAULT NULL,
machine
varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
program
varchar(48) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
module
varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
time_waited
bigint unsigned DEFAULT NULL,
plsql_entry_object_id
bigint DEFAULT NULL,
write_iops
bigint unsigned DEFAULT NULL,
read_iops
bigint unsigned DEFAULT NULL,
cpu_time
bigint unsigned DEFAULT NULL,
write_iomb
bigint unsigned DEFAULT NULL,
read_iomb
bigint unsigned DEFAULT NULL,
net_io
bigint unsigned DEFAULT NULL,
dbtime
bigint DEFAULT NULL,
sql_plan_hash_value
bigint DEFAULT NULL,
username
varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
PRIMARY KEY (sample_time
,Instance_name
,session_id
,session_serial
)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
DM在导出数据时任务分配逻辑是什么? 为什么会选择这样一个字段而不使用索引的首列?
将源端mysql数据truncate ,dm_meta相关任务表truncate,然后重新start-task执行任务,load完后开始sync是报错Message": “fail to restart streamer controller: start syncing binlog from remote streamer”,
“RawCause”: “invalid sequence 8 != 1”,
query-status的binlog位置
“syncerBinlog”: “(mysql2_bin.033376, 204642979)”,
“syncerBinlogGtid”: “a3c489d9-63ae-11eb-9c31-485702422bb3:1-13419301”,
binlog信息