Rechercher dans le manuel MySQL
15.14.5 InnoDB INFORMATION_SCHEMA Buffer Pool Tables
The InnoDB
INFORMATION_SCHEMA
buffer pool tables provide
buffer pool status information and metadata about the pages within
the InnoDB
buffer pool.
The InnoDB
INFORMATION_SCHEMA
buffer pool tables include
those listed below:
- +-----------------------------------------------+
- | Tables_in_INFORMATION_SCHEMA (INNODB_BUFFER%) |
- +-----------------------------------------------+
- | INNODB_BUFFER_PAGE_LRU |
- | INNODB_BUFFER_PAGE |
- | INNODB_BUFFER_POOL_STATS |
- +-----------------------------------------------+
Table Overview
INNODB_BUFFER_PAGE
: Holds information about each page in theInnoDB
buffer pool.INNODB_BUFFER_PAGE_LRU
: Holds information about the pages in theInnoDB
buffer pool, in particular how they are ordered in the LRU list that determines which pages to evict from the buffer pool when it becomes full. TheINNODB_BUFFER_PAGE_LRU
table has the same columns as theINNODB_BUFFER_PAGE
table, except that theINNODB_BUFFER_PAGE_LRU
table has anLRU_POSITION
column instead of aBLOCK_ID
column.INNODB_BUFFER_POOL_STATS
: Provides buffer pool status information. Much of the same information is provided bySHOW ENGINE INNODB STATUS
output, or may be obtained usingInnoDB
buffer pool server status variables.
Querying the INNODB_BUFFER_PAGE
or
INNODB_BUFFER_PAGE_LRU
table can
affect performance. Do not query these tables 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.
Example 15.6 Querying System Data in the INNODB_BUFFER_PAGE Table
This query provides an approximate count of pages that contain
system data by excluding pages where the
TABLE_NAME
value is either
NULL
or includes a slash /
or period .
in the table name, which
indicates a user-defined table.
- +----------+
- +----------+
- | 1516 |
- +----------+
This query returns the approximate number of pages that contain system data, the total number of buffer pool pages, and an approximate percentage of pages that contain system data.
- mysql> SELECT
- (
- FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE
- (
- +--------------+-------------+------------------------+
- | system_pages | total_pages | system_page_percentage |
- +--------------+-------------+------------------------+
- | 295 | 8192 | 4 |
- +--------------+-------------+------------------------+
The type of system data in the buffer pool can be determined by
querying the PAGE_TYPE
value. For example,
the following query returns eight distinct
PAGE_TYPE
values among the pages that contain
system data:
- +-------------------+
- | PAGE_TYPE |
- +-------------------+
- | SYSTEM |
- | IBUF_BITMAP |
- | UNKNOWN |
- | FILE_SPACE_HEADER |
- | INODE |
- | UNDO_LOG |
- | ALLOCATED |
- +-------------------+
Example 15.7 Querying User Data in the INNODB_BUFFER_PAGE Table
This query provides an approximate count of pages containing
user data by counting pages where the
TABLE_NAME
value is NOT
NULL
and NOT LIKE
'%INNODB_TABLES%'
.
- +----------+
- +----------+
- | 7897 |
- +----------+
This query returns the approximate number of pages that contain user data, the total number of buffer pool pages, and an approximate percentage of pages that contain user data.
This query identifies user-defined tables with pages in the buffer pool:
- +-------------------------+
- | TABLE_NAME |
- +-------------------------+
- | `employees`.`salaries` |
- | `employees`.`employees` |
- +-------------------------+
Example 15.8 Querying Index Data in the INNODB_BUFFER_PAGE Table
For information about index pages, query the
INDEX_NAME
column using the name of the
index. For example, the following query returns the number of
pages and total data size of pages for the
emp_no
index that is defined on the
employees.salaries
table:
This query returns the number of pages and total data size of
pages for all indexes defined on the
employees.salaries
table:
Example 15.9 Querying LRU_POSITION Data in the INNODB_BUFFER_PAGE_LRU Table
The INNODB_BUFFER_PAGE_LRU
table
holds information about the pages in the
InnoDB
buffer pool, in particular how they
are ordered that determines which pages to evict from the buffer
pool when it becomes full. The definition for this page is the
same as for INNODB_BUFFER_PAGE
,
except this table has an LRU_POSITION
column
instead of a BLOCK_ID
column.
This query counts the number of positions at a specific location
in the LRU list occupied by pages of the
employees.employees
table.
Example 15.10 Querying the INNODB_BUFFER_POOL_STATS Table
The INNODB_BUFFER_POOL_STATS
table
provides information similar to
SHOW ENGINE INNODB
STATUS
and InnoDB
buffer pool
status variables.
- *************************** 1. row ***************************
- POOL_ID: 0
- POOL_SIZE: 8192
- FREE_BUFFERS: 1
- DATABASE_PAGES: 8173
- OLD_DATABASE_PAGES: 3014
- MODIFIED_DATABASE_PAGES: 0
- PENDING_DECOMPRESS: 0
- PENDING_READS: 0
- PENDING_FLUSH_LRU: 0
- PENDING_FLUSH_LIST: 0
- PAGES_MADE_YOUNG: 15907
- PAGES_NOT_MADE_YOUNG: 3803101
- PAGES_MADE_YOUNG_RATE: 0
- PAGES_MADE_NOT_YOUNG_RATE: 0
- NUMBER_PAGES_READ: 3270
- NUMBER_PAGES_CREATED: 13176
- NUMBER_PAGES_WRITTEN: 15109
- PAGES_READ_RATE: 0
- PAGES_CREATE_RATE: 0
- PAGES_WRITTEN_RATE: 0
- NUMBER_PAGES_GET: 33069332
- HIT_RATE: 0
- YOUNG_MAKE_PER_THOUSAND_GETS: 0
- NOT_YOUNG_MAKE_PER_THOUSAND_GETS: 0
- NUMBER_PAGES_READ_AHEAD: 2713
- NUMBER_READ_AHEAD_EVICTED: 0
- READ_AHEAD_RATE: 0
- READ_AHEAD_EVICTED_RATE: 0
- LRU_IO_TOTAL: 0
- LRU_IO_CURRENT: 0
- UNCOMPRESS_TOTAL: 0
- UNCOMPRESS_CURRENT: 0
For comparison,
SHOW ENGINE INNODB
STATUS
output and InnoDB
buffer
pool status variable output is shown below, based on the same
data set.
For more information about
SHOW ENGINE INNODB
STATUS
output, see
Section 15.16.3, “InnoDB Standard Monitor and Lock Monitor Output”.
- ...
- ----------------------
- ----------------------
- Total large memory allocated 137428992
- Dictionary memory allocated 579084
- Buffer pool size 8192
- Free buffers 1
- Database pages 8173
- Old database pages 3014
- Modified db pages 0
- Pending reads 0
- Pending writes: LRU 0, flush list 0, single page 0
- 0.00 youngs/s, 0.00 non-youngs/s
- 0.00 reads/s, 0.00 creates/s, 0.00 writes/s
- LRU len: 8173, unzip_LRU len: 0
- ...
For status variable descriptions, see Section 5.1.10, “Server Status Variables”.
- +---------------------------------------+-------------+
- +---------------------------------------+-------------+
- | Innodb_buffer_pool_pages_data | 8173 |
- | Innodb_buffer_pool_bytes_data | 133906432 |
- | Innodb_buffer_pool_pages_dirty | 0 |
- | Innodb_buffer_pool_bytes_dirty | 0 |
- | Innodb_buffer_pool_pages_flushed | 15109 |
- | Innodb_buffer_pool_pages_free | 1 |
- | Innodb_buffer_pool_pages_misc | 18 |
- | Innodb_buffer_pool_pages_total | 8192 |
- | Innodb_buffer_pool_read_ahead_rnd | 0 |
- | Innodb_buffer_pool_read_ahead | 2713 |
- | Innodb_buffer_pool_read_ahead_evicted | 0 |
- | Innodb_buffer_pool_read_requests | 33069332 |
- | Innodb_buffer_pool_reads | 558 |
- | Innodb_buffer_pool_wait_free | 0 |
- | Innodb_buffer_pool_write_requests | 11985961 |
- +---------------------------------------+-------------+
Traduction non disponible
Le manuel MySQL n'est pas encore traduit en français sur l'infobrol. Seule la version anglaise est disponible pour l'instant.
Document créé le 26/06/2006, dernière modification le 26/10/2018
Source du document imprimé : https://www.gaudry.be/mysql-rf-innodb-information-schema-buffer-pool-tables.html
L'infobrol est un site personnel dont le contenu n'engage que moi. Le texte est mis à disposition sous licence CreativeCommons(BY-NC-SA). Plus d'info sur les conditions d'utilisation et sur l'auteur.
Références
Ces références et liens indiquent des documents consultés lors de la rédaction de cette page, ou qui peuvent apporter un complément d'information, mais les auteurs de ces sources ne peuvent être tenus responsables du contenu de cette page.
L'auteur de ce site est seul responsable de la manière dont sont présentés ici les différents concepts, et des libertés qui sont prises avec les ouvrages de référence. N'oubliez pas que vous devez croiser les informations de sources multiples afin de diminuer les risques d'erreurs.