権限はMySQLのテーブルからSelectしていたので、
運用している時に利用した事はないけれども一応確認してみた。

[root@HOME001 bin]# ./mysqlaccess localhost slave_user test -u root -p
mysqlaccess Version 2.06, 20 Dec 2000
By RUG-AIV, by Yves Carlier (Yves.Carlier@rug.ac.be)
Changes by Steve Harvey (sgh@vex.net)
This software comes with ABSOLUTELY NO WARRANTY.

Warning: mysqlaccess is deprecated and will be removed in a future version.
Password for MySQL user slave_user:
Password for MySQL superuser root:

Sele Inse Upda Dele Crea Drop Relo Shut Proc File Gran Refe Inde Alte Show Supe Crea Lock Exec Repl Repl Crea Show Crea Alte Crea Even Trig Crea Ssl_ Ssl_ X509 X509 Max_ Max_ Max_ Max_ Plug Auth Pass | Host,User,DB
---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- + --------------------
 Y    Y    Y    Y    Y    Y    N    N    N    N    N    Y    Y    Y    N    N    Y    Y    N    Y    N    Y    Y    Y    N    N    Y    Y    N    ?    ?    ?    ?    0    0    0    0    ?    ?    N   | localhost,slave_user,test
 Y    Y    Y    Y    Y    Y    N    N    N    N    N    Y    Y    Y    N    N    Y    Y    N    N    N    Y    Y    Y    N    N    Y    Y    N    N    N    N    N    N    N    N    N    N    N    N   | localhost,ANY_NEW_USER,test

BUGs can be reported at http://bugs.mysql.com/
[root@HOME001 bin]# 

Warningsは時々確認するので、ON/OFFの設定を確認。

mysql> tee my20140829.log
Logging to file 'my20140829.log'
mysql> warnings
Show warnings enabled.
mysql> nowarning
Show warnings disabled.
mysql> \w
Show warnings disabled.
mysql> \W
Show warnings enabled.
mysql> notee
Outfile disabled.
mysql>

エラー表示確認 (Defaultで確認可能)

mysql> select * from TABLE001 order by id desc limit 0,1;
+----+-------------+--------------------+
| id | title       | comment            |
+----+-------------+--------------------+
| 30 | Maintenance | Maintenance 201408 |
+----+-------------+--------------------+
1 row in set (0.33 sec)

mysql> insert into TABLE001(title,comment) values('Maintenance','Maintenance 20140829');
Query OK, 1 row affected (0.35 sec)

mysql> desc TABLE001;
+---------+------------------+------+-----+---------+----------------+
| Field   | Type             | Null | Key | Default | Extra          |
+---------+------------------+------+-----+---------+----------------+
| id      | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| title   | varchar(20)      | YES  |     | NULL    |                |
| comment | varchar(100)     | YES  |     | NULL    |                |
+---------+------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> insert into TABLE001(id,title,comment) values(31,'Maintenance','Maintenance 20140829');
ERROR 1062 (23000): Duplicate entry '31' for key 'PRIMARY'
mysql> show warnings;
+-------+------+----------------------------------------+
| Level | Code | Message                                |
+-------+------+----------------------------------------+
| Error | 1062 | Duplicate entry '31' for key 'PRIMARY' |
+-------+------+----------------------------------------+
1 row in set (0.00 sec)

ついでに、Teeで出したファイルも確認。

mysql> system ls
RPM-GPG-KEY.dag.txt  anaconda-ks.cfg  install.log  install.log.syslog  my20140829.log  rpmforge-release-0.5.2-2.el5.rf.i386.rpm
mysql>

[root@HOME001 ~]# cat my20140829.log
mysql> warnings
Show warnings enabled.
mysql> nowarning
Show warnings disabled.
mysql> \w
Show warnings disabled.
mysql> \W
Show warnings enabled.
mysql> notee
[root@HOME001 ~]#

MySQLの実行プランのベースとなるデータ確認
-found_records: total amount of read records.
-read_time: assumption of total disk access times.

■ テーブルの情報

mysql> show table status like 'language'\G
*************************** 1. row ***************************
           Name: language
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 3560363   (found_recordsの値)
 Avg_row_length: 51
    Data_length: 183156736 (rad_time=Data_Length/16KB)
Max_data_length: 0
   Index_length: 0
      Data_free: 4194304
 Auto_increment: 3950305
    Create_time: 2014-08-04 17:59:03
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

mysql>

■フルスキャンのコスト
found_recordsの確認(Rows),InnoDBのテーブル統計はSamplingの為,
正確にcount(*)の値と同じになってないようです。
Data_lengthを16KBで割るとread_timeが出るそうです。
read_time = (183,156,736/(16*1024))=11,179という事でしょうか。

コスト=read_time+found_records*ROW_EVALUATE_COST
found_records: 3,560,363
read_time: 183,156,736/(16*1024)
ROW_EVALUATE_COST: 0.20

mysql> select (183156736/(16*1024))+3560363*0.2;
+-----------------------------------+
| (183156736/(16*1024))+3560363*0.2 |
+-----------------------------------+
|                       723251.6000 |
+-----------------------------------+
1 row in set (0.00 sec)

実行プランとコストの確認

mysql> select SQL_NO_CACHE count(*) from language;
+----------+
| count(*) |
+----------+
|  3950304 |
+----------+
1 row in set (0.81 sec)

mysql> explain select SQL_NO_CACHE count(*) from language;
+----+-------------+----------+-------+---------------+---------+---------+------+---------+-------------+
| id | select_type | table    | type  | possible_keys | key     | key_len | ref  | rows    | Extra       |
+----+-------------+----------+-------+---------------+---------+---------+------+---------+-------------+
|  1 | SIMPLE      | language | index | NULL          | PRIMARY | 4       | NULL | 3560363 | Using index |
+----+-------------+----------+-------+---------------+---------+---------+------+---------+-------------+
1 row in set (0.00 sec)

mysql> show local status like 'Last_query_cost';
+-----------------+---------------+
| Variable_name   | Value         |
+-----------------+---------------+
| Last_query_cost | 723251.599000 |
+-----------------+---------------+
1 row in set (0.00 sec)

mysql> select (183156736/(16*1024))+3560363*0.2;
+-----------------------------------+
| (183156736/(16*1024))+3560363*0.2 |
+-----------------------------------+
|                       723251.6000 |
+-----------------------------------+
1 row in set (0.00 sec)

mysql>

found_records: 1
read_time: 1
統計情報ではなく、固定値として1が入るとの事。
主キー検索の場合は統計情報による判断が入らないので速い。


mysql> select SQL_NO_CACHE count(*) from language where language_id = 1;
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

mysql> explain select SQL_NO_CACHE count(*) from language where language_id = 1;
+----+-------------+----------+-------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table    | type  | possible_keys | key     | key_len | ref   | rows | Extra       |
+----+-------------+----------+-------+---------------+---------+---------+-------+------+-------------+
|  1 | SIMPLE      | language | const | PRIMARY       | PRIMARY | 4       | const |    1 | Using index |
+----+-------------+----------+-------+---------------+---------+---------+-------+------+-------------+
1 row in set (0.00 sec)

mysql> show local status like 'Last_query_cost';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| Last_query_cost | 1.000000 |
+-----------------+----------+
1 row in set (0.00 sec)

mysql> explain select SQL_NO_CACHE count(*) from language where language_id between 1 and 100;
+----+-------------+----------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table    | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
+----+-------------+----------+-------+---------------+---------+---------+------+------+--------------------------+
|  1 | SIMPLE      | language | range | PRIMARY       | PRIMARY | 4       | NULL |  100 | Using where; Using index |
+----+-------------+----------+-------+---------------+---------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

mysql> show local status like 'Last_query_cost';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| Last_query_cost | 41.091530 |
+-----------------+-----------+
1 row in set (0.00 sec)

mysql>


Reference:
http://dbstudy.info/files/20120310/mysql_costcalc.pdf


MySQL for Excel
MySQL for Excel

少しMySQLにあるデータを編集したり、DBのコマンドを直接実行するのが苦手なユーザーに対しては、
使えるかもしれません。全員がDBAではないので、データ管理者や利用者のオプションの選択肢として。

MySQL for Excel 1.2.1をダウンロードしてインストール
MySQL-Excel

ツールの部分にMySQL for Excelのオプションがアドオンされる。
MySQL-Excel2

MySQLのユーザー設定を入れる。(ユーザー毎にアカウント、パスワード、Default Schemaを分けておいた方が良い)
MySQL-Excel3

接続すると権限があるDBが表示される
MySQL-Excel4

テーブルを選択
MySQL-Excel5

選択すると中のデータを表示する事が出来る
MySQL-Excel6

データを編集出来る(権限必要)
MySQL-Excel7

変更を反映するかCommitを確認される
MySQL-Excel8

Commitすると以下のように処理が完了したとのレスポンスが返る
MySQL-Excel9

