Script – variable.jp [データベース,パフォーマンス,運用]

044月 2018

Slave側のSystem lockについて

by rdbms

MySQL5.7までのSHOW SLAVE STATUSだけでは分からない事が多かったけど、MySQL8.0のSHOW SLAVE STATUSは少し改善されていた。
マスター側で負荷をかけて、スレーブの状態を確認した時にスレーブ側で”Systetm lock”という状態になっていて、詳細を確認する為にPerformance Schemaを確認してみた。
MySQL8.0からはPerformance_Schemaを確認しなくても”Slave_SQL_Running_State”で状態が確認出来るようになっている。
以下、MySQL8.0で確認したログですが、MySQL5.7では”System lock”だった状態が、MySQL8.0では”Applying batch of row changes (update)”になっています。

[admin@misc02 ~]$ cat repli_log | grep Slave_SQL_Running_State
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Slave_SQL_Running_State: Reading event from the relay log
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Slave_SQL_Running_State: Reading event from the relay log
Slave_SQL_Running_State: Reading event from the relay log
Slave_SQL_Running_State: Reading event from the relay log
Slave_SQL_Running_State: Applying batch of row changes (update)
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
[admin@misc02 ~]$

詳細：　WL#7364: RBR: Enhanced Applier Thread Progress Details
https://dev.mysql.com/worklog/task/?id=7364

以下、MySQL5.7のケース
マスター側でmysqlslapで負荷をかけると、スレーブ側がsystem lockになっている。
スレーブ側でトランザクション実行して、UPLOCKかけて参照しても、同じsystem lockになる。
これだと、I/O Threadに遅延が無いことは分かるが、Applier Threadの状態が良く分からない。

[ON MASTER] – MySQL5.7

root@localhost [REPLI]> show create table T_SLAP01\G
*************************** 1. row ***************************
       Table: T_SLAP01
Create Table: CREATE TABLE `T_SLAP01` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `n_time` varchar(30) DEFAULT NULL,
  `s_time` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

root@localhost [REPLI]> 

[root@misc01 admin]# cat mysqlslap_exsisting_table.sh 
/usr/local/mysql/bin/mysqlslap -u root -p -S /tmp/mysql.sock --create-schema=REPLI --no-drop -c 10 -i 1000 -q 'INSERT INTO REPLI.T_SLAP01(n_time,s_time) values(now(3),sysdate(6))' 
[root@misc01 admin]# 

[root@misc01 admin]# ./mysqlslap_exsisting_table.sh 
Enter password: 
Benchmark
        Average number of seconds to run all queries: 0.046 seconds
        Minimum number of seconds to run all queries: 0.018 seconds
        Maximum number of seconds to run all queries: 0.135 seconds
        Number of clients running queries: 10
        Average number of queries per client: 1

[root@misc01 admin]#

[ON SLAVE]
上記マスターで負荷をかけた場合の、スレーブ側の状態。 “System lock”という状態になっている。ただし、この状態はApplier Threadが止まっている訳では無く、何らかの処理を行っている状態の場合もある。


root@localhost [REPLI]> show processlist; show engine innodb status\G show slave status\G
+-----+-----------------+--------------+-------+---------+------+----------------------------------+------------------+
| Id  | User            | Host         | db    | Command | Time | State                            | Info             |
+-----+-----------------+--------------+-------+---------+------+----------------------------------+------------------+
|   1 | system user     |              | NULL  | Connect |  888 | Waiting for master to send event | NULL             |
|   2 | system user     |              | NULL  | Connect |   -1 | System lock                      | NULL             |
|   3 | event_scheduler | localhost    | NULL  | Daemon  |  887 | Waiting on empty queue           | NULL             |
|  13 | root            | localhost    | REPLI | Query   |    0 | starting                         | show processlist |
|  29 | admin           | Labs01:44555 | mysql | Sleep   |    3 |                                  | NULL             |
| 763 | admin           | Labs01:47755 | mysql | Sleep   |   55 |                                  | NULL             |
+-----+-----------------+--------------+-------+---------+------+----------------------------------+------------------+
6 rows in set (0.00 sec)

*************************** 1. row ***************************
  Type: InnoDB
  Name: 
Status: 
=====================================
2018-03-23 15:32:58 0x7fcdb65f0700 INNODB MONITOR OUTPUT
=====================================
<SNIP>
------------
TRANSACTIONS
------------
Trx id counter 756832
Purge done for trx's n:o < 756831 undo n:o < 0 state: running but idle
History list length 11
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421997346109728, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421997346107904, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421997346106992, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421997346106080, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 756831, ACTIVE (PREPARED) 0 sec
3 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 2
MySQL thread id 2, OS thread handle 140521468139264, query id 69415 System lock

<SNIP>

--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=8340, Main thread ID=140522077779712, state: sleeping
Number of rows inserted 31953, updated 30756, deleted 0, read 32044
72.99 inserts/s, 72.99 updates/s, 0.00 deletes/s, 72.99 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

1 row in set (0.01 sec)

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.56.113
                  Master_User: repl_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.002848
          Read_Master_Log_Pos: 59249068
               Relay_Log_File: misc02-relay-bin.000766
                Relay_Log_Pos: 12937379
        Relay_Master_Log_File: mysql-bin.002848
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
<SNIP>
             Master_Server_Id: 1
                  Master_UUID: 2dde009f-d4dc-11e4-b437-0800279cea3c
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: System lock
           Master_Retry_Count: 86400
                  Master_Bind: 
<SNIP>
1 row in set (0.00 sec)

root@localhost [REPLI]>

SlaveのPerfomance_Schemaの状態。
Replication SlaveのIDで確認してみると、statement/sql/beginという状態である事が分かる。

root@localhost [REPLI]> SELECT  event_id, event_name, timer_wait, lock_time FROM performance_schema.events_statements_history_long WHERE thread_id =  @sql_thread;
+----------+---------------------+------------+-----------+
| event_id | event_name          | timer_wait | lock_time |
+----------+---------------------+------------+-----------+
|    10003 | statement/sql/begin |  175076000 |         0 |
+----------+---------------------+------------+-----------+
1 row in set (0.01 sec)


root@localhost [REPLI]> SELECT event_name, count_star, sum_timer_wait FROM performance_schema.events_waits_summary_by_thread_by_event_name WHERE thread_id =  @sql_thread AND sum_timer_wait > 0 ORDER BY sum_timer_wait DESC;
+--------------------------------------+------------+----------------+
| event_name                           | count_star | sum_timer_wait |
+--------------------------------------+------------+----------------+
| wait/io/table/sql/handler            |      10002 | 52235951612404 |
| wait/io/file/innodb/innodb_log_file  |      20012 | 24423316510586 |
| wait/io/file/sql/binlog              |      10014 |   372515866246 |
| wait/io/file/innodb/innodb_data_file |         29 |   158702658238 |
| wait/io/file/sql/relaylog            |      12641 |   144454610798 |
| wait/io/file/sql/binlog_index        |         30 |    62816598186 |
| wait/lock/table/sql/handler          |      10003 |    51676533966 |
| wait/io/file/sql/misc                |          3 |      116404090 |
+--------------------------------------+------------+----------------+

SLAVE側のSystem Lockの状態はRelay Logの適用から発生するトランザクションでした。

