InnoDB 隔离模式对 MySQL 性能的影响

InnoDB 隔离模式对 MySQL 性能的影响

过去的几个月我写了两篇文章,一篇是InnoDB 事务历史相关的危险债务,另一篇是关于MVCC 可能导致MySQL严重的性能问题的真相。在这篇文章里我将讨论一个相关的主题 – InnoDB 事务隔离模式,还有它们与MVCC(多版本并发控制)的关系,以及它们是如何影响MySQL性能的。

MySQL手册提供了一个关于MySQL支持的事务隔离模式的恰当描述 – 在这里我并不会再重复,而是聚焦到对性能的影响上。

SERIALIZABLE – 这是最强的隔离模式,本质上打败了在锁管理(设置锁是很昂贵的)的条件下,多版本控制对所有选择进行锁定造成大量的开销,还有你得到的并发。这个模式仅在MySQL应用中非常特殊的情况下使用。

REPEATABLE READ – 这是默认的隔离级别,通常它是相当不错的,对应用程序的便捷性来说也不错。它在第一次的时候读入所有数据 (假设使用标准的非锁读)。但是这有很高的代价 – InnoDB需要去维护事务记录,从一开始就要记录,它的代价是非常昂贵的。更为严重的情况是,程序频繁地更新和hot rows – 你真的就不想InnoDB去处理rows了,它有成百上千个版本。

在性能上的影响, 读和写都能够被影响。用select查询遍历多个行是代价高昂的,对于更新(update)也是,在MySQL 5.6中,尤其是版本控制看起来导致了严重的争用问题。

下面是例子:完全在内存中的数据集中运行 sysbench,并启动 transaction 、运行全表、扫描、查询几次,同时保持 transaction 是开着的:

sysbench  --num-threads=64 --report-interval=10 --max-time=0 --max-requests=0 --rand-type=pareto --oltp-table-size=80000000 --mysql-user=root --mysql-password= --mysql-db=sbinnodb  --test=/usr/share/doc/sysbench/tests/db/update_index.lua run

正如你可以看到的,写(write )操作的吞吐量大幅下降,并且持续走低,这时transaction 是开着的,不仅是在查询(query)操作运行的时候。在可复读的隔离模式下,当你已经选择了之外的transaction ,紧接着就是一个long transaction ,这也许是我能找到的最糟糕情况了。当然了你也会在其他情况下看到回归算法(regression )。

 如果有人想测试,可以重复下面我用的查询集合:

12345 select avg(length(c)) from sbtest1;begin;select avg(length(c)) from sbtest1;select sleep(300);commit;

 不只是可复读(Repeatable Read)的默认隔离级别,同样也可以用于InnoDB 逻辑备份 –  mydumper 或者 mysqldump –single-transaction

 这些结果显示这个备份的方法恢复的时间太长而不能用于大型数据集合,同样这个方法受到性能影响,也不能用于频繁写入(write )的环境中。

 READ COMMITTED 模式和REPEATABLE READ模式很相似,本质区别在于哪个版本都不在transaction中从头开始读取,取而代之的从当前语句开始读取。因此使用这种模式允许InnoDB少维护很多版本,特别是你没有很长的statements要允运行。如果你有很长的select要运行,如报表查询对性能的影响仍然很严重。

 通常我认为好的做法是把READ COMITTED隔离模式做为默认,对于应用程序或者transactions 有必要就改成REPEATABLE READ。

READ UNCOMMITTED – 我觉得这是最难理解的隔离模式(悲催的只有2条文档),只描述了它的逻辑观点。如果你使用了这种隔离模式,你会看到数据控中所有发生的变化,即使是那些还 没被提交的transactions 。这种隔离模式一种好的用例是:你能“watch”到大规模的有脏读(dirty reads)的UPDATE 语句,显示了哪行被改变了,哪些没有改变。

如果transaction 事务在运行的时候出错了,那么这个声明会显示还没被提交的和可能没被提交的变化,所以使用这个模式要小心为妙。有一些用例虽然不需要我们100%准确的数据,在这种情况下,这种模式就变得非常方便。

那么,从性能角度来看,如何体现READ UNCOMMITTED?理论上,InnoDB 可以清除行版本,在READ UNCOMMITTED模式下即便是该语句已经开始执行之后,也可以创建。在实践中,由于一个bug或者一些复杂实现的细节做不到,语句开始仍然是行版 本。所以,如果你在READ UNCOMMITTED声明中运行很长的SELECT,你会得到大量的行版本创建信息,就像你用了READ COMMITTED。No win here。

从SELECT方面还有一个重要的win - READ UNCOMMITTED隔离模式意味着InnoDB 不需要去检查旧的行版本 - 最后一行总是对的,这会使得性能有明显的改善,尤其是当undo空间已经在磁盘上溢出,查找旧的行版本会造成大量的IO读写。

也许上面这个select avg(k) from sbtest1;是我能找到的最好的查询例子了,能与之类似的更新工作量。假使READ UNCOMMITTED隔离模式在一分钟左右完成,我认为在READ COMMITTED隔离模式下没有完成过,因为新索引条目插入的速度要比扫描速度快。

最后思考:正确的使用InnoDB 隔离模式,能够让您的应用程序得到最佳性能。你得到的好处可能不同,在某些情况下,也可能没什么区别。关系到InnoDB 的历史版本,似乎好有好多工作要做,我希望在未来的MySQL中能解决。

原文发布时间:2015-02-12

本文来自云栖合作伙伴“linux中国”

时间: 2024-11-30 17:07:35

InnoDB 隔离模式对 MySQL 性能的影响的相关文章

Nysql中InnoDB隔离模式对数据库性能有何影响

