ACCESS020:Buffer Pool 、压缩页、CheckPoint、Double Write、Change Buffer

5.4. Insert/Change Buffer 查看

mysql gcdb@localhost:employees> show engine innodb status;
+--------+------+----------------------------------------------------------------------------------+
| Type   | Name | Status                                                                           |
+--------+------+----------------------------------------------------------------------------------+
| InnoDB |      |                                                                                  |
|        |      | =====================================                                            |
|        |      | 2018-01-08 14:39:01 0x7fa8c2470700 INNODB MONITOR OUTPUT                         |
|        |      | =====================================                                            |
|        |      | Per second averages calculated from the last 18 seconds                          |
|        |      | -----------------                                                                |
|        |      | BACKGROUND THREAD                                                                |
|        |      | -----------------                                                                |
|        |      | srv_master_thread loops: 175 srv_active, 0 srv_shutdown, 876729 srv_idle         |
|        |      | srv_master_thread log flush and writes: 876841                                   |
|        |      | ----------                                                                       |
-----------省略其他输出-------------
|        |      | -------------------------------------                                            |
|        |      | INSERT BUFFER AND ADAPTIVE HASH INDEX                                            |
|        |      | -------------------------------------                                            |
|        |      | Ibuf: size 1, free list len 0, seg size 2, 2 merges                              |
|        |      | merged operations:                                                               |
|        |      |  insert 0, delete mark 2, delete 0                                               |
|        |      | discarded operations:                                                            |
|        |      |  insert 0, delete mark 0, delete 0                                               |
|        |      | Hash table size 2656009, node heap has 0 buffer(s)                               |
|        |      | Hash table size 2656009, node heap has 0 buffer(s)                               |
|        |      | Hash table size 2656009, node heap has 1 buffer(s)                               |
|        |      | Hash table size 2656009, node heap has 0 buffer(s)                               |
|        |      | Hash table size 2656009, node heap has 1 buffer(s)                               |
|        |      | Hash table size 2656009, node heap has 1 buffer(s)                               |
|        |      | Hash table size 2656009, node heap has 1 buffer(s)                               |

1.seg
size:的数目,例如当前页为8K,则seg\_size \* 8K就是Insert/Change Buffer应用的内部存款和储蓄器大小;
2.merges:合并了稍稍
3.merged insert:插入了略微条记录

  • insert / merges `即使插入的频率(插入一条记下,就要读取一回页);

4.discarded
operations:应该是十分小的值,或许为0;当记录写入到Insert/Change Buffer后,对应的表被删除了,则附和的Buffer中的记录就应当被遗弃;

使用Insert/Change Buffer的前提时,须求使用随机IO,那时才放入Buffer中,如若页已经在Buffer Pool(内存)中,就不需要使用Insert/Change Buffer了`

1.3.4.1使用mid point 的LRU算法

  • LRU是Least Recently Used的缩写,即最近最久未使用,常用于页面置换算法,是为虚构页式存款和储蓄管理服务的。
  • 当该页被第一次读取时,将该页先放在
    mid point的地方(因为不可能担保一定是生动活泼);
  • 取当被读到第二次 时,才将改页放入到 new page 的首部;
  • innodb_old_blocks_pct 参数控制 mid point 的位置,默认是 37
    ,即 3/8 的位置

ACCESS 1