備考：以下のように、スレーブ側で参照ロックをかけた場合の状況もSystem Lock。
補足：トランザクションでロックをかけたセッションで、SYSスキーマを利用した為、sys.innodb_lock_waitsの値がおかしい。

違うセッションであれば、sys.innodb_lock_waitsも問題無い。

上記はバグでは無いですが、分かり難いのでスクリプト変更しました。（MySQL8.0では利用出来ません)
https://bugs.mysql.com/file.php?id=26694&bug_id=72131

Please Enjoy Replication with MySQL8.0 soon…..

315月 2017

NEW JSON FUNCTIONS in MySQL8.0

by rdbms

MySQL5.7で約20程のJSON関数が追加されていましたが、MySQL8.0においても更に追加でJSON関数が加えられているので、基本的な動作のみを確認しています。JSON_ARRAYAGG(), JSON_OBJECTAGG(),JSON_PRETTY()

MySQL5.7 JSON関数マニュアル
https://dev.mysql.com/doc/refman/5.7/en/json-functions.html

MySQL5.7のJSONの概要に関しては、こちらにて資料がダウンロード可能です。https://www.mysql.com/jp/why-mysql/presentations/mysql-json-201701-ja/

まだ、MySQL8.0はDMRなので、これからまだ仕様が変わる部分がある事はご了承ください。

確認バージョン


mysql> select now(),@@version;     
+---------------------+-----------+
| now()               | @@version |
+---------------------+-----------+
| 2017-05-31 19:50:46 | 8.0.1-dmr |
+---------------------+-----------+
1 row in set (0.00 sec)

mysql>

WL#7987 : JSON aggregation functions
https://dev.mysql.com/worklog/task/?id=7987

Add aggregation functions to generate JSON arrays and objects. This makes it possible to combine JSON documents in multiple rows into a JSON array or a JSON object.

mysql> select body from T_JSON_DOC where id in (1,2);              
+---------------------------------------------------------------------------------+
| body                                                                            |
+---------------------------------------------------------------------------------+
| {"id": 1, "name": "", "price": 10000, "Conditions": ["NEW", 2015, "Excellent"]} |
| {"id": 2, "name": "", "price": 30000, "Conditions": ["USED", 2013, ""]}         |
+---------------------------------------------------------------------------------+
2 rows in set (0.01 sec)

mysql> select JSON_ARRAYAGG(body) from T_JSON_DOC where id in(1,2);
+------------------------------------------------------------------------------------------------------------------------------------------------------------+
| JSON_ARRAYAGG(body)                                                                                                                                        |
+------------------------------------------------------------------------------------------------------------------------------------------------------------+
| [{"id": 1, "name": "", "price": 10000, "Conditions": ["NEW", 2015, "Excellent"]}, {"id": 2, "name": "", "price": 30000, "Conditions": ["USED", 2013, ""]}] |
+------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)


mysql> select id,body from T_JSON_DOC where id in(1,2);            
+----+---------------------------------------------------------------------------------+
| id | body                                                                            |
+----+---------------------------------------------------------------------------------+
|  1 | {"id": 1, "name": "", "price": 10000, "Conditions": ["NEW", 2015, "Excellent"]} |
|  2 | {"id": 2, "name": "", "price": 30000, "Conditions": ["USED", 2013, ""]}         |
+----+---------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> select JSON_OBJECTAGG(id,body) from T_JSON_DOC where id in(1,2);
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| JSON_OBJECTAGG(id,body)                                                                                                                                              |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {"1": {"id": 1, "name": "", "price": 10000, "Conditions": ["NEW", 2015, "Excellent"]}, "2": {"id": 2, "name": "", "price": 30000, "Conditions": ["USED", 2013, ""]}} |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select id,body from T_JSON_DOC;                                                
+----+---------------------------------------------------------------------------------+
| id | body                                                                            |
+----+---------------------------------------------------------------------------------+
|  1 | {"id": 1, "name": "", "price": 10000, "Conditions": ["NEW", 2015, "Excellent"]} |
|  2 | {"id": 2, "name": "", "price": 30000, "Conditions": ["USED", 2013, ""]}         |
|  3 | {"id": 3, "name": "", "price": 18198, "Conditions": ["NEW", 2015]}              |
|  4 | {"id": 4, "name": "", "price": 500000, "Conditions": ["NEW", 2015]}             |
|  5 | {"id": 5, "name": "", "price": 25000, "Conditions": ["NEW", 2015, "January"]}   |
+----+---------------------------------------------------------------------------------+
5 rows in set (0.01 sec)

