MySQL 5.6 Information_Schema確認
Chapter 21 INFORMATION_SCHEMA Tables
21.29 INFORMATION_SCHEMA Tables for InnoDB

mysql> select database();
+--------------------+
| database()         |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)

mysql> select @@version;
+------------+
| @@version  |
+------------+
| 5.6.19-log |
+------------+
1 row in set (0.00 sec)

mysql>

mysql> show tables;
+---------------------------------------+
| Tables_in_information_schema          |
+---------------------------------------+
| CHARACTER_SETS                        |	SHOW CHARACTER SET  (information about available character sets.)
| COLLATIONS                            |	SHOW COLLATION   (information about collations for each character set.)
| COLLATION_CHARACTER_SET_APPLICABILITY |	(Indicates what character set is applicable for what collation. )
| COLUMNS                               |	SHOW COLUMNS FROM tbl_name [FROM db_name] (Provides information about columns in tables.)
| COLUMN_PRIVILEGES                     |	SHOW GRANTS ...  (provides information about column privileges.)
| ENGINES                               |	(provides information about storage engines. )
| EVENTS                                |	(provides information about scheduled events.)
| FILES                                 |	(provides information about the files in which MySQL NDB Disk Data tables are stored. )
| GLOBAL_STATUS                         |	SHOW GLOBAL STATUS and SHOW SESSION STATUS (provide information about server status variables)
| GLOBAL_VARIABLES                      |	SHOW GLOBAL VARIABLES and SHOW SESSION VARIABLES(provide information about server status variables.)
| KEY_COLUMN_USAGE                      |	(describes which key columns have constraints. )
| OPTIMIZER_TRACE                       |	(provides information produced by the optimizer tracing capability.)
| PARAMETERS                            |	(provides information about stored procedure and function parameters)
| PARTITIONS                            |	(provides information about table partitions.)
| PLUGINS                               |	SHOW PLUGINS  (provides information about server plugins. )
| PROCESSLIST                           |	SHOW FULL PROCESSLIST (provides information about which threads are running.)
| PROFILING                             |	(provides statement profiling information.)
| REFERENTIAL_CONSTRAINTS               |	(provides information about foreign keys. )
| ROUTINES                              |	(provides information about stored routines (both procedures and functions).)
| SCHEMATA                              |	SHOW DATABASES ( provides information about databases. )
| SCHEMA_PRIVILEGES                     |	(provides information about schema (database) privileges. )
| SESSION_STATUS                        |	SHOW GLOBAL VARIABLES and SHOW SESSION VARIABLES (provide information about server status variables.)
| SESSION_VARIABLES                     |	SHOW GLOBAL VARIABLES and SHOW SESSION VARIABLES (provide information about server status variables.)
| STATISTICS                            |	show index from tbl_name from db_name;(provides information about table indexes. )
| TABLES                                |	SHOW TABLES from db_name (provides information about tables in databases. )
| TABLESPACES                           |	(provides information about active tablespaces. )
| TABLE_CONSTRAINTS                     |	(describes which tables have constraints. )
| TABLE_PRIVILEGES                      |	SHOW GRANTS FOR user@hostname (provides information about table privileges.)
| TRIGGERS                              |	(provides information about triggers.)
| USER_PRIVILEGES                       |	(provides information about global privileges)
| VIEWS                                 |	(provides information about views in databases)
| INNODB_LOCKS                          |	(contains information about every transaction currently executing inside InnoDB.)
| INNODB_TRX                            |	(contains information about each lock that an InnoDB transaction has requested but not yet acquired.)
| INNODB_SYS_DATAFILES                  |	(table stores InnoDB datafile path information)
| INNODB_LOCK_WAITS                     |	(table contains one or more rows for each blocked InnoDB transaction)
| INNODB_SYS_TABLESTATS                 |	(status information about performance statistics for InnoDB tables)
| INNODB_CMP                            |	(tables contain status information on operations related to compressed InnoDB tables. )
| INNODB_METRICS                        |	(table presents a wide variety of InnoDB performance information,)
| INNODB_CMP_RESET                      |	(contain status information on operations related to compressed InnoDB tables and indexes)
| INNODB_CMP_PER_INDEX                  |	(contain status information on operations related to compressed InnoDB tables and indexes)
| INNODB_CMPMEM_RESET                   |	(contain status information on compressed pages within the InnoDB buffer pool.)
| INNODB_FT_DELETED                     |	(records rows that are deleted from the FULLTEXT index for an InnoDB table.)
| INNODB_BUFFER_PAGE_LRU                |	(holds information about the pages in the InnoDB buffer pool)
| INNODB_SYS_FOREIGN                    |	(provides status information about InnoDB foreign keys, equivalent to the information from the SYS_FOREIGN table)
| INNODB_SYS_COLUMNS                    |	(provides status information about InnoDB table columns, equivalent to the information)
| INNODB_SYS_INDEXES                    |	(provides status information about InnoDB indexes, equivalent to the information)
| INNODB_FT_DEFAULT_STOPWORD            |	(table holds a list of stopwords that are used by default when creating a FULLTEXT index on an InnoDB table.)
| INNODB_SYS_FIELDS                     |	(provides status information about the key columns (fields) of InnoDB indexes)
| INNODB_CMP_PER_INDEX_RESET            |	(contain status information on operations related to compressed InnoDB tables and indexes)
| INNODB_BUFFER_PAGE                    |	(table holds information about each page in the InnoDB buffer pool.)
| INNODB_CMPMEM                         |	(tables contain status information on compressed pages within the InnoDB buffer pool.)
| INNODB_FT_INDEX_TABLE                 |	(displays information about the inverted index used to process text searches against the FULLTEXT index of an InnoDB table.)
| INNODB_FT_BEING_DELETED               |	(temporary work table while document IDs in the INNODB_FT_DELETED table are being removed from an InnoDB FULLTEXT index during an OPTIMIZE TABLE operation.)
| INNODB_SYS_TABLESPACES                |	(stores information about InnoDB tablespaces)
| INNODB_FT_INDEX_CACHE                 |	(displays token information about newly inserted rows in a FULLTEXT index for an InnoDB table.)
| INNODB_SYS_FOREIGN_COLS               |	(provides status information about the columns of InnoDB foreign keys)
| INNODB_SYS_TABLES                     |	(provides status information about performance statistics)
| INNODB_BUFFER_POOL_STATS              |	 SHOW ENGINE INNODB STATUS (provides much of the same buffer pool information)
| INNODB_FT_CONFIG                      |	(displays metadata about the FULLTEXT index and associated processing for an InnoDB table. )
+---------------------------------------+	
59 rows in set (0.00 sec)

