Innodb buffer pool 小解
in BlogDatabaseMySQL with 0 comment

Innodb buffer pool 小解

in BlogDatabaseMySQL with 0 comment

作者:圣地玛雅
热衷于MySQL和SQL Server,NoSQL等数据库技术,欢迎加入技术交流群:291519319 SQL优化群:659336691(刚创建,群里只讨论SQL优化)

简介

INNODB维护了一个缓存数据和索引信息到内存的存储区叫做buffer pool,他会将最近访问的数据缓存到缓冲区。通过配置各个buffer pool的参数,我们可以显著提高MySQL的性能。
INNODB的buffer pool是基于LRU算法来实现的,我们可以简单了解以下LRU算法:

least recently used (LRU),INNODB管理buffer poll是将buffer pool作为一个list管理,基于LRU算法的管理。当有新的页信息要读入到buffer pool里面的时候,buffer pool就将最近最少使用的页信息从buffer pool当中驱逐出去,并且将新页加入到list的中间位置,这就是所谓的中点插入策略。一般情况下list 头部存放的是热数据,就是所谓的young page(最近经常访问的数据),list尾部存放的就是old page(最近不被访问的数据)。这个算法就保证了最近经常使用的page信息会被保存在最近访问的sublist,相反的不被经常访问的就会保存在old sublist。而old sublist当中的page信息都是会被在新数据写入的时候被驱逐的。
LRU算法有以下的标准算法:

一般情况下,页信息会被查询语句立马查询到而被移动到new sublist,这就意味着他们会在buffer pool里面保留很长一段时间。表扫描(包括mysqldump或者没有where条件的select等操作)等操作将会刷入大量的数据进入buffer pool,同时也会将更多的buffer pool当中的信息刷出去,即使这个操作可能只会使用到一次而已。同样的如果 read-ahead后台进程读入大量数据的情况下也是会造成buffer pool大量高频的刷新数据页,但是这些操作是可控的,下面3,4会说得到。read-ahead操作简单说一下就是MySQL的一个后台预读进程,能够保证MySQL预读入数据进入buffer pool当中。

参数一览表

下面先看下innodb buffer pool的一些相关参数信息,后面会详细解释一下:

innodb_buffer_pool_size这个值是设置innodb buffer pool的总大小

innodb_buffer_pool_chunk_size是innodb buffer pool的执行单元 chunk size的大小。这里面有个关系要确定以下,最好按照这个设置

innodb_buffer_pool_size=innodb_buffer_pool_chunk_size innodb_buffer_pool_instancesN(N>=1)

innodb_buffer_pool_instances是设置innodb buffer pool实例的个数,每一个实例都有自己独立的list管理buffer pool。

innodb_old_blocks_pct是默认innodb buffer pool中点的位置,默认值是37,最大100,也就是我们所谓的3/8的位置,可以自己设置

innodb_old_blocks_time设置保留在buffer pool里面的数据在插入时候没有被改变list位置的时候的保存时间

innodb_read_ahead_threshold参数控制MySQL何时进行预读,也可以控制MySQL预读数据时候对于数据的敏感度,如果buffer pool里面存储的数据页的频繁值大于innodb_read_ahead_threshold的值,INNODB就会启动一个异步的预读操作

innodb_random_read_ahead默认是disabled,是控制预读方式的参数,开启的话将不使用线性预读而是使用随机预读

innodb_adaptive_flushing指定是否动态自适应刷新脏页到盘,这个是MySQL根据负载自己决定的。不过还是尽量不要设置

innodb_adaptive_flushing_lwm关闭adaptive_flushing的话才会有用,用来标记redo log的百分比。因为很少用,没过多看

innodb_flush_neighbors控制是否从刷新buffer pool或者刷新buffer pool当中的脏页重新整理到一个页。

innodb_flushing_avg_loops参数为innodb保存innodb buffer pool前几次的冲洗快照

innodb_lru_scan_depth控制LRU算法的一个参数,用来控制buffer pool后台进程page_cleaner 刷新脏页的位置

innodb_max_dirty_pages_pct参数会让innodb buffer pool刷新数据而不让脏数据的百分比超过这个值。

innodb_max_dirty_pages_pct_lwmINNODB会自动维护后台作业自动从buffer pool当中清除脏数据,当buffer pool中的脏页占用比 达到innodb_max_dirty_pages_pct_lwm的设定值的时候,就会自动将脏页清出buffer pool

innodb_buffer_pool_filename指定文件名字

