Rechercher dans le manuel MySQL

25.39.1 The INFORMATION_SCHEMA INNODB_BUFFER_PAGE Table

Inhoudsopgave

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

  1. mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE LIMIT 1\G
  2. *************************** 1. row ***************************
  3.             POOL_ID: 0
  4.            BLOCK_ID: 0
  5.               SPACE: 97
  6.         PAGE_NUMBER: 2473
  7.           PAGE_TYPE: INDEX
  8.          FLUSH_TYPE: 1
  9.           FIX_COUNT: 0
  10.           IS_HASHED: YES
  11. NEWEST_MODIFICATION: 733855581
  12. OLDEST_MODIFICATION: 0
  13.         ACCESS_TIME: 3378385672
  14.          TABLE_NAME: `employees`.`salaries`
  15.          INDEX_NAME: PRIMARY
  16.      NUMBER_RECORDS: 468
  17.           DATA_SIZE: 14976
  18.     COMPRESSED_SIZE: 0
  19.          PAGE_STATE: FILE_PAGE
  20.              IO_FIX: IO_NONE
  21.              IS_OLD: YES
  22.     FREE_PAGE_CLOCK: 66

Inhoudsopgave 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”.


Zoek in de MySQL-handleiding

Nederlandse vertaling

U hebt gevraagd om deze site in het Nederlands te bezoeken. Voor nu wordt alleen de interface vertaald, maar nog niet alle inhoud.

Als je me wilt helpen met vertalingen, is je bijdrage welkom. Het enige dat u hoeft te doen, is u op de site registreren en mij een bericht sturen waarin u wordt gevraagd om u toe te voegen aan de groep vertalers, zodat u de gewenste pagina's kunt vertalen. Een link onderaan elke vertaalde pagina geeft aan dat u de vertaler bent en heeft een link naar uw profiel.

Bij voorbaat dank.

Document heeft de 26/06/2006 gemaakt, de laatste keer de 26/10/2018 gewijzigd
Bron van het afgedrukte document:https://www.gaudry.be/nl/mysql-rf-innodb-buffer-page-table.html

De infobrol is een persoonlijke site waarvan de inhoud uitsluitend mijn verantwoordelijkheid is. De tekst is beschikbaar onder CreativeCommons-licentie (BY-NC-SA). Meer info op de gebruiksvoorwaarden en de auteur.

Referenties

  1. Bekijk - html-document Taal van het document:en Manuel MySQL : https://dev.mysql.com/

Deze verwijzingen en links verwijzen naar documenten die geraadpleegd zijn tijdens het schrijven van deze pagina, of die aanvullende informatie kunnen geven, maar de auteurs van deze bronnen kunnen niet verantwoordelijk worden gehouden voor de inhoud van deze pagina.
De auteur Deze site is als enige verantwoordelijk voor de manier waarop de verschillende concepten, en de vrijheden die met de referentiewerken worden genomen, hier worden gepresenteerd. Vergeet niet dat u meerdere broninformatie moet doorgeven om het risico op fouten te verkleinen.

Inhoudsopgave Haut