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

一. 缓冲池(Buffer Pool)

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一致;

1.2 Buffer Pool 性能测试

ACCESS 1

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


  • Buffer Pool动的内存超过数据库的大小经常,比如20G(库中有数据还当外存中),此时底性有了深怪之升官;

  • 拖欠图测试的是TPS (每秒事物数),sysbench中一个东西由18长达SQL语句组成,即这里的QPS为4.5W

  • 内存减少 10% ,性能降低 60%

1.3 Buffer Pool的管理

1.3.1 Buffer Pool 的组成

ACCESS 2

1.Free List

  • Buffer Pool
    刚起步时,有一个个16K之空白的页,这些页就存(链表串联)在
    Free List

2.LRU List

  • 当读取一个数据页的时候,就由 Free List
    中取出一个页,存入数据,并拿该页放入到 LRU List

3.Flush List

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

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] |

    –于输出的结果遭遇得一定及BUFFER POOL AND MEMORY ,以下一些显得了缓冲地的状态和占内存的景象:

    • Total large memory allocated 10994319360
      lnnoDB 所分配的内存总量为10994319360 字节。

    • 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 1000 / 1000
      缓冲地的命中率,这是监督极端关心之一个性能指标。命中率越强,数据库的性进一步好,这里吧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.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
    Time: 0.012s
    mysql gcdb@localhost:(none)> show engine innodb status;
    +——–+——+———————————————————————————-+
    | Type | Name | Status |
    +——–+——+———————————————————————————-+
    | InnoDB | | |
    | | | ===================================== |
    | | | 2018-01-05 15:23:16 0x7fa8b4649700 INNODB MONITOR OUTPUT |
    | | | ===================================== |
    — —————省多少其他输出—————–
    | | | ———————- |
    | | | BUFFER POOL AND MEMORY |
    | | | ———————- |
    | | | 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 BUFFER POOL INFO |
    | | | ———————- |
    | | | —BUFFER POOL 0 |
    | | | Buffer pool size 131062 | –BUFFER POOL 0 原先分配 81910页变为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,需要重启

1.3.4 LRU List 的管理

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 3

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;
    时,该语句会读取某个页很频繁(即该页可能被读取了一定量次等以上,读取一长达记下,就待读一不成页

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秒,一个页很快便为扫了了。

1.4. Buffer Pool 的预热

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语句,开销较大
    

二. Buffer Pool与压缩页

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>

2.2 压缩页在内存中之存

  • 压缩页存在于 unzip_LRU 中

    mysql gcdb@localhost:employees> show engine innodb status;
    +——–+——+———————————————————————————-+
    | Type | Name | Status |
    +——–+——+———————————————————————————-+
    | InnoDB | | |
    | | | ===================================== |
    | | | 2018-01-08 10:51:59 0x7fa8c2470700 INNODB MONITOR OUTPUT |
    | | | ===================================== |
    | | | Per second averages calculated from the last 19 seconds |
    ————-省稍其他输出————-
    | | | ———————- |
    | | | BUFFER POOL AND MEMORY |
    | | | ———————- |
    | | | Total large memory allocated 8795455488 |
    | | | Dictionary memory allocated 14672808 |
    | | | Buffer pool size 524224 |
    | | | Free buffers 501290 |
    | | | Database pages 19576 |
    | | | 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 11982, 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: 19576, 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] |
    | | | —BUFFER POOL 1 |
    ————-省不怎么其他输出————-
    1 row in set
    Time: 0.017s
    mysql gcdb@localhost:employees>

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大小

2.4. 压缩页在内存中保留

  • 为核减的页需要在Buffer Pool中解压
  • 原来的压缩页保留 在Buffer Pool中。
  • 缺点是压缩页占用了Buffer
    Pool的上空,对于热点数据以来,相当给外存小了,可能导致性能降低(热点空间更换多少)。

    • 所以在开启压缩后,Buffer Pool的空间要相应增大
    • 万一启用压缩后节省的磁盘IO能平衡掉Buffer
      Pool空间变小所带来的性降低,那完全性能还是会上上涨;
    • 启用压缩的前提是,内存尽可能的大
  • 压缩页保留的缘故:
  • 凡以当更新数据的时刻,将 redo
    添加到压缩页的空闲部分,如果只要刷回磁盘,可以直接
    将该压缩页刷回去。如果该页被描写满,则举行相同不良
    reorganize操作(在此之前也要召开解压),真的写满了才做分裂
  • 保留压缩页是为了更快的刷回磁盘
  • 解压的页凡是以还快之询问

