MySQLwwwhj8828com18088045700锁分析和监控

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
云数据库 RDS PostgreSQL,高可用系列 2核4GB
简介: 通常在MySQL的管理和监控中,Active Session(活动会话)是监控指标中的一个很重要的指标,通过活动会话监控,可以很清楚的了解到数据库当前是否有SQL堆积,是否处于非常繁忙的状态。那么除了活动会话之外,还有哪些指标是非常重要的呢,本文就来给大家介绍下MySQL里面另外几个重要指标,事务和锁信息,锁等待的监控。

通常在MySQL的管理和监控中,Active Session(活动会话)是监控指标中的一个很重要的指标,通过活动会话监控,可以很清楚的了解到数据库当前是否有SQL堆积,是否处于非常繁忙的状态。那么除了活动会话之外,还有哪些指标是非常重要的呢,本文就来给大家介绍下MySQL里面另外几个重要指标,事务和锁信息,锁等待的监控。

我们知道事务和锁是数据库中最最核心的内容,有了事务和锁,才保证了数据的ACID特性,上面说到的活动会话监控,可以反映出数据库的一个健康状态,但是如果监控到事务和锁,那么会对数据库的运行状态有更加全面的认识,在数据库出现异常时也可以很快定位到一些问题。比如业务设计开发同学开启了事务但是忘了提交,或者事务提交时间过长,都会导致一些数据库的问题产生,严重时会数据库故障。下面就如何查看和监控事务、锁信息做个简单介绍。

大多数时候我们通过执行show engine innodb status来查看和监控数据库的锁信息,其实还有更简单的方式,MySQL将事务和锁信息记录在了information_schema数据库中,我们只需要查询即可。
INNODB_TRX
记录INNODB未提交事务信息
INNODB_LOCKS
记录INNODB锁信息,当出现锁等待时才有数据
INNODB_LOCK_WAITS
记录锁等待信息,关联INNODB_LOCKS查询。

我们通过实例分析来说明如何监控事务和锁,首先开启事务T1,执行update:

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update t1 set name='xxxx' where id=10;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

然后查询INNODB_TRX表,可以看到如下信息,表示有1条事务当然没有提交,这个事务就是上面T1没有提交的事务。
mysql> use information_schema
mysql>SELECT * FROM INNODB_TRXG
1. row **

                trx_id: 36076063  (事务ID)
             trx_state: RUNNING (事务正在运行)
           trx_started: 2018-09-08 22:35:32(事务开始时间)
 trx_requested_lock_id: NULL
      trx_wait_started: NULL
            trx_weight: 3
   trx_mysql_thread_id: 882965 (MySQL线程ID)
             trx_query: NULL (执行的SQL语句)
   trx_operation_state: NULL
     trx_tables_in_use: 0
     trx_tables_locked: 0
      trx_lock_structs: 2
 trx_lock_memory_bytes: 360
       trx_rows_locked: 1 (锁定了1行索引记录)
     trx_rows_modified: 1

trx_concurrency_tickets: 0

   trx_isolation_level: READ COMMITTED (当前事务隔离级别)
     trx_unique_checks: 1 (唯一性检测,因为是UK锁)
trx_foreign_key_checks: 1 (外键检测)

trx_last_foreign_key_error:NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 10000

      trx_is_read_only: 0

trx_autocommit_non_locking:0
1 row inset (0.00 sec)

然后我们开启事务T2:
mysql>begin;
QueryOK, 0 rows affected (0.00 sec)
mysql> select * from t1 where id<=4 lock in share mode;
ERROR1205 (HY000): Lock wait timeout exceeded; try restarting transaction

在事务T2执行过程中我们来监控锁信息,首先来查询INNODB_LOCK_WAITS数据表,可以看到上面T1,T2两个事务已经产生了锁等待。
mysql>SELECT * FROM INNODB_LOCK_WAITSG
1. row **
requesting_trx_id:36076064 (T2请求的事务ID)
requested_lock_id:36076064:69:3:5 (T2请求的锁ID)
blocking_trx_id: 36076063 (T1阻塞的事务ID)
blocking_lock_id: 36076063:69:3:5 (T1阻塞的锁ID)
1 row inset (0.00 sec)