mysql>

InnoDBのファイルパス確認出来る。
こちらは、Windowsの場合。

mysql> select * from INNODB_SYS_DATAFILES;
+-------+----------------------------------+
| SPACE | PATH                             |
+-------+----------------------------------+
|     1 | .\mysql\innodb_table_stats.ibd   |
|     2 | .\mysql\innodb_index_stats.ibd   |
|     3 | .\mysql\slave_relay_log_info.ibd |
|     4 | .\mysql\slave_master_info.ibd    |
|     5 | .\mysql\slave_worker_info.ibd    |
|     7 | .\sakila\address.ibd             |
|     8 | .\sakila\category.ibd            |
|     9 | .\sakila\city.ibd                |
|    10 | .\sakila\country.ibd             |
|    11 | .\sakila\customer.ibd            |
|    12 | .\sakila\film.ibd                |
|    13 | .\sakila\film_actor.ibd          |
|    14 | .\sakila\film_category.ibd       |
|    15 | .\sakila\inventory.ibd           |
|    16 | .\sakila\language.ibd            |
|    17 | .\sakila\payment.ibd             |
|    18 | .\sakila\rental.ibd              |
|    19 | .\sakila\staff.ibd               |
|    20 | .\sakila\store.ibd               |
|    22 | .\test\lck.ibd                   |
|    24 | .\test\montable.ibd              |
|    28 | .\test\t.ibd                     |
|    30 | .\sakila\actor.ibd               |
|    38 | .\copy_test\language.ibd         |
|    39 | .\copy_test\lck.ibd              |
|    40 | .\copy_test\montable.ibd         |
|    41 | .\copy_test\t.ibd                |
|    45 | .\test\language.ibd              |
+-------+----------------------------------+
28 rows in set (0.01 sec)

mysql>

These statistics represent low-level information used by the MySQL optimizer to calculate which index to use when querying an InnoDB table.
This information is derived from in-memory data structures rather than corresponding to data stored on disk.

mysql> select * from INNODB_SYS_TABLESTATS;
+----------+----------------------------+-------------------+----------+------------------+------------------+------------------+---------+-----------+
| TABLE_ID | NAME                       | STATS_INITIALIZED | NUM_ROWS | CLUST_INDEX_SIZE | OTHER_INDEX_SIZE | MODIFIED_COUNTER | AUTOINC | REF_COUNT |
+----------+----------------------------+-------------------+----------+------------------+------------------+------------------+---------+-----------+
|       14 | SYS_DATAFILES              | Uninitialized     |        0 |                0 |                0 |                0 |       0 |         0 |
|       11 | SYS_FOREIGN                | Uninitialized     |        0 |                0 |                0 |                0 |       0 |         0 |
|       12 | SYS_FOREIGN_COLS           | Uninitialized     |        0 |                0 |                0 |                0 |       0 |         0 |
|       13 | SYS_TABLESPACES            | Uninitialized     |        0 |                0 |                0 |                0 |       0 |         0 |
|       52 | copy_test/language         | Uninitialized     |        0 |                0 |                0 |                0 |       0 |         0 |
|       53 | copy_test/lck              | Uninitialized     |        0 |                0 |                0 |                0 |       0 |         0 |
|       54 | copy_test/montable         | Uninitialized     |        0 |                0 |                0 |                0 |       0 |         0 |
|       55 | copy_test/t                | Uninitialized     |        0 |                0 |                0 |                0 |       0 |         0 |
|       16 | mysql/innodb_index_stats   | Uninitialized     |        0 |                0 |                0 |                0 |       0 |         0 |
|       15 | mysql/innodb_table_stats   | Uninitialized     |        0 |                0 |                0 |                0 |       0 |         0 |
|       18 | mysql/slave_master_info    | Initialized       |        0 |                1 |                0 |                0 |       0 |         1 |
|       17 | mysql/slave_relay_log_info | Initialized       |        0 |                1 |                0 |                0 |       0 |         1 |
|       19 | mysql/slave_worker_info    | Initialized       |        0 |                1 |                0 |                0 |       0 |         1 |
|       44 | sakila/actor               | Uninitialized     |        0 |                0 |                0 |                0 |       0 |         0 |
|       21 | sakila/address             | Uninitialized     |        0 |                0 |                0 |                0 |       0 |         0 |
|       22 | sakila/category            | Uninitialized     |        0 |                0 |                0 |                0 |       0 |         0 |
|       23 | sakila/city                | Uninitialized     |        0 |                0 |                0 |                0 |       0 |         0 |
|       24 | sakila/country             | Uninitialized     |        0 |                0 |                0 |                0 |       0 |         0 |
|       25 | sakila/customer            | Uninitialized     |        0 |                0 |                0 |                0 |       0 |         0 |
|       26 | sakila/film                | Uninitialized     |        0 |                0 |                0 |                0 |       0 |         0 |
|       27 | sakila/film_actor          | Uninitialized     |        0 |                0 |                0 |                0 |       0 |         0 |
|       28 | sakila/film_category       | Uninitialized     |        0 |                0 |                0 |                0 |       0 |         0 |
|       29 | sakila/inventory           | Uninitialized     |        0 |                0 |                0 |                0 |       0 |         0 |
|       30 | sakila/language            | Uninitialized     |        0 |                0 |                0 |                0 |       0 |         0 |
|       31 | sakila/payment             | Uninitialized     |        0 |                0 |                0 |                0 |       0 |         0 |
|       32 | sakila/rental              | Uninitialized     |        0 |                0 |                0 |                0 |       0 |         0 |
|       33 | sakila/staff               | Uninitialized     |        0 |                0 |                0 |                0 |       0 |         0 |
|       34 | sakila/store               | Uninitialized     |        0 |                0 |                0 |                0 |       0 |         0 |
|       59 | test/language              | Uninitialized     |        0 |                0 |                0 |                0 |       0 |         0 |
|       36 | test/lck                   | Uninitialized     |        0 |                0 |                0 |                0 |       0 |         0 |
|       38 | test/montable              | Uninitialized     |        0 |                0 |                0 |                0 |       0 |         0 |
|       42 | test/t                     | Uninitialized     |        0 |                0 |                0 |                0 |       0 |         0 |
+----------+----------------------------+-------------------+----------+------------------+------------------+------------------+---------+-----------+
32 rows in set (0.00 sec)

