25.39.1 The INFORMATION_SCHEMA INNODB_BUFFER_PAGE Table

The INNODB_BUFFER_PAGE table provides information about each page in the InnoDB buffer pool.

For related usage information and examples, see Section 15.14.5, “InnoDB INFORMATION_SCHEMA Buffer Pool Tables”.

Warning

Querying the INNODB_BUFFER_PAGE table can affect performance. Do not query this table on a production system unless you are aware of the performance impact and have determined it to be acceptable. To avoid impacting performance on a production system, reproduce the issue you want to investigate and query buffer pool statistics on a test instance.

The INNODB_BUFFER_PAGE table has these columns:

POOL_ID
The buffer pool ID. This is an identifier to distinguish between multiple buffer pool instances.
BLOCK_ID
The buffer pool block ID.
SPACE
The tablespace ID; the same value as INNODB_TABLES.SPACE.
PAGE_NUMBER
The page number.

PAGE_TYPE

The page type. The following table shows the permitted values.

Table 25.1 INNODB_BUFFER_PAGE.PAGE_TYPE Values

Page Type	Description
`ALLOCATED`	Freshly allocated page
`BLOB`	Uncompressed BLOB page
`COMPRESSED_BLOB2`	Subsequent comp BLOB page
`COMPRESSED_BLOB`	First compressed BLOB page
`ENCRYPTED_RTREE`	Encrypted R-tree
`EXTENT_DESCRIPTOR`	Extent descriptor page
`FILE_SPACE_HEADER`	File space header
`FIL_PAGE_TYPE_UNUSED`	Unused
`IBUF_BITMAP`	Insert buffer bitmap
`IBUF_FREE_LIST`	Insert buffer free list
`IBUF_INDEX`	Insert buffer index
`INDEX`	B-tree node
`INODE`	Index node
`LOB_DATA`	Uncompressed LOB data
`LOB_FIRST`	First page of uncompressed LOB
`LOB_INDEX`	Uncompressed LOB index
`PAGE_IO_COMPRESSED`	Compressed page
`PAGE_IO_COMPRESSED_ENCRYPTED`	Compressed and encrypted page
`PAGE_IO_ENCRYPTED`	Encrypted page
`RSEG_ARRAY`	Rollback segment array
`RTREE_INDEX`	R-tree index
`SDI_BLOB`	Uncompressed SDI BLOB
`SDI_COMPRESSED_BLOB`	Compressed SDI BLOB
`SDI_INDEX`	SDI index
`SYSTEM`	System page
`TRX_SYSTEM`	Transaction system data
`UNDO_LOG`	Undo log page
`UNKNOWN`	Unknown
`ZLOB_DATA`	Compressed LOB data
`ZLOB_FIRST`	First page of compressed LOB
`ZLOB_FRAG`	Compressed LOB fragment
`ZLOB_FRAG_ENTRY`	Compressed LOB fragment index
`ZLOB_INDEX`	Compressed LOB index

FLUSH_TYPE
The flush type.
FIX_COUNT
The number of threads using this block within the buffer pool. When zero, the block is eligible to be evicted.
IS_HASHED
Whether a hash index has been built on this page.
NEWEST_MODIFICATION
The Log Sequence Number of the youngest modification.
OLDEST_MODIFICATION
The Log Sequence Number of the oldest modification.
ACCESS_TIME
An abstract number used to judge the first access time of the page.
TABLE_NAME
The name of the table the page belongs to. This column is applicable only to pages with a PAGE_TYPE value of INDEX.
INDEX_NAME
The name of the index the page belongs to. This can be the name of a clustered index or a secondary index. This column is applicable only to pages with a PAGE_TYPE value of INDEX.
NUMBER_RECORDS
The number of records within the page.
DATA_SIZE
The sum of the sizes of the records. This column is applicable only to pages with a PAGE_TYPE value of INDEX.
COMPRESSED_SIZE
The compressed page size. NULL for pages that are not compressed.

PAGE_STATE

The page state. The following table shows the permitted values.

