参考文献

数据库和数据库实例

  • 从概率上说,数据库是文件的集合,是依照某种数据模型组织起来并存放与二级存储其中的数据集合;
  • 数据库实例是程序,是位于用户与操作系统之间的一层数据管理软件,用户对数据库数据的任何操作,包括数据库定义,数据查询,数据维护,数据库运行控制等都是在数据库实例下进行的,应用程序只有通过数据库实例才能和数据库打交道.

MySQL版本与InnoDB版本对照表

MySQL版本 InnoDB版本 版本开始时间 说明
1.0.x 1996
3.11.1 1996.10 MySQL没有2.x版本
4.0.x 2003
5.1.x 1.0.x版本(官方称为InnoDB Plugin)
5.5.x 1.1.x版本 2010 使用InnoDB作为默认引擎
5.6.x 1.2.x版本
8.0.x 8.0.x 2016

InnoDB体系架构

img

In-memory structures

  • Buffer pool 缓冲池
  • Change buffer
  • Adaptive hash index 自适应哈希索引
  • Log buffer 日志缓冲区

on-disk structures

  • System tablespace 系统表空间
  • File-per-table tablespaces
  • General tablespaces 通用表空间
  • Undo tablespaces
  • Temporary tablespaces
  • Doublewrite buffer 双写缓冲区
  • Redo log
  • Undo logs

后台线程

  • InnoDB存储引擎是多线程的模型,因此其后台有多个不同的后台线程,复制处理不同的任务.
  • 后台线程的主要作用是负责刷新内存池中的数据,保证缓冲池中的内存缓存的是最近的的数据.此外将已修改的数据文件刷新到新磁盘文件,同时保证在数据库发生异常的情况下InnoDB能恢复到正常状态.
img

Master Thread

  • Master Thread是一个非常核心的后台线程,主要负责将缓冲池中的数据异步刷新到磁盘,保证数据的一致性,包括脏页的刷新,合并插入缓冲(INSERT BUFFER),UNDO页的回收等.

IO Thread

  • 在InnoDB存储引擎中大量使用了AIO(Async IO)来处理IO请求,而IO Thread的工作主要负责这些IO请求的回调处理.

  • InnoDB 1.0版本之前共有4个IO Thread,分别为write,read,insert buffer和log.

  • 从InnoDB 1.0.x版本开始,read_thread和write_thread分别增加到了4个.

    1
    2
    3
    4
    5
    6
    7
    8
    mysql> show variables like 'innodb_%_io_threads';
    +-------------------------+-------+
    | Variable_name           | Value |
    +-------------------------+-------+
    | innodb_read_io_threads  | 4     |
    | innodb_write_io_threads | 4     |
    +-------------------------+-------+
    2 rows in set (0.01 sec)

  • 可以通过SHOW ENGINE INNODB STATUS\G来观察InnoDB的IO Thread:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    --------
    FILE I/O
    --------
    I/O thread 0 state: waiting for i/o request (insert buffer thread)
    I/O thread 1 state: waiting for i/o request (log thread)
    I/O thread 2 state: waiting for i/o request (read thread)
    I/O thread 3 state: waiting for i/o request (read thread)
    I/O thread 4 state: waiting for i/o request (read thread)
    I/O thread 5 state: waiting for i/o request (read thread)
    I/O thread 6 state: waiting for i/o request (write thread)
    I/O thread 7 state: waiting for i/o request (write thread)
    I/O thread 8 state: waiting for i/o request (write thread)
    I/O thread 9 state: waiting for i/o request (write thread)
    Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
     ibuf aio reads:, log i/o's:, sync i/o's:
    Pending flushes (fsync) log: 0; buffer pool: 1
    1731 OS file reads, 189582 OS file writes, 169485 OS fsyncs
    0.00 reads/s, 0 avg bytes/read, 0.50 writes/s, 0.50 fsyncs/s
    • 可以看到IO Thread 0为insert buffer thread,IO Thread 1为log thread,之后就是根据参数innodb_read_io_threads和innodb_write_io_threads来设置的读写线程,并且读线程的ID总是小于写线程.

Purge Thread

  • 事务被提交后,其所使用的undolog 可能不再需要,因此需要Purge Thread来回收已经使用并分配的undo页.

  • 可以在MySQL数据库的配置文件中添加以下命令来启用独立的Purge Thread

    1
    2
    [mysqld]
    innodb_purge_threads=1

