ps_helperはperformance_schemaやinformation_schemaのテーブルを使用して、
FUNCTION 8つ、 PROCEDURE 12つ、VIEW 53つ作成して、
直接、自分で集計するよりも簡単に把握出来るようにしてくれている。

GITからdbahelperを取得させて頂く。


variable.user@myPC /c/git (master)
$ git clone https://github.com/MarkLeith/dbahelper.git dbahelper
Cloning into ‘dbahelper’…
remote: Counting objects: 224, done.
Receiving objecemote: Total 224 (delta 0), reused 0 (delta 0)ts: 71% (160/224),
R
Receiving objects: 100% (224/224), 96.80 KiB | 139.00 KiB/s, done.
Resolving deltas: 100% (92/92), done.
Checking connectivity… done.

variable.user@myPC /c/git (master)
$ cd dbahelper/

variable.user@myPC /c/git/dbahelper (master)
$ “C:\Program Files\MySQL\MySQL Server 5.6\bin\mysql” -u root -p < ps_helper_56.sql Enter password: ********* variable.user@myPC /c/git/dbahelper (master) $ [/SHELL] ps_helperデータベースが出来ている事を確認

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| copy_test          |
| mysql              |
| performance_schema |
| ps_helper          |
| sakila             |
| sys                |
| test               |
| world              |
+--------------------+
9 rows in set (0.00 sec)

mysql> SELECT ROUTINE_SCHEMA AS db, ROUTINE_TYPE AS object_type, COUNT(*) AS count FROM INFORMATION_SCHEMA.ROUTINES where ROUTINE_SCHEMA = 'ps_helper' GROUP BY ROUTINE_SCHEMA, ROUTINE_TYPE
    ->  UNION
    -> SELECT TABLE_SCHEMA, TABLE_TYPE, COUNT(*) FROM INFORMATION_SCHEMA.TABLES  where TABLE_SCHEMA = 'ps_helper' GROUP BY TABLE_SCHEMA, TABLE_TYPE
    ->  UNION
    -> SELECT TABLE_SCHEMA, CONCAT('INDEX (', INDEX_TYPE, ')'), COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS where TABLE_SCHEMA = 'ps_helper' GROUP BY TABLE_SCHEMA, INDEX_TYPE
    ->  UNION
    -> SELECT TRIGGER_SCHEMA, 'TRIGGER', COUNT(*) FROM INFORMATION_SCHEMA.TRIGGERS where TRIGGER_SCHEMA = 'ps_helper' GROUP BY TRIGGER_SCHEMA
    ->  UNION
    -> SELECT EVENT_SCHEMA, 'EVENT', COUNT(*) FROM INFORMATION_SCHEMA.EVENTS where EVENT_SCHEMA = 'ps_helper' GROUP BY EVENT_SCHEMA ORDER BY DB,OBJECT_TYPE;
+-----------+-------------+-------+
| db        | object_type | count |
+-----------+-------------+-------+
| ps_helper | FUNCTION    |     8 |
| ps_helper | PROCEDURE   |    12 |
| ps_helper | VIEW        |    53 |
+-----------+-------------+-------+
3 rows in set (0.09 sec)

mysql>

mysql> show tables;
+-------------------------------------------------+
| Tables_in_ps_helper                             |
+-------------------------------------------------+
| _digest_95th_percentile_by_avg_us               |
| _digest_avg_latency_by_avg_us                   |
| check_lost_instrumentation                      |
| innodb_buffer_stats_by_schema                   |
| innodb_buffer_stats_by_schema_raw               |
| innodb_buffer_stats_by_table                    |
| innodb_buffer_stats_by_table_raw                |
| io_by_thread_by_latency                         |
| io_by_thread_by_latency_raw                     |
| io_global_by_file_by_bytes                      |
| io_global_by_file_by_bytes_raw                  |
| io_global_by_file_by_latency                    |
| io_global_by_file_by_latency_raw                |
| io_global_by_wait_by_bytes                      |
| io_global_by_wait_by_bytes_raw                  |
| io_global_by_wait_by_latency                    |
| io_global_by_wait_by_latency_raw                |
| latest_file_io                                  |
| latest_file_io_raw                              |
| processlist                                     |
| processlist_raw                                 |
| schema_index_statistics                         |
| schema_index_statistics_raw                     |
| schema_object_overview                          |
| schema_table_statistics                         |
| schema_table_statistics_raw                     |
| schema_table_statistics_with_buffer             |
| schema_table_statistics_with_buffer_raw         |
| schema_tables_with_full_table_scans             |
| schema_unused_indexes                           |
| statement_analysis                              |
| statement_analysis_raw                          |
| statements_with_errors_or_warnings              |
| statements_with_full_table_scans                |
| statements_with_runtimes_in_95th_percentile     |
| statements_with_runtimes_in_95th_percentile_raw |
| statements_with_sorting                         |
| statements_with_temp_tables                     |
| user_summary                                    |
| user_summary_by_stages                          |
| user_summary_by_stages_raw                      |
| user_summary_by_statement_type                  |
| user_summary_by_statement_type_raw              |
| user_summary_raw                                |
| version                                         |
| wait_classes_global_by_avg_latency              |
| wait_classes_global_by_avg_latency_raw          |
| wait_classes_global_by_latency                  |
| wait_classes_global_by_latency_raw              |
| waits_by_user_by_latency                        |
| waits_by_user_by_latency_raw                    |
| waits_global_by_latency                         |
| waits_global_by_latency_raw                     |
+-------------------------------------------------+
53 rows in set (0.00 sec)

mysql>

schema_unused_indexesビューを確認すると、アクセスされていないインデックスを確認可能
performance_schema.table_io_waits_summary_by_index_usageから情報を取得


mysql> desc schema_unused_indexes;
+---------------+-------------+------+-----+---------+-------+
| Field         | Type        | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
| object_schema | varchar(64) | YES  |     | NULL    |       |
| object_name   | varchar(64) | YES  |     | NULL    |       |
| index_name    | varchar(64) | YES  |     | NULL    |       |
+---------------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> show create view schema_unused_indexes\G
*************************** 1. row ***************************
                View: schema_unused_indexes
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY INVOKER VIEW 
`schema_unused_indexes` AS select `performance_schema`.`table_io_waits_summary_by_index_usage`.`OBJECT_SCHEMA` AS `object_schema`,
`performance_schema`.`table_io_waits_summary_by_index_usage`.`OBJECT_NAME` AS `object_name`,
`performance_schema`.`table_io_waits_summary_by_index_usage`.`INDEX_NAME` AS `index_name` 
from `performance_schema`.`table_io_waits_summary_by_index_usage` 
where ((`performance_schema`.`table_io_waits_summary_by_index_usage`.`INDEX_NAME` is not null) 
and (`performance_schema`.`table_io_waits_summary_by_index_usage`.`COUNT_STAR` = 0) 
and (`performance_schema`.`table_io_waits_summary_by_index_usage`.`OBJECT_SCHEMA` <> 'mysql')) 
order by `performance_schema`.`table_io_waits_summary_by_index_usage`.`OBJECT_SCHEMA`,
`performance_schema`.`table_io_waits_summary_by_index_usage`.`OBJECT_NAME`
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.00 sec)

mysql>

mysql> select * from schema_unused_indexes;
+---------------+-------------+------------+
| object_schema | object_name | index_name |
+---------------+-------------+------------+
| test          | language    | PRIMARY    |
+---------------+-------------+------------+
1 row in set (0.01 sec)

mysql>

Innodbのスキーマのバッファー状態が確認出来る。
innodb_buffer_pageテーブルから情報を取得している。
Explainで見ると以下のような感じで実行情報が表示される。

mysql> show create view innodb_buffer_stats_by_schema\G
*************************** 1. row ***************************
                View: innodb_buffer_stats_by_schema
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY INVOKER VIEW 
`innodb_buffer_stats_by_schema` AS select if((locate('.',`ibp`.`TABLE_NAME`) = 0),
'InnoDB System',replace(substring_index(`ibp`.`TABLE_NAME`,'.',1),'`','')) AS `object_schema`,
`format_bytes`(sum(if((`ibp`.`COMPRESSED_SIZE` = 0),16384,`ibp`.`COMPRESSED_SIZE`))) AS `allocated`,
`format_bytes`(sum(`ibp`.`DATA_SIZE`)) AS `data`,count(`ibp`.`PAGE_NUMBER`) AS `pages`,
count(if((`ibp`.`IS_HASHED` = 'YES'),1,0)) AS `pages_hashed`,count(if((`ibp`.`IS_OLD` = 'YES'),1,0)) AS `pages_old`,
round((sum(`ibp`.`NUMBER_RECORDS`) / count(distinct `ibp`.`INDEX_NAME`)),0) AS `rows_cached` 
from `information_schema`.`innodb_buffer_page` `ibp` where (`ibp`.`TABLE_NAME` is not null) 
group by `object_schema` order by sum(if((`ibp`.`COMPRESSED_SIZE` = 0),16384,`ibp`.`COMPRESSED_SIZE`)) desc
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.00 sec)

mysql>

mysql> select * from innodb_buffer_stats_by_schema;
+---------------+------------+-----------+-------+--------------+-----------+-------------+
| object_schema | allocated  | data      | pages | pages_hashed | pages_old | rows_cached |
+---------------+------------+-----------+-------+--------------+-----------+-------------+
| test          | 32.64 MiB  | 29.93 MiB |  2089 |         2089 |      2089 |      672443 |
| InnoDB System | 144.00 KiB | 26.48 KiB |     9 |            9 |         9 |          78 |
| mysql         | 112.00 KiB | 18.68 KiB |     7 |            7 |         7 |         205 |
+---------------+------------+-----------+-------+--------------+-----------+-------------+
3 rows in set (0.08 sec)

mysql>

