Movatterモバイル変換

This query provides an approximate count of pages that contain system data by excluding pages where theTABLE_NAME value is eitherNULL or includes a slash/ or period. in the table name, which indicates a user-defined table.

mysql> SELECT COUNT(*) FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE       WHERE TABLE_NAME IS NULL OR (INSTR(TABLE_NAME, '/') = 0 AND INSTR(TABLE_NAME, '.') = 0);+----------+| COUNT(*) |+----------+|     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       (SELECT COUNT(*) FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE       WHERE TABLE_NAME IS NULL OR (INSTR(TABLE_NAME, '/') = 0 AND INSTR(TABLE_NAME, '.') = 0)       ) AS system_pages,       (       SELECT COUNT(*)       FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE       ) AS total_pages,       (       SELECT ROUND((system_pages/total_pages) * 100)       ) AS system_page_percentage;+--------------+-------------+------------------------+| system_pages | total_pages | system_page_percentage |+--------------+-------------+------------------------+|          295 |        8192 |                      4 |+--------------+-------------+------------------------+

The type of system data in the buffer pool can be determined by querying thePAGE_TYPE value. For example, the following query returns eight distinctPAGE_TYPE values among the pages that contain system data:

mysql> SELECT DISTINCT PAGE_TYPE FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE       WHERE TABLE_NAME IS NULL OR (INSTR(TABLE_NAME, '/') = 0 AND INSTR(TABLE_NAME, '.') = 0);+-------------------+| PAGE_TYPE         |+-------------------+| SYSTEM            || IBUF_BITMAP       || UNKNOWN           || FILE_SPACE_HEADER || INODE             || UNDO_LOG          || ALLOCATED         |+-------------------+

This query provides an approximate count of pages containing user data by counting pages where theTABLE_NAME value isNOT NULL andNOT LIKE '%INNODB_TABLES%'.

mysql> SELECT COUNT(*) FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE       WHERE TABLE_NAME IS NOT NULL AND TABLE_NAME NOT LIKE '%INNODB_TABLES%';+----------+| COUNT(*) |+----------+|     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.

mysql> SELECT       (SELECT COUNT(*) FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE       WHERE TABLE_NAME IS NOT NULL AND (INSTR(TABLE_NAME, '/') > 0 OR INSTR(TABLE_NAME, '.') > 0)       ) AS user_pages,       (       SELECT COUNT(*)       FROM information_schema.INNODB_BUFFER_PAGE       ) AS total_pages,       (       SELECT ROUND((user_pages/total_pages) * 100)       ) AS user_page_percentage;+------------+-------------+----------------------+| user_pages | total_pages | user_page_percentage |+------------+-------------+----------------------+|       7897 |        8192 |                   96 |+------------+-------------+----------------------+

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

mysql> SELECT DISTINCT TABLE_NAME FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE       WHERE TABLE_NAME IS NOT NULL AND (INSTR(TABLE_NAME, '/') > 0 OR INSTR(TABLE_NAME, '.') > 0)       AND TABLE_NAME NOT LIKE '`mysql`.`innodb_%';+-------------------------+| TABLE_NAME              |+-------------------------+| `employees`.`salaries`  || `employees`.`employees` |+-------------------------+

For information about index pages, query theINDEX_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 theemp_no index that is defined on theemployees.salaries table:

mysql> SELECT INDEX_NAME, COUNT(*) AS Pages,ROUND(SUM(IF(COMPRESSED_SIZE = 0, @@GLOBAL.innodb_page_size, COMPRESSED_SIZE))/1024/1024)AS 'Total Data (MB)'FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGEWHERE INDEX_NAME='emp_no' AND TABLE_NAME = '`employees`.`salaries`';+------------+-------+-----------------+| INDEX_NAME | Pages | Total Data (MB) |+------------+-------+-----------------+| emp_no     |  1609 |              25 |+------------+-------+-----------------+

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

mysql> SELECT INDEX_NAME, COUNT(*) AS Pages,       ROUND(SUM(IF(COMPRESSED_SIZE = 0, @@GLOBAL.innodb_page_size, COMPRESSED_SIZE))/1024/1024)       AS 'Total Data (MB)'       FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE       WHERE TABLE_NAME = '`employees`.`salaries`'       GROUP BY INDEX_NAME;+------------+-------+-----------------+| INDEX_NAME | Pages | Total Data (MB) |+------------+-------+-----------------+| emp_no     |  1608 |              25 || PRIMARY    |  6086 |              95 |+------------+-------+-----------------+

TheINNODB_BUFFER_PAGE_LRU table holds information about the pages in theInnoDB 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 forINNODB_BUFFER_PAGE, except this table has anLRU_POSITION column instead of aBLOCK_ID column.

This query counts the number of positions at a specific location in the LRU list occupied by pages of theemployees.employees table.

mysql> SELECT COUNT(LRU_POSITION) FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE_LRU       WHERE TABLE_NAME='`employees`.`employees`' AND LRU_POSITION < 3072;+---------------------+| COUNT(LRU_POSITION) |+---------------------+|                 548 |+---------------------+

TheINNODB_BUFFER_POOL_STATS table provides information similar toSHOW ENGINE INNODB STATUS andInnoDB buffer pool status variables.

mysql> SELECT * FROM information_schema.INNODB_BUFFER_POOL_STATS \G*************************** 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: 0NOT_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 andInnoDB buffer pool status variable output is shown below, based on the same data set.

For more information aboutSHOW ENGINE INNODB STATUS output, seeSection 17.17.3, “InnoDB Standard Monitor and Lock Monitor Output”.

mysql> SHOW ENGINE INNODB STATUS\G...----------------------BUFFER POOL AND MEMORY----------------------Total large memory allocated 137428992Dictionary memory allocated 579084Buffer pool size   8192Free buffers       1Database pages     8173Old database pages 3014Modified db pages  0Pending reads 0Pending writes: LRU 0, flush list 0, single page 0Pages made young 15907, not young 38031010.00 youngs/s, 0.00 non-youngs/sPages read 3270, created 13176, written 151090.00 reads/s, 0.00 creates/s, 0.00 writes/sNo buffer pool page gets since the last printoutPages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/sLRU len: 8173, unzip_LRU len: 0I/O sum[0]:cur[0], unzip sum[0]:cur[0]...

For status variable descriptions, seeSection 7.1.10, “Server Status Variables”.

mysql> SHOW STATUS LIKE 'Innodb_buffer%';+---------------------------------------+-------------+| Variable_name                         | Value       |+---------------------------------------+-------------+| Innodb_buffer_pool_dump_status        | not started || Innodb_buffer_pool_load_status        | not started || Innodb_buffer_pool_resize_status      | not started || 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    |+---------------------------------------+-------------+