开发者社区> 余二五> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

MySQL Study之--MySQL体系结构深入解析

简介:
+关注继续查看

MySQL体系架构

 

    由连接池组件、管理服务和工具组件、sql接口组件、查询分析器组件、优化器组件、缓冲组件、插件式存储引擎、物理文件组成。mysql是独有的插件式体系结构,各个存储引擎有自己的特点。


MySQL内存结构:


Mysql 进程结构

Mysql不像oracle那样是通过多进程来完成其功能的。默认情况下,InnoDB存储引擎的后台线程有7个:

4个IO thread,

1个master thread,

1个锁(lock)监控线程,

1个错误监控线程;

在InnoDB Plugin版本开始增加了默认IO thread的数量,默认的read thread和write thread分别增大到了4个,并且不再使用innodb_file_ io_threads参数,而是分别使用innodb_read_io_threads和innodb_write_io_threads参数。

查看mysql thread:

1
2
3
4
5
6
7
mysql> show variables like 'innodb_%version%'
+----------------+-------+  
| Variable_name  | Value |  
+----------------+-------+  
| innodb_version | 1.2.4 |  
+----------------+-------+  
1 row in set (0.00 sec)
  1. 1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    62
    63
    64
    65
    66
    67
    68
    69
    70
    71
    72
    73
    74
    75
    76
    77
    78
    79
    80
    81
    82
    83
    84
    85
    86
    87
    88
    89
    90
    91
    92
    93
    94
    95
    96
    mysql> show engine innodb status;</span></strong>  
    =====================================  
    150709 15:26:40 INNODB MONITOR OUTPUT  
    =====================================  
    Per second averages calculated from thelast 12 seconds  
    -----------------  
    BACKGROUND THREAD  
    -----------------  
    srv_master_thread loops: 0 srv_active, 0srv_shutdown, 804 srv_idle  
    srv_master_thread log flush and writes: 804  
    ----------  
    SEMAPHORES  
    ----------  
    OS WAIT ARRAY INFO: reservation count 2  
    OS WAIT ARRAY INFO: signal count 2  
    Mutex spin waits 0, rounds 0, OS waits 0  
    RW-shared spins 2, rounds 60, OS waits 2  
    RW-excl spins 0, rounds 0, OS waits 0  
    Spin rounds per wait: 0.00 mutex, 30.00RW-shared, 0.00 RW-excl  
    ------------  
    TRANSACTIONS  
    ------------  
    Trx id counter 5377  
    Purge done for trx's n:o < 4872 undo n:o< 0  
    History list length 1  
    LIST OF TRANSACTIONS FOR EACH SESSION:  
    ---TRANSACTION 0not started  
    MySQL thread id 3, OS thread handle0x7f1656c75700, query id 5 localhost root init  
    show engine innodb status  
    --------  
    FILE I/O  
    --------  
    <strong>I/O thread 0 state: waiting for completedaio requests (insert buffer thread)  
    I/O thread 1 state: waiting for completedaio requests (log thread)  
    I/O thread 2 state: waiting for completedaio requests (read thread)  
    I/O thread 3 state: waiting for completedaio requests (read thread)  
    I/O thread 4 state: waiting for completedaio requests (read thread)  
    I/O thread 5 state: waiting for completedaio requests (read thread)  
    I/O thread 6 state: waiting for completedaio requests (write thread)  
    I/O thread 7 state: waiting for completedaio requests (write thread)  
    I/O thread 8 state: waiting for completedaio requests (write thread)  
    I/O thread 9 state: waiting for completedaio requests (write thread)  
    Pending normal aio reads: 0 [0000] ,aio writes: 0 [0000] ,</strong>  
     ibufaio reads: 0, log i/o's: 0, sync i/o's: 0  
    Pending flushes (fsync) log: 0; bufferpool: 0  
    283 OS file reads, 5 OS file writes, 5 OSfsyncs  
    0.00 reads/s, 0 avg bytes/read, 0.00writes/s, 0.00 fsyncs/s  
    -------------------------------------  
    INSERT BUFFER AND ADAPTIVE HASH INDEX  
    -------------------------------------  
    Ibuf: size 1, free list len 0, seg size 2,0 merges  
    merged operations:  
     insert 0delete mark 0delete 0  
    discarded operations:  
     insert 0delete mark 0delete 0  
    Hash table size 276707, node heap has 0buffer(s)  
    0.00 hash searches/s, 0.00 non-hashsearches/s  
    ---  
    LOG  
    ---  
    Log sequence number 1611537  
    Log flushed up to   1611537  
    Pages flushed up to 1611537  
    Last checkpoint at  1611537  
    0 pending log writes, 0 pending chkp writes  
    8 log i/o's done, 0.00 log i/o's/second  
    ----------------------  
    BUFFER POOL AND MEMORY  
    ----------------------  
    Total memory allocated 137363456; inadditional pool allocated 0  
    Dictionary memory allocated 39010  
    Buffer pool size   8192  
    Free buffers       8040  
    Database pages     152  
    Old database pages 0  
    Modified db pages  0  
    Pending reads 0  
    Pending writes: LRU 0, flush list 0 singlepage 0  
    Pages made young 0not young 0  
    0.00 youngs/s, 0.00 non-youngs/s  
    Pages read 152, created 0, written 1  
    0.00 reads/s, 0.00 creates/s, 0.00 writes/s  
    No buffer pool page gets since the lastprintout  
    Pages read ahead 0.00/s, evicted withoutaccess 0.00/s, Random read ahead 0.00/s  
    LRU len: 152, 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  
    Main thread process no. 2461, id139733873489664, state: sleeping  
    Number of rows inserted 0, updated 0,deleted 0, read 0  
    0.00 inserts/s, 0.00 updates/s, 0.00deletes/s, 0.00 reads/s  
    ----------------------------  
    END OF INNODB MONITOR OUTPUT
  2. 1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    [root@mysrv ~]# mysql -u root -p  
    Enter password:  
    Welcome to the MySQL monitor.  Commands end with or \g.  
    Your MySQL connection id is 3  
    Server version: 5.6.4-m7-log Sourcedistribution  
        
    Copyright (c) 20002011, Oracle and/or itsaffiliates. All rights reserved.  
        
    Oracle is a registered trademark of OracleCorporation and/or its  
    affiliates. Other names may be trademarksof their respective  
    owners.  
        
    Type 'help;' or '\h' for help. Type '\c' toclear the current input statement.  
        
    <strong><span style="font-size:14px;">mysql> show variables like'innodb_%io%';</span></strong>  
    +---------------------------------+---------+  
    | Variable_name                   | Value   |  
    +---------------------------------+---------+  
    | innodb_additional_mem_pool_size | 8388608|  
    | innodb_io_capacity              | 200     |  
    | innodb_read_io_threads          | 4       |  
    | innodb_replication_delay        | 0       |  
    | innodb_use_native_aio           | ON      |  
    | innodb_version                  | 1.2.4   |  
    | innodb_write_io_threads         | 4       |  
    +---------------------------------+---------+  
    7 rows in set (0.01 sec)