mysql>

This INFORMATION_SCHEMA table presents a wide variety of InnoDB performance information,
complementing the specific focus areas of the PERFORMANCE_SCHEMA tables for InnoDB.
With simple queries, you can check the overall health of the system.
With more detailed queries, you can diagnose issues such as performance bottlenecks,
resource shortages, and application issues.

詳細は、此方のURLを確認。
21.29.19 The INFORMATION_SCHEMA INNODB_METRICS Table
http://dev.mysql.com/doc/refman/5.6/en/innodb-metrics-table.html

mysql> select * from INNODB_METRICS where count <> 0;
+-----------------------------+---------------------+-----------+-----------+-----------+-----------------------+-------------+-----------------+-----------------+-----------------+---------------------+---------------+--------------+------------+---------+----------------+--------------------------------------------------------------------------------------------------------+
| NAME                        | SUBSYSTEM           | COUNT     | MAX_COUNT | MIN_COUNT | AVG_COUNT             | COUNT_RESET | MAX_COUNT_RESET | MIN_COUNT_RESET | AVG_COUNT_RESET | TIME_ENABLED        | TIME_DISABLED | TIME_ELAPSED | TIME_RESET | STATUS  | TYPE           | COMMENT                                                                                                |
+-----------------------------+---------------------+-----------+-----------+-----------+-----------------------+-------------+-----------------+-----------------+-----------------+---------------------+---------------+--------------+------------+---------+----------------+--------------------------------------------------------------------------------------------------------+
| metadata_mem_pool_size      | metadata            |   6291456 |   6291456 |   6291456 |                  NULL |     6291456 |         6291456 |         6291456 |            NULL | 2014-08-07 10:51:53 | NULL          |         2918 | NULL       | enabled | value          | Size of a memory pool InnoDB uses to store data dictionary and internal data structures in bytes       |
| buffer_pool_size            | server              | 238026752 | 238026752 | 238026752 |                  NULL |   238026752 |       238026752 |       238026752 |            NULL | 2014-08-07 10:51:53 | NULL          |         2918 | NULL       | enabled | value          | Server buffer pool size (all buffer pools) in bytes                                                    |
| buffer_pool_reads           | buffer              |       486 |       486 |      NULL |   0.16655243317340646 |         486 |             486 |            NULL |            NULL | 2014-08-07 10:51:53 | NULL          |         2918 | NULL       | enabled | status_counter | Number of reads directly from disk (innodb_buffer_pool_reads)                                          |
| buffer_pool_read_requests   | buffer              |      6846 |      6846 |      NULL |    2.3461274845784783 |        6846 |            6846 |            NULL |            NULL | 2014-08-07 10:51:53 | NULL          |         2918 | NULL       | enabled | status_counter | Number of logical read requests (innodb_buffer_pool_read_requests)                                     |
| buffer_pool_write_requests  | buffer              |         1 |         1 |      NULL | 0.0003427004797806717 |           1 |               1 |            NULL |            NULL | 2014-08-07 10:51:53 | NULL          |         2918 | NULL       | enabled | status_counter | Number of write requests (innodb_buffer_pool_write_requests)                                           |
| buffer_pool_pages_total     | buffer              |     14528 |     14528 |     14528 |                  NULL |       14528 |           14528 |           14528 |            NULL | 2014-08-07 10:51:53 | NULL          |         2918 | NULL       | enabled | value          | Total buffer pool size in pages (innodb_buffer_pool_pages_total)                                       |
| buffer_pool_pages_misc      | buffer              |         1 |         1 |         1 |                  NULL |           1 |               1 |               1 |            NULL | 2014-08-07 10:51:53 | NULL          |         2918 | NULL       | enabled | value          | Buffer pages for misc use such as row locks or the adaptive hash index (innodb_buffer_pool_pages_misc) |
| buffer_pool_pages_data      | buffer              |       485 |       485 |       485 |                  NULL |         485 |             485 |             485 |            NULL | 2014-08-07 10:51:53 | NULL          |         2918 | NULL       | enabled | value          | Buffer pages containing data (innodb_buffer_pool_pages_data)                                           |
| buffer_pool_bytes_data      | buffer              |   7946240 |   7946240 |   7946240 |                  NULL |     7946240 |         7946240 |         7946240 |            NULL | 2014-08-07 10:51:53 | NULL          |         2918 | NULL       | enabled | value          | Buffer bytes containing data (innodb_buffer_pool_bytes_data)                                           |
| buffer_pool_pages_free      | buffer              |     14042 |     14042 |     14042 |                  NULL |       14042 |           14042 |           14042 |            NULL | 2014-08-07 10:51:53 | NULL          |         2918 | NULL       | enabled | value          | Buffer pages currently free (innodb_buffer_pool_pages_free)                                            |
| buffer_pages_written        | buffer              |         1 |         1 |      NULL | 0.0003427004797806717 |           1 |               1 |            NULL |            NULL | 2014-08-07 10:51:53 | NULL          |         2918 | NULL       | enabled | status_counter | Number of pages written (innodb_pages_written)                                                         |
| buffer_pages_read           | buffer              |       485 |       485 |      NULL |   0.16620973269362577 |         485 |             485 |            NULL |            NULL | 2014-08-07 10:51:53 | NULL          |         2918 | NULL       | enabled | status_counter | Number of pages read (innodb_pages_read)                                                               |
| buffer_data_reads           | buffer              |   8032256 |   8032256 |      NULL |     2752.657984921179 |     8032256 |         8032256 |            NULL |            NULL | 2014-08-07 10:51:53 | NULL          |         2918 | NULL       | enabled | status_counter | Amount of data read in bytes (innodb_data_reads)                                                       |
| buffer_data_written         | buffer              |     34304 |     34304 |      NULL |    11.755997258396162 |       34304 |           34304 |            NULL |            NULL | 2014-08-07 10:51:53 | NULL          |         2918 | NULL       | enabled | status_counter | Amount of data written in bytes (innodb_data_written)                                                  |
| os_data_reads               | os                  |       501 |       501 |      NULL |    0.1716929403701165 |         501 |             501 |            NULL |            NULL | 2014-08-07 10:51:53 | NULL          |         2918 | NULL       | enabled | status_counter | Number of reads initiated (innodb_data_reads)                                                          |
| os_data_writes              | os                  |         5 |         5 |      NULL | 0.0017135023989033585 |           5 |               5 |            NULL |            NULL | 2014-08-07 10:51:53 | NULL          |         2918 | NULL       | enabled | status_counter | Number of writes initiated (innodb_data_writes)                                                        |
| os_data_fsyncs              | os                  |         5 |         5 |      NULL | 0.0017135023989033585 |           5 |               5 |            NULL |            NULL | 2014-08-07 10:51:53 | NULL          |         2918 | NULL       | enabled | status_counter | Number of fsync() calls (innodb_data_fsyncs)                                                           |
| os_log_bytes_written        | os                  |       512 |       512 |      NULL |   0.17546264564770392 |         512 |             512 |            NULL |            NULL | 2014-08-07 10:51:53 | NULL          |         2918 | NULL       | enabled | status_counter | Bytes of log written (innodb_os_log_written)                                                           |
| os_log_fsyncs               | os                  |         3 |         3 |      NULL |  0.001028101439342015 |           3 |               3 |            NULL |            NULL | 2014-08-07 10:51:53 | NULL          |         2918 | NULL       | enabled | status_counter | Number of fsync log writes (innodb_os_log_fsyncs)                                                      |
| trx_rseg_history_len        | transaction         |       295 |       295 |       295 |                  NULL |         295 |             295 |             295 |            NULL | 2014-08-07 10:51:53 | NULL          |         2918 | NULL       | enabled | value          | Length of the TRX_RSEG_HISTORY list                                                                    |
| log_writes                  | recovery            |         1 |         1 |      NULL | 0.0003427004797806717 |           1 |               1 |            NULL |            NULL | 2014-08-07 10:51:53 | NULL          |         2918 | NULL       | enabled | status_counter | Number of log writes (innodb_log_writes)                                                               |
| adaptive_hash_searches      | adaptive_hash_index |       110 |       110 |      NULL |   0.03769705277587389 |         110 |             110 |            NULL |            NULL | 2014-08-07 10:51:53 | NULL          |         2918 | NULL       | enabled | status_counter | Number of successful searches using Adaptive Hash Index                                                |
| file_num_open_files         | file_system         |         8 |         8 |         8 |                  NULL |           8 |               8 |               8 |            NULL | 2014-08-07 10:51:53 | NULL          |         2918 | NULL       | enabled | value          | Number of files currently open (innodb_num_open_files)                                                 |
| ibuf_size                   | change_buffer       |         1 |         1 |      NULL | 0.0003427004797806717 |           1 |               1 |            NULL |            NULL | 2014-08-07 10:51:53 | NULL          |         2918 | NULL       | enabled | status_counter | Change buffer size in pages                                                                            |
| innodb_activity_count       | server              |         3 |         3 |      NULL |  0.001028101439342015 |           3 |               3 |            NULL |            NULL | 2014-08-07 10:51:53 | NULL          |         2918 | NULL       | enabled | status_counter | Current server activity count                                                                          |
| innodb_dblwr_writes         | server              |         1 |         1 |      NULL | 0.0003427004797806717 |           1 |               1 |            NULL |            NULL | 2014-08-07 10:51:53 | NULL          |         2918 | NULL       | enabled | status_counter | Number of doublewrite operations that have been performed (innodb_dblwr_writes)                        |
| innodb_dblwr_pages_written  | server              |         1 |         1 |      NULL | 0.0003427004797806717 |           1 |               1 |            NULL |            NULL | 2014-08-07 10:51:53 | NULL          |         2918 | NULL       | enabled | status_counter | Number of pages that have been written for doublewrite operations (innodb_dblwr_pages_written)         |
| innodb_page_size            | server              |     16384 |     16384 |     16384 |                  NULL |       16384 |           16384 |           16384 |            NULL | 2014-08-07 10:51:53 | NULL          |         2918 | NULL       | enabled | value          | InnoDB page size in bytes (innodb_page_size)                                                           |
| innodb_rwlock_s_spin_waits  | server              |         2 |         2 |      NULL | 0.0006854009595613434 |           2 |               2 |            NULL |            NULL | 2014-08-07 10:51:53 | NULL          |         2918 | NULL       | enabled | status_counter | Number of rwlock spin waits due to shared latch request                                                |
| innodb_rwlock_s_spin_rounds | server              |        60 |        60 |      NULL |    0.0205620287868403 |          60 |              60 |            NULL |            NULL | 2014-08-07 10:51:53 | NULL          |         2918 | NULL       | enabled | status_counter | Number of rwlock spin loop rounds due to shared latch request                                          |
| innodb_rwlock_s_os_waits    | server              |         2 |         2 |      NULL | 0.0006854009595613434 |           2 |               2 |            NULL |            NULL | 2014-08-07 10:51:53 | NULL          |         2918 | NULL       | enabled | status_counter | Number of OS waits due to shared latch request                                                         |
+-----------------------------+---------------------+-----------+-----------+-----------+-----------------------+-------------+-----------------+-----------------+-----------------+---------------------+---------------+--------------+------------+---------+----------------+--------------------------------------------------------------------------------------------------------+
31 rows in set (0.00 sec)

