专栏首页mysql-dba备库大的select查询处于killed状态导致备库延迟
原创

备库大的select查询处于killed状态导致备库延迟

mysql版本Version:8.0.18

从监控上看备库延迟越来越大

主从延迟监控

show processlist 查看mysql线程

备库在应用主库同步的DDL操作语句处于Waiting for table metadata lock

还看都一个操作相关表的select count(*)操作 ,但这个查询语句处于killed状态

查看事务表select * from information_schema.innodb_trx发现这个select count(*)操作一直在running状态

再次kill这个这个查询,发现查询还是处于killed,事务表中也是一直running

ddl操作语句就是在等待这个查询释放元数据锁,查询一直处于killed状态,所以延迟越来越大

1.尝试停止复制 stop slave命令操作挂起停止不了

2.尝试kill掉复制线程执行的ddl操作,观察select count(*) 还是处于killed状态,启动复制ddl还是处于Waiting for table metadata lock

3.尝试正常停止mysql shutdown 停止操作也挂起

2021-06-22T21:08:39.942759+08:00 899941 [System] [MY-013172] [Server] Received SHUTDOWN from user root. Shutting down mysqld (Version: 8.0.18).
2021-06-22T21:08:40.593579+08:00 0 [Note] [MY-010067] [Server] Giving 56 client threads a chance to die gracefully
2021-06-22T21:08:40.594861+08:00 0 [Note] [MY-010117] [Server] Shutting down slave threads
2021-06-22T21:08:40.596045+08:00 0 [Note] [MY-010054] [Server] Event Scheduler: Killing the scheduler thread, thread id 8
2021-06-22T21:08:40.596081+08:00 0 [Note] [MY-010050] [Server] Event Scheduler: Waiting for the scheduler thread to reply
2021-06-22T21:08:40.596253+08:00 0 [Note] [MY-010048] [Server] Event Scheduler: Stopped
2021-06-22T21:08:42.600319+08:00 0 [Note] [MY-010118] [Server] Forcefully disconnecting 14 remaining clients
2021-06-22T21:08:42.600367+08:00 0 [Warning] [MY-010909] [Server] /usr/local/mysql/bin/mysqld: Forcing close of thread 899872  user: 'root'.
2021-06-22T21:08:42.600413+08:00 0 [Warning] [MY-010909] [Server] /usr/local/mysql/bin/mysqld: Forcing close of thread 899897  user: 'root'.
2021-06-22T21:08:42.600451+08:00 0 [Warning] [MY-010909] [Server] /usr/local/mysql/bin/mysqld: Forcing close of thread 900002  user: 'root'.
2021-06-22T21:08:42.600485+08:00 0 [Warning] [MY-010909] [Server] /usr/local/mysql/bin/mysqld: Forcing close of thread 899898  user: 'root'.
2021-06-22T21:08:42.600523+08:00 0 [Warning] [MY-010909] [Server] /usr/local/mysql/bin/mysqld: Forcing close of thread 899995  user: 'root'.
2021-06-22T21:08:42.600582+08:00 0 [Warning] [MY-010909] [Server] /usr/local/mysql/bin/mysqld: Forcing close of thread 899851  user: 'root'.
2021-06-22T21:08:42.600618+08:00 0 [Warning] [MY-010909] [Server] /usr/local/mysql/bin/mysqld: Forcing close of thread 899747  user: 'root'.
2021-06-22T21:08:42.600633+08:00 0 [Warning] [MY-010909] [Server] /usr/local/mysql/bin/mysqld: Forcing close of thread 899932  user: 'root'.
2021-06-22T21:08:42.600663+08:00 0 [Warning] [MY-010909] [Server] /usr/local/mysql/bin/mysqld: Forcing close of thread 899988  user: 'root'.
2021-06-22T21:08:42.600697+08:00 0 [Warning] [MY-010909] [Server] /usr/local/mysql/bin/mysqld: Forcing close of thread 899989  user: 'root'.
2021-06-22T21:08:42.600737+08:00 0 [Warning] [MY-010909] [Server] /usr/local/mysql/bin/mysqld: Forcing close of thread 899893  user: 'root'.
2021-06-22T21:08:42.600819+08:00 0 [Warning] [MY-010909] [Server] /usr/local/mysql/bin/mysqld: Forcing close of thread 182  user: 'root'.
2021-06-22T21:08:42.600910+08:00 0 [Warning] [MY-010909] [Server] /usr/local/mysql/bin/mysqld: Forcing close of thread 899871  user: 'root'.
2021-06-22T21:08:42.600955+08:00 0 [Warning] [MY-010909] [Server] /usr/local/mysql/bin/mysqld: Forcing close of thread 899999  user: 'root'.
2021-06-22T21:08:42.601007+08:00 0 [Note] [MY-010043] [Server] Event Scheduler: Purging the queue. 0 events