MySQL手册提供了一个关于MySQL支持的事务隔离模式的恰当描述 – 在这里我并不会再重复,而是聚焦到对性能的影响上. SERIALIZABLE – 这是最强的隔离模式,本质上打败了在锁管理(设置锁是很昂贵的)的条件下,多版本控制对所有选择进行锁定造成大量的开销,还有你得到的并发.这个模式仅在MySQL应用中非常特殊的情况下使用. REPEATABLE READ – 这是默认的隔离级别,通常它是相当不错的,对应用程序的便捷性来说也不错.它在第一次的时候读入所有数据 (假设使用标准的非锁读).但

浅谈InnoDB隔离模式的使用对MySQL性能造成的影响_Mysql

在这篇文章里我将讨论一个相关的主题 – InnoDB 事务隔离模式,还有它们与MVCC(多版本并发控制)的关系,以及它们是如何影响MySQL性能的. MySQL手册提供了一个关于MySQL支持的事务隔离模式的恰当描述 – 在这里我并不会再重复,而是聚焦到对性能的影响上. SERIALIZABLE – 这是最强的隔离模式,本质上打败了在锁管理(设置锁是很昂贵的)的条件下,多版本控制对所有选择进行锁定造成大量的开销,还有你得到的并发.这个模式仅在MySQL应用中非常特殊的情况下使用. REPEATA

mysql性能优化学习笔记

mysql性能优化 硬件对数据库的影响 CPU资源和可用内存大小 服务器硬件对mysql性能的影响 我们的应用是CPU密集型? 我们的应用的并发量如何? 数量比频率更好 64位使用32位的服务器版本 32位不能使用超过4G的内容,因此选择的时候需要注意. myisam innodb 磁盘的配置和选择 机械硬盘: 存储容量 传输速度 访问时间 主轴转速 物理尺寸 使用RAID增加传统机器硬盘的性能 RAID:一系列小磁盘组成大磁盘,数据冗余 RAID 0 :成本最低,但是没有数据冗余会造成数据丢失

MySQL性能优化之路---修改配置文件my.cnf_Mysql

在Apache, PHP, MySQL的体系架构中,MySQL对于性能的影响最大,也是关键的核心部分.对于Discuz!论坛程序也是如此,MySQL的设置是否合理优化,直接影响到论坛的速度和承载量!同时,MySQL也是优化难度最大的一个部分,不但需要理解一些MySQL专业知识,同时还需要长时间的观察统计并且根据经验进行判断,然后设置合理的参数. 下面我们了解一下MySQL优化的一些基础,MySQL的优化我分为两个部分,一是服务器物理硬件的优化,二是MySQL自身(my.cnf)的优化. 一.服务

数据库隔离级别(mysql+Spring)与性能分析

数据库隔离级别与Spring配置事务的联系及性能影响,以下是个人理解,如果有瑕疵请及时指正. 这里以mysql为例,先明确以下几个问题: 一.一般项目如果不自己配置事务的话,一般默认的是autocommit,即执行完一个操作后自动commit,提交事务. (注:事务是绑定在数据库操作上的,也就是当程序执行(statement.excute等操作)转而到数据库层面上的时候,事务才开始发生) 当然spring可以将几个数据库操作动作绑在一个事务中,这样就需要介绍下spring事务配置方法,下面介绍的

MySql删除和更新操作对性能有影响吗_MsSql

删除和更新操作的开销往往比插入高,所以一个好的设计需要减少对数据库的更新和删除操作. 3.1更新操作 数据库的更新操作会带来一连串的"效应":更新操作需要记录日志(以便错误时回滚):更新可变长字段(如,varchar类型)会带来数据物理存储的变化(记录的移动):更新索引字段会导致索引重建:更新主键会导致数据重组等.这一切不但会造成更新操作本身效率低,而且由于磁片碎片的产生会造成以后查询性能的降低.为了应对这一情况,有两种策略:一.减少更新次数,把多个字段的更新写到同一个语句里:二.避免

MySql删除和更新操作对性能有影响吗

删除和更新操作的开销往往比插入高,所以一个好的设计需要减少对数据库的更新和删除操作. 3.1更新操作 数据库的更新操作会带来一连串的"效应":更新操作需要记录日志(以便错误时回滚):更新可变长字段(如,varchar类型)会带来数据物理存储的变化(记录的移动):更新索引字段会导致索引重建:更新主键会导致数据重组等.这一切不但会造成更新操作本身效率低,而且由于磁片碎片的产生会造成以后查询性能的降低.为了应对这一情况,有两种策略:一.减少更新次数,把多个字段的更新写到同一个语句里:二.避免

五个影响mysql性能的关键配置

以下的文章主要是对MySQL性能影响关系紧密的五大配置参数的介绍,我前几天在相关网站看见对MySQL性能影响关系紧密的五大配置参数的资料,觉得挺好,就拿出来供大家分享,望你能有所收获. (一)连接 连接通常来自Web服务器,下面列出了一些与连接有关的参数,以及该如何设置它们. 1.max_connections 这是Web服务器允许的最大连接数,记住每个连接都要使用会话内存(关于会话内存,文章后面有涉及). 2.max_packet_allowed 最大数据包大小,通常等于你需要在一个大块中返回

影响MySQL性能的五大配置参数_Mysql

以下的文章主要是对MySQL性能影响关系紧密的五大配置参数的介绍,我前几天在相关网站看见对MySQL性能影响关系紧密的五大配置参数的资料,觉得挺好,就拿出来供大家分享,望你能有所收获. (一)连接 连接通常来自Web服务器,下面列出了一些与连接有关的参数,以及该如何设置它们. 1.max_connections 这是Web服务器允许的最大连接数,记住每个连接都要使用会话内存(关于会话内存,文章后面有涉及). 2.max_packet_allowed 最大数据包大小,通常等于你需要在一个大块中返回