mysql>

INNODB_BUFFER_PAGE_LRU table holds information about the pages in the InnoDB 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.


mysql> select PAGE_TYPE,count(*) 'Numbers' from INNODB_BUFFER_PAGE_LRU group by PAGE_TYPE order by count(*) desc;
+-------------------+---------+
| PAGE_TYPE         | Numbers |
+-------------------+---------+
| UNDO_LOG          |     324 |
| SYSTEM            |     130 |
| INDEX             |      18 |
| IBUF_BITMAP       |       6 |
| INODE             |       4 |
| FILE_SPACE_HEADER |       1 |
| TRX_SYSTEM        |       1 |
| IBUF_INDEX        |       1 |
+-------------------+---------+
8 rows in set (0.01 sec)

mysql>

INNODB_SYS_FOREIGN table provides status information about InnoDB foreign keys,
equivalent to the information from the SYS_FOREIGN table in the InnoDB data dictionary.


mysql> select * from INNODB_SYS_FOREIGN;
+----------------------------------+----------------------+------------------+--------+------+
| ID                               | FOR_NAME             | REF_NAME         | N_COLS | TYPE |
+----------------------------------+----------------------+------------------+--------+------+
| sakila/fk_address_city           | sakila/address       | sakila/city      |      1 |    4 |
| sakila/fk_city_country           | sakila/city          | sakila/country   |      1 |    4 |
| sakila/fk_customer_address       | sakila/customer      | sakila/address   |      1 |    4 |
| sakila/fk_customer_store         | sakila/customer      | sakila/store     |      1 |    4 |
| sakila/fk_film_actor_actor       | sakila/film_actor    | sakila/actor     |      1 |    4 |
| sakila/fk_film_actor_film        | sakila/film_actor    | sakila/film      |      1 |    4 |
| sakila/fk_film_category_category | sakila/film_category | sakila/category  |      1 |    4 |
| sakila/fk_film_category_film     | sakila/film_category | sakila/film      |      1 |    4 |
| sakila/fk_film_language          | sakila/film          | sakila/language  |      1 |    4 |
| sakila/fk_film_language_original | sakila/film          | sakila/language  |      1 |    4 |
| sakila/fk_inventory_film         | sakila/inventory     | sakila/film      |      1 |    4 |
| sakila/fk_inventory_store        | sakila/inventory     | sakila/store     |      1 |    4 |
| sakila/fk_payment_customer       | sakila/payment       | sakila/customer  |      1 |    4 |
| sakila/fk_payment_rental         | sakila/payment       | sakila/rental    |      1 |    6 |
| sakila/fk_payment_staff          | sakila/payment       | sakila/staff     |      1 |    4 |
| sakila/fk_rental_customer        | sakila/rental        | sakila/customer  |      1 |    4 |
| sakila/fk_rental_inventory       | sakila/rental        | sakila/inventory |      1 |    4 |
| sakila/fk_rental_staff           | sakila/rental        | sakila/staff     |      1 |    4 |
| sakila/fk_staff_address          | sakila/staff         | sakila/address   |      1 |    4 |
| sakila/fk_staff_store            | sakila/staff         | sakila/store     |      1 |    4 |
| sakila/fk_store_address          | sakila/store         | sakila/address   |      1 |    4 |
| sakila/fk_store_staff            | sakila/store         | sakila/staff     |      1 |    4 |
+----------------------------------+----------------------+------------------+--------+------+
22 rows in set (0.00 sec)