MySQL側でも問題無く反映されている事を確認
MySQL-Excel10


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 performance_schemaの状態とテーブル
Assuming that the Performance Schema is available,
it is enabled by default as of MySQL 5.6.6. Before 5.6.6,
it is disabled by default. To enable or disable it explicitly,
start the server with the performance_schema
variable set to an appropriate value.

5.6.6以前の場合、5.6.6以降はDefaultでEnableになっている。
For example, use these lines in your my.cnf file:

[mysqld]
performance_schema=on
mysql> use performance_schema
Database changed

mysql> SHOW VARIABLES LIKE 'perf%';
+--------------------------------------------------------+-------+
| Variable_name                                          | Value |
+--------------------------------------------------------+-------+
| performance_schema                                     | ON    |
| performance_schema_accounts_size                       | 100   |
| performance_schema_digests_size                        | 10000 |
| performance_schema_events_stages_history_long_size     | 10000 |
| performance_schema_events_stages_history_size          | 10    |
| performance_schema_events_statements_history_long_size | 10000 |
| performance_schema_events_statements_history_size      | 10    |
| performance_schema_events_waits_history_long_size      | 10000 |
| performance_schema_events_waits_history_size           | 10    |
| performance_schema_hosts_size                          | 100   |
| performance_schema_max_cond_classes                    | 80    |
| performance_schema_max_cond_instances                  | 3504  |
| performance_schema_max_file_classes                    | 50    |
| performance_schema_max_file_handles                    | 32768 |
| performance_schema_max_file_instances                  | 6770  |
| performance_schema_max_mutex_classes                   | 200   |
| performance_schema_max_mutex_instances                 | 15906 |
| performance_schema_max_rwlock_classes                  | 40    |
| performance_schema_max_rwlock_instances                | 9102  |
| performance_schema_max_socket_classes                  | 10    |
| performance_schema_max_socket_instances                | 322   |
| performance_schema_max_stage_classes                   | 150   |
| performance_schema_max_statement_classes               | 168   |
| performance_schema_max_table_handles                   | 4000  |
| performance_schema_max_table_instances                 | 12500 |
| performance_schema_max_thread_classes                  | 50    |
| performance_schema_max_thread_instances                | 402   |
| performance_schema_session_connect_attrs_size          | 512   |
| performance_schema_setup_actors_size                   | 100   |
| performance_schema_setup_objects_size                  | 100   |
| performance_schema_users_size                          | 100   |
+--------------------------------------------------------+-------+
31 rows in set (0.00 sec)

22.9.1 Performance Schema Table Index
http://dev.mysql.com/doc/refman/5.6/en/performance-schema-table-index.html

mysql> show tables;
+----------------------------------------------------+
| Tables_in_performance_schema                       |
+----------------------------------------------------+
| accounts                                           | Connection statistics per client account
| cond_instances                                     | synchronization object instances
| events_stages_current                              |  Current stage events
| events_stages_history                              |  Most recent stage events for each thread
| events_stages_history_long                         |  Most recent stage events overall
| events_stages_summary_by_account_by_event_name     |  Stage events per account and event name
| events_stages_summary_by_host_by_event_name        |  Stage events per host name and event name
| events_stages_summary_by_thread_by_event_name      |  Stage waits per thread and event name
| events_stages_summary_by_user_by_event_name        |  Stage events per user name and event name
| events_stages_summary_global_by_event_name         |  Stage waits per event name
| events_statements_current                          |  Current statement events
| events_statements_history                          |  Most recent statement events for each thread
| events_statements_history_long                     |  Most recent statement events overall
| events_statements_summary_by_account_by_event_name |  Statement events per account and event name
| events_statements_summary_by_digest                |  Statement events per schema and digest value
| events_statements_summary_by_host_by_event_name    |  Statement events per host name and event name
| events_statements_summary_by_thread_by_event_name  |  Statement events per thread and event name
| events_statements_summary_by_user_by_event_name    |  Statement events per user name and event name
| events_statements_summary_global_by_event_name     |  Statement events per event name
| events_waits_current                               |  Current wait events
| events_waits_history                               |  Most recent wait events for each thread
| events_waits_history_long                          |  Most recent wait events overall
| events_waits_summary_by_account_by_event_name      |  Wait events per account and event name
| events_waits_summary_by_host_by_event_name         |  Wait events per host name and event name
| events_waits_summary_by_instance                   |  Wait events per instance
| events_waits_summary_by_thread_by_event_name       |  Wait events per thread and event name
| events_waits_summary_by_user_by_event_name         |  Wait events per user name and event name
| events_waits_summary_global_by_event_name          |  Wait events per event name
| file_instances                                     |  File instances
| file_summary_by_event_name                         |  File events per event name
| file_summary_by_instance                           |  File events per file instance
| host_cache                                         |  Information from the internal host cache
| hosts                                              |  Connection statistics per client host name
| mutex_instances                                    |  Mutex synchronization object instances
| objects_summary_global_by_type                     |  Object summaries
| performance_timers                                 |  Which event timers are available
| rwlock_instances                                   |  Lock synchronization object instances
| session_account_connect_attrs                      |  Connection attributes per for the current session
| session_connect_attrs                              |  Connection attributes for all sessions
| setup_actors                                       |  How to initialize monitoring for new foreground threads
| setup_consumers                                    |  Consumers for which event information can be stored
| setup_instruments                                  |  Classes of instrumented objects for which events can be collected
| setup_objects                                      |  Which objects should be monitored
| setup_timers                                       |  Current event timer
| socket_instances                                   |  Active connection instances
| socket_summary_by_event_name                       |  Socket waits and I/O per event name
| socket_summary_by_instance                         |  Socket waits and I/O per instance
| table_io_waits_summary_by_index_usage              |  Table I/O waits per index
| table_io_waits_summary_by_table                    |  Table I/O waits per table
| table_lock_waits_summary_by_table                  |  Table lock waits per table
| threads                                            |  Information about server threads
| users                                              |  Connection statistics per client user name
+----------------------------------------------------+
52 rows in set (0.00 sec)

mysql> SHOW CREATE TABLE setup_timers\G
*************************** 1. row ***************************
       Table: setup_timers
Create Table: CREATE TABLE `setup_timers` (
  `NAME` varchar(64) NOT NULL,
  `TIMER_NAME` enum('CYCLE','NANOSECOND','MICROSECOND','MILLISECOND','TICK') NOT NULL
) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql>

Defaultでは全てのユーザーのデータを取得するようになっている。
MS SQLのProfiler同様にHeavyな環境で全てのデータを取得するとパフォーマンスに
影響が出るかもしれないので、ある程度対象が分かっていればここでUpdateして設定する
のも良いのかもしれません。

mysql> select * from setup_actors;
+------+------+------+
| HOST | USER | ROLE |
+------+------+------+
| %    | %    | %    |
+------+------+------+
1 row in set (0.00 sec)

mysql>

こちらもフィルター出来る値。
setup_actors, setup_consumers, setup_instruments, setup_objects,setup_timers

The Performance Schema is implemented as a storage engine.
If this engine is available (which you should already have checked earlier),
you should see it listed with a SUPPORT value of YES in the output from the
INFORMATION_SCHEMA.ENGINES table or the SHOW ENGINES statement:

mysql> SELECT * FROM INFORMATION_SCHEMA.ENGINES
    -> WHERE ENGINE='PERFORMANCE_SCHEMA'\G
*************************** 1. row ***************************
      ENGINE: PERFORMANCE_SCHEMA
     SUPPORT: YES
     COMMENT: Performance Schema
TRANSACTIONS: NO
          XA: NO
  SAVEPOINTS: NO
1 row in set (0.00 sec)

mysql> SHOW ENGINES;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

mysql>

22.9.2.3 The setup_instruments Table
http://dev.mysql.com/doc/refman/5.6/en/setup-instruments-table.html
setup_instruments lists the set of instruments for which events
can be collected and shows which of them are enabled:

Initially, not all instruments and consumers are enabled,
so the performance schema does not collect all events.
To turn all of these on and enable event timing, execute two statements
(the row counts may differ depending on MySQL version):

mysql> select enabled, count(*) as 'count' from setup_instruments group by enabled;
+---------+-------+
| enabled | count |
+---------+-------+
| YES     |   213 |
| NO      |   338 |
+---------+-------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM events_waits_current\G
Empty set (0.00 sec)

mysql> UPDATE setup_instruments SET ENABLED = 'YES', TIMED = 'YES';
Query OK, 338 rows affected (0.04 sec)
Rows matched: 551  Changed: 338  Warnings: 0

mysql> UPDATE setup_consumers SET ENABLED = 'YES';
Query OK, 8 rows affected (0.00 sec)
Rows matched: 12  Changed: 8  Warnings: 0

mysql> select enabled, count(*) as 'count' from setup_instruments group by enabled;
+---------+-------+
| enabled | count |
+---------+-------+
| YES     |   551 |
+---------+-------+
1 row in set (0.00 sec)

mysql>