mysql> explain select * from innodb_buffer_stats_by_schema;
+----+-------------+------------+------+---------------+------+---------+------+------+----------------------------------------------+
| id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows | Extra                                        |
+----+-------------+------------+------+---------------+------+---------+------+------+----------------------------------------------+
|  1 | PRIMARY     | <derived2> | ALL  | NULL          | NULL | NULL    | NULL |    2 | NULL                                         |
|  2 | DERIVED     | ibp        | ALL  | NULL          | NULL | NULL    | NULL | NULL | Using where; Using temporary; Using filesort |
+----+-------------+------------+------+---------------+------+---------+------+------+----------------------------------------------+
2 rows in set (0.00 sec)

mysql> explain format=JSON select * from innodb_buffer_stats_by_schema;
+--------------------------------------------------------------------------------------------------------------------------------------
| EXPLAIN
+--------------------------------------------------------------------------------------------------------------------------------------
| {
  "query_block": {
    "select_id": 1,
    "table": {
      "table_name": "innodb_buffer_stats_by_schema",
      "access_type": "ALL",
      "rows": 2,
      "filtered": 100,
      "materialized_from_subquery": {
        "using_temporary_table": true,
        "dependent": false,
        "cacheable": true,
        "query_block": {
          "select_id": 2,
          "ordering_operation": {
            "using_temporary_table": true,
            "using_filesort": true,
            "grouping_operation": {
              "using_filesort": true,
              "table": {
                "table_name": "ibp",
                "access_type": "ALL",
                "attached_condition": "(`ibp`.`TABLE_NAME` is not null)"
              }
            }
          }
        }
      }
    }
  }
} |
+--------------------------------------------------------------------------------------------------------------------------------------
1 row in set, 1 warning (0.00 sec)

mysql>


mysql> select * from latest_file_io;
+------------------------+----------------------------------------+-----------+-----------+-----------+
| thread                 | file                                   | latency   | operation | requested |
+------------------------+----------------------------------------+-----------+-----------+-----------+
| root@localhost:50115:1 | @@datadir/MyPC.log                   | 27.60 us  | write     | 57 bytes  |
| root@localhost:50115:1 | @@datadir/ps_helper/latest_file_io.frm | 85.66 us  | open      | NULL      |
| root@localhost:50115:1 | @@datadir/ps_helper/latest_file_io.frm | 18.51 us  | read      | 64 bytes  |
| root@localhost:50115:1 | @@datadir/ps_helper/latest_file_io.frm | 26.79 us  | open      | NULL      |
| root@localhost:50115:1 | @@datadir/ps_helper/latest_file_io.frm | 7.29 us   | read      | 3.39 KiB  |
| root@localhost:50115:1 | @@datadir/ps_helper/latest_file_io.frm | 13.99 us  | close     | NULL      |
| root@localhost:50115:1 | @@datadir/ps_helper/latest_file_io.frm | 7.22 us   | close     | NULL      |
| root@localhost:50115:1 | @@tmpdir//#sql1990_1_12.MYI            | 641.05 us | create    | NULL      |
| root@localhost:50115:1 | @@tmpdir//#sql1990_1_12.MYD            | 518.82 us | create    | NULL      |
| root@localhost:50115:1 | @@tmpdir//#sql1990_1_12.MYI            | 38.61 us  | write     | 176 bytes |
| root@localhost:50115:1 | @@tmpdir//#sql1990_1_12.MYI            | 22.86 us  | write     | 100 bytes |
| root@localhost:50115:1 | @@tmpdir//#sql1990_1_12.MYI            | 16.76 us  | write     | 7 bytes   |
| root@localhost:50115:1 | @@tmpdir//#sql1990_1_12.MYI            | 8.39 us   | write     | 7 bytes   |
| root@localhost:50115:1 | @@tmpdir//#sql1990_1_12.MYI            | 8.04 us   | write     | 7 bytes   |
| root@localhost:50115:1 | @@tmpdir//#sql1990_1_12.MYI            | 8.14 us   | write     | 7 bytes   |
| root@localhost:50115:1 | @@tmpdir//#sql1990_1_12.MYI            | 7.61 us   | write     | 7 bytes   |
| root@localhost:50115:1 | @@tmpdir//#sql1990_1_12.MYI            | 7.69 us   | write     | 7 bytes   |
| root@localhost:50115:1 | @@tmpdir//#sql1990_1_12.MYI            | 7.57 us   | write     | 7 bytes   |
| root@localhost:50115:1 | @@tmpdir//#sql1990_1_12.MYI            | 7.53 us   | write     | 7 bytes   |
| root@localhost:50115:1 | @@tmpdir//#sql1990_1_12.MYI            | 7.85 us   | write     | 7 bytes   |
| root@localhost:50115:1 | @@tmpdir//#sql1990_1_12.MYI            | 44.70 us  | chsize    | 1.00 KiB  |
| root@localhost:50115:1 | @@tmpdir//#sql1990_1_12.MYD            | 102.88 us | close     | NULL      |
| root@localhost:50115:1 | @@tmpdir//#sql1990_1_12.MYI            | 115.22 us | close     | NULL      |
| root@localhost:50115:1 | @@tmpdir//#sql1990_1_12.MYI            | 38.44 us  | open      | NULL      |
| root@localhost:50115:1 | @@tmpdir//#sql1990_1_12.MYI            | 6.68 us   | read      | 24 bytes  |
| root@localhost:50115:1 | @@tmpdir//#sql1990_1_12.MYI            | 789.17 ns | seek      | NULL      |
| root@localhost:50115:1 | @@tmpdir//#sql1990_1_12.MYI            | 3.02 us   | read      | 339 bytes |
| root@localhost:50115:1 | @@tmpdir//#sql1990_1_12.MYD            | 73.41 us  | open      | NULL      |
| root@localhost:50115:1 | @@datadir/mysql/proc.MYD               | 1.18 us   | seek      | NULL      |
| root@localhost:50115:1 | @@datadir/mysql/proc.MYD               | 5.41 us   | read      | 20 bytes  |
| root@localhost:50115:1 | @@datadir/mysql/proc.MYD               | 2.59 us   | read      | 1.25 KiB  |
| root@localhost:50115:1 | @@datadir/mysql/proc.MYD               | 1.04 us   | seek      | NULL      |
| root@localhost:50115:1 | @@datadir/mysql/proc.MYD               | 3.95 us   | read      | 20 bytes  |
| root@localhost:50115:1 | @@datadir/mysql/proc.MYD               | 2.15 us   | read      | 1.51 KiB  |
| root@localhost:50115:1 | @@datadir/mysql/proc.MYD               | 958.79 ns | seek      | NULL      |
| root@localhost:50115:1 | @@datadir/mysql/proc.MYD               | 4.00 us   | read      | 20 bytes  |
| root@localhost:50115:1 | @@datadir/mysql/proc.MYD               | 2.00 us   | read      | 1.17 KiB  |
| root@localhost:50115:1 | @@tmpdir//#sql1990_1_12.MYD            | 35.29 us  | write     | 96 bytes  |
| root@localhost:50115:1 | @@tmpdir//#sql1990_1_12.MYD            | 1.25 us   | tell      | NULL      |
| root@localhost:50115:1 | @@tmpdir//#sql1990_1_12.MYD            | 2.87 us   | seek      | NULL      |
| root@localhost:50115:1 | @@tmpdir//#sql1990_1_12.MYD            | 255.84 ns | seek      | NULL      |
| root@localhost:50115:1 | @@tmpdir//#sql1990_1_12.MYD            | 4.21 us   | read      | 96 bytes  |
+------------------------+----------------------------------------+-----------+-----------+-----------+
42 rows in set (0.04 sec)

mysql>

performance_schemaからIOヒストリー情報を取得している。

mysql> show create view latest_file_io_raw\G
*************************** 1. row ***************************
                View: latest_file_io_raw
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY INVOKER VIEW `latest_file_io_raw` AS select if(isnull(`information_schema`.`processlist`.`ID`),
concat(substring_index(`performance_schema`.`threads`.`NAME`,'/',-(1)),':',`performance_schema`.`events_waits_history_long`.`THREAD_ID`),
concat(`information_schema`.`processlist`.`USER`,'@',`information_schema`.`processlist`.`HOST`,':',`information_schema`.`processlist`.`ID`)) AS `thread`,
`performance_schema`.`events_waits_history_long`.`OBJECT_NAME` AS `file`,
`performance_schema`.`events_waits_history_long`.`TIMER_WAIT` AS `latency`,`performance_schema`.`events_waits_history_long`.`OPERATION` AS `operation`,
`performance_schema`.`events_waits_history_long`.`NUMBER_OF_BYTES` AS `requested` from ((`performance_schema`.`events_waits_history_long` join `performance_schema`.`threads` 
on((`performance_schema`.`events_waits_history_long`.`THREAD_ID` = `performance_schema`.`threads`.`THREAD_ID`))) left join `inf
ormation_schema`.`processlist` on((`performance_schema`.`threads`.`PROCESSLIST_ID` = `information_schema`.`processlist`.`ID`))) 
where ((`performance_schema`.`events_waits_history_long`.`OBJECT_NAME` is not null) and (`performance_schema`.`events_waits_history_long`.`EVENT_NAME` like 'wait/io/file/%')) 
order by `performance_schema`.`events_waits_history_long`.`TIMER_START`
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.00 sec)

mysql>