上面我们已经知道了事务T2在执行过程中被事务T1的锁阻塞住了,然后我们就可以通过查询INNODB_LOCKS查询看到的锁详细信息,具体如下所示,可以看到上面的事务T1(36076063)对t1表加了X模式的PK锁,锁类型为Record Lock,锁定了1行数据,锁定的位置为69表空间的第3个页面的第5行记录,锁定记录为10,因为是PK更新,所以这里的lock_data: 10就是id=10的这行记录的PK被加锁了。再来看事务T2(36076064),在请求id=10这个锁的时候无法获取到锁,导致了锁等待。
mysql>SELECT * FROM INNODB_LOCKSG
1. row **

lock_id: 36076064:69:3:5 (锁ID)

lock_trx_id:36076064 (事务ID)
lock_mode: S (锁模式)
lock_type: RECORD (锁类型)
lock_table: test.t1 (锁了哪个表)
lock_index: PRIMARY (锁定的索引类型)
lock_space: 69 (表空间位置)
lock_page: 3 (页位置)
lock_rec: 5 (记录位置)
lock_data: 10 (哪个数据被锁了,如果是PK,这个值就是PK值)
2. row **

lock_id: 36076063:69:3:5

lock_trx_id:36076063
lock_mode: X
lock_type: RECORD
lock_table: test.t1
lock_index: PRIMARY
lock_space: 69
lock_page: 3
lock_rec: 5
lock_data: 10
2 rowsin set (0.00 sec)

现在我们知道了如何定位和查询没有提交的事务,以及锁等待信息,只需要将上面的SQL定时采集告警即可很容易的实现事务和锁的监控了。最近我自己也写了一个demo,通过上面三个SQL监控了事务和锁的信息。

可以看到上面监控里面有大于0的数值,说明有锁等待现象,然后点击小圆点,即可以定位到相关锁信息,是不是更方便了。

相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
存储 API C++
【Qt 信号槽】深入探索 Qt 信号和槽机制中的引用传递“ (“A Deep Dive into Reference Passing in Qt Signal and Slot Mechanism“)
【Qt 信号槽】深入探索 Qt 信号和槽机制中的引用传递“ (“A Deep Dive into Reference Passing in Qt Signal and Slot Mechanism“)
946 0
|
人工智能 计算机视觉
Photoshop2023新版本win11系统安装下载教程
ps迎来了2023的版本,这次的版本提升针对windows11做了特别优化,启动速度比win10快了很多。期盼已久的Win版 PS 2023 终于来了,小编也是通过特殊渠道搞来的,本期带来的WIN版本支持一键安装激活,一次安装永久免费使用众所周知,版本越高,需要的电脑配置也就越来越高。下面放一下2023版本的配置供大家参考。需要注意的是这些版本不再支持windows7系统,仅支持win10及以上的操作系统。
2071 0
|
JavaScript 前端开发 小程序
基于springBoot + Vue电影售票系统分前后台【完整源码+数据库】
基于springBoot + Vue电影售票系统分前后台【完整源码+数据库】
185 4
|
存储 监控 安全
Pikachu PHP 反序列化通关解析
Pikachu PHP 反序列化通关解析
|
设计模式 安全 Java
|
10月前
|
存储 Java 编译器
String能存储多少个字符
String能存储多少个字符
|
11月前
|
SQL 数据库连接 API
SqlAlchemy 2.0 中文文档(二十八)(1)
SqlAlchemy 2.0 中文文档(二十八)
213 2
|
存储 网络协议 安全
C/C++网络编程基础知识超详细讲解第一部分(系统性学习day11)
C/C++网络编程基础知识超详细讲解第一部分(系统性学习day11)
|
Rust 编译器 Linux
Rust编译过程讲解与开发环境准备
目前主流编译平台有,GNU、MSVC、LLVM。因为rustc调用了llvm,因此我们以LLVM为例,我们从C语言的编译过程聊,再对比Rust,看它们的编译过程有何差异。
333 3