To see what the server is doing at the moment,
examine the events_waits_current table. It contains one row per thread
showing each thread’s most recent monitored event:

mysql> SELECT * FROM events_waits_current\G
*************************** 1. row ***************************
            THREAD_ID: 21
             EVENT_ID: 53
         END_EVENT_ID: 53
           EVENT_NAME: wait/io/file/sql/query_log
               SOURCE: mf_iocache.c:1788
          TIMER_START: 898529738286969
            TIMER_END: 898529803494782
           TIMER_WAIT: 65207813
                SPINS: NULL
        OBJECT_SCHEMA: NULL
          OBJECT_NAME: C:\ProgramData\MySQL\MySQL Server 5.6\data\P25719.log
           INDEX_NAME: NULL
          OBJECT_TYPE: FILE
OBJECT_INSTANCE_BEGIN: 309205312
     NESTING_EVENT_ID: 52
   NESTING_EVENT_TYPE: STAGE
            OPERATION: write
      NUMBER_OF_BYTES: 63
                FLAGS: NULL
1 row in set (0.00 sec)


mysql> select * from events_waits_current;
+-----------+----------+--------------+-----------------------------------------+-------------------+------------------+------------------+------------+-------+---------------+-------------------------------------------------------+------------+-------------+-----------------------+------------------+--------------------+-----------+-----------------+-------+
| THREAD_ID | EVENT_ID | END_EVENT_ID | EVENT_NAME                              | SOURCE            | TIMER_START      | TIMER_END        | TIMER_WAIT | SPINS | OBJECT_SCHEMA | OBJECT_NAME                                           | INDEX_NAME | OBJECT_TYPE | OBJECT_INSTANCE_BEGIN | NESTING_EVENT_ID | NESTING_EVENT_TYPE | OPERATION | NUMBER_OF_BYTES | FLAGS |
+-----------+----------+--------------+-----------------------------------------+-------------------+------------------+------------------+------------+-------+---------------+-------------------------------------------------------+------------+-------------+-----------------------+------------------+--------------------+-----------+-----------------+-------+
|        21 |      816 |          816 | wait/io/file/sql/query_log              | mf_iocache.c:1788 | 4905373104295754 | 4905373111110348 |    6814594 |  NULL | NULL          | C:\ProgramData\MySQL\MySQL Server 5.6\data\P25719.log | NULL       | FILE        |             309205312 |              815 | STAGE              | write     |              49 |  NULL |
|        28 |   719555 |       719555 | wait/synch/mutex/sql/THD::LOCK_thd_data | sql_class.cc:4606 | 4905373186684011 | 4905373186736943 |      52932 |  NULL | NULL          | NULL                                                  | NULL       | NULL        |                     0 |           719551 | STAGE              | lock      |            NULL |  NULL |
+-----------+----------+--------------+-----------------------------------------+-------------------+------------------+------------------+------------+-------+---------------+-------------------------------------------------------+------------+-------------+-----------------------+------------------+--------------------+-----------+-----------------+-------+
2 rows in set (0.00 sec)

mysql>

1st event of above is indicates that thread 21 was waiting for 65,207,813
picoseconds to write /io/file/sql/query_log.

■A picosecond is an SI unit of time equal to 10−12 of a second.
 That is one trillionth, or one millionth of one millionth of a second,
 or 0.000 000 000 001 seconds.

22.9.4.2 The events_waits_history Table
http://dev.mysql.com/doc/refman/5.6/en/events-waits-history-table.html
The events_waits_history table contains the most recent
10 wait events per thread. To change the table size,
modify the performance_schema_events_waits_history_size
system variable at server startup.

mysql> SELECT THREAD_ID,EVENT_ID, EVENT_NAME, TIMER_WAIT
    -> FROM events_waits_history;
+-----------+----------+----------------------------+-----------------+
| THREAD_ID | EVENT_ID | EVENT_NAME                 | TIMER_WAIT      |
+-----------+----------+----------------------------+-----------------+
|        21 |       28 | idle                       |  12831474863490 |
|        21 |       31 | wait/io/file/sql/query_log |        56420700 |
|        21 |       50 | idle                       |  15451250340070 |
|        21 |       53 | wait/io/file/sql/query_log |        65207813 |
|        21 |       68 | idle                       | 312420345274150 |
|        21 |       71 | wait/io/file/sql/query_log |        66936925 |
|        21 |       88 | idle                       |  12749062928440 |
|        21 |       91 | wait/io/file/sql/query_log |        68074562 |
|        21 |      106 | idle                       |  41669289225990 |
|        21 |      109 | wait/io/file/sql/query_log |        58701588 |
+-----------+----------+----------------------------+-----------------+
10 rows in set (0.00 sec)

mysql>

mysql> select * from events_waits_history;
+-----------+----------+--------------+----------------------------+-------------------+------------------+------------------+-----------------+-------+---------------+-------------------------------------------------------+------------+-------------+-----------------------+------------------+--------------------+-----------+-----------------+-------+
| THREAD_ID | EVENT_ID | END_EVENT_ID | EVENT_NAME                 | SOURCE            | TIMER_START      | TIMER_END        | TIMER_WAIT      | SPINS | OBJECT_SCHEMA | OBJECT_NAME                                           | INDEX_NAME | OBJECT_TYPE | OBJECT_INSTANCE_BEGIN | NESTING_EVENT_ID | NESTING_EVENT_TYPE | OPERATION | NUMBER_OF_BYTES | FLAGS |
+-----------+----------+--------------+----------------------------+-------------------+------------------+------------------+-----------------+-------+---------------+-------------------------------------------------------+------------+-------------+-----------------------+------------------+--------------------+-----------+-----------------+-------+
|        21 |      816 |          816 | wait/io/file/sql/query_log | mf_iocache.c:1788 | 4905373104295754 | 4905373111110348 |         6814594 |  NULL | NULL          | C:\ProgramData\MySQL\MySQL Server 5.6\data\P25719.log | NULL       | FILE        |             309205312 |              815 | STAGE              | write     |              49 |  NULL |
|        21 |      831 |          831 | idle                       | mysqld.cc:910     | 4904012990931030 | 5057673130661700 | 153660139730670 |  NULL | NULL          | NULL                                                  | NULL       | NULL        |                     0 |             NULL | NULL               | idle      |            NULL |  NULL |
|        21 |      836 |          836 | idle                       | mysqld.cc:910     | 5057673304717940 | 5083137360707880 |  25464055989940 |  NULL | NULL          | NULL                                                  | NULL       | NULL        |                     0 |             NULL | NULL               | idle      |            NULL |  NULL |
|        21 |      839 |          839 | wait/io/file/sql/query_log | mf_iocache.c:1788 | 5084549130565107 | 5084549198935607 |        68370500 |  NULL | NULL          | C:\ProgramData\MySQL\MySQL Server 5.6\data\P25719.log | NULL       | FILE        |             309205312 |              838 | STAGE              | write     |              63 |  NULL |
|        21 |      762 |          762 | wait/io/file/sql/query_log | mf_iocache.c:1788 | 4897270600937719 | 4897270677689119 |        76751400 |  NULL | NULL          | C:\ProgramData\MySQL\MySQL Server 5.6\data\P25719.log | NULL       | FILE        |             309205312 |              761 | STAGE              | write     |              63 |  NULL |
|        21 |      777 |          777 | idle                       | mysqld.cc:910     | 4895911828544680 | 4901262906444130 |   5351077899450 |  NULL | NULL          | NULL                                                  | NULL       | NULL        |                     0 |             NULL | NULL               | idle      |            NULL |  NULL |
|        21 |      780 |          780 | wait/io/file/sql/query_log | mf_iocache.c:1788 | 4902624030649702 | 4902624040836305 |        10186603 |  NULL | NULL          | C:\ProgramData\MySQL\MySQL Server 5.6\data\P25719.log | NULL       | FILE        |             309205312 |              779 | STAGE              | write     |              49 |  NULL |
|        21 |      795 |          795 | idle                       | mysqld.cc:910     | 4901263149466050 | 4902370987466420 |   1107838000370 |  NULL | NULL          | NULL                                                  | NULL       | NULL        |                     0 |             NULL | NULL               | idle      |            NULL |  NULL |
|        21 |      798 |          798 | wait/io/file/sql/query_log | mf_iocache.c:1788 | 4903732414301910 | 4903732421811036 |         7509126 |  NULL | NULL          | C:\ProgramData\MySQL\MySQL Server 5.6\data\P25719.log | NULL       | FILE        |             309205312 |              797 | STAGE              | write     |              49 |  NULL |
|        21 |      813 |          813 | idle                       | mysqld.cc:910     | 4902371237467010 | 4904011224095990 |   1639986628980 |  NULL | NULL          | NULL                                                  | NULL       | NULL        |                     0 |             NULL | NULL               | idle      |            NULL |  NULL |
+-----------+----------+--------------+----------------------------+-------------------+------------------+------------------+-----------------+-------+---------------+-------------------------------------------------------+------------+-------------+-----------------------+------------------+--------------------+-----------+-----------------+-------+
10 rows in set (0.00 sec)

