RDS for MySQL 表上 Metadata Lock 的产生和处理

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS PostgreSQL,高可用系列 2核4GB
云数据库 RDS MySQL,高可用系列 2核4GB
简介: RDS for MySQL 表上 Metadata lock 的产生和处理 1. Metadata lock wait 出现的场景 2. Metadata lock wait 的含义 3. 导致 Metadata lock wait 等待的活动事务 4. 解决方案 5. 如何避免出现长时间 Metadata lock wait 导致表上相关查询阻塞,影响业务 1.

RDS for MySQL 表上 Metadata lock 的产生和处理

1. Metadata lock wait 出现的场景

2. Metadata lock wait 的含义

3. 导致 Metadata lock wait 等待的活动事务

4. 解决方案

5. 如何避免出现长时间 Metadata lock wait 导致表上相关查询阻塞,影响业务


1. Metadata lock wait 出现的场景

  • 创建、删除索引

  • 修改表结构

  • 表维护操作(optimize table、repair table 等)

  • 删除表

  • 获取表上表级写锁 (lock table tab_name write)

metadata_lock_02.png

注:

  • 支持事务的 InnoDB 引擎表和 不支持事务的 MyISAM 引擎表,都会出现 Metadata Lock Wait 等待现象。
  • 一旦出现 Metadata Lock Wait 等待现象,后续所有对该表的访问都会阻塞在该等待上,导致连接堆积,业务受影响。

 

2. Metadata lock wait 的含义

为了在并发环境下维护表元数据的数据一致性,在表上有活动事务(显式或隐式)的时候,不可以对元数据进行写入操作。因此 MySQL 引入了 metadata lock ,来保护表的元数据信息。

因此在对表进行上述操作时,如果表上有活动事务(未提交或回滚),请求写入的会话会等待在 Metadata lock wait 。

3. 导致 Metadata lock wait 等待的活动事务

  • 当前有对表的长时间查询

  • 显示或者隐式开启事务后未提交或回滚,比如查询完成后未提交或者回滚。

  • 表上有失败的查询事务

4. 解决方案

  • show processlist 查看会话有长时间未完成的查询,使用kill 命令终止该查询。

metadata_lock_03.png

metadata_lock_04.png

  • 查询 information_schema.innodb_trx 看到有长时间未完成的事务, 使用 kill 命令终止该查询。

select concat('kill ',i.trx_mysql_thread_id,';') from information_schema.innodb_trx i,
  (select 
         id, time
     from
         information_schema.processlist
     where
         time = (select 
                 max(time)
             from
                 information_schema.processlist
             where
                 state = 'Waiting for table metadata lock'
                     and substring(info, 1, 5) in ('alter' , 'optim', 'repai', 'lock ', 'drop ', 'creat'))) p
  where timestampdiff(second, i.trx_started, now()) > p.time
  and i.trx_mysql_thread_id  not in (connection_id(),p.id);

-- 请根据具体的情景修改查询语句
-- 如果导致阻塞的语句的用户与当前用户不同,请使用导致阻塞的语句的用户登录来终止会话

metadata_lock_05.png

  • 如果上面两个检查没有发现,或者事务过多,建议使用下面的查询将相关库上的会话终止


select 
    concat('kill ', p1.id, ';')
from
    information_schema.processlist p1,
    (select 
        id, time
    from
        information_schema.processlist
    where
        time = (select 
                max(time)
            from
                information_schema.processlist
            where
                state = 'Waiting for table metadata lock'
                    and substring(info, 1, 5) in ('alter' , 'optim', 'repai', 'lock ', 'drop ', 'creat', 'trunc'))) p2
where
    p1.time >= p2.time
        and p1.command in ('Sleep' , 'Query')
        and p1.id not in (connection_id() , p2.id);

-- RDS for MySQL 5.5 语句请根据具体的 DDL 语句情况修改查询的条件;
-- 如果导致阻塞的语句的用户与当前用户不同,请使用导致阻塞的语句的用户登录来终止会话

 