4.最后只能kill -9 mysqld的进程,然后再重新启动mysql

2021-06-22T21:11:33.091811+08:00 0 [Warning] [MY-011069] [Server] The syntax '--slave-rows-search-algorithms' is deprecated and will be removed in a future release.
2021-06-22T21:11:33.094381+08:00 0 [Warning] [MY-010101] [Server] Insecure configuration for --secure-file-priv: Location is accessible to all OS users. Consider choosing a different directory.
2021-06-22T21:11:33.094526+08:00 0 [Note] [MY-010949] [Server] Basedir set to /usr/local/mysql-8.0.18-linux-glibc2.12-x86_64/.
2021-06-22T21:11:33.094543+08:00 0 [System] [MY-010116] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.0.18) starting as process 30056
2021-06-22T21:11:33.097219+08:00 0 [Warning] [MY-013242] [Server] --character-set-server: 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
2021-06-22T21:11:33.110038+08:00 0 [Note] [MY-012366] [InnoDB] Using Linux native AIO
2021-06-22T21:11:33.110291+08:00 0 [Warning] [MY-013267] [InnoDB] The setting INNODB_UNDO_TABLESPACES is deprecated and is no longer used.  InnoDB always creates 2 undo tablespaces to start with. If you need more, please use CREATE UNDO TABLESPACE.
2021-06-22T21:11:33.110637+08:00 0 [Note] [MY-010747] [Server] Plugin 'FEDERATED' is disabled.
2021-06-22T21:11:33.117187+08:00 1 [Note] [MY-012944] [InnoDB] Uses event mutexes
2021-06-22T21:11:33.119270+08:00 1 [Note] [MY-013251] [InnoDB] Number of pools: 1
2021-06-22T21:11:33.119645+08:00 1 [Note] [MY-012951] [InnoDB] Not using CPU crc32 instructions
2021-06-22T21:11:33.120718+08:00 1 [Note] [MY-012203] [InnoDB] Directories to scan './;./;undolog'
2021-06-22T21:11:33.120919+08:00 1 [Note] [MY-012204] [InnoDB] Scanning './'
2021-06-22T21:11:34.062668+08:00 1 [Note] [MY-012208] [InnoDB] Completed space ID check of 1824 files.
2021-06-22T21:11:34.065216+08:00 1 [Note] [MY-012955] [InnoDB] Initializing buffer pool, total size = 20.000000G, instances = 4, chunk size =128.000000M
2021-06-22T21:11:35.997031+08:00 1 [Note] [MY-012957] [InnoDB] Completed initialization of buffer pool
2021-06-22T21:11:36.047647+08:00 0 [Note] [MY-011952] [InnoDB] If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2021-06-22T21:11:36.117710+08:00 1 [Note] [MY-012560] [InnoDB] The log sequence number 34857307986779 in the system tablespace does not match the log sequence number 46364465298844 in the ib_logfiles!
2021-06-22T21:11:36.117905+08:00 1 [Note] [MY-012551] [InnoDB] Database was not shutdown normally!
2021-06-22T21:11:36.118027+08:00 1 [Note] [MY-012552] [InnoDB] Starting crash recovery.
2021-06-22T21:11:36.121404+08:00 1 [Note] [MY-013086] [InnoDB] Starting to parse redo log at lsn = 46364465298473, whereas checkpoint_lsn = 46364465298844
2021-06-22T21:11:36.121564+08:00 1 [Note] [MY-012550] [InnoDB] Doing recovery: scanned up to log sequence number 46364465298844
2021-06-22T21:11:36.158466+08:00 1 [Note] [MY-013083] [InnoDB] Log background threads are being started...
2021-06-22T21:11:36.159180+08:00 1 [Note] [MY-012532] [InnoDB] Applying a batch of 0 redo log records ...
2021-06-22T21:11:36.159345+08:00 1 [Note] [MY-012535] [InnoDB] Apply batch completed!
2021-06-22T21:11:36.260456+08:00 1 [Note] [MY-013252] [InnoDB] Using undo tablespace './undolog/undo_001'.
2021-06-22T21:11:36.261472+08:00 1 [Note] [MY-013252] [InnoDB] Using undo tablespace './undolog/undo_002'.
2021-06-22T21:11:36.265180+08:00 1 [Note] [MY-012910] [InnoDB] Opened 2 existing undo tablespaces.
2021-06-22T21:11:36.265373+08:00 1 [Note] [MY-011980] [InnoDB] GTID recovery trx_no: 29493469719
2021-06-22T21:11:36.545810+08:00 1 [Note] [MY-012255] [InnoDB] Removed temporary tablespace data file: "ibtmp1"
2021-06-22T21:11:36.545993+08:00 1 [Note] [MY-012923] [InnoDB] Creating shared tablespace for temporary tables
2021-06-22T21:11:36.546176+08:00 1 [Note] [MY-012265] [InnoDB] Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2021-06-22T21:11:36.614254+08:00 1 [Note] [MY-012266] [InnoDB] File './ibtmp1' size is now 12 MB.
2021-06-22T21:11:36.614610+08:00 1 [Note] [MY-011825] [InnoDB] Scanning temp tablespace dir:'./#innodb_temp/'
2021-06-22T21:11:36.682093+08:00 1 [Note] [MY-013018] [InnoDB] Created 128 and tracked 128 new rollback segment(s) in the temporary tablespace. 128 are now active.
2021-06-22T21:11:36.682863+08:00 1 [Note] [MY-012976] [InnoDB] 8.0.18 started; log sequence number 46364465298844
2021-06-22T21:11:36.697816+08:00 1 [Note] [MY-011089] [Server] Data dictionary restarting version '80017'.
2021-06-22T21:11:36.868743+08:00 1 [Note] [MY-012357] [InnoDB] Reading DD tablespace files
2021-06-22T21:11:37.174511+08:00 1 [Note] [MY-012356] [InnoDB] Validated 1826/1826  tablespaces
2021-06-22T21:11:37.196510+08:00 1 [Note] [MY-010006] [Server] Using data dictionary with version '80017'.
2021-06-22T21:11:37.252967+08:00 0 [Note] [MY-010855] [Server] Recovering after a crash using /data_binlog/mysql/mysql3312/logs/mysql-bin
2021-06-22T21:11:37.643538+08:00 0 [System] [MY-010229] [Server] Starting crash recovery...
2021-06-22T21:11:37.651222+08:00 0 [System] [MY-010232] [Server] Crash recovery finished.
2021-06-22T21:11:37.659491+08:00 0 [Note] [MY-012487] [InnoDB] DDL log recovery : begin
2021-06-22T21:11:37.660204+08:00 0 [Note] [MY-012488] [InnoDB] DDL log recovery : end
2021-06-22T21:11:37.660978+08:00 0 [Note] [MY-011946] [InnoDB] Loading buffer pool(s) from /data/mysql/mysql3312/data/ib_buffer_pool
2021-06-22T21:11:37.676498+08:00 0 [Note] [MY-012922] [InnoDB] Waiting for purge to start
2021-06-22T21:11:38.078245+08:00 0 [Note] [MY-010182] [Server] Found ca.pem, server-cert.pem and server-key.pem in data directory. Trying to enable SSL support using them.
2021-06-22T21:11:38.078625+08:00 0 [Note] [MY-010304] [Server] Skipping generation of SSL certificates as certificate files are present in data directory.
2021-06-22T21:11:38.081555+08:00 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2021-06-22T21:11:38.081943+08:00 0 [Note] [MY-010308] [Server] Skipping generation of RSA key pair through --sha256_password_auto_generate_rsa_keys as key files are present in data directory.
2021-06-22T21:11:38.082164+08:00 0 [Note] [MY-010308] [Server] Skipping generation of RSA key pair through --caching_sha2_password_auto_generate_rsa_keys as key files are present in data directory.
2021-06-22T21:11:38.083177+08:00 0 [Note] [MY-010252] [Server] Server hostname (bind-address): '*'; port: 3312

