大数跨境
首页
>
夜莺 + AI 重塑 MySQL MDL 锁监控实践
>
0
0

夜莺 + AI 重塑 MySQL MDL 锁监控实践

夜莺 + AI 重塑 MySQL MDL 锁监控实践 Kubernetes技术栈
2025-06-25
0
导读：近期，线上有台 MySQL 服务器接连出现平均负载过高的情况。经排查，是 MDL 锁引发了 SQL 阻塞，进

近期，线上有台 MySQL 服务器接连出现平均负载过高的情况。经排查，是 MDL 锁引发了 SQL 阻塞，进而导致服务器负载升高。因此，计划增加 MDL 锁告警指标，以便更及时地发现问题。 经组内讨论，期望达成以下效果：

  • 状态为 "Waiting for table metadata lock" 的线程持续时间超过 60 秒时触发告警
  • 告警信息中尽可能附带 MDL 锁的阻塞关系及对应的 SQL 语句，以便快速定位问题

需求分析

  1. 将状态为 "Waiting for table metadata lock" 且持续时间超过 60 秒的线程作为时序指标进行上报 
-- 统计 状态为 "Waiting for table metadata lock" 且持续时间 60 秒的线程数量
select 'Variable_name',count(*) Value from performance_schema.processlist where time >60 and command not in ('Sleep','Binlog Dump','Daemon') and state='Waiting for table metadata lock' and info is not null;
2. 告警事件生成时，分析 MDL 锁的阻塞关系，抓取线程操作的 SQL 信息在告警事件中展示。该过程需获取阻塞 DDL 的对象、被阻塞的 DDL 以及该 DDL 所阻塞的对象的相关信息。 
-- 获取被阻塞的 DDL 语句和线程 id 及 阻塞 DDL 执行语句和线程 id
select s.waiting_pid,s.waiting_query,s.blocking_pid,p.info blocking_query from sys.schema_table_lock_waits s join performance_schema.processlist p where s.blocking_lock_type!='SHARED_UPGRADABLE'and s.blocking_pid=p.id and s.waiting_query regexp '^(alter|create|drop|truncate|rename)'orderby s.waiting_pid limit1;

-- 获取被 DDL 阻塞的 DML 语句和线程 id, 此处仅取一条被阻塞的 DML 语句
select s.waiting_pid,s.waiting_query,s.blocking_pid,p.info blocking_query from sys.schema_table_lock_waits s join performance_schema.processlist p where s.blocking_lock_type!='SHARED_UPGRADABLE'and s.blocking_pid=p.id and s.waiting_query regexp '^(select|update|delete|insert)'orderby s.waiting_pid limit1;

-- 获取被 MDL 锁阻塞的所有 语句和线程 id（DDL 和 DML）
select s.waiting_pid,s.waiting_query,s.blocking_pid,p.info blocking_query from sys.schema_table_lock_waits s join performance_schema.processlist p where s.blocking_lock_type!='SHARED_UPGRADABLE'and s.blocking_pid=p.id;

前置信息：我们使用夜莺v8版本对数据库进行监控，以下实现思路基于现有环境考虑，但并不局限于夜莺监控。（夜莺v8抽象了告警事件的 pipline 功能，相比传统监控能更方便的对告警事件进行加工处理）

实现思路：在告警事件生成时，解析告警事件以获取DB实例信息，而后请求 API 获取对应实例 MDL 锁阻塞相关的 SQL 信息，然后更新告警事件。截至本文发出前，临时决定结合 AI 对告警事件进行分析给出处理建议。因此，在更新告警事件后，还需要请求 AI 接口进行分析，并将 AI 给出的建议放到告警事件中进行最终展示。

实现步骤

一: 指标采集

利用 categraf 采集器的 input.exec 插件

# cat conf/input.exec/exec.toml 
[[instances]]
# # commands, support glob
commands = [
  "python3 utils/exec_mysql_monitor.py"
]

# 时序指标数据标准输出格式，类 prometheus exporter
mysql,cluster=devops,port=3306 Table_metadata_locks=0

msyql_Table_metadata_locks: 状态为 "Waiting for table metadata lock" 且持续时间超过 60 秒的线程数;

同时告警条件设置为 msyql_Table_metadata_locks > 0

二：封装 API