mysql> select * from latest_file_io_raw;
+------------------------+-----------------------------------------------------------------------------+-----------+-----------+-----------+
| thread                 | file                                                                        | latency   | operation | requested |
+------------------------+-----------------------------------------------------------------------------+-----------+-----------+-----------+
| root@localhost:50115:1 | C:\ProgramData\MySQL\MySQL Server 5.6\data\MyPC.log                         |  27600429 | write     |        57 |
| root@localhost:50115:1 | C:\ProgramData\MySQL\MySQL Server 5.6\data\ps_helper\latest_file_io.frm     |  85656006 | open      |      NULL |
| root@localhost:50115:1 | C:\ProgramData\MySQL\MySQL Server 5.6\data\ps_helper\latest_file_io.frm     |  18514571 | read      |        64 |
| root@localhost:50115:1 | C:\ProgramData\MySQL\MySQL Server 5.6\data\ps_helper\latest_file_io.frm     |  26785597 | open      |      NULL |
| root@localhost:50115:1 | C:\ProgramData\MySQL\MySQL Server 5.6\data\ps_helper\latest_file_io.frm     |   7293388 | read      |      3476 |
| root@localhost:50115:1 | C:\ProgramData\MySQL\MySQL Server 5.6\data\ps_helper\latest_file_io.frm     |  13987682 | close     |      NULL |
| root@localhost:50115:1 | C:\ProgramData\MySQL\MySQL Server 5.6\data\ps_helper\latest_file_io.frm     |   7219203 | close     |      NULL |
| root@localhost:50115:1 | C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp\#sql1990_1_12.MYI           | 641052635 | create    |      NULL |
| root@localhost:50115:1 | C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp\#sql1990_1_12.MYD           | 518815003 | create    |      NULL |
| root@localhost:50115:1 | C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp\#sql1990_1_12.MYI           |  38607879 | write     |       176 |
| root@localhost:50115:1 | C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp\#sql1990_1_12.MYI           |  22860609 | write     |       100 |
| root@localhost:50115:1 | C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp\#sql1990_1_12.MYI           |  16757389 | write     |         7 |
| root@localhost:50115:1 | C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp\#sql1990_1_12.MYI           |   8394935 | write     |         7 |
| root@localhost:50115:1 | C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp\#sql1990_1_12.MYI           |   8044461 | write     |         7 |
| root@localhost:50115:1 | C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp\#sql1990_1_12.MYI           |   8140300 | write     |         7 |
| root@localhost:50115:1 | C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp\#sql1990_1_12.MYI           |   7609376 | write     |         7 |
| root@localhost:50115:1 | C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp\#sql1990_1_12.MYI           |   7687170 | write     |         7 |
| root@localhost:50115:1 | C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp\#sql1990_1_12.MYI           |   7574088 | write     |         7 |
| root@localhost:50115:1 | C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp\#sql1990_1_12.MYI           |   7526770 | write     |         7 |
| root@localhost:50115:1 | C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp\#sql1990_1_12.MYI           |   7852382 | write     |         7 |
| root@localhost:50115:1 | C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp\#sql1990_1_12.MYI           |  44696262 | chsize    |      1024 |
| root@localhost:50115:1 | C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp\#sql1990_1_12.MYD           | 102876550 | close     |      NULL |
| root@localhost:50115:1 | C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp\#sql1990_1_12.MYI           | 115218929 | close     |      NULL |
| root@localhost:50115:1 | C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp\#sql1990_1_12.MYI           |  38438256 | open      |      NULL |
| root@localhost:50115:1 | C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp\#sql1990_1_12.MYI           |   6677853 | read      |        24 |
| root@localhost:50115:1 | C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp\#sql1990_1_12.MYI           |    789168 | seek      |      NULL |
| root@localhost:50115:1 | C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp\#sql1990_1_12.MYI           |   3022738 | read      |       339 |
| root@localhost:50115:1 | C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp\#sql1990_1_12.MYD           |  73413877 | open      |      NULL |
| root@localhost:50115:1 | C:\ProgramData\MySQL\MySQL Server 5.6\data\mysql\proc.MYD                   |   1182950 | seek      |      NULL |
| root@localhost:50115:1 | C:\ProgramData\MySQL\MySQL Server 5.6\data\mysql\proc.MYD                   |   5409891 | read      |        20 |
| root@localhost:50115:1 | C:\ProgramData\MySQL\MySQL Server 5.6\data\mysql\proc.MYD                   |   2586450 | read      |      1280 |
| root@localhost:50115:1 | C:\ProgramData\MySQL\MySQL Server 5.6\data\mysql\proc.MYD                   |   1042600 | seek      |      NULL |
| root@localhost:50115:1 | C:\ProgramData\MySQL\MySQL Server 5.6\data\mysql\proc.MYD                   |   3952256 | read      |        20 |
| root@localhost:50115:1 | C:\ProgramData\MySQL\MySQL Server 5.6\data\mysql\proc.MYD                   |   2153771 | read      |      1543 |
| root@localhost:50115:1 | C:\ProgramData\MySQL\MySQL Server 5.6\data\mysql\proc.MYD                   |    958791 | seek      |      NULL |
| root@localhost:50115:1 | C:\ProgramData\MySQL\MySQL Server 5.6\data\mysql\proc.MYD                   |   3997970 | read      |        20 |
| root@localhost:50115:1 | C:\ProgramData\MySQL\MySQL Server 5.6\data\mysql\proc.MYD                   |   1996980 | read      |      1202 |
| root@localhost:50115:1 | C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp\#sql1990_1_12.MYD           |  35292812 | write     |        96 |
| root@localhost:50115:1 | C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp\#sql1990_1_12.MYD           |   1247511 | tell      |      NULL |
| root@localhost:50115:1 | C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp\#sql1990_1_12.MYD           |   2868353 | seek      |      NULL |
| root@localhost:50115:1 | C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp\#sql1990_1_12.MYD           |    255838 | seek      |      NULL |
| root@localhost:50115:1 | C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp\#sql1990_1_12.MYD           |   4205688 | read      |        96 |
| root@localhost:50115:1 | C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp\#sql1990_1_12.MYI           |  63665968 | write     |       124 |
| root@localhost:50115:1 | C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp\#sql1990_1_12.MYI           |   5678962 | write     |         2 |
| root@localhost:50115:1 | C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp\#sql1990_1_12.MYI           | 184342106 | close     |      NULL |
| root@localhost:50115:1 | C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp\#sql1990_1_12.MYD           | 124138773 | close     |      NULL |
| root@localhost:50115:1 | C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp\#sql1990_1_12.MYI           | 141266285 | delete    |      NULL |
| root@localhost:50115:1 | C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp\#sql1990_1_12.MYD           |  93093353 | delete    |      NULL |
| root@localhost:50115:1 | C:\ProgramData\MySQL\MySQL Server 5.6\data\MyPC.log                         |  28632603 | write     |        40 |
| root@localhost:50115:1 | C:\ProgramData\MySQL\MySQL Server 5.6\data\MyPC.log                         |  19960577 | write     |        61 |
| root@localhost:50115:1 | C:\ProgramData\MySQL\MySQL Server 5.6\data\ps_helper\latest_file_io_raw.frm |  85139117 | open      |      NULL |
| root@localhost:50115:1 | C:\ProgramData\MySQL\MySQL Server 5.6\data\ps_helper\latest_file_io_raw.frm |  16528418 | read      |        64 |
| root@localhost:50115:1 | C:\ProgramData\MySQL\MySQL Server 5.6\data\ps_helper\latest_file_io_raw.frm |  25623900 | open      |      NULL |
| root@localhost:50115:1 | C:\ProgramData\MySQL\MySQL Server 5.6\data\ps_helper\latest_file_io_raw.frm |   7239253 | read      |      3344 |
| root@localhost:50115:1 | C:\ProgramData\MySQL\MySQL Server 5.6\data\ps_helper\latest_file_io_raw.frm |  14083521 | close     |      NULL |
| root@localhost:50115:1 | C:\ProgramData\MySQL\MySQL Server 5.6\data\ps_helper\latest_file_io_raw.frm |   8134285 | close     |      NULL |
| root@localhost:50115:1 | C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp\#sql1990_1_15.MYI           | 626691221 | create    |      NULL |
| root@localhost:50115:1 | C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp\#sql1990_1_15.MYD           | 493983880 | create    |      NULL |
| root@localhost:50115:1 | C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp\#sql1990_1_15.MYI           |  31832182 | write     |       176 |
| root@localhost:50115:1 | C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp\#sql1990_1_15.MYI           |  12938265 | write     |       100 |
| root@localhost:50115:1 | C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp\#sql1990_1_15.MYI           |   8823203 | write     |         7 |
| root@localhost:50115:1 | C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp\#sql1990_1_15.MYI           |   7795841 | write     |         7 |
| root@localhost:50115:1 | C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp\#sql1990_1_15.MYI           |   7610579 | write     |         7 |
| root@localhost:50115:1 | C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp\#sql1990_1_15.MYI           |   7805465 | write     |         7 |
| root@localhost:50115:1 | C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp\#sql1990_1_15.MYI           |   7639050 | write     |         7 |
| root@localhost:50115:1 | C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp\#sql1990_1_15.MYI           |   7535191 | write     |         7 |
| root@localhost:50115:1 | C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp\#sql1990_1_15.MYI           |   7808673 | write     |         7 |
| root@localhost:50115:1 | C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp\#sql1990_1_15.MYI           |   7686368 | write     |         7 |
| root@localhost:50115:1 | C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp\#sql1990_1_15.MYI           |   7537597 | write     |         7 |
| root@localhost:50115:1 | C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp\#sql1990_1_15.MYI           |  43320832 | chsize    |      1024 |
| root@localhost:50115:1 | C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp\#sql1990_1_15.MYD           |  89308314 | close     |      NULL |
| root@localhost:50115:1 | C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp\#sql1990_1_15.MYI           | 104258797 | close     |      NULL |
| root@localhost:50115:1 | C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp\#sql1990_1_15.MYI           |  39969274 | open      |      NULL |
| root@localhost:50115:1 | C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp\#sql1990_1_15.MYI           |   6673041 | read      |        24 |
| root@localhost:50115:1 | C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp\#sql1990_1_15.MYI           |    786762 | seek      |      NULL |
| root@localhost:50115:1 | C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp\#sql1990_1_15.MYI           |   3104141 | read      |       339 |
| root@localhost:50115:1 | C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp\#sql1990_1_15.MYD           |  72283859 | open      |      NULL |
| root@localhost:50115:1 | C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp\#sql1990_1_15.MYD           |  27100783 | write     |       100 |
| root@localhost:50115:1 | C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp\#sql1990_1_15.MYD           |   1311671 | tell      |      NULL |
| root@localhost:50115:1 | C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp\#sql1990_1_15.MYD           |   2707953 | seek      |      NULL |
| root@localhost:50115:1 | C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp\#sql1990_1_15.MYD           |    264259 | seek      |      NULL |
| root@localhost:50115:1 | C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp\#sql1990_1_15.MYD           |   4439471 | read      |       100 |
+------------------------+-----------------------------------------------------------------------------+-----------+-----------+-----------+
82 rows in set (0.04 sec)