启动好之后,查询事务表select * from information_schema.innodb_trx发现事务记录已经没有了

启动复制start slave

复制恢复正常,过一段时间复制延迟变为0了

最后疑问不知道为什么kill掉select 会话一直处于killed的状态不释放,查询资料发现bug

https://bugs.mysql.com/bug.php?id=100326

bug描述

原创声明,本文系作者授权云+社区发表,未经许可,不得转载。

如有侵权,请联系 yunjia_community@tencent.com 删除。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 美女架构师教你如何分析主从延迟问题

    点击上方蓝字每天学习数据库 在我们实际工作过程中,会遇到形形色色的数据库问题,有些问题可以迎刃而解,而有的问题处理起来却比较让人揪心,比如说“主从延迟”问...

    腾讯云数据库 TencentDB
  • 28 | 读写分离有哪些坑?

    读写分离的主要目标就是分摊主库的压力。图 1 中的结构是客户端(client)主动做负载均衡,这种模式下一般会把数据库的连接信息放在客户端的连接层。也就是说,由...

    HaC
  • Postgresql 复制延迟 和 复制"延迟" 与 复制停止大乌龙

    Mysql的逻辑复制性能虽然被诟病的比较久了,但是功能多,延迟复制,级联复制,多源复制. 尤其MYSQL的复制的灵活性有种被玩坏了感觉. POSTGRESQL...

    AustinDatabases
  • Data Guard及ADG日常运维操作

    Data Guard作为Oracle提供的一个高可用及灾备解决方案,理解并可以实施它对于DBA来说是非常重要套的技能

    bsbforever
  • [Data Guard全解析]5.Data Guard及ADG日常运维操作

    Data Guard作为Oracle提供的一个高可用及灾备解决方案,理解并可以实施它对于DBA来说是非常重要套的技能

    bsbforever
  • 停课不停学,云数据库如何支撑好数千万学生上课

    疫情停倮以来,腾讯课堂助力全国数百万老师和数千万学生在线教学、听课。已有3000多个线下教育机构申请入驻腾讯课堂。这背后,离不开腾讯课堂可支持百万人同时在线上课...

    周小军@运维专家
  • mysql主从同步(2)-问题梳理

    之前部署了Mysql主从复制环境(Mysql主从同步(1)-主从/主主环境部署梳理),在mysql同步过程中会出现很多问题,导致数据同步异常。 以下梳理了几种主...

    洗尽了浮华
  • 数据库PostrageSQL-热备

    术语热备用来描述处于归档恢复或后备模式中的服务器连接到服务器并运行只读查询的能力。这有助于复制目的以及以高精度恢复一个备份到一个期望的状态。术语热备也指服务器从...

    cwl_java
  • 数据库PostrageSQL-热备

    术语热备用来描述处于归档恢复或后备模式中的服务器连接到服务器并运行只读查询的能力。这有助于复制目的以及以高精度恢复一个备份到一个期望的状态。术语热备也指服务器从...

    cwl_java
  • 巧用闪回查询来分析事务延迟的问题(r9笔记第77天)

    前段时间有个开发的同事向我咨询一个问题, 开发同事:Oracle会存在一个用户插入数据,已经提交了;但是另外一个用户还查询不到吗?都是同一张表 ...

    jeanron100
  • MySQL读写分离

    读写分离的主要目的就是分摊主库的压力。上图中的结构是客户端主动做负载均衡,这种模式下一般会把数据库的连接信息放在客户端的连接层。由客户端来选择后端数据库进行查询

    用户8639654
  • 史上最详细MySQL全局锁和表锁

    原文:http://www.enmotech.com/web/detail/1/728/1.html (复制链接,打开浏览器即可查看)

    数据和云01
  • MySQL 主从复制的问题及解决方案

    MySQL 主从复制的问题及解决方案

    Java架构师必看
  • union和union all,你使用哪一个?

    今天早上在公司,遇到了一个系统负载的问题,问题的内容如下:某个从库上的系统负载从5月26日开始,一直处于比较高的状态,磁盘IO也比较高,这里我先截取一部分监控的...

    AsiaYe
  • Mysql如何保证高可用

    主备切换是很正常的操作,比如服务下线,断电,软件升级等等,首先我们先了解另外一个概念就是同步延迟,与数据同步的三个时间点如下

    小土豆Yuki
  • 25 | MySQL是怎么保证高可用的?

    正常情况下,只要主库执行更新生成的所有 binlog,都可以传到备库并被正确地执行,备库就能达到跟主库一致的状态,这就是最终一致性。

    HaC
  • 数据库MySQL-读写分离

    数据库读写分离对于大型系统或者访问量很高的互联网应用来说,是必不可少的一个重要功能。

    cwl_java
  • MySQL语法之union和union all,你使用哪一个?

    相信有不少同学都看过“DBA随笔”,幕后的作者是我前同事小叶,作为小叶的导师,我教过他正事,也教过一些坏的习惯,不过写笔记这个习惯算是小叶自己开窍了,他已...

    jeanron100
  • MySQL语法之union和union all,你使用哪一个?

    这是去年在线上遇到了一个系统负载的问题,问题的内容如下:某个从库上的系统负载从5天前开始,一直处于比较高的状态,磁盘IO也比较高,这里我先截取一部分...

    AsiaYe

扫码关注云+社区

领取腾讯云代金券