Rechercher dans le manuel MySQL

15.14.5 InnoDB INFORMATION_SCHEMA Buffer Pool Tables

Inhaltsverzeichnis

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:

  1. mysql> SHOW TABLES FROM INFORMATION_SCHEMA LIKE 'INNODB_BUFFER%';
  2. +-----------------------------------------------+
  3. | Tables_in_INFORMATION_SCHEMA (INNODB_BUFFER%) |
  4. +-----------------------------------------------+
  5. | INNODB_BUFFER_PAGE_LRU                        |
  6. | INNODB_BUFFER_PAGE                            |
  7. | INNODB_BUFFER_POOL_STATS                      |
  8. +-----------------------------------------------+

Table Overview

Warning

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.

  1. mysql> SELECT COUNT(*) FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE
  2.        WHERE TABLE_NAME IS NULL OR (INSTR(TABLE_NAME, '/') = 0 AND INSTR(TABLE_NAME, '.') = 0);
  3. +----------+
  4. | COUNT(*) |
  5. +----------+
  6. |     1516 |
  7. +----------+

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.

  1. mysql> SELECT
  2.        (SELECT COUNT(*) FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE
  3.        WHERE TABLE_NAME IS NULL OR (INSTR(TABLE_NAME, '/') = 0 AND INSTR(TABLE_NAME, '.') = 0)
  4.        ) AS system_pages,
  5.        (
  6.        SELECT COUNT(*)
  7.        FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE
  8.        ) AS total_pages,
  9.        (
  10.        SELECT ROUND((system_pages/total_pages) * 100)
  11.        ) AS system_page_percentage;
  12. +--------------+-------------+------------------------+
  13. | system_pages | total_pages | system_page_percentage |
  14. +--------------+-------------+------------------------+
  15. |          295 |        8192 |                      4 |
  16. +--------------+-------------+------------------------+

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:

  1. mysql> SELECT DISTINCT PAGE_TYPE FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE
  2.        WHERE TABLE_NAME IS NULL OR (INSTR(TABLE_NAME, '/') = 0 AND INSTR(TABLE_NAME, '.') = 0);
  3. +-------------------+
  4. | PAGE_TYPE         |
  5. +-------------------+
  6. | SYSTEM            |
  7. | IBUF_BITMAP       |
  8. | UNKNOWN           |
  9. | FILE_SPACE_HEADER |
  10. | INODE             |
  11. | UNDO_LOG          |
  12. | ALLOCATED         |
  13. +-------------------+

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%'.

  1. mysql> SELECT COUNT(*) FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE
  2.        WHERE TABLE_NAME IS NOT NULL AND TABLE_NAME NOT LIKE '%INNODB_TABLES%';
  3. +----------+
  4. | COUNT(*) |
  5. +----------+
  6. |     7897 |
  7. +----------+

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.

  1. mysql> SELECT
  2.        (SELECT COUNT(*) FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE
  3.        WHERE TABLE_NAME IS NOT NULL AND (INSTR(TABLE_NAME, '/') > 0 OR INSTR(TABLE_NAME, '.') > 0)
  4.        ) AS user_pages,
  5.        (
  6.        SELECT COUNT(*)
  7.        FROM information_schema.INNODB_BUFFER_PAGE
  8.        ) AS total_pages,
  9.        (
  10.        SELECT ROUND((user_pages/total_pages) * 100)
  11.        ) AS user_page_percentage;
  12. +------------+-------------+----------------------+
  13. | user_pages | total_pages | user_page_percentage |
  14. +------------+-------------+----------------------+
  15. |       7897 |        8192 |                   96 |
  16. +------------+-------------+----------------------+

This query identifies user-defined tables with pages in the buffer pool:

  1. mysql> SELECT DISTINCT TABLE_NAME FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE
  2.        WHERE TABLE_NAME IS NOT NULL AND (INSTR(TABLE_NAME, '/') > 0 OR INSTR(TABLE_NAME, '.') > 0)
  3.        AND TABLE_NAME NOT LIKE '`mysql`.`innodb_%';
  4. +-------------------------+
  5. | TABLE_NAME              |
  6. +-------------------------+
  7. | `employees`.`salaries`  |
  8. | `employees`.`employees` |
  9. +-------------------------+

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:

  1. mysql> SELECT INDEX_NAME, COUNT(*) AS Pages,
  2. ROUND(SUM(IF(COMPRESSED_SIZE = 0, @@GLOBAL.innodb_page_size, COMPRESSED_SIZE))/1024/1024)
  3. AS 'Total Data (MB)'
  4. FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE
  5. WHERE INDEX_NAME='emp_no' AND TABLE_NAME = '`employees`.`salaries`';
  6. +------------+-------+-----------------+
  7. | INDEX_NAME | Pages | Total Data (MB) |
  8. +------------+-------+-----------------+
  9. | emp_no     |  1609 |              25 |
  10. +------------+-------+-----------------+