mysql>

The INNODB_FT_DEFAULT_STOPWORD table holds a list of stopwords that are used by
default when creating a FULLTEXT index on an InnoDB table. For information about the
default InnoDB stopword list and how to define your own stopword lists.

stopword
In a FULLTEXT index, a word that is considered common or trivial enough that
it is omitted from the search index and ignored in search queries.
Different configuration settings control stopword processing for InnoDB and MyISAM tables.

mysql> select * from INNODB_FT_DEFAULT_STOPWORD;
+-------+
| value |
+-------+
| a     |
| about |
| an    |
| are   |
| as    |
| at    |
| be    |
| by    |
| com   |
| de    |
| en    |
| for   |
| from  |
| how   |
| i     |
| in    |
| is    |
| it    |
| la    |
| of    |
| on    |
| or    |
| that  |
| the   |
| this  |
| to    |
| was   |
| what  |
| when  |
| where |
| who   |
| will  |
| with  |
| und   |
| the   |
| www   |
+-------+
36 rows in set (0.00 sec)

mysql>

The INNODB_BUFFER_PAGE table holds information about each page in the InnoDB buffer pool.

mysql> select * from INNODB_BUFFER_PAGE where PAGE_TYPE = 'INDEX';
+---------+----------+-------+-------------+-----------+------------+-----------+-----------+---------------------+---------------------+-------------+--------------------------------+-----------------------+----------------+-----------+-----------------+------------+---------+--------+-----------------+
| POOL_ID | BLOCK_ID | SPACE | PAGE_NUMBER | PAGE_TYPE | FLUSH_TYPE | FIX_COUNT | IS_HASHED | NEWEST_MODIFICATION | OLDEST_MODIFICATION | ACCESS_TIME | TABLE_NAME                     | INDEX_NAME            | NUMBER_RECORDS | DATA_SIZE | COMPRESSED_SIZE | PAGE_STATE | IO_FIX  | IS_OLD | FREE_PAGE_CLOCK |
+---------+----------+-------+-------------+-----------+------------+-----------+-----------+---------------------+---------------------+-------------+--------------------------------+-----------------------+----------------+-----------+-----------------+------------+---------+--------+-----------------+
|       0 |        5 |     0 |          11 | INDEX     |          0 |         0 | NO        |                   0 |                   0 |  2922007230 | `SYS_INDEXES`                  | CLUST_IND             |             61 |      4407 |               0 | FILE_PAGE  | IO_NONE | NO     |               0 |
|       0 |      459 |     0 |           8 | INDEX     |          0 |         0 | NO        |                   0 |                   0 |  2922007345 | `SYS_TABLES`                   | CLUST_IND             |             32 |      2438 |               0 | FILE_PAGE  | IO_NONE | NO     |               0 |
|       0 |      460 |     0 |          10 | INDEX     |          0 |         0 | NO        |                   0 |                   0 |  2922007365 | `SYS_COLUMNS`                  | CLUST_IND             |            174 |     11518 |               0 | FILE_PAGE  | IO_NONE | NO     |               0 |
|       0 |      461 |     0 |          12 | INDEX     |          0 |         0 | NO        |                   0 |                   0 |  2922007365 | `SYS_FIELDS`                   | CLUST_IND             |             68 |      3039 |               0 | FILE_PAGE  | IO_NONE | NO     |               0 |
|       0 |      462 |     0 |         303 | INDEX     |          0 |         0 | NO        |                   0 |                   0 |  2922007365 | `SYS_FOREIGN`                  | FOR_IND               |             22 |      1031 |               0 | FILE_PAGE  | IO_NONE | NO     |               0 |
|       0 |      463 |     0 |         304 | INDEX     |          0 |         0 | NO        |                   0 |                   0 |  2922007365 | `SYS_FOREIGN`                  | REF_IND               |             22 |      1007 |               0 | FILE_PAGE  | IO_NONE | NO     |               0 |
|       0 |      464 |     0 |           9 | INDEX     |          0 |         0 | NO        |                   0 |                   0 |  2922007366 | `SYS_TABLES`                   | ID_IND                |             32 |       998 |               0 | FILE_PAGE  | IO_NONE | NO     |               0 |
|       0 |      465 |     0 |         308 | INDEX     |          0 |         0 | NO        |                   0 |                   0 |  2922007366 | `SYS_DATAFILES`                | SYS_DATAFILES_SPACE   |             28 |      1355 |               0 | FILE_PAGE  | IO_NONE | NO     |               0 |
|       0 |      466 |     0 |         307 | INDEX     |          0 |         0 | NO        |                   0 |                   0 |  2922007367 | `SYS_TABLESPACES`              | SYS_TABLESPACES_SPACE |             28 |      1327 |               0 | FILE_PAGE  | IO_NONE | NO     |               0 |
|       0 |      467 |     2 |           3 | INDEX     |          0 |         0 | NO        |                   0 |                   0 |  2922007368 | `mysql`.`innodb_index_stats`   | PRIMARY               |              2 |        97 |               0 | FILE_PAGE  | IO_NONE | NO     |               0 |
|       0 |      469 |     2 |           5 | INDEX     |          0 |         0 | NO        |                   0 |                   0 |  2922007368 | `mysql`.`innodb_index_stats`   | PRIMARY               |             85 |      8311 |               0 | FILE_PAGE  | IO_NONE | NO     |               0 |
|       0 |      470 |     1 |           3 | INDEX     |          0 |         0 | NO        |                   0 |                   0 |  2922007369 | `mysql`.`innodb_table_stats`   | PRIMARY               |             23 |      1392 |               0 | FILE_PAGE  | IO_NONE | NO     |               0 |
|       0 |      472 |     2 |           4 | INDEX     |          0 |         0 | YES       |                   0 |                   0 |  2922007370 | `mysql`.`innodb_index_stats`   | PRIMARY               |             95 |      9328 |               0 | FILE_PAGE  | IO_NONE | NO     |               0 |
|       0 |      475 |     4 |           3 | INDEX     |          0 |         0 | NO        |                   0 |                   0 |  2922007481 | `mysql`.`slave_master_info`    | PRIMARY               |              0 |         0 |               0 | FILE_PAGE  | IO_NONE | NO     |               0 |
|       0 |      478 |     5 |           3 | INDEX     |          0 |         0 | NO        |                   0 |                   0 |  2922007483 | `mysql`.`slave_worker_info`    | PRIMARY               |              0 |         0 |               0 | FILE_PAGE  | IO_NONE | NO     |               0 |
|       0 |      481 |     3 |           3 | INDEX     |          0 |         0 | NO        |                   0 |                   0 |  2922007485 | `mysql`.`slave_relay_log_info` | PRIMARY               |              0 |         0 |               0 | FILE_PAGE  | IO_NONE | NO     |               0 |
|       0 |      484 |     0 |         302 | INDEX     |          0 |         0 | NO        |                   0 |                   0 |  2924462856 | `SYS_FOREIGN`                  | ID_IND                |             22 |      1781 |               0 | FILE_PAGE  | IO_NONE | NO     |               0 |
|       0 |      485 |     0 |         305 | INDEX     |          0 |         0 | NO        |                   0 |                   0 |  2924462856 | `SYS_FOREIGN_COLS`             | ID_IND                |             22 |      1598 |               0 | FILE_PAGE  | IO_NONE | NO     |               0 |
+---------+----------+-------+-------------+-----------+------------+-----------+-----------+---------------------+---------------------+-------------+--------------------------------+-----------------------+----------------+-----------+-----------------+------------+---------+--------+-----------------+
18 rows in set (0.08 sec)