mysql> select JSON_OBJECTAGG(id,body) from T_JSON_DOC group by body->"$.Conditions[0]"\G
*************************** 1. row ***************************
JSON_OBJECTAGG(id,body): {"1": {"id": 1, "name": "", "price": 10000, "Conditions": ["NEW", 2015, "Excellent"]}, "3": {"id": 3, "name": "", "price
": 18198, "Conditions": ["NEW", 2015]}, "4": {"id": 4, "name": "", "price": 500000, "Conditions": ["NEW", 2015]}, "5": {"id": 5, "name": "", "pri
ce": 25000, "Conditions": ["NEW", 2015, "January"]}}
*************************** 2. row ***************************
JSON_OBJECTAGG(id,body): {"2": {"id": 2, "name": "", "price": 30000, "Conditions": ["USED", 2013, ""]}}
2 rows in set (0.01 sec)

mysql> mysql> select JSON_OBJECTAGG(id,body) from T_JSON_DOC group by body->"$.Conditions[1]"\G
*************************** 1. row ***************************
JSON_OBJECTAGG(id,body): {"2": {"id": 2, "name": "", "price": 30000, "Conditions": ["USED", 2013, ""]}}
*************************** 2. row ***************************
JSON_OBJECTAGG(id,body): {"1": {"id": 1, "name": "", "price": 10000, "Conditions": ["NEW", 2015, "Excellent"]}, "3": {"id": 3, "name": "", "price": 18198, "Conditions": ["NEW", 2015]}, "4": {"id": 4, "name": "", "price": 500000, "Conditions": ["NEW", 2015]}, "5": {"id": 5, "name": "", "price": 25000, "Conditions": ["NEW", 2015, "January"]}}
2 rows in set (0.00 sec)

mysql>

WL#9191: JSON_PRETTY function
https://dev.mysql.com/worklog/task/?id=9191

User Feedback from presenting JSON features has suggested that we are missing a function to format JSON in a human-readable way (with new lines and indentation).

This functionality is available in both PHP and PostgreSQL under the name “pretty”:


mysql> select body from T_JSON_DOC where id = 1\G             
*************************** 1. row ***************************
body: {"id": 1, "name": "", "price": 10000, "Conditions": ["NEW", 2015, "Excellent"]}
1 row in set (0.00 sec)

mysql> select json_pretty(body) from T_JSON_DOC where id = 1\G
*************************** 1. row ***************************
json_pretty(body): {
  "id": 1,
  "name": "",
  "price": 10000,
  "Conditions": [
    "NEW",
    2015,
    "Excellent"
  ]
}
1 row in set (0.00 sec)

mysql>

その他のJSON関数（運用向け）

JSON_STORAGE_FREE(json_val)
For a JSON column value, this function shows how much storage space was freed in its binary representation after it was updated in place using JSON_SET() or JSON_REPLACE(). The argument can also be a valid JSON document or a string which can be parsed as one—either as a literal value or as the value of a user variable—in which case the function returns 0.

※Updating the column without using JSON_SET() (or JSON_REPLACE()) means that the optimizer cannot perform the update in place; in this case, JSON_STORAGE_FREE() returns 0.

JSON_STORAGE_SIZE(json_val)
This function returns the number of bytes used to store the binary representation of a JSON document.


mysql> SELECT 
    ->     jcol, 
    ->     JSON_STORAGE_SIZE(jcol) AS Size, 
    ->     JSON_STORAGE_FREE(jcol) AS Free 
    -> FROM jtable;

JSON_STORAGE_SIZE(): Return value (bytes)
JSON_STORAGE_FREE(): If no updates have yet been performed, this is 0, as expected.


SELECT
    ->     JSON_STORAGE_SIZE('[100, "sakila", [1, 3, 5], 425.05]') AS A,
    ->     JSON_STORAGE_SIZE('{"a": 1000, "b": "a", "c": "[1, 3, 5, 7]"}') AS B,
    ->     JSON_STORAGE_SIZE('{"a": 1000, "b": "wxyz", "c": "[1, 3, 5, 7]"}') AS C, 
    ->     JSON_STORAGE_SIZE('[100, "json", [[10, 20, 30], 3, 5], 425.05]') AS D;

154月 2016

mysqlxとMySQL Shell初期設定と基本動作確認

by rdbms

先日、リリースされたMySQL5.7.12に含まれるmysqlxプラグインと、
同時にリリースされた、MySQL Shellの初期設定と基本動作確認を確認してみました。
基本的にこれらは、MySQL5.7で追加されたJSONデータ型を含むスキーマレスなドキュメントデータを、
mysqlxプラグインを利用する事でより柔軟に処理出来るようにする為に追加され、
MySQLをドキュメントデータベースとしてより利用し易くする為に追加された機能となっています。

MySQL Shellは、MySQLサーバの開発と管理をサポートする、
JavaScript、Python、SQLの対話型のインタフェースをサポートするMySQLの新しいコンポーネント。
データのクエリと更新処理だけでなく、様々な管理操作を実行する為にMySQLのシェルを使用することができます。
※MySQL Shellを利用する為には、事前にMySQL5.7.12以降に含まれているXプラグインをインストールしておく必要があります。

Xプラグイン
Xプロトコルを使用して通信を可能にするMySQLサーバプラグイン。
X DevAPIを実装するクライアントをサポートし、ドキュメントストアとしてMySQLを使用することができます。

Xプロトコル
Xプロトコルは、Xプラグインを実行しているMySQLサーバーと通信します。
Xプロトコルは、SSL経由でCRUDとSQL操作、認証の両方をサポートしていて、
コマンドのストリーミング処理を可能にし、プロトコルとメッセージレイヤ上に拡張可能。

root@localhost [information_schema]> select PLUGIN_NAME,PLUGIN_VERSION,PLUGIN_DESCRIPTION from plugins
    -> where PLUGIN_NAME = 'mysqlx';
+-------------+----------------+--------------------+
| PLUGIN_NAME | PLUGIN_VERSION | PLUGIN_DESCRIPTION |
+-------------+----------------+--------------------+
| mysqlx      | 1.0            | X Plugin for MySQL |
+-------------+----------------+--------------------+
1 row in set (0.00 sec)

root@localhost [information_schema]>

【X Pluginインストール】
参照：http://dev.mysql.com/doc/refman/5.7/en/x-plugin-installation.html

通常通り、INSTALL PLUGINコマンドでインストール

root@localhost [mysql]> INSTALL PLUGIN mysqlx SONAME 'mysqlx.so';
Query OK, 0 rows affected (0.25 sec)

root@localhost [mysql]> show plugins;
+----------------------------+----------+--------------------+-------------------+-------------+
| Name                       | Status   | Type               | Library           | License     |
+----------------------------+----------+--------------------+-------------------+-------------+
| binlog                     | ACTIVE   | STORAGE ENGINE     | NULL              | PROPRIETARY |
| mysql_native_password      | ACTIVE   | AUTHENTICATION     | NULL              | PROPRIETARY |
| sha256_password            | ACTIVE   | AUTHENTICATION     | NULL              | PROPRIETARY |
| InnoDB                     | ACTIVE   | STORAGE ENGINE     | NULL              | PROPRIETARY |
| INNODB_TRX                 | ACTIVE   | INFORMATION SCHEMA | NULL              | PROPRIETARY |
| INNODB_LOCKS               | ACTIVE   | INFORMATION SCHEMA | NULL              | PROPRIETARY |
| INNODB_LOCK_WAITS          | ACTIVE   | INFORMATION SCHEMA | NULL              | PROPRIETARY |
| INNODB_CMP                 | ACTIVE   | INFORMATION SCHEMA | NULL              | PROPRIETARY |
<SNIP>
| MyISAM                     | ACTIVE   | STORAGE ENGINE     | NULL              | PROPRIETARY |
| MEMORY                     | ACTIVE   | STORAGE ENGINE     | NULL              | PROPRIETARY |
| CSV                        | ACTIVE   | STORAGE ENGINE     | NULL              | PROPRIETARY |
| BLACKHOLE                  | ACTIVE   | STORAGE ENGINE     | NULL              | PROPRIETARY |
| partition                  | ACTIVE   | STORAGE ENGINE     | NULL              | PROPRIETARY |
| FEDERATED                  | DISABLED | STORAGE ENGINE     | NULL              | PROPRIETARY |
| ARCHIVE                    | ACTIVE   | STORAGE ENGINE     | NULL              | PROPRIETARY |
| ngram                      | ACTIVE   | FTPARSER           | NULL              | PROPRIETARY |
| mecab                      | ACTIVE   | FTPARSER           | libpluginmecab.so | PROPRIETARY |
| MYSQL_FIREWALL             | ACTIVE   | AUDIT              | firewall.so       | PROPRIETARY |
| MYSQL_FIREWALL_USERS       | ACTIVE   | INFORMATION SCHEMA | firewall.so       | PROPRIETARY |
| MYSQL_FIREWALL_WHITELIST   | ACTIVE   | INFORMATION SCHEMA | firewall.so       | PROPRIETARY |
| mysqlx                     | ACTIVE   | DAEMON             | mysqlx.so         | PROPRIETARY |
+----------------------------+----------+--------------------+-------------------+-------------+
49 rows in set (0.00 sec)

root@localhost [mysql]>

【X Plugin オプションと変数について】
設定パラメータに関しては、此方を参照下さい。
http://dev.mysql.com/doc/refman/5.7/en/x-plugin-option-variable-reference.html

【接続方法】
既に、mysqlshをインストール済みなので –sqlオプションを仕様してSQLを流し込んでみる。
mysqlxのPort33060(Default：mysqlx_port)から、ローカルのMySQLにアクセスしてSQLを実行しています。

【サンプルデータベース作成】
参照：
http://downloads.mysql.com/docs/world_x-db.zip
http://dev.mysql.com/doc/refman/5.7/en/mysql-shell-tutorial-javascript-download.html

mysqlsh経由でサンプルデータベースの作成して、mysqlクライアントで接続してスキーマとテーブルの確認

[root@misc01 MID2016]# mysqlsh -u admin -p --sql --recreate-schema world_x ＜ world_x.sql
Enter password: 
Recreating schema world_x...

[root@misc01 MID2016]# mysql -u root -p -e "show databases like 'world%'"
Enter password: 
+-------------------+
| Database (world%) |
+-------------------+
| world             |
| world2            |
| world_x           |
+-------------------+
[root@misc01 MID2016]# mysql -u root -p -e "show tables from world_x"
Enter password: 
+-------------------+
| Tables_in_world_x |
+-------------------+
| City              |
| Country           |
| CountryInfo       |
| CountryLanguage   |
+-------------------+

【接続モード】
参考)　接続方法としては、現状では、以下のモードを選択できるようです。

# mysqlsh --help
MySQL Shell 1.0.3 Development Preview

<SNIP>

  --sql                    Start in SQL mode using a node session.
  --sqlc                   Start in SQL mode using a classic session.
  --js                     Start in JavaScript mode.
  --py                     Start in Python mode.

<SNIP>

こちらでは、mysqlsh経由でjava scriptモードの状態でJSONデータを処理してみます。


[root@misc01 MID2016]# mysqlsh -u admin -p 
Creating an X Session to admin@localhost:33060
Enter password: 
No default schema selected.

Welcome to MySQL Shell 1.0.3 Development Preview

Copyright (c) 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type '\help', '\h' or '\?' for help.

Currently in JavaScript mode. Use \sql to switch to SQL mode and execute queries.
mysql-js> news_collection = session.getSchema('NEW57').createCollection("X_JSON");
<Collection:X_JSON>
mysql-js> news_collection.add({"id": 1, "name": "Document Data", "price": 60000, "Conditions": ["NEW", 2016]});
Query OK, 1 item affected (0.01 sec)

mysql-js> news_collection.find('name like :n').bind('n','Document%');
[
    {
        "Conditions": [
            "NEW",
            2016
        ],
        "_id": "26c8af795703e611630c0800279cea3c",
        "id": 1,
        "name": "Document Data",
        "price": 60000
    }
]
1 document in set (0.03 sec)

mysql-js>

MySQLに接続してデータを確認した状態


root@localhost [NEW57]> desc X_JSON;
+-------+-------------+------+-----+---------+------------------+
| Field | Type        | Null | Key | Default | Extra            |
+-------+-------------+------+-----+---------+------------------+
| doc   | json        | YES  |     | NULL    |                  |
| _id   | varchar(32) | NO   | PRI | NULL    | STORED GENERATED |
+-------+-------------+------+-----+---------+------------------+
2 rows in set (0.00 sec)

root@localhost [NEW57]> select * from X_JSON\G
*************************** 1. row ***************************
doc: {"id": 1, "_id": "26c8af795703e611630c0800279cea3c", "name": "Document Data", "price": 60000, "Conditions": ["NEW", 2016]}
_id: 26c8af795703e611630c0800279cea3c
1 row in set (0.00 sec)

root@localhost [NEW57]>

【Beta Draft】
X DevAPI User Guide / Overview
http://dev.mysql.com/doc/x-devapi-userguide/en/devapi-users-introduction.html
その他、X DevAPI経由でコーディング出来そう。但し、現時点ではベータリリースなのでもう少ししたら確認。

【その他：Status変数】
こちらで、mysqlx経由の処理を確認する事が可能です。


[root@misc01 MID2016]# mysql -u root -p -e "show status like 'mysqlx%'"
Enter password: 
+-------------------------------------+--------------------------+
| Variable_name                       | Value                    |
+-------------------------------------+--------------------------+
| Mysqlx_bytes_received               | 389773                   |
| Mysqlx_bytes_sent                   | 3466                     |
| Mysqlx_connection_accept_errors     | 0                        |
| Mysqlx_connection_errors            | 0                        |
| Mysqlx_connections_accepted         | 2                        |
| Mysqlx_connections_closed           | 2                        |
| Mysqlx_connections_rejected         | 0                        |
| Mysqlx_crud_delete                  | 0                        |
| Mysqlx_crud_find                    | 0                        |
| Mysqlx_crud_insert                  | 0                        |
| Mysqlx_crud_update                  | 0                        |
| Mysqlx_errors_sent                  | 1                        |
| Mysqlx_expect_close                 | 0                        |
| Mysqlx_expect_open                  | 0                        |
| Mysqlx_init_error                   | 1                        |
| Mysqlx_notice_other_sent            | 76                       |
| Mysqlx_notice_warning_sent          | 2                        |
| Mysqlx_rows_sent                    | 18                       |
| Mysqlx_sessions                     | 0                        |
| Mysqlx_sessions_accepted            | 1                        |
| Mysqlx_sessions_closed              | 1                        |
| Mysqlx_sessions_fatal_error         | 0                        |
| Mysqlx_sessions_killed              | 0                        |
| Mysqlx_sessions_rejected            | 1                        |
| Mysqlx_ssl_accepts                  | 0                        |
| Mysqlx_ssl_active                   |                          |
| Mysqlx_ssl_cipher                   |                          |
| Mysqlx_ssl_cipher_list              |                          |
| Mysqlx_ssl_ctx_verify_depth         | 18446744073709551615     |
| Mysqlx_ssl_ctx_verify_mode          | 5                        |
| Mysqlx_ssl_finished_accepts         | 0                        |
| Mysqlx_ssl_server_not_after         | Oct 19 05:28:15 2025 GMT |
| Mysqlx_ssl_server_not_before        | Oct 22 05:28:15 2015 GMT |
| Mysqlx_ssl_verify_depth             |                          |
| Mysqlx_ssl_verify_mode              |                          |
| Mysqlx_ssl_version                  |                          |
| Mysqlx_stmt_create_collection       | 0                        |
| Mysqlx_stmt_create_collection_index | 0                        |
| Mysqlx_stmt_disable_notices         | 0                        |
| Mysqlx_stmt_drop_collection         | 0                        |
| Mysqlx_stmt_drop_collection_index   | 0                        |
| Mysqlx_stmt_enable_notices          | 0                        |
| Mysqlx_stmt_execute_sql             | 69                       |
| Mysqlx_stmt_execute_xplugin         | 2                        |
| Mysqlx_stmt_kill_client             | 0                        |
| Mysqlx_stmt_list_clients            | 0                        |
| Mysqlx_stmt_list_notices            | 0                        |
| Mysqlx_stmt_list_objects            | 2                        |
| Mysqlx_stmt_ping                    | 0                        |
| Mysqlx_worker_threads               | 2                        |
| Mysqlx_worker_threads_active        | 0                        |
+-------------------------------------+--------------------------+
[root@misc01 MID2016]#

参考までに、MySQL ShellのSQLモードの場合とjavascriptモードの場合では接続が3306か33060からの接続かどうかは以下のステータス変数でも確認する事が可能です。

【SQLモードの場合】


[root@misc01 MID2016]# echo "INSERT INTO T_UC01 VALUES (1,'mysql shell'),(2,'ﾏｲｴｽｷｭｳｴﾙｼｪﾙ');" | mysqlsh -u demo_user -ppassword --sql --schema=world_x2
mysqlx: [Warning] Using a password on the command line interface can be insecure.
[root@misc01 MID2016]#


root@localhost [sys]> show status like 'Mysqlx_crud%';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| Mysqlx_crud_delete | 0     |
| Mysqlx_crud_find   | 0     |
| Mysqlx_crud_insert | 0     |
| Mysqlx_crud_update | 0     |
+--------------------+-------+
4 rows in set (0.00 sec)

root@localhost [sys]> root@localhost [sys]> show status like 'Mysqlx_crud%';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| Mysqlx_crud_delete | 0     |
| Mysqlx_crud_find   | 0     |
| Mysqlx_crud_insert | 0     |
| Mysqlx_crud_update | 0     |
+--------------------+-------+
4 rows in set (0.00 sec)

root@localhost [sys]>

【JavaScriptモードの場合】


[root@misc01 MID2016]# mysqlsh --uri demo_user@localhost/NEW57 -ppassword
mysqlx: [Warning] Using a password on the command line interface can be insecure.
Creating an X Session to demo_user@localhost:33060/NEW57
Default schema `NEW57` accessible through db.

Welcome to MySQL Shell 1.0.3 Development Preview

Copyright (c) 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type '\help', '\h' or '\?' for help.

Currently in JavaScript mode. Use \sql to switch to SQL mode and execute queries.
mysql-js> db.createCollection("x_posts");
<Collection:x_posts>
mysql-js> db.x_posts.add({"title":"Hello World", "text":"This is the first post via mysqlx"});
Query OK, 1 item affected (0.01 sec)

mysql-js> db.x_posts.find("title = 'Hello World'").sort(["title"]);
[
    {
        "_id": "d2c6eb188b08e6113d110800279cea3c",
        "text": "This is the first post via mysqlx",
        "title": "Hello World"
    }
]
1 document in set (0.00 sec)

mysql-js>

mysqlx経由のCRUDの実行数が確認出来る。
INSERT　x　1回
SELECT x 1回


root@localhost [sys]> show status like 'Mysqlx_crud%';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| Mysqlx_crud_delete | 0     |
| Mysqlx_crud_find   | 0     |
| Mysqlx_crud_insert | 0     |
| Mysqlx_crud_update | 0     |
+--------------------+-------+
4 rows in set (0.00 sec)

root@localhost [sys]> show status like 'Mysqlx_crud%';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| Mysqlx_crud_delete | 0     |
| Mysqlx_crud_find   | 1     |
| Mysqlx_crud_insert | 1     |
| Mysqlx_crud_update | 0     |
+--------------------+-------+
4 rows in set (0.01 sec)

root@localhost [sys]>

【参考】
http://dev.mysql.com/doc/refman/5.7/en/mysql-shell.html
http://dev.mysql.com/doc/refman/5.7/en/document-store.html
http://dev.mysql.com/doc/dev/connector-nodejs/
http://mysqlserverteam.com/mysql-5-7-12-part-3-more-than-just-sql/
http://mysqlserverteam.com/mysql-5-7-12-part-4-a-new-mysql-command-line-shell/

メモ：Node.jsのサンプルもあるので、後日、別途JSONドキュメント処理を実施してみる。
http://dev.mysql.com/doc/dev/connector-nodejs/

266月 2015

MySQL ClusterによるNoSQL処理(ClusterJ)

by rdbms

NoSQL with MySQL Cluster
本日、話をさせて頂いた、MySQL Clusterにおけるトランザクション対応NoSQLについての資料です。

No sql with mysql cluster (MyNA・JPUG合同DB勉強会) from Shinya Sugiyama

MySQL ClusterはMySQLとは基本的には異なるデータベースです。
NDB(Network Database)に対して、MySQLからもNoSQLからもデータ処理出来るデータベースがMySQL Clusterです。
MySQLからはもともとストレージエンジンを選択出来るデータベースシステムですが、
MySQLはndbclusterというストレージエンジンとしてNDBを利用しています。

NDBはNDB API (C++)を経由してアクセスする事で,MySQLから独立して利用する事が可能です。
MySQLサーバの観点からは,NDB Clusterは行のテーブルを格納するためのストレージエンジンです。
NDB Clusterの観点からは、MySQLサーバインスタンスがクラスタに接続されているAPIのプロセスの一つです。

概要図

Storage Engine

ndbinfo
データは、冗長化と拡張性の為にデータノード間で分散されています。
その状況は、ndbinfoを確認する事でも確認可能です。

検証
先ずは、MySQL Clusterをダウンロードして頂き、MySQL Clsuterを設定して下さい。
設定が終了したら、MySQL ClusterにSQLで接続しスキーマとテスト用のテーブルを作成して下さい。
NDB APIからNoSQLでもNDBにコマンドでテーブルを作成出来ますが、その場合はMySQLからオブジェクトを見る事が出来ません。
MySQLとNoSQL両方から使いたい場合は、MySQLにて先ずはオブジェクト作成して下さい。

Download MySQL Cluster
https://dev.mysql.com/downloads/cluster/

18.2.2. Linux での MySQL Cluster のインストール
https://dev.mysql.com/doc/refman/5.6/ja/mysql-cluster-install-linux.html

5分で作るMySQL Cluster環境
http://www.ospn.jp/osc2013-kyoto/pdf/osc2013kyoto_mysql2.pdf


mysql> use TEST_DB
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show create table employee\G
*************************** 1. row ***************************
       Table: employee
Create Table: CREATE TABLE `employee` (
  `id` int(11) NOT NULL,
  `first` varchar(64) DEFAULT NULL,
  `last` varchar(64) DEFAULT NULL,
  `municipality` varchar(64) DEFAULT NULL,
  `started` varchar(64) DEFAULT NULL,
  `ended` varchar(64) DEFAULT NULL,
  `department` int(11) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_u_hash` (`first`,`last`) USING HASH,
  KEY `idx_municipality` (`municipality`)
) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

mysql>

ClusterJ サンプルソース
MySQLClusterJ_Sample

MySQL Clusterへの接続文字列は適宜変更して下さい。
— com.mysql.clusterj.connectstring
— com.mysql.clusterj.database=TEST_DB

[SELL]

[root@Labs01 java]# cat clusterj.properties
# Copyright (c) 2011, Oracle and/or its affiliates. All rights reserved.
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; version 2 of the License.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA

com.mysql.clusterj.connectstring=192.168.56.114:1186
com.mysql.clusterj.database=TEST_DB
com.mysql.clusterj.connect.retries=4
com.mysql.clusterj.connect.delay=5
com.mysql.clusterj.connect.verbose=1
com.mysql.clusterj.connect.timeout.before=30
com.mysql.clusterj.connect.timeout.after=20
com.mysql.clusterj.max.transactions=1024
[root@Labs01 java]#

[/SHELL]

コンパイル
※　javacにてコンパイル時には、clusterj-api-7.x.x.jarを含める必要があります。
※　パスは適宜書き換えて下さい。


javac -classpath /mysql-cluster-gpl-7.4.6/746bin/share/java/clusterj-api-7.4.6.jar:. Main.java Employee.java

実行例


java -classpath /mysql-cluster-gpl-7.4.6/746bin/share/java/clusterj-7.4.6.jar:. -Djava.library.path=/mysql-cluster-gpl-7.4.6/746bin/lib Main

上記の実行内容は、先にスキーマをSQLから作成してあるのでSQLでも見る事が出来ます。

それ以外の方法としては、NDB用にMySQL Clusterに用意されている以下のコマンドを利用すると良いでしょう。

ndb_show_tables

ndb_select_all

SQLが得意な処理は、SQLで処理して、Primary Keyベースでの特定のデータに対しての処理などは、
MySQL ClusterでのNoSQLで処理するという方法も選択出来ます。
NDB APIはC++ですので、C++が得意な方はそのままC++で書いて高速な処理を検証してみるのも良いかと思います。
※ネットワークは重要です、トラフィックが多い場合は10Gなどの高速なネットワークをご利用下さい。

098月 2014

ps_helper by dbahelper for Get Data from Performance_Schema

by rdbms

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

068月 2014

PythonからXLSを操作するモジュール

by rdbms

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

048月 2014

PythonによるInsert処理確認

by rdbms

PythonによるInsert処理確認
-　コネクションやSQL実行時にcommitを入れておく。

現状確認

mysql> select * from language;
+-------------+----------+---------------------+
| language_id | name     | last_update         |
+-------------+----------+---------------------+
|           1 | English  | 2006-02-15 05:02:19 |
|           2 | Italian  | 2006-02-15 05:02:19 |
|           3 | Japanese | 2014-07-15 16:14:50 |
|           4 | Mandarin | 2006-02-15 05:02:19 |
|           5 | French   | 2006-02-15 05:02:19 |
|           6 | German   | 2006-02-15 05:02:19 |
+-------------+----------+---------------------+
6 rows in set (0.00 sec)

前回のSQL処理を編集して再利用


$ vim mysql_test.py
$ cat mysql_test.py
# coding: utf-8

try:
 # import

 import mysql.connector

 # 接続
 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',())

 insert_stmt = "insert into language (name) values ('Chinese')"
 cursor.execute(insert_stmt)
 connect.commit()

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

except Exception as myout:

 print(myout)

$
$ python mysql_test.py

実行後の結果
検証していたので、language_idのIdentityがずれているが
問題なく処理されている事が確認出来た。

mysql> select * from language;
+-------------+----------+---------------------+
| language_id | name     | last_update         |
+-------------+----------+---------------------+
|           1 | English  | 2006-02-15 05:02:19 |
|           2 | Italian  | 2006-02-15 05:02:19 |
|           3 | Japanese | 2014-07-15 16:14:50 |
|           4 | Mandarin | 2006-02-15 05:02:19 |
|           5 | French   | 2006-02-15 05:02:19 |
|           6 | German   | 2006-02-15 05:02:19 |
|           9 | Chinese  | 2014-08-04 11:28:02 |
+-------------+----------+---------------------+
7 rows in set (0.00 sec)

mysql>

とりあえず、確認出来たのでテーブルのデータを整理しておく為に、
おまけ処理で、不要なデータを削除して、Alter TableでIndentityを直しておく。

mysql> select * from language;
+-------------+----------+---------------------+
| language_id | name     | last_update         |
+-------------+----------+---------------------+
|           1 | English  | 2006-02-15 05:02:19 |
|           2 | Italian  | 2006-02-15 05:02:19 |
|           3 | Japanese | 2014-07-15 16:14:50 |
|           4 | Mandarin | 2006-02-15 05:02:19 |
|           5 | French   | 2006-02-15 05:02:19 |
|           6 | German   | 2006-02-15 05:02:19 |
|           9 | Chinese  | 2014-08-04 11:28:02 |
+-------------+----------+---------------------+
7 rows in set (0.00 sec)

mysql> delete from language where language_id = 9;
Query OK, 1 row affected (0.00 sec)

mysql> alter table language auto_increment = 7;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table language\G
*************************** 1. row ***************************
       Table: language
Create Table: CREATE TABLE `language` (
  `language_id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
  `name` char(20) NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`language_id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql>

mysql> select * from language;
+-------------+----------+---------------------+
| language_id | name     | last_update         |
+-------------+----------+---------------------+
|           1 | English  | 2006-02-15 05:02:19 |
|           2 | Italian  | 2006-02-15 05:02:19 |
|           3 | Japanese | 2014-07-15 16:14:50 |
|           4 | Mandarin | 2006-02-15 05:02:19 |
|           5 | French   | 2006-02-15 05:02:19 |
|           6 | German   | 2006-02-15 05:02:19 |
+-------------+----------+---------------------+
6 rows in set (0.00 sec)

2回実行してデータをとりあえず、再度処理しておく。

$ python mysql_test.py

データ確認とデータの追加してIdentityを8まで追加。

mysql> select * from language;
+-------------+----------+---------------------+
| language_id | name     | last_update         |
+-------------+----------+---------------------+
|           1 | English  | 2006-02-15 05:02:19 |
|           2 | Italian  | 2006-02-15 05:02:19 |
|           3 | Japanese | 2014-07-15 16:14:50 |
|           4 | Mandarin | 2006-02-15 05:02:19 |
|           5 | French   | 2006-02-15 05:02:19 |
|           6 | German   | 2006-02-15 05:02:19 |
|           7 | Chinese  | 2014-08-04 11:36:23 |
+-------------+----------+---------------------+
7 rows in set (0.00 sec)

mysql>

mysql> select @max := max(language_id)+ 1 from language;
+-----------------------------+
| @max := MAX(language_id)+ 1 |
+-----------------------------+
|                           8 |
+-----------------------------+
1 row in set (0.01 sec)

mysql> select * from language;
+-------------+----------+---------------------+
| language_id | name     | last_update         |
+-------------+----------+---------------------+
|           1 | English  | 2006-02-15 05:02:19 |
|           2 | Italian  | 2006-02-15 05:02:19 |
|           3 | Japanese | 2014-07-15 16:14:50 |
|           4 | Mandarin | 2006-02-15 05:02:19 |
|           5 | French   | 2006-02-15 05:02:19 |
|           6 | German   | 2006-02-15 05:02:19 |
|           7 | Chinese  | 2014-08-04 11:36:23 |
|           8 | Korean   | 2014-08-04 14:20:16 |
+-------------+----------+---------------------+
8 rows in set (0.00 sec)

mysql>

今後の検証環境構築用に、追加でMultiple Insertにて複数データ登録確認してみた
実行してみる

$ cat mysql_test.py
# coding: utf-8

try:
 # import

 import mysql.connector

 # 接続
 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',())

 # insert_stmt = "insert into language (name) values ('Korean')"
 insert_stmt = "insert into language (name) values ('Ainu'),('Irish')"
 cursor.execute(insert_stmt)
 # connect.commit()

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

except Exception as myout:

 print(myout)

$
$ python mysql_test.py
$

データが登録されている事を確認

mysql> select * from language;
+-------------+----------+---------------------+
| language_id | name     | last_update         |
+-------------+----------+---------------------+
|           1 | English  | 2006-02-15 05:02:19 |
|           2 | Italian  | 2006-02-15 05:02:19 |
|           3 | Japanese | 2014-07-15 16:14:50 |
|           4 | Mandarin | 2006-02-15 05:02:19 |
|           5 | French   | 2006-02-15 05:02:19 |
|           6 | German   | 2006-02-15 05:02:19 |
|           7 | Chinese  | 2014-08-04 11:36:23 |
|           8 | Korean   | 2014-08-04 14:20:16 |
|           9 | Ainu     | 2014-08-04 14:44:03 |
|          10 | Irish    | 2014-08-04 14:44:03 |
+-------------+----------+---------------------+
10 rows in set (0.00 sec)

mysql>

Reference:
How to Reset an MySQL AutoIncrement using a MAX value from another table?

317月 2014

FIrst Step of Python

by rdbms

OpenStackでもMySQL UtilityでもPythonが使われていて、
関わっていく事も多いと思うので、少しずつPythonにCatch Upしておく。

Download the latest version for Windows　2.7.8
https://www.python.org/downloads/

admin@local#cat hello_world.py
# coding: utf-8

print "Hello World"
print "こんにちはPythonです"

import datetime # Import datetime Module
import locale   # import locale

d = datetime.datetime.today()

# year, month, day
print '%s年%s月%s日\n' % (d.year, d.month, d.day)

admin@local#python hello_world.py
Hello World
こんにちはPythonです
2014年7月30日

admin@local#

admin@local#cat count_row.py
# coding: utf-8

import sys
print "行数"
print len(sys.stdin.readlines())

admin@local#vim test
admin@local#cat test
1
2
3
4
5
6
7
admin@local#python count_row.py < test
行数
7
admin@local#
[/SHELL]


■MySQLと接続してみる
http://dev.mysql.com/downloads/connector/python/
Windows (Architecture Independent), MSI Installer
Python 2.7をインストールしました。

[SHELL]
$ cat mysql_test.py
# coding: utf-8

try:
 # import

 import mysql.connector

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

 # カーソル
 cursor = connect.cursor()
 # SQL 発行
 cursor.execute('select language_id,name from language', ())
 # フェッチ
 rows = cursor.fetchall()
 print(rows)
 cursor.close()
 # 切断
 connect.close()

except Exception as myout:

 print(myout)

$
[/SHELL]

テーブルの中身はこんな感じ
[SQL]
mysql> select language_id,name from language;
+-------------+----------+
| language_id | name     |
+-------------+----------+
|           1 | English  |
|           2 | Italian  |
|           3 | Japanese |
|           4 | Mandarin |
|           5 | French   |
|           6 | German   |
+-------------+----------+
6 rows in set (0.00 sec)

mysql>
[/SQL]


■実行するとこんな感じでアウトプットが出てくる。

[SHELL]

$ python mysql_test.py
[(1, u'English'), (2, u'Italian'), (3, u'Japanese'), (4, u'Mandarin'), (5, u'French'), (6, u'German')]

$

■Sample ScriptフォルダーにあるGoogle Searchのスクリプトは
　引数を後ろに付けられる。試しに確認してみた。

$ cat google.py
#! /usr/bin/env python

import sys, webbrowser

def main():
    args = sys.argv[1:]
    if not args:
        print "Usage: %s querystring" % sys.argv[0]
        return
    list = []
    for arg in args:
        if '+' in arg:
            arg = arg.replace('+', '%2B')
        if ' ' in arg:
            arg = '"%s"' % arg
        arg = arg.replace(' ', '+')
        list.append(arg)
    s = '+'.join(list)
    url = "http://www.google.com/search?q=%s" % s
    webbrowser.open(url)

if __name__ == '__main__':
    main()

$

297月 2014

MySQL Utilities (mysqlmetagrep)

by rdbms

MySQL Utilities (mysqlmetagrep)
Reference: http://thinkit.co.jp/story/2014/02/10/4814
mysqlmetagrep search metadata
DBのメタデータを表示してくれるコマンド

mysqluc> mysqlmetagrep --help
MySQL Utilities mysqlmetagrep.exe version 1.4.3 (part of MySQL Workbench Distribution 6.0.0)
License type: GPLv2
Usage: mysqlmetagrep.exe --server=user:pass@host:port:socket [options] pattern

mysqlmetagrep - search metadata

Options:
  --version             show program's version number and exit
  --help                display a help message and exit
  --license             display program's license and exit
  --server=SERVER       connection information for the server in the form:
                        <user>[:<password>]@<host>[:<port>][:<socket>] or
                        <login-path>[:<port>][:<socket>].
  --character-set=CHARSET
                        sets the client character set. The default is
                        retrieved from the server variable
                        'character_set_client'.
  -b, --body            search the body of routines, triggers, and events as
                        well
  --search-objects=OBJECT_TYPES, --object-types=OBJECT_TYPES
                        the object type to search in: a comma-separated list
                        of one or more of: 'database', 'trigger', 'user',
                        'routine', 'column', 'table', 'partition', 'event',
                        'view'
  -G, --basic-regexp, --regexp
                        use 'REGEXP' operator to match pattern. Default is to
                        use 'LIKE'.
  -p, --print-sql, --sql
                        print the statement instead of sending it to the
                        server
  -e PATTERN, --pattern=PATTERN
                        pattern to use when matching. Required if the pattern
                        looks like a connection specification.
  --database=DATABASE_PATTERN
                        only look at objects in databases matching this
                        pattern
  -f FORMAT, --format=FORMAT
                        display the output in either grid (default), tab, csv,
                        or vertical format

mysqluc>

データベースオブジェクトは、もちろんinformation_schemaをselectすれば確認出来るが、
mysqlmetagrepコマンドを利用しても確認出来る。
運用によっては、色々なケースが考えられるので使い分けても良いかと思います。

SQLコマンドの場合

mysql> select TABLE_SCHEMA,TABLE_NAME,ENGINE,ROW_FORMAT,CREATE_TIME from information_schema.tables where TABLE_TYPE = 'BASE TABLE' and TABLE_SCHEMA like 't%';
+--------------+------------+--------+------------+---------------------+
| TABLE_SCHEMA | TABLE_NAME | ENGINE | ROW_FORMAT | CREATE_TIME         |
+--------------+------------+--------+------------+---------------------+
| test         | db8        | MyISAM | Fixed      | 2014-07-11 16:49:34 |
| test         | language   | InnoDB | Compact    | 2014-07-29 10:23:26 |
| test         | lck        | InnoDB | Compact    | 2014-07-07 17:43:20 |
| test         | montable   | InnoDB | Compact    | 2014-07-23 12:58:38 |
| test         | t          | InnoDB | Compact    | 2014-07-24 15:09:26 |
+--------------+------------+--------+------------+---------------------+
5 rows in set (0.01 sec)

mysql>

MySQL Utilities

mysqluc> mysqlmetagrep --server=root:password@localhost:3306 -e 'test' --database='t%'
+------------------------+--------------+--------------+-----------+-------------+----------+
| Connection             | Object Type  | Object Name  | Database  | Field Type  | Matches  |
+------------------------+--------------+--------------+-----------+-------------+----------+
| root:*@localhost:3306  | SCHEMA       | test         | test      | DATABASE    | test     |
+------------------------+--------------+--------------+-----------+-------------+----------+

mysqluc>

mysqluc> mysqlmetagrep --server=root:password@localhost:3306 -e 't%' --database='t%'
+------------------------+--------------+--------------+-----------+-------------+---------------+
| Connection             | Object Type  | Object Name  | Database  | Field Type  | Matches       |
+------------------------+--------------+--------------+-----------+-------------+---------------+
| root:*@localhost:3306  | SCHEMA       | test         | test      | DATABASE    | test          |
| root:*@localhost:3306  | TABLE        | db8          | test      | COLUMN      | Trigger_priv  |
| root:*@localhost:3306  | TABLE        | t            | test      | TABLE       | t             |
+------------------------+--------------+--------------+-----------+-------------+---------------+

mysqluc>


mysqluc> mysqlmetagrep --server=root:password@localhost:3306 -e 't%' --database='t%' --format=vertical
*************************       1. row *************************
  Connection: root:*@localhost:3306
 Object Type: SCHEMA
 Object Name: test
    Database: test
  Field Type: DATABASE
     Matches: test
*************************       2. row *************************
  Connection: root:*@localhost:3306
 Object Type: TABLE
 Object Name: db8
    Database: test
  Field Type: COLUMN
     Matches: Trigger_priv
*************************       3. row *************************
  Connection: root:*@localhost:3306
 Object Type: TABLE
 Object Name: t
    Database: test
  Field Type: TABLE
     Matches: t
3 rows.

mysqluc>




mysqluc> mysqlmetagrep --server=root:password@localhost:3306 -e 'd%' --database='t%'
+------------------------+--------------+--------------+-----------+-------------+---------------------------+
| Connection             | Object Type  | Object Name  | Database  | Field Type  | Matches                   |
+------------------------+--------------+--------------+-----------+-------------+---------------------------+
| root:*@localhost:3306  | TABLE        | db8          | test      | COLUMN      | Drop_priv,Delete_priv,Db  |
| root:*@localhost:3306  | TABLE        | db8          | test      | TABLE       | db8                       |
+------------------------+--------------+--------------+-----------+-------------+---------------------------+

mysqluc>


mysqluc> mysqlmetagrep --server=root:password@localhost:3306 -e 'd%' --search-objects=table,view --database='t%'
+------------------------+--------------+--------------+-----------+-------------+----------+
| Connection             | Object Type  | Object Name  | Database  | Field Type  | Matches  |
+------------------------+--------------+--------------+-----------+-------------+----------+
| root:*@localhost:3306  | TABLE        | db8          | test      | TABLE       | db8      |
+------------------------+--------------+--------------+-----------+-------------+----------+

mysqluc>

297月 2014

MySQL Utilities (mysqldiskusage)

by rdbms

MySQL Utilities (mysqldiskusage)
Reference: http://thinkit.co.jp/story/2014/02/10/4814
mysqldiskusage show disk usage for databases

こちらの、コマンドでディスク容量の確認出来ます。
selectでも算出できますが、実際のファイルサイズが確認出来るので
使いようによっては便利かもしれません。

mysqluc> mysqldiskusage --help
MySQL Utilities mysqldiskusage.exe version 1.4.3 (part of MySQL Workbench Distribution 6.0.0)
License type: GPLv2
Usage: mysqldiskusage.exe --server=user:pass@host:port:socket db1 --all

mysqldiskusage - show disk usage for databases

Options:
  --version             show program's version number and exit
  --help                display a help message and exit
  --license             display program's license and exit
  --server=SERVER       connection information for the server in the form:
                        <user>[:<password>]@<host>[:<port>][:<socket>] or
                        <login-path>[:<port>][:<socket>].
  -f FORMAT, --format=FORMAT
                        display the output in either grid (default), tab, csv,
                        or vertical format
  -h, --no-headers      do not show column headers
  -b, --binlog          include binary log usage
  -r, --relaylog        include relay log usage
  -l, --logs            include general and slow log usage
  -i, --innodb          include InnoDB tablespace usage
  -m, --empty           include empty databases
  -a, --all             show all usage including empty databases
  -v, --verbose         control how much information is displayed. e.g., -v =
                        verbose, -vv = more verbose, -vvv = debug
  -q, --quiet           turn off all messages for quiet execution.

mysqluc>


mysqluc> mysqldiskusage --server=root:password@localhost:3306
# Source on localhost: ... connected.
# Database totals:
+---------------------+-------------+
| db_name             |      total  |
+---------------------+-------------+
| copy_test           | 545,285     |
| mysql               | 1,785,056   |
| performance_schema  | 510,023     |
| sakila              | 30,495,478  |
| sys                 | 0           |
| test                | 545,224     |
| world               | 471,349     |
+---------------------+-------------+

Total database disk usage = 34,597,270 bytes or 32.99 MB

#...done.

mysqluc>


mysqluc> mysqldiskusage --server=root:password@localhost:3306 -a
# Source on localhost: ... connected.
# Database totals:
+---------------------+-------------+
| db_name             |      total  |
+---------------------+-------------+
| copy_test           | 545,285     |
| mysql               | 1,785,056   |
| performance_schema  | 510,023     |
| sakila              | 30,495,478  |
| sys                 | 0           |
| test                | 545,224     |
| world               | 471,349     |
+---------------------+-------------+

Total database disk usage = 34,597,270 bytes or 32.99 MB

# Log information.
# general_log information is not accessible. Check your permissions.
# slow_query_log information is not accessible. Check your permissions.
# log_error information is not accessible. Check your permissions.
# Binary log information:
Current binary log file = MySvr-bin.000020
+--------------------+----------+
| log_file           | size     |
+--------------------+----------+
| MySvr-bin.000001  | 143      |
| MySvr-bin.000002  | 4933143  |
| MySvr-bin.000003  | 729      |
| MySvr-bin.000004  | 2225     |
| MySvr-bin.000005  | 143      |
| MySvr-bin.000006  | 630      |
| MySvr-bin.000007  | 3584334  |
| MySvr-bin.000008  | 168      |
| MySvr-bin.000009  | 143      |
| MySvr-bin.000010  | 143      |
| MySvr-bin.000011  | 168      |
| MySvr-bin.000012  | 143      |
| MySvr-bin.000013  | 143      |
| MySvr-bin.000014  | 342      |
| MySvr-bin.000015  | 3482441  |
| MySvr-bin.000016  | 143      |
| MySvr-bin.000017  | 143      |
| MySvr-bin.000018  | 322      |
| MySvr-bin.000019  | 456      |
| MySvr-bin.000020  | 2443     |
| MySvr-bin.index   | 400      |
+--------------------+----------+

Total size of binary logs = 12,008,945 bytes or 11.45 MB

# Server is not an active slave - no relay log information.
# InnoDB tablespace information:
+--------------+-------------+
| innodb_file  |       size  |
+--------------+-------------+
| ib_logfile0  | 50,331,648  |
| ib_logfile1  | 50,331,648  |
| ibdata1      | 79,691,776  |
+--------------+-------------+

Total size of InnoDB files = 180,355,072 bytes or 172.00 MB

#...done.

mysqluc>

1 2 次へ »

Category Archives: Script

Slave側のSystem lockについて

NEW JSON FUNCTIONS in MySQL8.0

mysqlxとMySQL Shell初期設定と基本動作確認

MySQL ClusterによるNoSQL処理(ClusterJ)

ps_helper by dbahelper for Get Data from Performance_Schema

PythonからXLSを操作するモジュール

PythonによるInsert処理確認

FIrst Step of Python

MySQL Utilities (mysqlmetagrep)

MySQL Utilities (mysqldiskusage)

Post Navigation