Page Cleaner Thread

  • Page Cleaner Thread是在InnoDB 1.2.x版本中引入的,其作用是将之前版本中脏页的刷新操作都放入单独的线程中完成.而其目的是为了减轻元Master Thread的工作以及对于用户查询线程的阻塞,进一步提高InnoDB存储引擎的性能.

内存

缓冲池

  • InnoDB存储引擎是基于磁盘存储的,并将其中的记录按照页的方式进行管理.因此可将其视为基于磁盘的数据库系统(Disk-base Database).

  • 缓冲池简单来说就是一块内存区域,通过内存的速度来弥补磁盘速度较慢,从而对数据库性能的影响.

  • 在数据库中进行读取页的操作,首先将从磁盘读到的页放在缓冲池中,这个过程称为将页"FIX"在缓冲池中.下次再读相同的页时,首先判断该页是否存在缓冲池中.若在缓冲池中,称该页在缓冲池中被命中,直接读取该页.否则,读取磁盘上的页.

  • 对于数据库中页的修改操作,则是首先修改在缓冲池中的页,然后再以一定的频率刷新到磁盘上.这里需要注意的是,页从缓冲池刷新回磁盘的操作并不是在每次页发生更新时触发,而是通过一种称为Checkpoint的机制刷新回磁盘.同样,这也是为提高数据库的整体性能.

  • 对于InnoDB存储引擎而言,其缓冲池的配置通过参数innodb_buffer_pool_size来设置

    1
    2
    3
    4
    5
    6
    7
    mysql> show variables like 'innodb_buffer_pool_size';
    +-------------------------+-----------+
    | Variable_name           | Value     |
    +-------------------------+-----------+
    | innodb_buffer_pool_size | 134217728 |
    +-------------------------+-----------+
    1 row in set (0.00 sec)

  • 缓冲池中缓存的数据页类型有:索引页,数据页,undo页,插入缓冲(insert buffer),自适应哈希索引(adaptive hash index),InnoDB存储的锁信息(lock info),数据字典信息(data dictionary)等.

    img

  • 从InnoDB1.0.x版本开始,允许有多个缓冲池实例.每个页可以根据哈希值平均分配到不同缓冲池实例中.这样做的好处是减少数据库内部的资源竞争,增加数据库的并发处理能力.可以通过参数innodb_buffer_pool_instances来配置.

    1
    2
    3
    4
    5
    6
    7
    mysql> show variables like 'innodb_buffer_pool_instances';
    +------------------------------+-------+
    | Variable_name                | Value |
    +------------------------------+-------+
    | innodb_buffer_pool_instances | 1     |
    +------------------------------+-------+
    1 row in set (0.02 sec)

  • 从MySQL5.6版本开始,还可以通过information_schema架构下的innodb_buffer_pool_stats来观察缓冲的状态.

    1
    2
    3
    4
    5
    6
    7
    mysql> select pool_id,pool_size,free_buffers,database_pages from innodb_buffer_pool_stats\G
    *************************** 1. row ***************************
           pool_id: 0
         pool_size: 8192
      free_buffers: 1517
    database_pages: 6600
    1 row in set (0.00 sec)

