浅析 InnoDB 存储引擎的工作流程

InnoDB

InnoDB 是由 Innobase Oy 公司开发,该存储引擎是第一个完整支持 ACID 事务的 MySQL 存储引擎。具有插入缓存两次写自适应哈希索引等关键特性,是一个高性能、高可用的存储引擎。

整体架构

InnoDB 有多个内存块,这些内存块组合在一起组成了一个大的内存池。而 InnoDB 的内存池中会有多个后台线程,这些后台线程负责刷新内存池中的数据,和将脏页(已修改的数据页)刷新到磁盘文件。

后台线程

默认情况下,InnoDB 存储引擎有 13 个后台线程:

  • 一个 master 线程
  • 一个锁监控线程
  • 一个错误监控线程
  • 十个 IO 线程
    • 插入缓存线程
    • 日志线程
    • 读线程(默认 4 个)
    • 写线程(默认 4 个)

下面是我本机上的十个 IO 线程

--------
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, 0] , aio writes: 0 [0, 0, 0, 0] ,
 ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
540 OS file reads, 89 OS file writes, 7 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s

内存池

InnoDB 存储引擎的内存池包含:缓冲池、日志缓存池、额外内存池。这些内存的大小分别由配置文件中的参数决定。其中占比最大的是缓冲池,里面包含了数据缓存页、索引、插入缓存、自适应哈希索引、锁信息和数据字典。InnoDB 会在读取数据库数据的时候,将数据缓存到缓冲池中,而在修改数据的时候,会先把缓冲池中的数据修改掉,一旦修改过的数据页就会被标记为脏页,而脏页则会被 master 线程按照一定的频率刷新到磁盘中。日志缓存则是缓存了redo-log 信息,然后再刷新到 redo-log 文件中。额外内存池则是在对一些数据结构本身分配内存时会从额外内存池中申请内存,当该区域内存不足则会到缓冲池中申请。

内存结构

Master Thread

InnoDB 存储引擎的主要工作都在一个单独的 Master Thread 中完成,其内部由四个循环体构成:主循环( loop )、后台循环( background loop )、刷新循环( flush loop )、暂停循环( suspend loop )。具体工作流程如下图所示:

mysql_thread

主循环

主要负责将缓冲池中的日志文件刷新到磁盘中、合并插入缓存、刷新缓冲池中的脏页数据到磁盘中、删除无用的 Undo 页、产生一个 checkpoint 。在主循环中会多次将脏页刷新到磁盘中,但是有一些刷新任务总会执行,有一些则根据参数来判断当前是否需要刷新。而这个参数 innodb_max_dirty_pages_pct 最大脏页比例是通过配置文件决定的,你可以根据实际情况来调整你自己的最大脏页比例,来达到最好的性能。

伪代码如下:

for (int i = 0; i<10; i++) {
    thread_sleep(1)
    do log buffer flush to disk
    if ( last_one_second_ios < 5) {
        do merge at most 5 insert buffer
    }
    if (buf_get_modified_ratio_pct > innodb_max_ditry_pages_pct) {
        do buffer pool flush 100 dirty page
    }
    if (no user activity) {
        goto background loop
    }
}
if (last_ten_second_ios < 200) {
    do buffer pool flush 100 dirty page
}
do merge at most 5 insert buffer
do log buffer flush to disk
do full pourge
if (buf_get_modifued_ratio_pct > 70%) {
    do buffer pool flush 100 dirty page
} else {
    buffer pool flush 10 dirty page
}
do fuzzy checkpoint
goto loop

后台循环

在后台循环中 InnoDB 会做这些事:删除无用的Undo页、合并插入缓存。如果当前 InnoDB 处于空闲状态,则跳转到刷新循环,否则跳转到主循环继续处理数据。

伪代码如下:

do full purge
do merge 20 insert buffer
if (not idle) {
    goto loop
} else {
    goto flush loop
}

刷新循环

一旦执行到刷新循环,InnoDB 会一直处理脏页数据,直到脏页数据达到最大脏页比例以下。这时候会跳转到暂停循环中(所有数据都处理完毕)。

伪代码如下:

flush loop:
do buffer pool flush 100 dirty page
if (buf_get_modified_ratio_pct > innodb_max_dirty_pages_pct) {
    goto flush loop
} else {
    goto suspend loop
}

暂停循环

在本循环中,InnoDB会将 Master Thread 挂起,减少内存资源使用,一直处于 waiting 状态,等待事件来唤醒。一旦有新的事件过来,就跳转到主循环中。

伪代码如下:

suspend loop:
suspend_thread()
waiting event
goto loop;=