后台线程主要作用有三个:

1. 刷新内存池中的数据,保证缓冲池中的内存缓存的是最近的数据

2. 将修改的数据文件刷新到磁盘文件

3. 在数据库发生异常情况下,保证InnoDB能恢复到正常运行状态

    master thread的线程优先级别最高。其内部由几个循环(loop)组成:主循环(loop)、后台循环(backgroundloop)、刷新循环(flush loop)、暂停循环(suspend loop)。master thread会根据数据库运行的状态在loop、background loop、 flush loop和suspend loop中进行切换。loop称为主循环,因为大多数的操作都在这个循环中,其中有两大部分操作:每秒钟的操作和每10秒的操作.

    Loop为主循环,如果没有用户活动,就切换到backgroundloop中,然后跳转会loop,如果还需要flush刷新,那就转到flush loop,如果在flush loop中也空闲,那就切换到suspend loop中,将master thread挂起等待事件的发生。

 

Mysql 进程结构




Loop:

每秒一次的操作包括:

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

   合并插入缓冲(可能)。

   至多刷新100个InnoDB的缓冲池中的脏页到磁盘(可能)。

   如果当前没有用户活动,切换到background loop(可能)。

接着来看每10秒的操作,包括如下内容

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

   合并至多5个插入缓冲(总是)。

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

   删除无用的Undo页(总是)。

   刷新100个或者10%脏页到磁盘(总是)。

   产生一个检查点(总是)。

