mysql清理过期binlog堵塞数据库

背景

线上有一套数据库业务迁移走一段时间，过了一周业务又接入进来，运行几分钟后突然出现数据库10秒左右无法写入，上去排查发现此时出现了几百G binlog的过期删除

线上环境

mysql_version：oracle mysql-5.7.17
expire_logs_days = 5
max_binlog_size = 1G

疑问

1.binlog在什么时候会进行过期清理清理
2.为什么删除大量binlog期间会出现夯死数据库
3.如何避免binlog突然大量过期，在清理时影响线上业务

疑问1分析(binlog在什么时候会进行过期清理清理)

1.重启数据库实例
2.flush binary logs或者flush logs
3.组提交过程中检查binlog文件大小是否 >= 设置的max_binlog_size

疑问2分析(为什么binlog过期期间会夯死数据库)

以下为简化版代码：
MYSQL_BIN_LOG::rotate_and_purge
    mysql_mutex_lock(&LOCK_log);     /*持锁期间不可写入*/
    error= rotate(force_rotate, &check_purge);
    mysql_mutex_unlock(&LOCK_log);
    purge();        /*如果需要则执行purge*/
        if (expire_logs_days)
            time_t purge_time= my_time(0) - expire_logs_days*24*60*60;
            if (purge_time >= 0)
                purge_logs_before_date(purge_time, true);
                   mysql_mutex_lock(&LOCK_index);
                 while (strcmp(log_file_name, log_info.log_file_name))
                     if (stat_area.st_mtime < purge_time)    /*stat_area.st_mtime为文件mtime*/
                            no_of_log_files_purged++     ／*获取需要purged的所有log*／
                            purge_logs
                                error=remove_logs_from_index(&log_info, need_update_threads)      /*先将要删除的binlog从binlogindex中删除*/
                                global_sid_lock->wrlock();  /*持锁期间不可写入*/
                                error= init_gtid_sets(xxxx     /*收集gtid，更新gtid_purged*/    
                                global_sid_lock->unlock();
                                error_index= purge_index_entry(thd, decrease_log_space, false/*need_lock_index=false*/))
                                    for (;;)
                                        if (!mysql_file_delete(key_file_binlog, log_info.log_file_name, MYF(0)))       /*删除binlogfile，如果积累很多binlog很容会占用大量的磁盘io，虽然此时可以写入但是也会影响业务*/
                    mysql_mutex_unlock(&LOCK_index);
概括：
1）在日志切换期间需要获取全局mutex，此时数据库无法写入
2）收集所有要清理binlog文件中的gtid，并维护到gtid_purged，期间无法写入
3）删除binlog文件操作涉及到剧烈io波动，如果文件数很多，需要花费较长的时间，此时会影响业务的响应时间

疑问3分析(如何避免binlog突然大量过期，在清理时影响线上业务)

思路：从疑问1了解binlog过期删除必须满足这三个触发点的任意一个，否则不会进行过期清理，MySQL这种实现方式有待于优化，对于目前这种情况可以通过以下2种方式实现：
1.DBA手动检查过期binlog，如果过期就删除(flush/purge)
2.更改mysql代码，增加其定时器实现检查binlog expire逻辑及使用
if (DBUG_EVALUATE_IF("force_rotate", 1, 0) || (do_rotate && thd->commit_error == THD::CE_NONE))
{
  DEBUG_SYNC(thd, "ready_to_do_rotation");
  bool check_purge= false;
  mysql_mutex_lock(&LOCK_log);
  int error= rotate(false, &check_purge);
  mysql_mutex_unlock(&LOCK_log);
  if (error)
    thd->commit_error= THD::CE_COMMIT_ERROR;
  else if (check_purge)
    purge();
} else{
  /*add gaochao all else,only check purge not rotate; */
  if (((my_time(0) - gaochao_expire_binlog_reset_check_time) > opt_nucc_binlog_expire_check_interval) && (opt_nucc_binlog_expire_check_interval != 0)){
    purge();
    gaochao_expire_binlog_reset_check_time= my_time(0);
  }
}

# nucc_binlog_expire_check_interval为0表示不启用该功能，否则为间隔检查时间
mysql> show variables like '%nucc%';
+-----------------------------------+-------+
| Variable_name                     | Value |
+-----------------------------------+-------+
| nucc_binlog_expire_check_interval | 0     |
+-----------------------------------+-------+
mysql> set global nucc_binlog_expire_check_interval=600;
Query OK, 0 rows affected (0.00 sec)

mysql> show global variables like '%nucc%';
+-----------------------------------+-------+
| Variable_name                     | Value |
+-----------------------------------+-------+
| nucc_binlog_expire_check_interval | 600   | 
+-----------------------------------+-------+
1 row in set (0.01 sec)

mysql>

最后编辑于：2020.07.08 17:51:05