InnoDB存储引擎

InnoDB体系架构

后台线程

InnoDB 存储引擎是多线程的模型，因此其后台有多个不同的后台线程，负责处理不同的任务。

Master Thread

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

I0 Thread

在InnoDB存储引擎中大量使用了AIO（Async IO）来处理写IO请求，这样可以极大提高数据库的性能。而IO Thread 的工作主要是负责这些IO 请求的回调(call back)处理。

从InnoDB 1.0.x 版本开始， read thread 和write thread 分别增大到了4 个，并且不再使用inodb_file_io_threads参数，而是分别使用innodb_read_io_threads和innodb_write_io_threads参数进行设置。

mysql> show variables like 'innodb_version'\G;
*************************** 1. row ***************************
Variable_name: innodb_version
        Value: 5.7.13
1 row in set, 1 warning (0.62 sec)

mysql> show variables like 'innodb_%io_threads'\G;
*************************** 1. row ***************************
Variable_name: innodb_read_io_threads
        Value: 4
*************************** 2. row ***************************
Variable_name: innodb_write_io_threads
        Value: 4
2 rows in set, 1 warning (0.00 sec)

可以通过命令SHOW ENGINE INNODB STATUS 来观察InnoDB 中的IO Thread:


mysql> show engine innodb status \G;
*************************** 1. row ***************************
  Type: InnoDB
  Name:
Status:
=====================================
2016-08-18 08:42:59 0x34b8 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 13 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 1132 srv_active, 0 srv_shutdown, 3755881 srv_idle
srv_master_thread log flush and writes: 3757011
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 1965
OS WAIT ARRAY INFO: signal count 1673
RW-shared spins 0, rounds 2788, OS waits 1172
RW-excl spins 0, rounds 2275, OS waits 30
RW-sx spins 5, rounds 150, OS waits 5
Spin rounds per wait: 2788.00 RW-shared, 2275.00 RW-excl, 30.00 RW-sx
------------------------
LATEST FOREIGN KEY ERROR
------------------------
2016-07-29 15:13:50 0x2cf4  Cannot drop table `dqd_database`.`news_main`
because it is referenced by `dqd_database`.`news_main_analysis`
------------
TRANSACTIONS
------------
Trx id counter 5149
Purge done for trx's n:o < 5149 undo n:o < 0 state: running but idle
History list length 627
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 281475148613424, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
--------
FILE I/O
--------
I/O thread 0 state: wait Windows aio (insert buffer thread)
I/O thread 1 state: wait Windows aio (log thread)
I/O thread 2 state: wait Windows aio (read thread)
I/O thread 3 state: wait Windows aio (read thread)
I/O thread 4 state: wait Windows aio (read thread)
I/O thread 5 state: wait Windows aio (read thread)
I/O thread 6 state: wait Windows aio (write thread)
I/O thread 7 state: wait Windows aio (write thread)
I/O thread 8 state: wait Windows aio (write thread)
I/O thread 9 state: wait Windows aio (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: 0
322 OS file reads, 13452 OS file writes, 7016 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 176 merges
merged operations:
 insert 0, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 1 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 1 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 16091420
Log flushed up to   16091420
Pages flushed up to 16091420
Last checkpoint at  16091411
0 pending log flushes, 0 pending chkp writes
4209 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 137297920
Dictionary memory allocated 505092
Buffer pool size   8192
Free buffers       7414
Database pages     776
Old database pages 266
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 346, not young 134
0.00 youngs/s, 0.00 non-youngs/s
Pages read 289, created 1721, written 7650
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 776, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=4504, Main thread ID=4024, state: sleeping
Number of rows inserted 7694, updated 0, deleted 0, read 11083
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

1 row in set (0.37 sec)

IO Thread 0 为insert buffer thread, IO Thread 1 为log thread 。之后就是根据参数inodb_read_io_threads及inodb_write_io_threads来设置的读写线程，并且读线程的ID总是小于写线程。

Purge Thread

事务被提交后，其所使用的undolog可能不再需要，因此需要PurgeThread来回收已经使用并分配的undo页。

从InnoDB1.2版本开始，InnoDB支持多个purge Thread，进一步加快undo页回收。同时由于Purge Thread 需要离散地读取undo页，这样也能更进一步利用磁盘的随机读取性能。如用户可以设置4 个Purge Thread:


mysql> select version() \G;
*************************** 1. row ***************************
version(): 5.7.13
1 row in set (0.11 sec)


mysql> show variables like 'innodb_purge_threads'\G;
*************************** 1. row ***************************
Variable_name: innodb_purge_threads
        Value: 4
1 row in set, 1 warning (0.02 sec)

Page Cleaner Thread

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

内存

缓冲池

InnoDB 存储引擎是基于磁盘存储的，并将其中的记录按照页的方式进行管理。在数据库系统中，由于CPU速度与磁盘速度之间的鸿沟，基于磁盘的数据库系统通常使用缓冲池技术来提高数据库
的整体性能。

读取：缓冲池是一块内存区域，通过内存的速度来弥补磁盘速度较慢对数据库性能的影响。在数据库中进行读取页的操作，首先将从磁盘读到的页存放在缓冲池中，这个过程称为将页咋“FIX”在缓冲池中。下一次再读相同的页时，首先判断该页是否在缓冲池中。若在缓冲池中，称该页在缓冲池中被命中，直接读取该页。否则，读取磁盘上的页。
修改：首先修改在缓冲池中的页。然后再以一定的频率刷新到磁盘上。这里需要注意的是，页从缓冲池刷新回磁盘的操作并不是在每次页发生更新时触发，而是通过Checkpoint的机制刷新回磁盘。

缓冲池的配置通过参数innodb_buffer_pool_size来设置。在我的电脑上缓冲池大小是128MB。

mysql> show variables like 'innodb_buffer_pool_size'\G;
*************************** 1. row ***************************
Variable_name: innodb_buffer_pool_size
        Value: 134217728
1 row in set, 1 warning (0.00 sec)

缓冲池中缓存的数据页类型有：索引页、数据页、undo 页、插入缓冲(insert buffer)、自适应哈希索引（adaptive bash index）、InnoDB存储的锁信息（lock info)，数据字典信息（data dictionary)等。