5. 如何避免出现长时间 metadata lock wait 导致表上相关查询阻塞,影响业务

  • 在业务低峰期执行上述操作,比如创建删除索引。

  • 在到RDS的数据库连接建立后,设置会话变量 autocommit 为 1 或者 on,比如 set autocommit=1; 或 set autocommit=on; 。

  • 考虑使用事件来终止长时间运行的事务,比如下面的例子中会终止执行时间超过60分钟的事务。

    create event my_long_running_trx_monitor
    on schedule every 60 minute
    starts '2015-09-15 11:00:00'
    on completion preserve enable do
    begin
      declare v_sql varchar(500);
      declare no_more_long_running_trx integer default 0; 
      declare c_tid cursor for
        select concat ('kill ',trx_mysql_thread_id,';') 
        from information_schema.innodb_trx 
        where timestampdiff(minute,trx_started,now()) >= 60;
      declare continue handler for not found
        set no_more_long_running_trx=1;
     
      open c_tid;
      repeat
        fetch c_tid into v_sql;
     set @v_sql=v_sql;
     prepare stmt from @v_sql;
     execute stmt;
     deallocate prepare stmt;
      until no_more_long_running_trx end repeat;
      close c_tid;
    end;


    注:请根据您自身情况,自行修改运行间隔和事务执行时长。

  • 执行上述1中操作前,设置会话变量 lock_wait_timeout 为较小值,比如 set lock_wait_timeout=30; 命令可以设置 metadata lock wait 的最长时间为 30 秒;避免长时间等待元数据锁影响表上其他业务查询。

 metadata_lock_08.png

 