mysql>
[/SQl]


<strong>SHOW FULL PROCESSLISTのような感じでperformance_schemaから情報を取得している。</strong>
[SQL]
mysql> desc processlist;
+------------------------+---------------------+------+-----+---------+-------+
| Field                  | Type                | Null | Key | Default | Extra |
+------------------------+---------------------+------+-----+---------+-------+
| thd_id                 | bigint(20) unsigned | NO   |     | NULL    |       |
| conn_id                | bigint(20) unsigned | YES  |     | NULL    |       |
| user                   | varchar(128)        | YES  |     | NULL    |       |
| db                     | varchar(64)         | YES  |     | NULL    |       |
| command                | varchar(16)         | YES  |     | NULL    |       |
| state                  | varchar(64)         | YES  |     | NULL    |       |
| time                   | bigint(20)          | YES  |     | NULL    |       |
| current_statement      | varchar(65)         | YES  |     | NULL    |       |
| last_statement         | varchar(65)         | YES  |     | NULL    |       |
| last_statement_latency | varchar(16)         | YES  |     | NULL    |       |
| lock_latency           | varchar(16)         | YES  |     | NULL    |       |
| rows_examined          | bigint(20) unsigned | YES  |     | NULL    |       |
| rows_sent              | bigint(20) unsigned | YES  |     | NULL    |       |
| rows_affected          | bigint(20) unsigned | YES  |     | NULL    |       |
| tmp_tables             | bigint(20) unsigned | YES  |     | NULL    |       |
| tmp_disk_tables        | bigint(20) unsigned | YES  |     | NULL    |       |
| full_scan              | varchar(3)          | NO   |     |         |       |
| last_wait              | varchar(128)        | YES  |     | NULL    |       |
| last_wait_latency      | varchar(16)         | YES  |     | NULL    |       |
| source                 | varchar(64)         | YES  |     | NULL    |       |
+------------------------+---------------------+------+-----+---------+-------+
20 rows in set (0.00 sec)