innodb_buffer_pool_dump_at_shutdown配置的InnoDB如何保留当前的缓冲池的状态,以避免在服务器重新启动后,还要经历一个漫长的暖机时间。

innodb_buffer_pool_load_at_startup指定此参数启动,数据库重启以后会自动暖机,读入buffer pool重启前保存的信息

innodb_buffer_pool_dump_now和innodb_buffer_pool_load_now当数据库已经提起来的时候,我们忘了以前指定,也可以指定马上恢复

innodb_buffer_pool_dump_pct设置以下设置设置恢复buffer pool中多少数据

innodb_buffer_pool_load_abort终止buffer pool加载,可以指定负载运行

innodb buffer pool的一些详细的解读

buffer pool size设置和生效过程:

理想情况下,buffer pool size应该设置的尽可能大,这样就可以减少进程的额外分页,当buffer pool size设置的足够大的时候,整个数据库就相当于存储再内存当中,当读取一次数据到buffer pool size以后,后续的读操作就不用在进行磁盘读。

下面我们看一下buffer pool size的设置方式:

当数据库已经启动的情况下,我们可以通过在线调整的方式修改buffer pool size的大小。通过以下语句:

SET GLOBAL innodb_buffer_pool_size=402653184;

当执行这个语句以后,并不会立即生效,而是要等待所有的事物全部执行成功以后才会生效,新的连接和事物必须等待其他事物完全执行成功以后buffer pool size设置生效以后才能够连接成功,不然会一直处于等待状态。期间buffer pool size要完成碎片整理,去除缓存page等等操作。在执行增加或者减少buffer pool size的操作是,操作会作为一个执行块执行,innodb_buffer_pool_chunk_size的大小会定义一个执行块的大小,默认的情况下,这个值是128M。buffer pool size的大小最好设置为innodb_buffer_pool_chunk_size innodb_buffer_pool_instances的整数倍,而且是大于等于1。如果你的机器配置的大小不是整数倍的话buffer pool size的大小是会自适应修改为innodb_buffer_pool_chunk_size innodb_buffer_pool_instances的整数倍,会略小于你配置的buffer pool size的大小。
比如以8G为例:mysqld --innodb_buffer_pool_size=8G --innodb_buffer_pool_instances=16,然后innodb_buffer_pool_instances=16的大小刚好设置为16.是一个整数倍的关系。而且innodb_buffer_pool_chunk_size的大小也是可以在my.cnf里面指定的。还有一种情况是innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances大于buffer pool size的情况下,innodb_buffer_pool_chunk_size 也会自适应为buffer pool size/innodb_buffer_pool_instances,可见MySQL的管理还是非常的只能的。
如果我们要查看buffer pool的状态的话,SHOW STATUS WHERE Variable_name='InnoDB_buffer_pool_resize_status可以帮我们查看到状态。我们可以看一下增加buffer pool的时候的一个过程,再看一下减少的时候的日志,其实还是很好理解的,我们可以看成每次增大或者减少buffer pool的时候就是进行innodb_buffer_pool_chunk的增加或者释放,按照innodb_buffer_pool_chunk_size 设定值的大小增加或者释放执行块。

增加的过程:增加执行块,指定新地址,将新加入的执行块加入到free list(控制执行块的一个列表,可以这么理解)。

减少的过程:重新整理buffer pool和空闲页,将数据从块中移除,指定新地址。

Buffer Pool Instances

在64位操作系统的情况下,可以拆分缓冲池成多个部分,这样可以在高并发的情况下最大可能的减少争用。下面我们看一下怎么配置Buffer Pool Instances

配置多个Buffer Pool Instances能在很大程度上能够提高MySQL在高并发的情况下处理事物的性能,优化不同连接读取缓冲页的争用。我们可以通过设置 innodb_buffer_pool_instances来设置Buffer Pool Instances。当InnoDB buffer pool 足够大的时候,你能够从内存中读取时候能有一个较好的性能,但是也有可能碰到多个线程同时请求缓冲池的瓶颈。这个时候设置多个Buffer Pool Instances能够尽量减少连接的争用。这能够保证每次从内存读取的页都对应一个Buffer Pool Instances,而且这种对应关系是一个随机的关系。并不是热数据存放在一个Buffer Pool Instances下,内部也是通过hash算法来实现这个随机数的。每一个Buffer Pool Instances都有自己的free lists,LRU和其他的一些buffer pool的数据结构,各个Buffer Pool Instances是相对独立的。

