`

MySQL技术内幕:InnoDB存储引擎读书笔记(上)

阅读更多

第一章、mysql体系结构和存储引擎

  1.1、数据库和实例的区别

  数据库:物理操作系统或其他形式文件类型的集合。在mysql下数据库文件可以是frm,myd,myi,ibd结尾的文件。

  数据库实例:由数据库后台进程/线程以及一个共享内存区组成。

  mysql数据库是但进程多线程的程序。

  1.2、mysql的体系结构

  mysql由连接池组件、管理服务和工具组件、sql接口组建、查询分析器组件、优化器组件、缓存组件、插件是存储引擎、物理文件。

  示意图见书:P4.

  1.3、mysql存储引擎

  1.3.1、innodb存储引擎,特点支持外键、行锁、非锁定读(默认情况下读取不会产生锁)、mysql-4.1开始支持每个innodb引擎的表单独放到一个表空间里。innodb通过使用MVCC来获取高并发性,并且实现sql标准的4种隔离级别,同时使用一种被称成next-key locking的策略来避免换读(phantom)现象。除此之外innodb引擎还提供了插入缓存(insert buffer)、二次写(double write)、自适应哈西索引(adaptive hash index)、预读(read ahead)等高性能技术。

  1.3.2、myisam存储引擎,myisam特点是不支持事物,适合olap应用,myisam表由MYD和MYI组成。mysql-5.0版本之前,myisam默认支持的表大小为4G,从mysql-5.0以后,myisam默认支持256T的表单数据。myisam只缓存索引数据,mysql-5.1.23版本之前无论32、64位操作系统环境下,缓存索引的缓冲区最大只能4G,在之后的版本中,64位系统可以支持大于4G的索引缓冲区。

  1.3.3、NDB存储引擎,2003年mysql从索爱公司收购的NDB引擎,NDB的特点是数据放在内存中,mysql-5.1版本开始可以将非索引数据放到磁盘上。NDB之前的缺陷是join查询是mysql数据库层完成的,而不是存储引擎完成的,复杂的join查询需要巨大的网络开销,速度很慢。当前mysql cluster7.2版本中已经解决此问题,join查询效率提高了70倍。

  1.3.4、memeory存储引擎,将数据放到内存中,默认使用hash索引,不支持text和blob类型,varchara是按照char的方式来存储的。mysql数据库使用memory存储引擎作为临时表还存储中间结果集(intermediate result),如果中间集结果大于memorg表的容量设置,又或者中间结果集包含text和blog列类型字段,则mysql会把他们转换到myisam存储引擎表而放到磁盘上,会对查询产生性能影响。

  1.3.5、archive存储引擎,压缩能力较强,主要用于归档存储。

  1.3.6、federated存储引擎,不存储数据,他指向一台远程mysql数据库上的表。

  1.3.7、maria存储引擎,myisam的后续版本,支持缓存数据和索引,行锁设计,支持mvcc,支持事务和非事务安全的选项,以及更好的BLOG字符类型的处理性能。

  1.3.8、其他存储引擎,sphinx用于全文索引,infobright用于数据仓库

  1.4、各引擎之间的对比

  可以通过 show engines查看mysql对存储引擎的支持情况。

  1.5、连接mysql

  第二章、innodb存储引擎

  2.1、第一章已经大概介绍innodb的特点

  2.2、innodb引擎架构

  维护所有进程/线程需要访问的多个内部数据结构

  缓存磁盘上的数据,方便快速的读取,并且在对磁盘文件的数据进行修改之前在这里缓存

  重做日志缓存

  ……….

  后台线程的主要作用是负责刷新内存池中的数据,保证缓冲池中的内存缓存是最近的数据,此外、将已经修改的数据文件刷新到磁盘文件,同时保证数据库发生异常情况下innodb能恢复到正常运行状态。

  2.2.1、后台线程

  innodb存储引擎后台有7个线程,—–4个IO线程,1个master thread,一个lock监控线程,一个错误监控线程。(当前5.5版本,默认IO线程是18个,8个读,8个写,一个insert buffer thread、一个log thread,加上master线程、lock监控线程、错误监控线程一共是21个)。可以通过 show variables like ‘innodb_%io_threads’\G和show engine innodb status\G来查看相关信息。

  2.2.2、内存

  innodb存储引擎内存由以下三个部分组成:缓冲池(buffer pool),重做日志缓存(redo log buffer),额外的内存池(additional memory pool)。分别使用innodb_buffer_pool_size和innodb_log_buffer_size、innodb_additional_mem_pool_size的大小决定。可以使用 show engine innodb status来查看innodb_buffer_pool的事情情况。在BUFFER POOL AND MEMORY里可以看到存储引擎缓存池的使用情况,buffer pool size表明一共有多少缓冲帧(buffer frame),每个buffer frame为16K.buffer pool size表明一共有多少缓冲帧、free buffers表示当前空闲的缓冲帧、database pages表示已经使用的缓存帧、modified db pages表示脏页的数量。

  innodb_buffer_pool_size:具体看,缓冲池中的数据库类型有:索引页、数据库页、undo页、插入缓存页(insert buffer)、自适应hash(adaptive hash index)、innodb存储的锁信息(lock info)、数据字典信息(data dictionary)

  示意图见书P24.

  注意:在32位windows下innodb_buffer_pool_size可以通过开启AWE功能突破内存限制,但是会自动禁用自适应hash(adaptive hash index).

  innodb_log_buffer_size:一般情况下innodb会每秒刷新log buffer到硬盘,因此保证每秒产生的事务梁在这个缓存大小之内就可以了。

  innodb_additional_mem_pool_size:当你的innodb_buffer_pool_size很大的时候,这个值也需要扩大。

  2.3、master thread

  2.3.1、master thread源码分析

 

void masteor_thread(){ 
  loop: //主循环,间隔10s 
  for(int i=0;i thread_sleep(1) //sleep 1 s 
  do log buffer flush to disk //每秒都要刷新日志缓存到硬盘 
  if(last_one_second_iosinnodb_max_dirty_pages_pct){//如果缓存中的脏页比例大于配置中的innodb_max_dirty_pages_pct就刷新100个脏页到硬盘 
  do buffer pool flush 100 dirty page 
  } 
  if(no user activity){ //如果当前没有活跃用户或者数据库关闭时,就跳入background loop 
  goto backgroud loop 
  } 
  sleep 1 second if necessary 
  } 
  //每10秒执行的操作 
  if(last_ten_second_ios70%){ //如果缓存中脏页比例大于70%,就刷新100个脏页到硬盘,否则只刷新10个 
  do buffer pool flush 100 dirty page 
  }else{ 
  buffer pool flush 10 dirty page 
  } 
  do fuzzy checkpoint //产生一个检查点 
  goto loop: 
  background loop: //backupgroud循环 
  do full purge //总是删除bufferpool中无用的undo页 
  do merge 20 insert buffer //总是合并20哥插入缓存 
  if not idle://如果不空闲,就跳回主循环,如果空闲就跳入flush loop 
  goto loop: 
  else 
  goto flush loop 
  flush loop: 
  do buffer pool flush 100 dirty page //总是刷新100个脏页到硬盘,直到缓存中的脏页比例小于innodb_max_dirty_pages_pct 
  if(buf_get_modified_ratio_pct>innodb_max_dirty_pages_pct){ 
  goto flush loop 
  } 
  goto suspend loop //完成刷新脏页的任务后,跳入suspend loop 
  suspend loop: 
  suspend_thread() //将master线程挂起,等待事件激活 
  waiting event 
  goto loop: 
  }

 

  可以通过show engine innodb status\G命令查看BACKGROUND THREAD段关于mster thread的信息。这里主循环执行了2857554次,每秒的thread执行了2857350次,每十秒的tread执行了285310次,6239次background thread和6238次flush thread。这里的每秒和每十秒的比例差不多,证明服务器压力不是很大。

 

----------------- 
  BACKGROUND THREAD 
  ----------------- 
  srv_master_thread loops: 2857554 1_second, 2857350 sleeps, 285310 10_second, 6239 background, 6238 flush 
  srv_master_thread log flush and writes: 3040886

 

  2.3.2、master thread的潜在问题

  1、由于硬件的发展,现在的硬件性能已经提高了很多,如果innodb每秒最大刷新100个脏页,那么效率会很低,为了解决这个问题,innodb plugin提供了一个参数innodb_io_capacity,用来表示磁盘IO的吞吐量,默认值是200,规则如下:

  在合并插入缓存时,合并插入缓存的数量为innodb_io_capacity的5%。

  在从缓冲区刷新脏页时,啥新脏页的数量为innodb_io_capacity。

  如果你使用的是ssd或者raid10,你可以调高这个参数,知道符合你的硬件。

  2、关于innodb_max_dirty_pages_pct值的争议,如果值过大,内存也很大或者服务器压力很大,那么效率很降低,如果设置的值过小,那么硬盘的压力会增加,建议是在75-80.并且innodb plugin引进了innodb_adaptive_flushng(自适应的刷新),该值影响每秒刷新脏页的数量。他会通过innodb_flush_get_desired_flush_rate的函数判断需要刷新脏页最合适的量,这个函数是通过重做日志产生的速度来判断的。

  通过上述改进,innodb master thread的为代码如下:

 

void masteor_thread(){ 
  loop: //主循环,间隔10s 
  for(int i=0;i thread_sleep(1) //sleep 1 s 
  do log buffer flush to disk //每秒都要刷新日志缓存到硬盘 
  if(last_one_second_iosinnodb_max_dirty_pages_pct){//如果缓存中的脏页比例大于配置中的innodb_max_dirty_pages_pct就刷新innodb_io_capacity个脏页到硬盘 
  do buffer pool flush 100% innodb_io_capacity dirty page 
  } 
  if(no user activity){ //如果当前没有活跃用户或者数据库关闭时,就跳入background loop 
  goto backgroud loop 
  } 
  sleep 1 second if necessary 
  } 
  //每10秒执行的操作 
  if(last_ten_second_ios< innodb_io_capacity){ //如果最后10s内IO小于innodb_io_capacity次,那么就刷新innodb_io_capacity个脏页到硬盘 do buffer pool flush 100% * innodb_io_capacity dirty page } do merge at most 5 insert buffer //总是合并最多5个插入缓存 do log buffer flush to disk //总是将日志缓存刷新到磁盘 do full purge //总是删除buffer_pool中无用的undo页,一次最多20个 if(buf_get_modified_ratio_pct>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 fuzzy checkpoint //产生一个检查点 
  goto loop: 
  background loop: //backupgroud循环 
  do full purge //总是删除bufferpool中无用的undo页 
  do merge 100% * innodb_io_capacity insert buffer //总是合并innodb_io_capacity个插入缓存
  if not idle://如果不空闲,就跳回主循环,如果空闲就跳入flush loop 
  goto loop: 
  else 
  goto flush loop 
  flush loop: 
  do buffer pool flush 100% * innodb_io_capacity dirty page //总是刷新innodb_io_capacity个脏页到硬盘,直到缓存中的脏页比例小于innodb_max_dirty_pages_pct 
  if(buf_get_modified_ratio_pct>innodb_max_dirty_pages_pct){ 
  goto flush loop 
  } 
  goto suspend loop //完成刷新脏页的任务后,跳入suspend loop 
  suspend loop: 
  suspend_thread() //将master线程挂起,等待事件激活 
  waiting event 
  goto loop: 
  }

 

  2.4、关键特性,为innodb提高性能的技术

  2.4.1、插入缓存

  当一个表有非聚集索引时,对于非聚集索引的叶子节点的插入不是顺序的,这时候需要离散的访问非聚集索引页,性能就在这里降低了,这是由于b+树的原理导致的。插入缓存就是用来解决这个问题的。

  对于非聚集索引的插入和更新操作,不是每一次都直接插入索引页,而是先判断插入的非聚集索引页是否在缓存中,如果在就直接插入,如果不在就放入到一个插入缓冲区中,好似欺骗数据库这个非聚集索引已经插入到叶子节点了。然后再以一定的频率插入缓存和非聚集索引页字节点的合并操作。

  插入缓存的使用需要满足以下两个条件(也就是非唯一的辅助索引):

  索引是辅助索引

  索引不是唯一的

  可以通过show engine innodb status\G查看INSERT BUFFER AND ADAPTIVE HASH INDEX段的信息来了解插入缓存的使用情况。

 

------------------------------------- 
  INSERT BUFFER AND ADAPTIVE HASH INDEX 
  ------------------------------------- 
  Ibuf: size 1, free list len 30, seg size 32, 8069 merges 
  merged operations: 
  insert 6063, delete mark 2347, delete 73 
  discarded operations: 
  insert 0, delete mark 0, delete 0 
  Hash table size 8850419, node heap has 1562 buffer(s) 
  34.71 hash searches/s, 127.48 non-hash searches/s 
  --- 
  LOG 
  --- 
  Log sequence number 94417057034 
  Log flushed up to 94417055332 
  Last checkpoint at 94415342503 
  Max checkpoint age 650641675 
  Checkpoint age target 630309123 
  Modified age 1714531 
  Checkpoint age 1714531 
  0 pending log writes, 0 pending chkp writes 
  3724702 log i/o's done, 1.39 log i/o's/second

 

  seg size显示了当前插入缓冲的大小为32*16K,size代表使用了的插入缓冲,free list len代表空闲的插入缓冲。等待完善标记

  2.4.2、两次写

  两次写给innodb带来的是可靠性,主要用来解决部分写失败(partial page write)。doublewrite有两部分组成,一部分是内存中的doublewrite buffer,大小为2M,另外一部分就是物理磁盘上的共享表空间中联系的128个页,即两个区,大小同样为2M。当缓冲池的张也刷新时,并不直接写硬盘,而是回通过memcpy函数将脏页先拷贝到内存中的doublewrite buffer,之后通过doublewrite buffer再分两次写,每次写入1M到共享表空间的物理磁盘上,然后马上调用fsync函数,同步磁盘。以下命令可以查看doublewrite的使用情况。

 

mysql> show global status like 'innodb_dblwr%'; 
  +----------------------------+----------+ 
  | Variable_name | Value | 
  +----------------------------+----------+ 
  | Innodb_dblwr_pages_written | 60511554 | 
  | Innodb_dblwr_writes | 828734 | 
  +----------------------------+----------+

 

  这台DB doublewrite一共写了60511554个页,但实际写入次数为828734,60511554/828734=73。说明这台DB的压力一般。slave上可以通过设置skip_innodb_doublewrite参数关闭两次写功能来提高性能,但是master上一定要开启此功能,保证数据安全。

  2.4.3、自适应哈西索引

  由于innodb不支持hash索引,但是在某些情况下hash索引的效率很高,于是出现了 adaptive hash index功能,innodb存储引擎会监控对表上索引的查找,如果观察到建立hash索引可以提高性能的时候,则自动建立hash索引。可以通过show engine innodb status\G来查看自适应哈西索引的使用情况。可以使用innodb_adaptive_hash_index来禁用和启用hash索引,默认开启。

  2.5、启动、关闭、恢复

  在关闭数据库时,innodb_fast_shutdown影响innodb存储引擎的行为。该参数有0、1、2三个参数。0代表mysql关闭时,innodb需要完成所有的full purge和merge insert buffer操作。1是默认值,表示不需要完成上述的full purge和merge insert buffer操作,但是缓冲池的一些数据脏页还是回刷新到磁盘。2表示不完成full purge和merge insert buffer操作,也不将缓冲池中的脏页回写磁盘,而是将日志都写入日志文件。

  innodb_force_recovery影响整个innodb存储引擎的恢复状况,该值默认为0,表示当需要恢复时,需要执行所有的恢复操作,当不能进行有效恢复时,如数据页发生了corruption,mysql数据库可能宕机,并把错误写入错误日志中。

  innodb_force_recovery还可以设置1-6这个几个值。大的数字包含小数字的影响。

  1(SRV_FORCE_IGNORE_CORRUPT):忽略检查到corrupt页。

  2(SRV_FORCE_NO_BACKGROUND):阻止主线程的运行,如主线程需要执行full purge操作,会导致crash。

  3(SRV_FORCE_NO_TRX_UNDO)不知行事务回滚操作

  4(SRV_FORCE_NO_IBUF_MERGE)不执行插入缓冲的合并操作

  5(SRV_FORCE_NO_UNDO_LOG_SCAN)不查看撤销日志undo log。innodb存储引擎会将未提交的事务视为已提交

  6(SRV_FORCE_NO_LOG_REDO)不执行前滚的操作

  2.6、innodb plugin = 新版本的innodb存储引擎

  mysql-5.1使用了插件式的架构。在mysql-5.1.38前,安装innodb plugin必须下载plugin的文件,再进行一系列的安装。

  第三章、文件

  3.1参数文件

  my.cnf文件。

  3.1.1、什么是参数

  键=值

  3.1.2、参数类型

  分为global和session

  3.2、日志文件

  3.2.1、错误日志

  show global variables like ‘log_error’;

  3.2.2、慢查询日志

  show global variables like ‘%long%’;

  show global variables like ‘log_slow_queries’; //mysql-5.1.2开始支持微妙级慢查询。

  show global variables like ‘log_queryies_not_using_indexs’; //未使用索引的查询

  mysql-5.1开始可以将慢查询放入log_output表中。

  3.2.3、查询日志

  mysql-5.1开始可以将查需日志放入general_log表中

  3.2.4、二进制日志

  主要功能

  恢复 基于时间点的恢复

  复制

  以下参数影响二进制日志

  max_binlog_size

  binlog_cache_size

  sync_binlog

  binlog-do-db

  binlog-ignore-db

  log-slave-update

  binlog_format

  将binlog_format设置成row,可以支持事务隔离级别为READ COMMITTED,以获得更好的并发性。

  在使用MIXED格式下,mysql采用STATEMENT格式进行二进制日志文件的记录,但是有一些情况下会使用ROW格式,可能的情况如下:

  1、表的存储引擎为NDB,这个时候DML操作都会以ROW格式记录

  2、使用了uuid()、user(),current_user(),found_rows(),row_count(),等不确定函数。

  3、使用了insert delay语句

  4、使用了用户定于的函数(UDF)

  5、使用了临时表(temporary table)

  注意:针对系统库mysql里面的表发生变化的处理规则如下:

  1、如果采用insert,update,delete直接操作表,则日志根据binlog_format设定的格式记录。

  2、如果使用grant,revoke,set password等DCL语句,那么无论如何都会使用SBR模式记录。

  3、blockhole引擎不支持row格式,ndb引擎不支持statement格式。

  3.3、套件字文件

  show variables likea ‘socket’;

  3.4、pid文件

  show variables like ‘pid_file’;

  3.5、表结构定义文件

  *.frm

  3.6、innodb引擎文件

  重做日志文件,表空间文件

  3.6.1、表空间文件

  默认会有一个大小10M的tablespace file,名字叫ibdata1.可以通过innodb_data_file_path=/db/ibdata1:2000M;/dr2/db/ibdata2:2000M:autoextend 来配置。这里使用了两个文件来组成的表空间,如果他们不在一个磁盘上的话,对IO性能会有所优化。

  还有一个innodb_file_per_table,这个参数会让每个innodb引擎的表都单独产生一个表空间。

  注意:单独表空间只保存该表的数据,索引和插入缓冲等信息,其余信息还是保存在共享表空间的(undo页,系统事务信息,二次写)。

  3.6.2、重做日志文件(redo log)

  redo log是在实例或者介质失败的时候,用来保证数据完整性。

  每个innodb存储引擎至少有一个重做日志组,每个重做日志文件组下至少又2个重做日志文件,如默认的ib_logfile0、ib_logfile1.为了得到更高的可靠性,你可以设置多个重做镜像日志组(mirrored log groups)。

  innodb_log_file_size //指定重做日志文件的大小

  innodb_log_files_in_group //指定重做日志组中文件的数量,默认为2

  innodb_mirrored_log_groups //指定日志镜像文件组的数量,默认为1

  innodb_log_group_home_dir //指定了日志文件组所在的路径

  还有一个很重要的参数与redo log相关,那就是innodb_flush_log_at_trx_commit的值,对innodb的性能影响很大。他有0,1,2三个值,0代表提交事务时,并不同步写redo log,而是等master threas每秒写。1代表commit的时候就将redo log缓存写入磁盘,2代表commit的时候将redo log缓存异步的写入磁盘。

分享到:
评论

相关推荐

Global site tag (gtag.js) - Google Analytics