Mysql学习笔记（十) Innodb内存优化

Mysql学习笔记（十) Innodb内存优化

在上一篇文章中我们说myisam的内存优化主要有四点，主要是设置索引缓存的大小key_buffer_size、使用多个索引缓存、调整中点插入策略set global key_cache_division_limit=70、调整read_buffer_size和排序空间read_rnd_buffer_size的大小。这里要注意的read_buffer_size和read_rnd_buffer_size是会话缓存，所以要合理分配。否则容易用掉很多内存。

那么今天主要学习一下innodb的内存优化。

1.innodb缓存的机制

innodb是用一块内存做io缓存池，这块缓存不仅用来缓存索引，而且用来缓存数据，在内部innodb有free list、flush list、和lru list组成。而lru list是innodb正在使用的缓存块，是innodb buffer pool的核心。

innodb使用的lru算法和myisam的中点插入策略类似。主要是将链表分为young和old区，数据从磁盘读入之后会将该缓存插入到lru的中点，也就是old区的头部，经过一定的时间访问(innodb_old_blocks_time)，会转移到young区头部转移，也就是lru链表的头部，随着时间的推移，young和old中较少访问的缓存块将从各自链表的头部逐渐向尾部移动。最后逐渐淘汰掉。

脏页的刷新存在于flush list和lru list这两个链表，lru上也存在可以刷新的脏页，这里默认是直接刷新的，lru链表淘汰的数据会立即进入free list中。我们可以通过调整innodb buffer pool的大小改变young和old的分配比例，控制脏缓存的 刷新，使用多个innodb缓存池的方法优化innodb的性能。

2.innodb_buffer_pool_size的设置

innodb_buffer_pool_size决定innodb表类型的数据和索引数据的最大缓存区大小。所以innodb_buffer_pool_size越大，缓存的命中率越高，io越少，性能也就越好。在专用的数据库服务器上，可以将其值设置为80%，但是要避免设置的过大而导致页交换。

我们可以通过命令设置和查看buffer_pool的大小

select version();set global innodb_buffer_pool_size=15032385536;show VARIABLES like "%buffer_pool%";

我们也可以通过

show status like 'Innodb_buffer_pool_%';

来查看mysql innodb的缓存情况。

通过上述参数，我们可以根据innodb的缓存命公式查看缓存的命中情况

(1-innodb_buffer_pool_reads/innodb_buffer_size_request)*100

如果命中率太低，可以扩展内存，增加iinnodb_buffer_pool_size的空间。

3.调整old区大小

在lru中，old区的比例是可以设置的，范围在5-95之间，模式是37（3/8），可以通过命令进行查看

show global like ‘%innodb_old_blocks_pct%’

在没有大表扫描的情况下，并且数据多为频繁使用的数据时，我们可以增加innodb_old_blocks_pct的值，并且减小innodb_old_blocks_time的值。让数据页能够更快和更多的进入的热点数据区。

4.调整innodb_old_blocks_time的值

innodb_old_blocks_time表示数据从lru中点移动到young区的最小时间，增大该值，可以让更多的数据留在old区，避免热数据被淘汰。

5.调整缓存池数量，减少内部缓存池数据结构的争用

mysql的内部线程都buffer pool的访问是互斥的，所以高并发情况下有一定问题。为了解决这个问题，innodb中引入了innodb_buffer_pool_instances的配置。对于较大的缓存池，可以适当增大该参数的值。

6.控制innodb_buffer刷新

我们知道在jvm中垃圾回收的时候会产生一些内存碎片，那么innodb的刷新也会产生，因为每个数据页的大小是16K，所以难免会产生垃圾碎片，但是这些垃圾碎片还是有数据的，所以innodb的后台线程就会将脏的缓存页写回到磁盘。这个过程叫做缓存刷新。

innodb_max_dirty_pages_pct表示缓存脏页的最大比例，默认是75%，当达到或者超过这个值得时候就会触发缓存刷新。

innodb_io_capacity表示磁盘系统的io能力，默认值为200，表示每秒完成的io次数。对于固态硬盘可以适当增加，能力比较弱的磁盘可以适当降低。

这两个参数主要表示的是当脏页比例达到多少的时候，一次性将多少个已经完成的缓存页刷新到磁盘。