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

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,高可用系列 2核4GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介:

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. 工欲善其事必先利其器
相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
11天前
|
存储 SQL 关系型数据库
mysql底层原理:索引、慢查询、 sql优化、事务、隔离级别、MVCC、redolog、undolog(图解+秒懂+史上最全)
mysql底层原理:索引、慢查询、 sql优化、事务、隔离级别、MVCC、redolog、undolog(图解+秒懂+史上最全)
mysql底层原理:索引、慢查询、 sql优化、事务、隔离级别、MVCC、redolog、undolog(图解+秒懂+史上最全)
|
1月前
|
人工智能 运维 关系型数据库
数据库运维:mysql 数据库迁移方法-mysqldump
本文介绍了MySQL数据库迁移的方法与技巧,重点探讨了数据量大小对迁移方式的影响。对于10GB以下的小型数据库,推荐使用mysqldump进行逻辑导出和source导入;10GB以上可考虑mydumper与myloader工具;100GB以上则建议物理迁移。文中还提供了统计数据库及表空间大小的SQL语句,并讲解了如何使用mysqldump导出存储过程、函数和数据结构。通过结合实际应用场景选择合适的工具与方法,可实现高效的数据迁移。
248 1
|
5天前
|
安全 关系型数据库 MySQL
mysql事务隔离级别
事务隔离级别用于解决脏读、不可重复读和幻读问题。不同级别在安全与性能间权衡,如SERIALIZABLE最安全但性能差,READ_UNCOMMITTED性能高但易导致数据不一致。了解各级别特性有助于合理选择以平衡并发性与数据一致性需求。
|
3月前
|
消息中间件 运维 监控
智能运维,由你定义:SAE自定义日志与监控解决方案
通过引入 Sidecar 容器的技术,SAE 为用户提供了更强大的自定义日志与监控解决方案,帮助用户轻松实现日志采集、监控指标收集等功能。未来,SAE 将会支持 istio 多租场景,帮助用户更高效地部署和管理服务网格。
338 52
|
2月前
|
运维 监控 中间件
Linux运维笔记 - 如何使用WGCLOUD监控交换机的流量
WGCLOUD是一款开源免费的通用主机监控工具,安装使用都非常简单,它可以监控主机、服务器的cpu、内存、磁盘、流量等数据,也可以监控数据库、中间件、网络设备
|
4月前
|
数据采集 运维 监控
数据采集监控与告警:错误重试、日志分析与自动化运维
本文探讨了数据采集技术从“简单采集”到自动化运维的演进。传统方式因反爬策略和网络波动常导致数据丢失,而引入错误重试、日志分析与自动化告警机制可显著提升系统稳定性与时效性。正方强调健全监控体系的重要性,反方则担忧复杂化带来的成本与安全风险。未来,结合AI与大数据技术,数据采集将向智能化、全自动方向发展,实现动态调整与智能识别反爬策略,降低人工干预需求。附带的Python示例展示了如何通过代理IP、重试策略及日志记录实现高效的数据采集程序。
204 7
数据采集监控与告警:错误重试、日志分析与自动化运维
|
8月前
|
Prometheus 运维 监控
智能运维实战:Prometheus与Grafana的监控与告警体系
【10月更文挑战第26天】Prometheus与Grafana是智能运维中的强大组合,前者是开源的系统监控和警报工具,后者是数据可视化平台。Prometheus具备时间序列数据库、多维数据模型、PromQL查询语言等特性,而Grafana支持多数据源、丰富的可视化选项和告警功能。两者结合可实现实时监控、灵活告警和高度定制化的仪表板,广泛应用于服务器、应用和数据库的监控。
786 3
|
7月前
|
SQL 安全 关系型数据库
【MySQL基础篇】事务(事务操作、事务四大特性、并发事务问题、事务隔离级别)
事务是MySQL中一组不可分割的操作集合,确保所有操作要么全部成功,要么全部失败。本文利用SQL演示并总结了事务操作、事务四大特性、并发事务问题、事务隔离级别。
3409 56
【MySQL基础篇】事务(事务操作、事务四大特性、并发事务问题、事务隔离级别)
|
4月前
|
消息中间件 运维 监控
智能运维,由你定义:SAE自定义日志与监控解决方案
SAE(Serverless应用引擎)是阿里云推出的全托管PaaS平台,致力于简化微服务应用开发与管理。为满足用户对可观测性和运维能力的更高需求,SAE引入Sidecar容器技术,实现日志采集、监控指标收集等功能扩展,且无需修改主应用代码。通过共享资源模式和独立资源模式,SAE平衡了资源灵活性与隔离性。同时,提供全链路运维能力,确保应用稳定性。未来,SAE将持续优化,支持更多场景,助力用户高效用云。
|
6月前
|
监控 运维
HTTPS 证书自动化运维:https证书管理系统- 自动化监控
本文介绍如何设置和查看域名或证书监控。步骤1:根据证书状态选择新增域名或证书监控,线上部署推荐域名监控,未部署选择证书监控。步骤2:查询监控记录详情。步骤3:在详情页查看每日定时检测结果或手动测试。
HTTPS 证书自动化运维:https证书管理系统- 自动化监控

推荐镜像

更多