mysql>

22.9.9.1 Event Wait Summary Tables
The Performance Schema maintains tables for collecting current and recent wait events,
and aggregates that information in summary tables.
http://dev.mysql.com/doc/refman/5.6/en/wait-summary-tables.html

sort the events_waits_summary_global_by_event_name table on the
COUNT_STAR or SUM_TIMER_WAIT column, which correspond to a
COUNT(*) or SUM(TIMER_WAIT) value, respectively, calculated over all events:

mysql> SELECT EVENT_NAME, COUNT_STAR
    -> FROM events_waits_summary_global_by_event_name
    -> ORDER BY COUNT_STAR DESC LIMIT 10;
+-----------------------------------------+------------+
| EVENT_NAME                              | COUNT_STAR |
+-----------------------------------------+------------+
| wait/synch/mutex/sql/THD::LOCK_thd_data |      50018 |
| wait/io/socket/sql/client_connection    |      40031 |
| wait/synch/mutex/mysys/THR_LOCK::mutex  |      20000 |
| wait/lock/table/sql/handler             |      20000 |
| idle                                    |      10067 |
| wait/io/file/sql/query_log              |      10039 |
| wait/synch/mutex/innodb/autoinc_mutex   |      10001 |
| wait/synch/mutex/innodb/trx_undo_mutex  |      10000 |
| wait/io/table/sql/handler               |      10000 |
| wait/io/file/sql/FRM                    |        925 |
+-----------------------------------------+------------+
10 rows in set (0.04 sec)

mysql> SELECT EVENT_NAME, SUM_TIMER_WAIT
    -> FROM events_waits_summary_global_by_event_name
    -> ORDER BY SUM_TIMER_WAIT DESC LIMIT 10;
+-----------------------------------------+------------------+
| EVENT_NAME                              | SUM_TIMER_WAIT   |
+-----------------------------------------+------------------+
| idle                                    | 3303044059943910 |
| wait/io/socket/sql/client_connection    |     392231099217 |
| wait/io/table/sql/handler               |     108480957278 |
| wait/io/file/sql/query_log              |      73737054930 |
| wait/io/file/sql/binlog                 |      19253155256 |
| wait/io/file/sql/FRM                    |       7584131446 |
| wait/lock/table/sql/handler             |       6172912196 |
| wait/synch/mutex/sql/THD::LOCK_thd_data |       2878397649 |
| wait/io/file/myisam/dfile               |       2485265269 |
| wait/io/file/sql/dbopt                  |       2225433309 |
+-----------------------------------------+------------------+
10 rows in set (0.02 sec)

mysql>

22.9.2.5 The setup_timers Table
http://dev.mysql.com/doc/refman/5.6/en/setup-timers-table.html
Setup tables are used to configure and display monitoring characteristics.
For example, to see which event timers are selected, query the setup_timers tables:

mysql> SELECT * FROM setup_timers;
+-----------+-------------+
| NAME      | TIMER_NAME  |
+-----------+-------------+
| idle      | MICROSECOND |
| wait      | CYCLE       |
| stage     | MICROSECOND |
| statement | MICROSECOND |
+-----------+-------------+
4 rows in set (0.00 sec)

mysql>

22.9.3.2 The file_instances Table
The file_instances table lists all the files seen by the Performance Schema
when executing file I/O instrumentation. If a file on disk has never been opened,
it will not be in file_instances. When a file is deleted from the disk,
it is also removed from the file_instances table.
http://dev.mysql.com/doc/refman/5.6/en/file-instances-table.html

mysql> SELECT * FROM file_instances limit 0,3\G
*************************** 1. row ***************************
 FILE_NAME: C:\Program Files\MySQL\MySQL Server 5.6\share\english\errmsg.sys
EVENT_NAME: wait/io/file/sql/ERRMSG
OPEN_COUNT: 0
*************************** 2. row ***************************
 FILE_NAME: C:\Program Files\MySQL\MySQL Server 5.6\share\charsets\Index.xml
EVENT_NAME: wait/io/file/mysys/charset
OPEN_COUNT: 0
*************************** 3. row ***************************
 FILE_NAME: C:\ProgramData\MySQL\MySQL Server 5.6\data\mysql\plugin.frm
EVENT_NAME: wait/io/file/sql/FRM
OPEN_COUNT: 0
3 rows in set (0.00 sec)

mysql> select * from file_instances order by OPEN_COUNT desc limit 0,5;
+--------------------------------------------------------------+-------------------------------+------------+
| FILE_NAME                                                    | EVENT_NAME                    | OPEN_COUNT |
+--------------------------------------------------------------+-------------------------------+------------+
| C:\ProgramData\MySQL\MySQL Server 5.6\data\P25719-slow.log   | wait/io/file/sql/slow_log     |          1 |
| C:\ProgramData\MySQL\MySQL Server 5.6\data\P25719.log        | wait/io/file/sql/query_log    |          1 |
| C:\ProgramData\MySQL\MySQL Server 5.6\data\P25719-bin.000026 | wait/io/file/sql/binlog       |          1 |
| C:\ProgramData\MySQL\MySQL Server 5.6\data\P25719-bin.index  | wait/io/file/sql/binlog_index |          1 |
| C:\ProgramData\MySQL\MySQL Server 5.6\data\mysql\user.MYI    | wait/io/file/myisam/kfile     |          1 |
+--------------------------------------------------------------+-------------------------------+------------+
5 rows in set (0.00 sec)

mysql>

File events per event name
File events per file instance

mysql> select * from file_summary_by_event_name order by AVG_TIMER_WAIT desc limit 0,3;
+---------------------------+------------+----------------+----------------+----------------+----------------+------------+----------------+----------------+----------------+----------------+--------------------------+-------------+-----------------+-----------------+-----------------+-----------------+---------------------------+------------+----------------+----------------+----------------+----------------+
| EVENT_NAME                | COUNT_STAR | SUM_TIMER_WAIT | MIN_TIMER_WAIT | AVG_TIMER_WAIT | MAX_TIMER_WAIT | COUNT_READ | SUM_TIMER_READ | MIN_TIMER_READ | AVG_TIMER_READ | MAX_TIMER_READ | SUM_NUMBER_OF_BYTES_READ | COUNT_WRITE | SUM_TIMER_WRITE | MIN_TIMER_WRITE | AVG_TIMER_WRITE | MAX_TIMER_WRITE | SUM_NUMBER_OF_BYTES_WRITE | COUNT_MISC | SUM_TIMER_MISC | MIN_TIMER_MISC | AVG_TIMER_MISC | MAX_TIMER_MISC |
+---------------------------+------------+----------------+----------------+----------------+----------------+------------+----------------+----------------+----------------+----------------+--------------------------+-------------+-----------------+-----------------+-----------------+-----------------+---------------------------+------------+----------------+----------------+----------------+----------------+
| wait/io/file/sql/pid      |          3 |     1157890708 |              0 |      385963302 |      855847483 |          0 |              0 |              0 |              0 |              0 |                        0 |           1 |        73653675 |               0 |        73653675 |        73653675 |                         5 |          2 |     1084237033 |              0 |      542118316 |      855847483 |
| wait/io/file/archive/data |          2 |      346097085 |              0 |      173048342 |      195457425 |          0 |              0 |              0 |              0 |              0 |                        0 |           0 |               0 |               0 |               0 |               0 |                         0 |          2 |      346097085 |              0 |      173048342 |      195457425 |
| wait/io/file/sql/casetest |         10 |     1309209662 |              0 |      130920886 |      344558047 |          0 |              0 |              0 |              0 |              0 |                        0 |           0 |               0 |               0 |               0 |               0 |                         0 |         10 |     1309209662 |              0 |      130920886 |      344558047 |
+---------------------------+------------+----------------+----------------+----------------+----------------+------------+----------------+----------------+----------------+----------------+--------------------------+-------------+-----------------+-----------------+-----------------+-----------------+---------------------------+------------+----------------+----------------+----------------+----------------+
3 rows in set (0.00 sec)

mysql>