由此可以看出,master 线程的最大的工作内容就是刷新脏页数据到磁盘了。这一步就是把缓存池中被修改的数据页同步到磁盘中。而脏页数据的刷新基本上都是由 innodb_max_dirty_pages_pct 来控制的,所以当你的服务器处理能力比较强,给 InnoDB 分配的内存池比较大,这时候可能你的脏页数据会很难达到最大脏页比,这时候你的数据基本上都在缓冲池中,可能需要很长一段时间才会到数据库磁盘文件中,也就是脏页的刷新速度会很低(MySQL 5.1之前的版本默认是 90%,后面调整到 75%)。所以实际应用中可以根据自己内存和数据库的读写量来设置这个最大脏页比。对于一次刷新脏页数量的设置,在 InnoDB Plugin 中有一个参数 innodb_adaptive_flushing 自适应刷新,InnoDB 会根据产生的重做日志速度来计算出当前最适合的刷新脏页数量。当然 InnoDB Plugin 中还有其它很多参数配置,合理利用这些配置可以极大的提升 InnoDB 存储引擎的性能。

关键特性

前面说到 InnoDB 的三大特性分别为:插入缓存、两次写、自适应哈希索引。下面就简单介绍下这三大特性。

插入缓存

当我插入一条数据,该数据只有一个 ID 索引(聚集索引数据行的物理顺序与列值的逻辑顺序相同)的时候,并且 ID 是自增长的,这时候页中的行记录按照 ID 顺序存放,所以只需要在最新页插入数据即可。但是如果我的表有多个非聚集索引:该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同,在插入的时候非聚集索引的插入不再是顺序的,这时候要离散的访问非聚集索引页,导致插入性能变低。而插入缓存则在插入的时候判断缓冲池中是否存在当前非聚集索引,如果存在则直接插入,否则先插入到一个缓存区,然后再通过 Master Thread 来合并插入缓存。这样极大的提高了数据的写性能。

两次写

两次写是为了解决在将缓冲池中的脏页刷新到磁盘的过程中,操作系统出现故障,导致当前的脏页部分写失效的问题。通过两次写在下次恢复的时候,InnoDB 会根据两次写的结果来恢复数据。

原理:在刷新脏页的时候,不是直接把脏页数据刷新到磁盘,而是将脏页先写到一个大小为2M的内存缓存中,再将这个内存缓存数据同步到磁盘的共享表空间中。当全部都写到共享表空间后,再将数据刷新到磁盘中。这样如果发生了上面描述的情况,这时候数据会在共享表空间中有个备份,恢复的时候就可以使用共享表空间的数据。

如果有数据库集群的情况下,master数据库是一定要开启两次写的,为了保证数据可靠性。而从数据库可以通过参数 skip_innodb_doublewrite 来禁止两次写功能,来提高插入效率。

自适应哈希索引

InnoDB 会监控对表示的索引查找,如果发现可以通过对索引进行哈希来优化搜索。这时候会对当前的索引建立哈希索引。称之为自适应哈希索引( AHI )。可以通过参数 innodb_adaptive_hash_index 来禁用或启用此特性。

小结

总体来说 InnoDB 的高性能体现在:插入数据的时候先保存在内存中,直接跟内存交互性能比较好,而且还有插入缓存优化,保证了高并发写操作。高可用则表现在两次写特性,保证了机器宕机或者出故障的时候数据不会丢失。这里只是简单介绍了一下 InnoDB 的工作流程和一些特性,当然 InnoDB 还有很多很多强大的功能,比如说事务、锁、索引、算法等等有兴趣的同学可以参考《 MySQL 技术内幕 InnoDB 存储引擎》这本书深入了解。

0

介绍一个 MySQL 自动化运维利器 – Inception

引子

最近打算做一个 MySQL 的数据库运维平台。这里面有一个非常重要的功能就是 SQL 的审核,如果完全靠人工去实现就没必要做成一个平台了。正没头绪如何去实现的时候,google 了一下,看下有没有现成的开源方案。果不其然,github 上发现一个『去哪儿网』开源的一个数据库运维工具 Inception, 它是一个集审核、执行、备份及生成回滚语句于一身的 MySQL 自动化运维工具。

Inception 介绍

Inception 的架构图如下图所示,简单来说,Inception 就是一个 MySQL 的代理,能够帮助你审核 SQL,执行 SQL,备份 SQL 影响的记录。Inception 是一个 C/S 的软件架构。我们可以通过原生的 MySQL 客户端 去连接,也可以通过远程的接口去连接,目前执行只支持通过C/C++接口、Python接口来对Inception访问

inception-architecture.png

执行流程图如下:

image

安装 Inception

