innodb log 机制及优化

更新数据,innodb 内部操作流程:

1. 将数据写入innodb buffer pool,并对相关记录加独占锁.
2. 将undo 信息写入undo 表空间的回滚段中.
3. 更改缓存页中的数据,并将更新记录写入redo buffer 中.
4. 提交时,根据innodb_flush_log_at_trx_commit的设置,用不同的方式将redo buffer 中的更新记录刷新到innodb redo log file 中.然后释放独占锁.
5. 最后,后台io线程根据需要择机将缓存中更新过的数据刷新到磁盘文件中.
6. 通过show engine innodb status 命令查看当前日志的写入情况.

log sequence number xxxxxx 上次数据页修改还没有刷新到日志文件的lsn号.
log flu shed up to xxxxx 上次成功操作,已经刷新到日志文件中的lsn号.
last checkpoint at xxxxx 上次检查点成功完成时的lsn号,意味着恢复的起点.

innodb_flush_log_at_trx_commit的设置.

0 在事务提交时,innodb 不会立即出发将缓存日志写到磁盘文件的操作,而是每秒触发一次缓存日志回写的操作,并调用操作系统fsync 刷新io缓存.
1 在事务提交时,innodb 立即将缓存中的redo日志写到日志文件,并调用操作系统fsync 刷新io缓存.
2. 在事务提交时,innodb 立即将缓存中的redo日志写到日志文件,并不会马上调用操作系统fsync 刷新io缓存.而是每秒只做一次磁盘io缓存刷新的操作.

0 最不安全,最快.1 安全. 2 数据库崩溃,数据不会丢失.

设置log file size,控制检查点.

1. 当日志文件写满后,innodb 会自动切换到另一个日志文件,但切换是会出发数据库检查点,这将导致innodb 缓存脏页的小批量刷新,会明显降低innodb的性能. 平均半小时写满一个日志文件比较合适.
2. 计算innodb 每分钟产生的日志量;

	pager grep -i 'log sequence number';
	show engine innodb status \G select sleep(60); show engine innodb status \G
	nopager
	select  round((xxx-xxx)/1024/1024) as MB

半小时日志量=30*19MB=570MB

innodb_log_file_size 大小至少应该512MB.

调整innodb_log_buffer_size.

innodb_log_buffer_size 决定innodb redo日志缓存池的大小.默认8M.对于可能产生大量更新记录的大事物,增加innodb_log_buffer_size的大小,可以避免innodb 在事务提交前就执行不必要的日志写入磁盘操作.