mysql lru_MySQL技术内幕InnoDB存储引擎 - 内存 - LRU List、Free List、Flush List

SHOW VARIABLES LIKE 'innodb_old_blocks_time'innodb_old_blocks_time 1000

如果预估热数据比较多，可以适当增大这个时间，防止真正的热数据被刷出 缓冲区。

----------------------

BUFFER POOL ANDMEMORY

----------------------

Total large memory allocated 137428992Dictionary memory allocated 1214677Buffer pool size 8192Free buffers 6371

Database pages 1796Old database pages 670Modified db pages 0Pending reads 0Pending writes: LRU 0, flush list 0, single page 0Pages made young 5, not young 0

0.00 youngs/s, 0.00 non-youngs/s

Pages read 1468, created 328, written 690057

0.00 reads/s, 0.00 creates/s, 0.29 writes/s

Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s

LRU len: 1796, unzip_LRU len: 0I/O sum[0]:cur[0], unzip sum[0]:cur[0]

Free buffers为空闲页列表

Database pages 为 LRU列表中的页。

这里例子中， Old database pages / Database pages= 37% ，和innodb_old_blocks_pct非常吻合。

但是， Database pages +  Free buffers 不等于 Buffer pool size ，因为：

缓冲池中的 自适应哈希索引、 Lock 信息、 Insert Buffer 这些页，

不需要 LRU 算法进行维护，因此不存在于 LRU 列表中。

SELECT POOL_ID,HIT_RATE,PAGES_MADE_YOUNG,PAGES_NOT_MADE_YOUNG FROMinformation_schema.INNODB_BUFFER_POOL_STATS;

+---------+----------+------------------+----------------------+

| POOL_ID | HIT_RATE | PAGES_MADE_YOUNG | PAGES_NOT_MADE_YOUNG |

+---------+----------+------------------+----------------------+

| 0 | 1000 | 5 | 0 |

+---------+----------+------------------+----------------------+

还可以通过表 INNODB_BUFFER_PAGE_LRU 来观察每个 LRU 表中每个页的具体信息:

SELECT TABLE_NAME , SPACE , PAGE_NUMBER , PAGE_TYPE FROMinformation_schema.INNODB_BUFFER_PAGE_LRU;

+------------+------------+-------------+------------+

| TABLE_NAME | SPACE | PAGE_NUMBER | PAGE_TYPE |

+------------+------------+-------------+------------+

| NULL | 0 | 7 | SYSTEM |

| NULL | 0 | 3 | SYSTEM |

| NULL | 0 | 2 | INODE |

| NULL | 0 | 4 | IBUF_INDEX |

| NULL | 0 | 5 | TRX_SYSTEM |

| NULL | 0 | 6 | SYSTEM |

| NULL | 4294967279 | 3 | RSEG_ARRAY |

| NULL | 4294967279 | 4 | SYSTEM |

| NULL | 4294967279 | 388 | UNDO_LOG |

| NULL | 4294967279 | 256 | UNDO_LOG |

| NULL | 4294967279 | 5 | SYSTEM |

| NULL | 4294967279 | 257 | UNDO_LOG |

| NULL | 4294967279 | 381 | UNDO_LOG |

| NULL | 4294967279 | 6 | SYSTEM |

| NULL | 4294967279 | 382 | UNDO_LOG |

| NULL | 4294967279 | 286 | UNDO_LOG |

| NULL | 4294967279 | 7 | SYSTEM |

| NULL | 4294967279 | 324 | UNDO_LOG |

| NULL | 4294967279 | 287 | UNDO_LOG |

| NULL | 4294967279 | 8 | SYSTEM |

...

+------------+------------+-------------+------------+

#第500行

+-----------------------------------------------+------------+-------------+-------------------+

| TABLE_NAME | SPACE | PAGE_NUMBER | PAGE_TYPE |

+-----------------------------------------------+------------+-------------+-------------------+

| `test_sec_vault`.`user_group` | 74 | 4 | INDEX |

| `test_sec_vault`.`oauth_client_details` | 38 | 6 | INDEX |

| `test_sec_vault`.`app_resource` | 83 | 5 | INDEX |

| `test_sec_vault`.`app_resource` | 83 | 6 | INDEX |

| `test_sec_vault`.`app_resource_to_tenant` | 16 | 6 | INDEX |

| `test_sec_vault`.`app_resource_to_tenant` | 16 | 4 | INDEX |

| `test_sec_vault`.`policy_plugin_config` | 44 | 5 | INDEX |

| NULL | 49 | 1 | IBUF_BITMAP |

| NULL | 21 | 1 | IBUF_BITMAP |

| NULL | 7 | 1 | IBUF_BITMAP |

| `test_sec_vault`.`safe` | 66 | 5 | INDEX |

| `test_sec_vault`.`acct_retry_task` | 10 | 5 | INDEX |

| `test_sec_vault`.`acct_task_msg` | 11 | 5 | INDEX |

| `test_sec_vault`.`acct_task_msg` | 11 | 4 | INDEX |

| `test_sec_vault`.`acct_doing_task` | 7 | 6 | INDEX |

| `test_sec_vault`.`account` | 2 | 4 | INDEX |

压缩页

InnoDB支持对内存页进行压缩，将原本16K的页压缩为1KB,2KB,4KB,8KB。

通过 SHOW ENGINE INNODB STATUS 可以查看到相关信息：

LRU len: 1796, unzip_LRU len: 0I/O sum[0]:cur[0], unzip sum[0]:cur[0]

LRU 中的页包含了 unzip_LRU 列表中的页。

脏页

LRU 列表中的页被修改后，称该页为脏页( dirty page )。

通过 SHOW ENGINE INNODB STATUS 可以查看脏页数量：

----------------------

BUFFER POOL ANDMEMORY----------------------

Total large memory allocated 137428992Dictionary memory allocated1214677Buffer pool size8192Free buffers6371

Database pages 1796Olddatabase pages 670Modified db pages0

通过下面的命令可以查看脏页的具体情况。

SELECT TABLE_NAME , SPACE , PAGE_NUMBER , PAGE_TYPE FROM information_schema.INNODB_BUFFER_PAGE_LRU WHERE OLDEST_MODIFICATION> 0 ;