更新数据,innodb 内部操作流程:
- 将数据写入innodb buffer pool,并对相关记录加独占锁.
- 将undo 信息写入undo 表空间的回滚段中.
- 更改缓存页中的数据,并将更新记录写入redo buffer 中.
- 提交时,根据innodb_flush_log_at_trx_commit的设置,用不同的方式将redo buffer 中的更新记录刷新到innodb redo log file 中.然后释放独占锁.
- 最后,后台io线程根据需要择机将缓存中更新过的数据刷新到磁盘文件中.
- 通过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,控制检查点.
- 当日志文件写满后,innodb 会自动切换到另一个日志文件,但切换是会出发数据库检查点,这将导致innodb 缓存脏页的小批量刷新,会明显降低innodb的性能. 平均半小时写满一个日志文件比较合适.
- 计算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 在事务提交前就执行不必要的日志写入磁盘操作.