Misc. INFORMATION_SCHEMA Tables

Temporary tables

Only temporary tables that were explicitly created with CREATE TEMPORARY TABLE are shown, and not the ones created during query execution. The temporary tables that are created for ALTER TABLE execution are not listed in INFORMATION_SCHEMA.TEMPORARY_TABLES or GLOBAL_TEMPORARY_TABLES tables.
table INFORMATION_SCHEMA.GLOBAL_TEMPORARY_TABLES
Columns:
  • SESSION_IDMySQL connection id
  • TABLE_SCHEMA – Schema in which the temporary table is created
  • TABLE_NAME – Name of the temporary table
  • ENGINE – Engine of the temporary table
  • NAME – Internal name of the temporary table
  • TABLE_ROWS – Number of rows of the temporary table
  • AVG_ROW_LENGTH – Average row length of the temporary table
  • DATA_LENGTH – Size of the data (Bytes)
  • INDEX_LENGTH – Size of the indexes (Bytes)
  • CREATE_TIME – Date and time of creation of the temporary table
  • UPDATE_TIME – Date and time of the latest update of the temporary table

This table holds information on the temporary tables existing for all connections. You don’t need the SUPER privilege to query this table.

table INFORMATION_SCHEMA.TEMPORARY_TABLES
Columns:
  • SESSION_IDMySQL connection id
  • TABLE_SCHEMA – Schema in which the temporary table is created
  • TABLE_NAME – Name of the temporary table
  • ENGINE – Engine of the temporary table
  • NAME – Internal name of the temporary table
  • TABLE_ROWS – Number of rows of the temporary table
  • AVG_ROW_LENGTH – Average row length of the temporary table
  • DATA_LENGTH – Size of the data (Bytes)
  • INDEX_LENGTH – Size of the indexes (Bytes)
  • CREATE_TIME – Date and time of creation of the temporary table
  • UPDATE_TIME – Date and time of the latest update of the temporary table

This table holds information on the temporary tables existing for the running connection.

Buffer Pool Data Structure Tables

The following tables provide various information about the contents of the InnoDB buffer pool.

table INFORMATION_SCHEMA.INNODB_BUFFER_POOL_PAGES
Columns:
  • PAGE_TYPE – Type of the page. Possible values: index, undo_log, inode, ibuf_free_list, allocated, bitmap, sys, trx_sys, fsp_hdr, xdes, blob, zblob, zblob2, unknown
  • SPACE_ID – tablespace ID
  • PAGE_NO – page offset within its tablespace
  • LRU_POSITION – this field is always 0 and will be removed in a future Percona Server release
  • FIX_COUNT – reference count of a page. It is incremented every time the page is accessed by InnoDB, it is 0 if and only if the page is not currently being accessed.
  • FLUSH_TYPE – type of the last flush of the page (0:LRU 2:flush_list)

Example:

mysql> select * from information_schema.INNODB_BUFFER_POOL_PAGES LIMIT 20;
+-----------+----------+---------+--------------+-----------+------------+
| page_type | space_id | page_no | lru_position | fix_count | flush_type |
+-----------+----------+---------+--------------+-----------+------------+
| allocated |        0 |       7 |            0 |         0 |          2 |
| allocated |        0 |       1 |            0 |         0 |          0 |
| allocated |        0 |       3 |            0 |         0 |          0 |
| inode     |        0 |       2 |            0 |         0 |          2 |
| index     |        0 |       4 |            0 |         0 |          2 |
| index     |        0 |      11 |            0 |         0 |          0 |
| index     |        0 |   12956 |            0 |         0 |          0 |
| allocated |        0 |       5 |            0 |         0 |          2 |
| allocated |        0 |       6 |            0 |         0 |          2 |
| undo_log  |        0 |      51 |            0 |         0 |          2 |
| undo_log  |        0 |      52 |            0 |         0 |          2 |
| index     |        0 |       8 |            0 |         0 |          0 |
| index     |        0 |     288 |            0 |         0 |          0 |
| index     |        0 |     290 |            0 |         0 |          2 |
| index     |        0 |     304 |            0 |         0 |          0 |
| allocated |        0 |       0 |            0 |         0 |          2 |
| index     |        0 |      10 |            0 |         0 |          0 |
| index     |        0 |   12973 |            0 |         0 |          0 |
| index     |        0 |       9 |            0 |         0 |          2 |
| index     |        0 |      12 |            0 |         0 |          0 |
+-----------+----------+---------+--------------+-----------+------------+
20 rows in set (0.81 sec)

This table shows the characteristics of the allocated pages in buffer pool and current state of them.

table INFORMATION_SCHEMA.INNODB_BUFFER_POOL_PAGES_INDEX
Columns:
  • index_id – index name
  • space_id – tablespace ID
  • page_no – page offset within its tablespace
  • n_recs – number of user records on page
  • data_size – sum of the sizes of the records in page
  • hashed – the block is in adaptive hash index (1) or not (0)
  • access_time – time of the last access to this page.
  • modified – modified since loaded (1) or not (0)
  • dirty – modified since last flushed (1) or not (0)
  • old – is old blocks in the LRU list (1) or not (0)
  • lru_position – page position in the LRU list
  • fix_count – reference count of a page. It is incremented every time the page is accessed by InnoDB, it is 0 if and only if the page is not currently being accessed.
  • flush_type – type of the last flush of the page (0:LRU 2:flush_list)

