`

PostgreSQL与Innodb并发控制大比拼

阅读更多

http://wangyuanzju.blog.163.com/blog/static/130292009107101544125/

多版本并发控制技术已经成为未来数据库的发展趋势。目前,多版本并发控制被很多数据库或存储引擎采用,如Oracle,MS SQL Server 2005+, PostgreSQL, Firebird, InnoDB, Falcon, PBXT, Maria等等。新的数据库存储引擎,几乎毫无例外的使用多版本而不是单版本加锁的方法实现并发控制。

    虽然都是多版本,但不同的数据库系统的实现却有很大不同。在开源数据库领域最负盛名的两个系统PostgreSQL和InnoDB的多版本实现就可谓有天壤之别。

    一、PostgreSQL的多版本实现(基于8.4.1版本)

    PostgreSQL采用堆+B+树索引(忽视R树、哈希、GiST等不常用的索引)的存储结构,堆与索引的存储模式不同。堆中记录包含版本化信 息,PostgreSQL不区分记录的最新版本或老版本,都存储在堆中。简单的说,堆中每条记录头上记录t_xmin和 t_xmax两个属性,分别表示创建与删除这一版本的事务ID,另外记录t_ctid属性,表示该记录下一个更新的版本的RID,即记录的多个版本构成从 最老到最新的单向链表(见HeapTupleHeaderData结构)。DELETE一条记录时,设置t_xmax,并不将记录真正删除;UPDATE 一条记录时,也不直接更新,而是插入一个新版本,对原来被更新的版本,将其t_xmax设为当前事务ID,设置其t_ctid指向新版本。

  有了这些信息还不够,为了判断版本的可见性,还需要两个东西,一是事务提交日志,二是事务快照。事务提交日志对每个事务使用两个bit,记录事 务是活跃、已提交还是已回滚。事务快照在事务开始时分配,其中最重要的信息是当时活跃事务的列表(见SnapshotData结构)。

  有了这些东西,系统可以判断一个版本是否可见。判断过程比较复杂,不过从简单的原理上说,系统先通过判断t_xmin是否在全局活跃事务列表 中、是否在事务快照活跃事务列表中、根据事务提交日志判断事务是提交还是回滚了等来判断t_xmin事务是否在事务开始时已经提交;然后用类似的方法判断 t_xmax是否在事务开始时已经提交。如果t_xmin在事务开始时没有提交则不可见;如果t_xmin在事务开始时已经提交而t_xmax没有,则可 见;如果t_xmin和t_xmax在事务开始时都已经提交了则不可见。(详细过程见HeapTupleSatisfiesMVCC、 TransactionIdDidCommit、XidInMVCCSnapshot等函数)。

  索引中则不包含版本信息。一般情况下,记录的所有版本都在索引中存在对应的索引项。举个例子,如果一个表有三个索引,更新一条记录时,不但在堆 中会插入一个新版本,新版本对应的索引项也要插入到三个索引中,即使这次更新可能没有更新某些索引的属性(见ExecUpdate函数)。在 PostgreSQL 8.3中引入了HOT(Heap-Only-Tuple)技术,如果新老版本在同一页面,并且UPDATE没有更新任何索引属性,则不插入新版本对应的索 引项。

  由于索引没有版本信息,进行索引扫描时,即使查询所需所有属性在索引中都存在,也需要从堆中取出对应的记录判断是否可见(见index_getnext函数)。----这会降低效率

  事务提交或回滚时操作简单,除事务提交时要写出事务外,只需要更新事务提交日志中对应的事务状态。也就是说回滚时并不需要将事务所作的操作从物理上清理掉,只要将事务状态设为已经回滚,则该事务产生的版本对其它事务自然就不可见了。----回滚操作的代价非常小

  老旧的不再需要的版本,即不会被将来的任何事务见到的版本的清理是通过VACUUM实现的。由于新老版本混杂在一起,进行VACUUM时本质上 是需要扫描所有数据。8.4版中引入了Visibility Map技术,用来在VACUUM时跳过那些肯定不包含老旧版本的页面,但如果系统更新频繁且离散,这一技术就派不上大用场。在线的VACUUM只能清理页 面中的老旧版本,但不能缩减表占用的空间,其实是产生碎片。要缩减表空间时的VACUUM会锁住表导致期间表不能被更新。------对旧版本的purge比较困难

  二、InnoDB的多版本实现(基于MySQL 5.1.33版本带的InnoDB)

  InnoDB采用索引组织表的存储结构,没有堆,记录存储在主键索引中,其它索引称为二级索引,其中每个索引项都包含所对应记录的主键。主键索引与二级索引的存储格式也不同。----主键是聚簇索引

  主键索引拥有版本化信息,但与PostgreSQL不同,一般情况下InnoDB的主键索引中只存储记录的最新版本,旧版本的信息则集中存储在回滚段中---回滚段是innodb的公共表空间只有主键被更新时才需要同时存储多个版本在主键索引中---所以任何时候都不要更新主键 。主键索引记录的头上包含有6字节的事务ID与7字节指向回滚段中旧版本的指针.DELETE时只是标记而不真正删除。UPDATE时进行本地更新,并将前像写到回滚段中。

  存在与PostgreSQL中事务快照类似读视图,也记录了事务开始时的活跃事务列表(见read_view_struct结构),但不需要 PostgreSQL中的事务提交日志。根据读视图和记录头上的事务ID,可以判断出一个版本在事务开始时是否已经提交,即是否可见。如果存储在主键索引 中的记录不可见,则根据指向回滚段中旧版本的指针找到旧版本信息,构造出旧的记录。回滚段采用的是append-only的日志型存储,记录的旧版本信息 并不是一条完整的记录,而只是被更新的属性的前像。回滚段中的旧版本信息中也包含更旧的版本的位置,即版本链表是从新到旧的。------始终通过最新的信息查找到较老的信息,来满足多个读版本的需要

  由于没有事务日志表示事务是否回滚,在事务回滚时必须清理该事务所进行的修改,插入的记录要删除,更新的记录要更新回来(见row_undo函数)。事务提交时则无需处理。-----所以,innodb的提交是非常快的,因为在提交的那一刻,该做的事情都做了;但是在回滚的时候,会比较慢

  二级索引中的每个索引项并没有版本化信息。但在页面头记录了对该页面操作的事务的ID的最大值,通过这一值可以判断页面中是否可能包含不可见的 数据,如果是,则需要访问主键索引判断可见性。否则,可以直接从索引中获取查询所需属性。二级索引中可能存储一条记录的多个版本对应的索引项,如果 UPDATE操作更新了某个索引的属性,则类似于PostgreSQL,插入新索引项到二级索引中,老索引项并不删除。但没有被UPDATE操作更新的索 引则不需要插入新索引项。---MVCC需要同时维护聚簇索引和二级索引,开销较大:先判断当前事物id与页面的最大的事务id,如果当前的事务id较大,那么直接读取即可;否则,需要回溯整个事务链表,直到找到最大的小于当前事务id编号的记录,然后返回.

  系统使用一个后台线程不时处理回滚段,在需要时清理由于DELETE、二级索引或主键索引中由于主键被更新而产生的老旧版本,这一过程称这purge。如果UPDATE没有更新索引,则不会带来purge开销。

  三、评价与总结

  PostgreSQL与InnoDB的多版本实现最大的区别在于最新版本和历史版本是否分离存储,PostgreSQL不分,InnoDB分-----innodb也只是分离了数据,索引本身没有分开

  PostgreSQL的这种设计被其最初的设计者Mike Stonebraker称为no-overwrite的设计,在设计了PostgreSQL几年之后他的一篇回顾性论文《The Implementation of Postgres》(PostgreSQL早期叫Postgres)中,Stonebraker指出当初这样设计的主要原因是寻求与当时已经广泛使用的 WAL模式不同的存储机制,有点为了创新而创新的意思。这一设计有两大好处:一是事务回滚时无需复杂处理,非常快;二是可以查询以前的历史数据。还有一个 可能的好处是可以实现数据即日志,即更新时只要更新数据就行了,不需要再写日志来描述做了什么更新。但要使这个好处实现,需要有一种持久的,并且随机写具 有与顺序写类似性能的存储介质才行,因为为了保证事务提交后的持久性,需要写出被事务更新的数据,而这些数据可能是离散的。WAL系统则不同,事务提交时 只需要写日志就行了,而日志是顺序写入的。当前的硬件环境并不是这样,因此PostgreSQL中仍然还要写日志,只不过不需要写UNDO日志,只要 REDO日志就行 ---undo日志其实就在数据文件的链表中,通过分析数据即可得出.但是如果要恢复数据库,还是必须记录整个日志,光数据是不行的,否则数据会越来越大

  最新的PostgreSQL与当初Stonebraker的设计已经有了很大改进,比如HOT技术减少了索引中的版本数,Visibility Map技术加快了VACUUM,记录头部结构也更紧凑。但no-overwrite的设计原则仍然没变。

  相对于InnoDB,PostgreSQL的优势似乎主要的只有一条:事务回滚可以立即完成,无论事务进行了多少操作。查询以前的历史数据的功能并不常用,在目前的PostgreSQL中也并不实用。--通过多版本来构造数据的机会可能并不多,除非非要使用事务的应用逻辑

  PostgreSQL的主要劣势在于:

  1、最新版本和历史版本不分离存储,导致清理老旧版本需要作更多的扫描,代价更大;

  2、UPDATE不是本地更新,会产生老旧版本需要清理。与之相对的是InnoDB只有在事务回滚时才需要清理老的记录数据。而事务回滚是罕见的;

  3、只要有一个索引属性被更新,或者新版本的记录与原版本不在同一页面,就要插入所有索引的新版本索引项;

  4、堆占用的空间不能通过在线的VACUUM回收,在线VACUUM会产生很多碎片(这也是由于使用了堆而不是索引组织表导致的);

  5、由于索引中完全没有版本信息,不能实现Coverage index scan,即查询只扫描索引,直接从索引中返回所需的属性。与之相对的是InnoDB中二级索引页头记录的最近修改该页的事务ID信息可以在大部分情况下 实现Coverage index scan。Coverage index scan是应用中经常使用的优化技巧,PostgreSQL不支持这个对提升系统性能带来很大限制,因为索引扫描是顺序访问,去访问堆则很可能变成乱序访 问,性能可能相差百倍;

  6、判断版本可见性更复杂,开销更大。PostgreSQL比InnoDB在判断可见性时,需要增加访问事务提交日志的操作,事务提交日志每个 事务需要分配两个bit,对高更新负载的系统会占用较大空间,这时要么事务提交日志回占用大量内存,要么判断可见性时就可能产生额外的IO。对比 PostgreSQL中判断可见性的函数HeapTupleSatisfiesMVCC和InnoDB中判断可见性的函数 read_view_sees_trx_id,可以容易看出这两者的复杂度不可同日而语。

  InnoDB的主要劣势在于事务回滚时需要清理事务所作的所有修改,因此使用InnoDB时要避免使用超大型事务,否则回滚可能超慢无比。---但是正如作者所说,回滚是罕见的,呵呵

分享到:
评论
2 楼 niyunjiu 2010-05-18  
diunei 写道
做人厚道点,别人的文章转载都不标注一下~~~~

风清扬:

http://wangyuanzju.blog.163.com/blog/static/130292009107101544125/


对不起!
你可以看,我博客里面大部分文章都有原始的url,另外在javaeye发表博客的时候会有一个选项,设置原创与否,我都如实填写。
你的这篇博客应该是二次转载,就是别人转载了,然后我又转载别人的,找不到原始地址,所以就没有写上原始url,请见谅!
现已经加上了。
1 楼 diunei 2010-05-17  
做人厚道点,别人的文章转载都不标注一下~~~~

风清扬:

http://wangyuanzju.blog.163.com/blog/static/130292009107101544125/

相关推荐

    并发控制实现方法的比较研究

    通过对比PostgreSQL与InnoDB在并发控制实现上的差异,我们可以看到多版本并发控制和两阶段锁定这两种方法各有千秋。在实际应用中,选择合适的并发控制策略需要综合考虑系统的具体需求、性能目标以及资源限制等因素。...

    浅析PostgreSQL事务处理机制

    而MySQL虽然使用广泛,但在默认的InnoDB存储引擎中使用了基于行的锁定机制,这与PostgreSQL的MVCC机制相比,在处理大量并发读取操作时可能效率较低。 总之,PostgreSQL通过其先进的事务处理机制和并发控制策略,在...

    PostgreSQL与mysql字段对比[总结].pdf

    - MySQL支持InnoDB引擎,提供行级锁定,适合高并发场景,且有较好的分区和复制功能。 - PostgreSQL的扩展性较强,支持窗口函数、物化视图、JSON和XML处理,以及地理空间数据类型等,适合构建复杂的应用。 在实际...

    postgresql介绍使用 和mysql的对比优化

    相较于MySQL,PostgreSQL提供了更多的高级特性,如事务一致性、多版本并发控制(MVCC)、窗口函数以及复杂的JSON处理能力。在本篇文章中,我们将深入探讨PostgreSQL的使用方法及其与MySQL的对比,同时也会关注数据库...

    PostgreSQL 最佳性能优化实践授课PPT 386页

    1. **与MySQL比较**:PostgreSQL在ACID合规性、事务处理能力、JSON支持和窗口函数等方面有优势,但MySQL在轻量级部署和InnoDB引擎的性能上有特色。 2. **与Oracle比较**:PostgreSQL在开源、社区支持和成本上占据...

    PostgreSQL 8.2,Oracle 10g,SQL Server 2005,MYSQL 5.1 脚本例子

    它的SQL支持包括标准的SQL:2003,同时还提供了一些高级特性,如事务一致性、多版本并发控制(MVCC)和窗口函数。学习PostgreSQL的脚本,可以帮助你理解如何创建表、索引、触发器、存储过程,以及进行复杂查询。 ...

    Mysql+5.5+vs+Postgresql+9.1+负载均衡比较

    本次研究对比了两个广泛使用的开源关系型数据库系统——MySQL 5.5与PostgreSQL 9.1,在负载均衡方面的表现,旨在为数据库管理员和技术决策者提供更深入的理解和参考。 ### MySQL 5.5与PostgreSQL 9.1的负载均衡机制...

    Mysql5.5与Postgresql9.1负载均衡比较.pdf

    从MySQL 5.5版本开始,默认使用InnoDB存储引擎,它是一个支持事务处理的存储引擎,能够更好地处理并发和数据完整性。 在MySQL中,复制机制是负载均衡的基础。复制涉及一个主服务器(Master)和多个从服务器(Slave...

    为什么选择pg

    从上述对比来看,PostgreSQL 在很多方面表现更为优秀,尤其是在支持 SQL 标准、事务一致性、并发控制以及高可用技术实现等方面。尽管 MySQL 在某些特定场景下仍具有优势,如 Web 应用程序中的简单数据交易处理,但从...

    PostgreSQL 8.2,Oracle 10g,SQL Server 2005,MYSQL 5.1 脚本规范

    - 事务处理、并发控制和锁机制。 - 安全性和权限管理,包括用户账户、角色和权限分配。 - 性能调优,如索引创建、查询优化和存储配置。 - 备份和恢复策略,以及故障恢复方法。 - 数据导入导出和迁移工具的使用。 ...

    mysql源码学习.zip

    MySQL是一款广泛使用的开源关系型数据库管理系统,其源码学习对于深入理解数据库的工作原理和技术细节...此外,MySQL源码的阅读也可以帮助我们理解和对比其他数据库系统,如PostgreSQL、Oracle等,进一步提升技术能力。

    开源基础软件产品的选型评估标准及MySQLPostgreSQL的选型对比.docx

    二、MySQL与PostgreSQL的选型对比 1. **功能特性**: - MySQL更适合读密集型应用,其查询速度较快,尤其在InnoDB引擎下对事务处理有良好支持。 - PostgreSQL则更注重ACID合规性,支持复杂查询和JSON等高级数据...

    mysql2pgsql.zip

    **MySQL与PostgreSQL对比** 1. **MySQL**:开源、轻量级,适用于高并发读写场景,以其高效性能和易于使用而闻名。它支持多种存储引擎,如InnoDB(事务处理)和MyISAM(非事务处理)。 2. **PostgreSQL**:同样开源...

    mysql_eve

    此外,PostgreSQL的事务隔离级别更高,更适合并发控制,而MySQL的InnoDB引擎提供了行级锁定,也适合高并发场景。 如果“mysql_eve”项目是关于在MySQL中模拟PLpgSQL的行为,那么可能涉及到了如何在MySQL中实现类似...

    几种开源数据库的比较.pdf

    本文将深入探讨并对比四种主流的开源数据库系统——MySQL、PostgreSQL、SAPDB 和 InterBase,通过分析它们的主要特性以及各自的优缺点来帮助读者更好地理解这些数据库系统的特点。 #### 1. 开源数据库系统概述 ...

    2019Java高级面试专题

    1. 多线程:深入理解Java并发编程,包括线程池、锁机制(如synchronized、ReentrantLock)、并发容器(如ConcurrentHashMap、BlockingQueue)等。 2. 高级IO/NIO:掌握Java IO流的使用,理解NIO(非阻塞I/O)的工作...

    mysql-8.0.22-win64.zip

    - **与 PostgreSQL 比较**:PostgreSQL 提供更丰富的数据类型和更强大的事务处理能力,而 MySQL 以其高性能和易用性受到青睐。 - **与 Oracle 比较**:Oracle 提供更全面的企业级功能,但成本较高,MySQL 则适合中...

    精简版mysql 5.5.40及对应navicat

    1. **InnoDB 引擎改进**:MySQL 5.5 引入了 InnoDB 存储引擎的许多增强,包括更好的并发性能,通过并行插入线程来加速写操作,以及支持更多行记录的自适应哈希索引。 2. **Full-text Search**:增强了全文本搜索功能...

    LAMP系统性能调优理解LAMP架构.pdf

    性能度量还能用于评估调优效果,对比调整前后的数据,确保改进措施真正提高了系统性能。 **LAMP架构的变体** 包括Windows上的WAMP(Windows、Apache、MySQL、PHP)或其他Web服务器如lighttpd,以及使用PostgreSQL、...

Global site tag (gtag.js) - Google Analytics