关于MySQL的commit非规律性失败案例的深入分析

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

案例描述:

  一个普通的事务提交,在应用里面会提示commit超时,失败。

一、理论知识

1、关于commit原理,事务提交过程

  1、寻找修改的数据页:

    1、如果该数据页在内存中,则直接是内存读;

    2、如果该数据页内存中没有,物理读,就从磁盘调入内存;

  2、磁盘中的undo页调入内存;

  3、先将原来的数据存入undo,然后修改数据(数据页成脏页);

  4、修改数据的信息生成redo数据存入log_buffer(内存buffer_pool的一个空间,默认16M)中;

mysql> show variables like '%log_buffer%';+------------------------+----------+| Variable_name          | Value    |+------------------------+----------+| innodb_log_buffer_size | 16777216 |+------------------------+----------+1 row in set (0.01 sec)

  5、log_buffer通过log线程(后台线程,非常勤快),持续不断的将redo信息写入disk的innodb_log_file中;

mysql> show variables like 'innodb_log_file%';+---------------------------+----------+| Variable_name             | Value    |+---------------------------+----------+| innodb_log_file_size      | 50331648 || innodb_log_files_in_group | 2        |+---------------------------+----------+2 rows in set (0.01 sec)

  6、事务提交,刻意触发log线程,将剩余的log_buffer中的redo数据信息写入磁盘中,数据量已剩不多,写完提交成功。

注意:

  1、修改记录前,一定要先写日志;

    “日志先行”,这是数据库最基本的原则。

  2、事务提交过程中,一定要保证日志先落盘,才能算事务提交完成。

  3、意外掉电,内存脏页丢失,但是磁盘的innodb_log_file中存放了redo日志信息,待重启服务器,MySQL通过读取磁盘的log_files数据,自动将数据的修改重新跑一边。

Q:为什么mysql commit速度总是很快,尽管事务修改的数据量可能很大?

A:

  因为事务提交,并不是对磁盘数据进行修改,而是将修改数据的redo信息通过后台log线程写入磁盘的redo logfile中,完成mysql commit,无论事务修改的数据量有多大,这个过程速度是很快的。

  而内存中的脏块,也就是修改后的数据页,正常情况下是由后台相关write线程周期性的将脏页数据刷入磁盘中,保证innodb buffer pool有足够的干净块、可用块。

2、关于rollback原理,回滚过程

  1、MySQL读取内存中undo页信息

  2、通过undo信息找到脏页,反着对数据进行修改

  3、do、undo的时间相同,且都会产成redo信息

  4、事务提交

MySQL回滚处理机制:

  如果线程中断,事务没有提交,undo会将记录此信息,待另一会话进程连上,查看该块数据信息,MySQL自动回滚进行数据页修改,然后被读取。也就是说为了避免系统因为rollback被hang住,通过直接杀死进程的方式,中断事务,等待后来者要读取该数据信息时进行回滚,再返回结果。

Q:rollback为什么有时候很慢,rollback的风险和风险避免方式?

A:

  rollback的时间取决于回滚前事务修改数据的时间,处理量大回滚时间长,处理量小回滚时间短。

  1、rollback风险:容易导致系统被hang住;

  2、风险避免方式:直接杀死会话进程或是mysql进程。

3、存储写入性能分析

Q:mysql commit,存储为什么写速度能够保持在0ms,极少出现1ms情况?

A:

  对于存储来说,写性能相当高:假设存储cache总有空闲空间的情况下,事务提交,将log buffer中剩余的很少的redo数据写入存储cache,即为完成mysql commit,这个过程是相当快的(能够保持在0ms,极少出现1ms情况),后续redo数据由cache写入磁盘的过程是后台进行。

4、存储级别的灾备(同城灾备)

  1、灾备同步过程:commit

    1、redo、binlog写入本地存储cache;

    2、通过网络同步binlog写入远端同步的服务器的存储cache中;

    3、响应本地数据库;

    4、事务提交成功;

  2、风险:

    网络出现问题(信号断续,缆线断了),导致写hang住,commit超时失败。

  3、解决:

    通过超时设置,网络中断超过限制,自动将同步改为灾备异步,尽可能少的影响业务commit超时失败。

 

二、分析与处理

  存储写性能比较差,很多时段会达到5ms,甚至于10ms以上

    备注:灾备同步已经停止的情况下。

1、存储中BBU问题,出现监控BBU的bug;

  解决:重启BBU,不行就更新BBU。

2、cache被占满

  1、海量数据写入,commit数据占满cache;

  2、硬盘I/O异常,异常SQL导致的海量物理读;

  解决:索引优化。

3、存储性能差

  解决:找老板掏钱,更换优质设备。