相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
2月前
|
负载均衡 算法 关系型数据库
大数据大厂之MySQL数据库课程设计:揭秘MySQL集群架构负载均衡核心算法:从理论到Java代码实战,让你的数据库性能飙升!
本文聚焦 MySQL 集群架构中的负载均衡算法,阐述其重要性。详细介绍轮询、加权轮询、最少连接、加权最少连接、随机、源地址哈希等常用算法,分析各自优缺点及适用场景。并提供 Java 语言代码实现示例,助力直观理解。文章结构清晰,语言通俗易懂,对理解和应用负载均衡算法具有实用价值和参考价值。
大数据大厂之MySQL数据库课程设计:揭秘MySQL集群架构负载均衡核心算法:从理论到Java代码实战,让你的数据库性能飙升!
|
2月前
|
存储 关系型数据库 MySQL
【免费动手教程上线】阿里云RDS MySQL推出大容量高性能存储:高性能本地盘(最高16TB存储空间)、高性能云盘(最高64TB存储空间)
阿里云RDS MySQL提供高性能本地盘与高性能云盘等存储方案,满足用户大容量、低延迟需求。高性能本地盘单盘最大16TB,IO延时微秒级;高性能云盘兼容ESSD特性,支持IO性能突发、BPE及16K原子写等能力。此外,阿里云还提供免费动手体验教程,帮助用户直观感受云数据库 RDS 存储性能表现。
|
3月前
|
负载均衡 算法 关系型数据库
大数据新视界--大数据大厂之MySQL数据库课程设计:MySQL集群架构负载均衡故障排除与解决方案
本文深入探讨 MySQL 集群架构负载均衡的常见故障及排除方法。涵盖请求分配不均、节点无法响应、负载均衡器故障等现象,介绍多种负载均衡算法及故障排除步骤,包括检查负载均衡器状态、调整算法、诊断修复节点故障等。还阐述了预防措施与确保系统稳定性的方法,如定期监控维护、备份恢复策略、团队协作与知识管理等。为确保 MySQL 数据库系统高可用性提供全面指导。
|
3月前
|
SQL 关系型数据库 MySQL
大数据新视界--大数据大厂之MySQL数据库课程设计:MySQL 数据库 SQL 语句调优方法详解(2-1)
本文深入介绍 MySQL 数据库 SQL 语句调优方法。涵盖分析查询执行计划,如使用 EXPLAIN 命令及理解关键指标;优化查询语句结构,包括避免子查询、减少函数使用、合理用索引列及避免 “OR”。还介绍了索引类型知识,如 B 树索引、哈希索引等。结合与 MySQL 数据库课程设计相关文章,强调 SQL 语句调优重要性。为提升数据库性能提供实用方法,适合数据库管理员和开发人员。
|
3月前
|
关系型数据库 MySQL 大数据
大数据新视界--大数据大厂之MySQL 数据库课程设计:MySQL 数据库 SQL 语句调优的进阶策略与实际案例(2-2)
本文延续前篇,深入探讨 MySQL 数据库 SQL 语句调优进阶策略。包括优化索引使用,介绍多种索引类型及避免索引失效等;调整数据库参数,如缓冲池、连接数和日志参数;还有分区表、垂直拆分等其他优化方法。通过实际案例分析展示调优效果。回顾与数据库课程设计相关文章,强调全面认识 MySQL 数据库重要性。为读者提供综合调优指导,确保数据库高效运行。
|
5月前
|
关系型数据库 MySQL 数据库
RDS用多了,你还知道MySQL主从复制底层原理和实现方案吗?
随着数据量增长和业务扩展,单个数据库难以满足需求,需调整为集群模式以实现负载均衡和读写分离。MySQL主从复制是常见的高可用架构,通过binlog日志同步数据,确保主从数据一致性。本文详细介绍MySQL主从复制原理及配置步骤,包括一主二从集群的搭建过程,帮助读者实现稳定可靠的数据库高可用架构。
324 9
RDS用多了,你还知道MySQL主从复制底层原理和实现方案吗?
|
8月前
|
关系型数据库 MySQL Linux
在 CentOS 7 中通过编译源码方式安装 MySQL 数据库的详细步骤,包括准备工作、下载源码、编译安装、配置 MySQL 服务、登录设置等。
本文介绍了在 CentOS 7 中通过编译源码方式安装 MySQL 数据库的详细步骤,包括准备工作、下载源码、编译安装、配置 MySQL 服务、登录设置等。同时,文章还对比了编译源码安装与使用 RPM 包安装的优缺点,帮助读者根据需求选择最合适的方法。通过具体案例,展示了编译源码安装的灵活性和定制性。
744 2
|
9月前
|
关系型数据库 MySQL Java
Django学习二:配置mysql,创建model实例,自动创建数据库表,对mysql数据库表已经创建好的进行直接操作和实验。
这篇文章是关于如何使用Django框架配置MySQL数据库,创建模型实例,并自动或手动创建数据库表,以及对这些表进行操作的详细教程。
306 0
Django学习二:配置mysql,创建model实例,自动创建数据库表,对mysql数据库表已经创建好的进行直接操作和实验。
|
26天前
|
人工智能 运维 关系型数据库
数据库运维:mysql 数据库迁移方法-mysqldump
本文介绍了MySQL数据库迁移的方法与技巧,重点探讨了数据量大小对迁移方式的影响。对于10GB以下的小型数据库,推荐使用mysqldump进行逻辑导出和source导入;10GB以上可考虑mydumper与myloader工具;100GB以上则建议物理迁移。文中还提供了统计数据库及表空间大小的SQL语句,并讲解了如何使用mysqldump导出存储过程、函数和数据结构。通过结合实际应用场景选择合适的工具与方法,可实现高效的数据迁移。
206 1
|
4月前
|
关系型数据库 MySQL Java
【YashanDB知识库】原生mysql驱动配置连接崖山数据库
【YashanDB知识库】原生mysql驱动配置连接崖山数据库
【YashanDB知识库】原生mysql驱动配置连接崖山数据库

热门文章

最新文章

推荐镜像

更多