Example:

+----------+----------+---------+--------+-----------+--------+-------------+----------+-------+-----+--------------+-----------+------------+
| index_id | space_id | page_no | n_recs | data_size | hashed | access_time | modified | dirty | old | lru_position | fix_count | flush_type |
+----------+----------+---------+--------+-----------+--------+-------------+----------+-------+-----+--------------+-----------+------------+
|       39 |        0 |    5787 |    468 |     14976 |      1 |  2636182517 |        1 |     0 |   1 |            0 |         0 |          2 |
|       40 |        0 |    5647 |   1300 |     15600 |      1 |  2636182517 |        1 |     0 |   1 |            0 |         0 |          2 |
|       39 |        0 |    5786 |    468 |     14976 |      1 |  2636182516 |        1 |     0 |   1 |            0 |         0 |          2 |
|       40 |        0 |    6938 |   1300 |     15600 |      1 |  2636193968 |        1 |     0 |   1 |            0 |         0 |          2 |
|       39 |        0 |    5785 |    468 |     14976 |      1 |  2636182514 |        1 |     0 |   1 |            0 |         0 |          2 |
|       39 |        0 |    5784 |    468 |     14976 |      1 |  2636182512 |        1 |     0 |   1 |            0 |         0 |          2 |
|       40 |        0 |    5646 |   1300 |     15600 |      1 |  2636182511 |        1 |     0 |   1 |            0 |         0 |          2 |
|       39 |        0 |    7203 |    468 |     14976 |      1 |  2636193967 |        1 |     0 |   1 |            0 |         0 |          2 |
|       39 |        0 |    5783 |    468 |     14976 |      1 |  2636182507 |        1 |     0 |   1 |            0 |         0 |          2 |
|       39 |        0 |    5782 |    468 |     14976 |      1 |  2636182506 |        1 |     0 |   1 |            0 |         0 |          2 |
+----------+----------+---------+--------+-----------+--------+-------------+----------+-------+-----+--------------+-----------+------------+

This table shows information about the index pages located in the buffer pool.

table INFORMATION_SCHEMA.INNODB_BUFFER_POOL_PAGES_BLOB
Columns:
  • space_id – tablespace id
  • page_no – page offset within its tablespace
  • compressed – contains compressed data (1) or not (0)
  • part_len – data length in the page
  • next_page_no – page number of the next data
  • lru_position – page position in the LRU list
  • fix_count – reference count of a page. It is incremented every time the page is accessed by InnoDB, it is 0 if and only if the page is not currently being accessed.
  • flush_type – type of the last flush of the page (0:LRU 2:flush_list)

Example:

mysql> select * from information_schema.INNODB_BUFFER_POOL_PAGES_BLOB LIMIT 20;
+----------+---------+------------+----------+--------------+--------------+-----------+------------+
| space_id | page_no | compressed | part_len | next_page_no | lru_position | fix_count | flush_type |
+----------+---------+------------+----------+--------------+--------------+-----------+------------+
|     1748 |     111 |          0 |    10137 |            0 |          263 |         0 |          2 |
|     1748 |     307 |          0 |     5210 |            0 |         1084 |         0 |          2 |
|     1748 |    1329 |          0 |     6146 |            0 |         4244 |         0 |          2 |
|     1748 |    1330 |          0 |    11475 |            0 |         4245 |         0 |          2 |
|     1748 |    1345 |          0 |     5550 |            0 |         4247 |         0 |          2 |
|     1748 |    1346 |          0 |     7597 |            0 |         4248 |         0 |          2 |
|     1748 |    3105 |          0 |     6716 |            0 |         8919 |         0 |          2 |
|     1748 |    3213 |          0 |     8170 |            0 |         9390 |         0 |          2 |
|     1748 |    6142 |          0 |     5648 |            0 |        19638 |         0 |          2 |
|     1748 |    7387 |          0 |    10634 |            0 |        24191 |         0 |          2 |
|     1748 |    7426 |          0 |     5355 |            0 |        24194 |         0 |          2 |
|     1748 |    7489 |          0 |    16330 |         7489 |        24196 |         0 |          2 |
|     1748 |    7490 |          0 |     7126 |            0 |        24197 |         0 |          2 |
|     1748 |    7657 |          0 |    13571 |            0 |        24681 |         0 |          2 |
|     1748 |    7840 |          0 |    11208 |            0 |        25737 |         0 |          2 |
|     1748 |    9599 |          0 |    11882 |            0 |        31989 |         0 |          2 |
|     1748 |   11719 |          0 |     7367 |            0 |        40466 |         0 |          2 |
|     1748 |   12051 |          0 |    11049 |            0 |        41441 |         0 |          2 |
|     1748 |   12052 |          0 |    16330 |        12052 |        41442 |         0 |          2 |
|     1748 |   12053 |          0 |     2674 |            0 |        41443 |         0 |          2 |
+----------+---------+------------+----------+--------------+--------------+-----------+------------+
20 rows in set (0.05 sec)

This table shows information from blob pages located in buffer pool.

© Copyright Percona LLC and/or its affiliates 2009-2014.
Except where otherwise noted, this documentation is licensed under the following license:
CC Attribution-ShareAlike 2.0 Generic
Created using Sphinx 1.2.2.
This documentation is developed in Launchpad as part of the Percona Server source code.
If you spotted innacuracies, errors, don't understood it or you think something is missing or should be improved, please file a bug.