mysql>

The INNODB_SYS_TABLESPACES table stores information about InnoDB tablespaces,
allowing it to be queried through INFORMATION_SCHEMA.

mysql> select * from INNODB_SYS_TABLESPACES;
+-------+----------------------------+------+-------------+----------------------+-----------+---------------+
| SPACE | NAME                       | FLAG | FILE_FORMAT | ROW_FORMAT           | PAGE_SIZE | ZIP_PAGE_SIZE |
+-------+----------------------------+------+-------------+----------------------+-----------+---------------+
|     1 | mysql/innodb_table_stats   |    0 | Antelope    | Compact or Redundant |     16384 |             0 |
|     2 | mysql/innodb_index_stats   |    0 | Antelope    | Compact or Redundant |     16384 |             0 |
|     3 | mysql/slave_relay_log_info |    0 | Antelope    | Compact or Redundant |     16384 |             0 |
|     4 | mysql/slave_master_info    |    0 | Antelope    | Compact or Redundant |     16384 |             0 |
|     5 | mysql/slave_worker_info    |    0 | Antelope    | Compact or Redundant |     16384 |             0 |
|     7 | sakila/address             |    0 | Antelope    | Compact or Redundant |     16384 |             0 |
|     8 | sakila/category            |    0 | Antelope    | Compact or Redundant |     16384 |             0 |
|     9 | sakila/city                |    0 | Antelope    | Compact or Redundant |     16384 |             0 |
|    10 | sakila/country             |    0 | Antelope    | Compact or Redundant |     16384 |             0 |
|    11 | sakila/customer            |    0 | Antelope    | Compact or Redundant |     16384 |             0 |
|    12 | sakila/film                |    0 | Antelope    | Compact or Redundant |     16384 |             0 |
|    13 | sakila/film_actor          |    0 | Antelope    | Compact or Redundant |     16384 |             0 |
|    14 | sakila/film_category       |    0 | Antelope    | Compact or Redundant |     16384 |             0 |
|    15 | sakila/inventory           |    0 | Antelope    | Compact or Redundant |     16384 |             0 |
|    16 | sakila/language            |    0 | Antelope    | Compact or Redundant |     16384 |             0 |
|    17 | sakila/payment             |    0 | Antelope    | Compact or Redundant |     16384 |             0 |
|    18 | sakila/rental              |    0 | Antelope    | Compact or Redundant |     16384 |             0 |
|    19 | sakila/staff               |    0 | Antelope    | Compact or Redundant |     16384 |             0 |
|    20 | sakila/store               |    0 | Antelope    | Compact or Redundant |     16384 |             0 |
|    22 | test/lck                   |    0 | Antelope    | Compact or Redundant |     16384 |             0 |
|    24 | test/montable              |    0 | Antelope    | Compact or Redundant |     16384 |             0 |
|    28 | test/t                     |    0 | Antelope    | Compact or Redundant |     16384 |             0 |
|    30 | sakila/actor               |    0 | Antelope    | Compact or Redundant |     16384 |             0 |
|    38 | copy_test/language         |    0 | Antelope    | Compact or Redundant |     16384 |             0 |
|    39 | copy_test/lck              |    0 | Antelope    | Compact or Redundant |     16384 |             0 |
|    40 | copy_test/montable         |    0 | Antelope    | Compact or Redundant |     16384 |             0 |
|    41 | copy_test/t                |    0 | Antelope    | Compact or Redundant |     16384 |             0 |
|    45 | test/language              |    0 | Antelope    | Compact or Redundant |     16384 |             0 |
+-------+----------------------------+------+-------------+----------------------+-----------+---------------+
28 rows in set (0.00 sec)