mysql> select * from file_summary_by_instance limit 0,10;
+------------------------------------------------------------------+-------------------------------+-----------------------+------------+----------------+----------------+----------------+----------------+------------+----------------+----------------+----------------+----------------+--------------------------+-------------+-----------------+-----------------+-----------------+-----------------+---------------------------+------------+----------------+----------------+----------------+----------------+
| FILE_NAME                                                        | EVENT_NAME                    | OBJECT_INSTANCE_BEGIN | COUNT_STAR | SUM_TIMER_WAIT | MIN_TIMER_WAIT | AVG_TIMER_WAIT | MAX_TIMER_WAIT | COUNT_READ | SUM_TIMER_READ | MIN_TIMER_READ | AVG_TIMER_READ | MAX_TIMER_READ | SUM_NUMBER_OF_BYTES_READ | COUNT_WRITE | SUM_TIMER_WRITE | MIN_TIMER_WRITE | AVG_TIMER_WRITE | MAX_TIMER_WRITE | SUM_NUMBER_OF_BYTES_WRITE | COUNT_MISC | SUM_TIMER_MISC | MIN_TIMER_MISC | AVG_TIMER_MISC | MAX_TIMER_MISC |
+------------------------------------------------------------------+-------------------------------+-----------------------+------------+----------------+----------------+----------------+----------------+------------+----------------+----------------+----------------+----------------+--------------------------+-------------+-----------------+-----------------+-----------------+-----------------+---------------------------+------------+----------------+----------------+----------------+----------------+
| C:\Program Files\MySQL\MySQL Server 5.6\share\english\errmsg.sys | wait/io/file/sql/ERRMSG       |             309198976 |          5 |      140991600 |        5298012 |       28198320 |       57330168 |          3 |       49863147 |        5298012 |       16621049 |       22329685 |                    59320 |           0 |               0 |               0 |               0 |               0 |                         0 |          2 |       91128453 |       33798285 |       45564026 |       57330168 |
| C:\Program Files\MySQL\MySQL Server 5.6\share\charsets\Index.xml | wait/io/file/mysys/charset    |             309199680 |          3 |       67456220 |       11204341 |       22485273 |       30768329 |          1 |       25483550 |       25483550 |       25483550 |       25483550 |                    18305 |           0 |               0 |               0 |               0 |               0 |                         0 |          2 |       41972670 |       11204341 |       20986335 |       30768329 |
| C:\ProgramData\MySQL\MySQL Server 5.6\data\mysql\plugin.frm      | wait/io/file/sql/FRM          |             309202496 |         13 |      100849896 |         266264 |        7757345 |       49996279 |          7 |       31000909 |        1237085 |        4428644 |       18924794 |                     1104 |           0 |               0 |               0 |               0 |               0 |                         0 |          6 |       69848987 |         266264 |       11641431 |       49996279 |
| C:\ProgramData\MySQL\MySQL Server 5.6\data\mysql\plugin.MYI      | wait/io/file/myisam/kfile     |             309203200 |          5 |      136199650 |         655635 |       27239930 |       59826794 |          2 |       16195588 |        2857526 |        8097794 |       13338062 |                      364 |           0 |               0 |               0 |               0 |               0 |                         0 |          3 |      120004062 |         655635 |       40001354 |       59826794 |
| C:\ProgramData\MySQL\MySQL Server 5.6\data\mysql\plugin.MYD      | wait/io/file/myisam/dfile     |             309203904 |          4 |      241976232 |        1195782 |       60494058 |      190878406 |          0 |              0 |              0 |              0 |              0 |                        0 |           0 |               0 |               0 |               0 |               0 |                         0 |          4 |      241976232 |        1195782 |       60494058 |      190878406 |
| C:\ProgramData\MySQL\MySQL Server 5.6\data\P25719-slow.log       | wait/io/file/sql/slow_log     |             309204608 |          4 |      352624162 |        1618837 |       88155840 |      217803150 |          0 |              0 |              0 |              0 |              0 |                        0 |           1 |       127974338 |       127974338 |       127974338 |       127974338 |                       197 |          3 |      224649824 |        1618837 |       74883141 |      217803150 |
| C:\ProgramData\MySQL\MySQL Server 5.6\data\P25719.log            | wait/io/file/sql/query_log    |             309205312 |     110121 |   869759674344 |        1604000 |        7898096 |    61257359094 |          0 |              0 |              0 |              0 |              0 |                        0 |      110118 |    869597279369 |         5130394 |         7896893 |     61257359094 |                   7928189 |          3 |      162394975 |        1604000 |       54131391 |      157402525 |
| C:\ProgramData\MySQL\MySQL Server 5.6\data\P25719-bin.000025     | wait/io/file/sql/binlog       |             309206016 |          8 |     9908398022 |        1994975 |     1238549452 |     9632917037 |          2 |       98211717 |       18486100 |       49105658 |       79725617 |                    16384 |           0 |               0 |               0 |               0 |               0 |                         0 |          6 |     9810186305 |        1994975 |     1635030984 |     9632917037 |
| C:\ProgramData\MySQL\MySQL Server 5.6\data\P25719-bin.000026     | wait/io/file/sql/binlog       |             309206720 |       2205 |    29322108064 |        1129617 |       13297962 |     3103265216 |          2 |       14349384 |        4167192 |        7174692 |       10182192 |                      120 |        2196 |     24314145379 |         3582534 |        11072011 |       103890679 |                  17930890 |          7 |     4993613301 |        1129617 |      713372985 |     3103265216 |
| C:\ProgramData\MySQL\MySQL Server 5.6\data\P25719-bin.index      | wait/io/file/sql/binlog_index |             309207424 |          5 |      510165834 |        1413525 |      102032846 |      380365342 |          0 |              0 |              0 |              0 |              0 |                        0 |           0 |               0 |               0 |               0 |               0 |                         0 |          5 |      510165834 |        1413525 |      102032846 |      380365342 |
+------------------------------------------------------------------+-------------------------------+-----------------------+------------+----------------+----------------+----------------+----------------+------------+----------------+----------------+----------------+----------------+--------------------------+-------------+-----------------+-----------------+-----------------+-----------------+---------------------------+------------+----------------+----------------+----------------+----------------+
10 rows in set (0.00 sec)

mysql>

22.9.2.2 The setup_consumers Table
http://dev.mysql.com/doc/refman/5.6/en/setup-consumers-table.html
The setup_consumers table lists the types of consumers for which event
information can be stored and which are enabled:

mysql> SELECT * FROM setup_consumers;
+--------------------------------+---------+
| NAME                           | ENABLED |
+--------------------------------+---------+
| events_stages_current          | YES     |
| events_stages_history          | YES     |
| events_stages_history_long     | YES     |
| events_statements_current      | YES     |
| events_statements_history      | YES     |
| events_statements_history_long | YES     |
| events_waits_current           | YES     |
| events_waits_history           | YES     |
| events_waits_history_long      | YES     |
| global_instrumentation         | YES     |
| thread_instrumentation         | YES     |
| statements_digest              | YES     |
+--------------------------------+---------+
12 rows in set (0.00 sec)

mysql>

“Current statement events” and “Information about server threads” like “Show Full Processlist”

mysql> select * from events_statements_current;
+-----------+----------+--------------+----------------------+---------------+------------------+------------------+------------+-----------+-----------------------------------------------------------+----------------------------------+-------------------------------------------+--------------------+-------------+---------------+-------------+-----------------------+-------------+-------------------+--------------+--------+----------+---------------+-----------+---------------+-------------------------+--------------------+------------------+------------------------+--------------+--------------------+-------------+-------------------+------------+-----------+-----------+---------------+--------------------+------------------+--------------------+
| THREAD_ID | EVENT_ID | END_EVENT_ID | EVENT_NAME           | SOURCE        | TIMER_START      | TIMER_END        | TIMER_WAIT | LOCK_TIME | SQL_TEXT                                                  | DIGEST                           | DIGEST_TEXT                               | CURRENT_SCHEMA     | OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | OBJECT_INSTANCE_BEGIN | MYSQL_ERRNO | RETURNED_SQLSTATE | MESSAGE_TEXT | ERRORS | WARNINGS | ROWS_AFFECTED | ROWS_SENT | ROWS_EXAMINED | CREATED_TMP_DISK_TABLES | CREATED_TMP_TABLES | SELECT_FULL_JOIN | SELECT_FULL_RANGE_JOIN | SELECT_RANGE | SELECT_RANGE_CHECK | SELECT_SCAN | SORT_MERGE_PASSES | SORT_RANGE | SORT_ROWS | SORT_SCAN | NO_INDEX_USED | NO_GOOD_INDEX_USED | NESTING_EVENT_ID | NESTING_EVENT_TYPE |
+-----------+----------+--------------+----------------------+---------------+------------------+------------------+------------+-----------+-----------------------------------------------------------+----------------------------------+-------------------------------------------+--------------------+-------------+---------------+-------------+-----------------------+-------------+-------------------+--------------+--------+----------+---------------+-----------+---------------+-------------------------+--------------------+------------------+------------------------+--------------+--------------------+-------------+-------------------+------------+-----------+-----------+---------------+--------------------+------------------+--------------------+
|        21 |      616 |         NULL | statement/sql/select | mysqld.cc:940 | 4322525515586060 |             NULL |       NULL |         0 | select * from events_statements_current                   | NULL                             | NULL                                      | performance_schema | NULL        | NULL          | NULL        |                  NULL |           0 | NULL              | NULL         |      0 |        0 |             0 |         0 |             0 |                       0 |                  0 |                0 |                      0 |            0 |                  0 |           1 |                 0 |          0 |         0 |         0 |             1 |                  0 |             NULL | NULL               |
|        27 |   718200 |       718227 | statement/sql/insert | mysqld.cc:940 | 4322525313615140 | 4322525502860250 |  189245110 |         0 | insert into language(name) values("RWyDULUHlJYgnjw9Yx8o") | 9c2953f8e62dc70ec329b2b787819a46 | INSERT INTO LANGUAGE ( NAME ) VALUES (?)  | test               | NULL        | NULL          | NULL        |                  NULL |           0 | 00000             | NULL         |      0 |        0 |             1 |         0 |             0 |                       0 |                  0 |                0 |                      0 |            0 |                  0 |           0 |                 0 |          0 |         0 |         0 |             0 |                  0 |             NULL | NULL               |
+-----------+----------+--------------+----------------------+---------------+------------------+------------------+------------+-----------+-----------------------------------------------------------+----------------------------------+-------------------------------------------+--------------------+-------------+---------------+-------------+-----------------------+-------------+-------------------+--------------+--------+----------+---------------+-----------+---------------+-------------------------+--------------------+------------------+------------------------+--------------+--------------------+-------------+-------------------+------------+-----------+-----------+---------------+--------------------+------------------+--------------------+
2 rows in set (0.00 sec)