解析告警事件以获取DB实例信息，抓取 MDL 锁阻塞相关的 SQL 信息，然后更新告警事件。
msyql_Table_metadata_locks > 0 时产生告警，告警事件的格式如下：

{
    "id": 148393,
    "cate": "prometheus",
    "cluster": "VictoriaMetrics",
    "datasource_id": 1,
    "group_id": 2,
    "group_name": "DBA Group",
    "hash": "8be17844f5063f4ce764295b7fcd4125",
    "rule_id": 44,
    "rule_name": "Waiting for table metadata lock",
    "rule_note": "Waiting for table metadata lock 状态的线程持续时间超过了 60s",
    "rule_prod": "metric",
    "rule_algo": "",
    "severity": 3,
    "prom_for_duration": 60,
    "prom_ql": "mysql_Table_metadata_locks > 0",
    "rule_config": {
        "queries": [
            {
                "prom_ql": "mysql_Table_metadata_locks > 0",
                "severity": 3,
                "unit": "none"
            }
        ]
    },
    "prom_eval_interval": 30,
    "callbacks": [],
    "runbook_url": "",
    "notify_recovered": 1,
    "notify_groups": [
        "3"
    ],
    "target_ident": "172.xx.xx.xxx",
    "target_note": "",
    "trigger_time": 1749536026,
    "trigger_value": "1421",
    "trigger_values": "1421",
    "trigger_values_json": {
        "values_with_unit": null
    },
    "tags": [
        "Region=JXQ",
        "__name__=mysql_Table_metadata_locks",
        "app=mysql",
        "cluster=prod",
        "ident=172.xx.xx.xxx",
        "port=3306",
        "rulename=Waiting for table metadata lock"
    ],
    "tags_map": {
        "Region": "JXQ",
        "__name__": "mysql_Table_metadata_locks",
        "app": "mysql",
        "cluster": "prod",
        "ident": "172.xxx.xxx.xxx",
        "port": "3306",
        "rulename": "Waiting for table metadata lock"
    },
    "original_tags": [
        "Region=JXQ",
        "__name__=mysql_Table_metadata_locks",
        "app=mysql",
        "cluster=prod",
        "ident=172.xxx.xxx.xxx",
        "port=3306",
        "rulename=Waiting for table metadata lock"
    ],
    "annotations": {},
    "is_recovered": false,
    "last_eval_time": 1749536026,
    "last_sent_time": 0,
    "notify_cur_number": 6,
    "first_trigger_time": 1749535213,
    "extra_config": null,
    "status": 0,
    "claimant": "",
    "sub_rule_id": 0,
    "extra_info": null,
    "target": null,
    "recover_config": {
        "judge_type": 0,
        "recover_exp": ""
    },
    "rule_hash": "",
    "extra_info_map": null,
    "notify_rule_ids": null,
    "notify_version": 0,
    "notify_rules": null
}

以下是分析抓取 MDL 阻塞信息的主要逻辑：解析告警事件，然后把锁相关的信息添加到告警事件的 annotations 字段中（annotations 是一个 map 类型）, 注意告警事件的结构不能乱改，否则后续的告警流程不认

from flask import request
from flask_restful import Resource
from utils.mysql import DB,Queries
from utils.logger import logger

API_SECRET = "xxxxxxxxx"

class EventUpdateView(Resource):
    """
    Nightingale 告警事件处理
    """
    def post(self):
        # 验证 API 密钥
        api_key = request.headers.get('X-Api-Key')
        if api_key != API_SECRET:
            return {'status': 'error', 'message': 'Invalid API key'}, 401

        try:
            data = request.get_json()
            tags_map = data.get('tags_map')

            # 初始化数据库连接
            ident = tags_map.get('ident')
            port = int(tags_map.get('port'))
            db = DB(ident, port)

            # 获取被 DDL 阻塞的 DML 操作 (sql & session id）
            blocked_dml_result = self._get_blocked_query_result(db, is_ddl_blocked=True)
            if blocked_dml_result:
                data['annotations']['blocked_ddl_pid'] = blocked_dml_result['waiting_pid']
                data['annotations']['blocked_ddl_query'] = blocked_dml_result['waiting_query']
                data['annotations']['blocked_ddl_by_dml_pid'] = blocked_dml_result['blocking_pid']
                data['annotations']['blocked_ddl_by_dml_query'] = blocked_dml_result['blocking_query']

            # 获取阻塞 DDL 语句的操作（sql & session id）
            blocked_ddl_result = self._get_blocked_query_result(db, is_ddl_blocked=False)
            if blocked_ddl_result:
                data['annotations']['blocked_dml_pid'] = blocked_ddl_result['waiting_pid']
                data['annotations']['blocked_dml_query'] = blocked_ddl_result['waiting_query']

            return data, 200

        except Exception as e:
            logger.error("Error processing event update: {}".format(e))
            return {'status': 'error', 'message': 'Internal server error'}, 500

    def _get_blocked_query_result(self, db, is_ddl_blocked):
        """
        根据是否获取 DDL 阻塞情况执行对应的 SQL
        """
        if is_ddl_blocked:
            query = Queries.get('blocked_ddl_by_dml')
        else:
            query = Queries.get('blocked_dml_by_ddl')
        try:
            result = db.executed(query)
            return result[0] if result elseNone
        except Exception as e:
            logger.error("Database query execution failed: {}".format(e))
            returnNone