Table 25.2 INNODB_BUFFER_PAGE.PAGE_STATE Values

Page State	Description
`FILE_PAGE`	A buffered file page
`MEMORY`	Contains a main memory object
`NOT_USED`	In the free list
`NULL`	Clean compressed pages, compressed pages in the flush list, pages used as buffer pool watch sentinels
`READY_FOR_USE`	A free page
`REMOVE_HASH`	Hash index should be removed before placing in the free list

IO_FIX
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
Whether 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. The freed_page_clock counter tracks the number of blocks removed from the end of the LRU list.

Example

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE LIMIT 1\G
*************************** 1. row ***************************
POOL_ID: 0
BLOCK_ID: 0
SPACE: 97
PAGE_NUMBER: 2473
PAGE_TYPE: INDEX
FLUSH_TYPE: 1
FIX_COUNT: 0
IS_HASHED: YES
NEWEST_MODIFICATION: 733855581
OLDEST_MODIFICATION: 0
ACCESS_TIME: 3378385672
TABLE_NAME: `employees`.`salaries`
INDEX_NAME: PRIMARY
NUMBER_RECORDS: 468
DATA_SIZE: 14976
COMPRESSED_SIZE: 0
PAGE_STATE: FILE_PAGE
IO_FIX: IO_NONE
IS_OLD: YES
FREE_PAGE_CLOCK: 66

Inhaltsverzeichnis Haut

Notes

This table is useful primarily for expert-level performance monitoring, or when developing performance-related extensions for MySQL.
You must have the PROCESS privilege to query this table.
Use the INFORMATION_SCHEMA COLUMNS table or the SHOW COLUMNS statement to view additional information about the columns of this table, including data types and default values.
When tables, table rows, partitions, or indexes are deleted, associated pages remain in the buffer pool until space is required for other data. The INNODB_BUFFER_PAGE table reports information about these pages until they are evicted from the buffer pool. For more information about how the InnoDB manages buffer pool data, see Section 15.5.1, “Buffer Pool”.

Suchen Sie im MySQL-Handbuch

Deutsche Übersetzung

Sie haben gebeten, diese Seite auf Deutsch zu besuchen. Momentan ist nur die Oberfläche übersetzt, aber noch nicht der gesamte Inhalt.

Wenn Sie mir bei Übersetzungen helfen wollen, ist Ihr Beitrag willkommen. Alles, was Sie tun müssen, ist, sich auf der Website zu registrieren und mir eine Nachricht zu schicken, in der Sie gebeten werden, Sie der Gruppe der Übersetzer hinzuzufügen, die Ihnen die Möglichkeit gibt, die gewünschten Seiten zu übersetzen. Ein Link am Ende jeder übersetzten Seite zeigt an, dass Sie der Übersetzer sind und einen Link zu Ihrem Profil haben.

Vielen Dank im Voraus.

Dokument erstellt 26/06/2006, zuletzt geändert 26/10/2018
Quelle des gedruckten Dokuments:https://www.gaudry.be/de/mysql-rf-innodb-buffer-page-table.html

Die Infobro ist eine persönliche Seite, deren Inhalt in meiner alleinigen Verantwortung liegt. Der Text ist unter der CreativeCommons-Lizenz (BY-NC-SA) verfügbar. Weitere Informationen auf die Nutzungsbedingungen und dem Autor.

Referenzen

Manuel MySQL : https://dev.mysql.com/

Diese Verweise und Links verweisen auf Dokumente, die während des Schreibens dieser Seite konsultiert wurden, oder die zusätzliche Informationen liefern können, aber die Autoren dieser Quellen können nicht für den Inhalt dieser Seite verantwortlich gemacht werden.
Der Autor Diese Website ist allein dafür verantwortlich, wie die verschiedenen Konzepte und Freiheiten, die mit den Nachschlagewerken gemacht werden, hier dargestellt werden. Denken Sie daran, dass Sie mehrere Quellinformationen austauschen müssen, um das Risiko von Fehlern zu reduzieren.

Inhaltsverzeichnis Haut