LRU 中new page和old
page是在三个链表上的,想像成排队,访问多的就从mid point排到了链表的前面然后后冷的页就渐渐被挤到了old page中,借使old中的数据持续被一再造访,照旧会再次回到new page中。

  • 经过如下:
  • 1 : mid –> new
  • 2 : mid –> old –> new
  • 3 : mid –> old –> 刷回磁盘
  • 4 : new –> old –> 刷回磁盘
  • 当Free List中尚无空闲的页时,就供给从 old page
    中最后的页(被淘汰的页)给取出,给新的查询所使用
  • 假使被淘汰的页是脏页(page number在Flush List中),则须要先刷回磁盘后,再给新的查询使用

    mysql gcdb@localhost:(none)> show variables like “%innodb_old_blocks_pct%”
    +———————–+——-+
    | Variable_name | Value |
    +———————–+——-+
    | innodb_old_blocks_pct | 37 | –调整为innodb_buffer_pool_size为8G
    +———————–+——-+
    1 row in set
    Time: 0.012s

  • 幸免扫描语句污染LRU

  • 当使用 select * from tablename;
    时,该语句会读取有些页很频仍(即该页恐怕被读取了两遍以上,读取一条记下,就要求读一回页

2.1 查找Buffer Pool中的压缩页

mysql gcdb@localhost:information_schema> desc information_schema.`INNODB_BUFFER_PAGE_LRU`
+---------------------+---------------------+------+-----+---------+-------+
| Field               | Type                | Null | Key | Default | Extra |
+---------------------+---------------------+------+-----+---------+-------+
| POOL_ID             | bigint(21) unsigned | NO   |     | 0       |       |
| LRU_POSITION        | bigint(21) unsigned | NO   |     | 0       |       |
| SPACE               | bigint(21) unsigned | NO   |     | 0       |       |
| PAGE_NUMBER         | bigint(21) unsigned | NO   |     | 0       |       |
| PAGE_TYPE           | varchar(64)         | YES  |     | <null>  |       |
| FLUSH_TYPE          | bigint(21) unsigned | NO   |     | 0       |       |
| FIX_COUNT           | bigint(21) unsigned | NO   |     | 0       |       |
| IS_HASHED           | varchar(3)          | YES  |     | <null>  |       |
| NEWEST_MODIFICATION | bigint(21) unsigned | NO   |     | 0       |       |
| OLDEST_MODIFICATION | bigint(21) unsigned | NO   |     | 0       |       |
| ACCESS_TIME         | bigint(21) unsigned | NO   |     | 0       |       |
| TABLE_NAME          | varchar(1024)       | YES  |     | <null>  |       |
| INDEX_NAME          | varchar(1024)       | YES  |     | <null>  |       |
| NUMBER_RECORDS      | bigint(21) unsigned | NO   |     | 0       |       |
| DATA_SIZE           | bigint(21) unsigned | NO   |     | 0       |       |
| COMPRESSED_SIZE     | bigint(21) unsigned | NO   |     | 0       |       |
| COMPRESSED          | varchar(3)          | YES  |     | <null>  |       |
| IO_FIX              | varchar(64)         | YES  |     | <null>  |       |
| IS_OLD              | varchar(3)          | YES  |     | <null>  |       |
| FREE_PAGE_CLOCK     | bigint(21) unsigned | NO   |     | 0       |       |
+---------------------+---------------------+------+-----+---------+-------+
20 rows in set
Time: 0.012s
mysql gcdb@localhost:employees> select table_name, space, page_number, index_name, compressed, compressed_size from information_schema.INNODB_BUFFER_PAGE_LRU where compressed = 'yes' limit
                             -> 1;
+------------+-------+-------------+------------+------------+-----------------+
| table_name | space | page_number | index_name | compressed | compressed_size | --没查到很奇怪待解决。
+------------+-------+-------------+------------+------------+-----------------+
0 rows in set
Time: 0.253s
mysql gcdb@localhost:employees>

mysql gcdb@localhost:employees> create table employee_comps_2 like employees;
Query OK, 0 rows affected
Time: 0.007s
mysql gcdb@localhost:employees> insert into employee_comps_2 select * from employees;
Query OK, 300024 rows affected
Time: 2.463s
mysql gcdb@localhost:employees>  alter table employee_comps_2  row_format=compressed,key_block_size=4;
Query OK, 0 rows affected
Time: 7.972s
mysql gcdb@localhost:employees> select table_id, name, space, row_format, zip_page_size from information_schema.INNODB_SYS_TABLES where name like 'employees%';
+----------+----------------------------+-------+------------+---------------+
| table_id | name                       | space | row_format | zip_page_size为4K |
+----------+----------------------------+-------+------------+---------------+
| 1641     | employees/departments      | 1629  | Dynamic    | 0             |
| 1643     | employees/dept_emp         | 1631  | Dynamic    | 0             |
| 1642     | employees/dept_manager     | 1630  | Dynamic    | 0             |
| 3429     | employees/employee_comps_1 | 3421  | Compressed | 16384         |
| 3433     | employees/employee_comps_2 | 3425  | Compressed | 4096          | --zip_page_size为4K
| 1640     | employees/employees        | 1628  | Dynamic    | 0             |
| 1655     | employees/newsal           | 1642  | Dynamic    | 0             |
| 1645     | employees/salaries         | 1633  | Dynamic    | 0             |
| 1644     | employees/titles           | 1632  | Dynamic    | 0             |
+----------+----------------------------+-------+------------+---------------+
9 rows in set
Time: 0.017s
mysql gcdb@localhost:employees> show create table employee_comps_2;
+------------------+-----------------------------------------------------------------------------+
| Table            | Create Table                                                                |
+------------------+-----------------------------------------------------------------------------+
| employee_comps_2 | CREATE TABLE `employee_comps_2` (                                           |
|                  |   `emp_no` int(11) NOT NULL,                                                |
|                  |   `birth_date` date NOT NULL,                                               |
|                  |   `first_name` varchar(14) NOT NULL,                                        |
|                  |   `last_name` varchar(16) NOT NULL,                                         |
|                  |   `gender` enum('M','F') NOT NULL,                                          |
|                  |   `hire_date` date NOT NULL,                                                |
|                  |   PRIMARY KEY (`emp_no`)                                                    |
|                  | ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4 | --ROW_FORMAT压缩模式
+------------------+-----------------------------------------------------------------------------+
1 row in set
Time: 0.010s
mysql gcdb@localhost:employees>

1.3.2 查看Buffer Pool的状态

  1. 动用命令 show engine innodb status\G

    mysql gcdb@localhost:(none)> show engine innodb status;
    +——–+——+———————————————————————————-+
    | Type | Name | Status |
    +——–+——+———————————————————————————-+
    | InnoDB | | |
    | | | ===================================== |
    | | | 2018-01-05 14:18:27 0x7fa8b4649700 INNODB MONITOR OUTPUT |
    | | | ===================================== |

    —————省略别的输出—————–

    | | | ———————- |
    | | | BUFFER POOL AND MEMORY |
    | | | ———————- |
    | | | Total large memory allocated 10994319360 |
    | | | Dictionary memory allocated 14685357 |
    | | | Buffer pool size 655280 |
    | | | Free buffers 648346 |
    | | | Database pages 6904 |
    | | | Old database pages 2662 |
    | | | Modified db pages 0 |
    | | | Pending reads 0 |
    | | | Pending writes: LRU 0, flush list 0, single page 0 |
    | | | Pages made young 2, not young 0 |
    | | | 0.00 youngs/s, 0.00 non-youngs/s |
    | | | Pages read 6663, created 241, written 988 |
    | | | 0.00 reads/s, 0.00 creates/s, 0.39 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: 6904, unzip_LRU len: 0 |
    | | | I/O sum[0]:cur[0], unzip sum[0]:cur[0] |

    –在出口的结果中得以稳定到BUFFE奥迪Q3 POOL AND MEMOTiguanY ,以下部分显得了缓冲地的意况以及占用内部存款和储蓄器的情况:

    • Total large memory allocated 109943一九三六0
      lnnoDB 所分配的内部存款和储蓄器总量为109943一九三七0 字节。

    • Dictionary memory allocated 14685357
      数据字典内部存款和储蓄器区使用 14685357字节

    • Buffer pool size 16384
      缓冲地中页的数码,那里是 655280 个页,总共占用10G 内部存款和储蓄器( 16K*655280) 。

    • Free buffers 648346
      缓冲地中free 链表中空闲页的数据。

    • Database pages 41
      Buffer Pool中运用了多少页(LRU List)

    • Old database pages 2662
      近年来不被访问的多寡

    • Modified db pages
      赃页的页数

    • Pending reads 0
      正在读取的页的数码,那里为0 。

    • Pending writes: LRU 0, flush list 0, single page 0
      正在进展刷新页的数据,刷新能够分为LRU 、flush list 、single page 三类别型,那在背后的

    • Pages made young 2, not young 0
      0.00 youngs/s, 0.00 non-youngs/s
      — young表示old–>new的状态

    • Pages read 6663, created 241, written 988
      缓冲地中早已读取、创立和刷新页的次数,这里分别为6663 、241 、998 。

    • 0.00 reads/s, 0.00 creates/s, 0.39 writes/s
      过去一段时间内,每秒页的读取、也IJ 建和刷新的次数。注意: SHOW INNODB STATUS 输出
      的是病故一段时间内的结果。

    • Buffer pool hit rate 一千 / 一千缓冲地的命中率,那是监控极端关怀的叁特品质目的。命中率越高,数据库的性质越好,那里为1 00% ,表示缓冲地能够缓存全部InnoDB 存款和储蓄引擎表。

    —————省略别的输出—————–

    | | | —————————- |
    | | | END OF INNODB MONITOR OUTPUT |
    | | | ============================ |
    | | | |
    +——–+——+———————————————————————————-+
    (END)

  2. 选择元数据表 information_schema.INNODB_BUFFER_POOL_STATS

    mysql gcdb@localhost:(none)> select * from information_schema.INNODB_BUFFER_POOL_STATS\G
    POOL_ID | 0
    POOL_SIZE | 81910
    FREE_BUFFERS | 80996
    DATABASE_PAGES | 910
    OLD_DATABASE_PAGES | 339
    MODIFIED_DATABASE_PAGES | 0
    PENDING_DECOMPRESS | 0
    PENDING_READS | 0
    PENDING_FLUSH_LRU | 0
    PENDING_FLUSH_LIST | 0
    PAGES_MADE_YOUNG | 0
    PAGES_NOT_MADE_YOUNG | 0
    PAGES_MADE_YOUNG_RATE | 0.0
    PAGES_MADE_NOT_YOUNG_RATE | 0.0
    NUMBER_PAGES_READ | 865
    NUMBER_PAGES_CREATED | 45
    NUMBER_PAGES_WRITTEN | 104
    PAGES_READ_RATE | 0.0
    PAGES_CREATE_RATE | 0.0
    PAGES_WRITTEN_RATE | 0.0
    NUMBER_PAGES_GET | 69856
    HIT_RATE | 0
    YOUNG_MAKE_PER_THOUSAND_GETS | 0
    NOT_YOUNG_MAKE_PER_THOUSAND_GETS | 0
    NUMBER_PAGES_READ_AHEAD | 768
    NUMBER_READ_AHEAD_EVICTED | 0
    READ_AHEAD_RATE | 0.0
    READ_AHEAD_EVICTED_RATE | 0.0
    LRU_IO_TOTAL | 0
    LRU_IO_CURRENT | 0
    UNCOMPRESS_TOTAL | 0
    UNCOMPRESS_CURRENT | 0
    POOL_ID | 1
    POOL_SIZE | 81910
    FREE_BUFFERS | 80843
    DATABASE_PAGES | 1063
    OLD_DATABASE_PAGES | 400
    MODIFIED_DATABASE_PAGES | 0
    PENDING_DECOMPRESS | 0
    PENDING_READS | 0

    — —————省略别的输出—————–

    mysql gcdb@localhost:(none)> select * from information_schema.INNODB_BUFFER_PAGE_LRU limit 1\G
    POOL_ID | 0
    LRU_POSITION | 0
    SPACE | 0 — space id 表空间号
    PAGE_NUMBER | 7 — 对应的页号
    PAGE_TYPE | SYSTEM
    FLUSH_TYPE | 1
    FIX_COUNT | 0
    IS_HASHED | NO
    NEWEST_MODIFICATION | 32993864040 — 该页近来一遍(最新)被修改的LSN值
    OLDEST_MODIFICATION | 0 — 该页在Buffer Pool中率先次被改动的LSN值,FLushList是基于该值实行排序的

                                        -- 该值越小,表示该页应该最先被刷新
    

    ACCESS_TIME | 2688054668
    TABLE_NAME |
    INDEX_NAME |
    NUMBER_RECORDS | 0
    DATA_SIZE | 0
    COMPRESSED_SIZE | 0
    COMPRESSED | NO
    IO_FIX | IO_NONE
    IS_OLD | YES
    FREE_PAGE_CLOCK | 0
    1 row in set
    Time: 0.143s
    mysql gcdb@localhost:(none)>

    —————–省略其余输出—————–

1.4. Buffer Pool 的预热

1.3.3 Buffer Pool 在线调整

  • 从 MySQL 5.7 先河,能够在线修改 innodb_buffer_pool_size

    mysql gcdb@localhost:(none)> show variables like “%innodb_buffer_pool_size%”;
    +————————-+————-+
    | Variable_name | Value |
    +————————-+————-+
    | innodb_buffer_pool_size | 10737418240 | — innodb_buffer_pool_size为10G
    +————————-+————-+
    1 row in set
    Time: 0.012s
    mysql gcdb@localhost:(none)> set global innodb_buffer_pool_size=1610241024*1024; –调整为innodb_buffer_pool_size为16G
    Query OK, 0 rows affected
    Time: 0.008s
    mysql gcdb@localhost:(none)> show variables like “%innodb_buffer_pool_size%”;
    +————————-+————-+
    | Variable_name | Value |
    +————————-+————-+
    | innodb_buffer_pool_size | 17179869184 | –调整为innodb_buffer_pool_size为16G
    +————————-+————-+
    1 row in set
    提姆e: 0.012s
    mysql gcdb@localhost:(none)> show engine innodb status;
    +——–+——+———————————————————————————-+
    | Type | Name | Status |
    +——–+——+———————————————————————————-+
    | InnoDB | | |
    | | | ===================================== |
    | | | 2018-01-05 15:23:16 0x7fa8b4649700 INNODB MONITOLAND OUTPUT |
    | | | ===================================== |
    — —————省略别的输出—————–
    | | | ———————- |
    | | | BUFFE昂Cora POOL AND MEMO汉兰达Y |
    | | | ———————- |
    | | | Total large memory allocated 17590910976 | –lnnoDB 所分配的内存总量为17590910976字节
    | | | Dictionary memory allocated 14685357 |
    | | | Buffer pool size 1048496 |
    | | | Free buffers 1041477 |
    | | | Database pages 7019 |
    | | | Old database pages 2662 |
    | | | Modified db pages 0 |
    | | | Pending reads 0 |
    | | | Pending writes: LRU 0, flush list 0, single page 0 |
    | | | Pages made young 2, not young 0 |
    | | | 0.00 youngs/s, 0.00 non-youngs/s |
    | | | Pages read 6663, created 356, written 1158 |
    | | | 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: 7019, unzip_LRU len: 0 |
    | | | I/O sum[0]:cur[0], unzip sum[0]:cur[0] |
    | | | ———————- |
    | | | INDIVIDUAL BUFFE奥迪Q5 POOL INFO |
    | | | ———————- |
    | | | —BUFFE君越 POOL 0 |
    | | | Buffer pool size 131062 | –BUFFE劲客 POOL 0 原先分配 8一九零八页变为131062
    | | | Free buffers 130152 |
    | | | Database pages 910 |
    | | | Old database pages 339 |
    | | | Modified db pages 0 |
    | | | 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 865, created 45, written 104 |
    | | | 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: 910, unzip_LRU len: 0 |
    | | | I/O sum[0]:cur[0], unzip sum[0]:cur[0] |
    | | | —BUFFER POOL 1 |
    | | | Buffer pool size 131062 |
    | | | Free buffers 129999 |
    | | | Database pages 1063 |
    | | | Old database pages 400 |
    | | | Modified db pages 0 |
    | | | Pending reads 0 |

mysql gcdb@localhost:(none)> set global innodb_buffer_pool_size=8*1024*1024*1024;   -- 缩小,没修改的页被丢弃,修改的需要刷回磁盘
Query OK, 0 rows affected
Time: 0.001s

mysql gcdb@localhost:(none)> show variables like "%innodb_buffer_pool_size%";
+-------------------------+------------+
| Variable_name           | Value      |
+-------------------------+------------+
| innodb_buffer_pool_size | 8589934592 |
+-------------------------+------------+
1 row in set
Time: 0.012s
mysql gcdb@localhost:(none)>
  • MySQL 5.7事先的本子,修改innodb_buffer_pool_size,需要重启

3.2.1 日志(redo)中的LSN:

  • 一经当前的LSN为C ,此时对某个页做修改,则会发生M
    个字节的日志(须要写入M个字节的日记),那此时的LSN 则为C+M
    。依次类推,LSN是1个单调递增的值(字节数)。
  • 日记中的LSN代表了日志一共写入了多少个字节。

5.2. Insert/Change Buffer举例

CREATE TABLE t (
a INT AUTO_INCREMENT, -- a 列是自增的
b VARCHAR(30),   -- b 列是varchar
PRIMARY KEY(a)   -- a 是主键
key(b)           -- b 是二级索引(如果是name之类的,可以看成是非唯一的)
);
  • 对于主键a列),每一回插入都要立刻插入对应的聚集索引页中(在内部存储器中就直接插入,不在内部存款和储蓄器就先读取到内部存储器)

  • 对于二级索引secondary index)(b列

  1. 没有 Insert/Change Buffer时,每趟插入一条记下,就要读取一次页(读取内部存储器,或许从磁盘读到内存),然后将记录插入到页中;

  2. 有 Insert/Change Buffer时,当插入一条记下时,先判断记录对应要插入的二级索引(secondary
    index)页是否Buffer Pool中:

    • 如果该二级索引(secondary
      index)页已经在Buffer Pool中,则直接插入
    • 反之,先将其Cache起来,放到Insert/Change Buffer中,等到该二级索引(secondary
      index)页被读到时,将Insert/Change Buffer中该页对应的记录合并Merge)进去,从而收缩I/O操作;