LRU List/Free List/Flush List

  • 通常来说,数据库中的缓冲池是通过LRU(最近最少使用)算法来进行管理的.

    • 即最频繁使用的页在LRU列表的前端,而最少使用的页在LRU列表的尾端.当缓冲池不能存放新读取到的页时,将首先释放LRU列表中尾端的页.

  • 在InnoDB存储引擎中,缓冲池中页的大小默认为16KB,同样使用LRU算法对缓冲池进行管理.稍有不同的是InnoDB存储引擎对传统的LRU算法做了一些优化.

    • 在InnoDB的存储引擎中,在LRU列表中还加入了midpoint位置.新读取到的页,虽然是最新访问的页,但并不是直接放入到LRU列表的首部,而是放入LRU列表的midpoint位置.这个算法在InnoDB存储引擎称为midpoint insertion strategy.在默认配置下,该位置在LRU列表长度的5/8处.midpoint位置可由参数innodb_old_blocks_pct控制

      1
      2
      3
      4
      5
      6
      7
      mysql> show variables like '%innodb_old_blocks_pct%';
      +-----------------------+-------+
      | Variable_name         | Value |
      +-----------------------+-------+
      | innodb_old_blocks_pct | 37    |
      +-----------------------+-------+
      1 row in set (0.00 sec)
      • 参数innodb_old_blocks_pct默认值为37,表示新读取的页插入到LRU列表尾端的37%的位置(差不多3/8的位置).在InnoDB存储引擎中,把midpoint之后的列表称为old列表,之前的列表称为new列表(即new列表的页都是最为活跃的热点数据).
      • 为什么不直接使用朴素的LRU算法,直接将读取的页放入到LRU列表的首部?
        • 这是因为若直接将读取到的页放入到LRU的首部,那么某些SQL操作可能会使缓冲池中的页被刷新出,从而影响缓冲池的效率.
        • 常见的这类操作为索引或数据的扫描操作.这类操作需要访问表中的许多页,甚至是全部的页,而这些页通常来说又仅在这次查询操作中需要,并不是活跃的热点数据.如果页被放入LRU列表的首部,那么非常可能将所需要的热点数据页从LRU列表中移除,而在下次需要读取该页时,InnoDB存储引擎需要再次访问磁盘.
      • 为了解决这个问题,InnoDB存储引擎引入innodb_old_blocks_time参数,用于表示页读取到mid位置后需要等待多久才会被加入到LRU列表的热端.

  • LRU列表用来管理已经读取的页,但当数据库刚启动时,LRU列表是空的,即没有任何页,这是页都存放在Free列表中.

    • 当需要从缓冲池中分页时,首先从Free列表中查找是否有可用的空闲页,若有则将该页从Free列表中删除,放入LRU列表中.否则,根据LRU算法,淘汰LRU列表末尾的页,将该内存分配给新的页.
    • 当页从LRU列表的old部分加入到new部分时,称此时发生的操作为page made young,而因为innodb_old_blocks_time的设置而导致页没有从old部分移动到new部分的操作称为page not made young.
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    ----------------------
    BUFFER POOL AND MEMORY
    ----------------------
    Total large memory allocated 137052160
    Dictionary memory allocated 4042634
    Buffer pool size   8192
    Free buffers       1544
    Database pages     6573
    Old database pages 2406
    Modified db pages  0
    Pending reads      0
    Pending writes: LRU 0, flush list 0, single page 0
    Pages made young 3760, not young 12734
    0.00 youngs/s, 0.00 non-youngs/s
    Pages read 1662, created 5160, written 28370
    0.00 reads/s, 0.00 creates/s, 0.00 writes/s
    Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
    Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
    LRU len: 6573, unzip_LRU len: 0
    I/O sum[5]:cur[0], unzip sum[0]:cur[0]

    • 可用通过show engine innodb status\G看到:

      • 当前Buffer pool size共有8192个页,即8192*16K.
      • Free buffers表示当前Free列表中的页的数量
      • Database pages表示当前LRU列表中页的数量.
      • 可能的情况是Free buffers与Database pages的数量之和不等于Buffer pool size.因为缓冲池中的页还可能会被分配给自适应哈希索引,Lock信息,Insert Buffer等页,而这部分页不需要LRU算法进行维护,因此不存在LRU列表中.
      • Pages made young 显示了LRU列表中页移动到前端的次数.
      • 0.00 youngs/s, 0.00 non-youngs/s表示每秒这两类操作的次数.
      • Buffer pool hit rate 表示缓冲池的命中率,当前为100%,说明缓冲池状态非常良好.通常该值不应该小于95%.若发现Buffer pool hit rate 的值小于95%这种情况,则需要观察是否是由于全表扫描引起的LRU列表被污染的问题.

    • TIPS: show engine innodb status\G 显示的不是当前的状态,而是过去某个时间范围内InnoDB存储引擎的状态.

    • 从InnoDB1.2版本开始,还可以通过表innodb_buffer_pool_stats来观察缓冲池的运行状态

      1
      2
      3
      4
      5
      6
      7
      mysql> select pool_id,hit_rate,pages_made_young,pages_not_made_young from information_schema.innodb_buffer_pool_stats\G
      *************************** 1. row ***************************
                   pool_id: 0
                  hit_rate: 1000
          pages_made_young: 3789
      pages_not_made_young: 12818
      1 row in set (0.01 sec)

    • 此外还可以通过表innodb_buffer_page_lru来观察每个LRU列表的每个页的具体信息.

      1
      mysql> select table_name,space,page_number,page_type from innodb_buffer_page_lru where space=1;

    • InnoDB存储引擎从1.0.x版本开始支持压缩的功能,即将原来的16KB的页压缩为1KB,2KB,4KB和8KB.而由于页大小发生了变化,LRU列表也有些许的改变.对于非16KB的页,是通过unzip_LRU列表进行管理的.

      1
      2
      3
      4
      5
      6
      7
      ----------------------
      BUFFER POOL AND MEMORY
      ----------------------
      ...
      Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
      LRU len: 6573, unzip_LRU len: 0
      I/O sum[5]:cur[0], unzip sum[0]:cur[0]

      • 可以看到LRU列表中一共有6573个页,而unzip_LRU列表中有0个页,这里需要注意的是,LRU中页包含了unzip_LRU列表的页.

      • unzip_LRU从缓冲池中分配内存过程大致如下:

        • 首先,在unzip_LRU列表中对不同压缩页大小的页进行分别管理.其次,通过伙伴算法进行内存的分配.例如对需要从缓冲池中申请页为4KB的大小,其过程如下:
          • 检查4KB的unzip_LRU列表,检查是否有可用的空闲页
          • 若有,则直接使用
          • 否则,检查8KB的unzip_LRU列表
          • 若能够得到空闲页,将页分成2个4KB,存放到8KB的unzip_LRU列表
          • 若不能得到空闲页,从LRU列表中申请一个16KB的页,将页分为一个8KB的页,2个4KB的页,分别存放到对应的unzip_LRU中.

      • 可用通过information_schema架构下的表innodb_buffer_page_lru来观察unzip_LRU列表中页

        1
        2
        mysql> select table_name,space,page_number,compressed_size from innodb_buffer_page_lru where
        compressed_size <> 0;

  • 在LRU列表中的页被修改后,称该页为脏页(ditty page),即缓冲池中的页和磁盘上的页的数据产生了不一致.这时数据库会通过CHECKPOINT机制将脏页刷新回磁盘,而FLUSH列表的页即为脏页列表,需要注意的是,脏页即存在与LRU列表中,也存在于FLUSH列表中.LRU列表用来管理缓冲池中页的可用性,Flush列表用来管理将页刷新回磁盘,二者互不影响.

    • Modified db pages 就显示了脏页的数量.

    • 可用通过information_schema架构下的表innodb_buffer_page_lru来观察脏页的数量以及脏页的类型

      1
      mysql> select table_name,space,page_number,page_type from innodb_buffer_page_lru where oldest_modification > 0;