mysql> show variables like 'innodb_buffer_pool_instances'\G;
*************************** 1. row ***************************
Variable_name: innodb_buffer_pool_instances
        Value: 1
1 row in set, 1 warning (0.00 sec)

通过information_schema数据库下的表innodb_buffer_pool_stats来观察缓冲池的状态。


mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| dqd_database       |
| employees          |
| mysql              |
| performance_schema |
| spider             |
| sys                |
| temp               |
| test               |
+--------------------+
9 rows in set (0.13 sec)

mysql> use information_schema
Database changed
mysql> select * from innodb_buffer_pool_stats\G;
*************************** 1. row ***************************
                         POOL_ID: 0
                       POOL_SIZE: 8192
                    FREE_BUFFERS: 7414
                  DATABASE_PAGES: 776
              OLD_DATABASE_PAGES: 266
         MODIFIED_DATABASE_PAGES: 0
              PENDING_DECOMPRESS: 0
                   PENDING_READS: 0
               PENDING_FLUSH_LRU: 0
              PENDING_FLUSH_LIST: 0
                PAGES_MADE_YOUNG: 346
            PAGES_NOT_MADE_YOUNG: 134
           PAGES_MADE_YOUNG_RATE: 0
       PAGES_MADE_NOT_YOUNG_RATE: 0
               NUMBER_PAGES_READ: 289
            NUMBER_PAGES_CREATED: 1721
            NUMBER_PAGES_WRITTEN: 7650
                 PAGES_READ_RATE: 0
               PAGES_CREATE_RATE: 0
              PAGES_WRITTEN_RATE: 0
                NUMBER_PAGES_GET: 120146
                        HIT_RATE: 0
    YOUNG_MAKE_PER_THOUSAND_GETS: 0
NOT_YOUNG_MAKE_PER_THOUSAND_GETS: 0
         NUMBER_PAGES_READ_AHEAD: 0
       NUMBER_READ_AHEAD_EVICTED: 0
                 READ_AHEAD_RATE: 0
         READ_AHEAD_EVICTED_RATE: 0
                    LRU_IO_TOTAL: 0
                  LRU_IO_CURRENT: 0
                UNCOMPRESS_TOTAL: 0
              UNCOMPRESS_CURRENT: 0
1 row in set (0.05 sec)

LRU List 、Free List和Flush List

数据库中的缓冲池是通过LRU（Latest Recent Used ，最近最少使用）算法来进行管理的。即最频繁使用的页在LRU列表的前端，而最少使用的页在LRU列表的尾端。当缓冲池不能存放新读取到的页时，将首先释放LRU列表中尾端的页。

在InnoDB存储引擎中，缓冲池中页的大小默认为16KB ，同样使用LRU算法对缓冲池进行管理。InnoDB存储引擎对传统LRU算法进行优化，通过使用midpoint insertion strategy在LRU列表中加入了midpoint位置。默认配置在LRU列表的5/8处。midpoint位置可由参数innodb_old_blocks_pct控制。

mysql> show variables like 'innodb_old_blocks_pct'\G;
*************************** 1. row ***************************
Variable_name: innodb_old_blocks_pct
        Value: 37
1 row in set, 1 warning (0.01 sec)

在上面的innodb_old_blocks_pct默认参数为37，表示新读取的页插入到LRU列表尾端的37%的位置（近似3/8）。在InnoDB中，midpoint之后的列表成为old列表，之前的列表成为new列表。new列表中的页都是最为活跃的热点数据。

某些SQL操作如索引或数据的扫描操作可能会使缓冲池中的热点数据页从LRU列表中移除，在下次需要读取该页时，InnoDB存储引擎需要再次访问磁盘。通过使用innodb_old_blocks_time参数，用于表示页读取到mid位置后需要等待多久时间被加入到LRU列表的热端，尽可能使LRU列表中热点数据不被刷出。