Insert/Change Buffer不畏用来提升二级索引插入的性能使用空间换时间,批量插入的方式(二级索引可以不急着插入,只要主键已经插入了即可)

5.5.Change Buffer

  • MySQL 5.5以后,改名为Change Buffer,表示不仅仅适用于insert。`

      1. Insert
      1. Delete-Marking(标记删除)
      1. Purge(清除)
      1. innodb_change_buffering = all
      • all
      • none (禁用)
      • inserts
      • deletes
      • changes =(insert & delete-marking)
      • purge

七. Flush Neighbor Page (FNP)

  • 刷新脏页所在区extent)的所有脏页,合并IO,随机转顺序的优化;
    • 写入的数据太多
    • 如果业务确实是频繁更新,那刷新也会很频繁
  • 对守旧机械磁盘有含义;
    • innodb_flush_neighbors={0|1|2} >=MySQL 5.6
    • 0:表示关闭该功能
    • 1:表示刷新一个区内的脏页
    • 2:表示刷新几个 连续 的脏页
  • SSD提出关闭次成效;

    mysql gcdb@localhost:employees> show variables like “%flush_neigh%”;
    +————————+——-+
    | Variable_name | Value |
    +————————+——-+
    | innodb_flush_neighbors | 1 | — 非SSD提议选取2
    +————————+——-+
    1 row in set
    Time: 0.012s
    mysql gcdb@localhost:employees>

1.3.4.2 innodb_old_blocks_time

mysql gcdb@localhost:(none)> show variables like"%innodb_old_blocks_time%"
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| innodb_old_blocks_time | 1000  |   --设置为1s
+------------------------+-------+
1 row in set
Time: 0.012s
mysql gcdb@localhost:(none)>
  1. 当该页被第一回 读取时,将该页放在mid point
    地点,然则随后随便你读多少次 ,我在那 innodb_old_blocks_time
    的时日内都不管(都作为只读取了叁回),等那么些时刻过去了(时间到),倘使该页还是被读取了,作者才把那么些页放到
    new page 的首部。

  2. 通常 select * 扫描操作不会胜出1秒,三个页十分的快就被扫完了。

5.3. Insert/Change Buffer 性能

ACCESS 2

  • 1.左图使开启了Insert/Change Buffer,而右图未打开;
  • 2.一开头都相比高是因为还不曾全量的进行刷磁盘(脏页全部在Buffer Pool中,还没满)`
  • 如开始介绍时所说,当Insert Buffer进行合并的时候,性能进行下降
  • 3.开启Insert/Change Buffer后,insert的常量值在5K左右;
  • 4.SSD地方下也建议拉开;