mysql> show create view processlist\G
*************************** 1. row ***************************
                View: processlist
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` 
SQL SECURITY INVOKER VIEW `processlist` AS select `pps`.`THREAD_ID` AS `thd_id`,
`pps`.`PROCESSLIST_ID` AS `conn_id`,if((`pps`.`NAME` = 'thread/sql/one_connection'),
concat(`pps`.`PROCESSLIST_USER`,'@',`pps`.`PROCESSLIST_HOST`),replace(`pps`.`NAME`,'thread/','')) AS `user`,
`pps`.`PROCESSLIST_DB` AS `db`,`pps`.`PROCESSLIST_COMMAND` AS `command`,`pps`.`PROCESSLIST_STATE` AS `state`,
`pps`.`PROCESSLIST_TIME` AS `time`,`format_statement`(`pps`.`PROCESSLIST_INFO`) AS `current_statement`,
if((`esc`.`TIMER_WAIT` is not null),`format_statement`(`esc`.`SQL_TEXT`),NULL) AS `last_statement`,
if((`esc`.`TIMER_WAIT` is not null),`format_time`(`esc`.`TIMER_WAIT`),NULL) AS `last_statement_latency`,
`format_time`(`esc`.`LOCK_TIME`) AS `lock_latency`,`esc`.`ROWS_EXAMINED` AS `rows_examined`,
`esc`.`ROWS_SENT` AS `rows_sent`,`esc`.`ROWS_AFFECTED` AS `rows_affected`,`esc`.`CREATED_TMP_TABLES` AS `tmp_tables`,
`esc`.`CREATED_TMP_DISK_TABLES` AS `tmp_disk_tables`,if(((`esc`.`NO_GOOD_INDEX_USED` > 0) or 
(`esc`.`NO_INDEX_USED` > 0)),'YES','NO') AS `full_scan`,`ewc`.`EVENT_NAME` AS `last_wait`,
if((isnull(`ewc`.`TIMER_WAIT`) and (`ewc`.`EVENT_NAME` is not null)),'Still Waiting',
`format_time`(`ewc`.`TIMER_WAIT`)) AS `last_wait_latency`,`ewc`.`SOURCE` AS `source` 
from ((`performance_schema`.`threads` `pps` left join `performance_schema`.`events_waits_current` `ewc` 
on((`pps`.`THREAD_ID` = `ewc`.`THREAD_ID`))) left join `performance_schema`.`events_statements_current` `esc` 
on((`pps`.`THREAD_ID` = `esc`.`THREAD_ID`))) order by `pps`.`PROCESSLIST_TIME`
desc,if((isnull(`ewc`.`TIMER_WAIT`) and (`ewc`.`EVENT_NAME` is not null)),'Still Waiting',`format_time`(`ewc`.`TIMER_WAIT`)) desc
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.00 sec)

mysql> show create table processlist\G
*************************** 1. row ***************************

mysql> select * from processlist;
+--------+---------+---------------------------------+-----------+---------+--------------+-------+-----------------------------------------------------------+-----------------------------------------------------------+------------------------+--------------+---------------+-----------+---------------+------------+-----------------+-----------+----------------------------+-------------------+-------------------+
| thd_id | conn_id | user                            | db        | command | state        | time  | current_statement                                         | last_statement                                            | last_statement_latency | lock_latency | rows_examined | rows_sent | rows_affected | tmp_tables | tmp_disk_tables | full_scan | last_wait                  | last_wait_latency | source            |
+--------+---------+---------------------------------+-----------+---------+--------------+-------+-----------------------------------------------------------+-----------------------------------------------------------+------------------------+--------------+---------------+-----------+---------------+------------+-----------------+-----------+----------------------------+-------------------+-------------------+
|      1 |    NULL | sql/main                        | NULL      | NULL    | System lock  | 15348 | INTERNAL DDL LOG RECOVER IN PROGRESS                      | NULL                                                      | NULL                   | NULL         |          NULL |      NULL |          NULL |       NULL |            NULL | NO        | NULL                       | NULL              | NULL              |
|     47 |      27 | root@localhost                  | test      | Query   | init         |     0 | insert into language(name) values("Ez00lbg0ueyrCQDOmo1K") | insert into language(name) values("Ez00lbg0ueyrCQDOmo1K") | 120.69 us              | 0 ps         |             0 |         0 |             1 |          0 |               0 | NO        | wait/io/table/sql/handler  | Still Waiting     | handler.cc:7267   |
|     21 |       1 | root@localhost                  | ps_helper | Query   | Sending data |     0 | select * from processlist                                 | NULL                                                      | NULL                   | 0 ps         |             0 |         0 |             0 |          1 |               0 | YES       | wait/io/file/sql/query_log | 10.28 us          | mf_iocache.c:1788 |
|     11 |    NULL | innodb/io_handler_thread        | NULL      | NULL    | NULL         |  NULL | NULL                                                      | NULL                                                      | NULL                   | NULL         |          NULL |      NULL |          NULL |       NULL |            NULL | NO        | NULL                       | NULL              | NULL              |
|     20 |    NULL | sql/con_sockets                 | NULL      | NULL    | NULL         |  NULL | NULL                                                      | NULL                                                      | NULL                   | NULL         |          NULL |      NULL |          NULL |       NULL |            NULL | NO        | NULL                       | NULL              | NULL              |
|      3 |    NULL | innodb/io_handler_thread        | NULL      | NULL    | NULL         |  NULL | NULL                                                      | NULL                                                      | NULL                   | NULL         |          NULL |      NULL |          NULL |       NULL |            NULL | NO        | NULL                       | NULL              | NULL              |
|     13 |    NULL | innodb/srv_lock_timeout_thread  | NULL      | NULL    | NULL         |  NULL | NULL                                                      | NULL                                                      | NULL                   | NULL         |          NULL |      NULL |          NULL |       NULL |            NULL | NO        | NULL                       | NULL              | NULL              |
|      4 |    NULL | innodb/io_handler_thread        | NULL      | NULL    | NULL         |  NULL | NULL                                                      | NULL                                                      | NULL                   | NULL         |          NULL |      NULL |          NULL |       NULL |            NULL | NO        | NULL                       | NULL              | NULL              |
|     14 |    NULL | innodb/srv_error_monitor_thread | NULL      | NULL    | NULL         |  NULL | NULL                                                      | NULL                                                      | NULL                   | NULL         |          NULL |      NULL |          NULL |       NULL |            NULL | NO        | NULL                       | NULL              | NULL              |
|      5 |    NULL | innodb/io_handler_thread        | NULL      | NULL    | NULL         |  NULL | NULL                                                      | NULL                                                      | NULL                   | NULL         |          NULL |      NULL |          NULL |       NULL |            NULL | NO        | NULL                       | NULL              | NULL              |
|     15 |    NULL | innodb/srv_monitor_thread       | NULL      | NULL    | NULL         |  NULL | NULL                                                      | NULL                                                      | NULL                   | NULL         |          NULL |      NULL |          NULL |       NULL |            NULL | NO        | NULL                       | NULL              | NULL              |
|      6 |    NULL | innodb/io_handler_thread        | NULL      | NULL    | NULL         |  NULL | NULL                                                      | NULL                                                      | NULL                   | NULL         |          NULL |      NULL |          NULL |       NULL |            NULL | NO        | NULL                       | NULL              | NULL              |
|     16 |    NULL | innodb/srv_master_thread        | NULL      | NULL    | NULL         |  NULL | NULL                                                      | NULL                                                      | NULL                   | NULL         |          NULL |      NULL |          NULL |       NULL |            NULL | NO        | NULL                       | NULL              | NULL              |
|      7 |    NULL | innodb/io_handler_thread        | NULL      | NULL    | NULL         |  NULL | NULL                                                      | NULL                                                      | NULL                   | NULL         |          NULL |      NULL |          NULL |       NULL |            NULL | NO        | NULL                       | NULL              | NULL              |
|     17 |    NULL | innodb/srv_purge_thread         | NULL      | NULL    | NULL         |  NULL | NULL                                                      | NULL                                                      | NULL                   | NULL         |          NULL |      NULL |          NULL |       NULL |            NULL | NO        | NULL                       | NULL              | NULL              |
|      8 |    NULL | innodb/io_handler_thread        | NULL      | NULL    | NULL         |  NULL | NULL                                                      | NULL                                                      | NULL                   | NULL         |          NULL |      NULL |          NULL |       NULL |            NULL | NO        | NULL                       | NULL              | NULL              |
|     18 |    NULL | innodb/page_cleaner_thread      | NULL      | NULL    | NULL         |  NULL | NULL                                                      | NULL                                                      | NULL                   | NULL         |          NULL |      NULL |          NULL |       NULL |            NULL | NO        | NULL                       | NULL              | NULL              |
|      9 |    NULL | innodb/io_handler_thread        | NULL      | NULL    | NULL         |  NULL | NULL                                                      | NULL                                                      | NULL                   | NULL         |          NULL |      NULL |          NULL |       NULL |            NULL | NO        | NULL                       | NULL              | NULL              |
|     19 |    NULL | sql/shutdown                    | NULL      | NULL    | NULL         |  NULL | NULL                                                      | NULL                                                      | NULL                   | NULL         |          NULL |      NULL |          NULL |       NULL |            NULL | NO        | NULL                       | NULL              | NULL              |
|      2 |    NULL | innodb/io_handler_thread        | NULL      | NULL    | NULL         |  NULL | NULL                                                      | NULL                                                      | NULL                   | NULL         |          NULL |      NULL |          NULL |       NULL |            NULL | NO        | NULL                       | NULL              | NULL              |
|     10 |    NULL | innodb/io_handler_thread        | NULL      | NULL    | NULL         |  NULL | NULL                                                      | NULL                                                      | NULL                   | NULL         |          NULL |      NULL |          NULL |       NULL |            NULL | NO        | NULL                       | NULL              | NULL              |
+--------+---------+---------------------------------+-----------+---------+--------------+-------+-----------------------------------------------------------+-----------------------------------------------------------+------------------------+--------------+---------------+-----------+---------------+------------+-----------------+-----------+----------------------------+-------------------+-------------------+
21 rows in set (0.00 sec)

mysql>

ユーザー処理を確認出来る。

mysql> select * from user_summary;
+------+------------------+---------------+-------------+---------------------+-------------------+--------------+
| user | total_statements | total_latency | avg_latency | current_connections | total_connections | unique_hosts |
+------+------------------+---------------+-------------+---------------------+-------------------+--------------+
| root |           680477 | 00:01:05.15   | 95.75 us    |                   2 |                29 |            1 |
+------+------------------+---------------+-------------+---------------------+-------------------+--------------+
1 row in set (0.00 sec)

mysql> select * from user_summary_by_stages;
+------+-------------------------------------+---------+-----------+-----------+
| user | event_name                          | count   | wait_sum  | wait_avg  |
+------+-------------------------------------+---------+-----------+-----------+
| root | stage/sql/freeing items             |  680402 | 25.96 s   | 37.77 us  |
| root | stage/sql/init                      | 1360606 | 19.15 s   | 13.96 us  |
| root | stage/sql/update                    |  680000 | 10.80 s   | 15.60 us  |
| root | stage/sql/Opening tables            |  681688 | 2.47 s    | 3.28 us   |
| root | stage/sql/closing tables            |  680386 | 1.63 s    | 2.05 us   |
| root | stage/sql/System lock               |  680230 | 1.59 s    | 2.05 us   |
| root | stage/sql/query end                 |  680386 | 922.56 ms | 1.23 us   |
| root | stage/sql/checking permissions      |  680609 | 669.60 ms | 821.02 ns |
| root | stage/sql/Sending data              |     150 | 484.30 ms | 3.23 ms   |
| root | stage/sql/cleaning up               |  680458 | 468.11 ms | 410.51 ns |
| root | stage/sql/Creating sort index       |      53 | 327.28 ms | 6.17 ms   |
| root | stage/sql/end                       |  680144 | 250.31 ms | 0 ps      |
| root | stage/sql/removing tmp table        |     147 | 21.33 ms  | 144.91 us |
| root | stage/sql/executing                 |     156 | 14.14 ms  | 90.31 us  |
| root | stage/sql/explaining                |       8 | 8.55 ms   | 1.07 ms   |
| root | stage/sql/converting HEAP to MyISAM |       1 | 6.44 ms   | 6.44 ms   |
| root | stage/sql/Creating tmp table        |      36 | 4.87 ms   | 135.06 us |
| root | stage/sql/preparing                 |     158 | 3.86 ms   | 24.22 us  |
| root | stage/sql/statistics                |     158 | 3.39 ms   | 21.35 us  |
| root | stage/sql/optimizing                |     164 | 1.18 ms   | 6.98 us   |
| root | stage/sql/Sorting for group         |       5 | 297.21 us | 59.11 us  |
| root | stage/sql/Sorting result            |      45 | 110.84 us | 2.46 us   |
| root | stage/sql/updating                  |       1 | 17.65 us  | 17.65 us  |
+------+-------------------------------------+---------+-----------+-----------+
23 rows in set (0.00 sec)

mysql>

I/O処理を確認出来る。
IOスレッド,ファイルIO時間,ファイルIO量,IOイベント時間,処理ごとのIO

mysql> select * from io_by_thread_by_latency limit 0,1;
+----------------+------------+---------------+-------------+-------------+-------------+-----------+----------------+
| user           | count_star | total_latency | min_latency | avg_latency | max_latency | thread_id | processlist_id |
+----------------+------------+---------------+-------------+-------------+-------------+-----------+----------------+
| root@localhost |       7546 | 90.63 ms      | 210.12 ns   | 63.86 us    | 2.22 ms     |        21 |              1 |
+----------------+------------+---------------+-------------+-------------+-------------+-----------+----------------+
1 row in set (0.00 sec)

mysql>

mysql> select * from io_global_by_file_by_latency limit 0,1;
+----------------------+------------+---------------+------------+--------------+-------------+---------------+------------+--------------+
| file                 | count_star | total_latency | count_read | read_latency | count_write | write_latency | count_misc | misc_latency |
+----------------------+------------+---------------+------------+--------------+-------------+---------------+------------+--------------+
| @@datadir/mypc.log   |     680475 | 5.26 s        |          0 | 0 ps         |      680472 | 5.26 s        |          3 | 162.39 us    |
+----------------------+------------+---------------+------------+--------------+-------------+---------------+------------+--------------+
1 row in set (0.00 sec)

mysql>


mysql> select * from io_global_by_file_by_bytes limit 0,1;
+-----------------------------+------------+------------+----------+-------------+---------------+-----------+------------+-----------+
| file                        | count_read | total_read | avg_read | count_write | total_written | avg_write | total      | write_pct |
+-----------------------------+------------+------------+----------+-------------+---------------+-----------+------------+-----------+
| @@datadir/MyPC-bin.000026   |          2 | 120 bytes  | 60 bytes |       13556 | 105.71 MiB    | 7.99 KiB  | 105.71 MiB |    100.00 |
+-----------------------------+------------+------------+----------+-------------+---------------+-----------+------------+-----------+
1 row in set (0.00 sec)

mysql>


mysql> select * from io_global_by_wait_by_latency limit 0,1;
+---------------+------------+---------------+-------------+-------------+--------------+---------------+--------------+------------+------------+----------+-------------+---------------+-------------+
| event_name    | count_star | total_latency | avg_latency | max_latency | read_latency | write_latency | misc_latency | count_read | total_read | avg_read | count_write | total_written | avg_written |
+---------------+------------+---------------+-------------+-------------+--------------+---------------+--------------+------------+------------+----------+-------------+---------------+-------------+
| sql/query_log |     680479 | 5.26 s        | 7.74 us     | 61.26 ms    | 0 ps         | 5.26 s        | 162.39 us    |          0 | 0 bytes    | 0 bytes  |      680476 | 46.78 MiB     | 72 bytes    |
+---------------+------------+---------------+-------------+-------------+--------------+---------------+--------------+------------+------------+----------+-------------+---------------+-------------+
1 row in set (0.00 sec)

mysql>

mysql> select * from latest_file_io limit 0,10;
+------------------------+------------------------------------------------+-----------+-----------+-----------+
| thread                 | file                                           | latency   | operation | requested |
+------------------------+------------------------------------------------+-----------+-----------+-----------+
| root@localhost:50115:1 | @@datadir/MyPC.log                             | 7.45 us   | write     | 41 bytes  |
| root@localhost:50115:1 | @@datadir/MyPC.log                             | 20.00 us  | write     | 55 bytes  |
| root@localhost:50115:1 | @@datadir/MyPC.log                             | 29.31 us  | write     | 55 bytes  |
| root@localhost:50115:1 | @@datadir/MyPC.log                             | 26.59 us  | write     | 55 bytes  |
| root@localhost:50115:1 | @@datadir/MyPC.log                             | 28.61 us  | write     | 41 bytes  |
| root@localhost:50115:1 | @@datadir/MyPC.log                             | 30.14 us  | write     | 64 bytes  |
| root@localhost:50115:1 | @@datadir/MyPC.log                             | 26.89 us  | write     | 55 bytes  |
| root@localhost:50115:1 | @@datadir/MyPC.log                             | 29.99 us  | write     | 65 bytes  |
| root@localhost:50115:1 | @@datadir/ps_helper/user_summary_by_stages.frm | 105.91 us | open      | NULL      |
| root@localhost:50115:1 | @@datadir/ps_helper/user_summary_by_stages.frm | 22.66 us  | read      | 64 bytes  |
+------------------------+------------------------------------------------+-----------+-----------+-----------+
10 rows in set (0.03 sec)

mysql>

スキーマ毎のオブジェクト数を確認出来る。

mysql> show create table schema_object_overview\G
*************************** 1. row ***************************
                View: schema_object_overview
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` 
SQL SECURITY INVOKER VIEW `schema_object_overview` AS select `information_schema`.`routines`.`ROUTINE_SCHEMA` AS `db`,
`information_schema`.`routines`.`ROUTINE_TYPE` AS `object_type`,count(0) AS `count` from `information_schema`.`routines` 
group by `information_schema`.`routines`.`ROUTINE_SCHEMA`,`information_schema`.`routines`.`ROUTINE_TYPE` union 
select `information_schema`.`tables`.`TABLE_SCHEMA` AS `TABLE_SCHEMA`,`information_schema`.`tables`.`TABLE_TYPE` AS `TABLE_TYPE`,
count(0) AS `COUNT(*)` from `information_schema`.`tables` group by `information_schema`.`tables`.`TABLE_SCHEMA`,
`information_schema`.`tables`.`TABLE_TYPE` union select `information_schema`.`statistics`.`TABLE_SCHEMA` AS `TABLE_SCHEMA`,
concat('INDEX (',`information_schema`.`statistics`.`INDEX_TYPE`,')') AS `CONCAT('INDEX (', INDEX_TYPE, ')')`,
count(0) AS `COUNT(*)` from `information_schema`.`statistics` group by `information_schema`.`statistics`.`TABLE_SCHEMA`,
`information_schema`.`statistics`.`INDEX_TYPE` union select `information_schema`.`triggers`.`TRIGGER_SCHEMA` AS `TRIGGER_SCHEMA`,
'TRIGGER' AS `TRIGGER`,count(0) AS `COUNT(*)` from `information_schema`.`triggers` 
group by `information_schema`.`triggers`.`TRIGGER_SCHEMA` union select `information_schema`.`events`.`EVENT_SCHEMA` AS `EVENT_SCHEMA`,
'EVENT' AS `EVENT`,count(0) AS `COUNT(*)` from `information_schema`.`events` 
group by `information_schema`.`events`.`EVENT_SCHEMA` order by `DB`,`OBJECT_TYPE`
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.01 sec)

