优化InnoDB
事务处理,在事务特性的性能开销和服务器的工作负载之间找到理想的平衡。例如,如果一个应用程序每秒提交数千次,那么它可能会遇到性能问题,而如果它每2-3小时才提交一次,则会遇到不同的性能问题。
MySQL的默认设置
自动提交= 1
可以对繁忙的数据库服务器施加性能限制。在实际情况下,将几个相关的数据更改操作包装成一个事务,通过发出设置自动提交= 0
或者一个开始事务
语句,后跟a提交
语句。InnoDB
如果事务对数据库进行了修改,则必须在每次事务提交时将日志刷新到磁盘。当每次更改之后都进行提交时(与默认的自动提交设置一样),存储设备的I/O吞吐量会对每秒可能的操作数量设置一个上限。或者,对于只包含单个
选择
声明中,打开自动提交
帮助InnoDB
识别只读事务并优化它们。看到第8.5.3节“优化InnoDB只读事务”为需求。避免在插入、更新或删除大量行的情况下执行回滚。如果一个大事务降低了服务器的性能,回滚它会使问题变得更糟,执行它的时间可能是原始数据更改操作的几倍。关闭数据库进程没有帮助,因为回滚将在服务器启动时再次启动。
要尽量减少这个问题发生的机会:
的大小缓冲池因此,所有的数据更改更改都可以被缓存,而不是立即写入磁盘。
集
innodb_change_buffering =所有
因此,除了插入之外,更新和删除操作也被缓冲。考虑发行
提交
语句在大数据更改操作期间周期性地执行,可能将单个删除或更新操作分解为多个语句,这些语句操作的行数量更少。
若要在发生失控回滚时消除它,请增加缓冲池,以便回滚成为cpu绑定并快速运行,或者关闭服务器并重新启动
innodb_force_recovery = 3
,详见第15.18.2节,“InnoDB恢复”.在默认设置下,预计这个问题不会经常出现
innodb_change_buffering =所有
它允许将更新和删除操作缓存在内存中,使它们的执行速度更快,并且在需要时也可以更快地回滚。请确保在处理具有许多插入、更新或删除的长时间事务的服务器上使用此参数设置。如果您能够承担发生意外退出时最新提交的一些事务的损失,则可以设置
innodb_flush_log_at_trx_commit
参数设置为0。InnoDB
尽管不能保证每秒刷新一次日志。当修改或删除行时,行和关联undo日志不会立即从物理上删除,甚至不会在事务提交后立即删除。旧数据将一直保存到较早启动或并发启动的事务完成为止,以便这些事务可以访问修改或删除的行的以前状态。因此,长时间运行的事务可以阻止
InnoDB
清除被不同事务更改的数据。方法修改或删除长时间运行的事务中的行时,使用
读过承诺
而且可重复读取
隔离级别如果读取相同的行,则必须做更多的工作来重构旧数据。当长时间运行的事务修改表时,从其他事务对该表的查询不会使用覆盖索引技术。通常可以从二级索引检索所有结果列的查询,而不是从表数据中查找适当的值。
如果发现辅助索引页具有
PAGE_MAX_TRX_ID
这太新了,或者如果二级索引中的记录被删除了,InnoDB
可能需要使用聚集索引查找记录。