mysql>

The INNODB_SYS_FOREIGN_COLS table provides status information about the columns of InnoDB foreign keys.

mysql> select * from INNODB_SYS_FOREIGN_COLS;
+----------------------------------+----------------------+--------------+-----+
| ID                               | FOR_COL_NAME         | REF_COL_NAME | POS |
+----------------------------------+----------------------+--------------+-----+
| sakila/fk_address_city           | city_id              | city_id      |   0 |
| sakila/fk_city_country           | country_id           | country_id   |   0 |
| sakila/fk_customer_address       | address_id           | address_id   |   0 |
| sakila/fk_customer_store         | store_id             | store_id     |   0 |
| sakila/fk_film_actor_actor       | actor_id             | actor_id     |   0 |
| sakila/fk_film_actor_film        | film_id              | film_id      |   0 |
| sakila/fk_film_category_category | category_id          | category_id  |   0 |
| sakila/fk_film_category_film     | film_id              | film_id      |   0 |
| sakila/fk_film_language          | language_id          | language_id  |   0 |
| sakila/fk_film_language_original | original_language_id | language_id  |   0 |
| sakila/fk_inventory_film         | film_id              | film_id      |   0 |
| sakila/fk_inventory_store        | store_id             | store_id     |   0 |
| sakila/fk_payment_customer       | customer_id          | customer_id  |   0 |
| sakila/fk_payment_rental         | rental_id            | rental_id    |   0 |
| sakila/fk_payment_staff          | staff_id             | staff_id     |   0 |
| sakila/fk_rental_customer        | customer_id          | customer_id  |   0 |
| sakila/fk_rental_inventory       | inventory_id         | inventory_id |   0 |
| sakila/fk_rental_staff           | staff_id             | staff_id     |   0 |
| sakila/fk_staff_address          | address_id           | address_id   |   0 |
| sakila/fk_staff_store            | store_id             | store_id     |   0 |
| sakila/fk_store_address          | address_id           | address_id   |   0 |
| sakila/fk_store_staff            | manager_staff_id     | staff_id     |   0 |
+----------------------------------+----------------------+--------------+-----+
22 rows in set (0.00 sec)