mysql>


mysql> select * from schema_object_overview;
+--------------------+------------------+-------+
| db                 | object_type      | count |
+--------------------+------------------+-------+
| copy_test          | BASE TABLE       |     5 |
| copy_test          | INDEX (BTREE)    |     7 |
| information_schema | SYSTEM VIEW      |    59 |
| mysql              | BASE TABLE       |    28 |
| mysql              | INDEX (BTREE)    |    63 |
| performance_schema | BASE TABLE       |    52 |
| ps_helper          | FUNCTION         |     8 |
| ps_helper          | PROCEDURE        |    12 |
| ps_helper          | VIEW             |    53 |
| sakila             | BASE TABLE       |    16 |
| sakila             | FUNCTION         |     3 |
| sakila             | INDEX (BTREE)    |    45 |
| sakila             | INDEX (FULLTEXT) |     2 |
| sakila             | PROCEDURE        |     3 |
| sakila             | TRIGGER          |     6 |
| sakila             | VIEW             |     7 |
| sys                | FUNCTION         |     8 |
| sys                | PROCEDURE        |    16 |
| sys                | VIEW             |    63 |
| test               | BASE TABLE       |     5 |
| test               | INDEX (BTREE)    |     8 |
| world              | BASE TABLE       |     3 |
| world              | INDEX (BTREE)    |     4 |
+--------------------+------------------+-------+
23 rows in set (0.55 sec)

mysql> 

実行時間がかかっているSQLを確認。
performance_schema.events_statements_summary_by_digestからselectするのと同じ。

mysql> select * from statement_analysis limit 0,1;
+-------------------------------------------+------+-----------+------------+-----------+------------+---------------+-------------+-------------+--------------+-----------+---------------+---------------+-------------------+------------+-----------------+-------------+-------------------+----------------------------------+---------------------+---------------------+
| query                                     | db   | full_scan | exec_count | err_count | warn_count | total_latency | max_latency | avg_latency | lock_latency | rows_sent | rows_sent_avg | rows_examined | rows_examined_avg | tmp_tables | tmp_disk_tables | rows_sorted | sort_merge_passes | digest                           | first_seen          | last_seen           |
+-------------------------------------------+------+-----------+------------+-----------+------------+---------------+-------------+-------------+--------------+-----------+---------------+---------------+-------------------+------------+-----------------+-------------+-------------------+----------------------------------+---------------------+---------------------+
| INSERT INTO LANGUAGE ( NAME ) VALUES (?)  | test |           |     680000 |         0 |          0 | 00:01:03.32   | 61.76 ms    | 92.78 us    | 18.98 s      |         0 |             0 |             0 |                 0 |          0 |               0 |           0 |                 0 | 9c2953f8e62dc70ec329b2b787819a46 | 2014-08-08 09:14:27 | 2014-08-08 13:45:27 |
+-------------------------------------------+------+-----------+------------+-----------+------------+---------------+-------------+-------------+--------------+-----------+---------------+---------------+-------------------+------------+-----------------+-------------+-------------------+----------------------------------+---------------------+---------------------+
1 row in set (0.00 sec)

mysql>

FULL TABLEスキャンでインデックスが使えてないQueryを特定。

mysql> select * from statements_with_full_table_scans limit 0,3;
+-------------------------------------------------------------------+--------------------+------------+---------------------+--------------------------+-------------------+-----------+---------------+---------------+-------------------+---------------------+---------------------+----------------------------------+
| query                                                             | db                 | exec_count | no_index_used_count | no_good_index_used_count | no_index_used_pct | rows_sent | rows_examined | rows_sent_avg | rows_examined_avg | first_seen          | last_seen           | digest                           |
+-------------------------------------------------------------------+--------------------+------------+---------------------+--------------------------+-------------------+-----------+---------------+---------------+-------------------+---------------------+---------------------+----------------------------------+
| SELECT * FROM `user_summary` S ... em` . `SUM_TIMER_WAIT` ) DESC  | ps_helper          |         12 |                  12 |                        0 |               100 |        12 |          4020 |             1 |               335 | 2014-08-08 13:17:16 | 2014-08-08 13:46:02 | aa63ec3352becb56cdb68b82fe669d5b |
| SELECT * FROM `events_statements_current`                         | performance_schema |          8 |                   8 |                        0 |               100 |        12 |            12 |             2 |                 2 | 2014-08-08 09:58:55 | 2014-08-08 10:00:19 | 6aa57f9427136fc389e84be571cad650 |
| SELECT `EVENT_NAME` , `COUNT_S ...  BY `COUNT_STAR` DESC LIMIT ?  | performance_schema |          7 |                   7 |                        0 |               100 |        70 |          1995 |            10 |               285 | 2014-08-08 09:11:48 | 2014-08-08 11:21:03 | a629020ce748ea0b8845ad529e26935e |
+-------------------------------------------------------------------+--------------------+------------+---------------------+--------------------------+-------------------+-----------+---------------+---------------+-------------------+---------------------+---------------------+----------------------------------+
3 rows in set (0.00 sec)

mysql>

Temp Tableを最も使用しているQueryを特定

