Mysql学习笔记（十一）- Innodb log机制和优化

在上一片文章中，我们说innodb的内存优化主要是通过多buffer pool size的优化，首先是lru链表的young和old区，以及之间的数据页的迁移的时间优化。因为我们执行一条sql，其实是在内存中做这件事情的，做完毕之后会加入的dolog中，然后后台线性会将其写入磁盘，所有这个缓存的大小就成为性能的重要影响因素。除此之外，调整old区的大小也可以让热点数据不易从buffer pool中淘汰，我们可以通过设置innodb_old_blocks_pct去设置old区的比例。当然我们也可以通过old区的最小淘汰时间innodb_old_blocks_time来让更多的数据能够留在热点区域内。当然由于线程对innodb缓存池的访问是互斥的，所以并发比较大的时候，容易出现瓶颈，所以在这里可以设置innodb_buffer_instances，这样buffer_pool_size就会平分到每个instance上。对于长时间不用或者要被淘汰的数据页，也有操作的方式，其提供了innodb_io_capacity和innodb_max_dirty_pages_pct分别表示一秒需要刷新到磁盘的io次数和要进行数据回写磁盘的触发上线，默认值是75%。

在上片文章中，由于时间紧张，还有一点内容没有写。这里大概进行说明一下。

7.doublewrite

在进行脏数据页的刷新的时候innodb采用的是双写策略，为什么这么做的原因是mysql的数据页是16K，但是操作系统的是4K，无法保证mysql缓存的数据一致性，而innodb的redo日志只记录数据页改变的部分。比如要将缓存页中的4K写入磁盘，然后断电，就会出现数据页无法恢复的问题，这种问题还是和redo日志的解决方案一样。在系统表磁盘空间开辟2M，用来存储脏页内容，然后写入redo日志进行最后再写入到磁盘。在恢复的时候会先通过系统表空间进行恢复。

show global varablis kile ‘%doublewrite%’

8.调整排序缓存区

通过show global status看到sort_merge_passes的值比较大的时候，可以调整sort_buffer_size来增大排序区，可以改善order by和group子句的性能。如果无法通过索引进行连接操作的查询可以通过增大join_buffer_size的值。但上述两个参数都是基于会话的。

今天主要学习一下，innodb log的机制和相关的优化

我们在学习数据库原理的时候学习过redo log的作用，主要是数据恢复的作用。那么今天我们主要还是详细的学习一下。

更新数据的时候，innodb的主要操作流程为：

1，将数据读入到buffer pool，并对相关数据加独占锁

2，将undo信息写入到undo表空间的回滚段中

3. 更新缓存页中的数据，并将更新记录写入到redo buffer

4. 提交时候，根据innodb_flush_log_at_trx_commit的设置，用不同的方式将redo buffer中的更新记录刷新到innodb log file中。

5. 后台io线程择机更新到磁盘

可以通过

show engine innodb status

查看日志写入的情况

这里的lsn表示日志的编号

新的lsn=旧lsn+日志大小

通过上诉描述，我们大概知道了调优的方向，也就是pool buffer、log buffer、redo日志和刷新的commit参数等。

1.innodb_flush_log_at_trx_commit的设置

该参数用来描述日志文件写入到磁盘的操作时机。0表示在事务提交时不会立即触发写入磁盘，而是每秒进行一次写入磁盘操作。如果为1，表示立即写入到redo日志并回写到日志文件然后刷新到磁盘。如果为2，表示会立即将redo操作写入到日志，但是每一秒执行一次写入到磁盘的操作。该值默认为1，也是最安全的一种，为0或者2可能会再数据库崩溃的最后一秒内的数据消失。

2.设置log file size，控制检查点

因为日志文件不能无限的写，mysql在日志写满之后会重新创建日志文件，也就是说日志的切换会导致数据库恢复的检查点的变化，会导致innodb缓存页的小批量刷新，会降低性能。所以合理设置log file size对于性能由一定的好处。一般来说每半个小时写满一个日志文件比较合适。可以通过查看每分钟的日志量，然后计算30分钟的日志量，然后再设置该值。

3.调整innodb_log_buffer_size

该值决定日志缓存池的大小，默认为8M，对于具有大量更新记录的事务，可以增大该值，减少对磁盘的写入，从而提升事务处理的能力。