透明压缩虽无上述压缩页的问题,因为压缩是文件系统层的,对MySQL是晶莹的

三 CheckPoint

3.1. CheckPoint的作用

  • 缩短数据库的复原日
  • 缓冲池不够用时,将脏页刷新到磁盘
  • 重做日志无可用时, 刷新脏页
  • 数码页首先被读入缓冲池中,当数码页中的某部几乎长长的记下为更新或者插入新的笔录时,所有的操作都是在Buffer
    Pool 先得的;

  • Buffer
    Pool中的某某页与磁盘中之有页在(Space, Page_Number)齐是相同的,但是那内容可能是不同的(Buffer
    Pool中的给更新了了),形成了脏页;

  • 一旦限期将缓冲池中的脏页刷回磁盘(Flush),达到最后一致,即透过CheckPoint机制来刷脏页;

ACCESS 4

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
    ,则表示不需要恢复

3.2.1 日志(redo)中的LSN:

  • 倘当前底LSN为C ,此时针对某个页做修改,则会起M
    个字节的日志(需要写入M个字节的日记),那这的LSN 则为C+M
    。依次类推,LSN是一个平淡递增的值(字节数)。
  • 日记被的LSN代表了日志一共写副了略微个字节。

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>

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左右)

3.4. 刷新

  • Master Thread Checkpoint

  • FLUSH_LIST 中刷新

  • FLUSH_LRU_LIST Checkpoint

  • LRU_LIST中刷新(即使不以污秽页链表中)

    • 5.5原先要保证在
      LRU_LIST 尾部要有100个空闲页(可替换的页),即
      刷新一部分数据 ,保证发生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 参数控制

四. Double Write

4.1 Double Write介绍

  • Double
    Write的目的是以保证数据写副的可靠性,避免partial write的情况

    • partial write(*部分写*)
      • 16K的页才写副了4K6K8K12K的情况(此时是勿完整、不穷之页);
      • 不可以通过redo log进行复原;
      • redo平复的前提是拖欠必须是完整、干净的;
  • Double Write全局的;

  • 共享表空间是一个段对象 double write,然后这个段落由2个区(1M)组成
  • 2M恒定大小(both file and memory);
  • 页在刷新时,首先顺序的状副到double write
  • 下一场再度刷回磁盘(ibd

ACCESS 5

小类似 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> 

4.2 Double Write的开销

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

4.3.Double Write好关闭的前提

4.3.1.支持原子形容的配备

  • 磁盘
    • Funsion-IO
    • 宝存
  • 文件系统
    • ZFS (Linux达到不推荐以)
    • btrfs(Linux达成无引进下)
      • 使用copy on wirte机制,不进行原地更新,而是开辟新的位置,写成功后,将原的页释放
      • 本质上之笔触要保留一个副本

4.3.2. innodb_doublewrite参数

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

五. 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

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列

  • 没有 Insert/Change Buffer常常,每次插入一修记下,就要读取一次页(读取内存,或者由磁盘读到内存),然后用记录插入到页中;

  • 有 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.3. Insert/Change Buffer 性能

ACCESS 6

  • 1.左图一旦开启了Insert/Change Buffer,而右手图不开;
  • 2.一致开端都于强是以还未曾全量的拓展粉刷磁盘(脏页全部以Buffer Pool中,还没满)`
  • 如开始介绍时所说,当Insert Buffer进行合并的时候,性能进行下降
  • 3.开启Insert/Change Buffer后,insert的常量值在5K左右;
  • 4.SSD现象下也建议拉开;

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了`

5.5.Change Buffer

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

      1. Insert
      1. Delete-Marking(标记删除)
      1. Purge(清除)
      1. innodb_change_buffering = all

      2. all

      3. none (禁用)
      4. inserts
      5. deletes
      6. changes =(insert & delete-marking)
      7. purge

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

  • 搜索的时间复杂度
    • B+O(T),只能定位及拖欠记录所当的页;
    • 哈希表 O(1),可以直接定位及记录;
  • 得友善判断是否是活跃的页,如果是生动活泼的页,可以自动开Hash,管理员无需人工干预;
  • MySQL5.6版本后,官方不推荐利用从适应Hash索引- CPU使用率变高`,但是性能没晋级;
  • MySQL5.7受益了innodb_adaptive_hash_index_parts,增加分片,以调减竞争;
  • 只对等值的操作有意义

七. 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>

相关文章