4.1 Double Write介绍

  • Double
    Write的目标是为了保障数据写入的可信性,避免partial write的情况

    • partial write(*部分写*)
      • 16K的页只写入了4K6K8K12K的气象(此时是不完全、不彻底的页);
      • 不可以通过redo log开始展览回复;
      • redo还原的前提是该必须是完整、干净的;
  • Double Write全局的;

  • 共享表空间存在3个段对象 double write,然后那几个段由2个区(1M)组成
  • 2M一定大小(both file and memory);
  • 页在刷新时,首先顺序的写入到double write
  • 下一场再刷回磁盘(ibd

ACCESS 3

有点类似 RAID-1 的机制,总有一份数据是天经地义的
粗略说来,便是在脏页刷新到磁盘前,先要有个地方记录这个脏页的副本

1.将脏页copyDouble Write Buffer对象中,默认2M大小;

2.将Double Write Buffer中的对象先写入到共享表空间(ibdata1)中的Double Write

  • 2M巡回覆盖
  • 顺序写入(一次IO)

3.再根据(spacepage\_no)写入到原来的ibd文件中;

4.假使是在写到ibdata1中的Double Write时,发生宕机;此刻原本的ibd file 仍然是完整、干净的,下次起步后是足以用redo文件进行复原的。

5.假设是写到ibd文件时,发生了宕机;此刻在原本的ibdata1中存在副本,能够直接覆盖到ibd文件(对应的页)中去,然后再拓展redo展开还原

redo物理逻辑的,物理意味着记录的日志针对的是页(page)的修改,逻辑代表记录日志的情节是逻辑的。

mysql gcdb@localhost:employees>  show variables like "%doublewrite%";
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| innodb_doublewrite | ON    |
+--------------------+-------+
1 row in set
Time: 0.012s
mysql gcdb@localhost:employees> 

3.1. CheckPoint的作用

  • 缩短数据库的死灰复燃时间
  • 缓冲池不够用时,将脏页刷新到磁盘
  • 重做日志不可用时, 刷新脏页
  • 数据页首先被读入缓冲池中,当数码页中的某几条记下被更新也许插入新的笔录时,全数的操作都是在Buffer
    Pool 先成功的;
  1. Buffer
    Pool中的某些页和磁盘中的某些页在(Space, Page_Number)上是一模一样的,然而其内容可能是不同的(Buffer
    Pool中的被更新过了),形成了脏页;
  2. 要定期将缓冲池中的脏页刷回磁盘(Flush),达到最终一致,即通过CheckPoint机制来刷脏页;

ACCESS 4

一. 缓冲池(Buffer Pool)

五. Insert/Change Buffer

5.1. Insert/Change Buffer介绍

  • MySQL5.5本子在此之前叫做insert buffer,现在号称change buffer
  • 提高辅助索引的插入性能
  • 非唯一的二级索引(non-unique secondary index
  • Insert/Change Buffer是一个持久化的对象(在ibdata1中,同时也会写redo日志)`
  • Insert/Change Buffer页是一棵B+树,每一次最缓存2K的记录`
  • 开启后有30%的性子提高(暗中同意开启)`
  • MySQL5.5本子此前,最大能够安装为Buffer Pool1/2,未来最大不得不设置为`
    1/4
  • Insert Buffer进行合并的时候,`特性下落

    mysql gcdb@localhost:employees> show variables like “%change_buffer%”;
    +——————————-+——-+
    | Variable_name | Value |
    +——————————-+——-+
    | innodb_change_buffer_max_size | 25 |
    | innodb_change_buffering | all |
    +——————————-+——-+
    2 rows in set
    Time: 0.012s

六. Adaptive Hash Index(自适应Hash索引)

  • 搜索的时间复杂度
    • B+O(T),只可以定位到该记录所在的页;
    • 哈希表 O(1),能够直接固定到记录;
  • 能够协调判断是或不是是活跃的页,假若是活跃的页,能够自行做Hash,管理员无需人工干预;
  • MySQL5.6版本后,官方不推荐应用自适应Hash索引- CPU使用率变高`,不过品质没有晋级;
  • MySQL5.7中加进了innodb_adaptive_hash_index_parts,扩大分片,以压缩竞争;
  • 只对等值的操作有意义

2.2 压缩页在内部存款和储蓄器中的存放

  • 压缩页存在于 unzip_LRU 中

    mysql gcdb@localhost:employees> show engine innodb status;
    +——–+——+———————————————————————————-+
    | Type | Name | Status |
    +——–+——+———————————————————————————-+
    | InnoDB | | |
    | | | ===================================== |
    | | | 2018-01-08 10:51:59 0x7fa8c2470700 INNODB MONITO福睿斯 OUTPUT |
    | | | ===================================== |
    | | | Per second averages calculated from the last 19 seconds |
    ————-省略其余输出————-
    | | | ———————- |
    | | | BUFFE索罗德 POOL AND MEMO福特ExplorerY |
    | | | ———————- |
    | | | Total large memory allocated 8795455488 |
    | | | Dictionary memory allocated 14672808 |
    | | | Buffer pool size 524224 |
    | | | Free buffers 501290 |
    | | | Database pages 一九六零6 |
    | | | Old database pages 7063 |
    | | | Modified db pages 0 |
    | | | Pending reads 0 |
    | | | Pending writes: LRU 0, flush list 0, single page 0 |
    | | | Pages made young 2, not young 0 |
    | | | 0.00 youngs/s, 0.00 non-youngs/s |
    | | | Pages read 7594, created 1一九八三, written 17956 |
    | | | 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: 一九五七6, unzip_LRU len: 8946 | — 压缩页LRU的尺寸在buffer pool中的长度是8946
    | | | I/O sum[0]:cur[0], unzip sum[0]:cur[0] |
    | | | ———————- |
    | | | INDIVIDUAL BUFFER POOL INFO |
    | | | ———————- |
    | | | —BUFFER POOL 0 |
    | | | Buffer pool size 65528 |
    | | | Free buffers 62599 |
    | | | Database pages 2480 |
    | | | Old database pages 895 |
    | | | Modified db pages 0 |
    | | | 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 993, created 1487, written 1621 |
    | | | 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: 2480, unzip_LRU len: 1150 |
    | | | I/O sum[0]:cur[0], unzip sum[0]:cur[0] |
    | | | —BUFFEGL450 POOL 1 |
    ————-省略别的输出————-
    1 row in set
    Time: 0.017s
    mysql gcdb@localhost:employees>

1.3.4 LRU List 的管理

1.4.1 Buffer Pool预热

  • 在MySQL运转后(MySQL5.6事先),Buffer
    Pool中页的数码是空的,必要多量的时刻才能把磁盘中的页读入到内存中,导致启动后的一段时间性能很差

  • 行使该办法预热,强制扫描,将数据刷入buffer pool,不过不可能确实将走俏数据放入buffer pool
    ;

  • select count(1) from table force index(PRIMARY) ;

  • select count(1) from table FORCE index(index name);

  • 在 MySQL 5.6 以后,可以在 停机 的时候 dumpbuffer pool
    的数据(space,page number),然后在 启动 的时候 Load
    buffer pool,该意义能够让MySQL运营时 自动预热 ,无需人工干预。

    mysql gcdb@localhost:(none)> show variables like “%innodb_buffer_pool%”;
    +————————————-+—————-+
    | Variable_name | Value |
    +————————————-+—————-+
    | innodb_buffer_pool_chunk_size | 134217728 |
    | innodb_buffer_pool_dump_at_shutdown | ON | — 在停机时dump出buffer pool中的(space,page)
    | innodb_buffer_pool_dump_now | OFF | — set 一下,表示以往就从buffer pool中dump
    | innodb_buffer_pool_dump_pct | 25 | — dump的百分比,是种种buffer pool文件,而不是总体
    | innodb_buffer_pool_filename | ib_buffer_pool | — dump出的公文的名字
    | innodb_buffer_pool_instances | 8 |
    | innodb_buffer_pool_load_abort | OFF |
    | innodb_buffer_pool_load_at_startup | ON | — 运营时加载dump的文书,恢复生机到buffer pool中
    | innodb_buffer_pool_load_now | OFF | — set一晃,表示未来加载 dump的公文
    | innodb_buffer_pool_size | 8589934592 |
    +————————————-+—————-+
    10 rows in set
    Time: 0.013s
    mysql gcdb@localhost:(none)>

    [root@localhost-m(252) /r2/mysqldata]# head ib_buffer_pool –dump出来的文书
    2,7560
    2,7557
    2,7552
    2,7100
    2,7096
    2,7092
    2,7090
    2,7084
    2,7082
    2,7077
    [root@localhost-m(252) /r2/mysqldata]#

  • 1.ib_buffer_pool dump的越多,启动的越慢

  • 2.频繁的手工dump( set innodb_buffer_pool_dump_now = 1 ),会导致Buffer Pool中的数据越来越少,是因为安装了
    innodb_buffer_pool_dump_pct

  • 3.若是做了高可用,能够定期dump,然后将该dump的公文字传递送到slave上,然后直接load( set innodb_buffer_pool_load_now = 1 )``(slave上的(Space,Page)和Master上的 大致相同 )

  • 4.load now 和 dumpnow都是 异步在后台加载的,重回的进度迅猛

    –mysql 启动

    sehll> cat error.log
    ## —————省略别的输出—————–
    2017-11-24T10:45:22.008199+08:00 0 [Note] InnoDB: Loading buffer pool(s) from /r2/mysqldata/ib_buffer_pool
    ## —————省略别的输出—————–

    2017-11-24T10:45:25.716362+08:00 0 [Note] InnoDB: Buffer pool(s) load completed at 171124 10:45:25 –速度依旧全速的

    –mysql 停机

    shell> cat error.log
    —————省略其余输出—————–
    2017-12-29T10:31:47.844235+08:00 0 [Note] InnoDB: Dumping buffer pool(s) to /r2/mysqldata/ib_buffer_pool –dump buffer
    2017-12-29T10:31:47.844597+08:00 0 [Note] InnoDB: Buffer pool(s) dump completed at 171229 10:31:47
    —————省略其余输出—————–

  • 查看当前buffer pool中的数据的条数

    [root@localhost-m(252) /r2/mysqldata]# wc -l ib_buffer_pool
    129 ib_buffer_pool

    mysql gcdb@localhost:(none)> set global innodb_buffer_pool_dump_now=1;
    Query OK, 0 rows affected
    Time: 0.001s
    mysql gcdb@localhost:(none)> show status like ‘Innodb_buffer_pool_dump_status’;
    +——————————–+————————————————–+
    | Variable_name | Value |
    +——————————–+————————————————–+
    | Innodb_buffer_pool_dump_status | Buffer pool(s) dump completed at 180105 17:49:54 |
    +——————————–+————————————————–+
    1 row in set
    Time: 0.011s
    mysql gcdb@localhost:(none)>
    — 已经成功
    [root@localhost-m(252) /r2/mysqldata]# wc -l ib_buffer_pool
    1751 ib_buffer_pool –变为1751条

  • innodb_buffer_pool_dump_pct,该百分比(N<100)不是您近年来buffer pool总的数据(总页数)N%,而是你每种buffer
    pool实例中最近使用的页N%

if there are 4 buffer pools with 100 pages each, and
innodb_buffer_pool_dump_pct is set to 25, the 25 most recently
used pages from each buffer pool are dumped

  • 查看Buffer Pool中的Flush List

    SELECT
    pool_id,
    lru_position,
    space,
    page_number,
    table_name,
    oldest_modification,
    newest_modification
    FROM
    information_schema.INNODB_BUFFER_PAGE_LRU
    WHERE
    oldest_modification <> 0
    AND oldest_modification <> newest_modification;
    -- 如果没有脏页,结果集为空
    -- 不要在线上操作该SQL语句,开销较大
    

三 CheckPoint

1.1 缓冲池介绍

  • 每次读写数据都以通过 Buffer Pool
  • Buffer Pool 中绝非用户所急需的数量时,才去硬盘中获取;
  • 通过 innodb_buffer_pool_size进展设置总容积,该值设置的越大越好;
  • innodb_buffer_pool_instances 设置为多个缓冲池;

  • 总容积照旧innodb_buffer_pool_size

  • 设置两个instance 可将走俏打散,升高并发质量(提出设置成CPU个数值)

  • Buffer Pool也是以
    页(page)为单位的,且大小和innodb_page_size一致;

二. Buffer Pool与压缩页

3.4. 刷新

  • Master Thread Checkpoint

  • FLUSH_LIST 中刷新

  • FLUSH_LRU_LIST Checkpoint

  • LRU_LIST中刷新(尽管不在脏页链表中)

    • 5.5原先必要确认保障在
      LRU_LIST 尾部要有100个空闲页(可替换的页),即
      刷新一部分数据 ,保证有玖十几个空闲页
  • innodb_lru_scan_depth – 每一趟进行 LRU_LIST 刷新的脏页的数量
    • 使用到每一个Buffer Pool实例,总数即为该值乘以Buffer Pool的实例个数,要是当先
      innodb_io_capacity 是不客观的
    • 建议该值不能够当先 innodb_io_capacity /
      innodb_buffer_pool_instances
  • Async/Sync Flush Checkpoint

  • 重做日志重用

  • Dirty Page too much Checkpoint

  • innodb_max_dirty_pages_pct 参数控制

1.3 Buffer Pool的管理

4.3.1.支持原子写的装备

  • 磁盘
    • Funsion-IO
    • 宝存
  • 文件系统
    • ZFS (Linux上不推荐使用)
    • btrfs(Linux上不引进应用)
      • 使用copy on wirte机制,不进行原地更新,而是开辟新的职责,写成功后,将本来的页释放
      • 本质上的思绪还是保留一个副本

四. Double Write

3.2. LSN (Log Sequence Number)

mysql gcdb@localhost:employees> show engine innodb status;
+--------+------+----------------------------------------------------------------------------------+
| Type   | Name | Status                                                                           |
+--------+------+----------------------------------------------------------------------------------+
| InnoDB |      |                                                                                  |
|        |      | =====================================                                            |
|        |      | 2018-01-08 11:50:58 0x7fa8c2470700 INNODB MONITOR OUTPUT                         |
|        |      | =====================================                                            |
|        |      | Per second averages calculated from the last 1 seconds                           |
-- ----------省略其他输出-------------
|        |      | ---                                                                              |
|        |      | LOG                                                                              |
|        |      | ---                                                                              |
|        |      | Log sequence number 33112246157                                                  | -- 当前内存中最新的LSN
|        |      | Log flushed up to   33112246157                                                  | -- redo刷到磁盘的LSN(不是在内存中的)
|        |      | Pages flushed up to 33112246157                                                  | -- 最后一个刷到磁盘上的页的最新的LSN(NEWEST_MODIFICATION)
|        |      | Last checkpoint at  33112246148                                                  | -- 最后一个刷到磁盘上的页的第一次被修改时的LSN(OLDEST_MODIFICATION)
|        |      | 0 pending log flushes, 0 pending chkp writes                                     |
|        |      | 439 log i/o's done, 0.00 log i/o's/second                                        |
|        |      | ----------------------                                                           |
-- LSN(Log Sequence Number) 是一个字节数

注意:

  1. Log sequence numberLog flushed up
    那八个LSN大概会分化,运营进程中后者只怕会低于前者,因为redo日志也是先在内部存储器中更新,再刷到磁盘的。
  2. Pages flushed upLast checkpoint
    其实都指向了最后四个刷新到磁盘的页,只是Pages flushed up
    代表了页中的NEWEST_MODIFICATION ,而Last checkpoint
    代表了页中的OLDEST_MODIFICATION
  • FLUSH LIST使用OLDEST_MODIFICATION
    实行记录并排序,那在刷新脏页时,CheckPointLSN 值就相应的是当下刷新到有个别页的OLDEST_MODIFICATION

  • 当有些页只被涂改过二回,则Pages flushed up
    Last checkpoint相等,反之数十次修改,则Pages flushed up大于Last checkpoint

  • 在还原时,从CheckPoint
    发轫上升,假设当前页的LSN大于CheckPoint的LSN
    ,则表示不需要恢复

1.3.1 Buffer Pool 的组成

ACCESS 5

1.Free List

  • Buffer Pool
    刚运行时,有2个个16K的空白的页,那一个页就存放(链表串联)在
    Free List

2.LRU List

  • 当读取三个数据页的时候,就从 Free List
    中取出二个页,存入数据,并将该页放入到 LRU List

3.Flush List

  • LRU List 中的页 第一次 被涂改了,就将该页的 指针(page
    number) 放入了 Flush List (只要修改过,就放入,不管修改三回)
  • Flush List 中包含脏页(数据经过修改,但是未刷入磁盘的页)
  • Flush list 中存放的不是1个页,而是页的指针(page number)

1.2 Buffer Pool 质量测试

ACCESS 6

  • 18G的测试数据,80M条记录;


  • Buffer Pool选拔的内部存款和储蓄器超过数据库的大小时,比如20G(库中全体数据都在内部存款和储蓄器中),此时的属性有了相当大的晋升;

  • 该图测试的是TPS (每秒事物数),sysbench中1个东西由18条SQL语句组成,即那里的QPS为4.5W

  • 内部存储器减弱 10% ,性能下落 60%

4.3.2. innodb_doublewrite参数

  • innodb_doublewrite=0 可以关闭double write功能

4.3.Double Write方可关闭的前提

2.3 伙伴算法

磁盘中存放压缩页(row_format=compressed),假设
key_block_size=8KBuffer Pool 的页大小是 16K,向 Free List
申请空闲的页,要是没有空闲页,则向 LRU List
申请页,如果LRU满了,则找LRU中最后的三个页,要是最终的页是脏页
,则做 flush
操作,最终收获一个空白的页(16K),该16K的空白页,就给8K的压缩页使用,这样就多出一个8K的空间
,该空间会移到8K的Free List中去,假设有贰个4K的压缩页,就把8K的Free list中的空白页给它用,然后多余的4K的上空移到4K的Free List
中去

  • 经过上述办法,不同大小的页可以在`同一个Buffer Pool中应用
    (能够简单的觉得Free List是 依照页大小 来进展 划分 的)。
  • 不能据说页大小来划分缓冲池,缓冲池中页的尺寸就是固定的大小
    等于innodb_page_size )
  • LRU ListFlush List 不须求根据页大小划分,都以统一的
    innodb_page_size大小

4.2 Double Write的开销

  • Double Write2M数据是顺序刷入磁盘的,是一次IO`,该次IO的大小为2M`。
  • 开启Double Write的属性降低5% \~ 25%IO Bound场馆降低的最厉害)
  • slave服务器同样需要开启

3.2.2 页中的LSN:

  • 中也设有LSN,表示该页被涂改的时候,对应的日志的LSN是稍稍;
  • Page中的LSN主要用在恢复的时候,Page中的LSN放在页头

    mysql gcdb@localhost:employees> desc information_schema.INNODB_BUFFER_PAGE_LRU;
    +———————+———————+——+—–+———+——-+
    | Field | Type | Null | Key | Default | Extra |
    +———————+———————+——+—–+———+——-+
    | POOL_ID | bigint(21) unsigned | NO | | 0 | |
    | LRU_POSITION | bigint(21) unsigned | NO | | 0 | |
    | SPACE | bigint(21) unsigned | NO | | 0 | |
    | PAGE_NUMBER | bigint(21) unsigned | NO | | 0 | |
    | PAGE_TYPE | varchar(64) | YES | | | |
    | FLUSH_TYPE | bigint(21) unsigned | NO | | 0 | |
    | FIX_COUNT | bigint(21) unsigned | NO | | 0 | |
    | IS_HASHED | varchar(3) | YES | | | |
    | NEWEST_MODIFICATION | bigint(21) unsigned | NO | | 0 | | — 该页近年来二回(最新)被涂改的LSN值
    | OLDEST_MODIFICATION | bigint(21) unsigned | NO | | 0 | | — 该页在Buffer Pool中率先次被改动的LSN值
    | ACCESS_TIME | bigint(21) unsigned | NO | | 0 | |
    | TABLE_NAME | varchar(1024) | YES | | | |
    | INDEX_NAME | varchar(1024) | YES | | | |
    | NUMBER_RECORDS | bigint(21) unsigned | NO | | 0 | |
    | DATA_SIZE | bigint(21) unsigned | NO | | 0 | |
    | COMPRESSED_SIZE | bigint(21) unsigned | NO | | 0 | |
    | COMPRESSED | varchar(3) | YES | | | |
    | IO_FIX | varchar(64) | YES | | | |
    | IS_OLD | varchar(3) | YES | | | |
    | FREE_PAGE_CLOCK | bigint(21) unsigned | NO | | 0 | |
    +———————+———————+——+—–+———+——-+
    20 rows in set
    Time: 0.014s
    mysql gcdb@localhost:employees>

2.4. 压缩页在内部存储器中保留

  • 被核减的页须要在Buffer Pool中解压
  • 原来的压缩页保留 在Buffer Pool中。
  • 症结是压缩页占用了Buffer
    Pool的半空中,对于热点数据来说,也就是内部存款和储蓄器小了,或然造成质量下落(热点空间变小)。

    • 所以在开启压缩后,Buffer Pool的空间要相应增大
    • 假设启用压缩后节省的磁盘IO可见平衡掉Buffer
      Pool空间变小所带来的特性下落,那完全质量还是会上升;
    • 启用压缩的前提是,内存尽可能的大
  • 压缩页保留的来头:
  • 是为着在更新数据的时候,将 redo
    添加到压缩页的空闲部分,如若要刷回磁盘,能够一向
    将该压缩页刷回去。假设该页被写满,则做一回
    reorganize操作(在此以前也要做解压),真的写满了才做分裂
  • 封存压缩页是为着更快的刷回磁盘
  • 解压的页是为了更快的查询

透明压缩则从未上述压缩页的难点,因为压缩是文件系统层的,对MySQL是透明的

3.2.3 CheckPoint LSN

种种数据库中也有三个LSN,表示最后一个刷新到磁盘的页的LSN
,评释了该LSN之前的数据都刷回到磁盘了,且假如要做恢复操作,也只要从当前这个CheckPoint LSN
发轫重操旧业。

CheckPoint LSN 写在redo log 的前2K 空间中:

  1. 日志中的LSN = CheckPoint的LSN ,则代表拥有页都已经刷回磁盘
  2. 日记中的LSN > CheckPoint的LSN
    ,则意味还有页没刷到磁盘;借使是宕机,则必要用日志恢复生机。
  3. 日志中的LSN < CheckPoint的LSN ,则报错

3.3 CheckPoint的分类

  • Sharp CheckPoint

    • 所有的脏页刷新回磁盘
    • 通常在数据库关闭的时候
    • 刷新时系统hang住
    • innodb_fast_shutdown={1|0}
  • Fuzzy CheckPoint

    • 部分脏页刷新回磁盘
    • 对系统影响较小
  • innodb_io_capacity

    • 细微限制为100
    • 一回最多刷新脏页的力量,与IOPS相关
    • SSD能够设置在4000-8000
    • SAS 最多安装在`800多“(IOPS在1000左右)

相关文章