我安装的环境
OS: Ubuntu 16.04.2 LTS

安装依赖

  • 下载bison: 版本最好是2.6之前的(Ubuntu 16.04.2 LTS 版本下安装的是 bison-2.5.1),最新的可能会有问题,下载之后,需要自己编译源码来安装,具体安装方法,可以参数网上的一些说明。
  • cmake安装:apt-get install cmake
  • ncurses安装:apt-get install libncurses5-dev
  • 安装openssl:apt-get install libssl-dev
  • 安装g++:sudo apt-get install g++
  • 安装m4: apt-get install m4

编译安装 Inception

git clone https://github.com/mysql-inception/inception.git
sh inception_build.sh debug [linux]  (如果不指定就是linux平台,而如果要指定是Xcode,就后面指定Xcode)

可执行文件在 debug/sql/Debug/ 目录下面(不同平台有可能不相同)。

启动 Inception

创建一个配置文件 inc.cnf, 里面主要是配置 Inception 启动的端口,SQL 审核的策略,备份数据库的配置等等,更多可参考官方文档

[inception]
general_log=1
general_log_file=inception.log
port=6669   # Inception 的监听的端口
socket=/tmp/inc.socket
character-set-client-handshake=0
character-set-server=utf8
inception_remote_system_password=root  # 备份数据库密码
inception_remote_system_user=wzf1      # 备份数据库用户名
inception_remote_backup_port=3306      # 备份数据库端口
inception_remote_backup_host=127.0.0.1 # 备份数据库地址
inception_support_charset=utf8mb4
inception_enable_nullable=0
inception_check_primary_key=1
inception_check_column_comment=1
inception_check_table_comment=1
inception_osc_min_table_size=1
inception_osc_bin_dir=/data/temp
inception_osc_chunk_time=0.1
inception_enable_blob_type=1
inception_check_column_default_value=1

启动

./Inception --defaults-file=inc.cnf

访问
1. 通过原生的 MySQL 客户端的方式。主要注意的是,请不要将的 SQL 语句块,放到 MySQL 客户端中执行,因为这是一个自动化运维工具,如果使用交互式的命令行来使用的话没有意义,所有的 SQL 执行应该都通过接口的方式,这个方式仅仅可用来查看和设置上诉配置文件里的配置,如 inception get variables; 可查看所有的变量,更多请参考官方文档

mysql -uroot -h127.0.0.1 -P6669
  1. 通过接口的方式。下面是官方示例中的 Python 代码,需要注意的是如果使用 Python3 的 pymsql 去连接会有异常,目前的解决方案是需要修改 pymysql 的源码,具体 issue
#!/usr/bin/python
#-\*-coding: utf-8-\*-
import MySQLdb
sql='/*--user=username;--password=password;--host=127.0.0.1;--execute=1;--port=3306;*/\
inception_magic_start;\
use mysql;\
CREATE TABLE adaptive_office(id int);\
inception_magic_commit;'
try:
    conn=MySQLdb.connect(host='127.0.0.1',user='',passwd='',db='',port=9998)
    cur=conn.cursor()
    ret=cur.execute(sql)
    result=cur.fetchall()
    num_fields = len(cur.description) 
    field_names = [i[0] for i in cur.description]
    print field_names
    for row in result:
        print row[0], "|",row[1],"|",row[2],"|",row[3],"|",row[4],"|",
        row[5],"|",row[6],"|",row[7],"|",row[8],"|",row[9],"|",row[10]
    cur.close()
    conn.close()
except MySQLdb.Error,e:
     print "Mysql Error %d: %s" % (e.args[0], e.args[1])

SQL 审核 & 执行

通过 Inception 对语句进行审核时,必须要告诉 Inception 这些语句对应的数据库地址、数据库端口以及
Inception 连接数据库时使用的用户名、密码等信息,而不能简单的只是执行一条 sql 语句,所以必须要通过某种方式将这些信息传达给 Inception。

连接信息放在 /* ... */ 的注释中,真正的 SQL 语句则包括在 inception_magic_startinception_magic_commit:

/*--user=zhufeng;--password=xxxxxxxxxxx;--host=xxxxxxxxxx;
--enable-check;--port=3456;*/  
inception_magic_start;  
use mysql;  
CREATE TABLE adaptive_office(id int);  
inception_magic_commit;

连接的信息里可以配置更多的信息,比如关闭备份等等,具体请参考官方文档

审核

审核的规范见官方文档,有些规范是可配置的,可根据自己公司的规范在 Inception 的配置文件中配置。

执行

注意下,官方说是支持 DDL,DML 语句的,但是并不支持 SELECT 查询。

inception_accept.png