Background loop会执行以下操作

   删除无用的Undo页(总是)。

   合并20个插入缓冲(总是)。

   跳回到主循环(总是)。

   不断刷新100个页,直到符合条件(可能,跳转到flush loop中完成)。

Mysql进程结构--控制磁盘io的参数

  innodb_io_capacity

  用来表示磁盘IO的吞吐量,默认值为200。对于刷新到磁盘的数量(脏缓冲),会按照innodb_io_capacity的百分比来刷新相对数量的页

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

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

  innodb_max_dirty_pages_pct

    默认值为75%。加快脏页刷新频率,减少恢复时间,也可保证磁盘IO负载。

  innodb_adaptive_flushing:

  在innodbplugin中。该值影响每1秒刷新脏页的数量。原来的刷新规则是:如果脏页在缓冲池所占的比例小于innodb_max_dirty_pages_pct时,不刷新脏页。大于innodb_max_dirty_pages_pct时,刷新100个脏页,而innodb_adaptive_flushing参数的引入,InnoDB存储引擎会通过一个名为buf_flush_get_desired_flush_rate的函数判断产生重做日志的速度来判断最合适的刷新脏页的数量。因此,当脏页的比例小于innodb_max_dirty_pages_pct时,也会刷新一定量的脏页。

  还有两个和mysql启动和关闭有关的参数

  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操作,也不将缓冲池中的数据脏页写回到磁

     盘,而是将日志写入到日志文件。MySQL下次启动时,会执行恢复操作。

  innodb_force_recovery

      影响InnoDB的恢复状况。默认为0,表示需恢复时执行所有的恢复操作。若不能有效恢复,则MySQL有可能宕机,错误信息会被写入错误日志文件。还有 1~6等不同值,根据

  需要可以设置。

  0 :正常的关闭和启动,不会做任何强迫恢复操作;

  1 :跳过错误页,让mysqld服务继续运行。跳过错误索引记录和存储页,可以执行备份操作

  2 :阻止InnoDB的主线程运行。清理操作时出现mysqld服务崩溃,则会阻止数据恢复操作;

  3 :恢复的时候,不进行事务回滚;

  4 :阻止INSERT缓冲区的合并操作。不做合并操作,为防止出现mysqld服务崩溃。不计算表的统计信息

  5 :mysqld服务启动的时候不检查回滚日志:InnoDB引擎对待每个不确定的事务就像提交的事务一样;

  6 :不做事务日志前滚恢复操作;

 

Mysql内存结构—buffer pool

    InnoDB存储引擎内存主要由几个部分组成:缓冲池(bufferpool)、重做日志缓冲池(redo log buffer)以及额外的内存池(additional memory pool)

缓冲池是占内存最大的部分,用来存放各种数据的缓存。因为InnoDB的存储引擎的工作方式总是将数据库文件按页(每页16K)读取到缓冲池,然后按最近最少使用(LRU)的算法来保留在缓冲池中的缓存数据。如果数据库文件需要修改,总是首先修改在缓存池中的页(发生修改后,该页即为脏页),然后再按照一定的频率将缓冲池的脏页刷新(flush)到文件。

buffer pool是通过三种list来管理的:

