MySQL运维系列 之 如何监控大事务

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
云数据库 RDS MySQL,高可用系列 2核4GB
简介:

long transaction

背景

大家有没有遇到这样的情况

  1. 某个SQL执行特别慢,导致整个transaction一直处于running阶段
  2. 某个Session的SQL已经执行完了,但是迟迟没有commit,一直处于sleep阶段
  3. 某个Session处于lock wait阶段,迟迟没有结束

以上,大部分原因都是大事务导致的,接下来我们好好聊聊相关话题

关键字

  • 环境
1. MySQL5.7.22

    低版本MySQL这边不再考虑,就像还有使用SAS盘的公司一样,费时费力,MySQL5.7+ 标配

2. InnoDB存储引擎

3. CentOS 6
  • 大事务的相关特征
1. transaction开启到结束的时间非常长,我们这边举例为10s
2. 正在执行的事务
3. 未提交的事务

实战

  • 如何监控那些正在执行的事务
1. select * from sys.processlist
2. show processlist
3. select * from information_schema.processlist
4. select * from sys.session
5. select * from information_schema.innodb_trx;
6. select * from performance_schema.events_statements_current
  • 如何监控那些未提交的事务
select * from information_schema.innodb_trx
  • 如何两者结合
select trx_id,INNODB_TRX.trx_state,INNODB_TRX.trx_started,se.conn_id as processlist_id,trx_lock_memory_bytes,se.user,se.command,se.state,se.current_statement,se.last_statement from information_schema.INNODB_TRX,sys.session as se where trx_mysql_thread_id=conn_id;


+---------+-----------+---------------------+----------------+-----------------------+------+---------+----------+-----------------------------------+-----------------------------------+
| trx_id  | trx_state | trx_started         | processlist_id | trx_lock_memory_bytes | user | command | state    | current_statement                 | last_statement                    |
+---------+-----------+---------------------+----------------+-----------------------+------+---------+----------+-----------------------------------+-----------------------------------+
| 1592104 | LOCK WAIT | 2018-06-26 11:51:17 |              3 |                  1136 | NULL | Query   | updating | update lc_1 set id=4 where id = 1 | NULL                              |
| 1592100 | RUNNING   | 2018-06-26 11:49:08 |              2 |                  1136 | NULL | Sleep   | NULL     | NULL                              | update lc_1 set id=3 where id = 1 |
+---------+-----------+---------------------+----------------+-----------------------+------+---------+----------+-----------------------------------+-----------------------------------+

大家可以看到,通过这个可以立马发现事务语句处于running阶段 , 哪些事务处于lock wait阶段 , 如果遇到这种情况,我们应该如何处理呢?
聪明的你,一定会去根据trx_started去寻找蛛丝马迹,可是如果再生产环境中,这是一件非常复杂和繁忙的事情
不过没关系,我们还有神器可以使用

  • 如何快速解决锁等待问题
dba:sys> select * from sys.innodb_lock_waits\G
*************************** 1. row ***************************
                wait_started: 2018-06-26 11:49:58
                    wait_age: 00:00:03
               wait_age_secs: 3
                locked_table: `lc`.`lc_1`
                locked_index: GEN_CLUST_INDEX
                 locked_type: RECORD
              waiting_trx_id: 1592102
         waiting_trx_started: 2018-06-26 11:49:58
             waiting_trx_age: 00:00:03
     waiting_trx_rows_locked: 2
   waiting_trx_rows_modified: 0
                 waiting_pid: 3
               waiting_query: update lc_1 set id=4 where id = 1
             waiting_lock_id: 1592102:32:3:4
           waiting_lock_mode: X
             blocking_trx_id: 1592100
                blocking_pid: 2
              blocking_query: NULL
            blocking_lock_id: 1592100:32:3:4
          blocking_lock_mode: X
        blocking_trx_started: 2018-06-26 11:49:08
            blocking_trx_age: 00:00:53
    blocking_trx_rows_locked: 1
  blocking_trx_rows_modified: 1
     sql_kill_blocking_query: KILL QUERY 2
sql_kill_blocking_connection: KILL 2

MySQL最终非常贴心都连kill SQL 语句都生产了,你只需要复制、粘贴即可

细心的你会发现,通过innodb_lock_waits你只能看到被lock的语句,但是看不到是哪个query语句拥有的锁,这又是为什么呢?

不卖关子,因为拥有锁的事务中可能拥有多条query语句,也可能已经执行完,但是没有commit,所以无法给出所有query语句。

那怎么办呢?哈哈,如果幸运的话,你可以根据我上述的案例 current_statement,last_statement 得到答案。

