INNODB_BUFFER_PAGE_LRU 介绍
2,332 total views, 1 views today
INNODB_BUFFER_PAGE_LRU 表存在于INFORMATION_SCHEMA中，它记录了InnoDB buffer pool中所有pages的信息，特别是当buffer pool满了之后，LRU列表决定了按照顺序驱逐pages。
警告！！查询INNODB_BUFFER_PAGE_LRU表会引起显著的性能压力，千万不要在生产环境查询该表。
一、INNODB_BUFFER_PAGE_LRU 的列介绍
| Column name | Description |
|---|---|
POOL_ID |
Buffer Pool ID. An identifier to distinguish between multiple buffer pool instances. 多个buffer pool时，page所在buffer pool 的id。 |
LRU_POSITION |
The position of the page in the LRU list. 该page在LRU列表中的位置。 |
SPACE |
Tablespace ID. Uses the same value as in INNODB_SYS_TABLES.SPACE.MySQL InnoDB buffer pool预热机制保存数据时，需要的tablespace id就是获取的这列。 |
PAGE_NUMBER |
Page number. |
PAGE_TYPE |
Page type. Permitted values are ALLOCATED (Freshly allocated page), INDEX (B-tree node), UNDO_LOG (Undo log page), INODE(Index node), IBUF_FREE_LIST (Insert buffer free list), IBUF_BITMAP (Insert buffer bitmap), SYSTEM (System page), TRX_SYSTEM(Transaction system data), FILE_SPACE_HEADER (File space header), EXTENT_DESCRIPTOR (Extent descriptor page), BLOB(Uncompressed BLOB page), COMPRESSED_BLOB (First compressed BLOB page), COMPRESSED_BLOB2 (Subsequent comp BLOB page), IBUF_INDEX (Insert buffer index), UNKNOWN (unknown).Page的类型，如INDEX、UNDO_LOG、system page、TRX_SYSTEM等类型。 |
FLUSH_TYPE |
Flush type. |
FIX_COUNT |
Number of threads using this block within the buffer pool. When zero, the block is eligible to be evicted. thread从buffer pool获取的block数量。 |
IS_HASHED |
Whether hash index has been built on this page. |
NEWEST_MODIFICATION |
Log Sequence Number of the youngest modification. |
OLDEST_MODIFICATION |
Log Sequence Number of the oldest modification. |
ACCESS_TIME |
An abstract number used to judge the first access time of the page. |
TABLE_NAME |
Name of the table the page belongs to. This column is only applicable to pages of type INDEX.page所属的表名。 |
INDEX_NAME |
Name of the index the page belongs to. It can be the name of a clustered index or a secondary index. This column is only applicable to pages of type INDEX.page所属的索引名。 |
NUMBER_RECORDS |
Number of records within the page. page中含有的记录数量。 |
DATA_SIZE |
Sum of the sizes of the records. This column is only applicable to pages of type INDEX. |
COMPRESSED_SIZE |
Compressed page size. Null for pages that are not compressed. |
PAGE_STATE |
Page state. A page with valid data has one of the following states: FILE_PAGE (buffers a page of data from a file), MEMORY(buffers a page from an in-memory object), COMPRESSED. Other possible states (managed by InnoDB) are: NULL,READY_FOR_USE, NOT_USED, REMOVE_HASH. |
IO_FIX |
Specifies whether any I/O is pending for this page: IO_NONE = no pending I/O, IO_READ = read pending, IO_WRITE = write pending. |
IS_OLD |
Specifies whether or not the block is in the sublist of old blocks in the LRU list. |
FREE_PAGE_CLOCK |
The value of the freed_page_clock counter when the block was the last placed at the head of the LRU list. Thefreed_page_clock counter tracks the number of blocks removed from the end of the LRU list. |
二、例子
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
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 |
三、提示
- 查询该表必须有PROCESS权限；
- 使用DESC 、SHOW COLUMNS命令去查看列和数据类型的信息；
- 查询INNODB_BUFFER_PAGE_LRU表需要分配连续的内存，特别是InnoDB buffer pool上G容量的时候，可能会导致OOM（out-of-memory）；
- 查询INNODB_BUFFER_PAGE_LRU表会锁定LRU列中的数据结构，特别是InnoDB buffer pool上G容量的时候，会导致并发性下降。
- 删除表、表数据、分区或者索引，这些被删除的对象的page并不会马上从buffer pool中清除，直到空间紧张，才把它们删除。