网站关键词放哪,大连天健网大连,苏州在线网站制作,做电商网站需要会些什么事务隔离性由锁来实现。原子性、一致性、持久性通过数据库的 redo log 和 undo log 来完成。
redo log 称为重做日志#xff0c;用来保证事务的原子性和持久性。undo log 用来保证事务的一致性。
有的 DBA 或许会认为 undo 是 redo 的逆过程#xff0c;其实不然。redo 和 u…事务隔离性由锁来实现。原子性、一致性、持久性通过数据库的 redo log 和 undo log 来完成。
redo log 称为重做日志用来保证事务的原子性和持久性。undo log 用来保证事务的一致性。
有的 DBA 或许会认为 undo 是 redo 的逆过程其实不然。redo 和 undo 的作用都可以视为是一种恢复操作redo 恢复提交事务修改的页操作而 undo 回滚行记录到某个特定版本。
因此两者记录的内容不同redo 通常是物理日志记录的是页的物理修改操作。undo 是逻辑日志根据每行记录进行记录。 注物理日志是指数据页的变更逻辑日志是指记录的是 SQL 语句
redo log redo log 的引入
1硬盘知识回顾 机械硬盘机械硬盘是将数据写入扇区一个扇区 512b那么写入数据都会有一个寻址的过程就是寻找扇区机械硬盘写入数据时需要经过1、定位到磁道2、等待旋转到对应扇区3、开始读写。步骤 1 和 2 机械定位速度慢。 固态硬盘固态硬盘将数据写入闪存芯片使用电子定位所以比机械硬盘快很多。【拓展对于拷贝数据是连续的扇区写入数据一次性申请连续的空间固态硬盘比机械硬盘快 3 到 5 倍。对于随机读写固态硬盘比机械硬盘快 100-300 倍而我们大多数的场景都是随机读写】 2文件系统
我们一般不会直接与硬盘打交道都是与文件系统打交道。文件系统将硬盘划分为一个一个的 block1block 8 扇区 4kb一个block 是由多个连续的扇区组成这样就大大减少了扇区寻址的时间但是这样会存在 block 写不满的情况这就是磁盘碎片。 文件系统磁盘读写模式 顺序读写顺序读写分配的是连续的 block所以读写性能高但是需要满足两个条件1、文件大小固定2、预分配磁盘空间。 随机读写随机读写分配的 block 不是连续的磁头需要频繁切换读写性能比较低。 文件系统磁盘读写规则文件系统实际不是直接将数据读写到磁盘中它会先将数据写入 buffer buffer 按照策略将数据刷新到磁盘读取数据时先读 cachecache 读不到再去读磁盘。【拓展如果系统宕机、断电使用 Linux 提供的 fsync 可以直接将数据刷新到磁盘】 3为什么要用 redo log
数据库将数据直接写入磁盘使用的是随机读写模式这种模式性能比较差会造成数据库读写瓶颈。所以数据库就想先将数据写入内存缓存池然后再定时从内存刷到磁盘这样就快很多。
问题来了一旦发生故障内存中的数据会全部丢失那么要如何保证数据的一致性呢此时就有了 redo log。 redo log 的模式叫做 WAL 技术也就是写前日志write ahead logredo log 在硬盘中是一个大小固定扇区连续的模块这样 redo log 就可以实现顺序写入比随机写入快很多。 问什么是WAL呢
WAL即 Write Ahead LogWAL的核心思想是每次更新操作都先写入日志只做了写日志这一个磁盘操作。这个日志叫做redo log重做日志也就是《孔乙己》记账的粉板在更新完内存写完redo log后就返回给客户端本次更新成功。而实际更新操作是由后台线程根据redo log异步写入。因此对Client来说延迟会减少。同时顺序写入的可能性很大如果更新的多条记录位于同一Page则磁盘IO次数也能大大降低。
所以WAL机制主要得益于三个方面 减少更新数据的磁盘IO。 redo log 和 binlog都是顺序写 磁盘的顺序写比随机写速度要快很多。 组提交机制 可以大幅度降低磁盘的IOPS消耗。 MySQL 的数据在写入内存的时候同时将数据写入到 redo log 中这样当内存数据丢失的时候以 redo log 数据为准恢复数据则可保证数据一致性。redo log 保存的每一条数据都是记录事务对哪些数据页做了哪些修改。【拓展Innodb 以页为单位进行磁盘交互一页大小 16 KB一页中至少保存两条数据也就是说当你更新某个页中的一条数据中的一个字段时它都需要整页更新而且如果事务修改了很多页页之间的 block 不是连续的磁盘还要不停的寻址。IO 大不大速度慢不慢所以有了 redo log】
redo log 的概念
重做日志用来实现事务的持久性即事务的 ACID 中的 D。其由两部分组成一是内存中的重做日志缓冲redo log buffer其是易失的二是重做日志文件redo log file其是持久的。 InnoDB 是事务的存储引擎其通过 Force Log at Commit 机制实现事务的持久性即当事务提交commit时必须先将该事务的所有日志写到重做日志进行持久化待事务的 commit 操作完成才算完成。 这里的日志是指重做日志在 InnoDB 存储引擎中由两部分组成即 redo log 和 undo log。redo log 用来保证事务的持久性undo log 用来帮助事务回滚及 MVCC 功能。redo log 基本上都是顺序写的在数据库运行时不需要对 redo log 的文件进行读取操作。而 undo log 是需要进行随机读写的。 为了确保每次日志都写入重做日志文件在每次将重做日志缓冲写入重做日志文件后InnoDB 存储引擎都需要调用一次 fsync 操作fsync 指异步刷新redo log。 注意InnoDB 存储引擎允许用户手工设置非持久性的情况发生以此来提高数据库的性能。即当事务提交时日志不写入重做日志文件而是等待一个时间周期后再次执行 fsync 操作。但是当数据库发生宕机时由于部分日志未刷新到磁盘因此会丢失最后一段时间的事务。 参数 innodb_flush_log_at_trx_commit 用来控制重做日志刷新到磁盘的策略参数取值及含义如下 innodb_flush_log_at_trx_commit 0 日志写入日志在每次事务提交时都写入redo log buffer但并不写入磁盘。 刷新时机通过Master Thread线程异步刷新到磁盘通常是每秒。 性能由于减少了磁盘I/O操作这个设置通常可以提供最好的性能。 数据持久性风险断点数据丢失。 innodb_flush_log_at_trx_commit 1 日志写入每次事务提交时事务日志都会写入日志缓冲区并立即写入重做日志文件同时Master Thread线程也会每秒异步刷新redo log。 刷新时机每次事务提交都会触发日志刷新到磁盘。 性能由于每次事务提交都涉及到磁盘 I/O 操作当有大量小事务时会降低性能。 数据持久性断电数据不丢失。 innodb_flush_log_at_trx_commit 2 日志写入日志在每次事务提交时都写入redo log buffer并调用flush函数把redo log buffer中的数据刷新到磁盘的缓冲区中并没有刷新到磁盘且不等flush操作完成就返回给客户端事务提交成功的响应。 刷新时机提交两个事务刷新一次磁盘同时Master Thread线程也会每秒异步刷新到磁盘。 性能这个设置在性能和持久性之间取得了一个折中。它减少了磁盘I/O操作的频率但仍然在每次事务提交时保持数据的安全。 数据持久性风险当 MySQL 数据库发生宕机而操作系统不发生宕机时并不会导致事务的丢失而当操作系统宕机时重启数据库后会丢失未从文件系统缓存刷新到重做日志文件的那部分数据。 问1flush操作和fsync操作的区别
答flush操作可能只是将数据从一种缓存转移到另一种缓存如从内存缓冲区到内核缓冲区而fsync操作则确保了数据从内核缓冲区到磁盘的同步。 问2将innodb_flush_log_at_trx_commit参数分别设置不同的值然后向数据库中插入50万条记录其插入速度如何
答三种值对应插入数据如下 虽然用户可以通过把innodb_flush_log_at_trx_commit设置为0或2来提高事务提交的性能但这种设置方法丧失了事务的ACID特性。 问3什么是binlogbinlog和redo log的区别是什么
答在MySQL数据库中还有一种二进制日志binlog其用来进行POINT-IN-TIMEPIT的恢复及主从复制Replication。
binlog和redo log的区别 重做日志是在InnoDB引擎层产生而二进制日志是在MySQL数据库上层产生的。 重做日志是全局的binlog是线程私有的。 两种日志记录的内容形式不同。二进制日志是一种逻辑日志其记录的是对应的SQL语句。而重做日志是物理日志其记录的是对于每个页的修改。 两种日志记录写入磁盘的时间点也不同如下图所示。二进制日志只在事务提交完成后进行一次写入。而重做日志在事务进行中不断地被写入即重做日志并不是随着事务提交的顺序进行写入的。 从上图可知二进制日志仅在事务提交时记录并且对于每一个事务仅包含对应事务的一个日志。
而对于InnoDB存储引擎的重做日志由于记录的是物理操作日志因此每个事务对应多个条目并且事务的重做日志写入是并发的并非在事务提交时写入故其在文件中记录的顺序并非是事务开始的顺序。*T1、*T2、*T3表示的是事务提交时的日志。
总结binlog日志以事务为粒度而重做日志redo log的粒度更细。PolarDB Serverless实现主要是使用redo log和undo log 注意 在事务开始时redo log会被写到内存中的redo log buffer。此时redo log状态为prepare状态表示事务已经开始但尚未提交。 在事务提交前redo log的状态会从prepare变为commit。表示在提交事务之前redo log中的记录已经准备好写入磁盘但实际写入磁盘的操作会在提交事务后进行。 redo log 的结构
在InnoDB存储引擎中重做日志都是以512字节进行存储的。这意味着重做日志缓存、重做日志文件都是以块block的方式进行保存的称之为重做日志块redo log block每块的大小为512字节。 若一个页中产生的重做日志数量大于512字节则需要分割为多个重做日志块进行存储。此外由于重做日志块的大小和磁盘扇区大小一样都是512字节因此重做日志的写入可以保证原子性不需要doublewrite技术。 重做日志块除了日志本身之外还有日志块头log block header及日志块尾log block tailer两部分组成。重做日志头一共占用12字节重做日志尾占用8字节。故每个重做日志块实际可以存储的大小为492字节512-12-8。
重做日志块缓存结构 log block header由4部分组成redo log buffer是由log block组成的在内部redo log buffer就好似一个数组 名称 占用字节 作用 LOG_BLOCK_HDR_NO 4 标记数组中的位置 LOG_BLOCK_HDR_DATA_LEN 2 标识log block所占用的大小当log block被写满时该值为0x200 LOG_BLOCK_FIRST_REC_GROUP 2 表示log block中第一个日志所在的偏移量 LOG_BLOCK_CHECKPOINT_NO 4 表示该log block最后被写入时的检查点第4字节的值
问如果LOG_BLOCK_FIRST_REC_GROUP的值等于LOG_BLOCK_HDR_DATA_LEN的值则意味着什么
答如果二者相等则意味着log block不包含新的日志。如果事务T1的重做日志1占762字节事务T2的重做日志占100字节。由于log block实际只能保存492字节因此其在log buffer中的情况如下图所示。 从上图可以观察到由于事务T1的重做日志1占762字节因此需要占用两个log block。第一个log block中 LOG _BLOCK _FIRST_REC_GROUP的值为12log block中第一个日志的开始位置。在第二个log block中由于包含了之前事务T1的重做日志事务T2的日志才是log block中第一个日志因此该log block的LOG_ BLOCK _ FIRST _REC_GROUP为28227012。
log blick tailer组成部分 名称 占用字节 作用 LOG_BLOCK_TRL_NO 4 其值和LOG_BLOCK_HDR_NO相同 redo log 的格式
由于InnoDB存储引擎的存储管理是基于页的故其重做日志格式也是基于页的。虽然有着不同的重做日志格式但是它们有着通用的头部格式如图所示。 通用的头部格式由以下3部分组成 redo_log_type重做日志类型。 space表空间的ID。 page_no页的偏移量。 redo log body部分根据重做日志类型的不同会有不同的存储内容。例如对于页上记录的插入和删除操作分别对应下图所示的格式 到InnoDB1.2版本时一共有51种重做日志类型。
redo log 的 LSN
LSN是Log Sequence Number的缩写其代表的是日志序列号。在InnoDB存储引擎中LSN占用8字节并且单调递增。LSN表示的含义如下 重做日志写入的总量。 checkpoint的位置。 页的版本。 LSN表示事务写入重做日志的字节总量。
例如当前重做日志的LSN为1000有一个事务T1写入了100字节的重做日志那么LSN就变为了1100若又有事务T2写入了200字节的重做日志那么LSN就变为了1300。 注意LSN不仅记录在重做日志中还存在于每个页中。
在每个页的头部有一个值FIL_PAGE_LSN记录了该页的LSN。在页中LSN表示该页最后刷新时LSN的大小。因为重做日志记录的是每个页的日志因此页中的LSN用来判断是否需要进行恢复操作。
例如页P1的LSN为10000而数据库启动时InnoDB检测到写入重做日志中的LSN为13000并且事务已经提交那么数据库需要进行恢复操作将重做日志应用到P1页中。同样的如果重做日志中LSN小于P1页中的LSN则不要进行重做因为P1页中的LSN表示页已经被刷新到该位置。 问用户如何查看LSN的情况
答用户可以通过 show engine innodb status 命令查看LSN的情况。示例如下 参数说明 Log sequence number表示当前的LSN。随着数据库操作的进行而不断增加。 Log flushed up to表示刷新到重做日志文件的LSN。当redo log buffer中的内容被刷新到磁盘上的redo log文件时这个值会更新为最新刷到磁盘的LSN。保证了在数据库恢复时可以从这个位置开始重放redo log。 Last checkpoint at表示最后一次刷新到磁盘的LSN即页中的LSN。Checkpoint是一个将缓存池中的脏页刷回磁盘的过程当Checkpoint发生时会记录此时的LSN值。 在实际生产环境中Log sequence number和Log flushed up to的值可能是不同的。因为在一个事务中从日志缓冲刷新到重做日志文件并不只是在事务提交时发生每秒都会有从日志缓冲刷新到重做日志文件的动作。
在生产环境下重做日志的信息示例 从图中可知Log sequence number、Log flushed up to、Last checkpoint at的值都不相同。
redo log 的恢复
InnoDB存储引擎在启动时不管上次数据库运行时是否正常关闭都会尝试进行恢复操作。
因为从重做日志记录的是物理日志因此恢复的速度比逻辑日志如二进制日志binlog要快很多。
由于checkpoint表示已经刷新到磁盘页上的LSN因此在恢复过程中仅需要恢复checkpoint开始的日志部分。 对于下图中的例子当数据库在checkpoint的LSN为10000时发生宕机恢复操作仅恢复LSN10000~13000范围的日志。 问1redo log具体记录的是什么
答redo log记录的是每个页上的变化。假设有如下表结构
create table t (a int, b int, primary key(1), key(b)); -- 执行插入语句 insert into t select 1, 2;
由于需要对聚集索引页和二级索引页进行操作其记录的重做日志大致为
page(2, 3), offset 32, value 1, 2 # 聚集索引 page(2, 4), offset 64, value 2 # 二级索引
可以看到记录的是页的物理修改操作若插入涉及B数的split可能会有更多的页需要记录日志。 问2数据库中的幂等是指什么
答幂等概念f(f(x))f(x)
换句话说幂等操作无论被调用多少次其结果都与单次调用的结果相同不会产生重复结果。
错误认知只要将二进制日志binlog的格式设置为ROW那么二进制日志也是幂等的。
例如insert操作在二进制日志中就不是幂等的重复执行可能会插入多条重复的记录。而上述的insert操作的重做日志是幂等的。 问3为什么把二进制日志binlog的格式设置为ROW其binlog仍然不是幂等
答ROW格式下binlog记录的是每一行数据在执行SQL操作前后的具体变化。这包括表名、行ID以及发生变化的列值。ROW格式本身并不直接提供幂等性保证它只是记录了数据的变化。 问4如果解决非幂等问题
答最常见的就是创建唯一索引防止插入重复数据。 redo log、binlog关于幂等性问题总结 redo log的幂等性redo log是物理日志记录的是数据页的物理变化而非具体的SQL语句。因此无论多少次写入相同的redo log记录对数据库的实际影响都是相同的。也就是说redo log的幂等性体现在它只关心数据页的最终状态而不关心达到这个状态的具体过程。因此即使redo log中的某些记录被多次写入只要它们代表的是相同的物理修改最终的数据页状态就不会改变。 binlog的非幂等性binlog是逻辑日志记录的是对数据库执行的修改操作即SQL语句。如果相同的SQL语句被多次执行那么数据库中的数据可能被多次修改从而导致非幂等结果。 注对于binlog而言即便因为表中存在主键id导致insert语句在多次插入时产生冲突从而避免数据重复但这并不意味着binlog本身是幂等的。因为此时是由于主键约束保证了数据的唯一性而不是因为binlog本身是幂等的。
undo log undo log 的概念
undo log的作用回滚和MVCC并发控制。
redo log存放在重做日志文件中而undo log存放在数据库内部的一个特殊段segment中这个段称为undo段。
undo段位于共享表空间中。可以通过py_innodb_page_info.py工具查看当前共享表空间中undo的数量。
示例如下 从上图可知当前的共享表空间ibdata1内有2222个undo页。 误解undo用于将数据库物理地恢复到执行语句或事务之前的样子!
事实并非如此
例如用户执行了一个insert 10w条记录的事务这个事务会导致分配一个新的段即表空间增大。用户在回滚时会将插入的事务进行回滚但是表空间的大小并不会因此而收缩。因此当InnoDB存储引擎发生回滚时它实际上做的是与先前相反的工作。
回滚的实质对于insert操作回滚时会执行delete对于delete操作回滚时会执行insert对于update操作回滚时会执行一个相反的update 注意undo log也会产生redo log也就是undo log的产生会伴随着redo log的产生因为undo log也需要持久性的保护。
undo log 存储管理
InnoDB存储引擎对undo的管理采用段的方式。
InnoDB存储引擎有rollback segment每个回滚段记录了1024个undo log segment而在每个undo log segment段中进行undo页申请。共享表空间偏移量为5的页05记录了所有rollback segment header所在的页这个页类型为file_page_type_sys。 在InnoDB 1.1版本之前不包括InnoDB 1.1只有一个rollback segment因此支持同时在线的事务限制为1024。从InnoDB 1.1版本开始最大支持128个rollback segment故其支持同时在线的事务限制提高到了128*1024。 注意 每个undo log segment用于记录一个事务的undo log在InnoDB 1.1之前每个rollback segmen记录了1024个undo log segment这意味着同时只能有1024个事务在线因为每个事务都需要一个undo log segment来记录其undo日志。 InnoDB 1.1版本支持128个rollback segment但这些rollback segment都存储于共享表空间。 从InnoDB 1.2开始可通过参数对rollback segment进行控制参数如下 innodb_undo_directory用于设置rollback segment文件所在路径可以存放在共享表空间以外的位置即可以设置为独立的共享表空间。该参数的默认值为 “.”表示当前InnoDB存储引擎的目录。 innodb_undo_logs用于设置rollback segment的个数默认值为128。InnoDB 1.1版本中用参数innodb_rollback_segment设置现已被该参数替换。 innodb_undo_tablespaces用于设置构成rollback segment文件的数量这样rollback segment可以较为平均的分布在多个文件中。该参数设置后会在路径innodb_undo_directory中看到undo为前缀的文件该文件就代表rollback segment文件。下图显示了由3个文件组成的rollback segment。 注意事务在undo log segment分配页并写入undo log的这个过程同样需要写入重做日志。当事务提交时InnoDB会做以下两件事 将undo log放入列表中以供之后的purge操作。 判断undo log所在的页是否可以重用若可以则分配给下个事务使用。 事务提交后并不能马上删除undo log及undo log所在页。这是因为可能还有其它事务需要通过undo log来得到行记录之前的版本。 问是否可以为每一个事物分配一个单独的undo页
答不能这样会非常浪费存储空间特别是对于OLTP的应用类型。因此在InnoDB存储引擎的设计中对undo页可以进行重用。具体来说当事务提交时首先将undo log放入链表中然后判断undo页的使用空间是否小于3/4若是则表示该undo页可以被重用之后新的undo log记录在当前undo log的后面。 示例假设某个应用的删除和更新操作的TPStransaction per second为1000为每个事务分配一个undo页那么一分钟就需要1000*60个页大约需要的存储空间为1GB。若每秒purge页的数量为20则这样的涉及对磁盘空间有着相当高的要求。 注意由于存放undo log的列表是以记录进行组织的而undo页可能存放着不同事务的undo log因此purge操作需要涉及磁盘及的离散读取操作是一个比较缓慢的过程。 可以通过show engine innodb status来查看链表中undo log的数量如 History list length就代表了undo log的数量这里为12。purge操作会减少该值然而由于undo log所在的页可以被重用因此即使操作发生History list length的值也可以不为0。
undo log 的格式
思考如果多个事务对同一条记录进行修改且同时有事务在处理同一个表中的其它记录那么同一条记录的不同版本是连续存放还是间断性存放 在InnoDB存储引擎中undo log分为
insert undo log
指在insert操作中产生的undo log。
因为insert操作的记录只对事务本身可见对其他事务不可见故该undo log可以在事务提交后直接删除。无需进行purge操作。
insert undo log结构示意图 名词解释 next记录下一个undo log的位置通过next的字节可以知道一个undo log所占的空间字节数。 type_cmpl占用一个字节记录undo的类型对于insert undo log该值总是11。 undo_no记录事务的ID。 table_id记录undo log所对应的表对象。 ……记录了所有主键的列和值。 * 表示对存储字段进行了压缩。 start记录的是undo log的开始位置。 在进行rollback操作时根据这些值可以定位到具体的记录然后进行删除即可。
update undo log
update undo log记录的是对delete和update操作产生的undo log。
该undo log可能需要提供MVCC机制因此不能在事务提交是就进行删除。提交时放入undo log链表等待purge线程进行最后的删除。
update undo log结构示意图 名词解释 next、start、undo_no、table_id和insert undo log部分相同。 由于update undo log本身还有分类type_cmpl取值如下 update_vector表示update操作导致发生改变的列。每个修改的列信息都要记录在undo log中。对于不同的undo log类型可能还需要记录对索引列所做的修改。 查看undo log信息
Oracle和Microsoft SQLServer数据库都由内部的数据字典来观察当前undo的信息InnoDB存储引擎在这方面做得还不够DBA只能通过原理和经验来进行判断。
InnoSQL对information_schema进行了扩展添加了两张数据字典表innodb_trx_rollback_segment、innodb_trx_undo这样用户可以非常方便和快捷地查看undo的信息。
但MySQL不支持感兴趣的读者可自行参考MySQL技术内幕-InnoDB存储引擎。
purge purge用于最终完成delete和update操作。这样设计是因为InnoDB存储引擎支持MVCC所以记录不能在事务提交时立即进行处理。这时其他事物可能正在引用这行故InnoDB存储引擎需要保存记录之前的版本。
而是否可以删除该条记录通过purge来进行判断。若该行记录已不被任何其他事务引用那么就就可以进行真正的delete操作。可见purge操作是清理之前的delete和update操作将上述操作最终完成。 InnoDB存储引擎的设计原理一个页上允许多个事务的undo log存在。虽然这不代表事务在全局中的提交顺序但后面的事务产生的undo log总在最后。此外InnoDB存储引擎还有一个history列表它根据事务的提交顺序将undo log进行连接。 undo log与history列表的关系 history list表示按照事务提交的顺序将undo log进行组织。
在InnoDB存储引擎的设计中先提交的事务总在尾端。undo page存放了undo log由于可以重用因此一个undo page中可能存放了多个不同事务的undo log。图中阴影部分trx5表示该undo log正在被其它事务引用。 问1InnoDB存储引擎是怎么清除undo log的
答在执行purge的过程中InnoDB存储引擎首先会从history list中找到第一个需要被清理的记录这里为trx1清理之后InnoDB存储引擎会在trx1的undo log所在页中继续寻找是否存在可以被清理的记录undo log这里会找到trx3接着找到trx5但是发现trx5正在被其他事务引用而不能清理。故再次去history list中查找发现这时最尾端的记录为trx2接着找trx2所在的页然后依次再把事务trx4、trx6的记录进行清理undo log。
由于undo page2中所有的页都被清理因此该undo page可以被重用。 注意InnoDB存储引擎这种先从history list中找undo log然后再从undo page中找undo log的涉及模式是为了避免大量的随机读取操作从而提高purge的效率。 全局动态参数innodb_purge_batch_size用来设置每次purge操作需要清理的undo page数量。
在InnoDB 1.2版本之前该参数默认值为20。从InnoDB 1.2版本开始该参数的默认值为300。 问2innodb_purge_batch_size参数是否设置的越大越好
答不是的。通常来说该参数设置的越大每次回收的undo page也就越多这样可供重用的undo page就越多减少了磁盘存储空间与分配的开销。不过若该参数设置的太大则每次需要purge处理的undo page就越多从而导致CPU和磁盘IO过于集中对于undo page的处理使性能下降。
注MySQL官方手册建议普通用户不需要调整该参数。 问3当InnoDB存储引擎的压力非常大时并不能高效地进行purge操作。那么history list的长度会变得越来越长此时怎么办
答使用全局动态参数innodb_max_purge_lag参数来控制history list的长度若长度大于该参数其会延缓DML操作。该参数默认值为0表示不对history list做任何限制。当该参数大于0时就会延缓DML的操作其延缓算法为
delay((length(history_list)-innodb_max_purge_lag)*10)-5
delay的单位是毫秒。
此外需要注意的是delay的对象是行而不是一个DML操作。
例如当一个update操作需要更新5行数据时每行数据的操作都会被delay故总的延迟时间为5*delay。而delay的统计会在每一次purge操作完成后重新进行计算。 InnoDB 1.2版本引入了参数innodb_max_purge_lag_delay其用来控制delay的最大毫秒数。当上述计算得到的delay大于该参数时将delay设置为innodb_max_purge_lag_delay避免由于purge操作缓慢导致其它SQL线程出现无限制的等待。
group commit 若事务为非只读事务则每次事务提交时都要进行一次fsynu操作以保证重做日志都写入磁盘。
为了提高fsync的效率当前数据库都提供了group commit的功能即一次fsync可以刷新确保多个事务日志被写入文件。对于InnoDB存储引擎来说事务提交时会进行两个阶段的操作 修改内存中事务对应的信息并将日志写入重做日志缓冲。 调用fsync将确保日志都从重做日志缓冲写入磁盘。 步骤2相对较慢因为存储引擎需要与磁盘打交道。 group commit原理
当有事务进行步骤2时其他事务可以进行步骤1正在提交的事务完成提交后再次进行步骤2时可以将多个事务的重做日志通过一次fsync刷新到磁盘这样就大大减少了磁盘的压力从而提高数据库的整体性能。
对于写入或更新较为频繁的操作group commit的效果尤为明显。 然而在InnoDB 1.2版本之前在开启二进制日志后InnoDB存储引擎的group commit功能会失效从而导致性能的下降。并且在线环境多使用replication环境因此二进制日志的选项基本都为开启状态因此这个问题尤为显著。 导致这个问题的原因是在开启二进制日志后为了保证存储引擎层中的事务和二进制日志的一致性二者之间使用了两阶段事务其步骤如下 当事务提交时InnoDB存储引擎进行prepare操作。 MySQL数据库上层写入二进制日志。 InnoDB存储引擎层将日志写入重做日志文件。 修改内存中事务对应的信息并且将日志写入重做日志缓冲。 调用fsync将确保日志都从重做日志缓冲写入磁盘。 注意步骤2和步骤3都需要进行一次fsync操作才能保证上下两层数据的一致性分别由sync_binlog、innodb_flush_log _at_trx_commit参数控制。 上述整个过程流程图如下 为了保证MySQL数据库上层二进制日志的写入顺序和InnoDB层的事务提交顺序一致MySQL数据库内部使用了prepare_commit_ mutex这个锁。但是在启用这个锁之后步骤3中的步骤a不可以在其它事务执行步骤b时进行从而导致了group commit失效。 问1为什么需要保证MySQL数据库上层二进制日志的写入顺序和InnoDB层的事务提交顺序一致呢
答因为备份及恢复的需要。 例如通过工具xtrabackup或者ibbackup进行备份并用来建立replication如图所示 从上图可以看到若通过在线备份进行数据库恢复来重新建立replication事务T1的数据会产生丢失。因为在InnoDB存储引擎层会检测事务T3在上下两层都完成了提交不需要再进行恢复。 因此通过锁prepare_commit_mutex以串行的方式来保证顺序性然而这会使group commit无法生效如图所示 问2如何解决prepare_commit_mutex锁导致group commit失效问题
答自MySQL 5.6开始该问题就已经被解决且将其称为Binary Log Group CommitBLGC。
MySQL 5.6 BLGC的具体实现如下 在MySQL数据库上层进行提交时首先按顺序将其放入一个队列中队列中的第一个事务称为leader其它事务称为followerleader控制着follower的行为。
BLGC的步骤分为以下三个阶段 Flush阶段将每个事务的二进制日志写到内存中。 Sync阶段将内存中的二进制日志刷新到磁盘按队列中事务的顺序若队列中有多个事务那么仅一次fsync操作就完成了二进制日志的写入这就是BLGC。 Commit阶段leader根据顺序调用存储引擎层事务的提交InnoDB存储引擎本就支持group commit因此修复了原先由于锁prepare_commit_mutex导致group commit失效的问题。 注该解决方案去除了prepare_commit_mutex锁。 当有一组事务在进行Commit阶段时其它新事务可以进行Flush阶段从而使group commit不断生效。
此外参数binlog_max_flush_queue_time用来控制Flush阶段中的等待时间即上一组事务完成提交当前组的事务也不马上进入fsync阶段而是至少需要等待一段时间。 问3为什么上一组事务提交后需要等待一段时间再提交当前组事务
答这样可以使得group commit中的事务数量更多但如果实物数量过多可能会导致事务的响应时间变慢。该参数默认值为0建议值也为0。除非用户的MySQL数据库系统中有着大量的连接如100个连接并且再不断地进行事务的写入或更新操作。 文章转载自： http://www.morning.kjtdy.cn.gov.cn.kjtdy.cn http://www.morning.qyhcm.cn.gov.cn.qyhcm.cn http://www.morning.kfclh.cn.gov.cn.kfclh.cn http://www.morning.dkzrs.cn.gov.cn.dkzrs.cn http://www.morning.rcmcw.cn.gov.cn.rcmcw.cn http://www.morning.kxypt.cn.gov.cn.kxypt.cn http://www.morning.dlhxj.cn.gov.cn.dlhxj.cn http://www.morning.bmpjp.cn.gov.cn.bmpjp.cn http://www.morning.rppf.cn.gov.cn.rppf.cn http://www.morning.bkylg.cn.gov.cn.bkylg.cn http://www.morning.kjlhb.cn.gov.cn.kjlhb.cn http://www.morning.gagapp.cn.gov.cn.gagapp.cn http://www.morning.zqzhd.cn.gov.cn.zqzhd.cn http://www.morning.qfgxk.cn.gov.cn.qfgxk.cn http://www.morning.dqkrf.cn.gov.cn.dqkrf.cn http://www.morning.rpsjh.cn.gov.cn.rpsjh.cn http://www.morning.xjpnq.cn.gov.cn.xjpnq.cn http://www.morning.fkffr.cn.gov.cn.fkffr.cn http://www.morning.ttkns.cn.gov.cn.ttkns.cn http://www.morning.pxjp.cn.gov.cn.pxjp.cn http://www.morning.gfznl.cn.gov.cn.gfznl.cn http://www.morning.wrtxk.cn.gov.cn.wrtxk.cn http://www.morning.kbqqn.cn.gov.cn.kbqqn.cn http://www.morning.c7491.cn.gov.cn.c7491.cn http://www.morning.stpkz.cn.gov.cn.stpkz.cn http://www.morning.qttft.cn.gov.cn.qttft.cn http://www.morning.cpnsh.cn.gov.cn.cpnsh.cn http://www.morning.jrwbl.cn.gov.cn.jrwbl.cn http://www.morning.wwxg.cn.gov.cn.wwxg.cn http://www.morning.qlkjh.cn.gov.cn.qlkjh.cn http://www.morning.xlpdm.cn.gov.cn.xlpdm.cn http://www.morning.wptdg.cn.gov.cn.wptdg.cn http://www.morning.rnmmh.cn.gov.cn.rnmmh.cn http://www.morning.ktsth.cn.gov.cn.ktsth.cn http://www.morning.nlmm.cn.gov.cn.nlmm.cn http://www.morning.yhljc.cn.gov.cn.yhljc.cn http://www.morning.tcsdlbt.cn.gov.cn.tcsdlbt.cn http://www.morning.kwxr.cn.gov.cn.kwxr.cn http://www.morning.nnmnz.cn.gov.cn.nnmnz.cn http://www.morning.lgrkr.cn.gov.cn.lgrkr.cn http://www.morning.lphtm.cn.gov.cn.lphtm.cn http://www.morning.tntbs.cn.gov.cn.tntbs.cn http://www.morning.gpryk.cn.gov.cn.gpryk.cn http://www.morning.bmrqz.cn.gov.cn.bmrqz.cn http://www.morning.rntby.cn.gov.cn.rntby.cn http://www.morning.wtxdp.cn.gov.cn.wtxdp.cn http://www.morning.fswml.cn.gov.cn.fswml.cn http://www.morning.znrlg.cn.gov.cn.znrlg.cn http://www.morning.pltbd.cn.gov.cn.pltbd.cn http://www.morning.bzsqr.cn.gov.cn.bzsqr.cn http://www.morning.sskkf.cn.gov.cn.sskkf.cn http://www.morning.dsncg.cn.gov.cn.dsncg.cn http://www.morning.qwwhs.cn.gov.cn.qwwhs.cn http://www.morning.nfyc.cn.gov.cn.nfyc.cn http://www.morning.jwsrp.cn.gov.cn.jwsrp.cn http://www.morning.tlzbt.cn.gov.cn.tlzbt.cn http://www.morning.skmpj.cn.gov.cn.skmpj.cn http://www.morning.nsncq.cn.gov.cn.nsncq.cn http://www.morning.tnwwl.cn.gov.cn.tnwwl.cn http://www.morning.zgdnz.cn.gov.cn.zgdnz.cn http://www.morning.nfmlt.cn.gov.cn.nfmlt.cn http://www.morning.pbbzn.cn.gov.cn.pbbzn.cn http://www.morning.grwgw.cn.gov.cn.grwgw.cn http://www.morning.qtfss.cn.gov.cn.qtfss.cn http://www.morning.dfkby.cn.gov.cn.dfkby.cn http://www.morning.fbdtd.cn.gov.cn.fbdtd.cn http://www.morning.bhxzx.cn.gov.cn.bhxzx.cn http://www.morning.jwtwf.cn.gov.cn.jwtwf.cn http://www.morning.nbwyk.cn.gov.cn.nbwyk.cn http://www.morning.ryznd.cn.gov.cn.ryznd.cn http://www.morning.kpqjr.cn.gov.cn.kpqjr.cn http://www.morning.lgznc.cn.gov.cn.lgznc.cn http://www.morning.tbqbd.cn.gov.cn.tbqbd.cn http://www.morning.mjzgg.cn.gov.cn.mjzgg.cn http://www.morning.lfsmf.cn.gov.cn.lfsmf.cn http://www.morning.nmpdm.cn.gov.cn.nmpdm.cn http://www.morning.attorneysportorange.com.gov.cn.attorneysportorange.com http://www.morning.tgbx.cn.gov.cn.tgbx.cn http://www.morning.qpntn.cn.gov.cn.qpntn.cn http://www.morning.tdxnz.cn.gov.cn.tdxnz.cn