mysql> set global innodb_old_blocks_time=1000;
Query OK, 0 rows affected (0.06 sec)

mysql> set global innodb_old_blocks_time=0;
Query OK, 0 rows affected (0.00 sec)

若预估活跃的热点数据不止67%（5/8），可通过设置参数减少可能被刷出的概率。


mysql> set global innodb_old_blocks_pct=20;
Query OK, 0 rows affected (0.07 sec)

LRU列表用来管理已经读取的页，但当数据库刚启动时， LRU 列表是空的，即没有任何的页。这时页都存放在Free列表中。当需要从缓冲池中分页时，首先从Free列表中查找是否有可用的空闲页，若有则将该页从Free列表中删除，放入到LRU 列表中。否则，根据LRU算法，淘汰LRU列表末尾的页，将该内存空间分配给新的页。当页从LRU 列表的old 部分加入到new 部分时，称此时发生的操作为page made young。而因为innodb_old_blocks_time的设置而导致页没有从old部分移动到new部分的操作称为page not made young。可以通过命令SHOW ENGINE INNODB STATUS 来观察LRU 列表及Free 列表的使用悄况和运行状态.

----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 137297920
Dictionary memory allocated 505092
Buffer pool size   8192 -- 缓冲池总大小，8192*16K
Free buffers       7414 -- Free列表中的页的数量
Database pages     776 -- LRU列表中页的数量
Old database pages 266
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 346, not young 134
0.00 youngs/s, 0.00 non-youngs/s   -- 每秒这两类操作的次数
Pages read 289, created 1721, written 7650
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 776, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------

因为缓冲池中的页还可能会被分配给自适应哈希索引、Lock 信息、Insert Buffer 等页，而这部分页不需要LRU 算法进行维护，因此不存在于LRU 列表中。所以可能的情况是Free buffer与Database pages的数量之和不等于Buffer pool size。

观察变量--Buffer pool hit rate。表示缓冲池的命中率，通常该值不应该小子95%。若发生Buffer pool hit rate 的值小于95% 这种情况，用户需要观察是否是由于全表扫描引起的LRU 列表被污染的问题。

在LRU 列表中的页被修改后，称该页为脏页(dirty page) ，即缓冲池中的页和磁盘上的页的数据产生了不一致。这时数据库会通过CHECKPOINT机制将脏页刷新回磁盘，而Flush列表中的页目即为脏页列表。脏页既存在于LRU 列表中，也存在于Flush 列表中。LRU列表用来管理缓冲池中页的可用性。Flush 列表用来管理将页刷新回磁盘，二者互不影响。

重做日志缓冲

以下三种情况会将重做日志缓冲中的内容刷新到重做日志文件中：

Master Thread 每一秒将重做日志缓冲刷新到重做日志文件
每个事务提交时会将重做日志缓冲刷新到重做日志文件
当重做日志缓冲池剩余空间小于1/2 时，重做日志缓冲刷新到重做日志文件。

额外的内存池

InnoDB存储引擎通过内存堆的方式对内存进行管理。在对一些数据结构本身的内存进行分配时，需要从额外的内存池中进行申请，当该区域的内存不够时，会从缓冲池中进行申请。

Checkpoint技术

缓冲池设计目的是为了协调CPU速度与磁盘速度。因此，页的操作是在缓冲池中完成的。DML语句，如update或delete改变页中的记录，那么此时页是脏的，即缓冲池中的页的版本要比磁盘的新。数据库需要将新版本的页从缓冲池刷新到磁盘。

为避免数据丢失的问题，当前事务数据库普遍采用Write Ahead Log策略，即当事务提交时，先写重做日志，再修改页。当发生宕机导致数据丢失时，通过重做日志来完成数据的恢复。（ACID中D特性）

Checkpoint技术的目的是解决一下问题：

数据库宕机对Checkpoint后的重做日志进行恢复，缩短数据库恢复时间
缓冲池不够用时，将脏页刷新到磁盘根据LRU算法会溢出最近最少使用的页，若此页为脏页，强制执行Checkpoint，将脏页刷新到磁盘
重做日志不可用时，刷新脏页重做日志出现不可用的情况是因为当前事务数据库系统对重做日志的设计都是循环使用的，并不是让其无限增大。重做日志可以被重用的部分是指重做日志已经不再需要，当数据库发生宕机时，数据库恢复操作不需要这部分的重做日志，因此就可以被覆盖重用。若此时重做日志还需要使用，必须强制产生Checkpoint，将缓冲池中的页至少刷新到当前重做日志的位置。

两种Checkpoint：

Sharp Checkpoint：发生在数据库关闭时将所有的脏页都刷新回磁盘（默认工作方式），参数innodb_fast_shutdown=1
Fuzzy Checkpoint：只刷新一部分脏页，不刷新所有的脏页回磁盘

posted @ 2016-08-18 08:29 弦断阅读(404) 评论(0) 编辑收藏举报

刷新页面返回顶部