主从数据5点10分钟左右延迟问题分析
1. 问题原因分析:
登录数据库服务器查看主从数据确实存在延迟
登录数据库show processlist查看
可以看出是从内部的一个ip上用dev_read 用户连接到备库上执行的 查询导致数据库备份拷贝完数据文件后FLUSH NO_WRITE_TO_BINLOG TABLES加锁处等待状态(waiting for table flush)
而由于上述慢sql查询,导致flush table一直无法关闭该表而一直处于等待状态
(FLUSH NO_WRITE_TO_BINLOG TABLES 关闭所有打开的表,强制关闭所有正在使用的表)
数据备份日志如下:
210123 04:32:04 >> log scanned up to (34781249431476)
210123 04:32:05 [01] ...done
210123 04:32:05 Executing FLUSH NO_WRITE_TO_BINLOG TABLES...
210123 04:32:05 >> log scanned up to (34781249448552)
210123 04:32:06 >> log scanned up to (34781249465337)
之后业务的所有DML操作都处于等待状态,进而导致数据库主从延迟。
2. 分析慢sql查询来源:
show processlist看到ip地址是数据查询平台的ip地址,而dev_read这个用户就是为这个平台开放的查询用户,所以可以确认这个语句是从这个平台发起的
从数据库查询平台查看日志,可以确认是开发人员发起的查询
问题解决:
第一时间获取到信息并进行保留(用户问题分析),然后立即通过kill点慢sql语句的线程,kill备份线程问题解决
主从开始同步
后续连带问题:
第二天检查备份发现备份失败:
210125 01:00:02 Connecting to MySQL server host: localhost, user: dbauser, password: set, port: 3312, socket: /tmp/mysql3312.sock
xtrabackup: Redo Log Archiving is not set up.
Starting to parse redo log at lsn = 34849857389211
Recovery parsing buffer extended to 4194304.
Recovery parsing buffer extended to 8388608.
Recovery parsing buffer extended to 16777216.
210125 01:00:16 >> log scanned up to (34850360108928)
xtrabackup: Generating a list of tablespaces
Directories to scan '.;undolog;.'
Scanning './'
Completed space ID check of 2 files.
Allocated tablespace ID 44413 for guiyu_notice/notice_info, old maximum was 0
210125 01:00:17 >> log scanned up to (34850361716914)
Undo tablespace number 1 was being truncated when mysqld quit.
Cannot recover a truncated undo tablespace in read-only mode
xtrabackup: error: xb_load_tablespaces() failed with error code 57
考虑到上述错误应该是和上面处理问题时kill掉备份的进程有关
解决:
试着重启数据库报错:
删除undo再次重启解决问题
关闭数据库,删除undo 然后启动数据库
备份恢复正常
预防:
建议开发人员执行sql之前要进行查看执行计划,对sql进行审核,以防止语句长时间运行
从数据库层面对数据库进行过载保护
通过pt-kill 或者参数max_execution_time 来kill长时间运行的查询语句
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。