比如通过 Inception 执行一个建表语句:

...
inception_magic_start;  
use mysql;  
CREATE TABLE adaptive_office(id int);  
inception_magic_commit;
...

返回结果, 可见是每一条 SQL 就会返回一个可执行的结果,errlevel 非 0 时表示执行失败,下面所示中的第二条 SQL 语句 Audit completed(审核完成) 但是不符合建表的规范,更多关于返回结果的说明可见官方文档

'ID', 'stage', 'errlevel', 'stagestatus', 'errormessage', 'SQL', 'Affected_rows', 'sequence', 'backup_dbname', 'execute_time', 'sqlsha1'
1 | CHECKED | 0 | Audit completed | None | use inception_test | 0 | '0_0_0' | None | 0 | 
2 | CHECKED | 1 | Audit completed | Set engine to innodb for table 'adaptive_office'.
Set charset to one of 'utf8mb4' for table 'adaptive_office'.
Set comments for table 'adaptive_office'.
Column 'id' in table 'adaptive_office' have no comments.
Column 'id' in table 'adaptive_office' is not allowed to been nullable.
Set Default value for column 'id' in table 'adaptive_office'
Set a primary key for table 'adaptive_office'. | CREATE TABLE adaptive_office(id int) | 0 | '0_0_1' | 10_10_1_67_1028_inception_test | 0 | 

备份功能

前提条件

  • 线上服务器必须要打开 binlog,不然不会备份及生成回滚语句。
  • 参数 binlog_format 必须要设置为 mixed 或者 row 模式,通过语句:set global binlog_format=mixed/row 来设置,如果是 statement 模式,则不做备份及回滚语句的生成。
  • 被影响的行中必须存在主键,因为回滚语句的 WHERE 条件就是主键。比如,我插入一条数据并返回主键 id=1, 那么相应的它就会反向生成一个删除语句 (WHERE 的条件就是主键) DELETE FROM xx WHERE id = 1

Inception 在做 DML 操作时具有备份功能(默认开启,可通过在执行 SQL 中注释文件中指定 --disable-remote-backup),它会将所有当前语句修改的行备份下来,存储到一个指定的备份库中, 备份库通过配置 Inception 参数来指定。

关于备份数据库的命名方式,备份机器的库名组成是由线上机器的 IP 地址的点换成下划线,再加上端口号,再加上库名三部分,这三部分也是通过下划线连接起来的。例如:我执行 DML 操作的数据库地址是 192.168.1.1, 端口是 3306, 库名是 inceptiondb, 则在备份数据库中表名为:192_168_1_1_3306_inceptiondb

比如,我有一个 inception_test 库,其中有一张 userinfo 表,就两个字段:

userinfo.png

我通过 Inception 去执行一个 INSERT 一条记录:

/*--user=root;--password=xxx;--host=1.1.1.1;--execute=1;--port=3306;--sleep=0;--enable-remote-backup;*/\
inception_magic_start;\
use inception_test; \
insert into userinfo(`username`) values("test");\
inception_magic_commit;

返回的结果如下, 可以看到已经执行成功并且备份成功了:

2 | EXECUTED | 0 | Execute Successfully
Backup successfully | None | insert into userinfo(`username`) values("test") | 1 | '1533716166_25519001_1' | 1_1_1_1_3306_inception_test | 0.060 | 

查看下备份数据库中的 1_1_1_1_3306_inception_testuserinfo 表的结果, 根据 INSERT 的语句相应地生成了一条 DELETE 语句:

DELETE FROM `inception_test`.`userinfo` WHERE id=4;

那么,我需要如果正确地找到回滚的语句呢?

可以查看下备份库 1_1_1_1_3306_inception_testuserinfo 的表结构:

backup_userinfo.png

主要有两个字段:

  • rollback_statement text: 生成修改的回滚语句。
  • opid_time varchar(50): 这个列存储的是的被执行的 SQL 语句在执行时的一个序列号,这个序列号由三部分组成:timestamp(int 值,是语句被执行的时间点) + 线上服务器执行时所产生的 thread_id + 当前这条语句在所有被执行的语句块中的一个序号组成。可见上面的结果:1533716166_25519001_1, 这个序列号同时也会出现在执行返回的结果中,所有需要回滚就是根据这个序列号去备份表中查询回滚的 SQL 语句。

更多说明,请参考官方文档中的备份功能说明

最后

有了这么好用的工具,基于这个为基础,我们通过一个 WEB 应用做一个权限审批管理等功能,一个数据库运维平台就可以实现了,真的需要自己去写吗?我有发现了一个基于 Inception 实现的一个数据库运维平台 Yearning

感谢开源!

参考

0