首页 > 代码库 > Mysql 笔记(一)

Mysql 笔记(一)

InnoDB存储引擎

mysql 存储引擎(好难用,看https://www.zybuluo.com/eqyun/note/27850)


简介

InnoDB是事务安全的MySQL存储引擎,从MySQL5.5版本开始是默认的表存储引擎,是第一个完整支持ACID事务的MySQL存储引擎,其特点是行锁设计、支持MVCC、支持外键、提供一致性锁定读,同时被设计用来最有效地利用以及使用内存和CPU

InnoDB存储引擎体系架构

后台线程(多个)->InnoDB存储引擎内存池->物理文件

后台线程

1. Master Thread

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

2. IO Thread

在InnoDB中大量使用了AIO(Async IO)来处理IO 请求,IO Thread主要是负责这些IO请求的回调处理。Io Thread共有4类:writereadinsert bufferlog IO thread。在InnoDB 1.0.x 版本开始,read thread和write thread分别增加大4个,用innodb_file_io_threadsinnodb_write_io_threads参数来设置,如
可以看到thread 0为insert buffer thread,IO thread 1为log thread.

    mysql> show engine innodb status\G;    *************************** 1. row ***************************    Type: InnoDB    Name:    ....    --------    FILE I/O    --------    I/O thread 0 state: waiting for completed aio requests (insert buffer thread)    I/O thread 1 state: waiting for completed aio requests (log thread)    I/O thread 2 state: waiting for completed aio requests (read thread)    I/O thread 3 state: waiting for completed aio requests (read thread)    I/O thread 4 state: waiting for completed aio requests (read thread)    I/O thread 5 state: waiting for completed aio requests (read thread)    I/O thread 6 state: waiting for completed aio requests (write thread)    I/O thread 7 state: waiting for completed aio requests (write thread)    I/O thread 8 state: waiting for completed aio requests (write thread)    I/O thread 9 state: waiting for completed aio requests (write thread)

 

3. Purge Thread

事务提交后,使用的undolog可能不再需要,因此需要PurgeThread 来回收已经使用并分配的undo页面。在InnoDB1.1版本前,它是在Master线程中完成的,InnoDB1.1后,独立一条线程。在配置文件中 启用独立Purge Thread线程配置为:

    [mysqld]    innnodb_purge_threads=1

 

在1.2版本后支持多个Purge Thread,加书undo页的回收,可用以后语句查询:

    mysql> show variables like innodb_purge_threads\G;    *************************** 1. row ***************************    Variable_name: innodb_purge_threads    Value: 1    1 row in set (0.00 sec)    ERROR:    No query specified

 

4. Page Cleaner Thread

Page Cleaner Thread是在InnoDB 1.2x版本加入的,作用是将之前版本中脏页的刷新操作都加入到单独的线程中完成,目的是为了减轻原Master Thread的工作和用户查询线程的阻塞。

内存

1. 内存池

InnoDB是基于磁盘存储的,并将记录按照页的方式进行管理,内存池就是为了解决CUP速度与磁盘速度之间的鸿沟。

在数据库中进行记取页的操作,首先将磁盘的页放到缓冲池,这个过程称将页FIX在缓冲池。下一次再读相同的页时,首先读缓冲池,有直接读,没有再去读磁盘

对数据库中页的修改也首先修改缓冲池,再以一定的频率刷新到磁盘上。缓冲池的大小直接影响数据库的整体性能

在InnoDB下,其缓冲池配置可通过innodb_buffer_pool_size来设置,查询语句为:

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

 

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

从InnoDB 1.0.x版本开始,允许多个缓冲池实例,这样做的好处是减少数据库内部的资源竞争,增加数据库的并发能力,可以通过innodb_buffer_pool_instances来配置,默认为1,查询语句为:

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

 

查看缓冲池的使用状态可以通过以下语法查询:

    mysql> use information_schema;    Database changed    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: 7606    database_pages: 586    1 row in set (0.00 sec)

 

2. LRU List、Free List和 Fush List

主要设计对内存池的管理,算法理论太强,看得头痛

通常来说,数据库中的缓冲池是通过LRU(Lastest Recent Used,最近最少使用)算法来管理的。

在InnoDB中,缓冲池页的大小默认为16k

LRU List:最频繁使用的页在LRU列表的首端,最少使用的在尾端,先释放尾端的页

InnoDB中,优化了LRU算法,在列表中加入了midpoint位置,就是说,新加入的页不是直接放在首部,而是放在LRU列表中的 midpoint位置,这个算法称为midpoint insertion strategy,默认下该位置 是在LRU列表长度的5/8,可通过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 (0.00 sec)

 

在innoDB中,midpoint之后的列表称为old列表,之前的为new列表

优化还加入了另一个参数innodb_old_blocks_time,用来表示页读取到mid位置后需要等待多久才会被加入到LRU列表的new列表,因此可以通过修改这个值来尽可以使LRU列表中的热点数不被刷出。

    mysql> show variables like innodb_old_blocks_time\G;    *************************** 1. row ***************************    Variable_name: innodb_old_blocks_time    Value: 0    1 row in set (0.00 sec)

 

够狠,默认为0

当页从LRU列表的old部分加入new部分,这个操作称为page make young,而因innodb_old_blocks_time的设置而导致没有从old移动new的操作称为page not make young,可以通过以下方法查看:

    mysql> show engine innodb status\G;    *************************** 1. row ***************************    Type: InnoDB    Name:    Status:    =====================================    141024 7:50:59 INNODB MONITOR OUTPUT    =====================================    ....    ----------------------    BUFFER POOL AND MEMORY    ----------------------    Total memory allocated 137363456; in additional pool allocated 0    Dictionary memory allocated 219529    Buffer pool size 8192    Free buffers 5528    Database pages 2661    Old database pages 1002    Modified db pages 374    Pending reads 0    Pending writes: LRU 0, flush list 0, single page 0    Pages made young 0, not young 0    0.00 youngs/s, 0.00 non-youngs/s    Pages read 2655, created 6, written 41    40.57 reads/s, 0.12 creates/s, 0.80 writes/s    Buffer pool hit rate 986 / 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: 2661, unzip_LRU len: 0    I/O sum[0]:cur[32], unzip sum[0]:cur[0]

 

可以看到buffer pool size有8192,即8198*16=127M左右,(真少)
Free buffers表示当前Free列表中页的数量
Database pages表示LRU列表页的数量
youngs/s表示第秒page make young的次数
non_yongs/s:每秒page not make young的次数

重要:Buffer pool hit rage,表示缓冲池的命中率,这个通常不小于95%,如果小于95%,请观察是否由于全表扫描引起的LRU列表被污染

可以通过以下语法看缓冲池的使用情况:

    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: 998    pages_made_young: 6580    pages_not_made_young: 0    1 row in set (0.00 sec)

 

可以通过以下语句看LRU表的暗体情况:

    mysql> select table_name,space,page_number,page_type from innodb_buffer_page_lru where space=1;    Empty set (0.16 sec)

 

没东西....

InnoDB支持压缩功能,原来16K的页压缩为1K

LRU列表中的页被修改后,称为脏页(dirty page),即缓冲页面上的数据产生了不一致,这时候数据库会通过CHECKPIOINT机制将脏页刷新回磁盘,即Flush列表中的页为脏页列表。

show engine innodb status
Modified db pages为是脏页数目

3. 重做日志缓冲

InnoDB内存区除了缓冲池外,还有重做日志缓冲(redolog buffer)。Innodb首先将重做日志放到这个缓冲,再一定频率刷新到重做日志文件。重做缓冲不用太多,因为每一秒就会刷新到日志文件,默认为8M,可通过innodb_log_buffer_size控制

把日志缓冲刷新到日志文件中的情况有以下三种 :
a. Master Thread每一秒将重做日志缓冲刷新到重做日志文件中
b. 每个事务提交时
c.当重做日志缓冲池剩余空间小于1/2时

4. 额外的内存池

在InnoDB中,对内存的管理是通过一种 称为内存堆 (heap)的方式进行的,在对一些数据结构本身的内存进行分配 时,需要从额外内存池中申请,当该区不够时,会从缓冲池中申请。
例如:分配 了多个缓冲池,但是每个缓冲池的帧缓冲(frame buffer)还有对应的缓冲控制对象(buffer contrl block),这些对象记录一些如LRU、锁、等待等信息

CheckPoint

上面提到需要把脏页刷新到磁盘,若每一次弄脏的页都刷新,那开销太大,但是如果脏了不刷新,突然停电死机了,那数据就不能恢复了。为了避免这种情况,事务数据库都采用了Write Ahead Log策略,就是当事务提交时,先做重做日志,再修改页。当死机时,可以通过重做日志来完成数据的恢复。

CheckPoint是为了解决以下问题:
a. 缩短数据库的恢复时间
b. 缓冲池不够用时,将脏页刷新回磁盘
c. 重做日志不可用时,刷新脏页

CheckPoint分两种 :
a. Sharp Checkpoint
b. Fuzzy Checkpoint

Sharp Checkpoint 发生 在数据库关闭时将所有的脏页刷回磁盘
在数据库运行时运行的是Fuzzy Checkpoint,就是只刷新一部分脏页,而不是刷新所有的脏页

Fuzzy CheckPoint分以下几种 情况:
a. Master Thread Checkpoint
b. Flush_lru_list checkpoint
c. Async/Sync Flush Checkpoint
d. Dirty Page too much Checkpoint

Master Thread Checkpoint 差不多以每秒的速度从缓冲池刷新一定比例回磁盘

Flush_lru_list checkpoint是在移除LRU列表的尾端的页面时,刷新脏页的操作
Ayync/Sync Flush Checkpoiint是在重做日志不可用时,强制刷新

Dirty page too mush是在脏页太多的情况下,强制刷新。可用 innodb_max_dirty_pages_pct来控制

Mysql 笔记(一)