理解change buffer(insert buffer)

Change Buffer的作用：

• 作用于不在缓存池中的非唯一索引页
• 对该页进行写操作后并不会立刻将该页从磁盘中加载到缓存池，而是在change buffer中记录页的修改，等该页被读取时，再将修改的数据merger到缓存池中；
• 以此来缓解写操作多次随机读的IO消耗；
• 当索引中包含降序索引或者主键中包含降序索引时不会用到change buffer；
• 当change buffer大小超过最大阈值后不会再使用change buffer；

merge策略：

• 被动merge：当用户线程主动发起change buffer中索引页的读取操作时，会被动的将页的修改记录merge到原始索引页中；
• 主要merge：master thread定期将change buffer中的记录随机刷回索引页，如果每秒master thread有空闲I/O能力，则刷5个缓存索引页，每10秒不管I/O压力都会刷5个缓存的索引页记录；

相关参数：

innodb_change_buffer_max_size = 25 #默认25表示change buffer最大可以占用innodb buffer的25%，最大可设置的值为50innodb_change_buffering = all #默认all表示所有的非唯一普通索引页写入都使用change buffer all none #关闭change buffer inserts #缓冲插入操作 deletes #缓冲删除操作 changes #缓冲插入和删除操作 purges #缓冲在后台发生的物理删除操作

相关监控信息：

show engine innodb status\G-------------------------------------INSERT BUFFER AND ADAPTIVE HASH INDEX-------------------------------------Ibuf: size 1, free list len 3077, seg size 3079, 3338271 mergesmerged operations: insert 3679942, delete mark 333798, delete 1737discarded operations: insert 0, delete mark 0, delete 0 #size 1 正在使用的page #free list len 3077 空闲的page #seg size 3079 当前change buffer大小3079*16K #3338271 merges 合并的数目 #insert 3679942 通过change buffer插入的数目 #delete mark 333798 通过change buffer删除的数目 #delete 1737 通过change buffer purge的数目 #change buffer的效果=merges/(insert+delete mark+delete)结果越小说明change buffer对性能提升越有利mysql> select NAME,COUNT,MAX_COUNT,MIN_COUNT,AVG_COUNT,COMMENT from information_schema.innodb_metrics where name like '%ibuf%';+-----------------------------------------+---------+-----------+-----------+--------------------------+-------------------------------------------------------------+| NAME         | COUNT | MAX_COUNT | MIN_COUNT | AVG_COUNT    | COMMENT              |+-----------------------------------------+---------+-----------+-----------+--------------------------+-------------------------------------------------------------+| buffer_page_read_index_ibuf_leaf  |  0 |  NULL |  NULL |      NULL | Number of Insert Buffer Index Leaf Pages read    || buffer_page_read_index_ibuf_non_leaf |  0 |  NULL |  NULL |      NULL | Number of Insert Buffer Index Non-Leaf Pages read   || buffer_page_read_ibuf_free_list   |  0 |  NULL |  NULL |      NULL | Number of Insert Buffer Free List Pages read    || buffer_page_read_ibuf_bitmap   |  0 |  NULL |  NULL |      NULL | Number of Insert Buffer Bitmap Pages read     || buffer_page_written_index_ibuf_leaf  |  0 |  NULL |  NULL |      NULL | Number of Insert Buffer Index Leaf Pages written   || buffer_page_written_index_ibuf_non_leaf |  0 |  NULL |  NULL |      NULL | Number of Insert Buffer Index Non-Leaf Pages written  || buffer_page_written_ibuf_free_list  |  0 |  NULL |  NULL |      NULL | Number of Insert Buffer Free List Pages written    || buffer_page_written_ibuf_bitmap   |  0 |  NULL |  NULL |      NULL | Number of Insert Buffer Bitmap Pages written    || ibuf_merges_insert      | 3681684 | 3681684 |  NULL |  0.9494142111810469 | Number of inserted records merged by change buffering  || ibuf_merges_delete_mark     | 333962 | 333962 |  NULL |  0.08612044618561636 | Number of deleted records merged by change buffering  || ibuf_merges_delete      | 1740 |  1740 |  NULL | 0.0004487024762187688 | Number of purge records merged by change buffering   || ibuf_merges_discard_insert    |  0 |   0 |  NULL |      0 | Number of insert merged operations discarded    || ibuf_merges_discard_delete_mark   |  0 |   0 |  NULL |      0 | Number of deleted merged operations discarded    || ibuf_merges_discard_delete    |  0 |   0 |  NULL |      0 | Number of purge merged operations discarded    || ibuf_merges        | 3340164 | 3340164 |  NULL |  0.8613447458487284 | Number of change buffer merges        || ibuf_size        |  1 |  699 |  NULL | 0.0000002578749863326257 | Change buffer size in pages         || innodb_ibuf_merge_usec     |  0 |  NULL |  NULL |      NULL | Time (in microseconds) spent to process change buffer merge |+-----------------------------------------+---------+-----------+-----------+--------------------------+-------------------------------------------------------------+

change buffer局限：

• 需要作用于非唯一索引，因为非唯一索引不要校验数据重复；
• change buffer会占用一定的innodb buffer空间；　　

　

原文转载：http://www.shaoqun.com/a/522302.html

跨境电商：https://www.ikjzd.com/

俄罗斯灰色清关：https://www.ikjzd.com/w/1409

cbo：https://www.ikjzd.com/w/2670

ChangeBuffer的作用：作用于不在缓存池中的非唯一索引页对该页进行写操作后并不会立刻将该页从磁盘中加载到缓存池，而是在changebuffer中记录页的修改，等该页被读取时，再将修改的数据merger到缓存池中；以此来缓解写操作多次随机读的IO消耗；当索引中包含降序索引或者主键中包含降序索引时不会用到changebuffer；当changebuffer大小超过最大阈值后不会再使用chang
海维：海维
parser：parser
我的广告费真的进入姐夫的个人钱包了？：我的广告费真的进入姐夫的个人钱包了？
告急！多国港口关闭、码头拥堵，全球海运一团糟！：告急！多国港口关闭、码头拥堵，全球海运一团糟！
【今日外贸】JioMart飞速崛起，日均活跃用户高达100万！：【今日外贸】JioMart飞速崛起，日均活跃用户高达100万！