当前位置：首页 > news >正文

Percona pt-archiver 出现长事务

news 2025/7/26 10:41:41

表结构

CREATE TABLE `operation_record` (`record_id` bigint NOT NULL COMMENT '主键',`enterprise_id` bigint DEFAULT NULL COMMENT '企业ID',`account_id` bigint DEFAULT NULL COMMENT '操作人ID',`user_name` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '操作人名称',`real_name` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '操作人名称',`handle_status` tinyint DEFAULT '1' COMMENT '处理状态. 1:成功 2:失败',`handle_status_desc` char(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '成功' COMMENT '处理状态. 1:成功 2:失败',`header` varchar(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '操作请求头',`in_body` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT '操作请求内容',`out_body` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT '操作响应内容',`business_no` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '业务单号',`business_id` bigint DEFAULT NULL COMMENT '业务ID',`message_id` varchar(40) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'MQ消息ID',`service_name` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '服务名称',`module_name` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '模块名称',`method` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '接口名称',`remark` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '功能描述',`desc` varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '描述信息',`source_type` int unsigned NOT NULL COMMENT '来源方式类型',`source_name` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '来源方式名称',`user_agent` varchar(1024) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'User-Agent',`domain` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '域名',`source_ip` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '来源IP',`handle_ip` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '事件处理节点的IP',`trace_id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '观测云traceId',`create_time` datetime(3) NOT NULL COMMENT '创建时间',`update_time` datetime(3) NOT NULL COMMENT '修改时间',PRIMARY KEY (`record_id`),KEY `idx_business_no` (`business_no`),KEY `idx_business_id` (`business_id`),KEY `idx_create_time` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT='操作记录表';

主键不是自增的

归档命令

/development/percona-toolkit-2.2.17/bin/pt-archiver \
--source h=rm-xxx.mysql.rds.aliyuncs.com,P=3306,u=db_user,p='123456',D=db0,t=operation_record \
--where "create_time<=''2025-04-24 08:00:00'" \
--charset=utf8 \
--no-version-check \
--no-check-charset \
--progress 1000 \
--limit=100 \
--txn-size 500 \
--statistics \
--no-safe-auto-increment \
--bulk-delete \
--purge

满足条件的数据有 482 条

执行归档命令,被’卡住’, 执行过程出现了长事务, 捕获到的SQL如下

SELECT /*!40001 SQL_NO_CACHE */ `record_id`,`enterprise_id`,`account_id`,`user_name`,`real_name`,`handle_status`,`handle_status_desc`,`header`,`in_body`,`out_body`,`business_no`,`business_id`,`message_id`,`service_name`,`module_name`,`method`,`remark`,`desc`,`source_type`,`source_name`,`user_agent`,`domain`,`source_ip`,`handle_ip`,`trace_id`,`create_time`,`update_time` FROM `db0`.`operation_record` FORCE INDEX(`PRIMARY`) WHERE (create_time<='2025-04-24 08:00:00') AND ((`record_id` >= '614730460908331008')) ORDER BY `record_id` LIMIT 100

EXPLAIN 查看执行计划

在这里插入图片描述
rows = 5759686. 没有使用 idx_create_time 索引.

修改归档命令,手动指定索引 i=idx_create_time

/development/percona-toolkit-2.2.17/bin/pt-archiver \
--source h=rm-xxx.mysql.rds.aliyuncs.com,P=3306,u=db_user,p='123456',D=db0,t=operation_record,i=idx_create_time \
--where "create_time<=''2025-04-24 08:00:00'" \
--charset=utf8 \
--no-version-check \
--no-check-charset \
--progress 1000 \
--limit=100 \
--txn-size 500 \
--statistics \
--no-safe-auto-increment \
--bulk-delete \
--purge

调整之后, 执行的SQL语句举例如下

SELECT /*!40001 SQL_NO_CACHE */ `record_id`,`enterprise_id`,`account_id`,`user_name`,`real_name`,`handle_status`,`handle_status_desc`,`header`,`in_body`,`out_body`,`business_no`,`business_id`,`message_id`,`service_name`,`module_name`,`method`,`remark`,`desc`,`source_type`,`source_name`,`user_agent`,`domain`,`source_ip`,`handle_ip`,`trace_id`,`create_time`,`update_time` FROM `db0`.`operation_record` FORCE INDEX(`idx_create_time`) WHERE (create_time<='2025-04-31') ORDER BY `create_time` LIMIT 100"