MySQL主从延迟问题处理

IT那活儿 2024-09-04

104

点击上方“IT那活儿”公众号--专注于企业全栈运维技术分享，不管IT什么活儿，干就完了！！！

问题概况

巡检发现雅典娜大屏xxx.xxx.110.72/78MySQL存在主从延迟的情况。经排查发现是由于从库执行主从复制delete语句阻塞导致，进一步了解业数据库使用情况，业务数据用于大屏展示，数据保留近7天，表较大（两千万级别的表），且表未创建索引。

根据业务使用场景分析，并给出创建索引，分批删除数据，根据创建时间设计分区表优化建议，考虑到改成分区表业务改动较大，使用创建索引，分批删除优化后，延迟问题解决。

处理过程

2.1 问题定位

检查发现MySQL主从延迟较大：

查看MySQL进程，从库主从复制的delete语句卡住：

根据进程id查看完整sql：

delete from app_big_screen_kpi_data_day where create_time < '20240506'

查看app_big_screen_kpi_data_day表大小，表较大，有一百四十多万行：

EXPLAIN 语句发现sql未引用索引：

查看app_big_screen_kpi_data_day表结构，app_big_screen_kpi_data_day表未创建索引：

2.2 问题处理

1）创建索引

在执行delete语句时，在未引用索引的情况下，MySQL会进行全表扫描，导致删除操作的性能较差，尤其是在大表上执行删除操作时会消耗大量的时间和资源。

为了提高delete语句的执行效率，针对”delete from app_big_screen_kpi_data_day where create_time < '20240506'；”删除语句，在app_big_screen_kpi_data_day表加上索引，创建sql为CREATE INDEX idx_create_time ON app_big_screen_kpi_data_day(create_time);

2）分批删除

删除走索引，效率提升明显，内容如下：

DELIMITER $

-- PROCEDURE 如存在先删除
DROP PROCEDURE IF EXISTS bigscreen.delete_large_data $
-- 分批删除
CREATE DEFINER=`bigscreen`@`%` PROCEDURE `bigscreen`.`delete_large_data`(in `p_table_conditions` varchar(255))
BEGIN
    DECLARE limit_value INT;
    SET limit_value = 5000; -- 每次删除数据条数 
    
    SET @stmt_count = NULL; 
    SET @stmt = NULL; 
    SET @stmt_count = CONCAT('SELECT count(*) INTO @row_count FROM ', p_table_conditions);
    SET @stmt = CONCAT('DELETE FROM ', p_table_conditions, ' LIMIT ', limit_value);
    
    PREPARE s_count FROM @stmt_count;
    PREPARE s_del FROM @stmt;
    EXECUTE s_count;
   
   select @row_count;
    
    WHILE @row_count > 0 DO
      EXECUTE s_del;
        COMMIT; -- 提交
        SET @row_count = @row_count - limit_value; -- 更新数据总条数
    END WHILE;
    
    
    DEALLOCATE PREPARE s_count; -- 释放游标
    DEALLOCATE PREPARE s_del; -- 释放游标
END $

DELIMITER ;

-- app_big_screen_kpi_data_day 清理超过7天数据
call bigscreen.delete_large_data('screendata.app_big_screen_kpi_data_day where create_time < STR_TO_DATE("20240603", "%Y%m%d")');

-- sys_lx_msg_result 清理超过30天数据
call bigscreen.delete_large_data('screendata.sys_lx_msg_result where stat_date < date_format(DATE_ADD( NOW(), INTERVAL -30 DAY ), "%Y%m%d")');

3）验证

优化整改后，主从同步延迟问题已解决：

END

本文作者：符海(上海新炬中北团队）

本文来源：“IT那活儿”公众号

文章转载自IT那活儿，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。