重做日志(redo log)缓冲

  • InnoDB存储引擎的内存区域除了有缓冲池外,还有重做日志缓冲.

  • InnoDB存储引擎先将重做日志信息放入到这个缓冲区,然后按一定频率将其刷新到重做日志文件.重做日志一般不需要设置得很大,因为一般情况下每一秒会将重做日志刷新到日志文件中,因此用户只需要保证每秒产生的事务量在这个缓冲大小之内即可.该值可由配置参数innodb_log_buffer_size控制,默认为16MB

    1
    2
    3
    4
    5
    mysql> show variables like 'innodb_log_buffer_size'\G
    *************************** 1. row ***************************
    Variable_name: innodb_log_buffer_size
            Value: 16777216
    1 row in set (0.01 sec)

  • 通常情况下,16MB的重做缓冲池足以满足绝大部分的应用,因为重做日志在下列三种情况下会将重做日志缓冲中的内容刷新到外部磁盘的重做日志文件中.

  • Master Thread每一秒将重做日志缓冲刷新到重做日志文件中;

  • 每个事务提交时会将重做日志缓冲刷新到重做文件中;

  • 当重做日志缓冲池剩余空间小于1/2时,重做日志缓冲刷新到重做日志文件.

额外的内存池

  • 在InnoDB存储引擎中,对内存的管理是通过一种称为内存堆的方式进行的.在对一些数据结构本身的内存进行分配时,需要从额外的内存池中进行申请,当该区域的内存不够时,会从缓冲池中申请.
  • 例如,分配了缓冲池(innodb_buffer_pool),但是每个缓冲池中帧缓冲(frame buffer)还有对应的缓冲控制对象(buffer control block),这些对象记录了一些诸如LRU,锁,等待等信息,而这个对象的内存需要从额外内存池中申请.因此在申请了很大的InnoDB缓冲池时,也应该考虑相应地增加这个值.