innodb体系架构

void master_thread() {
    loop:
    for(int i=0; i<10; i++) {
        do thing once per second
        sleep 1 second if necessary
    }
    do things once per then seconds
    goto loop;
}

void master_thread() {
    loop:
    for(int i=0; i<10; i++) {
        thread_sleep(1)    // sleep 1秒
        do log buffer flush to dish

        if (last_one_second_ios < 5% innodb_io_capacity) {
            do merget 5% innodb_io_capacity insert buffer
        }

        if (buf_get_modified_ratio_pct > innodb_max_dirty_pages_pct) {  // 如果缓冲池中的脏页比例大于innodb_max_dirty_pages_pct(默认是75时)
            do buffer pool flush 100% innodb_io_capacity dirty page  // 刷新全部脏页到磁盘
        } else if (enable adaptive flush) {    // 如果开户了自适应刷新
            do buffer pool flush desired amount dirty page // 通过判断产生redo log的速度决定最合适的刷新脏页的数量
        }

        if (no user activetuy) {
            goto background loop
        }
    }

    if (last_ten_second_ios < innodb_io_capacity) {  // 如果过去10内磁盘IO次数小于设置的innodb_io_capacity的值（默认是200）
        do buffer pool flush 100%  innodb_io_capacity dirty page
    }

    do merge 5% innodb_io_capacity insert buffer  // 合并插入缓冲是innodb_io_capacity的5%（10）（总是）
    do log buffer flush to dish
    do flush purge

    if (buf_get_modified_ratio_pct > 70%) {
        do buffer pool flush 100% innodb_io_capacity dirty page
    } else {
        do buffer pool flush 10% innodb_io_capacity dirty page
    }
    goto loop

    backgroud loop:   // 后台循环
    do full purge     // 删除无用的undo页 （总是）
    do merger 5% innodb_io_capacity insert buffer  // 合并插入缓冲是innodb_io_capacity的5%（10）（总是）
    if not idle:      // 如果不空闲，就跳回主循环，如果空闲就跳入flush loop
    goto loop:    // 跳到主循环
    else:
        goto flush loop
    flush loop:  // 刷新循环
    do buf_get_modified_ratio_pct pool flush 100% innodb_io_capacity dirty page // 刷新200个脏页到磁盘
    if ( buf_get_modified_ratio_pct > innodb_max_dirty_pages_pct ) // 如果缓冲池中的脏页比例大于innodb_max_dirty_pages_pct的值（默认75%）
        goto flush loop            // 跳到刷新循环，不断刷新脏页，直到符合条件
        goto suspend loop          // 完成刷新脏页的任务后，跳入suspend loop
    suspend loop:
    suspend_thread()               //master线程挂起，等待事件发生
    waiting event
    goto loop;
}

if (InnoDB is idle) {
    srv_master_do_idle_tasks();    // 每10秒操作
} else {
    srv_master_do_active_tasks();    // 每秒操作
}

--------
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)
...共16个
I/O thread 18 state: waiting for completed aio requests (write thread)
...共16个

线程控制方式：
mysql> show variables like '%io_threads%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| innodb_read_io_threads  | 16    |
| innodb_write_io_threads | 16    |
+-------------------------+-------+
2 rows in set (0.00 sec)

mysql> show variables like '%purge_threads%';  
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| innodb_purge_threads | 4     |
+----------------------+-------+
1 row in set (0.00 sec)

mysql> show variables like '%innodb_buffer_pool%';
+-------------------------------------+----------------+
| Variable_name                       | Value          |
+-------------------------------------+----------------+
| innodb_buffer_pool_instances        | 8              |
| innodb_buffer_pool_size             | 17179869184    |
+-------------------------------------+----------------+

----------------------
INDIVIDUAL BUFFER POOL INFO
----------------------
---BUFFER POOL 0 --标记（0-7）
Buffer pool size   131072 --大小，此处统计的是页，每个页大小为16K，则总大小为2G
Free buffers       44109 --FREE列表中的页数量
Database pages     79650 --LRU列表中的页数量
Old database pages 29382
Modified db pages  49525
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 76764, created 2886, written 2897291
0.00 reads/s, 0.00 creates/s, 544.74 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: 79650, unzip_LRU len: 0
I/O sum[0]:cur[4730], unzip sum[0]:cur[0]

mysql> select pool_id,pool_size from information_schema.innodb_buffer_pool_stats;
+---------+-----------+
| pool_id | pool_size |
+---------+-----------+
|       0 |    131072 |
|       1 |    131072 |
|       2 |    131072 |
|       3 |    131072 |
|       4 |    131072 |
|       5 |    131072 |
|       6 |    131072 |
|       7 |    131072 |
+---------+-----------+
8 rows in set (0.01 sec)

mysql> show variables like 'innodb_old_blocks_pct';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_old_blocks_pct | 37    |
+-----------------------+-------+
1 row in set (0.00 sec)

mysql> show variables like 'innodb_old_blocks_time';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| innodb_old_blocks_time | 1000  |
+------------------------+-------+
1 row in set (0.00 sec)

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE_LRU LIMIT 1\G
*************************** 1. row ***************************
            POOL_ID: 0
       LRU_POSITION: 0
              SPACE: 97
        PAGE_NUMBER: 1984
          PAGE_TYPE: INDEX
         FLUSH_TYPE: 1
          FIX_COUNT: 0
          IS_HASHED: YES
NEWEST_MODIFICATION: 719490396
OLDEST_MODIFICATION: 0
        ACCESS_TIME: 3378383796
         TABLE_NAME: `employees`.`salaries`
         INDEX_NAME: PRIMARY
     NUMBER_RECORDS: 468
          DATA_SIZE: 14976
    COMPRESSED_SIZE: 0
         COMPRESSED: NO
             IO_FIX: IO_NONE
             IS_OLD: YES
    FREE_PAGE_CLOCK: 0

mysql> select table_name,page_type,count(*) from information_schema.innodb_buffer_page_lru where pool_id=1 group by page_type,table_name;
+------------------------------+-------------------+----------+
| table_name                   | page_type         | count(*) |
+------------------------------+-------------------+----------+
| NULL                         | EXTENT_DESCRIPTOR |       11 |
| NULL                         | FILE_SPACE_HEADER |        1 |
| NULL                         | IBUF_BITMAP       |       13 |
| `mysql`.`innodb_table_stats` | INDEX             |        1 |
| `SYS_TABLES`                 | INDEX             |     2880 |
| `t_1000w`.`sbtest1`          | INDEX             |    19332 |
| `t_3000w`.`sbtest1`          | INDEX             |    57289 |
| NULL                         | INODE             |        2 |
| NULL                         | SYSTEM            |       24 |
| NULL                         | UNDO_LOG          |      718 |
+------------------------------+-------------------+----------+
10 rows in set (2.08 sec)