一、在共享表空间(系统表空间)中,innodb会维护一些系统信息:

1、Internal data dictionary

2、Rollback segments

3、undo space

4、insert buffer

5、Double write buffer

6、MySQL replication info

二、Innodb索引结构:

1、所有的Innodb索引都是B+树结构,索引记录放在叶子节点.

2、data page页默认16kb,当有新索引记录写入时,会预留1/16(1kb)空闲空间用于以后的索引记录写入

3、当索引记录按照顺序(正序、倒叙)写入时,最理想的结果是索引页能填充15/16；如果随机无序写入,则索引页填充率可能会从1/2-15/16,当fill factor(填充因子）小于1/2时,会开始收缩数据页,释放空闲空间

4、5.6开始修改page size,支持4k、8k、16k、初始化时指定,后续无法修改,不同page size的实例间也不能直接迁移使用.5.7开始扩展到32kb、64kb。

5、innodb表为IOT,采用了B+树类型,故每个页面至少存储2行数据,如果行过大则会产生溢出；

6、理论上Innodb表中varchar(65535)的字节,但对于Innodb其实上限为65532,且该值为表所有varchar列长度总和;对于utf-8字符集,一个字符集占3个字节,则其上限又缩小为1/3;如果强制创建varchar(65535)的字段,在SQL_mode部位restricted的情况下,其会被隐式转换为mediumtext;不论是varchar还是blob/text,只要保证一个16k的页面能容下2行数据,应该不会溢出;而一旦行溢出,字段前768字节(Antelope格式)依旧存放于当前页面,数据一般使用B-tree Node页,而溢出的行存放于Uncompress Blob页;而barracuda采用了完全行溢出,即只保留字段的前20字节.

7、当file format为Antelope时,支持:REDUNDANT、COMPACT这两种行格式.发生行溢出时,在当前page会存储前768字节,多余的放在off-page.

8、当file format为Barracuda时,支持:compress,dynamic这两种格式,并兼容前两种,在当前page会存储前20字节,多余的放在off-page.

9、在多版本方式下,当你使用SQL语句删除某一行的时候,该行并不会马上从数据的物理文件上移除.只有当Innodb能够删除掉更新日志记录的时候,那些行及其对应的索引记录才会真正从物理上删除掉.这个移除操作称为purge

option:

innodb_file_format_max = barracuda

innodb_file_format = barracuda

要设置成一样,并且同时最好把innodb_file_format_check设置为1(默认值也是1）,避免Innodb在启动过程中需要恢复数据,因为没有检查而写入不支持格式的表中,导致数据丢失.

当file format 为Antelope时,支持REDUNDANT、COMPACT这两种行格式

当file format为Barracuda时,支持:COMPRESS、DYNAMIC这两种行格式,并且兼容前两种

【重点】关于Innodb行格式的选择

1、compact格式消耗磁盘空间和备份耗时最下,redundant相比之下略大一些.建议采用默认compact格式,适用于绝大数场景;

2、dynamic及compressed格式下.大字段数据存储在off-page中,如果不需要读取大字段效率较高,否则效率很差.因此count(*)之类的操作相对快,但进行备份需要全表扫描时,其代价反而更高;适用于很多大字段但无需经常被更新且备份的表.

三、消除碎片

1、随机方式插入新数据,可能导致辅助索引产生大量的碎片,意思是索引page和索引顺序不接近,或者有大量的空洞. 执行alter table xxx engine = Innodb;可以重新创建表空间,消除碎片、或者备份数据表,删掉,重新导入

四、回收表空间

1、共享表空间无法在线回收，共享表空间想要回收的话,需要全部Innodb导出、删除、导入,数据表空间用上面方法即可,或者直接清空不需要保存的历史表,临时表 truncate table

五、Checkpoint

1、innodb会批量的把buffer pool中的脏页以及redo log 刷新到磁盘,称之为检查点.

2、并不是在一次刷新中刷新所有的内容,因为这样会降低mysql的性能,甚至无法提供服务

3、在恢复的过程中,innodb会向前扫描实务日志,把这些脏数据刷新到磁盘中

4、innodb循环使用它的事务日志,所以旧的日志必然在未来某一时刻被覆盖,innodb必须保证,在旧日志被覆盖之前,与这些旧日志条目相关的脏数据都被刷新到了磁盘

5、如果这一点不能保证,万一服务器crash,buffer pool中的脏页就永远也无法恢复了.

6、所以在切换日志的时候,innodb必然会做检查点,把所有的脏页都刷新到磁盘

7、从这个意义上,innodb的事物日志越大,节省的磁盘IO越多,对系统性能越好.但是crash后恢复的时间肯定会变长

8、innodb的检查点每隔几秒钟就会做一次

9、只是经过日志切换后,在日志被冲用前,该日志的内容必须被全部刷新到磁盘,否则系统就会hung住

10、尝试用大一点的事务日志,可以减少检查点过程中写磁盘的次数(之所以节省,是因为IO的合并)

Checkpoint触发条件

1、每1秒,若buffer pool中的脏页比率超过了srv_max_buf_pool_modified_pct = 75,则进行checkpoint,刷脏页, flush PCT_IO(100)的dirty pages = 200(参数:innodb_io_capacity 能够对其定义);若采用adaptive flushing,则计算flush rate,进行必要的flush。

2、每10秒,若buffer pool中的脏页比率超过了70%,flush PCT_IO(100)的dirty pages,若buffer pool中的脏页比率未超过70%,flush PCT_IO(10%)的dirty pages = 20;每10s,必定调用一次log_checkpoint,做一次checkpoint

脏页比率 = 需要被flush的页面数/(使用中的页面数+空闲页面数+1)

innodb_adaptive_flushing_lwm —设置redo log flush低水位线,当需要flush的redo log超过这个低水位时,立即强制启用adaptive flushing,即便没有设置使用adaptive flush 机制

innodb_io_capacity = N —-设置Innodb后台进程最大的IO性能指标,列如:从buffer pool中刷新数据页,从insert buffer中合并数据等.默认值200,在繁忙的OLTP模式下,需要适当提高.

innodb_io_capacity_max = N —设置Innodb_io_capacity_在紧急情况下的上限值

innodb_flushing_avg_loops = N —-设置Innodb统计前N个page flush 速率,避免太快flush

【问题】Innodb都有哪些后台进程?

后台线程(15个)

1、master thread(1个)

2、lock monitor thread(1个)

3、error monitor thread(1个)

4、log thread(1个)

5、read/write thread(8个,默认各4个)

6、purge thread(1个)

7、page cleaner thread(1个)

如下source code:

master_thread_main_loop()  loop:  {      //A、每秒需要执行的      for(int i=0;i<10;i++) //sleep 1s  //每秒都要刷新日志缓存到磁盘      {       do log buffer flush to disk;      }      //如果缓存中的脏页比例大于配置中的innodb_max_dirty_pages_pct就刷新innodb_io_capacity个脏页到硬盘      if(last_one_second_iosinnodb_max_dirty_pages_pct)      {          do buffer pool flush 100% innodb_io_capacity dirty page;  }  //如果没有活跃用户或者数据关闭时,就跳入background loop  if (no user activity){      goto background loop;  }  //有必要的话,就空闲1秒  sleep 1 second if necessary;  }    //B、每10秒需要执行的  //如果最后10s内IO小于innodb_io_capacity次,那么就刷新innodb_io_capacity个脏页到磁盘  if(last_ten_second_ios < innodb_io_capacity)  {      do buffer pool flush 100% * innodb_io_capacity dirty page;      //总是合并最多5个插入      do merge at most 5 insert buffer            //总是将日志缓存刷新到磁盘      do log buffer flush to disk;            //总是删除buffer_pool中无用的undo页,一次最多20个      do full purge;  }    if (buf_get_modified_ratio_pct&get;70%)  //如果缓存中脏页比例大于70%,就刷新innodb_io_capacity个脏页到磁盘,否则值只刷新10% * innodb_io_capacity个  {      do buffer pool flush 100% * innodb_io_capacity dirty page;  }  else  {      buffer pool flush 10% * innodb_io_capacity dirty page;  //产生一个检查点      do checkpoint      //返回主循环      goto loop;  }    //backupgroud 循环  backupgroud loop;  {      //总是删除buffer pool中无用的undo页      do full purge                //总是合并innodb_io_capacity个插入缓存      do merge 100% * innodb_io_capacity insert buffer        //如果不空闲,就调回主循环,如果空闲就跳入flush loop  if not idle  {      goto loop:  }  else  {      goto flush loop;      flush loop;      //总是刷新innodb_io_capacity个脏页到硬盘,知道缓存中的脏页比例小于innodb_max_diry_pages_pct      do buffer pool flush 100% * innodb_io_capacity dirty page                if(buf_get_modified_ratio_pct&get;innodb_max_dirty_pages_pct)          {              goto flush loop;          }      //完成刷新脏页的任务后,跳入suspend loop      goto suspend loop;            suspend loop:      //将master线程挂起,等待事件激活      {          suspend_thread()      waiting event      }  }  }

master thread的线程优先级别最高.

其内部几个循环(loop)组成:主循环(loop),后台循环(background loop),刷新循环(flush loop),暂停循环(suspend loop)。

srv_master_thread loops: 8565077 srv_active, 0 srv_shutdown, 1939115 srv_idle

srv_master_thread log flush and writes: 10504192

master thread会根据数据运行的状态在loop,background loop,flush loop和suspend loop中进行切换,loop称为主循环,因为大多数的操作都是在这个循环中,其中有两个部分的操作:每秒的操作和每10秒的操作,loop循环通过thread sleep来实现,这意味着所谓的每一秒一次或者每10秒一次的操作是不精确的.当然,innodb源码中还采用了其他的方法来尽量保证这个频率.

@每秒一次操作包括:

1、日志缓冲(log buffer)刷新到磁盘,即使这个事务还没有提交(总是)

2、合并插入缓冲(insert buffer)可能

3、之多刷新100个innodb的缓冲池(buffer pool)中的脏页(dirty page)到磁盘(可能)

4、如果当前没有用户活动,切刀background loop(可能)

@每10秒操作包括:

1、刷新100个脏页到磁盘(可能)

2、合并之多5个插入缓冲(总是)

3、将日志缓冲刷新到磁盘(总是)

4、删除无用undo页(总是)

5、刷新100个或者10个脏页到磁盘(总是）

6、产生一个检查点(总是)

【重点】关键点:

1、dirty pages不要堆积太多,否则热点数据不能被有效缓存,命中率低,并且瞬间大批量刷新dirty pages时也影响IOPS；

2、undo pages不要堆积太多,否则ibdata1可能暴涨,或者tsp受到影响;

3、checkpoint不要延迟太厉害,否则crash recovery进程很慢;

4、记住最重要的一点,这些后踢进程有条不紊按照固定频率工作着,不要有停滞,也不要太频繁。

本站声明:网站内容来源于网络,如有侵权,请联系我们https://www.qiquanji.com,我们将及时处理。