mysql>

mysql> select * from threads;
+-----------+----------------------------------------+------------+----------------+------------------+------------------+--------------------+---------------------+------------------+-------------------+--------------------------------------+------------------+------+--------------+
| THREAD_ID | NAME                                   | TYPE       | PROCESSLIST_ID | PROCESSLIST_USER | PROCESSLIST_HOST | PROCESSLIST_DB     | PROCESSLIST_COMMAND | PROCESSLIST_TIME | PROCESSLIST_STATE | PROCESSLIST_INFO                     | PARENT_THREAD_ID | ROLE | INSTRUMENTED |
+-----------+----------------------------------------+------------+----------------+------------------+------------------+--------------------+---------------------+------------------+-------------------+--------------------------------------+------------------+------+--------------+
|         1 | thread/sql/main                        | BACKGROUND |           NULL | NULL             | NULL             | NULL               | NULL                |             6986 | System lock       | INTERNAL DDL LOG RECOVER IN PROGRESS |             NULL | NULL | YES          |
|         2 | thread/innodb/io_handler_thread        | BACKGROUND |           NULL | NULL             | NULL             | NULL               | NULL                |             NULL | NULL              | NULL                                 |             NULL | NULL | YES          |
|         3 | thread/innodb/io_handler_thread        | BACKGROUND |           NULL | NULL             | NULL             | NULL               | NULL                |             NULL | NULL              | NULL                                 |             NULL | NULL | YES          |
|         4 | thread/innodb/io_handler_thread        | BACKGROUND |           NULL | NULL             | NULL             | NULL               | NULL                |             NULL | NULL              | NULL                                 |             NULL | NULL | YES          |
|         5 | thread/innodb/io_handler_thread        | BACKGROUND |           NULL | NULL             | NULL             | NULL               | NULL                |             NULL | NULL              | NULL                                 |             NULL | NULL | YES          |
|         6 | thread/innodb/io_handler_thread        | BACKGROUND |           NULL | NULL             | NULL             | NULL               | NULL                |             NULL | NULL              | NULL                                 |             NULL | NULL | YES          |
|         7 | thread/innodb/io_handler_thread        | BACKGROUND |           NULL | NULL             | NULL             | NULL               | NULL                |             NULL | NULL              | NULL                                 |             NULL | NULL | YES          |
|         8 | thread/innodb/io_handler_thread        | BACKGROUND |           NULL | NULL             | NULL             | NULL               | NULL                |             NULL | NULL              | NULL                                 |             NULL | NULL | YES          |
|         9 | thread/innodb/io_handler_thread        | BACKGROUND |           NULL | NULL             | NULL             | NULL               | NULL                |             NULL | NULL              | NULL                                 |             NULL | NULL | YES          |
|        10 | thread/innodb/io_handler_thread        | BACKGROUND |           NULL | NULL             | NULL             | NULL               | NULL                |             NULL | NULL              | NULL                                 |             NULL | NULL | YES          |
|        11 | thread/innodb/io_handler_thread        | BACKGROUND |           NULL | NULL             | NULL             | NULL               | NULL                |             NULL | NULL              | NULL                                 |             NULL | NULL | YES          |
|        13 | thread/innodb/srv_lock_timeout_thread  | BACKGROUND |           NULL | NULL             | NULL             | NULL               | NULL                |             NULL | NULL              | NULL                                 |             NULL | NULL | YES          |
|        14 | thread/innodb/srv_error_monitor_thread | BACKGROUND |           NULL | NULL             | NULL             | NULL               | NULL                |             NULL | NULL              | NULL                                 |             NULL | NULL | YES          |
|        15 | thread/innodb/srv_monitor_thread       | BACKGROUND |           NULL | NULL             | NULL             | NULL               | NULL                |             NULL | NULL              | NULL                                 |             NULL | NULL | YES          |
|        16 | thread/innodb/srv_master_thread        | BACKGROUND |           NULL | NULL             | NULL             | NULL               | NULL                |             NULL | NULL              | NULL                                 |             NULL | NULL | YES          |
|        17 | thread/innodb/srv_purge_thread         | BACKGROUND |           NULL | NULL             | NULL             | NULL               | NULL                |             NULL | NULL              | NULL                                 |             NULL | NULL | YES          |
|        18 | thread/innodb/page_cleaner_thread      | BACKGROUND |           NULL | NULL             | NULL             | NULL               | NULL                |             NULL | NULL              | NULL                                 |             NULL | NULL | YES          |
|        19 | thread/sql/shutdown                    | BACKGROUND |           NULL | NULL             | NULL             | NULL               | NULL                |             NULL | NULL              | NULL                                 |                1 | NULL | YES          |
|        20 | thread/sql/con_sockets                 | BACKGROUND |           NULL | NULL             | NULL             | NULL               | NULL                |             NULL | NULL              | NULL                                 |                1 | NULL | YES          |
|        21 | thread/sql/one_connection              | FOREGROUND |              1 | root             | localhost        | performance_schema | Query               |                0 | Sending data      | select * from threads                |               20 | NULL | YES          |
|        35 | thread/sql/one_connection              | FOREGROUND |             15 | root             | localhost        | test               | Sleep               |                0 | NULL              | NULL                                 |             NULL | NULL | YES          |
+-----------+----------------------------------------+------------+----------------+------------------+------------------+--------------------+---------------------+------------------+-------------------+--------------------------------------+------------------+------+--------------+
21 rows in set (0.00 sec)

mysql>

Wait events per account and event name
Wait events per host name and event name

mysql> select * from events_waits_summary_by_account_by_event_name where USER IS NOT NULL limit 0,3;
+------+-----------+-----------------------------------------------+------------+----------------+----------------+----------------+----------------+
| USER | HOST      | EVENT_NAME                                    | COUNT_STAR | SUM_TIMER_WAIT | MIN_TIMER_WAIT | AVG_TIMER_WAIT | MAX_TIMER_WAIT |
+------+-----------+-----------------------------------------------+------------+----------------+----------------+----------------+----------------+
| root | localhost | wait/synch/mutex/sql/PAGE::lock               |          0 |              0 |              0 |              0 |              0 |
| root | localhost | wait/synch/mutex/sql/TC_LOG_MMAP::LOCK_sync   |          0 |              0 |              0 |              0 |              0 |
| root | localhost | wait/synch/mutex/sql/TC_LOG_MMAP::LOCK_active |          0 |              0 |              0 |              0 |              0 |
+------+-----------+-----------------------------------------------+------------+----------------+----------------+----------------+----------------+
3 rows in set (0.00 sec)

mysql> select * from events_waits_summary_by_host_by_event_name limit 0,3;
+------+-----------------------------------------------+------------+----------------+----------------+----------------+----------------+
| HOST | EVENT_NAME                                    | COUNT_STAR | SUM_TIMER_WAIT | MIN_TIMER_WAIT | AVG_TIMER_WAIT | MAX_TIMER_WAIT |
+------+-----------------------------------------------+------------+----------------+----------------+----------------+----------------+
| NULL | wait/synch/mutex/sql/PAGE::lock               |          0 |              0 |              0 |              0 |              0 |
| NULL | wait/synch/mutex/sql/TC_LOG_MMAP::LOCK_sync   |          0 |              0 |              0 |              0 |              0 |
| NULL | wait/synch/mutex/sql/TC_LOG_MMAP::LOCK_active |          0 |              0 |              0 |              0 |              0 |
+------+-----------------------------------------------+------------+----------------+----------------+----------------+----------------+
3 rows in set (0.00 sec)

mysql>

Connection statistics per client host name

mysql> select * from hosts;
+-----------+---------------------+-------------------+
| HOST      | CURRENT_CONNECTIONS | TOTAL_CONNECTIONS |
+-----------+---------------------+-------------------+
| NULL      |                  19 |                21 |
| localhost |                   2 |                10 |
+-----------+---------------------+-------------------+
2 rows in set (0.00 sec)