innodb_buffer_pool_instances 的设置必须大于一才算得上是多配置,但是这个功能起作用的前提是innodb_buffer_pool_size的大小必须大于1G,理想情况下innodb_buffer_pool_instances的每一个instance都保证在1G以上。

innodb buffer pool LRU原理

你可以频繁的往buffer pool里面读取数据当backup或者report的时候,不用有太多的顾虑。INNODB采用的是一种不是像LRU那么严格的方法来保证将最近访问的数据写入到buffer pool里面,并且最大可能的降低减少数据的带入量。这个语句是全表扫描或者以后这个数据将不会再被访问到,但是缓冲数据还是会写入到buffer pool里面。

新写入的数据会被插入到LRU list的中间位置,默认会插入到从list尾部算起来的3/8的位置,当这些写入的数据在buffer pool中被第一次访问的时候,在list中的位置就会向前移动,这样其实就会在list保留两个位置,老的位置并不会被立即清除,直到老的LRU list的位置被标记为OLD的时候,才会在下一次插入数据的时候被作为牺牲者清除掉。

我们本身是可以指定插入LRU list的位置,并且也可以设置当索引扫描或者是全表扫描的时候是不是采用这个相同的优化方法。 innodb_old_blocks_pct这个参数设置的是插入的位置,默认的值是37,我们可以设置的值是5-95之间,其余部分并不用来保存热数据。但是还有一个严重的问题就是当一个全表扫描或者索引的扫描经常被访问的时候,就会存储很大的数据到buffer pool里面,我们都知道这是很危险的一件事。所以MySQL给我们以下参数来设置保留在buffer pool里面的数据在插入时候没有被改变list位置的时候的保存时间innodb_old_blocks_time,单位是毫秒,这个值的默认值是1000。如果增大这个值的话,就会让buffer pool里面很多页信息变老的速度变快,这个很好理解把,因为这些数据会不会很快被内存中擦除的话,就会变成热数据而挤掉原有缓存的数据。

以上的两个参数都是可以动态设置的,当然也可以在my.cnf里面设置。当然设置这些前一定要对机器配置,表信息,负载情况有充分的了解才能进行设置,生产库尽量不要随便修改。如果OLTP系统中有大量的大查询的话,设置innodb_old_blocks_time能够较大的提供系统的稳定性。如果当一个大查询很大不足够存储到buffer pool当中的时候,我们可以指定innodb_old_blocks_pct的值小一点,以保证这些数据只会被读取一次,比如说设置为5的时候,就就限制了一次读取数据最多只能被读取到buffer pool当中5%。一些小表并且是经常访问到的数据的话就可以适当设置较大的值,比如50。当然设置这两个值的时候一定要建立在你充分了解你的数据负载的基础上,不然千万不要乱改。

innodb buffer pool预读

我们可以控制MySQL何时以何种方式预读数据进入buffer pool。
预读就是IO异步读取多个页数据读入buffer pool的一个过程,并且这些页被认为是很快就会被 读取到的,当需要读取这些数据的时候就会将需要的页放在一个区当中,INNODB就是通过两次预读的方式来提高IO读写的性能。

线性预读:能够预测将有那些数据很快能被读到的一种技术,因为buffer pool中的页数据是顺序访问的。我们可以通过设置innodb_read_ahead_threshold参数控制MySQL何时进行预读,也可以控制MySQL预读数据时候对于数据的敏感度,如果buffer pool里面存储的数据页的频繁值大于innodb_read_ahead_threshold的值,INNODB就会启动一个异步的预读操作,innodb_read_ahead_threshold的值可以设置为0-64的任何一个值。默认值是56,值设置的越高就会造成检索更加严格。当设置为8的时候,只有小于8个页数据被读进buffer pool中才会被检索。

随机预读:随机预读能够将在内存当中的将被读取到的页信息很快的组织到一个区中,而且这些页面的读取顺序不用分顺序,INNODB能够很快调度buffer pool当中相似的很多页的信息,并且发出请求。这些页可能并不是连续的,要想使用这个功能就要设置innodb_random_read_ahead 这个参数为ON。

通过SHOW ENGINE INNODB的以下输出信息能够帮我们定位到是否要打开这个参数:

Innodb_buffer_pool_read_ahead

Innodb_buffer_pool_read_ahead_evicted

Innodb_buffer_pool_read_ahead_rnd

Responses
点击这里给我发消息
嘿!有什么能帮到您的吗?