[技术分享]一招解决 MySQL 中 DDL 被阻塞的问题
爱可生开源社区.
爱可生开源社区,提供稳定的MySQL企业级开源工具及服务,每年1024开源一款优良组件,并持续运营维护。
背景
之前碰到客户咨询定位DDL阻塞的相关问题,整理了一下方法,如何解决DDL被阻塞的问题。下面,就这个问题,整理了一下思路:
怎么判断一个 DDL 是不是被阻塞了?
当 DDL 被阻塞时,怎么找出阻塞它的会话?
1. 如何判断一个DDL是不是被阻塞了?
测试过程如下:
mysql> use test;
Database changed
mysql> CREATE TABLE `test` (
-> `id` int(11) AUTO_INCREMENT PRIMARY KEY,
-> `name` varchar(10)
-> );
Query OK, 0 rows affected (0.01 sec)
# 插入数据
mysql> insert into test values (1,'aaa'),(2,'bbb'),(3,'ccc'),(4,'ddd');
Query OK, 1 row affected (0.01 sec)
mysql> begin;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from test;
+----+------+
| id | name |
+----+------+
| 1 | aaa |
| 2 | bbb |
| 3 | ccc |
| 4 | ddd |
+----+------+
4 rows in set (0.00 sec)
# 模拟元数据锁阻塞
# 会话 1
mysql> lock tables test read;
Query OK, 0 rows affected (0.00 sec)
# 会话 2
mysql> alter table test add c1 varchar(25);
阻塞中
# 会话 3
mysql> show processlist;
DDL一旦被阻塞了,后续针对该表的所有操作都会被阻塞,都会显示 Waiting for table metadata lock。
上述情况的解决方案:Kill DDL 操作或 Kill 阻塞 DDL 的会话。
下面对于DDL的操作,我们需要获取元数据库锁的阶段有两个方面:DDL开始之初和DDL结束之前。如果是后者,就意味着之前的操作都要回滚,成本相对较高。所以,碰到类似情况,我们一般都会Kill阻塞DDL的会话。
2. 怎么知道是哪些会话阻塞了DDL?
sys.schema_table_lock_waits 是 MySQL 5.7 引入的,用来定位 DDL 被阻塞的问题。
针对上面这个情况。可以查看 sys.schema_table_lock_waits 的输出。
mysql> select * from sys.schema_table_lock_waits\G;
只有一个alter操作,却产生了两条记录,而且两条记录的Kill对象还不一样。如果对表结构不熟悉或不仔细看记录内容的话,难免会kill错对象。
两条记录的 blocking_lock_type 类型分别为 shared_read_only 和 shared_upgradable。我们需要kill掉的是 shared_read_only。
在ddl操作被阻塞后,如果后续有多个查询被ddl操作堵塞,还会产生2n多个条记录。
在定位问题时,这2n条记录看起来就比较难以定位了。这个时候,我们需要对上述2n条记录进行过滤。过滤的关键是 blocking_lock_type 不等于 shared_upgradable。
shared_upgradable 是一个可升级的共享元数据锁,加锁期间,允许并发查询和更新。所以,阻塞ddl的不会是 shared_upgradable。
针对上面这个场景,我们可以通过下面这个查询来精确地定位出需要 Kill 的会话。
mysql> SELECT sql_kill_blocking_connection FROM sys.schema_table_lock_waits
WHERE blocking_lock_type <> 'SHARED_UPGRADABLE'
AND waiting_query = 'alter table test add c1 varchar(25)';
MySQL 5.7中使用sys.schema_table_lock_waits的注意事项
sys.schema_table_lock_waits 视图依赖了一张MDL相关的表 performance_schema.metadata_locks。该表是MySQL 5.7引入的,会显示MDL的相关信息,包括作用对象、锁的类型及锁的状态等。但在MySQL 5.7中,该表默认为空,因为与之相关的 instrument 默认没有开启,MySQL 8.0才默认开启。
mysql> select * from performance_schema.setup_instruments
where name='wait/lock/metadata/sql/mdl';
1 row in set (0.00 sec)
所以,在MySQL 5.7中,如果我们要使用 sys.schema_table_lock_waits,必须首先开启MDL 相关的 instrument。
开启方式: 直接修改 performance_schema.setup_instruments 表即可。具体SQL如下。
mysql> UPDATE PERFORMANCE_SCHEMA.setup_instruments SET ENABLED = 'YES', TIMED = 'YES' WHERE NAME = 'wait/lock/metadata/sql/mdl';
但这种方式是临时生效,实例重启后,又会恢复为默认值。
建议:同步修改配置文件或者在部署 MySQL 集群时一开始配置文件的参数就修改成功。
[mysqld]
performance-schema-instrument ='wait/lock/metadata/sql/mdl=ON'
总结
执行 show processlist,如果DDL的状态是 Waiting for table metadata lock ,则意味着这个DDL被阻塞了。
定位导致DDL被阻塞的会话,常用的方法如下:sys.schema_table_lock_waits
select sql_kill_blocking_connection from sys.schema_table_lock_waits WHERE blocking_lock_type <> 'SHARED_UPGRADABLE' and (waiting_query like 'alter%' OR waiting_query like 'create%' OR waiting_query like 'drop%' OR waiting_query like 'truncate%' OR waiting_query like 'rename%');
这种方法适用于MySQL 5.7和8.0。
注意,MySQL 5.7中,MDL相关的instrument默认没有打开。
Kill DDL之前的会话。
select concat('kill',i.trx_mysql_thread_id,';') from information_schema.innodb_trx i, (select max(time) as max_time from information_schema.processlist
where state = 'Waiting for table metadata lock'
and (info like 'alter%'
OR info like 'create%'
OR info like 'drop%'
OR info like 'truncate%'
OR info like 'rename%')) p
WHERE timestampdiff(second, i.trx_started ,now()) > p.max_time;
如果MySQL 5.7中MDL相关的instrument没有打开,可使用该方法。
相关文章:
[技术分享]一招解决 MySQL 中 DDL 被阻塞的问题
爱可生开源社区. 爱可生开源社区,提供稳定的MySQL企业级开源工具及服务,每年1024开源一款优良组件,并持续运营维护。 背景 之前碰到客户咨询定位DDL阻塞的相关问题,整理了一下方法,如何解决DDL被阻塞的问题。下面,就这个问题,整理了一下思路: 怎么判断一个 DDL 是…...
Windows搭建Emby媒体库服务器,无公网IP远程访问本地影音文件
文章目录 1.前言2. Emby网站搭建2.1. Emby下载和安装2.2 Emby网页测试 3. 本地网页发布3.1 注册并安装cpolar内网穿透3.2 Cpolar云端设置3.3 Cpolar内网穿透本地设置 4.公网访问测试5.结语 1.前言 在现代五花八门的网络应用场景中,观看视频绝对是主力应用场景之一&…...
自动化测试系列 之 Python单元测试框架unittest
一、概述 什么是单元测试 单元测试是一种软件测试方法,是测试最小的可测试单元,通常是一个函数或一个方法。 在软件开发过程中,单元测试作为一项重要的测试方法被广泛应用。 为什么需要单元测试 单元测试是软件开发中重要的一环…...
C语言朴素算法
#include <stdio.h> #include <string.h>// 朴素算法,用于字符串匹配 void naiveMatch(char* text, char* pattern) {int textLength strlen(text); // 计算文本串长度int patternLength strlen(pattern); // 计算模式串长度for …...
【力扣题解】P501-二叉搜索树中的众数-Java题解
👨💻博客主页:花无缺 欢迎 点赞👍 收藏⭐ 留言📝 加关注✅! 本文由 花无缺 原创 收录于专栏 【力扣题解】 文章目录 【力扣题解】P501-二叉搜索树中的众数-Java题解🌏题目描述💡题解…...
Wnmp本地部署结合内网穿透实现任意浏览器远程访问本地服务
最近,我发现了一个超级强大的人工智能学习网站。它以通俗易懂的方式呈现复杂的概念,而且内容风趣幽默。我觉得它对大家可能会有所帮助,所以我在此分享。点击这里跳转到网站。 文章目录 前言1.Wnmp下载安装2.Wnmp设置3.安装cpolar内网穿透3.1…...
深信服AF防火墙配置SSL VPN
防火墙版本:8.0.85 需提前确认防火墙是是否有SSL VPN的授权,确认授权用户数量 1、确认内外网接口划分 2、网络→SSL VPN,选择内外网接口地址 3、SSL VPN→用户管理→新增一个SSL VPN的用户 4、新增L3VPN资源,类型选择Other&…...
在Spring Cloud中使用Gateway 网关
我们在上述文章中介绍了相关Spring Cloud的五大核心组件,现在我们来了解一下关于Spring Cloud的网关,关于使用网关,我们同时也需要知道他在一个架构中起到的作用,并且,我们需要知道网关具体的相关功能,本篇…...
【Python】配置环境变量
Python配置Windows系统环境变量 打开电脑属性 ——> 高级系统设置 ——> 高级 ——> 环境变量 Python安装目录 D:\Program Files\Python39 winR打开运行,输入cmd打开命令窗口 python -V...
使用.Net nanoFramework 驱动ESP32的OLED显示屏
本文介绍如何使用.Net nanoFramework 驱动ESP32的OLED显示屏。我们将会从最基础的部分开始,逐步深入,让你能够理解并实现整个过程。无论你是初学者还是有一定经验的开发者,这篇文章都会对你有所帮助。 1. 硬件准备 1.1 ESP32开发板 这里我们…...
0基础学习VR全景平台篇第134篇:720VR全景,云台调整节点
相机、云台和脚架全套设备组装完成后需要进行调校才能开始拍摄。这一节,我们将主要介绍云台调整的两个内容:对中心靶、调三点一线。(后附调校原理) 云台部件名称 一、调节准备 (一)对于安装好的云台 1.检…...
扫地机器人地图与用户终端的同步
以下内容为本人的学习笔记,如需要转载,请声明原文链接 微信公众号「ENG八戒」https://mp.weixin.qq.com/s/APaJheSbgTW3jNssWsp5Ng 地图数据来源于机器人算法模块,一般通过SLAM算法完成建图的过程。 建图过程中,基础数据涉及到各…...
使用机器学习进行语法错误检测/纠正
@francescofranco_39234 一、说明 一般的学习,特别是深度学习,促进了自然语言处理。各种模型使人们能够执行机器翻译、文本摘要和情感分析——仅举几个用例。今天,我们将研究另一个流行的用途:我们将使用Gramformer构建一个用于机器学习语法错误检测和纠正的管道。 阅读本文…...
从0到1快速入门ETLCloud
一、ETLCloud的介绍 ETL是将业务系统的数据经过抽取(Extract)、清洗转换(Transform)之后加载(Load)到数据仓库的过程,目的是将企业中的分散、凌乱、标准不统一的数据整合到一起,为企…...
QT上位机开发(会员管理软件)
【 声明:版权所有,欢迎转载,请勿用于商业用途。 联系信箱:feixiaoxing 163.com】 前面我们学习了ini文件的解析办法,通过QSettings类就可以很轻松地访问ini文件里面的数据。除了ini文件之外,另外一种经常出…...
线性代数笔记3 1.1
学习视频: 2.2 矩阵运算(二)_哔哩哔哩_bilibili 包括内容: p10矩阵运算(二) p11特殊矩阵 p12逆矩阵(一) p13逆矩阵(二)...
2023年12月编程语言排行榜
TIOBE Index for December 2023 December Headline: C# on its way to become programming language of the year 2023 2023年12月的TIOBE指数:12月头条:c#将成为2023年最佳编程语言 Yes, I know, we have been here before. At the end of 2022, it looked like …...
Redis VS Memcached:选择哪个更适合您的应用?
目录 1、前言 2、概念简介 2.1 Redis 2.2 Memcached 3、数据模型 4、持久性 5、分布式能力 6、性能和扩展性 7、如何选择适合您引用的缓存系统 8、结语 1、前言 Redis和Memcached都是常见的内存缓存系统,用于提升应用程序的性能和可扩展性。它们都具有高…...
【HarmonyOS开发】共享包HAR和HSP的创建和使用以及三方库的发布
OpenHarmony提供了两种共享包,HAR(Harmony Archive)静态共享包,和HSP(Harmony Shared Package)动态共享包。 HAR与HSP都是为了实现代码和资源的共享,都可以包含代码、C库、资源和配置文件&…...
安装 Node.js、npm
安装 nodejs 安装Node.js的最简单的方法是通过软件包管理器。 Node.js官网:https://nodejs.org/en/download/ cd /usr/local/src/wget -c https://nodejs.org/dist/v18.16.0/node-v18.16.0-linux-x64.tar.xz xz -d node-v18.16.0-linux-x64.tar.xz tar -xf node…...
【Linux】shell脚本忽略错误继续执行
在 shell 脚本中,可以使用 set -e 命令来设置脚本在遇到错误时退出执行。如果你希望脚本忽略错误并继续执行,可以在脚本开头添加 set e 命令来取消该设置。 举例1 #!/bin/bash# 取消 set -e 的设置 set e# 执行命令,并忽略错误 rm somefile…...
微软PowerBI考试 PL300-选择 Power BI 模型框架【附练习数据】
微软PowerBI考试 PL300-选择 Power BI 模型框架 20 多年来,Microsoft 持续对企业商业智能 (BI) 进行大量投资。 Azure Analysis Services (AAS) 和 SQL Server Analysis Services (SSAS) 基于无数企业使用的成熟的 BI 数据建模技术。 同样的技术也是 Power BI 数据…...
边缘计算医疗风险自查APP开发方案
核心目标:在便携设备(智能手表/家用检测仪)部署轻量化疾病预测模型,实现低延迟、隐私安全的实时健康风险评估。 一、技术架构设计 #mermaid-svg-iuNaeeLK2YoFKfao {font-family:"trebuchet ms",verdana,arial,sans-serif;font-size:16px;fill:#333;}#mermaid-svg…...
Day131 | 灵神 | 回溯算法 | 子集型 子集
Day131 | 灵神 | 回溯算法 | 子集型 子集 78.子集 78. 子集 - 力扣(LeetCode) 思路: 笔者写过很多次这道题了,不想写题解了,大家看灵神讲解吧 回溯算法套路①子集型回溯【基础算法精讲 14】_哔哩哔哩_bilibili 完…...
HTML 列表、表格、表单
1 列表标签 作用:布局内容排列整齐的区域 列表分类:无序列表、有序列表、定义列表。 例如: 1.1 无序列表 标签:ul 嵌套 li,ul是无序列表,li是列表条目。 注意事项: ul 标签里面只能包裹 li…...
苍穹外卖--缓存菜品
1.问题说明 用户端小程序展示的菜品数据都是通过查询数据库获得,如果用户端访问量比较大,数据库访问压力随之增大 2.实现思路 通过Redis来缓存菜品数据,减少数据库查询操作。 缓存逻辑分析: ①每个分类下的菜品保持一份缓存数据…...
ServerTrust 并非唯一
NSURLAuthenticationMethodServerTrust 只是 authenticationMethod 的冰山一角 要理解 NSURLAuthenticationMethodServerTrust, 首先要明白它只是 authenticationMethod 的选项之一, 并非唯一 1 先厘清概念 点说明authenticationMethodURLAuthenticationChallenge.protectionS…...
06 Deep learning神经网络编程基础 激活函数 --吴恩达
深度学习激活函数详解 一、核心作用 引入非线性:使神经网络可学习复杂模式控制输出范围:如Sigmoid将输出限制在(0,1)梯度传递:影响反向传播的稳定性二、常见类型及数学表达 Sigmoid σ ( x ) = 1 1 +...
Mobile ALOHA全身模仿学习
一、题目 Mobile ALOHA:通过低成本全身远程操作学习双手移动操作 传统模仿学习(Imitation Learning)缺点:聚焦与桌面操作,缺乏通用任务所需的移动性和灵活性 本论文优点:(1)在ALOHA…...
CRMEB 中 PHP 短信扩展开发:涵盖一号通、阿里云、腾讯云、创蓝
目前已有一号通短信、阿里云短信、腾讯云短信扩展 扩展入口文件 文件目录 crmeb\services\sms\Sms.php 默认驱动类型为:一号通 namespace crmeb\services\sms;use crmeb\basic\BaseManager; use crmeb\services\AccessTokenServeService; use crmeb\services\sms\…...