mysql> select * from statements_with_temp_tables limit 0,3;
+-------------------------------------------------------------------+-----------+------------+-------------------+-----------------+--------------------------+------------------------+---------------------+---------------------+----------------------------------+
| query                                                             | db        | exec_count | memory_tmp_tables | disk_tmp_tables | avg_tmp_tables_per_query | tmp_tables_to_disk_pct | first_seen          | last_seen           | digest                           |
+-------------------------------------------------------------------+-----------+------------+-------------------+-----------------+--------------------------+------------------------+---------------------+---------------------+----------------------------------+
| SELECT * FROM `schema_object_o ... MA` , `information_schema` ... | ps_helper |          1 |               189 |              33 |                      189 |                     17 | 2014-08-08 14:00:31 | 2014-08-08 14:00:31 | 54f9bd520f0bbf15db0c2ed93386bec9 |
| SHOW CREATE TABLE `schema_obje ... istics` . `TABLE_SCHEMA` , ... | ps_helper |          3 |                21 |               9 |                        7 |                     43 | 2014-08-08 14:01:41 | 2014-08-08 14:02:11 | 3ee60de4f4e84b761149e92903897574 |
| EXPLAIN SELECT * FROM `innodb_ ...  . `COMPRESSED_SIZE` ) ) DESC  | ps_helper |          3 |                12 |               3 |                        4 |                     25 | 2014-08-08 13:22:17 | 2014-08-08 13:22:52 | 6297d8cbe1e79362ec755bc21886e09d |
+-------------------------------------------------------------------+-----------+------------+-------------------+-----------------+--------------------------+------------------------+---------------------+---------------------+----------------------------------+
3 rows in set (0.00 sec)

mysql>

パフォーマンス・スキーマ:MySQLサーバーの稼働統計を確認可能
A collection of scripts to help MySQL DBAs


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


RDBMSを運用していて、Queryでオブジェクトの確認をしたい場合がありますが、
information_schema.table_constraintsでは外部キーなどのオブジェクト依存関係を
確認する事が出来ます。

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

mysql>

特定データベースにてFK制約を確認する方法。

mysql> SELECT * FROM information_schema.table_constraints
    -> WHERE table_schema = 'sakila'
    -> AND constraint_type='FOREIGN KEY';
+--------------------+-------------------+---------------------------+--------------+---------------+-----------------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME           | TABLE_SCHEMA | TABLE_NAME    | CONSTRAINT_TYPE |
+--------------------+-------------------+---------------------------+--------------+---------------+-----------------+
| def                | sakila            | fk_address_city           | sakila       | address       | FOREIGN KEY     |
| def                | sakila            | fk_city_country           | sakila       | city          | FOREIGN KEY     |
| def                | sakila            | fk_customer_address       | sakila       | customer      | FOREIGN KEY     |
| def                | sakila            | fk_customer_store         | sakila       | customer      | FOREIGN KEY     |
| def                | sakila            | fk_film_language          | sakila       | film          | FOREIGN KEY     |
| def                | sakila            | fk_film_language_original | sakila       | film          | FOREIGN KEY     |
| def                | sakila            | fk_film_actor_actor       | sakila       | film_actor    | FOREIGN KEY     |
| def                | sakila            | fk_film_actor_film        | sakila       | film_actor    | FOREIGN KEY     |
| def                | sakila            | fk_film_category_category | sakila       | film_category | FOREIGN KEY     |
| def                | sakila            | fk_film_category_film     | sakila       | film_category | FOREIGN KEY     |
| def                | sakila            | fk_inventory_film         | sakila       | inventory     | FOREIGN KEY     |
| def                | sakila            | fk_inventory_store        | sakila       | inventory     | FOREIGN KEY     |
| def                | sakila            | fk_payment_customer       | sakila       | payment       | FOREIGN KEY     |
| def                | sakila            | fk_payment_rental         | sakila       | payment       | FOREIGN KEY     |
| def                | sakila            | fk_payment_staff          | sakila       | payment       | FOREIGN KEY     |
| def                | sakila            | fk_rental_customer        | sakila       | rental        | FOREIGN KEY     |
| def                | sakila            | fk_rental_inventory       | sakila       | rental        | FOREIGN KEY     |
| def                | sakila            | fk_rental_staff           | sakila       | rental        | FOREIGN KEY     |
| def                | sakila            | fk_staff_address          | sakila       | staff         | FOREIGN KEY     |
| def                | sakila            | fk_staff_store            | sakila       | staff         | FOREIGN KEY     |
| def                | sakila            | fk_store_address          | sakila       | store         | FOREIGN KEY     |
| def                | sakila            | fk_store_staff            | sakila       | store         | FOREIGN KEY     |
+--------------------+-------------------+---------------------------+--------------+---------------+-----------------+
22 rows in set (0.00 sec)

特定のテーブルの外部キー制約のみ確認

mysql> SELECT * FROM information_schema.table_constraints
    -> WHERE table_schema = 'sakila'
    -> AND table_name = 'customer'
    -> AND constraint_type='FOREIGN KEY';
+--------------------+-------------------+---------------------+--------------+------------+-----------------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME     | TABLE_SCHEMA | TABLE_NAME | CONSTRAINT_TYPE |
+--------------------+-------------------+---------------------+--------------+------------+-----------------+
| def                | sakila            | fk_customer_address | sakila       | customer   | FOREIGN KEY     |
| def                | sakila            | fk_customer_store   | sakila       | customer   | FOREIGN KEY     |
+--------------------+-------------------+---------------------+--------------+------------+-----------------+
2 rows in set (0.00 sec)

特定のテーブルの制約を確認

mysql> SELECT * FROM information_schema.table_constraints
    -> WHERE table_schema = 'sakila'
    -> AND table_name = 'customer';
+--------------------+-------------------+---------------------+--------------+------------+-----------------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME     | TABLE_SCHEMA | TABLE_NAME | CONSTRAINT_TYPE |
+--------------------+-------------------+---------------------+--------------+------------+-----------------+
| def                | sakila            | PRIMARY             | sakila       | customer   | PRIMARY KEY     |
| def                | sakila            | fk_customer_address | sakila       | customer   | FOREIGN KEY     |
| def                | sakila            | fk_customer_store   | sakila       | customer   | FOREIGN KEY     |
+--------------------+-------------------+---------------------+--------------+------------+-----------------+
3 rows in set (0.00 sec)

mysql>



SELECT TABLE_SCHEMA,TABLE_NAME,ENGINE
FROM information_schema.tables
where TABLE_SCHEMA = 'information_schema';

mysql55_information_schema

■CHARACTER_SETS | MEMORY | 利用できる文字セットに関する情報
■COLLATIONS | MEMORY | 各文字セットの照合に関する情報
■COLLATION_CHARACTER_SET_APPLICABILITY | MEMORY | どの文字セットがどの照合に適用できるかを示します
■COLUMNS | MyISAM | テーブルのカラムに関する情報
■COLUMN_PRIVILEGES | MEMORY | カラムの権限に関する情報
■ENGINES | MEMORY | ストレージ エンジンに関する情報
■EVENTS | MyISAM | 計画したイベントに関する情報
■FILES | MEMORY | NDBディスクデータテーブルが保存されるファイルに関する情報
■GLOBAL_STATUS | MEMORY | サーバーのステータス変数に関する情報(GLOBAL)
■GLOBAL_VARIABLES | MEMORY | サーバーのステータス変数に関する情報(GLOBAL)
■KEY_COLUMN_USAGE | MEMORY | どのキーカラムに制約があるかを説明
■PARTITIONS | MyISAM | テーブルの分割に関する情報 (パーティショニング)
■PLUGINS | MyISAM | サーバーのプラグインに関する情報
■PROCESSLIST | MyISAM | 動作しているスレッドに関する情報
■PROFILING | MEMORY | プロファイリング情報(SHOW PROFILES/SHOW PROFILE)
■REFERENTIAL_CONSTRAINTS | MEMORY | 外部キーに関する情報
■ROUTINES | MyISAM | 保存されたルーチンに関する情報
■SCHEMATA | MEMORY | データベースに関する情報
■SCHEMA_PRIVILEGES | MEMORY | スキーマ(データベース)権限に関する情報
■SESSION_STATUS | MEMORY | サーバーのステータス変数に関する情報(SESSION)
■SESSION_VARIABLES | MEMORY | サーバーのステータス変数に関する情報(SESSION)
■STATISTICS | MEMORY | テーブル インデックスの情報
■TABLES | MEMORY | データベースのテーブルに関する情報
■TABLE_CONSTRAINTS | MEMORY | テーブルに制約があるか情報
■TABLE_PRIVILEGES | MEMORY | テーブル権限に関する情報
■TRIGGERS | MyISAM | トリガに関する情報
■USER_PRIVILEGES | MEMORY | グルーバル権限に関する情報
■VIEWS | MyISAM | データベースの表示に関する情報
■INNODB_CMP_RESET | MEMORY | InnoDB Plugin関連(圧縮テーブルに関する情報/統計)
■INNODB_TRX | MEMORY | InnoDB Plugin関連(実行中のトランザクション)
■INNODB_CMPMEM_RESET | MEMORY | InnoDB Plugin関連(Buffer pool内の圧縮されたPageに関する情報)
■INNODB_LOCK_WAITS | MEMORY | InnoDB Plugin関連(Blocking/Requestingトランザクション情報)
■INNODB_CMPMEM | MEMORY | InnoDB Plugin関連(Buffer pool内の圧縮されたPageに関する情報)
■INNODB_CMP | MEMORY | InnoDB Plugin関連(圧縮テーブルに関する情報/統計)
■INNODB_LOCKS | MEMORY | InnoDB Plugin関連(ロック競合が発生しているトランザクション)

INFORMATION_SCHEMA ON MYSQL5.5 (基本)
information_schema_55_basic

INFORMATION_SCHEMA ON MYSQL5.5 (INNODB_PLUGIN関連)
information_schema_55_innodb_p

参考サイト
————————————————————————
Chapter 6. InnoDB INFORMATION_SCHEMA tables


用途が無いので、業務で利用した事はありませんが以下のように
2つのテーブルをFROM句のあとに置くとデータは以下のようにそれぞれの
列を掛け算した分だけ結果が返ってくる。

以下の例だとそれぞれのテーブルに4件ずつのデータが入っているので
4X4=16件のデータが表示されます。これがもしデータ量が多いテーブルだと
相当時間がかかり重たくなります。

select * from MYSQLIMP,MYSQLIMP2;
select_join

データ量が多いテーブルの場合
mysql> SET @CityCount = (SELECT COUNT(*) FROM City);
Query OK, 0 rows affected (0.00 sec)

mysql> SET @CountryCount = (SELECT COUNT(*) FROM Country);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @CityCount * @CountryCount;
+—————————-+
| @CityCount * @CountryCount |
+—————————-+
| 979440 |
+—————————-+
1 row in set (0.00 sec)

979440件のデータが表示されると重たい…
select_all1

INFORMATION_SCHEMAのテーブルを利用してテーブル結合の検証

information_schema_tables_join

mysql> select count(*) from SCHEMATA;
+———-+
| count(*) |
+———-+
| 9 |
+———-+
1 row in set (0.00 sec)

mysql> select count(*) from TABLES;
+———-+
| count(*) |
+———-+
| 159 |
+———-+
1 row in set (0.01 sec)

mysql> set @SCHEMATA = (select count(*) from SCHEMATA);
Query OK, 0 rows affected (0.01 sec)

mysql> set @TABLES = (select count(*) from TABLES);
Query OK, 0 rows affected (0.01 sec)

mysql> select count(*) from SCHEMATA,TABLES;
+———-+
| count(*) |
+———-+
| 1431 |
+———-+
1 row in set (0.01 sec)

mysql> select @SCHEMATA * @TABLES;
+———————+
| @SCHEMATA * @TABLES |
+———————+
| 1431 |
+———————+
1 row in set (0.00 sec)

select_total

SCHEMATAには9件のデータがありTABLESテーブルには159件のデータがあるので、
以下のようなwhere句の条件だと多いデータの多いTABLESの列分(159件)だけデータが表示される。
mysql> select count(*) from SCHEMATA,TABLES
-> where SCHEMATA.SCHEMA_NAME = TABLES.TABLE_SCHEMA;

+———-+
| count(*) |
+———-+
| 159 |
+———-+
1 row in set (0.01 sec)

mysql>

例えば、上記2つのテーブルを利用すると以下のような運用で利用出来る確認用クエリーが作成出来る。
desc_tables

SCHEMA_NAME,テーブルのリストを作成したり

select
SCHEMA_NAME,DEFAULT_COLLATION_NAME,
ENGINE,TABLE_TYPE
from SCHEMATA,TABLES
where SCHEMATA.SCHEMA_NAME = TABLES.TABLE_SCHEMA;

schemeta_table
159 rows in set (0.05 sec)


自分が作成した、又は他の人が作成したprocedureの内容を確認したい場合が
業務上あるかと思います。そんな時は以下の方法で確認する事が出来ます。

その1) information_schemaから情報を呼び出す。
該当するテーブルはROUTINESテーブルになります。

information_schema_routines


select ROUTINE_NAME,ROUTINE_DEFINITION
from information_schema.ROUTINES
where ROUTINE_SCHEMA = 'STUDY'
AND ROUTINE_NAME = 'DO_WHILE_TEST'\G

routines

その2) SHOW CREATEステートメントにて確認(information_schemaから情報を得ている)

SHOW CREATE PROCEDURE STUDY.DO_WHILE_TEST\G

show_create_procedure

おまけ) SHOW PROCEDURE STATUSでは内容の確認は出来ないが状態を確認出来る。
SHOW PROCEDURE STATUS LIKE 'DO_WHILE_TEST'\G

show_procedure_status1

参考サイト

21.14. INFORMATION_SCHEMA ROUTINES テーブル


DATABASEのCOLLATION

作成するときにDBのCOLLATIONを決めるが、既に作成してあるデータベースの
COLLATIONを確認する方法をいかに3つメモしておきます。

—————————————————————————-
1: SHOW CREATE DATABASEで確認
—————————————————————————-


mysql> show create database STUDY;
+----------+----------------------------------------------------------------+
| Database | Create Database |
+----------+----------------------------------------------------------------+
| STUDY | CREATE DATABASE `STUDY` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+----------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

database_collation

—————————————————————————-
2: データベースのフォルダー以下にあるdb.optファイルで確認。
—————————————————————————-

[root@colinux STUDY]# pwd
/usr/local/mysql/data/STUDY
[root@colinux STUDY]# ls -l db.opt
-rw-rw—- 1 mysql mysql 61 2009-07-11 23:06 db.opt
[root@colinux STUDY]# cat db.opt
default-character-set=utf8
default-collation=utf8_general_ci
[root@colinux STUDY]#

opt

—————————————————————————-
3: information_schemaを確認
—————————————————————————-

mysql> desc SCHEMATA;
+----------------------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------------------+--------------+------+-----+---------+-------+
| CATALOG_NAME | varchar(512) | YES | | NULL | |
| SCHEMA_NAME | varchar(64) | NO | | | |
| DEFAULT_CHARACTER_SET_NAME | varchar(64) | NO | | | |
| DEFAULT_COLLATION_NAME | varchar(64) | NO | | | |
| SQL_PATH | varchar(512) | YES | | NULL | |
+----------------------------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

mysql> select SCHEMA_NAME,DEFAULT_CHARACTER_SET_NAME,DEFAULT_COLLATION_NAME from information_schema.SCHEMATA;
+--------------------+----------------------------+------------------------+
| SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME |
+--------------------+----------------------------+------------------------+
| information_schema | utf8 | utf8_general_ci |
| CaseSensitive | utf8 | utf8_general_ci |
| DB01 | utf8 | utf8_general_ci |
| DB02 | utf8 | utf8_general_ci |
| MyAdmin | utf8 | utf8_general_ci |
| STUDY | utf8 | utf8_general_ci |
| TEST | utf8 | utf8_general_ci |
| client_test_db | utf8 | utf8_general_ci |
| mysql | utf8 | utf8_general_ci |
+--------------------+----------------------------+------------------------+
9 rows in set (0.01 sec)

mysql>
mysql> desc SCHEMATA;
+----------------------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------------------+--------------+------+-----+---------+-------+
| CATALOG_NAME | varchar(512) | YES | | NULL | |
| SCHEMA_NAME | varchar(64) | NO | | | |
| DEFAULT_CHARACTER_SET_NAME | varchar(64) | NO | | | |
| DEFAULT_COLLATION_NAME | varchar(64) | NO | | | |
| SQL_PATH | varchar(512) | YES | | NULL | |
+----------------------------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

mysql> select SCHEMA_NAME,DEFAULT_CHARACTER_SET_NAME,DEFAULT_COLLATION_NAME from information_schema.SCHEMATA;
+--------------------+----------------------------+------------------------+
| SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME |
+--------------------+----------------------------+------------------------+
| information_schema | utf8 | utf8_general_ci |
| CaseSensitive | utf8 | utf8_general_ci |
| DB01 | utf8 | utf8_general_ci |
| DB02 | utf8 | utf8_general_ci |
| MyAdmin | utf8 | utf8_general_ci |
| STUDY | utf8 | utf8_general_ci |
| TEST | utf8 | utf8_general_ci |
| client_test_db | utf8 | utf8_general_ci |
| mysql | utf8 | utf8_general_ci |
+--------------------+----------------------------+------------------------+
9 rows in set (0.01 sec)

mysql>

schema_db

—————————————————————————–
追記: DBの文字コードとCOLLATION変更
—————————————————————————–

mysql> select SCHEMA_NAME,DEFAULT_CHARACTER_SET_NAME,DEFAULT_COLLATION_NAME from information_schema.SCHEMATA;
+--------------------+----------------------------+------------------------+
| SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME |
+--------------------+----------------------------+------------------------+
| information_schema | utf8 | utf8_general_ci |
| CaseSensitive | utf8 | utf8_general_ci |
| DB01 | utf8 | utf8_general_ci |
| DB02 | utf8 | utf8_general_ci |
| MyAdmin | utf8 | utf8_general_ci |
| STUDY | utf8 | utf8_general_ci |
| TEST | utf8 | utf8_general_ci |
| client_test_db | utf8 | utf8_general_ci |
| mysql | utf8 | utf8_general_ci |
+--------------------+----------------------------+------------------------+
9 rows in set (0.01 sec)

mysql> alter database TEST default character set latin1 COLLATE latin1_swedish_ci;
Query OK, 1 row affected (0.00 sec)

mysql> select SCHEMA_NAME,DEFAULT_CHARACTER_SET_NAME,DEFAULT_COLLATION_NAME from information_schema.SCHEMATA;
+--------------------+----------------------------+------------------------+
| SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME |
+--------------------+----------------------------+------------------------+
| information_schema | utf8 | utf8_general_ci |
| CaseSensitive | utf8 | utf8_general_ci |
| DB01 | utf8 | utf8_general_ci |
| DB02 | utf8 | utf8_general_ci |
| MyAdmin | utf8 | utf8_general_ci |
| STUDY | utf8 | utf8_general_ci |
| TEST | latin1 | latin1_swedish_ci |
| client_test_db | utf8 | utf8_general_ci |
| mysql | utf8 | utf8_general_ci |
+--------------------+----------------------------+------------------------+
9 rows in set (0.01 sec)

db.optも変わっている。

[root@colinux TEST]# pwd
/usr/local/mysql/data/TEST
[root@colinux TEST]# cat db.opt
default-character-set=latin1
default-collation=latin1_swedish_ci
[root@colinux TEST]#

alter_database1


TABLES テーブルはデータベースのテーブルに関する情報を提供します。


mysql> select TABLE_NAME,ROW_FORMAT from information_schema.TABLES
-> where TABLE_SCHEMA = 'DB01' and TABLE_NAME = 'TABLE911';
+------------+------------+
| TABLE_NAME | ROW_FORMAT |
+------------+------------+
| TABLE911 | Compact |
+------------+------------+
1 row in set (0.06 sec)

mysql>

どんな情報があるか?
tables

ROW-FORMAT情報など

rows


21.2. INFORMATION_SCHEMA TABLES テーブル


==============================================
まとめてデータベース内のテーブルストレージタイプをリスト
==============================================


SELECT table_name, table_type, engine
FROM information_schema.tables
WHERE table_schema = 'TEST'
ORDER BY table_name DESC;

my_info

==============================================
テーブルで利用されているインデックスの数リスト
==============================================


SELECT
t.table_schema,t.table_name
,COUNT( IF(c.column_key = 'PRI',1,NULL) ) AS PK
,COUNT( IF(c.column_key != 'PRI',1,NULL) ) AS POSSIBLE
FROM information_schema.tables t
LEFT JOIN information_schema.columns c
ON t.table_schema = c.table_schema
AND t.table_name = c.table_name
AND c.column_key != ''
WHERE t.table_type != 'VIEW'
AND t.table_schema = database()
GROUP BY t.table_name
ORDER BY table_schema, table_name;

te-buru_key

==============================================
DBオブジェクトリスト
==============================================


(SELECT
table_schema AS object_schema,
table_name AS object_name,
table_type AS object_type
FROM information_schema.tables
)
UNION ALL
(SELECT
routine_schema,
routine_name,
routine_type
FROM information_schema.routines )
UNION ALL
(SELECT
trigger_schema,
trigger_name,
'TRIGGER'
FROM information_schema.triggers )
ORDER BY object_schema, object_type, object_name;

obj