mysql>

Mutex synchronization object instances

mysql> select * from mutex_instances;
+-------------------------------------------------+-----------------------+---------------------+
| NAME                                            | OBJECT_INSTANCE_BEGIN | LOCKED_BY_THREAD_ID |
+-------------------------------------------------+-----------------------+---------------------+
| wait/synch/mutex/mysys/my_thread_var::mutex     |             711531888 |                NULL |
| wait/synch/mutex/sql/TABLE_SHARE::LOCK_ha_data  |             749544400 |                NULL |
| wait/synch/mutex/mysys/THR_LOCK::mutex          |             713680056 |                NULL |
| wait/synch/mutex/innodb/autoinc_mutex           |             749887584 |                NULL |
| wait/synch/mutex/innodb/zip_pad_mutex           |             713913576 |                NULL |
| wait/synch/mutex/sql/MDL_wait::LOCK_wait_status |             749871280 |                NULL |
| wait/synch/mutex/sql/THD::LOCK_thd_data         |             749874056 |                NULL |
| wait/synch/mutex/innodb/trx_mutex               |             748756816 |                NULL |
| wait/synch/mutex/innodb/trx_undo_mutex          |             748757472 |                NULL |
+-------------------------------------------------+-----------------------+---------------------+
9 rows in set (0.00 sec)

mysql>

Active connection instances

mysql> select * from socket_instances;
+--------------------------------------+-----------------------+-----------+-----------+------------------+-------+--------+
| EVENT_NAME                           | OBJECT_INSTANCE_BEGIN | THREAD_ID | SOCKET_ID | IP               | PORT  | STATE  |
+--------------------------------------+-----------------------+-----------+-----------+------------------+-------+--------+
| wait/io/socket/sql/client_connection |              10782080 |        33 |       380 | ::ffff:127.0.0.1 | 50522 | ACTIVE |
+--------------------------------------+-----------------------+-----------+-----------+------------------+-------+--------+
1 row in set (0.00 sec)

mysql>

events_statements_summary_by_digest
Statement events per schema and digest value
累積実行時間が最も長いSQLを特定する事が可能

mysql>  SELECT * FROM performance_schema.events_statements_summary_by_digest order by sum_timer_wait desc limit 1\G
*************************** 1. row ***************************
                SCHEMA_NAME: test
                     DIGEST: 9c2953f8e62dc70ec329b2b787819a46
                DIGEST_TEXT: INSERT INTO LANGUAGE ( NAME ) VALUES (?)
                 COUNT_STAR: 590000
             SUM_TIMER_WAIT: 53454275577080
             MIN_TIMER_WAIT: 60344970
             AVG_TIMER_WAIT: 90312200
             MAX_TIMER_WAIT: 61756713890
              SUM_LOCK_TIME: 15936952000000
                 SUM_ERRORS: 0
               SUM_WARNINGS: 0
          SUM_ROWS_AFFECTED: 590000
              SUM_ROWS_SENT: 0
          SUM_ROWS_EXAMINED: 0
SUM_CREATED_TMP_DISK_TABLES: 0
     SUM_CREATED_TMP_TABLES: 0
       SUM_SELECT_FULL_JOIN: 0
 SUM_SELECT_FULL_RANGE_JOIN: 0
           SUM_SELECT_RANGE: 0
     SUM_SELECT_RANGE_CHECK: 0
            SUM_SELECT_SCAN: 0
      SUM_SORT_MERGE_PASSES: 0
             SUM_SORT_RANGE: 0
              SUM_SORT_ROWS: 0
              SUM_SORT_SCAN: 0
          SUM_NO_INDEX_USED: 0
     SUM_NO_GOOD_INDEX_USED: 0
                 FIRST_SEEN: 2014-08-08 09:14:27
                  LAST_SEEN: 2014-08-08 11:23:22
1 row in set (0.00 sec)

mysql>

Reference:
22.1 Performance Schema Quick Start
22.9.1 Performance Schema Table Index
パフォーマンス・スキーマ:MySQLサーバーの稼働統計を確認可能


Bazaar in five minutes
Bazaar is a distributed version control system that
makes it easier for people to work together on software projects.
Bazaar in five minutes
SVNやGitと同じようにバージョン管理が出来ます。

C:\Python27\Scripts>bzr whoami "Bzr Sample_User<sample@bzr.sample.com>"

C:\Python27\Scripts>bzr whoami
Bzr Sample_User<sample@bzr.sample.com>

C:\Python27\Scripts>

C:\Python27\Scripts>bzr init-repo bzr_svn
Shared repository with trees (format: 2a)
Location:
  shared repository: bzr_svn

C:\Python27\Scripts>bzr init bzr_svn/trunk
repository treeを作成しました。(フォーマット:2a)
共有リポジトリを使用しました。: C:/Python27/Scripts/bzr_svn/

C:\Python27\Scripts>cd bzr_svn/trunk

C:\Python27\Scripts\bzr_svn\trunk>echo test > test1.txt

C:\Python27\Scripts\bzr_svn\trunk>c:\Python27\Scripts\bzr add test1.txt
adding test1.txt

C:\Python27\Scripts\bzr_svn\trunk>c:\Python27\Scripts\bzr commit -m "Add first line to test1.txt"
Committing to: C:/Python27/Scripts/bzr_svn/trunk/
added test1.txt
Committed revision 1.

C:\Python27\Scripts\bzr_svn\trunk>echo "test change on text1" > test1.txt

C:\Python27\Scripts\bzr_svn\trunk>echo "test add line on test1" >> test1.txt

C:\Python27\Scripts\bzr_svn\trunk>c:\Python27\Scripts\bzr add test1.txt

C:\Python27\Scripts\bzr_svn\trunk>c:\Python27\Scripts\bzr diff
=== modified file 'test1.txt'
--- test1.txt   2014-08-06 01:23:21 +0000
+++ test1.txt   2014-08-06 01:24:09 +0000
@@ -1,1 +1,2 @@
-test
+"test change on text1"
+"test add line on test1"


C:\Python27\Scripts\bzr_svn\trunk>c:\Python27\Scripts\bzr commit -m "Change and Add line on text1"
Committing to: C:/Python27/Scripts/bzr_svn/trunk/
modified test1.txt
Committed revision 2.

C:\Python27\Scripts\bzr_svn\trunk>c:\Python27\Scripts\bzr log
------------------------------------------------------------
revno: 2
committer: Bzr Sample_User<sample@bzr.sample.com>
branch nick: trunk
timestamp: Wed 2014-08-06 10:30:02 +0900
message:
  Change and Add line on text1
------------------------------------------------------------
revno: 1
committer: Bzr Sample_User<sample@bzr.sample.com>
branch nick: trunk
timestamp: Wed 2014-08-06 10:23:21 +0900
message:
  Add first line to test1.txt

C:\Python27\Scripts\bzr_svn\trunk>


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


PythonからXLSを操作するモジュール
Extract data from Excel spreadsheets (.xls and .xlsx, versions 2.0 onwards) on any platform.
Pure Python (2.6, 2.7, 3.2+). Strong support for Excel dates. Unicode-aware.

$ cd 'C:\Python27\xls'
$ pwd
/c/Python27/xls
$ ls -l
合計 175
-rw-r--r-- 1 python_user Administrators 178490 Aug  5 11:10 xlrd-0.9.3.tar.gz

$ tar zxvf xlrd-0.9.3.tar.gz
xlrd-0.9.3/
xlrd-0.9.3/PKG-INFO
xlrd-0.9.3/README.html
xlrd-0.9.3/scripts/
xlrd-0.9.3/setup.py
.. 省略
xlrd-0.9.3/tests/test_xldate.py
xlrd-0.9.3/tests/test_xldate_to_datetime.py
xlrd-0.9.3/tests/test_xlsx_comments.py
xlrd-0.9.3/tests/text_bar.xlsx
xlrd-0.9.3/tests/xf_class.xls
xlrd-0.9.3/scripts/runxlrd.py

$ ls -l
合計 179
drwxr-xr-x 5 python_user Administrators   4096 Apr  9 16:24 xlrd-0.9.3
-rw-r--r-- 1 python_user Administrators 178490 Aug  5 11:10 xlrd-0.9.3.tar.gz

$ cd xlrd-0.9.3

$ ls -l
合計 20
-rw-r--r-- 1 python_user Administrators  994 Apr  9 16:24 PKG-INFO
-rw-r--r-- 1 python_user Administrators 4672 Jun 11  2013 README.html
drwxr-xr-x 2 python_user Administrators    0 Aug  5 11:13 scripts
-rwxr-xr-x 1 python_user Administrators 1887 Jun 11  2013 setup.py
drwxr-xr-x 2 python_user Administrators 8192 Aug  5 11:13 tests
drwxr-xr-x 2 python_user Administrators 4096 Apr  9 16:24 xlrd