This query returns the number of pages and total data size of pages for all indexes defined on the employees.salaries table:

  1. mysql> SELECT INDEX_NAME, COUNT(*) AS Pages,
  2.        ROUND(SUM(IF(COMPRESSED_SIZE = 0, @@GLOBAL.innodb_page_size, COMPRESSED_SIZE))/1024/1024)
  3.        AS 'Total Data (MB)'
  4.        FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE
  5.        WHERE TABLE_NAME = '`employees`.`salaries`'
  6.        GROUP BY INDEX_NAME;
  7. +------------+-------+-----------------+
  8. | INDEX_NAME | Pages | Total Data (MB) |
  9. +------------+-------+-----------------+
  10. | emp_no     |  1608 |              25 |
  11. | PRIMARY    |  6086 |              95 |
  12. +------------+-------+-----------------+

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.

  1. mysql> SELECT COUNT(LRU_POSITION) FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE_LRU
  2.        WHERE TABLE_NAME='`employees`.`employees`' AND LRU_POSITION < 3072;
  3. +---------------------+
  4. | COUNT(LRU_POSITION) |
  5. +---------------------+
  6. |                 548 |
  7. +---------------------+

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. mysql> SELECT * FROM information_schema.INNODB_BUFFER_POOL_STATS \G
  2. *************************** 1. row ***************************
  3.                          POOL_ID: 0
  4.                        POOL_SIZE: 8192
  5.                     FREE_BUFFERS: 1
  6.                   DATABASE_PAGES: 8173
  7.               OLD_DATABASE_PAGES: 3014
  8.          MODIFIED_DATABASE_PAGES: 0
  9.               PENDING_DECOMPRESS: 0
  10.                    PENDING_READS: 0
  11.                PENDING_FLUSH_LRU: 0
  12.               PENDING_FLUSH_LIST: 0
  13.                 PAGES_MADE_YOUNG: 15907
  14.             PAGES_NOT_MADE_YOUNG: 3803101
  15.            PAGES_MADE_YOUNG_RATE: 0
  16.        PAGES_MADE_NOT_YOUNG_RATE: 0
  17.                NUMBER_PAGES_READ: 3270
  18.             NUMBER_PAGES_CREATED: 13176
  19.             NUMBER_PAGES_WRITTEN: 15109
  20.                  PAGES_READ_RATE: 0
  21.                PAGES_CREATE_RATE: 0
  22.               PAGES_WRITTEN_RATE: 0
  23.                 NUMBER_PAGES_GET: 33069332
  24.                         HIT_RATE: 0
  25.     YOUNG_MAKE_PER_THOUSAND_GETS: 0
  26. NOT_YOUNG_MAKE_PER_THOUSAND_GETS: 0
  27.          NUMBER_PAGES_READ_AHEAD: 2713
  28.        NUMBER_READ_AHEAD_EVICTED: 0
  29.                  READ_AHEAD_RATE: 0
  30.          READ_AHEAD_EVICTED_RATE: 0
  31.                     LRU_IO_TOTAL: 0
  32.                   LRU_IO_CURRENT: 0
  33.                 UNCOMPRESS_TOTAL: 0
  34.               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”.

  1. mysql> SHOW ENGINE INNODB STATUS \G
  2. ...
  3. ----------------------
  4. BUFFER POOL AND MEMORY
  5. ----------------------
  6. Total large memory allocated 137428992
  7. Dictionary memory allocated 579084
  8. Buffer pool size   8192
  9. Free buffers       1
  10. Database pages     8173
  11. Old database pages 3014
  12. Modified db pages  0
  13. Pending reads 0
  14. Pending writes: LRU 0, flush list 0, single page 0
  15. Pages made young 15907, not young 3803101
  16. 0.00 youngs/s, 0.00 non-youngs/s
  17. Pages read 3270, created 13176, written 15109
  18. 0.00 reads/s, 0.00 creates/s, 0.00 writes/s
  19. No buffer pool page gets since the last printout
  20. Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
  21. LRU len: 8173, unzip_LRU len: 0
  22. I/O sum[0]:cur[0], unzip sum[0]:cur[0]
  23. ...

For status variable descriptions, see Section 5.1.10, “Server Status Variables”.

  1. mysql> SHOW STATUS LIKE 'Innodb_buffer%';
  2. +---------------------------------------+-------------+
  3. | Variable_name                         | Value       |
  4. +---------------------------------------+-------------+
  5. | Innodb_buffer_pool_dump_status        | not started |
  6. | Innodb_buffer_pool_load_status        | not started |
  7. | Innodb_buffer_pool_resize_status      | not started |
  8. | Innodb_buffer_pool_pages_data         | 8173        |
  9. | Innodb_buffer_pool_bytes_data         | 133906432   |
  10. | Innodb_buffer_pool_pages_dirty        | 0           |
  11. | Innodb_buffer_pool_bytes_dirty        | 0           |
  12. | Innodb_buffer_pool_pages_flushed      | 15109       |
  13. | Innodb_buffer_pool_pages_free         | 1           |
  14. | Innodb_buffer_pool_pages_misc         | 18          |
  15. | Innodb_buffer_pool_pages_total        | 8192        |
  16. | Innodb_buffer_pool_read_ahead_rnd     | 0           |
  17. | Innodb_buffer_pool_read_ahead         | 2713        |
  18. | Innodb_buffer_pool_read_ahead_evicted | 0           |
  19. | Innodb_buffer_pool_read_requests      | 33069332    |
  20. | Innodb_buffer_pool_reads              | 558         |
  21. | Innodb_buffer_pool_wait_free          | 0           |
  22. | Innodb_buffer_pool_write_requests     | 11985961    |
  23. +---------------------------------------+-------------+


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-information-schema-buffer-pool-tables.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

  1. Zeigen Sie - html-Dokument Sprache des Dokuments:en 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