三：配置告警

配置概述：告警规则 需要关联 通知规则 ，通知规则 关联 事件处理器、通知媒介、消息模版。 告警规则负责产生告警事件，通知规则负责二次处理告警事件（由事件处理器完成），并把告警发送出去。

由此可知夜莺的告警配置是一个逐级关联的关系，所以我建议从后往前逐一进行配置，也就是先配置消息模板，通知媒介，然后是通知规则，最后是告警规则

  • 通知媒介配置，我这里使用脚本发送飞书消息告警。（消息模版配置比较简单此处省略）

  • 通知规则配置
    我们需要在通知规则中关联事件处理器，目前夜莺的告警引擎内置了 5 种事件处理器

Relable: 可以对告警事件的标签进行各种操作，删除、重命名、合并和修改标签值等
Callback: 可以通过 HTTP 调用将告警事件信息发送到外部服务，主要用于事件通知和集成
Event Update：可以通过 HTTP 调用外部服务来动态更新告警事件的内容
Event Drop：根据自定义的逻辑条件来决定是否删除特定的告警事件
AI Summary：调用 AI 模型分析告警事件，生成告警总结和建议

因为我们要修改告警事件的内容以及结合AI 做一些告警分析，所以我们只需要用 Event Update 和  AI Summary 这两个处理器即可。

Event Update 处理器：在该处理器中调用我们前面封装的 API ，抓取 相关的SQL语句添加到告警事件中并返回一个新的 event

AI Summary 处理器：

我们还要在通知规则中关联通知媒介、消息模版

  • 告警规则配置

我们要在告警规则中配置告警条件，以及关联通知规则

实现效果

通过告警信息，可获取 MDL 锁的阻塞关系以及相关 SQL 信息，这有助于我们快速定位问题并及时止损

另外需明确的是：MDL 锁阻塞的原因可能是在高并发 DML 场景下，或者该表存在慢 SQL 时执行 DDL 操作，从而引发阻塞，进而使该 DDL 之后的语句也被阻塞。当前的抓取逻辑仅能抓取某一瞬间的 SQL。若 DDL 阻塞是由高并发导致的，告警发出时涉及的线程 id 可能已执行完毕。此处实现的效果可理解为采样，不过这仍能有效帮助我们定位问题 SQL

篇幅有限：关于 MDL 锁阻塞关系的分析只给出了 SQL ，有问题可以留言进一步交流


References

https://dev.mysql.com/doc/refman/8.0/en/sys-schema-table-lock-waits.html

https://n9e.github.io/zh/docs/usecase/alerting/

https://n9e.github.io/zh/docs/usecase/webhook/

https://help.aliyun.com/zh/das/user-guide/deadlock-analysis-1

https://dev.mysql.com/doc/refman/8.4/en/sys-processlist.html

https://dev.mysql.com/doc/refman/8.4/en/sys-processlist.html

【声明】内容源于网络
0
0
Kubernetes技术栈
聚焦云原生技术生态建设，涵盖DBA、SRE、DevOps、大数据、AI等领域的实践案例与前沿技术分享，欢迎关注交流。
内容 337
粉丝 0
Kubernetes技术栈 聚焦云原生技术生态建设，涵盖DBA、SRE、DevOps、大数据、AI等领域的实践案例与前沿技术分享，欢迎关注交流。
总阅读264
粉丝0
内容337