$ python setup.py install
running install
running build
running build_py
creating build
creating build\lib
creating build\lib\xlrd
copying xlrd\biffh.py -> build\lib\xlrd
copying xlrd\book.py -> build\lib\xlrd
copying xlrd\compdoc.py -> build\lib\xlrd
.. 省略
byte-compiling c:\Python27\Lib\site-packages\xlrd\info.py to info.pyc
byte-compiling c:\Python27\Lib\site-packages\xlrd\licences.py to licences.pyc
byte-compiling c:\Python27\Lib\site-packages\xlrd\sheet.py to sheet.pyc
byte-compiling c:\Python27\Lib\site-packages\xlrd\timemachine.py to timemachine.pyc
byte-compiling c:\Python27\Lib\site-packages\xlrd\xldate.py to xldate.pyc
byte-compiling c:\Python27\Lib\site-packages\xlrd\xlsx.py to xlsx.pyc
byte-compiling c:\Python27\Lib\site-packages\xlrd\__init__.py to __init__.pyc
running install_scripts
creating c:\Python27\Scripts
copying build\scripts-2.7\runxlrd.py -> c:\Python27\Scripts
running install_egg_info
Writing c:\Python27\Lib\site-packages\xlrd-0.9.3-py2.7.egg-info
$
$ cat read_xls_file.py
# coding: utf-8

import xlrd
import urllib

def read_xls(url):
    webpage = urllib.urlopen(url)
    webdata = webpage.read()
    webpage.close()
    book = xlrd.open_workbook(file_contents=webdata)
    sheet1 = book.sheet_by_index(0)
    for col in range(sheet1.ncols):
        print "----------------------------"
        for row in range(sheet1.nrows):
            cell=sheet1.cell(row,col)
            if cell.ctype == xlrd.XL_CELL_TEXT:
                print 'col=', col, 'row=', row, cell.value.encode('UTF-8')
            else:
                print 'col=', col, 'row=', row, cell.value
if __name__ == '__main__':
    import sys
    if len( sys.argv ) > 1:
        url = sys.argv[1]
    read_xls(url)

$

電力情報をWebから取得してデータ作成して値段推移を確認してみる。

$ python read_xls_file.py  'http://www.enecho.meti.go.jp/about/whitepaper/2013html/data/whitepaper2013_214-1-7.xls' | grep 'row= 4' | awk '{print $5}'

電灯
24.805595108
24.6026352946
24.2067276624
24.4931131033
23.3280284336
23.061707533
23.0761062356
22.7901832667
21.8335163592
21.4982452593
21.2212843648
20.7917694214
20.7261067007
20.7846825484
21.8873581716
20.5422138002
20.3707924016
21.2596934385

$ python read_xls_file.py  'http://www.enecho.meti.go.jp/about/whitepaper/2013html/data/whitepaper2013_214-1-7.xls' | grep 'row= 5' | awk '{print $5}'

電力
17.1488350119
16.9583292586
16.5184136265
16.7650452054
15.8949172913
15.4675782507
15.4433739677
15.4572175847
14.3915064494
14.0749796905
13.7543726135
13.5120790035
13.6176359413
13.6556277198
15.2149111291
13.7677260803
13.6462341174
14.5917638787

$ python read_xls_file.py  'http://www.enecho.meti.go.jp/about/whitepaper/2013html/data/whitepaper2013_214-1-7.xls' | grep 'row= 6' | awk '{print $5}'

電灯・電力計
19.3784749229
19.2269525733
18.7837216319
19.0340122112
18.1364447046
17.7751731119
17.7624823149
17.7215241422
16.7213643101
16.3852613132
16.1059097541
15.8322628265
15.8420723513
15.9017966876
17.3563940883
16.0163599156
15.9032620599
16.8325761746

$

Reference:
http://d.hatena.ne.jp/addition/20140104/1388832149
http://www.python-izm.com/contents/external/xlrd.shtml
http://stackoverflow.com/questions/15588713/sheets-of-excel-workbook-from-a-url-into-a-pandas-dataframe
http://stackoverflow.com/questions/3665379/django-and-xlrd-reading-from-memory
http://geeks-squad.com/access-excel-file-in-python
http://java.dzone.com/articles/reading-excel-spreadsheets


10,000件のデータをInsertしてみて1件ずつのCommitと
全件InsertしてからCommitした場合で実行時間を確認してみた。

サンプルコード

# coding: utf-8

try:
 # import

 import mysql.connector
 import string
 from random import randrange


 # 接続
 connect = mysql.connector.connect(user='root', password='password', host='localhost', database='test', charset='utf8')

 # カーソル
 cursor = connect.cursor()
 # SQL 発行
 # cursor.execute('select language_id,name from language',())

 for i in  range(0, 10000):

  # Random Charactor Create
  LENGTH = 20
  alphabets = string.digits + string.letters

  def randstr(n):
      return ''.join(alphabets[randrange(len(alphabets))] for i in xrange(n))
  if __name__ == '__main__':


   randstr_ins =  randstr(LENGTH)
   insert_stmt = 'insert into language(name) values("%s")' % randstr_ins
   cursor.execute(insert_stmt)
   #print insert_stmt
   #connect.commit()

 else:
    print('Finish Creating Data')

 # フェッチ
 #
 # rows = cursor.fetchall()
 # print(rows)
 connect.commit()
 cursor.close()
 # 切断
 connect.close()

except Exception as myout:

 print(myout)

■全てのデータInsertが完了してからCommitした場合

1回目
$ time python mysql_loop.py
Finish Creating Data

real 0m2.017s
user 0m0.000s
sys 0m0.062s

$

2回目
$ time python mysql_loop.py

real 0m1.917s
user 0m0.000s
sys 0m0.015s

$

3回目(100,000件で試してみた)
$ time python mysql_loop.py
Finish Creating Data

real 0m17.972s
user 0m0.000s
sys 0m0.031s

$

   randstr_ins =  randstr(LENGTH)
   insert_stmt = 'insert into language(name) values("%s")' % randstr_ins
   cursor.execute(insert_stmt)
   #print insert_stmt
   #connect.commit()

 else:
    print('Finish Creating Data')

 # フェッチ
 #
 # rows = cursor.fetchall()
 # print(rows)
 connect.commit()

■1件Insert毎にCommitした場合

1回目
$ time python mysql_loop.py
Finish Creating Data

real 0m5.950s
user 0m0.000s
sys 0m0.031s

$

2回目
$ time python mysql_loop.py
Finish Creating Data

real 0m6.006s
user 0m0.000s
sys 0m0.062s

$

3回目(100,000件で試してみた)
$ time python mysql_loop.py
Finish Creating Data

real 0m58.563s
user 0m0.000s
sys 0m0.046s

$

   randstr_ins =  randstr(LENGTH)
   insert_stmt = 'insert into language(name) values("%s")' % randstr_ins
   cursor.execute(insert_stmt)
   #print insert_stmt
   connect.commit()

 else:
    print('Finish Creating Data')

 # フェッチ
 #
 # rows = cursor.fetchall()
 # print(rows)
 # connect.commit()

結果

mysql> select * from language order by language_id desc limit 0,10;
+-------------+----------------------+---------------------+
| language_id | name                 | last_update         |
+-------------+----------------------+---------------------+
|       80300 | TFEGGvjH8b74uFawV7OS | 2014-08-05 10:11:14 |
|       80299 | ZMAF25n87TaIjgT8qJgr | 2014-08-05 10:11:14 |
|       80298 | sxV2CjebjaMpmOlQe9Yf | 2014-08-05 10:11:14 |
|       80297 | s9c0avx5UumRtcPdtDRu | 2014-08-05 10:11:14 |
|       80296 | MH0M5t7jD9F2jGs3jbM4 | 2014-08-05 10:11:14 |
|       80295 | Ap4ThCp5RsFHAOrUw0BV | 2014-08-05 10:11:14 |
|       80294 | cwLz0e8Vx5L73rzJd6uW | 2014-08-05 10:11:14 |
|       80293 | FAfnZReIagqi4mgCnsSV | 2014-08-05 10:11:14 |
|       80292 | YfxFjzfJVATB5sEHnkR0 | 2014-08-05 10:11:14 |
|       80291 | wENaU30qX4aF51tpDSfY | 2014-08-05 10:11:14 |
+-------------+----------------------+---------------------+
10 rows in set (0.00 sec)

mysql>

mysql> SELECT @@global.tx_isolation;
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| REPEATABLE-READ       |
+-----------------------+

mysql>

※ やはりCommitの回数が少ない方が断然早い。

※ InnoDB のデフォルト分離レベルは、REPEATABLE-READなので、
  Commitが終わったものが見る事が出来ます。
  Selectをバッチ中に確認すると1回毎にCommitしているQueryは、
  Selectを実施する毎にその時の最新の状況を確認する事が出来ます。

Reference: 13.5.10.3. InnoDB と TRANSACTION ISOLATION LEVEL
http://dev.mysql.com/doc/refman/5.1/ja/innodb-transaction-isolation.html