再换句话说,即便没有找到那条query,也不妨碍你解决当前的问题哈

总结

  1. MySQL5.7 默默的提供了非常多的实用工具和新特性,需要DBA们去挖掘和探索。将看似平淡无奇的特性挖掘成黑武器,你才能成为那闪着光芒的Top5 MySQLer
  2. 工欲善其事必先利其器
相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
23天前
|
运维 监控 安全
构建高效运维体系:从监控到自动化的全方位实践
本文深入探讨了构建高效运维体系的关键要素,从监控、日志管理、自动化工具、容器化与微服务架构、持续集成与持续部署(CI/CD)、虚拟化与云计算以及安全与合规等方面进行了全面阐述。通过引入先进的技术和方法,结合实际案例和项目经验,为读者提供了一套完整的运维解决方案,旨在帮助企业提升运维效率,降低运营成本,确保业务稳定运行。
|
2月前
|
运维 Prometheus 监控
OceanBase 的运维与监控最佳实践
【8月更文第31天】随着分布式数据库解决方案的需求日益增长,OceanBase 作为一种高性能的分布式数据库系统,在众多场景下得到了广泛应用。为了确保 OceanBase 集群的稳定运行,合理的运维与监控是必不可少的。本文将探讨 OceanBase 的日常运维管理与监控策略,并提供相应的代码示例。
72 2
|
27天前
|
存储 Oracle 关系型数据库
Oracle和MySQL有哪些区别?从基本特性、技术选型、字段类型、事务、语句等角度详细对比Oracle和MySQL
从基本特性、技术选型、字段类型、事务提交方式、SQL语句、分页方法等方面对比Oracle和MySQL的区别。
Oracle和MySQL有哪些区别?从基本特性、技术选型、字段类型、事务、语句等角度详细对比Oracle和MySQL
|
1月前
|
SQL 关系型数据库 MySQL
MySQL基础:事务
本文详细介绍了数据库事务的概念及操作,包括事务的定义、开启、提交与回滚。事务作为一组不可分割的操作集合,确保了数据的一致性和完整性。文章还探讨了事务的四大特性(原子性、一致性、隔离性、持久性),并分析了并发事务可能引发的问题及其解决方案,如脏读、不可重复读和幻读。最后,详细讲解了不同事务隔离级别的特点和应用场景。
93 4
MySQL基础:事务
|
1月前
|
监控 关系型数据库 MySQL
zabbix agent集成percona监控MySQL的插件实战案例
这篇文章是关于如何使用Percona监控插件集成Zabbix agent来监控MySQL的实战案例。
32 2
zabbix agent集成percona监控MySQL的插件实战案例
|
1月前
|
存储 弹性计算 运维
自动化监控和响应ECS系统事件
阿里云提供的ECS系统事件用于记录云资源信息,如实例启停、到期通知等。为实现自动化运维,如故障处理与动态调度,可使用云助手插件`ecs-tool-event`。该插件定时获取并转化ECS事件为日志存储,便于监控与响应,无需额外开发,适用于大规模集群管理。详情及示例可见链接文档。
|
26天前
|
存储 运维 监控
构建高效运维体系:从监控到自动化的全方位实践指南
在当今数字化时代,企业对运维(Operations)的需求日益增长。运维不仅仅是保持系统运行那么简单,它涉及到监控、日志管理、故障排除、性能优化和自动化等多个层面。本文将从实际操作的角度出发,详细探讨如何构建一个高效的运维体系。通过具体案例,我们将了解不同运维工具和方法的应用,以及它们是如何帮助企业提高生产效率和降低运营风险的。无论你是刚接触运维的新手,还是经验丰富的专家,这篇文章都将为你提供宝贵的参考和启示。
|
1天前
|
SQL 关系型数据库 MySQL
【MySQL】索引和事务
【MySQL】索引和事务
10 0
|
8天前
|
运维 监控 安全
构建高效运维体系:从监控到自动化的实践之路
在当今信息技术飞速发展的时代,运维作为保障企业信息系统稳定运行的关键环节,其重要性日益凸显。本文将探讨如何通过构建高效的运维体系,实现从被动响应到主动预防的转变,以及如何利用自动化工具提升运维效率和质量。我们将从运维的基本概念出发,逐步深入到监控、自动化和安全管理等方面,为企业提供一套实用的运维优化方案。
25 0
|
11天前
|
SQL Oracle 关系型数据库
详解 MySQL 的事务以及隔离级别
详解 MySQL 的事务以及隔离级别
21 0