8.5.2 Optimizing InnoDB Transaction Management
优化InnoDB 事务处理,找到理想的平衡在性能负载和你的服务器的负载。
比如,一个应用可能遇到性能问题如果它每秒提交了数千次,和不同的性能问题如果它每2-3个小时提交。
默认的MySQL 设置为AUTOCOMMIT=1 ,可以强加性能限制在一个繁忙的数据库上.
在实践中,把几个相关的DML操作到一个单独的事务,通过执行SET AUTOCOMMIT=0
或者START TRANSACTION 豫剧, 跟着一个COMMIT 语句在做所有的改变后。
InnoDB 必须 flush 日志到磁盘在每次事务提交,如果事务修改了数据库。
当每个改变后面跟着一个commit（如默认的autoCommit设置），存储设备的I/O 吞吐量。
另外, 对于交易只有一个单独的SELECT 语句, 打开AUTOCOMMIT 帮助InnoDB 来重新组织read-only 事务
和 优化它们。See Section 8.5.3, “Optimizing InnoDB Read-Only Transactions” for requirements.
避免性能回滚在每次inserting, updating, or deleting大量记录。
如果一个大事务是弄慢了server性能, 回滚会造成更严重的问题,
可能几倍于原始的DML操作。 kill数据库进程没有帮助, 因为回滚在数据库启动时又运行
尽量减少这个问题发生的几率：
增加 buffer pool 的大小,可以让所有的DML 操作可以被cached 相比立即写入到磁盘。
Set innodb_change_buffering=all 所有的更新和删除操作是被缓存除了INSERT操作
考虑执行COMMIT 语句定期的在大的DML操作
一旦失控的回滚操作发生,增加buffer pool,rollback 变的CPU-bound和运行的很快,
或者kill server 和重启带innodb_force_recovery=3,as explained in Section 14.15.1, “The InnoDB Recovery Process”
.
这个问题在在MySQL 5.5和更高的版本变的不重要,因为默认的设置innodb_change_buffering=all
允许耿勋和删除操作 被cache在内存里,让它们快速执行在第一个地方,
如果需要 也可以快速回滚。确保使用这个参数设置在每个服务器上,处理长的事务有很多的插入,更新和删除
如果你能承受 最后提交的一些事务的丢失如果一个crash 发生,
你可以设置innodb_flush_log_at_trx_commit parameter to 0,默认为1
mysql> show variables like ‘%innodb_flush_log_at_trx_commit%’;
+——————————–+——-+
| Variable_name | Value |
+——————————–+——-+
| innodb_flush_log_at_trx_commit | 1 |
+——————————–+——-+
1 row in set (0.00 sec)
InnoDB 尝试每秒flush 日志,尽管flush不是强制的。
设置innodb_support_xa to 0,会降低磁盘flush的次数 由于同步磁盘和binary log.
当修改或者删除记录,记录和相关的undo logs 不是立即物理删除的,
或者甚至在事务提交后。 undo里老的数据是被保留的直到事务很早开始的或者并发结束的,
因此那些事务可以访问先前的状态或者修改或者删除的记录。
因此,长时间运行的事务可以防止InnoDB 清理数据,被不同会话修改
当记录被修改或者删除在一个长时间运行的事务里, 其他的会话使用
READ COMMITTED and REPEATABLE READ 隔离级别需要做更多的工作来重建老的数据 如果它们读取相同的记录。
当一个长时间运行的事务修改一个表,查询针对的表从其他会话不使用覆盖索引技术。
查询通常检索所有的数据从一个 secondary index, 代替的是寻找相应的值从表数据。