mysql>

The INNODB_BUFFER_POOL_STATS table provides much of the same buffer pool information provided in SHOW ENGINE INNODB STATUS output.

mysql> select * from INNODB_BUFFER_POOL_STATS;
+---------+-----------+--------------+----------------+--------------------+-------------------------+--------------------+---------------+-------------------+--------------------+------------------+----------------------+-----------------------+---------------------------+-------------------+----------------------+----------------------+-----------------+-------------------+--------------------+------------------+----------+------------------------------+----------------------------------+-------------------------+---------------------------+-----------------+-------------------------+--------------+----------------+------------------+--------------------+
| POOL_ID | POOL_SIZE | FREE_BUFFERS | DATABASE_PAGES | OLD_DATABASE_PAGES | MODIFIED_DATABASE_PAGES | PENDING_DECOMPRESS | PENDING_READS | PENDING_FLUSH_LRU | PENDING_FLUSH_LIST | PAGES_MADE_YOUNG | PAGES_NOT_MADE_YOUNG | PAGES_MADE_YOUNG_RATE | PAGES_MADE_NOT_YOUNG_RATE | NUMBER_PAGES_READ | NUMBER_PAGES_CREATED | NUMBER_PAGES_WRITTEN | PAGES_READ_RATE | PAGES_CREATE_RATE | PAGES_WRITTEN_RATE | NUMBER_PAGES_GET | HIT_RATE | YOUNG_MAKE_PER_THOUSAND_GETS | NOT_YOUNG_MAKE_PER_THOUSAND_GETS | NUMBER_PAGES_READ_AHEAD | NUMBER_READ_AHEAD_EVICTED | READ_AHEAD_RATE | READ_AHEAD_EVICTED_RATE | LRU_IO_TOTAL | LRU_IO_CURRENT | UNCOMPRESS_TOTAL | UNCOMPRESS_CURRENT |
+---------+-----------+--------------+----------------+--------------------+-------------------------+--------------------+---------------+-------------------+--------------------+------------------+----------------------+-----------------------+---------------------------+-------------------+----------------------+----------------------+-----------------+-------------------+--------------------+------------------+----------+------------------------------+----------------------------------+-------------------------+---------------------------+-----------------+-------------------------+--------------+----------------+------------------+--------------------+
|       0 |     14528 |        14042 |            485 |                  0 |                       0 |                  0 |             0 |                 0 |                  0 |                0 |                    0 |                     0 |                         0 |               485 |                    0 |                    1 |               0 |                 0 |                  0 |             7443 |        0 |                            0 |                                0 |                       0 |                         0 |               0 |                       0 |            0 |              0 |                0 |                  0 |
+---------+-----------+--------------+----------------+--------------------+-------------------------+--------------------+---------------+-------------------+--------------------+------------------+----------------------+-----------------------+---------------------------+-------------------+----------------------+----------------------+-----------------+-------------------+--------------------+------------------+----------+------------------------------+----------------------------------+-------------------------+---------------------------+-----------------+-------------------------+--------------+----------------+------------------+--------------------+
1 row in set (0.00 sec)

mysql> SHOW ENGINE INNODB STATUS\G
*************************** 1. row ***************************
  Type: InnoDB
  Name:
Status:
=====================================
2014-08-07 12:19:57 d60 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 38 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 1 srv_active, 0 srv_shutdown, 5282 srv_idle
srv_master_thread log flush and writes: 5283
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 2
OS WAIT ARRAY INFO: signal count 2
Mutex spin waits 1, rounds 0, OS waits 0
RW-shared spins 2, rounds 60, OS waits 2
RW-excl spins 0, rounds 0, OS waits 0
Spin rounds per wait: 0.00 mutex, 30.00 RW-shared, 0.00 RW-excl
------------
TRANSACTIONS
------------
Trx id counter 153093
Purge done for trx's n:o < 152169 undo n:o < 0 state: running but idle
History list length 295
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 1, OS thread handle 0xd60, query id 47 localhost ::1 root init
SHOW ENGINE INNODB STATUS
--------
FILE I/O
--------
I/O thread 0 state: wait Windows aio (insert buffer thread)
I/O thread 1 state: wait Windows aio (log thread)
I/O thread 2 state: wait Windows aio (read thread)
I/O thread 3 state: wait Windows aio (read thread)
I/O thread 4 state: wait Windows aio (read thread)
I/O thread 5 state: wait Windows aio (read thread)
I/O thread 6 state: wait Windows aio (write thread)
I/O thread 7 state: wait Windows aio (write thread)
I/O thread 8 state: wait Windows aio (write thread)
I/O thread 9 state: wait Windows aio (write thread)
Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] ,
 ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
501 OS file reads, 5 OS file writes, 5 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
 insert 0, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 471173, node heap has 1 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 91057309
Log flushed up to   91057309
Pages flushed up to 91057309
Last checkpoint at  91057309
0 pending log writes, 0 pending chkp writes
8 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 243728384; in additional pool allocated 0
Dictionary memory allocated 200595
Buffer pool size   14528
Free buffers       14042
Database pages     485
Old database pages 0
Modified db pages  0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 485, created 0, written 1
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 485, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Main thread id 8072, state: sleeping
Number of rows inserted 0, updated 0, deleted 0, read 0
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

1 row in set (0.01 sec)

mysql>

Reference:
http://dev.mysql.com/doc/refman/5.6/en/information-schema.html
http://dev.mysql.com/doc/refman/5.6/en/extended-show.html

Comments are closed.

Post Navigation