1) free list

2) lru list

3) flush list

buffer pool中的最小单位是page,在innodb中定义三种page

1) free page :此page未被使用,此种类型page位于free链表中

2) clean page:此page被使用,对应数据文件中的一个页面,但是页面没有被修改,此种类型

  page位于lru链表中

3) dirty page:此page被使用,对应数据文件中的一个页面,但是页面被修改过,此种类型page

   位于lru链表和flush链表中

 

插入缓冲:

      不是缓冲池的一部分,InsertBuffer是物理页的一个组成部分,它带来InnoDB性能的提高。根据B+算法(下文会提到)的特点,插入数据的时候会主键索引是顺序的,不会造成数据库的随机读取,而对于非聚集索引(即辅助索引),叶子节点的插入不再是顺序的了,这时需要离散地访问非聚集索引,插入性能在这里变低了。InnoDB引入插入缓冲,判断非聚集索引页是否在缓冲池中,如果在则直接插入;不在,则先放在插入缓冲区中。然后根据上述master thread中介绍的,会有一定的频率将插入缓冲合并。此外,辅助索引不能是唯一的,因为插入到插入缓冲时,并不去查找索引页的情况,否则仍然会造成随机读,失去插入缓冲的意义了。插入缓冲可能会占缓冲池中内存,默认也能会占到1/2,所以可以将这个值调小点,到1/3。通过IBUF_POOL_SIZE_PER_MAX_SIZE来设置,2表示1/2,3表示1/3。

两次写: 

     它带来InnoDB数据的可靠性。如果写失效,可以通过重做日志进行恢复,但是重做日志中记录的是对页的物理操作,如果页本身损坏,再对其进行重做是没有意义的。所以,在应用重做日志前,需要一个页的副本,当写入失效发生时,先通过页的副本来还原该页,再进行重做,这就是doublewire。
恢复数据=页副本+重做日志 

 

Mysql内存结构—log buffer与管理池

日志缓冲:

     日志缓冲将重做日志信息先放入这个缓冲区,然后按一定频率将其刷新到重做日志文件。该值一般不需要设置为很大,因为一般情况下每一秒钟就会将重做日志缓冲刷新到日志文件,因此我们只需要保证每秒产生的事务量在这个缓冲大小之内即可。

额外的内存池:

      额外的内存池同样十分重要。在InnoDB存储引擎中,对内存的管理是通过一种称为内存堆(heap)的方式进行的,他是用于缓存InnoDB引擎的数据字典信息和内部数据结构,在对一些数据结构本身分配内存时,需要从额外的内存池中申请,当该区域的内存不够时,会从缓冲池中申请,若是mysqld服务上的表对象数量较多,InnoDB引擎数据量很大,且innodb_buffer_pool_size的值设置较大,则应该适当地调整innodb_additional_mem_pool_size的值。若是出现缓存区的内存不足,则会直接向操作系统申请内存分配,并且会向MySQL的error log文件写入警告信息。


binlog和redolog区别:

1) binary log,会记录所有与MySQL有关的日志记录,包括 Innodb、MyISAM、Heap等其他存储引

    擎的日志而 innodb存储引擎的重做日志(redolog),只记录有关其本身的事务日志   

    
2) 记录内容不同:
      binary log:不管你将二进制日志文件的记录格式设置为STATEMENT,还是ROW,又或是

                 MIXED, 其记录的都是关于一个事务的具体操作内容.
      redo log:  记录innodb 每个页的更改的物理情况

3)写入时间不同:
      binary log:事务提交前进行记录
      redo log :事物进行过程中,不断写入。










本文转自 客居天涯 51CTO博客,原文链接:http://blog.51cto.com/tiany/1672606,如需转载请自行联系原作者

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
+关注
20376
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
低代码开发师(初级)实战教程
立即下载
阿里巴巴DevOps 最佳实践手册
立即下载
冬季实战营第三期:MySQL数据库进阶实战
立即下载