本文转自 sshpp 51CTO博客,原文链接:http://blog.51cto.com/12902932/1949356,如需转载请自行联系原作者
相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
7天前
|
存储 SQL 关系型数据库
服务器数据恢复—云服务器上mysql数据库数据恢复案例
某ECS网站服务器,linux操作系统+mysql数据库。mysql数据库采用innodb作为默认存储引擎。 在执行数据库版本更新测试时,操作人员误误将在本来应该在测试库执行的sql脚本在生产库上执行,导致生产库上部分表被truncate,还有部分表中少量数据被delete。
47 25
|
8天前
|
SQL 关系型数据库 MySQL
MySQL原理简介—11.优化案例介绍
本文介绍了四个SQL性能优化案例,涵盖不同场景下的问题分析与解决方案: 1. 禁止或改写SQL避免自动半连接优化。 2. 指定索引避免按聚簇索引全表扫描大表。 3. 按聚簇索引扫描小表减少回表次数。 4. 避免产生长事务长时间执行。
|
10天前
|
关系型数据库 MySQL Linux
MySQL原理简介—6.简单的生产优化案例
本文介绍了数据库和存储系统的几个主题: 1. **MySQL日志的顺序写和数据文件的随机读指标**:解释了磁盘随机读和顺序写的原理及对数据库性能的影响。 2. **Linux存储系统软件层原理及IO调度优化原理**:解析了Linux存储系统的分层架构,包括VFS、Page Cache、IO调度等,并推荐使用deadline算法优化IO调度。 3. **数据库服务器使用的RAID存储架构**:介绍了RAID技术的基本概念及其如何通过多磁盘阵列提高存储容量和数据冗余性。 4. **数据库Too many connections故障定位**:分析了MySQL连接数限制问题的原因及解决方法。
|
10天前
|
缓存 NoSQL 关系型数据库
MySQL原理简介—4.深入分析Buffer Pool
本文介绍了MySQL的Buffer Pool机制,包括其作用、配置方法及内部结构。Buffer Pool是MySQL用于缓存磁盘数据页的关键组件,能显著提升数据库读写性能。默认大小为128MB,可根据服务器配置调整(如32GB内存可设为2GB)。它通过free链表管理空闲缓存页,flush链表记录脏页,并用LRU链表区分冷热数据以优化淘汰策略。此外,还探讨了多Buffer Pool实例、chunk动态调整等优化并发性能的方法,以及如何通过`show engine innodb status`查看Buffer Pool状态。关键词:MySQL内存数据更新机制。
|
29天前
|
关系型数据库 MySQL 数据库
mysql慢查询每日汇报与分析
通过启用慢查询日志、提取和分析慢查询日志,可以有效识别和优化数据库中的性能瓶颈。结合适当的自动化工具和优化措施,可以显著提高MySQL数据库的性能和稳定性。希望本文的详解和示例能够为数据库管理人员提供有价值的参考,帮助实现高效的数据库管理。
40 11
|
1月前
|
SQL 关系型数据库 MySQL
MySQL事务日志-Undo Log工作原理分析
事务的持久性是交由Redo Log来保证,原子性则是交由Undo Log来保证。如果事务中的SQL执行到一半出现错误,需要把前面已经执行过的SQL撤销以达到原子性的目的,这个过程也叫做"回滚",所以Undo Log也叫回滚日志。
MySQL事务日志-Undo Log工作原理分析
|
2月前
|
存储 关系型数据库 MySQL
10个案例告诉你mysql不使用子查询的原因
大家好,我是V哥。上周与朋友讨论数据库子查询问题,深受启发。为此,我整理了10个案例,详细说明如何通过优化子查询提升MySQL性能。主要问题包括性能瓶颈、索引失效、查询优化器复杂度及数据传输开销等。解决方案涵盖使用EXISTS、JOIN、IN操作符、窗口函数、临时表及索引优化等。希望通过这些案例,帮助大家在实际开发中选择更高效的查询方式,提升系统性能。关注V哥,一起探讨技术,欢迎点赞支持!
194 5
|
2月前
|
SQL 关系型数据库 MySQL
MySQL 窗口函数详解:分析性查询的强大工具
MySQL 窗口函数从 8.0 版本开始支持,提供了一种灵活的方式处理 SQL 查询中的数据。无需分组即可对行集进行分析,常用于计算排名、累计和、移动平均值等。基本语法包括 `function_name([arguments]) OVER ([PARTITION BY columns] [ORDER BY columns] [frame_clause])`,常见函数有 `ROW_NUMBER()`, `RANK()`, `DENSE_RANK()`, `SUM()`, `AVG()` 等。窗口框架定义了计算聚合值时应包含的行。适用于复杂数据操作和分析报告。
132 11
|
2月前
|
关系型数据库 MySQL 数据库
数据库数据恢复—MYSQL数据库文件损坏的数据恢复案例
mysql数据库文件ibdata1、MYI、MYD损坏。 故障表现:1、数据库无法进行查询等操作;2、使用mysqlcheck和myisamchk无法修复数据库。
|
4月前
|
关系型数据库 MySQL 数据库
一个 MySQL 数据库死锁的案例和解决方案
本文介绍了一个 MySQL 数据库死锁的案例和解决方案。
332 3

热门文章

最新文章