オンラインで色々なDDL処理が出来るのは、サービス提供側も、利用者も、DBAとしても
非常に便利な機能かと思います。MySQLは4.1からUnicode対応していたので、
10年弱程利用してますが、Online DDLが利用可能になるまでは、
夜中のユーザーが少ない時間にテーブル定義を変更していたりする事も多々ありました。
MySQL5.6以降のオンラインDDLはそういったメンテナンス対応の方には、非常に便利で有難い機能かと思います。

オンライン処理可能かどうかは、14.11.1. オンライン DDL の概要で確認する事が出来ます。
手元で簡単に確認したい場合は、以下のオプションで処理を指定して確認する事が可能です。

ALGORITHM=INPLACE
ALGORITHM=COPY

インプレース DDL 操作とテーブルコピー DDL 操作のパフォーマンスの比較
オンライン DDL 操作の raw パフォーマンスは、その操作がインプレースで実行されるか、
またはテーブル全体のコピーと再構築が必要かによってほとんど決定されます。
インプレースで実行できる操作の種類や、テーブルコピー操作を行わないための
何らかの要件を確認するには、表14.5「DDL 操作のオンラインステータスのサマリー」を参照してください。

例)これは5.7の場合(varcharサイズ変更)


ALTER TABLE T_ONLINE_DDL ALGORITHM=INPLACE, CHANGE COLUMN text text VARCHAR(255);

※ オンライン DDL のロックオプション
LOCK=EXCLUSIVE 
クエリーと DML 操作の両方がブロックされます。
LOCK=SHARED
DDL 操作では、テーブルへの書き込みがすべてブロックされますが、
そのテーブル内のデータは読み取ることができます。
LOCK=NONE
DDL 操作では、クエリーと並列 DML の両方が許可されます。
LOCK=DEFAULT
LOCK 句が省略された DDL 操作では、MySQL はその種類の操作で
使用可能なもっとも低いレベルのロックを使用することにより、
可能な場合は常に並列クエリー、DML、またはその両方を許可します。

参照:
14.11.2. オンライン DDL でのパフォーマンスと並列性に関する考慮事項

参照:MySQL5.7におけるオンライン処理の拡張
Online ALTER TABLE Enhance Varchar Size

オンライン DDL
DDL (主に ALTER TABLE) 操作中の InnoDB テーブルのパフォーマンス、並列性、および可用性を改善する機能。
詳細は、操作の種類に応じて異なります。場合によっては、ALTER TABLE の進行中にテーブルを同時に変更できます。この操作はテーブルコピーを行わずに、または特別に最適化されたタイプのテーブルコピーを使用せずに実行できる場合があります。
領域の使用量は、innodb_online_alter_log_max_size 構成オプションで制御されます。

innodb_online_alter_log_max_sizeに関しては、こちらの資料が参考になります。
https://www.percona.com/live/mysql-conference-2014/sites/default/files/slides/Online%20schema%20changes%20for%20maximizing%20uptime.pdf

——————————————————————————————-
抜粋:
14.11. InnoDB とオンライン DDL

——————————————————————————————-
MySQL 5.6 で導入されたオンライン DDL 機能は、ほかのタイプの多くの ALTER TABLE 操作を、
テーブルコピー、DDL が進行中の DML 操作のブロック化、またはその両方を行わないように拡張しています。

オンライン DDL 機能には、次の利点があります。
1)インデックスやカラム定義を変更する場合は常に、テーブルを数分または数時間にわたって使用できなくすることが現実的でないビジー状態の本番環境での応答性と可用性を向上させます。
2)テーブルへのアクセスを完全にブロックするか (LOCK=EXCLUSIVE 句)、クエリーを許可するが、
DML は許可しないか (LOCK=SHARED 句)、またはテーブルへの完全なクエリーおよび DML アクセスを許可するか (LOCK=NONE 句)どうかを選択することによって、DDL 操作中のパフォーマンスと並列性のバランスを調整できます。
3)LOCK 句を省略するか、または LOCK=DEFAULT を指定すると、MySQL は、操作の種類に応じてできるだけ高い並列性を許可します。
4)テーブルの新しいコピーを作成するのではなく、可能な場合はインプレースで変更を行うことによって、
テーブルのコピーおよびすべてのセカンダリインデックスの再構築のためのディスク領域の使用量やI/O オーバーヘッドの一時的な増加が回避されます。
——————————————————————————————-

本日は、ALTER TABLEにてストレージエンジンをINNODBに変換する場合の挙動について確認してみます。

MySQL 5.6.17 の時点では、ALTER TABLE tbl_name ENGINE=INNODB とALTER TABLE tbl_name FORCE の両方が
オンライン DDL (ALGORITHM=COPY) を使用します。
http://dev.mysql.com/doc/refman/5.6/ja/alter-table.html

ALGORITHM=COPY に 関する注意
ALGORITHM=COPY 句で実行 される ALTER TABLE 操作はすべて、並列 DML 操 作を妨げます。
並列クエリーは、引き続き許可されます。つま り、テーブルコピー操作には常に、
少なくとも LOCK=SHARED (クエリーを許可するが、DML は許 可しない) の並列性 の制限が含まれます。

初期設定確認


root@localhost [test]> select @@version;
+-------------------------------------------+
| @@version                                 |
+-------------------------------------------+
| 5.6.24-enterprise-commercial-advanced-log |
+-------------------------------------------+
1 row in set (0.00 sec)

root@localhost [test]> show variables like '%old_alter_table%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| old_alter_table | OFF   |
+-----------------+-------+
1 row in set (0.00 sec)


以下、オンラインでの変更可否の確認
INPLACEを明示的に指定して、ストレージエンジンを変換してますが、処理がエラーになっています。
COPYでは問題無く終了しています。LOCKもSHAREDで無ければ許可されません。

root@localhost [USER01]> select TABLE_SCHEMA,TABLE_NAME,ENGINE from information_schema.TABLES where TABLE_SCHEMA = 'USER01' and TABLE_NAME = 'T_STORAGE_CONV';
+--------------+----------------+--------+
| TABLE_SCHEMA | TABLE_NAME     | ENGINE |
+--------------+----------------+--------+
| USER01       | T_STORAGE_CONV | MyISAM |
+--------------+----------------+--------+
1 row in set (0.00 sec)

root@localhost [USER01]> ALTER TABLE T_STORAGE_CONV ALGORITHM=INPLACE, ENGINE=InnoDB;
ERROR 1845 (0A000): ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY. 

root@localhost [test]> ALTER TABLE T_STORAGE_CONV ALGORITHM=COPY,LOCK=NONE,ENGINE=InnoDB;
ERROR 1846 (0A000): LOCK=NONE is not supported. Reason: COPY algorithm requires a lock. Try LOCK=SHARED.

root@localhost [USER01]> ALTER TABLE T_STORAGE_CONV ALGORITHM=COPY, ENGINE=InnoDB;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

root@localhost [USER01]> select TABLE_SCHEMA,TABLE_NAME,ENGINE from information_schema.TABLES where TABLE_SCHEMA = 'USER01' and TABLE_NAME = 'T_STORAGE_CONV';
+--------------+----------------+--------+
| TABLE_SCHEMA | TABLE_NAME     | ENGINE |
+--------------+----------------+--------+
| USER01       | T_STORAGE_CONV | InnoDB |
+--------------+----------------+--------+
1 row in set (0.00 sec)

root@localhost [USER01]> 

SHAREDロックになってしまう処理
データを3件入れて、変換してみると3行程影響を受けている事が確認出来ます。
こにより、テーブルがCOPYされている事が確認出来ます。

root@localhost [test]> CREATE TABLE `T_STORAGE_CONV` (
    ->   `ID` int(11) NOT NULL AUTO_INCREMENT,
    ->   `MEMO` char(255) DEFAULT NULL,
    ->   PRIMARY KEY (`ID`)
    -> ) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.01 sec)

root@localhost [test]> insert into T_STORAGE_CONV(MEMO) values('TEST ONLINE DDL 1');
Query OK, 1 row affected (0.00 sec)

root@localhost [test]> insert into T_STORAGE_CONV(MEMO) values('TEST ONLINE DDL 2');
Query OK, 1 row affected (0.00 sec)

root@localhost [test]> insert into T_STORAGE_CONV(MEMO) values('TEST ONLINE DDL 3');
Query OK, 1 row affected (0.00 sec)

root@localhost [test]> ALTER TABLE T_STORAGE_CONV ALGORITHM=COPY,LOCK=SHARED,ENGINE=InnoDB;
Query OK, 3 rows affected (0.04 sec)
Records: 3  Duplicates: 0  Warnings: 0

root@localhost [test]> 

Secondaryインデックスの追加や列の追加はONLINE出来る。
rows affectedの値が0であることからも確認出来る。

root@localhost [test]> ALTER TABLE T_STORAGE_CONV ALGORITHM=INPLACE,ADD INDEX IDX_MEMO(MEMO);                       
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

root@localhost [test]> show create table T_STORAGE_CONV\G
*************************** 1. row ***************************
       Table: T_STORAGE_CONV
Create Table: CREATE TABLE `T_STORAGE_CONV` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `MEMO` char(100) DEFAULT NULL,
  PRIMARY KEY (`ID`),
  KEY `IDX_MEMO` (`MEMO`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4
1 row in set (0.01 sec)

root@localhost [test]> desc T_STORAGE_CONV;
+-------+-----------+------+-----+---------+----------------+
| Field | Type      | Null | Key | Default | Extra          |
+-------+-----------+------+-----+---------+----------------+
| ID    | int(11)   | NO   | PRI | NULL    | auto_increment |
| MEMO  | char(100) | YES  | MUL | NULL    |                |
+-------+-----------+------+-----+---------+----------------+
2 rows in set (0.01 sec)

root@localhost [test]> ALTER TABLE T_STORAGE_CONV ALGORITHM=INPLACE,
    -> ADD CATEGORY varchar(10) after ID;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

root@localhost [test]> desc T_STORAGE_CONV;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| ID       | int(11)     | NO   | PRI | NULL    | auto_increment |
| CATEGORY | varchar(10) | YES  |     | NULL    |                |
| MEMO     | char(100)   | YES  | MUL | NULL    |                |
+----------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

root@localhost [test]> 

ONLINE_DDL

マニュアルにも載っているが、もしオンラインで処理可能か?もしくはDMLがブロックされるかどうか不明な場合は、
上記のようにオプションを付けて確認して見る事も可能です。

参考: 14.11.1. オンライン DDL の概要


MySQLのSYS Schemaから色々な情報を確認する事が出来ます。

それぞれのObjectの数

root@localhost [sys]>select * from sys.schema_object_overview where db = 'sys';
+-----+-------------+-------+
| db  | object_type | count |
+-----+-------------+-------+
| sys | FUNCTION    |    11 |
| sys | VIEW        |    78 |
| sys | PROCEDURE   |    22 |
+-----+-------------+-------+
3 rows in set, 1 warning (0.06 sec)

Latencyのhistogram確認用のSP

root@localhost [sys]>CALL sys.ps_statement_avg_latency_histogram()\G
*************************** 1. row ***************************
Performance Schema Statement Digest Average Latency Histogram: 

  . = 1 unit
  * = 2 units
  # = 3 units

(0 - 82ms)       103 | ##################################
(82 - 164ms)     2   | ..
(164 - 245ms)    1   | .
(245 - 327ms)    0   | 
(327 - 409ms)    0   | 
(409 - 491ms)    0   | 
(491 - 572ms)    0   | 
(572 - 654ms)    0   | 
(654 - 736ms)    0   | 
(736 - 818ms)    0   | 
(818 - 899ms)    0   | 
(899 - 981ms)    0   | 
(981 - 1063ms)   0   | 
(1063 - 1145ms)  0   | 
(1145 - 1226ms)  0   | 
(1226 - 1308ms)  0   | 

  Total Statements: 107; Buckets: 16; Bucket Size: 82 ms;

1 row in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

root@localhost [sys]>


こちらのViewは、Workbenchから作成したものです。


root@localhost [sys]>select * from host_summary;
+----------------+------------+-------------------+-----------------------+-------------+----------+-----------------+---------------------+-------------------+--------------+
| host           | statements | statement_latency | statement_avg_latency | table_scans | file_ios | file_io_latency | current_connections | total_connections | unique_hosts |
+----------------+------------+-------------------+-----------------------+-------------+----------+-----------------+---------------------+-------------------+--------------+
| 192.168.56.108 |         17 | 13.79 ms          | 810.91 us             |           3 |       55 | 757.59 us       |                   0 |                 1 |            1 |
| CentOS02       |          9 | 4.44 ms           | 493.35 us             |           2 |     3482 | 1.62 s          |                   1 |                 1 |            1 |
| localhost      |        296 | 7.60 s            | 25.66 ms              |          28 |     9338 | 5.28 s          |                   1 |                 3 |            1 |
+----------------+------------+-------------------+-----------------------+-------------+----------+-----------------+---------------------+-------------------+--------------+
3 rows in set (0.02 sec)

root@localhost [sys]>

root@localhost [sys]>select * from host_summary_by_file_io;  
+----------------+------+------------+
| host           | ios  | io_latency |
+----------------+------+------------+
| NULL           | 2168 | 9.36 s     |
| localhost      | 9344 | 5.28 s     |
| CentOS02       | 3482 | 1.62 s     |
| 192.168.56.108 |   55 | 757.59 us  |
+----------------+------+------------+
4 rows in set (0.01 sec)

root@localhost [sys]>


root@localhost [sys]>select * from host_summary_by_file_io_type;
+----------------+--------------------------------------+-------+-----------+-------------+
| host           | event_name                           | total | latency   | max_latency |
+----------------+--------------------------------------+-------+-----------+-------------+
| NULL           | wait/io/file/innodb/innodb_data_file |   623 | 3.07 s    | 362.56 ms   |
| NULL           | wait/io/file/sql/binlog              |   355 | 1.32 s    | 208.15 ms   |
| NULL           | wait/io/file/innodb/innodb_log_file  |    22 | 1.14 s    | 394.59 ms   |
| NULL           | wait/io/file/sql/FRM                 |   910 | 964.35 ms | 134.84 ms   |
| NULL           | wait/io/file/sql/binlog_index        |    21 | 810.08 ms | 411.41 ms   |
| NULL           | wait/io/file/sql/ERRMSG              |     5 | 771.84 ms | 711.30 ms   |
| NULL           | wait/io/file/sql/slow_log            |     4 | 428.92 ms | 428.90 ms   |
| NULL           | wait/io/file/myisam/kfile            |    67 | 286.50 ms | 175.43 ms   |
| NULL           | wait/io/file/sql/query_log           |     4 | 230.20 ms | 230.18 ms   |
| NULL           | wait/io/file/mysys/cnf               |     5 | 138.75 ms | 138.68 ms   |
| NULL           | wait/io/file/sql/relaylog            |    75 | 75.81 ms  | 75.44 ms    |
| NULL           | wait/io/file/mysys/charset           |     3 | 69.91 ms  | 69.73 ms    |
| NULL           | wait/io/file/myisam/dfile            |    44 | 38.47 ms  | 35.59 ms    |
| NULL           | wait/io/file/sql/relaylog_index      |     9 | 13.24 ms  | 13.18 ms    |
| NULL           | wait/io/file/sql/casetest            |    10 | 1.01 ms   | 601.72 us   |
| NULL           | wait/io/file/sql/pid                 |     3 | 109.28 us | 92.89 us    |
| NULL           | wait/io/file/csv/data                |     6 | 29.28 us  | 13.07 us    |
| NULL           | wait/io/file/sql/global_ddl_log      |     2 | 11.91 us  | 10.12 us    |
| 192.168.56.108 | wait/io/file/csv/data                |    54 | 747.85 us | 38.46 us    |
| 192.168.56.108 | wait/io/file/sql/dbopt               |     1 | 9.75 us   | 9.75 us     |
| CentOS02       | wait/io/file/csv/data                |  3405 | 825.63 ms | 282.11 ms   |
| CentOS02       | wait/io/file/csv/metadata            |    21 | 774.72 ms | 367.48 ms   |
| CentOS02       | wait/io/file/sql/FRM                 |    39 | 17.67 ms  | 17.53 ms    |
| CentOS02       | wait/io/file/sql/binlog              |    17 | 84.64 us  | 23.62 us    |
| localhost      | wait/io/file/sql/FRM                 |   963 | 2.35 s    | 288.10 ms   |
| localhost      | wait/io/file/myisam/kfile            |  4497 | 1.27 s    | 325.61 ms   |
| localhost      | wait/io/file/innodb/innodb_data_file |   115 | 590.69 ms | 210.05 ms   |
| localhost      | wait/io/file/csv/metadata            |    49 | 417.85 ms | 234.59 ms   |
| localhost      | wait/io/file/myisam/dfile            |  2271 | 269.27 ms | 65.60 ms    |
| localhost      | wait/io/file/sql/file_parser         |   202 | 220.82 ms | 98.15 ms    |
| localhost      | wait/io/file/sql/partition           |    20 | 80.27 ms  | 79.51 ms    |
| localhost      | wait/io/file/csv/data                |  1200 | 74.32 ms  | 39.83 ms    |
| localhost      | wait/io/file/innodb/innodb_log_file  |     2 | 14.11 ms  | 14.08 ms    |
| localhost      | wait/io/file/sql/dbopt               |    28 | 1.41 ms   | 892.22 us   |
| localhost      | wait/io/file/sql/binlog              |     2 | 44.76 us  | 23.05 us    |
| localhost      | wait/io/file/archive/data            |     1 | 11.00 us  | 11.00 us    |
+----------------+--------------------------------------+-------+-----------+-------------+
36 rows in set (0.01 sec)

root@localhost [sys]>

root@localhost [sys]>select * from host_summary_by_stages;
Empty set (0.00 sec)

root@localhost [sys]>

root@localhost [sys]>select * from host_summary_by_statement_latency;
+----------------+-------+---------------+-------------+--------------+-----------+---------------+---------------+------------+
| host           | total | total_latency | max_latency | lock_latency | rows_sent | rows_examined | rows_affected | full_scans |
+----------------+-------+---------------+-------------+--------------+-----------+---------------+---------------+------------+
| localhost      |   300 | 7.63 s        | 5.41 s      | 978.76 ms    |       535 |         29778 |             1 |         32 |
| 192.168.56.108 |    17 | 13.79 ms      | 7.03 ms     | 1.77 ms      |        29 |            26 |             0 |          3 |
| CentOS02       |     9 | 4.44 ms       | 2.65 ms     | 698.00 us    |         5 |             2 |             0 |          2 |
+----------------+-------+---------------+-------------+--------------+-----------+---------------+---------------+------------+
3 rows in set (0.00 sec)

root@localhost [sys]>

root@localhost [sys]>select * from host_summary_by_statement_type;
+----------------+-------------------+-------+---------------+-------------+--------------+-----------+---------------+---------------+------------+
| host           | statement         | total | total_latency | max_latency | lock_latency | rows_sent | rows_examined | rows_affected | full_scans |
+----------------+-------------------+-------+---------------+-------------+--------------+-----------+---------------+---------------+------------+
| 192.168.56.108 | show_tables       |     2 | 6.41 ms       | 3.24 ms     | 634.00 us    |        18 |            18 |             0 |          2 |
| 192.168.56.108 | Field List        |     9 | 3.46 ms       | 568.37 us   | 369.00 us    |         0 |             0 |             0 |          0 |
| 192.168.56.108 | show_databases    |     1 | 1.83 ms       | 1.83 ms     | 326.00 us    |         8 |             8 |             0 |          1 |
| 192.168.56.108 | select            |     3 | 1.22 ms       | 524.43 us   | 303.00 us    |         3 |             0 |             0 |          0 |
| 192.168.56.108 | Init DB           |     1 | 682.28 us     | 682.28 us   | 89.00 us     |         0 |             0 |             0 |          0 |
| 192.168.56.108 | Quit              |     1 | 190.70 us     | 190.70 us   | 50.00 us     |         0 |             0 |             0 |          0 |
| CentOS02       | show_variables    |     2 | 2.48 ms       | 1.78 ms     | 331.00 us    |         2 |             2 |             0 |          2 |
| CentOS02       | set_option        |     3 | 925.25 us     | 325.51 us   | 162.00 us    |         0 |             0 |             0 |          0 |
| CentOS02       | select            |     3 | 856.66 us     | 370.57 us   | 205.00 us    |         3 |             0 |             0 |          0 |
| CentOS02       | Register Slave    |     1 | 176.49 us     | 176.49 us   | 0 ps         |         0 |             0 |             0 |          0 |
| localhost      | select            |    33 | 5.47 s        | 4.68 s      | 398.01 ms    |       162 |         30071 |             0 |         21 |
| localhost      | Field List        |   244 | 2.04 s        | 614.23 ms   | 561.31 ms    |         0 |             0 |             0 |          0 |
| localhost      | show_databases    |     4 | 48.88 ms      | 45.59 ms    | 1.04 ms      |        32 |            32 |             0 |          4 |
| localhost      | call_procedure    |     2 | 38.89 ms      | 23.06 ms    | 214.00 us    |         0 |             0 |             0 |          0 |
| localhost      | Init DB           |     4 | 19.30 ms      | 19.02 ms    | 19.02 ms     |         0 |             0 |             0 |          0 |
| localhost      | insert            |     1 | 14.91 ms      | 14.91 ms    | 426.00 us    |         0 |             0 |             1 |          0 |
| localhost      | show_tables       |     7 | 5.63 ms       | 1.62 ms     | 2.45 ms      |       342 |           342 |             0 |          7 |
| localhost      | Quit              |     2 | 1.68 ms       | 1.56 ms     | 103.00 us    |         0 |             0 |             0 |          0 |
| localhost      | drop_table        |     1 | 1.60 ms       | 1.60 ms     | 97.00 us     |         0 |             0 |             0 |          0 |
| localhost      | show_fields       |     1 | 981.28 us     | 981.28 us   | 633.00 us    |         2 |             2 |             0 |          1 |
| localhost      | show_create_table |     1 | 180.04 us     | 180.04 us   | 47.00 us     |         0 |             0 |             0 |          0 |
| localhost      | error             |     1 | 92.56 us      | 92.56 us    | 0 ps         |         0 |             0 |             0 |          0 |
+----------------+-------------------+-------+---------------+-------------+--------------+-----------+---------------+---------------+------------+
22 rows in set (0.01 sec)

root@localhost [sys]>

root@localhost [sys]>select * from innodb_buffer_stats_by_schema;
+---------------+------------+-----------+-------+--------------+-----------+-------------+
| object_schema | allocated  | data      | pages | pages_hashed | pages_old | rows_cached |
+---------------+------------+-----------+-------+--------------+-----------+-------------+
| sakila        | 320.00 KiB | 58.39 KiB |    20 |           20 |        20 |        1330 |
| InnoDB System | 176.00 KiB | 35.59 KiB |    11 |           11 |        11 |         101 |
| mysql         | 112.00 KiB | 21.92 KiB |     7 |            7 |         7 |         238 |
| test          | 80.00 KiB  | 2.26 KiB  |     5 |            5 |         5 |          34 |
| audit_test    | 16.00 KiB  | 7.07 KiB  |     1 |            1 |         1 |          57 |
+---------------+------------+-----------+-------+--------------+-----------+-------------+
5 rows in set (0.09 sec)

root@localhost [sys]>

root@localhost [sys]>select * from innodb_buffer_stats_by_table;
+---------------+----------------------+-----------+------------+-------+--------------+-----------+-------------+
| object_schema | object_name          | allocated | data       | pages | pages_hashed | pages_old | rows_cached |
+---------------+----------------------+-----------+------------+-------+--------------+-----------+-------------+
| InnoDB System | SYS_FOREIGN          | 48.00 KiB | 3.73 KiB   |     3 |            3 |         3 |          22 |
| mysql         | innodb_index_stats   | 48.00 KiB | 19.98 KiB  |     3 |            3 |         3 |         207 |
| InnoDB System | SYS_TABLES           | 32.00 KiB | 4.38 KiB   |     2 |            2 |         2 |          40 |
| sakila        | address              | 32.00 KiB | 8.56 KiB   |     2 |            2 |         2 |         117 |
| sakila        | city                 | 32.00 KiB | 13.38 KiB  |     2 |            2 |         2 |         389 |
| sakila        | customer             | 32.00 KiB | 10.43 KiB  |     2 |            2 |         2 |         136 |
| sakila        | film                 | 32.00 KiB | 3.76 KiB   |     2 |            2 |         2 |          36 |
| sakila        | inventory            | 32.00 KiB | 1.27 KiB   |     2 |            2 |         2 |          52 |
| sakila        | payment              | 32.00 KiB | 3.21 KiB   |     2 |            2 |         2 |         113 |
| sakila        | rental               | 32.00 KiB | 4.92 KiB   |     2 |            2 |         2 |         149 |
| audit_test    | EE_log               | 16.00 KiB | 7.07 KiB   |     1 |            1 |         1 |          57 |
| InnoDB System | SYS_COLUMNS          | 16.00 KiB | 14.45 KiB  |     1 |            1 |         1 |         228 |
| InnoDB System | SYS_DATAFILES        | 16.00 KiB | 1.79 KiB   |     1 |            1 |         1 |          36 |
| InnoDB System | SYS_FIELDS           | 16.00 KiB | 3.06 KiB   |     1 |            1 |         1 |          71 |
| InnoDB System | SYS_FOREIGN_COLS     | 16.00 KiB | 1.56 KiB   |     1 |            1 |         1 |          22 |
| InnoDB System | SYS_INDEXES          | 16.00 KiB | 4.86 KiB   |     1 |            1 |         1 |          69 |
| InnoDB System | SYS_TABLESPACES      | 16.00 KiB | 1.76 KiB   |     1 |            1 |         1 |          36 |
| mysql         | innodb_table_stats   | 16.00 KiB | 1.94 KiB   |     1 |            1 |         1 |          31 |
| mysql         | slave_master_info    | 16.00 KiB | 0 bytes    |     1 |            1 |         1 |           0 |
| mysql         | slave_relay_log_info | 16.00 KiB | 0 bytes    |     1 |            1 |         1 |           0 |
| mysql         | slave_worker_info    | 16.00 KiB | 0 bytes    |     1 |            1 |         1 |           0 |
| sakila        | actor                | 16.00 KiB | 7.33 KiB   |     1 |            1 |         1 |         200 |
| sakila        | category             | 16.00 KiB | 487 bytes  |     1 |            1 |         1 |          16 |
| sakila        | country              | 16.00 KiB | 3.61 KiB   |     1 |            1 |         1 |         109 |
| sakila        | language             | 16.00 KiB | 396 bytes  |     1 |            1 |         1 |           9 |
| sakila        | staff                | 16.00 KiB | 1021 bytes |     1 |            1 |         1 |           2 |
| sakila        | store                | 16.00 KiB | 52 bytes   |     1 |            1 |         1 |           2 |
| test          | Customer_Data        | 16.00 KiB | 1.12 KiB   |     1 |            1 |         1 |           6 |
| test          | MyISAM_InnoDB        | 16.00 KiB | 156 bytes  |     1 |            1 |         1 |           3 |
| test          | Personal_Info        | 16.00 KiB | 990 bytes  |     1 |            1 |         1 |          24 |
| test          | Personal_Info0       | 16.00 KiB | 0 bytes    |     1 |            1 |         1 |           0 |
| test          | Personal_Info1       | 16.00 KiB | 29 bytes   |     1 |            1 |         1 |           1 |
+---------------+----------------------+-----------+------------+-------+--------------+-----------+-------------+
32 rows in set (0.07 sec)

root@localhost [sys]>

root@localhost [sys]>select * from io_by_thread_by_latency;
+------------------------+-------+---------------+-------------+-------------+-------------+-----------+----------------+
| user                   | total | total_latency | min_latency | avg_latency | max_latency | thread_id | processlist_id |
+------------------------+-------+---------------+-------------+-------------+-------------+-----------+----------------+
| main                   |  2125 | 8.33 s        | 25.73 ns    | 27.46 ms    | 711.30 ms   |         1 |           NULL |
| root@localhost         |  8637 | 4.30 s        | 25.73 ns    | 2.73 ms     | 325.61 ms   |        25 |              5 |
| GTID_SSL_USER@CentOS02 |  3482 | 1.62 s        | 25.73 ns    | 9.40 ms     | 367.48 ms   |        21 |              1 |
| io_handler_thread      |     3 | 783.70 ms     | 5.93 ms     | 261.23 ms   | 394.59 ms   |         3 |           NULL |
| srv_master_thread      |     6 | 188.64 ms     | 25.73 us    | 31.44 ms    | 183.65 ms   |        15 |           NULL |
| io_handler_thread      |     3 | 46.71 ms      | 2.75 ms     | 15.57 ms    | 22.17 ms    |         8 |           NULL |
| page_cleaner_thread    |     8 | 13.47 ms      | 12.35 us    | 1.68 ms     | 8.87 ms     |        19 |           NULL |
| srv_purge_thread       |    14 | 354.94 us     | 4.19 us     | 25.35 us    | 118.80 us   |        18 |           NULL |
| signal_handler         |     3 | 109.28 us     | 7.23 us     | 36.43 us    | 92.89 us    |        20 |           NULL |
+------------------------+-------+---------------+-------------+-------------+-------------+-----------+----------------+
9 rows in set (0.02 sec)

root@localhost [sys]>

root@localhost [sys]>select * from io_global_by_file_by_bytes limit 0,10;
+--------------------------------------------------------------------------------------------+------------+------------+-----------+-------------+---------------+-----------+------------+-----------+
| file                                                                                       | count_read | total_read | avg_read  | count_write | total_written | avg_write | total      | write_pct |
+--------------------------------------------------------------------------------------------+------------+------------+-----------+-------------+---------------+-----------+------------+-----------+
| /usr/local/mysql-advanced-5.6.21-linux-glibc2.5-x86_64/data/ibdata1                        |        448 | 8.97 MiB   | 20.50 KiB |           5 | 112.00 KiB    | 22.40 KiB | 9.08 MiB   |      1.20 |
| /usr/local/mysql-advanced-5.6.21-linux-glibc2.5-x86_64/data/mysql/general_log.CSV          |       2023 | 7.90 MiB   | 4.00 KiB  |         339 | 32.32 KiB     | 98 bytes  | 7.93 MiB   |      0.40 |
| /usr/local/mysql-advanced-5.6.21-linux-glibc2.5-x86_64/data/mysql/proc.MYD                 |        482 | 738.98 KiB | 1.53 KiB  |           0 | 0 bytes       | 0 bytes   | 738.98 KiB |      0.00 |
| /usr/local/mysql-advanced-5.6.21-linux-glibc2.5-x86_64/data/mysql/slow_log.CSV             |        144 | 571.86 KiB | 3.97 KiB  |          22 | 3.03 KiB      | 141 bytes | 574.89 KiB |      0.53 |
| /usr/local/mysql-advanced-5.6.21-linux-glibc2.5-x86_64/data/mysql/innodb_index_stats.ibd   |          9 | 144.00 KiB | 16.00 KiB |           0 | 0 bytes       | 0 bytes   | 144.00 KiB |      0.00 |
| /usr/local/mysql-advanced-5.6.21-linux-glibc2.5-x86_64/data/sakila/actor.ibd               |          8 | 128.00 KiB | 16.00 KiB |           0 | 0 bytes       | 0 bytes   | 128.00 KiB |      0.00 |
| /usr/local/mysql-advanced-5.6.21-linux-glibc2.5-x86_64/data/mysql/innodb_table_stats.ibd   |          7 | 112.00 KiB | 16.00 KiB |           0 | 0 bytes       | 0 bytes   | 112.00 KiB |      0.00 |
| /usr/local/mysql-advanced-5.6.21-linux-glibc2.5-x86_64/data/test/Personal_Info.ibd         |          4 | 64.00 KiB  | 16.00 KiB |           1 | 16.00 KiB     | 16.00 KiB | 80.00 KiB  |     20.00 |
| /usr/local/mysql-advanced-5.6.21-linux-glibc2.5-x86_64/data/mysql/slave_relay_log_info.ibd |          5 | 80.00 KiB  | 16.00 KiB |           0 | 0 bytes       | 0 bytes   | 80.00 KiB  |      0.00 |
| /usr/local/mysql-advanced-5.6.21-linux-glibc2.5-x86_64/data/mysql/slave_master_info.ibd    |          5 | 80.00 KiB  | 16.00 KiB |           0 | 0 bytes       | 0 bytes   | 80.00 KiB  |      0.00 |
+--------------------------------------------------------------------------------------------+------------+------------+-----------+-------------+---------------+-----------+------------+-----------+
10 rows in set (0.00 sec)

root@localhost [sys]>

root@localhost [sys]>select * from io_global_by_file_by_latency limit 0,10;
+----------------------------------------------------------------------------------------------------------+-------+---------------+------------+--------------+-------------+---------------+------------+--------------+
| file                                                                                                     | total | total_latency | count_read | read_latency | count_write | write_latency | count_misc | misc_latency |
+----------------------------------------------------------------------------------------------------------+-------+---------------+------------+--------------+-------------+---------------+------------+--------------+
| /usr/local/mysql-advanced-5.6.21-linux-glibc2.5-x86_64/data/ibdata1                                      |   460 | 1.96 s        |        448 | 1.93 s       |           5 | 161.09 us     |          7 | 37.89 ms     |
| /usr/local/mysql-advanced-5.6.21-linux-glibc2.5-x86_64/data/mysql/general_log.CSV                        |  4395 | 840.16 ms     |       2025 | 824.34 ms    |         341 | 5.72 ms       |       2029 | 10.10 ms     |
| /usr/local/mysql-advanced-5.6.21-linux-glibc2.5-x86_64/data/ib_logfile0                                  |    13 | 806.86 ms     |          4 | 23.05 ms     |           3 | 83.95 us      |          6 | 783.72 ms    |
| /usr/local/mysql-advanced-5.6.21-linux-glibc2.5-x86_64/data/mysql/general_log.CSM                        |    21 | 774.72 ms     |          3 | 1.17 ms      |           3 | 44.64 us      |         15 | 773.50 ms    |
| /usr/local/mysql-advanced-5.6.21-linux-glibc2.5-x86_64/share/english/errmsg.sys                          |     5 | 771.84 ms     |          3 | 771.60 ms    |           0 | 0 ps          |          2 | 239.28 us    |
| /usr/local/mysql-advanced-5.6.21-linux-glibc2.5-x86_64/data/slow.log                                     |     4 | 428.92 ms     |          0 | 0 ps         |           1 | 428.90 ms     |          3 | 20.07 us     |
| /usr/local/mysql-advanced-5.6.21-linux-glibc2.5-x86_64/data/mysql/slow_log.CSM                           |    49 | 417.85 ms     |          7 | 1.02 ms      |           7 | 106.03 us     |         35 | 416.73 ms    |
| /usr/local/mysql-advanced-5.6.21-linux-glibc2.5-x86_64/data/mysql-bin.index                              |     5 | 411.43 ms     |          0 | 0 ps         |           0 | 0 ps          |          5 | 411.43 ms    |
| /usr/local/mysql-advanced-5.6.21-linux-glibc2.5-x86_64/data/ib_logfile1                                  |    11 | 349.60 ms     |          2 | 146.93 ms    |           3 | 87.66 us      |          6 | 202.59 ms    |
| /usr/local/mysql-advanced-5.6.21-linux-glibc2.5-x86_64/data/sys/x@0024host_summary_by_statement_type.frm |     6 | 288.36 ms     |          2 | 288.11 ms    |           0 | 0 ps          |          4 | 251.39 us    |
+----------------------------------------------------------------------------------------------------------+-------+---------------+------------+--------------+-------------+---------------+------------+--------------+
10 rows in set (0.00 sec)

root@localhost [sys]>

root@localhost [sys]>select * from io_global_by_wait_by_bytes limit 0,10;
+-------------------------+-------+---------------+-------------+-------------+-------------+------------+------------+-----------+-------------+---------------+-------------+-----------------+
| event_name              | total | total_latency | min_latency | avg_latency | max_latency | count_read | total_read | avg_read  | count_write | total_written | avg_written | total_requested |
+-------------------------+-------+---------------+-------------+-------------+-------------+------------+------------+-----------+-------------+---------------+-------------+-----------------+
| innodb/innodb_data_file |   738 | 3.66 s        | 0 ps        | 4.95 ms     | 362.56 ms   |        578 | 11.00 MiB  | 19.49 KiB |           6 | 128.00 KiB    | 21.33 KiB   | 11.13 MiB       |
| myisam/dfile            |  2796 | 313.68 ms     | 0 ps        | 112.19 us   | 65.60 ms    |        998 | 5.26 MiB   | 5.40 KiB  |         207 | 4.49 MiB      | 22.23 KiB   | 9.75 MiB        |
| csv/data                |  4734 | 902.26 ms     | 0 ps        | 190.59 us   | 282.11 ms   |       2174 | 8.49 MiB   | 4.00 KiB  |         368 | 36.21 KiB     | 101 bytes   | 8.52 MiB        |
| sql/FRM                 |  1997 | 3.33 s        | 0 ps        | 1.67 ms     | 288.10 ms   |        969 | 485.91 KiB | 513 bytes |           0 | 0 bytes       | 0 bytes     | 485.91 KiB      |
| myisam/kfile            |  4722 | 1.55 s        | 0 ps        | 329.24 us   | 325.61 ms   |        380 | 78.97 KiB  | 213 bytes |        3155 | 64.15 KiB     | 21 bytes    | 143.11 KiB      |
| innodb/innodb_log_file  |    24 | 1.16 s        | 0 ps        | 48.19 ms    | 394.59 ms   |          6 | 68.00 KiB  | 11.33 KiB |           6 | 3.50 KiB      | 597 bytes   | 71.50 KiB       |
| sql/ERRMSG              |     5 | 771.84 ms     | 0 ps        | 154.37 ms   | 711.30 ms   |          3 | 58.14 KiB  | 19.38 KiB |           0 | 0 bytes       | 0 bytes     | 58.14 KiB       |
| mysys/charset           |     3 | 69.91 ms      | 0 ps        | 23.30 ms    | 69.73 ms    |          1 | 17.30 KiB  | 17.30 KiB |           0 | 0 bytes       | 0 bytes     | 17.30 KiB       |
| sql/binlog              |   374 | 1.32 s        | 0 ps        | 3.54 ms     | 208.15 ms   |        140 | 11.04 KiB  | 81 bytes  |           5 | 687 bytes     | 137 bytes   | 11.72 KiB       |
| sql/file_parser         |   117 | 216.52 ms     | 0 ps        | 1.85 ms     | 98.15 ms    |          8 | 3.83 KiB   | 490 bytes |           0 | 0 bytes       | 0 bytes     | 3.83 KiB        |
+-------------------------+-------+---------------+-------------+-------------+-------------+------------+------------+-----------+-------------+---------------+-------------+-----------------+
10 rows in set (0.01 sec)

root@localhost [sys]>


root@localhost [sys]>select * from io_global_by_wait_by_latency limit 0,10;
+-------------------------+-------+---------------+-------------+-------------+--------------+---------------+--------------+------------+------------+-----------+-------------+---------------+-------------+
| event_name              | total | total_latency | avg_latency | max_latency | read_latency | write_latency | misc_latency | count_read | total_read | avg_read  | count_write | total_written | avg_written |
+-------------------------+-------+---------------+-------------+-------------+--------------+---------------+--------------+------------+------------+-----------+-------------+---------------+-------------+
| innodb/innodb_data_file |   738 | 3.66 s        | 4.95 ms     | 362.56 ms   | 3.59 s       | 184.81 us     | 64.68 ms     |        578 | 11.00 MiB  | 19.49 KiB |           6 | 128.00 KiB    | 21.33 KiB   |
| sql/FRM                 |  1997 | 3.33 s        | 1.67 ms     | 288.10 ms   | 3.25 s       | 0 ps          | 87.60 ms     |        969 | 485.91 KiB | 513 bytes |           0 | 0 bytes       | 0 bytes     |
| myisam/kfile            |  4722 | 1.55 s        | 329.24 us   | 325.61 ms   | 526.23 ms    | 7.89 ms       | 1.02 s       |        380 | 78.97 KiB  | 213 bytes |        3155 | 64.15 KiB     | 21 bytes    |
| sql/binlog              |   374 | 1.32 s        | 3.54 ms     | 208.15 ms   | 1.10 s       | 87.02 us      | 221.08 ms    |        140 | 11.04 KiB  | 81 bytes  |           5 | 687 bytes     | 137 bytes   |
| csv/metadata            |    70 | 1.19 s        | 17.04 ms    | 367.48 ms   | 2.19 ms      | 150.67 us     | 1.19 s       |         10 | 350 bytes  | 35 bytes  |          10 | 350 bytes     | 35 bytes    |
| innodb/innodb_log_file  |    24 | 1.16 s        | 48.19 ms    | 394.59 ms   | 169.98 ms    | 171.61 us     | 986.31 ms    |          6 | 68.00 KiB  | 11.33 KiB |           6 | 3.50 KiB      | 597 bytes   |
| csv/data                |  4740 | 902.33 ms     | 190.37 us   | 282.11 ms   | 885.27 ms    | 6.21 ms       | 10.85 ms     |       2176 | 8.49 MiB   | 4.00 KiB  |         370 | 36.43 KiB     | 101 bytes   |
| sql/binlog_index        |    20 | 809.93 ms     | 40.50 ms    | 411.41 ms   | 4.24 us      | 0 ps          | 809.92 ms    |          2 | 513 bytes  | 257 bytes |           0 | 0 bytes       | 0 bytes     |
| sql/ERRMSG              |     5 | 771.84 ms     | 154.37 ms   | 711.30 ms   | 771.60 ms    | 0 ps          | 239.28 us    |          3 | 58.14 KiB  | 19.38 KiB |           0 | 0 bytes       | 0 bytes     |
| sql/slow_log            |     4 | 428.92 ms     | 107.23 ms   | 428.90 ms   | 0 ps         | 428.90 ms     | 20.07 us     |          0 | 0 bytes    | 0 bytes   |           1 | 242 bytes     | 242 bytes   |
+-------------------------+-------+---------------+-------------+-------------+--------------+---------------+--------------+------------+------------+-----------+-------------+---------------+-------------+
10 rows in set (0.00 sec)

root@localhost [sys]>

root@localhost [sys]>select * from latest_file_io;
Empty set (0.01 sec)

root@localhost [sys]>

root@localhost [sys]>select * from processlist limit 0,10;
+--------+---------+---------------------------------+------+------------------+------------------------------------------------------------------+------+--------------------------------------+--------------+---------------+-----------+---------------+------------+-----------------+-----------+----------------+------------------------+-----------+-------------------+--------+
| thd_id | conn_id | user                            | db   | command          | state                                                            | time | current_statement                    | lock_latency | rows_examined | rows_sent | rows_affected | tmp_tables | tmp_disk_tables | full_scan | last_statement | last_statement_latency | last_wait | last_wait_latency | source |
+--------+---------+---------------------------------+------+------------------+------------------------------------------------------------------+------+--------------------------------------+--------------+---------------+-----------+---------------+------------+-----------------+-----------+----------------+------------------------+-----------+-------------------+--------+
|      1 |    NULL | sql/main                        | NULL | NULL             | System lock                                                      | 4087 | INTERNAL DDL LOG RECOVER IN PROGRESS | NULL         |          NULL |      NULL |          NULL |       NULL |            NULL | NO        | NULL           | NULL                   | NULL      | NULL              | NULL   |
|     21 |       1 | GTID_SSL_USER@CentOS02          | NULL | Binlog Dump GTID | Master has sent all binlog to slave; waiting for binlog to be up | 4081 | NULL                                 | 45.00 us     |             0 |         0 |             0 |          0 |               0 | NO        | NULL           | NULL                   | NULL      | NULL              | NULL   |
|     25 |       5 | root@localhost                  | sys  | Query            | Sending data                                                     |    0 | select * from processlist limit 0,10 | 1.43 ms      |             0 |         0 |             0 |          2 |               0 | YES       | NULL           | NULL                   | NULL      | NULL              | NULL   |
|     17 |    NULL | innodb/srv_lock_timeout_thread  | NULL | NULL             | NULL                                                             | NULL | NULL                                 | NULL         |          NULL |      NULL |          NULL |       NULL |            NULL | NO        | NULL           | NULL                   | NULL      | NULL              | NULL   |
|      4 |    NULL | innodb/io_handler_thread        | NULL | NULL             | NULL                                                             | NULL | NULL                                 | NULL         |          NULL |      NULL |          NULL |       NULL |            NULL | NO        | NULL           | NULL                   | NULL      | NULL              | NULL   |
|     15 |    NULL | innodb/srv_master_thread        | NULL | NULL             | NULL                                                             | NULL | NULL                                 | NULL         |          NULL |      NULL |          NULL |       NULL |            NULL | NO        | NULL           | NULL                   | NULL      | NULL              | NULL   |
|      3 |    NULL | innodb/io_handler_thread        | NULL | NULL             | NULL                                                             | NULL | NULL                                 | NULL         |          NULL |      NULL |          NULL |       NULL |            NULL | NO        | NULL           | NULL                   | NULL      | NULL              | NULL   |
|     14 |    NULL | innodb/srv_monitor_thread       | NULL | NULL             | NULL                                                             | NULL | NULL                                 | NULL         |          NULL |      NULL |          NULL |       NULL |            NULL | NO        | NULL           | NULL                   | NULL      | NULL              | NULL   |
|      2 |    NULL | innodb/io_handler_thread        | NULL | NULL             | NULL                                                             | NULL | NULL                                 | NULL         |          NULL |      NULL |          NULL |       NULL |            NULL | NO        | NULL           | NULL                   | NULL      | NULL              | NULL   |
|     13 |    NULL | innodb/srv_error_monitor_thread | NULL | NULL             | NULL                                                             | NULL | NULL                                 | NULL         |          NULL |      NULL |          NULL |       NULL |            NULL | NO        | NULL           | NULL                   | NULL      | NULL              | NULL   |
+--------+---------+---------------------------------+------+------------------+------------------------------------------------------------------+------+--------------------------------------+--------------+---------------+-----------+---------------+------------+-----------------+-----------+----------------+------------------------+-----------+-------------------+--------+
10 rows in set (0.01 sec)

root@localhost [sys]>

root@localhost [sys]>select * from ps_check_lost_instrumentation;
Empty set (0.00 sec)

root@localhost [sys]>select * from schema_index_statistics limit 0,10;
+--------------+---------------------------+--------------------+---------------+----------------+---------------+----------------+--------------+----------------+--------------+----------------+
| table_schema | table_name                | index_name         | rows_selected | select_latency | rows_inserted | insert_latency | rows_updated | update_latency | rows_deleted | delete_latency |
+--------------+---------------------------+--------------------+---------------+----------------+---------------+----------------+--------------+----------------+--------------+----------------+
| sakila       | store                     | idx_unique_manager |             3 | 235.88 us      |             0 | 0 ps           |            0 | 0 ps           |            0 | 0 ps           |
| mysql        | time_zone_transition_type | PRIMARY            |             0 | 0 ps           |             0 | 0 ps           |            0 | 0 ps           |            0 | 0 ps           |
| mysql        | time_zone                 | PRIMARY            |             0 | 0 ps           |             0 | 0 ps           |            0 | 0 ps           |            0 | 0 ps           |
| mysql        | time_zone_name            | PRIMARY            |             0 | 0 ps           |             0 | 0 ps           |            0 | 0 ps           |            0 | 0 ps           |
| mysql        | time_zone_leap_second     | PRIMARY            |             0 | 0 ps           |             0 | 0 ps           |            0 | 0 ps           |            0 | 0 ps           |
| mysql        | proxies_priv              | Grantor            |             0 | 0 ps           |             0 | 0 ps           |            0 | 0 ps           |            0 | 0 ps           |
| mysql        | proxies_priv              | PRIMARY            |             0 | 0 ps           |             0 | 0 ps           |            0 | 0 ps           |            0 | 0 ps           |
| mysql        | db                        | User               |             0 | 0 ps           |             0 | 0 ps           |            0 | 0 ps           |            0 | 0 ps           |
| mysql        | db                        | PRIMARY            |             0 | 0 ps           |             0 | 0 ps           |            0 | 0 ps           |            0 | 0 ps           |
| test         | Personal_Info1            | PRIMARY            |             0 | 0 ps           |             0 | 0 ps           |            0 | 0 ps           |            0 | 0 ps           |
+--------------+---------------------------+--------------------+---------------+----------------+---------------+----------------+--------------+----------------+--------------+----------------+
10 rows in set (0.00 sec)

root@localhost [sys]>


root@localhost [sys]>select * from schema_object_overview;
+--------------------+------------------+-------+
| db                 | object_type      | count |
+--------------------+------------------+-------+
| audit_test         | BASE TABLE       |     2 |
| audit_test         | INDEX (BTREE)    |     1 |
| information_schema | SYSTEM VIEW      |    59 |
| mysql              | BASE TABLE       |    29 |
| mysql              | INDEX (BTREE)    |    64 |
| performance_schema | BASE TABLE       |    52 |
| sakila             | BASE TABLE       |    17 |
| sakila             | FUNCTION         |     3 |
| sakila             | INDEX (BTREE)    |    48 |
| sakila             | INDEX (FULLTEXT) |     2 |
| sakila             | PROCEDURE        |     3 |
| sakila             | TRIGGER          |     6 |
| sakila             | VIEW             |     7 |
| sys                | FUNCTION         |    11 |
| sys                | PROCEDURE        |    22 |
| sys                | VIEW             |    78 |
| test               | BASE TABLE       |     9 |
| test               | INDEX (BTREE)    |     6 |
| test               | PROCEDURE        |     1 |
+--------------------+------------------+-------+
19 rows in set (0.06 sec)

root@localhost [sys]>

root@localhost [sys]>select * from schema_table_statistics limit 0,10;
+--------------+--------------------+---------------+--------------+---------------+---------------+----------------+--------------+----------------+--------------+----------------+------------------+------------+-----------------+-------------------+-----------+------------------+------------------+-----------------+
| table_schema | table_name         | total_latency | rows_fetched | fetch_latency | rows_inserted | insert_latency | rows_updated | update_latency | rows_deleted | delete_latency | io_read_requests | io_read    | io_read_latency | io_write_requests | io_write  | io_write_latency | io_misc_requests | io_misc_latency |
+--------------+--------------------+---------------+--------------+---------------+---------------+----------------+--------------+----------------+--------------+----------------+------------------+------------+-----------------+-------------------+-----------+------------------+------------------+-----------------+
| test         | Personal_Info      | 316.26 us     |           25 | 54.22 us      |             2 | 262.03 us      |            0 | 0 ps           |            0 | 0 ps           |               11 | 67.53 KiB  | 81.17 ms        |                 2 | 32.00 KiB | 46.31 us         |               13 | 25.25 ms        |
| sakila       | store              | 235.88 us     |            3 | 235.88 us     |             0 | 0 ps           |            0 | 0 ps           |            0 | 0 ps           |               12 | 80.71 KiB  | 22.87 ms        |                 0 | 0 bytes   | 0 ps             |               11 | 277.67 us       |
| mysql        | help_category      | 0 ps          |            0 | 0 ps          |             0 | 0 ps           |            0 | 0 ps           |            0 | 0 ps           |                9 | 1.27 KiB   | 141.42 ms       |                 0 | 0 bytes   | 0 ps             |               14 | 150.86 us       |
| mysql        | innodb_index_stats | 0 ps          |            0 | 0 ps          |             0 | 0 ps           |            0 | 0 ps           |            0 | 0 ps           |               16 | 148.61 KiB | 86.00 ms        |                 0 | 0 bytes   | 0 ps             |               11 | 325.14 us       |
| mysql        | db                 | 0 ps          |            0 | 0 ps          |             0 | 0 ps           |            0 | 0 ps           |            0 | 0 ps           |               10 | 3.85 KiB   | 11.69 ms        |                 0 | 0 bytes   | 0 ps             |               17 | 86.13 us        |
| mysql        | slow_log           | 0 ps          |            0 | 0 ps          |             0 | 0 ps           |            0 | 0 ps           |            0 | 0 ps           |              179 | 607.52 KiB | 74.22 ms        |                36 | 4.23 KiB  | 565.28 us        |              208 | 417.58 ms       |
| mysql        | time_zone          | 0 ps          |            0 | 0 ps          |             0 | 0 ps           |            0 | 0 ps           |            0 | 0 ps           |               11 | 1.29 KiB   | 2.14 ms         |                 0 | 0 bytes   | 0 ps             |               19 | 117.24 us       |
| sakila       | city               | 0 ps          |            0 | 0 ps          |             0 | 0 ps           |            0 | 0 ps           |            0 | 0 ps           |               12 | 80.80 KiB  | 173.95 ms       |                 0 | 0 bytes   | 0 ps             |               11 | 121.46 us       |
| mysql        | columns_priv       | 0 ps          |            0 | 0 ps          |             0 | 0 ps           |            0 | 0 ps           |            0 | 0 ps           |                9 | 2.08 KiB   | 10.09 ms        |                 0 | 0 bytes   | 0 ps             |               14 | 112.87 us       |
| sakila       | film               | 0 ps          |            0 | 0 ps          |             0 | 0 ps           |            0 | 0 ps           |            0 | 0 ps           |               15 | 85.20 KiB  | 125.65 ms       |                 0 | 0 bytes   | 0 ps             |               17 | 361.29 us       |
+--------------+--------------------+---------------+--------------+---------------+---------------+----------------+--------------+----------------+--------------+----------------+------------------+------------+-----------------+-------------------+-----------+------------------+------------------+-----------------+
10 rows in set (0.06 sec)

root@localhost [sys]>

root@localhost [sys]>select * from schema_table_statistics_with_buffer limit 0,10;
+--------------------+-------------------------------------------------+--------------+---------------+---------------+----------------+--------------+----------------+--------------+----------------+------------------+-----------+-----------------+-------------------+-----------+------------------+------------------+-----------------+-------------------------+--------------------+---------------------+----------------------------+-------------------------+---------------------------+
| table_schema       | table_name                                      | rows_fetched | fetch_latency | rows_inserted | insert_latency | rows_updated | update_latency | rows_deleted | delete_latency | io_read_requests | io_read   | io_read_latency | io_write_requests | io_write  | io_write_latency | io_misc_requests | io_misc_latency | innodb_buffer_allocated | innodb_buffer_data | innodb_buffer_pages | innodb_buffer_pages_hashed | innodb_buffer_pages_old | innodb_buffer_rows_cached |
+--------------------+-------------------------------------------------+--------------+---------------+---------------+----------------+--------------+----------------+--------------+----------------+------------------+-----------+-----------------+-------------------+-----------+------------------+------------------+-----------------+-------------------------+--------------------+---------------------+----------------------------+-------------------------+---------------------------+
| test               | Personal_Info                                   |           50 | 108.45 us     |            50 | 524.06 us      |            0 | 0 ps           |            0 | 0 ps           |               11 | 67.53 KiB | 81.17 ms        |                 2 | 32.00 KiB | 46.31 us         |               13 | 25.25 ms        |                   32768 |               2040 |                   2 |                          2 |                       2 |                        50 |
| sakila             | store                                           |            6 | 471.77 us     |             6 | 0 ps           |            0 | 0 ps           |            0 | 0 ps           |               12 | 80.71 KiB | 22.87 ms        |                 0 | 0 bytes   | 0 ps             |               11 | 277.67 us       |                   65536 |                132 |                   4 |                          4 |                       4 |                         4 |
| mysql              | time_zone_transition_type                       |            0 | 0 ps          |             0 | 0 ps           |            0 | 0 ps           |            0 | 0 ps           |               11 | 1.53 KiB  | 175.82 ms       |                 0 | 0 bytes   | 0 ps             |               19 | 774.47 us       |                    NULL |               NULL |                NULL |                       NULL |                    NULL |                      NULL |
| performance_schema | events_waits_current                            |            0 | 0 ps          |             0 | 0 ps           |            0 | 0 ps           |            0 | 0 ps           |                7 | 4.11 KiB  | 134.86 ms       |                 0 | 0 bytes   | 0 ps             |                6 | 103.84 us       |                    NULL |               NULL |                NULL |                       NULL |                    NULL |                      NULL |
| performance_schema | events_waits_summary_global_by_event_name       |            0 | 0 ps          |             0 | 0 ps           |            0 | 0 ps           |            0 | 0 ps           |                7 | 1.14 KiB  | 38.64 ms        |                 0 | 0 bytes   | 0 ps             |                6 | 126.24 us       |                    NULL |               NULL |                NULL |                       NULL |                    NULL |                      NULL |
| performance_schema | rwlock_instances                                |            0 | 0 ps          |             0 | 0 ps           |            0 | 0 ps           |            0 | 0 ps           |                7 | 1.07 KiB  | 13.49 ms        |                 0 | 0 bytes   | 0 ps             |                6 | 63.51 us        |                    NULL |               NULL |                NULL |                       NULL |                    NULL |                      NULL |
| performance_schema | table_lock_waits_summary_by_table               |            0 | 0 ps          |             0 | 0 ps           |            0 | 0 ps           |            0 | 0 ps           |                7 | 6.50 KiB  | 582.50 us       |                 0 | 0 bytes   | 0 ps             |                6 | 111.13 us       |                    NULL |               NULL |                NULL |                       NULL |                    NULL |                      NULL |
| performance_schema | events_stages_summary_by_host_by_event_name     |            0 | 0 ps          |             0 | 0 ps           |            0 | 0 ps           |            0 | 0 ps           |                7 | 1.35 KiB  | 547.39 us       |                 0 | 0 bytes   | 0 ps             |                6 | 30.26 us        |                    NULL |               NULL |                NULL |                       NULL |                    NULL |                      NULL |
| performance_schema | events_statements_summary_by_host_by_event_name |            0 | 0 ps          |             0 | 0 ps           |            0 | 0 ps           |            0 | 0 ps           |                7 | 2.62 KiB  | 1.19 ms         |                 0 | 0 bytes   | 0 ps             |                6 | 25.85 us        |                    NULL |               NULL |                NULL |                       NULL |                    NULL |                      NULL |
| sakila             | rental                                          |            0 | 0 ps          |             0 | 0 ps           |            0 | 0 ps           |            0 | 0 ps           |               15 | 81.76 KiB | 415.46 ms       |                 0 | 0 bytes   | 0 ps             |               17 | 213.30 us       |                   98304 |              15117 |                   6 |                          6 |                       6 |                       447 |
+--------------------+-------------------------------------------------+--------------+---------------+---------------+----------------+--------------+----------------+--------------+----------------+------------------+-----------+-----------------+-------------------+-----------+------------------+------------------+-----------------+-------------------------+--------------------+---------------------+----------------------------+-------------------------+---------------------------+
10 rows in set (0.10 sec)

root@localhost [sys]>

root@localhost [sys]>select * from schema_tables_with_full_table_scans;
+---------------+----------------+-------------------+
| object_schema | object_name    | rows_full_scanned |
+---------------+----------------+-------------------+
| test          | Personal_Info  |                25 |
| test          | performance    |                 7 |
| test          | Personal_Info0 |                 1 |
+---------------+----------------+-------------------+
3 rows in set (0.00 sec)

root@localhost [sys]>

root@localhost [sys]>select * from schema_unused_indexes ;
+---------------+----------------+-----------------------------+
| object_schema | object_name    | index_name                  |
+---------------+----------------+-----------------------------+
| audit_test    | EE_log         | PRIMARY                     |
| sakila        | actor          | PRIMARY                     |
| sakila        | actor          | idx_actor_last_name         |
| sakila        | actor          | idx_fl                      |
| sakila        | actor          | idx_actor_first_name        |
| sakila        | address        | idx_fk_city_id              |
| sakila        | address        | PRIMARY                     |
| sakila        | category       | PRIMARY                     |
| sakila        | city           | PRIMARY                     |
| sakila        | city           | idx_fk_country_id           |
| sakila        | country        | PRIMARY                     |
| sakila        | customer       | idx_last_name               |
| sakila        | customer       | idx_fk_address_id           |
| sakila        | customer       | idx_fk_store_id             |
| sakila        | customer       | PRIMARY                     |
| sakila        | film           | idx_fk_original_language_id |
| sakila        | film           | idx_fk_language_id          |
| sakila        | film           | idx_title                   |
| sakila        | film           | PRIMARY                     |
| sakila        | film_actor     | PRIMARY                     |
| sakila        | film_actor     | idx_fk_film_id              |
| sakila        | film_category  | PRIMARY                     |
| sakila        | film_category  | fk_film_category_category   |
| sakila        | film_text      | idx_title_description       |
| sakila        | film_text      | PRIMARY                     |
| sakila        | inventory      | PRIMARY                     |
| sakila        | inventory      | idx_fk_film_id              |
| sakila        | inventory      | idx_store_id_film_id        |
| sakila        | language       | PRIMARY                     |
| sakila        | payment        | fk_payment_rental           |
| sakila        | payment        | idx_fk_customer_id          |
| sakila        | payment        | idx_fk_staff_id             |
| sakila        | payment        | PRIMARY                     |
| sakila        | rental         | idx_fk_staff_id             |
| sakila        | rental         | idx_fk_customer_id          |
| sakila        | rental         | idx_fk_inventory_id         |
| sakila        | rental         | rental_date                 |
| sakila        | rental         | PRIMARY                     |
| sakila        | staff          | PRIMARY                     |
| sakila        | staff          | idx_fk_store_id             |
| sakila        | staff          | idx_fk_address_id           |
| sakila        | store          | idx_fk_address_id           |
| sakila        | store          | PRIMARY                     |
| test          | Customer_Data  | PRIMARY                     |
| test          | Customer_Data  | idx_name                    |
| test          | MyISAM_InnoDB  | PRIMARY                     |
| test          | Personal_Info  | PRIMARY                     |
| test          | Personal_Info0 | PRIMARY                     |
| test          | Personal_Info1 | PRIMARY                     |
+---------------+----------------+-----------------------------+
49 rows in set (0.00 sec)

root@localhost [sys]>

root@localhost [sys]>select * from statement_analysis limit 0,10;
+-------------------------------------------------------------------+------+-----------+------------+-----------+------------+---------------+-------------+-------------+--------------+-----------+---------------+---------------+-------------------+------------+-----------------+-------------+-------------------+----------------------------------+---------------------+---------------------+
| 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           |
+-------------------------------------------------------------------+------+-----------+------------+-----------+------------+---------------+-------------+-------------+--------------+-----------+---------------+---------------+-------------------+------------+-----------------+-------------+-------------------+----------------------------------+---------------------+---------------------+
| SELECT * FROM `sys` . `schema_ ... ATISTICS` . `TABLE_SCHEMA` ... | sys  | *         |          4 |         3 |          1 | 5.23 s        | 4.68 s      | 1.31 s      | 173.34 ms    |         3 |             1 |           499 |               125 |        579 |             102 |          77 |                 0 | 5170dc5f93bc1119d5ae4e33c35b9a9a | 2014-12-23 07:08:35 | 2014-12-23 07:26:51 |
| SELECT `performance_schema` .  ... a` . `accounts` . `HOST` = ... | sys  |           |          3 |         0 |          0 | 619.11 ms     | 614.23 ms   | 206.37 ms   | 540.72 ms    |         0 |             0 |             0 |                 0 |         15 |               0 |           0 |                 0 | 46f73fffb370a0c58fe74b1f0c71f85b | 2014-12-23 06:52:12 | 2014-12-23 07:26:42 |
| SELECT `information_schema` .  ... TICS` . `INDEX_TYPE` UNION ... | sys  |           |          3 |         0 |          0 | 367.43 ms     | 328.81 ms   | 122.48 ms   | 109.00 us    |         0 |             0 |             0 |                 0 |         21 |               9 |           0 |                 0 | ae52e0db739083c9787386574a466db2 | 2014-12-23 06:52:12 | 2014-12-23 07:26:43 |
| SELECT IF ( ( `locate` ( ? , ` ...  . `COMPRESSED_SIZE` ) ) DESC  | sys  |           |          3 |         0 |          0 | 330.62 ms     | 256.40 ms   | 110.21 ms   | 117.00 us    |         0 |             0 |             0 |                 0 |          6 |               3 |           0 |                 0 | 97b93c349004ed5d8774479aecc83eda | 2014-12-23 06:52:12 | 2014-12-23 07:26:42 |
| SELECT IF ( `isnull` ( `inform ... tory_long` . `THREAD_ID` = ... | sys  |           |          3 |         0 |          0 | 123.40 ms     | 99.50 ms    | 41.13 ms    | 150.00 us    |         0 |             0 |             0 |                 0 |          6 |               3 |           0 |                 0 | 6a3616ea2e897af54c2cd67e897c1026 | 2014-12-23 06:52:12 | 2014-12-23 07:26:42 |
| SELECT * FROM `schema_table_st ... at_bytes` ( SUM ( `fsbi` . ... | sys  | *         |          1 |         0 |          0 | 101.48 ms     | 101.48 ms   | 101.48 ms   | 101.42 ms    |        10 |            10 |          9210 |              9210 |          7 |               3 |         186 |                 0 | b7b460a53f2b7adaa1ef906ab252ffcf | 2014-12-23 08:00:23 | 2014-12-23 08:00:23 |
| SELECT * FROM `schema_table_st ... at_bytes` ( SUM ( `fsbi` . ... | sys  | *         |          1 |         0 |          0 | 101.04 ms     | 101.04 ms   | 101.04 ms   | 100.96 ms    |       109 |           109 |          9309 |              9309 |          7 |               3 |         186 |                 0 | 118fe5e749cef7bb98721f42b27272cd | 2014-12-23 08:00:17 | 2014-12-23 08:00:17 |
| SELECT * FROM `sys` . `innodb_ ... bp` . `COMPRESSED_SIZE` = ? )  | sys  | *         |          1 |         0 |          0 | 84.68 ms      | 84.68 ms    | 84.68 ms    | 84.64 ms     |        32 |            32 |          8364 |              8364 |          4 |               3 |         108 |                 0 | 904e7bec1adda724acb5246c40a7250e | 2014-12-23 07:39:16 | 2014-12-23 07:39:16 |
| SELECT * FROM `innodb_buffer_s ...  . `COMPRESSED_SIZE` ) ) DESC  | sys  | *         |          1 |         0 |          0 | 84.26 ms      | 84.26 ms    | 84.26 ms    | 84.21 ms     |         5 |             5 |          8251 |              8251 |          4 |               3 |          49 |                 0 | 7f118a1b7ade086fc5721e0c52d67e8d | 2014-12-23 07:46:47 | 2014-12-23 07:46:47 |
| SELECT * FROM `innodb_buffer_s ...  `ibp` . `COMPRESSED_SIZE` ... | sys  | *         |          1 |         0 |          0 | 76.98 ms      | 76.98 ms    | 76.98 ms    | 76.83 ms     |        32 |            32 |          8332 |              8332 |          4 |               3 |          76 |                 0 | 3e176b8aec7493370bac9a0140121550 | 2014-12-23 07:48:05 | 2014-12-23 07:48:05 |
+-------------------------------------------------------------------+------+-----------+------------+-----------+------------+---------------+-------------+-------------+--------------+-----------+---------------+---------------+-------------------+------------+-----------------+-------------+-------------------+----------------------------------+---------------------+---------------------+
10 rows in set (0.00 sec)

root@localhost [sys]>

root@localhost [sys]>select * from statements_with_errors_or_warnings;
+-------------------------------------------------------------------+------+------------+--------+-----------+----------+-------------+---------------------+---------------------+----------------------------------+
| query                                                             | db   | exec_count | errors | error_pct | warnings | warning_pct | first_seen          | last_seen           | digest                           |
+-------------------------------------------------------------------+------+------------+--------+-----------+----------+-------------+---------------------+---------------------+----------------------------------+
| SELECT * FROM `sys` . `schema_ ... ATISTICS` . `TABLE_SCHEMA` ... | sys  |          4 |      3 |   75.0000 |        1 |     25.0000 | 2014-12-23 07:08:35 | 2014-12-23 07:26:51 | 5170dc5f93bc1119d5ae4e33c35b9a9a |
| <                                                                 | NULL |          1 |      1 |  100.0000 |        0 |      0.0000 | 2014-12-23 07:04:10 | 2014-12-23 07:04:10 | 4c4154c90f9c70d2c865fbba17469831 |
| SELECT * FROM `sys` . `schema_overview` WHERE `db` = ?            | sys  |          1 |      1 |  100.0000 |        0 |      0.0000 | 2014-12-23 07:08:09 | 2014-12-23 07:08:09 | 9c0f4109fbd431777e669b0024381584 |
| SELECT * FROM `federated_table`                                   | test |          1 |      1 |  100.0000 |        0 |      0.0000 | 2014-12-23 07:11:40 | 2014-12-23 07:11:40 | 4efc47cc0a3b062fe8ecd4f6dc751c98 |
| DESC `Personal_Info` +                                            | test |          1 |      1 |  100.0000 |        0 |      0.0000 | 2014-12-23 07:54:50 | 2014-12-23 07:54:50 | 1fecc49ac0c1257680c5d3b14c27a4a5 |
| SELECT * FROM PROCESSLIST :                                       | sys  |          1 |      1 |  100.0000 |        0 |      0.0000 | 2014-12-23 07:56:40 | 2014-12-23 07:56:40 | 0a161211bcfa223ac5355844c9b8f45e |
| SELECT * FROM `sys` . `user_su ... atency` `stmt` ON ( ( IF ( ... | sys  |          1 |      0 |    0.0000 |        4 |    400.0000 | 2014-12-23 07:30:54 | 2014-12-23 07:30:54 | b8cc646b95da6a9fe467debd187bba98 |
+-------------------------------------------------------------------+------+------------+--------+-----------+----------+-------------+---------------------+---------------------+----------------------------------+
7 rows in set (0.00 sec)

root@localhost [sys]>

root@localhost [sys]>select * from statements_with_full_table_scans limit 0,10;
+-------------------------------------------------------------------+------+------------+---------------+---------------------+--------------------------+-------------------+-----------+---------------+---------------+-------------------+---------------------+---------------------+----------------------------------+
| query                                                             | db   | exec_count | total_latency | 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 `ps_check_lost_i ... s` . `VARIABLE_VALUE` > ? ) )  | sys  |          1 | 955.72 us     |                   1 |                        0 |               100 |         0 |            23 |             0 |                23 | 2014-12-23 07:58:03 | 2014-12-23 07:58:03 | 4e4d87490ae3897ea806bd076116593d |
| SELECT * FROM `latest_file_io` ... ( ( `performance_schema` . ... | sys  |          3 | 9.19 ms       |                   3 |                        0 |               100 |         0 |            62 |             0 |                21 | 2014-12-23 07:53:38 | 2014-12-23 07:55:10 | 55e76ed25f3c3206f0f0c3905be1c7d7 |
| SELECT * FROM `sys` . `innodb_ ... bp` . `COMPRESSED_SIZE` = ? )  | sys  |          1 | 84.68 ms      |                   1 |                        0 |               100 |        32 |          8364 |            32 |              8364 | 2014-12-23 07:39:16 | 2014-12-23 07:39:16 | 904e7bec1adda724acb5246c40a7250e |
| SELECT * FROM `innodb_buffer_s ...  . `COMPRESSED_SIZE` ) ) DESC  | sys  |          1 | 84.26 ms      |                   1 |                        0 |               100 |         5 |          8251 |             5 |              8251 | 2014-12-23 07:46:47 | 2014-12-23 07:46:47 | 7f118a1b7ade086fc5721e0c52d67e8d |
| SELECT * FROM `innodb_buffer_s ...  `ibp` . `COMPRESSED_SIZE` ... | sys  |          1 | 76.98 ms      |                   1 |                        0 |               100 |        32 |          8332 |            32 |              8332 | 2014-12-23 07:48:05 | 2014-12-23 07:48:05 | 3e176b8aec7493370bac9a0140121550 |
| SELECT * FROM `io_global_by_wa ... ( ( `performance_schema` . ... | sys  |          1 | 7.91 ms       |                   1 |                        0 |               100 |        10 |            56 |            10 |                56 | 2014-12-23 07:52:01 | 2014-12-23 07:52:01 | fd3b6f36113a46d3865a75837fa0c790 |
| SELECT * FROM `sys` . `user_su ... mt` ON ( ( IF ( `isnull` ( ... | sys  |          1 | 7.21 ms       |                   1 |                        0 |               100 |         4 |          1842 |             4 |              1842 | 2014-12-23 07:30:59 | 2014-12-23 07:30:59 | dde89d4792dc23edcf2ff3ea77d8f28d |
| SELECT * FROM `sys` . `user_su ... atency` `stmt` ON ( ( IF ( ... | sys  |          7 | 66.60 ms      |                   7 |                        0 |               100 |         7 |         11033 |             1 |              1576 | 2014-12-23 07:29:37 | 2014-12-23 07:30:51 | 769b46157ec1a6441a48da07d1ccf7b2 |
| SELECT * FROM `io_global_by_fi ... `file_summary_by_instance` ... | sys  |          1 | 62.30 ms      |                   1 |                        0 |               100 |       375 |           750 |           375 |               750 | 2014-12-23 07:50:57 | 2014-12-23 07:50:57 | 18f8431093bdf755abe8470a130a7e5b |
| SELECT * FROM `host_summary_by ... ` , `performance_schema` . ... | sys  |          1 | 6.76 ms       |                   1 |                        0 |               100 |        36 |          1184 |            36 |              1184 | 2014-12-23 07:42:48 | 2014-12-23 07:42:48 | c3fe3613ac65b56475622798e793bae6 |
+-------------------------------------------------------------------+------+------------+---------------+---------------------+--------------------------+-------------------+-----------+---------------+---------------+-------------------+---------------------+---------------------+----------------------------------+
10 rows in set (0.01 sec)

root@localhost [sys]>

root@localhost [sys]>select * from statements_with_runtimes_in_95th_percentile;
+-------------------------------------------------------------------+------+-----------+------------+-----------+------------+---------------+-------------+-------------+-----------+---------------+---------------+-------------------+---------------------+---------------------+----------------------------------+
| query                                                             | db   | full_scan | exec_count | err_count | warn_count | total_latency | max_latency | avg_latency | rows_sent | rows_sent_avg | rows_examined | rows_examined_avg | first_seen          | last_seen           | digest                           |
+-------------------------------------------------------------------+------+-----------+------------+-----------+------------+---------------+-------------+-------------+-----------+---------------+---------------+-------------------+---------------------+---------------------+----------------------------------+
| SELECT * FROM `sys` . `schema_ ... ATISTICS` . `TABLE_SCHEMA` ... | sys  | *         |          4 |         3 |          1 | 5.23 s        | 4.68 s      | 1.31 s      |         3 |             1 |           499 |               125 | 2014-12-23 07:08:35 | 2014-12-23 07:26:51 | 5170dc5f93bc1119d5ae4e33c35b9a9a |
| SELECT `performance_schema` .  ... a` . `accounts` . `HOST` = ... | sys  |           |          3 |         0 |          0 | 619.11 ms     | 614.23 ms   | 206.37 ms   |         0 |             0 |             0 |                 0 | 2014-12-23 06:52:12 | 2014-12-23 07:26:42 | 46f73fffb370a0c58fe74b1f0c71f85b |
| SELECT `information_schema` .  ... TICS` . `INDEX_TYPE` UNION ... | sys  |           |          3 |         0 |          0 | 367.43 ms     | 328.81 ms   | 122.48 ms   |         0 |             0 |             0 |                 0 | 2014-12-23 06:52:12 | 2014-12-23 07:26:43 | ae52e0db739083c9787386574a466db2 |
| SELECT IF ( ( `locate` ( ? , ` ...  . `COMPRESSED_SIZE` ) ) DESC  | sys  |           |          3 |         0 |          0 | 330.62 ms     | 256.40 ms   | 110.21 ms   |         0 |             0 |             0 |                 0 | 2014-12-23 06:52:12 | 2014-12-23 07:26:42 | 97b93c349004ed5d8774479aecc83eda |
| SELECT * FROM `schema_table_st ... at_bytes` ( SUM ( `fsbi` . ... | sys  | *         |          1 |         0 |          0 | 101.48 ms     | 101.48 ms   | 101.48 ms   |        10 |            10 |          9210 |              9210 | 2014-12-23 08:00:23 | 2014-12-23 08:00:23 | b7b460a53f2b7adaa1ef906ab252ffcf |
| SELECT * FROM `schema_table_st ... at_bytes` ( SUM ( `fsbi` . ... | sys  | *         |          1 |         0 |          0 | 101.04 ms     | 101.04 ms   | 101.04 ms   |       109 |           109 |          9309 |              9309 | 2014-12-23 08:00:17 | 2014-12-23 08:00:17 | 118fe5e749cef7bb98721f42b27272cd |
| SELECT * FROM `sys` . `innodb_ ... bp` . `COMPRESSED_SIZE` = ? )  | sys  | *         |          1 |         0 |          0 | 84.68 ms      | 84.68 ms    | 84.68 ms    |        32 |            32 |          8364 |              8364 | 2014-12-23 07:39:16 | 2014-12-23 07:39:16 | 904e7bec1adda724acb5246c40a7250e |
| SELECT * FROM `innodb_buffer_s ...  . `COMPRESSED_SIZE` ) ) DESC  | sys  | *         |          1 |         0 |          0 | 84.26 ms      | 84.26 ms    | 84.26 ms    |         5 |             5 |          8251 |              8251 | 2014-12-23 07:46:47 | 2014-12-23 07:46:47 | 7f118a1b7ade086fc5721e0c52d67e8d |
+-------------------------------------------------------------------+------+-----------+------------+-----------+------------+---------------+-------------+-------------+-----------+---------------+---------------+-------------------+---------------------+---------------------+----------------------------------+
8 rows in set (0.05 sec)

root@localhost [sys]>

root@localhost [sys]>select * from statements_with_sorting limit 0,10;
+-------------------------------------------------------------------+------+------------+---------------+-------------------+-----------------+-------------------+------------------+-------------+-----------------+---------------------+---------------------+----------------------------------+
| query                                                             | db   | exec_count | total_latency | sort_merge_passes | avg_sort_merges | sorts_using_scans | sort_using_range | rows_sorted | avg_rows_sorted | first_seen          | last_seen           | digest                           |
+-------------------------------------------------------------------+------+------------+---------------+-------------------+-----------------+-------------------+------------------+-------------+-----------------+---------------------+---------------------+----------------------------------+
| SELECT * FROM `sys` . `schema_ ... ATISTICS` . `TABLE_SCHEMA` ... | sys  |          4 | 5.23 s        |                 0 |               0 |                12 |                0 |          77 |              19 | 2014-12-23 07:08:35 | 2014-12-23 07:26:51 | 5170dc5f93bc1119d5ae4e33c35b9a9a |
| SELECT * FROM `schema_table_st ... at_bytes` ( SUM ( `fsbi` . ... | sys  |          1 | 101.48 ms     |                 0 |               0 |                 3 |                0 |         186 |             186 | 2014-12-23 08:00:23 | 2014-12-23 08:00:23 | b7b460a53f2b7adaa1ef906ab252ffcf |
| SELECT * FROM `schema_table_st ... at_bytes` ( SUM ( `fsbi` . ... | sys  |          1 | 101.04 ms     |                 0 |               0 |                 3 |                0 |         186 |             186 | 2014-12-23 08:00:17 | 2014-12-23 08:00:17 | 118fe5e749cef7bb98721f42b27272cd |
| SELECT * FROM `sys` . `innodb_ ... bp` . `COMPRESSED_SIZE` = ? )  | sys  |          1 | 84.68 ms      |                 0 |               0 |                 3 |                0 |         108 |             108 | 2014-12-23 07:39:16 | 2014-12-23 07:39:16 | 904e7bec1adda724acb5246c40a7250e |
| SELECT * FROM `innodb_buffer_s ...  . `COMPRESSED_SIZE` ) ) DESC  | sys  |          1 | 84.26 ms      |                 0 |               0 |                 2 |                0 |          49 |              49 | 2014-12-23 07:46:47 | 2014-12-23 07:46:47 | 7f118a1b7ade086fc5721e0c52d67e8d |
| SELECT * FROM `innodb_buffer_s ...  `ibp` . `COMPRESSED_SIZE` ... | sys  |          1 | 76.98 ms      |                 0 |               0 |                 2 |                0 |          76 |              76 | 2014-12-23 07:48:05 | 2014-12-23 07:48:05 | 3e176b8aec7493370bac9a0140121550 |
| SELECT * FROM `sys` . `user_su ... atency` `stmt` ON ( ( IF ( ... | sys  |          7 | 66.60 ms      |                 0 |               0 |                21 |                0 |          65 |               9 | 2014-12-23 07:29:37 | 2014-12-23 07:30:51 | 769b46157ec1a6441a48da07d1ccf7b2 |
| SELECT * FROM `io_global_by_fi ... `file_summary_by_instance` ... | sys  |          1 | 62.30 ms      |                 0 |               0 |                 1 |                0 |         375 |             375 | 2014-12-23 07:50:57 | 2014-12-23 07:50:57 | 18f8431093bdf755abe8470a130a7e5b |
| SELECT * FROM `io_global_by_fi ... ile_summary_by_instance` . ... | sys  |          1 | 58.35 ms      |                 0 |               0 |                 1 |                0 |         375 |             375 | 2014-12-23 07:49:45 | 2014-12-23 07:49:45 | 3ba99c6174dec8f0c634305296abb19b |
| SELECT * FROM `schema_object_o ... MA` , `information_schema` ... | sys  |          1 | 56.56 ms      |                 0 |               0 |                 6 |                0 |          38 |              38 | 2014-12-23 07:59:21 | 2014-12-23 07:59:21 | ccc857fb69a9f151a1b8cb8687697b1a |
+-------------------------------------------------------------------+------+------------+---------------+-------------------+-----------------+-------------------+------------------+-------------+-----------------+---------------------+---------------------+----------------------------------+
10 rows in set (0.01 sec)

root@localhost [sys]>

root@localhost [sys]>select * from statements_with_temp_tables limit 0,10;
+-------------------------------------------------------------------+------+------------+---------------+-------------------+-----------------+--------------------------+------------------------+---------------------+---------------------+----------------------------------+
| query                                                             | db   | exec_count | total_latency | memory_tmp_tables | disk_tmp_tables | avg_tmp_tables_per_query | tmp_tables_to_disk_pct | first_seen          | last_seen           | digest                           |
+-------------------------------------------------------------------+------+------------+---------------+-------------------+-----------------+--------------------------+------------------------+---------------------+---------------------+----------------------------------+
| SELECT * FROM `sys` . `schema_ ... ATISTICS` . `TABLE_SCHEMA` ... | sys  |          4 | 5.23 s        |               579 |             102 |                      145 |                     18 | 2014-12-23 07:08:35 | 2014-12-23 07:26:51 | 5170dc5f93bc1119d5ae4e33c35b9a9a |
| SELECT * FROM `schema_object_o ... MA` , `information_schema` ... | sys  |          1 | 56.56 ms      |               189 |              33 |                      189 |                     17 | 2014-12-23 07:59:21 | 2014-12-23 07:59:21 | ccc857fb69a9f151a1b8cb8687697b1a |
| SELECT `information_schema` .  ... TICS` . `INDEX_TYPE` UNION ... | sys  |          3 | 367.43 ms     |                21 |               9 |                        7 |                     43 | 2014-12-23 06:52:12 | 2014-12-23 07:26:43 | ae52e0db739083c9787386574a466db2 |
| SELECT `stmts` . `DIGEST_TEXT` ... M ( `performance_schema` . ... | sys  |          3 | 35.45 ms      |                12 |               3 |                        4 |                     25 | 2014-12-23 06:52:13 | 2014-12-23 07:26:43 | f3ccd31f72c22e9419a309de00822ff5 |
| SELECT * FROM `schema_table_st ... at_bytes` ( SUM ( `fsbi` . ... | sys  |          1 | 101.04 ms     |                 7 |               3 |                        7 |                     43 | 2014-12-23 08:00:17 | 2014-12-23 08:00:17 | 118fe5e749cef7bb98721f42b27272cd |
| SELECT * FROM `schema_table_st ... at_bytes` ( SUM ( `fsbi` . ... | sys  |          1 | 101.48 ms     |                 7 |               3 |                        7 |                     43 | 2014-12-23 08:00:23 | 2014-12-23 08:00:23 | b7b460a53f2b7adaa1ef906ab252ffcf |
| SELECT IF ( ( `locate` ( ? , ` ...  . `COMPRESSED_SIZE` ) ) DESC  | sys  |          3 | 14.25 ms      |                 6 |               3 |                        2 |                     50 | 2014-12-23 06:52:12 | 2014-12-23 07:26:42 | 5aae34a9612abcf6604a68de054cbb85 |
| SELECT IF ( ( `locate` ( ? , ` ...  . `COMPRESSED_SIZE` ) ) DESC  | sys  |          3 | 330.62 ms     |                 6 |               3 |                        2 |                     50 | 2014-12-23 06:52:12 | 2014-12-23 07:26:42 | 97b93c349004ed5d8774479aecc83eda |
| SELECT IF ( `isnull` ( `inform ... tory_long` . `THREAD_ID` = ... | sys  |          3 | 123.40 ms     |                 6 |               3 |                        2 |                     50 | 2014-12-23 06:52:12 | 2014-12-23 07:26:42 | 6a3616ea2e897af54c2cd67e897c1026 |
| SELECT IF ( ( `locate` ( ? , ` ...  . `COMPRESSED_SIZE` ) ) DESC  | sys  |          3 | 31.08 ms      |                 6 |               3 |                        2 |                     50 | 2014-12-23 06:52:13 | 2014-12-23 07:26:43 | 544388f8ee4e7f53375a9b186533fb4a |
+-------------------------------------------------------------------+------+------------+---------------+-------------------+-----------------+--------------------------+------------------------+---------------------+---------------------+----------------------------------+
10 rows in set (0.01 sec)

root@localhost [sys]>

root@localhost [sys]>select * from user_summary;
+---------------+------------+-------------------+-----------------------+-------------+----------+-----------------+---------------------+-------------------+--------------+
| user          | statements | statement_latency | statement_avg_latency | table_scans | file_ios | file_io_latency | current_connections | total_connections | unique_hosts |
+---------------+------------+-------------------+-----------------------+-------------+----------+-----------------+---------------------+-------------------+--------------+
| GTID_SSL_USER |          9 | 4.44 ms           | 493.35 us             |           2 |     3485 | 1.62 s          |                   1 |                 1 |            1 |
| admin         |         86 | 163.00 ms         | 1.90 ms               |          16 |      397 | 96.51 ms        |                   1 |                 3 |            1 |
| background    |       NULL | NULL              | 0 ps                  |        NULL |     2179 | 9.42 s          |                  18 |                22 |            0 |
| root          |        340 | 8.50 s            | 25.01 ms              |          71 |    12468 | 5.36 s          |                   1 |                 3 |            1 |
+---------------+------------+-------------------+-----------------------+-------------+----------+-----------------+---------------------+-------------------+--------------+
4 rows in set (0.01 sec)

root@localhost [sys]>

root@localhost [sys]>select * from user_summary_by_file_io;
+---------------+-------+------------+
| user          | ios   | io_latency |
+---------------+-------+------------+
| background    |  2179 | 9.42 s     |
| root          | 12474 | 5.36 s     |
| GTID_SSL_USER |  3485 | 1.62 s     |
| admin         |   397 | 96.51 ms   |
+---------------+-------+------------+
4 rows in set (0.01 sec)

root@localhost [sys]>

root@localhost [sys]>select * from user_summary_by_file_io_type limit 0,10;
+---------------+--------------------------------------+-------+-----------+-------------+
| user          | event_name                           | total | latency   | max_latency |
+---------------+--------------------------------------+-------+-----------+-------------+
| GTID_SSL_USER | wait/io/file/csv/data                |  3405 | 825.63 ms | 282.11 ms   |
| GTID_SSL_USER | wait/io/file/csv/metadata            |    21 | 774.72 ms | 367.48 ms   |
| GTID_SSL_USER | wait/io/file/sql/FRM                 |    39 | 17.67 ms  | 17.53 ms    |
| GTID_SSL_USER | wait/io/file/sql/binlog              |    20 | 152.98 us | 56.58 us    |
| admin         | wait/io/file/myisam/kfile            |    90 | 68.84 ms  | 42.58 ms    |
| admin         | wait/io/file/innodb/innodb_log_file  |     2 | 21.94 ms  | 21.90 ms    |
| admin         | wait/io/file/csv/data                |   264 | 4.28 ms   | 548.74 us   |
| admin         | wait/io/file/myisam/dfile            |    29 | 968.23 us | 203.67 us   |
| admin         | wait/io/file/innodb/innodb_data_file |     7 | 408.68 us | 167.94 us   |
| admin         | wait/io/file/sql/binlog              |     1 | 39.46 us  | 39.46 us    |
+---------------+--------------------------------------+-------+-----------+-------------+
10 rows in set (0.00 sec)

root@localhost [sys]>

root@localhost [sys]>select * from user_summary_by_stages;
Empty set (0.00 sec)

root@localhost [sys]>

root@localhost [sys]>select * from user_summary_by_statement_latency;
+---------------+-------+---------------+-------------+--------------+-----------+---------------+---------------+------------+
| user          | total | total_latency | max_latency | lock_latency | rows_sent | rows_examined | rows_affected | full_scans |
+---------------+-------+---------------+-------------+--------------+-----------+---------------+---------------+------------+
| root          |   346 | 8.53 s        | 5.41 s      | 1.84 s       |      2348 |         88008 |             1 |         77 |
| admin         |    86 | 163.00 ms     | 88.86 ms    | 13.77 ms     |       154 |           144 |             1 |         16 |
| GTID_SSL_USER |     9 | 4.44 ms       | 2.65 ms     | 698.00 us    |         5 |             2 |             0 |          2 |
+---------------+-------+---------------+-------------+--------------+-----------+---------------+---------------+------------+
3 rows in set (0.01 sec)

root@localhost [sys]>

root@localhost [sys]>select * from user_summary_by_statement_type limit 0,10;
+---------------+----------------+-------+---------------+-------------+--------------+-----------+---------------+---------------+------------+
| user          | statement      | total | total_latency | max_latency | lock_latency | rows_sent | rows_examined | rows_affected | full_scans |
+---------------+----------------+-------+---------------+-------------+--------------+-----------+---------------+---------------+------------+
| GTID_SSL_USER | show_variables |     2 | 2.48 ms       | 1.78 ms     | 331.00 us    |         2 |             2 |             0 |          2 |
| GTID_SSL_USER | set_option     |     3 | 925.25 us     | 325.51 us   | 162.00 us    |         0 |             0 |             0 |          0 |
| GTID_SSL_USER | select         |     3 | 856.66 us     | 370.57 us   | 205.00 us    |         3 |             0 |             0 |          0 |
| GTID_SSL_USER | Register Slave |     1 | 176.49 us     | 176.49 us   | 0 ps         |         0 |             0 |             0 |          0 |
| admin         | Field List     |    51 | 97.22 ms      | 47.42 ms    | 2.31 ms      |         0 |             0 |             0 |          0 |
| admin         | insert         |     1 | 25.18 ms      | 25.18 ms    | 628.00 us    |         0 |             0 |             1 |          0 |
| admin         | select         |    14 | 12.20 ms      | 2.90 ms     | 1.96 ms      |        18 |             8 |             0 |          3 |
| admin         | show_tables    |     8 | 11.54 ms      | 3.24 ms     | 2.10 ms      |       102 |           102 |             0 |          8 |
| admin         | show_databases |     4 | 7.10 ms       | 2.40 ms     | 1.62 ms      |        32 |            32 |             0 |          4 |
| admin         | show_fields    |     1 | 5.29 ms       | 5.29 ms     | 4.31 ms      |         2 |             2 |             0 |          1 |
+---------------+----------------+-------+---------------+-------------+--------------+-----------+---------------+---------------+------------+
10 rows in set (0.01 sec)

root@localhost [sys]>

root@localhost [sys]>select * from version;
+-------------+-------------------------------------------+
| sys_version | mysql_version                             |
+-------------+-------------------------------------------+
| 1.1.0       | 5.6.21-enterprise-commercial-advanced-log |
+-------------+-------------------------------------------+
1 row in set (0.00 sec)

root@localhost [sys]>

root@localhost [sys]>select * from wait_classes_global_by_avg_latency;
+-----------------+-------+---------------+-------------+-------------+-------------+
| event_class     | total | total_latency | min_latency | avg_latency | max_latency |
+-----------------+-------+---------------+-------------+-------------+-------------+
| wait/io/file    | 18586 | 16.49 s       | 0 ps        | 887.44 us   | 711.30 ms   |
| wait/io/table   |    38 | 1.26 ms       | 219.49 ns   | 33.20 us    | 665.72 us   |
| wait/lock/table |    12 | 365.26 us     | 684.61 ns   | 30.44 us    | 349.01 us   |
+-----------------+-------+---------------+-------------+-------------+-------------+
3 rows in set (0.01 sec)

root@localhost [sys]>

root@localhost [sys]>select * from wait_classes_global_by_latency;
+-----------------+-------+---------------+-------------+-------------+-------------+
| event_class     | total | total_latency | min_latency | avg_latency | max_latency |
+-----------------+-------+---------------+-------------+-------------+-------------+
| wait/io/file    | 18592 | 16.49 s       | 0 ps        | 887.16 us   | 711.30 ms   |
| wait/io/table   |    38 | 1.26 ms       | 219.49 ns   | 33.20 us    | 665.72 us   |
| wait/lock/table |    12 | 365.26 us     | 684.61 ns   | 30.44 us    | 349.01 us   |
+-----------------+-------+---------------+-------------+-------------+-------------+
3 rows in set (0.01 sec)

root@localhost [sys]>

root@localhost [sys]>select * from waits_by_host_by_latency limit 0,10;
+----------------+--------------------------------------+-------+---------------+-------------+-------------+
| host           | event                                | total | total_latency | avg_latency | max_latency |
+----------------+--------------------------------------+-------+---------------+-------------+-------------+
| 192.168.56.108 | wait/io/file/myisam/kfile            |    90 | 68.84 ms      | 764.84 us   | 42.58 ms    |
| 192.168.56.108 | wait/io/file/innodb/innodb_log_file  |     2 | 21.94 ms      | 10.97 ms    | 21.90 ms    |
| 192.168.56.108 | wait/io/file/csv/data                |   264 | 4.28 ms       | 16.22 us    | 548.74 us   |
| 192.168.56.108 | wait/io/table/sql/handler            |    12 | 1.07 ms       | 89.53 us    | 665.72 us   |
| 192.168.56.108 | wait/io/file/myisam/dfile            |    29 | 968.23 us     | 33.39 us    | 203.67 us   |
| 192.168.56.108 | wait/io/file/innodb/innodb_data_file |     7 | 408.68 us     | 58.38 us    | 167.94 us   |
| 192.168.56.108 | wait/lock/table/sql/handler          |     8 | 359.52 us     | 44.94 us    | 349.01 us   |
| 192.168.56.108 | wait/io/file/sql/binlog              |     1 | 39.46 us      | 39.46 us    | 39.46 us    |
| 192.168.56.108 | wait/io/file/sql/dbopt               |     4 | 36.28 us      | 9.07 us     | 12.14 us    |
| CentOS02       | wait/io/file/csv/data                |  3405 | 825.63 ms     | 242.48 us   | 282.11 ms   |
+----------------+--------------------------------------+-------+---------------+-------------+-------------+
10 rows in set (0.01 sec)

root@localhost [sys]>

root@localhost [sys]>select * from waits_by_user_by_latency limit 0,10;
+---------------+--------------------------------------+-------+---------------+-------------+-------------+
| user          | event                                | total | total_latency | avg_latency | max_latency |
+---------------+--------------------------------------+-------+---------------+-------------+-------------+
| GTID_SSL_USER | wait/io/file/csv/data                |  3405 | 825.63 ms     | 242.48 us   | 282.11 ms   |
| GTID_SSL_USER | wait/io/file/csv/metadata            |    21 | 774.72 ms     | 36.89 ms    | 367.48 ms   |
| GTID_SSL_USER | wait/io/file/sql/FRM                 |    39 | 17.67 ms      | 453.10 us   | 17.53 ms    |
| GTID_SSL_USER | wait/io/file/sql/binlog              |    20 | 152.98 us     | 7.65 us     | 56.58 us    |
| admin         | wait/io/file/myisam/kfile            |    90 | 68.84 ms      | 764.84 us   | 42.58 ms    |
| admin         | wait/io/file/innodb/innodb_log_file  |     2 | 21.94 ms      | 10.97 ms    | 21.90 ms    |
| admin         | wait/io/file/csv/data                |   264 | 4.28 ms       | 16.22 us    | 548.74 us   |
| admin         | wait/io/table/sql/handler            |    12 | 1.07 ms       | 89.53 us    | 665.72 us   |
| admin         | wait/io/file/myisam/dfile            |    29 | 968.23 us     | 33.39 us    | 203.67 us   |
| admin         | wait/io/file/innodb/innodb_data_file |     7 | 408.68 us     | 58.38 us    | 167.94 us   |
+---------------+--------------------------------------+-------+---------------+-------------+-------------+
10 rows in set (0.00 sec)

root@localhost [sys]>

root@localhost [sys]>select * from waits_global_by_latency limit 0,10;
+--------------------------------------+-------+---------------+-------------+-------------+
| events                               | total | total_latency | avg_latency | max_latency |
+--------------------------------------+-------+---------------+-------------+-------------+
| wait/io/file/innodb/innodb_data_file |   752 | 3.69 s        | 4.91 ms     | 362.56 ms   |
| wait/io/file/sql/FRM                 |  1997 | 3.33 s        | 1.67 ms     | 288.10 ms   |
| wait/io/file/myisam/kfile            |  6022 | 1.67 s        | 277.65 us   | 325.61 ms   |
| wait/io/file/sql/binlog              |   378 | 1.32 s        | 3.50 ms     | 208.15 ms   |
| wait/io/file/innodb/innodb_log_file  |    30 | 1.20 s        | 40.04 ms    | 394.59 ms   |
| wait/io/file/csv/metadata            |    70 | 1.19 s        | 17.04 ms    | 367.48 ms   |
| wait/io/file/csv/data                |  5190 | 910.17 ms     | 175.37 us   | 282.11 ms   |
| wait/io/file/sql/binlog_index        |    20 | 809.93 ms     | 40.50 ms    | 411.41 ms   |
| wait/io/file/sql/ERRMSG              |     5 | 771.84 ms     | 154.37 ms   | 711.30 ms   |
| wait/io/file/sql/slow_log            |     4 | 428.92 ms     | 107.23 ms   | 428.90 ms   |
+--------------------------------------+-------+---------------+-------------+-------------+
10 rows in set (0.01 sec)

root@localhost [sys]>

GITからのダウンロード。
英語ですが、こちらのページに各Viewの詳細が説明されております。
https://github.com/MarkLeith/mysql-sys

上記Gitからダウンロードした方がObjectの数が多いようです。

[admin@CentOS01 mysql-sys-master]$ mysql -u root -p < sys_56.sql 
Enter password: 
[admin@CentOS01 mysql-sys-master]$ 


root@localhost [sys]>select * from sys.schema_object_overview where db = 'sys';
+-----+---------------+-------+
| db  | object_type   | count |
+-----+---------------+-------+
| sys | BASE TABLE    |     1 |
| sys | INDEX (BTREE) |     1 |
| sys | TRIGGER       |     2 |
| sys | FUNCTION      |    11 |
| sys | PROCEDURE     |    22 |
| sys | VIEW          |    81 |
+-----+---------------+-------+
6 rows in set (0.06 sec)

root@localhost [sys]>

SYS Schemaに関しては、こちらが参考になるかと思います。
https://oracleus.activeevents.com/2014/connect/fileDownload/session/72527FD42DFF7B2148314B9E72BE7B6A/CON3751_Leith-mysql_sys_schema_oow_2014.pdf


MYSQL5.6へアップグレード後のWarningを無くす為にmysqlのオプションファイルを編集

InnoDBのメモリー処理関連のWarning

2013-05-05 16:28:19 b784c8e0 InnoDB: Warning: Using innodb_additional_mem_pool_size is DEPRECATED. 
This option may be removed in future releases, together with the option innodb_use_sys_malloc and with the InnoDB's internal memory allocator.
[root@HOME001 data]# mysql -u root -p -e "show variables like 'innodb_use_sys_malloc'"
Enter password:
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_use_sys_malloc | ON    |
+-----------------------+-------+
[root@HOME001 data]#

以下の値をOptionファイルに追加して対応。
innodb_use_sys_malloc

innodb_buffer_pool_size = 32M
#innodb_additional_mem_pool_size = 2M
innodb_use_sys_malloc=1

http://dev.mysql.com/doc/refman/5.1/en/innodb-parameters.html#sysvar_innodb_use_sys_malloc

http://docs.oracle.com/cd/E17952_01/refman-5.5-en/innodb-performance-use_sys_malloc.html

You can control whether InnoDB uses its own memory allocator or an allocator of the operating system,
by setting the value of the system configuration parameter innodb_use_sys_malloc in the MySQL option file (my.cnf or my.ini).
If set to ON or 1 (the default), InnoDB uses the malloc and free functions of the underlying system rather than manage memory pools itself.
This parameter is not dynamic, and takes effect only when the system is started.
To continue to use the InnoDB memory allocator, set innodb_use_sys_malloc to 0.

InnoDBのメモリーマネージャーではなく、OSのメモリ割り当てを任せるという事になるが、
どちらが良いかはタイミング見て確認。

TIMESTAMP型を指定した時の処理の変更点

2013-05-05 16:43:37 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. 
Please use --explicit_defaults_for_timestamp server option (see documentation for more details).

設定を変えなければ、TIMESTAMPを指定したときのテーブル属性に変更無し。

[root@HOME001 data]# mysql -u root -p -e "show variables like 'explicit_defaults_for_timestamp'"
Enter password:
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| explicit_defaults_for_timestamp | OFF   |
+---------------------------------+-------+
[root@HOME001 data]# mysql -u root -p -e "create table test.timestamp_chk (chk_explicit_defaults_for_timestamp timestamp) ENGINE=InnoDB;"
Enter password:
[root@HOME001 data]# mysql -u root -p -e "show create table test.timestamp_chk\G"
Enter password:
*************************** 1. row ***************************
       Table: timestamp_chk
Create Table: CREATE TABLE `timestamp_chk` (
  `chk_explicit_defaults_for_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1
[root@HOME001 data]#

この値をmy.cnfに追加してWarning対応。
explicit_defaults_for_timestamp=true

TIMESTAMP型を利用した時にテーブル属性が変わった事を確認

[root@HOME001 data]# vi /etc/my.cnf
[root@HOME001 data]# /etc/init.d/mysql restart
Shutting down MySQL..                                      [  OK  ]
Starting MySQL.                                            [  OK  ]
[root@HOME001 data]# mysql -u root -p -e "show variables like 'explicit_defaults_for_timestamp'"
Enter password:
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| explicit_defaults_for_timestamp | ON    |
+---------------------------------+-------+
[root@HOME001 data]#


[root@HOME001 data]# mysql -u root -p -e "create table test.timestamp_chk2 (chk_explicit_defaults_for_timestamp timestamp) ENGINE=InnoDB;"
Enter password:
[root@HOME001 data]# mysql -u root -p -e "show create table test.timestamp_chk2\G"
Enter password:
*************************** 1. row ***************************
       Table: timestamp_chk2
Create Table: CREATE TABLE `timestamp_chk2` (
  `chk_explicit_defaults_for_timestamp` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
[root@HOME001 data]#

新旧を並べるとこんな感じになります。

[root@HOME001 data]# mysql -u root -p -e "show create table test.timestamp_chk\G"
Enter password:
*************************** 1. row ***************************
       Table: timestamp_chk
Create Table: CREATE TABLE `timestamp_chk` (
  `chk_explicit_defaults_for_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1
[root@HOME001 data]# mysql -u root -p -e "show create table test.timestamp_chk2\G"
Enter password:
*************************** 1. row ***************************
       Table: timestamp_chk2
Create Table: CREATE TABLE `timestamp_chk2` (
  `chk_explicit_defaults_for_timestamp` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
[root@HOME001 data]#

MYSQLを再起動しても、Warningが無い事を確認してとりあえず確認終了。


130505 16:55:24 mysqld_safe mysqld from pid file /usr/local/mysql/data/HOME001.localdomain.pid ended
130505 16:55:25 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data
2013-05-05 16:55:25 7031 [Note] Plugin ‘FEDERATED’ is disabled.
2013-05-05 16:55:25 7031 [Note] InnoDB: The InnoDB memory heap is disabled
2013-05-05 16:55:25 7031 [Note] InnoDB: Mutexes and rw_locks use InnoDB’s own implementation
2013-05-05 16:55:25 7031 [Note] InnoDB: Compressed tables use zlib 1.2.3
2013-05-05 16:55:25 7031 [Note] InnoDB: Using Linux native AIO
2013-05-05 16:55:25 7031 [Note] InnoDB: Not using CPU crc32 instructions
2013-05-05 16:55:25 7031 [Note] InnoDB: Initializing buffer pool, size = 32.0M
2013-05-05 16:55:25 7031 [Note] InnoDB: Completed initialization of buffer pool
2013-05-05 16:55:25 7031 [Note] InnoDB: Highest supported file format is Barracuda.
2013-05-05 16:55:25 7031 [Note] InnoDB: 128 rollback segment(s) are active.
2013-05-05 16:55:25 7031 [Note] InnoDB: Waiting for purge to start
2013-05-05 16:55:26 7031 [Note] InnoDB: 5.6.11 started; log sequence number 1747262
2013-05-05 16:55:26 7031 [Note] Semi-sync replication initialized for transactions.
2013-05-05 16:55:26 7031 [Note] Semi-sync replication enabled on the master.
2013-05-05 16:55:26 7031 [Note] Server hostname (bind-address): ‘*’; port: 3306
2013-05-05 16:55:26 7031 [Note] IPv6 is available.
2013-05-05 16:55:26 7031 [Note] – ‘::’ resolves to ‘::’;
2013-05-05 16:55:26 7031 [Note] Server socket created on IP: ‘::’.
2013-05-05 16:55:26 7031 [Note] Event Scheduler: Loaded 0 events
2013-05-05 16:55:26 7031 [Note] /usr/local/mysql/bin/mysqld: ready for connections.
Version: ‘5.6.11-log’ socket: ‘/tmp/mysql.sock’ port: 3306 MySQL Community Server (GPL)
2013-05-05 16:56:21 7031 [Note] Start semi-sync binlog_dump to slave (server_id: 2), pos(mysql-bin.000027, 120)


http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_explicit_defaults_for_timestamp


先日、スレーブをアップグレードしてみて特に問題無さそうでしたので、
マスターDBをアップグレードしてみました。

アップグレード前の状況
既存では5.5.29をマスターDBで利用している。


[root@HOME001 local]# ls -l
合計 48
drwxr-xr-x.  2 root  root  4096  1月 31 14:18 2013 bin
drwxr-xr-x.  2 root  root  4096  9月 23 20:47 2011 etc
drwxr-xr-x.  2 root  root  4096  9月 23 20:47 2011 games
drwxr-xr-x.  2 root  root  4096  9月 23 20:47 2011 include
drwxr-xr-x.  3 root  root  4096  1月 31 14:12 2013 lib
drwxr-xr-x.  2 root  root  4096  9月 23 20:47 2011 libexec
drwxr-xr-x.  3 root  root  4096 12月 30 16:07 2012 man
lrwxrwxrwx.  1 mysql mysql   27 12月 22 14:42 2012 mysql -> mysql-5.5.29-linux2.6-i686/
drwxrwxr-x. 13 mysql mysql 4096  1月 24 15:37 2013 mysql-5.5.29-linux2.6-i686
drwxr-xr-x.  4 mysql mysql 4096  1月 18 05:26 2013 percona-xtrabackup-2.0.5
drwxr-xr-x.  2 root  root  4096  9月 23 20:47 2011 sbin
drwxr-xr-x.  7 root  root  4096  1月  7 14:59 2013 share
drwxr-xr-x.  2 root  root  4096  9月 23 20:47 2011 src
drwxrwxrwt.  2 root  root    40  3月 24 12:22 2013 tmp
lrwxrwxrwx.  1 mysql mysql   24  1月 24 14:09 2013 xtrabackup -> percona-xtrabackup-2.0.5
[root@HOME001 local]#

MYSQL5.6をダウンロードして展開。


[root@HOME001 local]# ls -l
合計 52
drwxr-xr-x.  2 root  root  4096  1月 31 14:18 2013 bin
drwxr-xr-x.  2 root  root  4096  9月 23 20:47 2011 etc
drwxr-xr-x.  2 root  root  4096  9月 23 20:47 2011 games
drwxr-xr-x.  2 root  root  4096  9月 23 20:47 2011 include
drwxr-xr-x.  3 root  root  4096  1月 31 14:12 2013 lib
drwxr-xr-x.  2 root  root  4096  9月 23 20:47 2011 libexec
drwxr-xr-x.  3 root  root  4096 12月 30 16:07 2012 man
lrwxrwxrwx.  1 mysql mysql   27 12月 22 14:42 2012 mysql -> mysql-5.5.29-linux2.6-i686/
drwxrwxr-x. 13 mysql mysql 4096  1月 24 15:37 2013 mysql-5.5.29-linux2.6-i686
drwxr-xr-x. 13 root  root  4096  3月 24 13:09 2013 mysql-5.6.10-linux-glibc2.5-i686
drwxr-xr-x.  4 mysql mysql 4096  1月 18 05:26 2013 percona-xtrabackup-2.0.5
drwxr-xr-x.  2 root  root  4096  9月 23 20:47 2011 sbin
drwxr-xr-x.  7 root  root  4096  1月  7 14:59 2013 share
drwxr-xr-x.  2 root  root  4096  3月 24 13:10 2013 src
drwxrwxrwt.  2 root  root    40  3月 24 12:22 2013 tmp
lrwxrwxrwx.  1 mysql mysql   24  1月 24 14:09 2013 xtrabackup -> percona-xtrabackup-2.0.5
[root@HOME001 local]#

既存DBを停止してアップグレード作業開始

1) STOP MYSQL
2) Remove Symbolic Link
3) Copy Data from Old MySQL to New SQL
4) Set Authentication
6) Recreate Symbolic Link with MySQL5.6


[root@HOME001 local]# /etc/init.d/mysql stop
Shutting down MySQL...                                    [  OK  ]
[root@HOME001 local]# 

[root@HOME001 local]# rm mysql
rm: remove シンボリックリンク `mysql'? y
[root@HOME001 local]#

[root@HOME001 local]# cp -rp /usr/local/mysql-5.5.29-linux2.6-i686/data/* /usr/local/mysql-5.6.10-linux-glibc2.5-i686/data/               
[root@HOME001 local]#

[root@HOME001 local]# chown -R mysql:mysql mysql-5.6.10-linux-glibc2.5-i686/
[root@HOME001 local]# chmod -R 755 mysql-5.6.10-linux-glibc2.5-i686/
[root@HOME001 local]#


[root@HOME001 local]# ln -s mysql-5.6.10-linux-glibc2.5-i686/ mysql
[root@HOME001 local]# ls -l
合計 52
drwxr-xr-x.  2 root  root  4096  1月 31 14:18 2013 bin
drwxr-xr-x.  2 root  root  4096  9月 23 20:47 2011 etc
drwxr-xr-x.  2 root  root  4096  9月 23 20:47 2011 games
drwxr-xr-x.  2 root  root  4096  9月 23 20:47 2011 include
drwxr-xr-x.  3 root  root  4096  1月 31 14:12 2013 lib
drwxr-xr-x.  2 root  root  4096  9月 23 20:47 2011 libexec
drwxr-xr-x.  3 root  root  4096 12月 30 16:07 2012 man
lrwxrwxrwx.  1 root  root    33  3月 24 13:18 2013 mysql -> mysql-5.6.10-linux-glibc2.5-i686/
drwxrwxr-x. 13 mysql mysql 4096  1月 24 15:37 2013 mysql-5.5.29-linux2.6-i686
drwxr-xr-x. 13 mysql mysql 4096  3月 24 13:09 2013 mysql-5.6.10-linux-glibc2.5-i686
drwxr-xr-x.  4 mysql mysql 4096  1月 18 05:26 2013 percona-xtrabackup-2.0.5
drwxr-xr-x.  2 root  root  4096  9月 23 20:47 2011 sbin
drwxr-xr-x.  7 root  root  4096  1月  7 14:59 2013 share
drwxr-xr-x.  2 root  root  4096  3月 24 13:10 2013 src
drwxrwxrwt.  2 root  root    40  3月 24 12:22 2013 tmp
lrwxrwxrwx.  1 mysql mysql   24  1月 24 14:09 2013 xtrabackup -> percona-xtrabackup-2.0.5
[root@HOME001 local]#

準備が出来たので、MYSQLを起動してシステムDBをアップグレードします。
この段階では、旧バージョンで利用していたmy.confは編集していません。


[root@HOME001 bin]# /etc/init.d/mysql start
Starting MySQL..                                           [  OK  ]
[root@HOME001 bin]# ./mysql_upgrade -u root -p
Enter password:
Looking for 'mysql' as: ./mysql
Looking for 'mysqlcheck' as: ./mysqlcheck
Running 'mysqlcheck' with connection arguments: '--port=3306' '--socket=/tmp/mysql.sock'
Warning: Using a password on the command line interface can be insecure.
Running 'mysqlcheck' with connection arguments: '--port=3306' '--socket=/tmp/mysql.sock'
Warning: Using a password on the command line interface can be insecure.
mysql.columns_priv                                 OK
mysql.db                                           OK
mysql.event                                        OK
mysql.func                                         OK
mysql.general_log                                  OK
mysql.help_category                                OK
mysql.help_keyword                                 OK
mysql.help_relation                                OK
mysql.help_topic                                   OK
mysql.host                                         OK
mysql.ndb_binlog_index                             OK
mysql.plugin                                       OK
mysql.proc                                         OK
mysql.procs_priv                                   OK
mysql.proxies_priv                                 OK
mysql.servers                                      OK
mysql.slow_log                                     OK
mysql.tables_priv                                  OK
mysql.time_zone                                    OK
mysql.time_zone_leap_second                        OK
mysql.time_zone_name                               OK
mysql.time_zone_transition                         OK
mysql.time_zone_transition_type                    OK
mysql.user                                         OK
test.TABLE001                                      OK
Running 'mysql_fix_privilege_tables'...
Warning: Using a password on the command line interface can be insecure.
OK
[root@HOME001 bin]#

アップグレードは終わりましたが、念の為再起動してログを確認。
以下のオプションだけ後日確認。

explicit_defaults_for_timestamp
innodb_additional_mem_pool_size
innodb_use_sys_malloc

[root@HOME001 bin]# /etc/init.d/mysql restart
Shutting down MySQL...                                     [  OK  ]
Starting MySQL..                                           [  OK  ]
[root@HOME001 bin]#




130324 13:21:15 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data
2013-03-24 13:21:15 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2013-03-24 13:21:15 5061 [Note] Plugin 'FEDERATED' is disabled.
2013-03-24 13:21:15 b789e8e0 InnoDB: Warning: Using innodb_additional_mem_pool_size is DEPRECATED. This option may be removed in future releases, together with the option innodb_use_sys_malloc and with the InnoDB's internal memory allocator.
2013-03-24 13:21:15 5061 [Note] InnoDB: The InnoDB memory heap is disabled
2013-03-24 13:21:15 5061 [Note] InnoDB: Mutexes and rw_locks use InnoDB's own implementation
2013-03-24 13:21:15 5061 [Note] InnoDB: Compressed tables use zlib 1.2.3
2013-03-24 13:21:15 5061 [Note] InnoDB: CPU does not support crc32 instructions
2013-03-24 13:21:15 5061 [Note] InnoDB: Using Linux native AIO
2013-03-24 13:21:15 5061 [Note] InnoDB: Initializing buffer pool, size = 32.0M
2013-03-24 13:21:15 5061 [Note] InnoDB: Completed initialization of buffer pool
2013-03-24 13:21:15 5061 [Note] InnoDB: Highest supported file format is Barracuda.
2013-03-24 13:21:16 5061 [Note] InnoDB: 128 rollback segment(s) are active.
2013-03-24 13:21:16 5061 [Note] InnoDB: Waiting for purge to start
2013-03-24 13:21:16 5061 [Note] InnoDB: 1.2.10 started; log sequence number 1733578
2013-03-24 13:21:16 5061 [Note] Semi-sync replication initialized for transactions.
2013-03-24 13:21:16 5061 [Note] Semi-sync replication enabled on the master.
2013-03-24 13:21:16 5061 [Note] Server hostname (bind-address): '*'; port: 3306
2013-03-24 13:21:16 5061 [Note] IPv6 is available.
2013-03-24 13:21:16 5061 [Note]   - '::' resolves to '::';
2013-03-24 13:21:16 5061 [Note] Server socket created on IP: '::'.
2013-03-24 13:21:16 5061 [Note] Event Scheduler: Loaded 0 events
2013-03-24 13:21:16 5061 [Note] /usr/local/mysql/bin/mysqld: ready for connections.
Version: '5.6.10-log'  socket: '/tmp/mysql.sock'  port: 3306  MySQL Community Server (GPL)
[root@HOME001 data]#


バージョンアップ結果確認


[root@HOME001 data]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.10-log MySQL Community Server (GPL)

Copyright (c) 2000, 2012, 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;' or '\h' for help. Type '\c' to clear the current input statement.

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

mysql>

MYSQL5610-master

その他レプリケーションの状態など。

マスターアップグレード前にSlaveを念の為、停止しておきました。
マスターアップグレード中なので書き込みも発生しないし、
スレーブは参照だけ出来ていれば良いかと。
但し、コマースサイトなどで書き込み処理も停止出来ない場合は、
他のスレーブをマスターにアップグレードする方法でも良いかと。
但し、スペックはますてーとして十分なシステムを選択する必要あり。


mysql> stop slave;
Query OK, 0 rows affected (0.08 sec)
mysql>

アップグレード後にスレーブ再開して問題無い事を確認済み。

mysql> start slave;
Query OK, 0 rows affected (0.21 sec)

mysql>


mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: home001
                  Master_User: slave_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000020
          Read_Master_Log_Pos: 120
               Relay_Log_File: relay-bin.000007
                Relay_Log_Pos: 283
        Relay_Master_Log_File: mysql-bin.000020
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB: mysql,performance_schema
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 120
              Relay_Log_Space: 609
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
                  Master_UUID: 2516bf0a-943a-11e2-8417-00123fd6c9fa
             Master_Info_File: /usr/local/mysql-5.6.10-linux-glibc2.5-i686/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
1 row in set (0.00 sec)

mysql>
 

マスターでデータをINSERTして確認


mysql> select @@hostname;
+---------------------+
| @@hostname          |
+---------------------+
| HOME001.localdomain |
+---------------------+
1 row in set (0.00 sec)

mysql>


mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000020 |      120 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql>


mysql> insert into TABLE001(title,comment) values('after upgrade master','Upgrade to  5.6.10-log and Check Replication');
Query OK, 1 row affected (0.45 sec)

mysql>

スレーブ側にてデータ確認


mysql> select @@hostname;
+---------------------+
| @@hostname          |
+---------------------+
| HOME002.localdomain |
+---------------------+
1 row in set (0.00 sec)

mysql>

mysql> select * from TABLE001;
+----+----------------------+----------------------------------------------+
| id | title                | comment                                      |
+----+----------------------+----------------------------------------------+
|  1 | Before upgrade       | MYSQL 5.6 - 0                                |
|  2 | Before upgrade       | MYSQL 5.6 - 1                                |
|  3 | Before upgrade       | MYSQL 5.6 - 2                                |
|  4 | After Slave upgrade  | MYSQL 5.6 - 0                                |
|  5 | After Slave upgrade  | MYSQL 5.6 - 1                                |
|  6 | reset replication    | Execute Change Master                        |
|  7 | reset replication    | Execute Change Master2                       |
|  8 | After Change Master  | Log is deleted by purge in my.conf           |
|  9 | after upgrade master | Upgrade to  5.6.10-log and Check Replication |
+----+----------------------+----------------------------------------------+
9 rows in set (0.23 sec)

mysql>

Slaveも再起動してみてログを確認


[root@HOME002 data]# /etc/init.d/mysql restart
Shutting down MySQL..                                      [  OK  ]
Starting MySQL...                                          [  OK  ]
[root@HOME002 data]#


[root@HOME002 data]# cat HOME002.localdomain.err
130324 14:22:23 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data
2013-03-24 14:22:24 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2013-03-24 14:22:24 13058 [Note] Plugin 'FEDERATED' is disabled.
2013-03-24 14:22:24 b7f596d0 InnoDB: Warning: Using innodb_additional_mem_pool_size is DEPRECATED. This option may be removed in future releases, together with the option innodb_use_sys_malloc and with the InnoDB's internal memory allocator.
2013-03-24 14:22:24 13058 [Note] InnoDB: The InnoDB memory heap is disabled
2013-03-24 14:22:24 13058 [Note] InnoDB: Mutexes and rw_locks use InnoDB's own implementation
2013-03-24 14:22:24 13058 [Note] InnoDB: Compressed tables use zlib 1.2.3
2013-03-24 14:22:24 13058 [Note] InnoDB: CPU does not support crc32 instructions
2013-03-24 14:22:24 13058 [Note] InnoDB: Using Linux native AIO
2013-03-24 14:22:24 13058 [Note] InnoDB: Initializing buffer pool, size = 32.0M
2013-03-24 14:22:24 13058 [Note] InnoDB: Completed initialization of buffer pool
2013-03-24 14:22:24 13058 [Note] InnoDB: Highest supported file format is Barracuda.
2013-03-24 14:22:25 13058 [Note] InnoDB: 128 rollback segment(s) are active.
2013-03-24 14:22:25 13058 [Note] InnoDB: Waiting for purge to start
2013-03-24 14:22:25 13058 [Note] InnoDB: 1.2.10 started; log sequence number 1734793
2013-03-24 14:22:26 13058 [Note] Server hostname (bind-address): '*'; port: 3306
2013-03-24 14:22:26 13058 [Note] IPv6 is available.
2013-03-24 14:22:26 13058 [Note]   - '::' resolves to '::';
2013-03-24 14:22:26 13058 [Note] Server socket created on IP: '::'.
2013-03-24 14:22:26 13058 [Note] Slave I/O thread: Start semi-sync replication to master 'slave_user@home001:3306' in log 'mysql-bin.000020' at position 449
2013-03-24 14:22:26 13058 [Warning] Storing MySQL user name or password information in the master.info repository is not secure and is therefore not recommended. Please see the MySQL Manual for more about this issue and possible alternatives.
2013-03-24 14:22:26 13058 [Note] Slave I/O thread: connected to master 'slave_user@home001:3306',replication started in log 'mysql-bin.000020' at position 449
2013-03-24 14:22:26 13058 [Warning] Slave SQL: If a crash happens this configuration does not guarantee that the relay log info will be consistent, Error_code: 0
2013-03-24 14:22:27 13058 [Note] Event Scheduler: Loaded 0 events
2013-03-24 14:22:27 13058 [Note] /usr/local/mysql/bin/mysqld: ready for connections.
Version: '5.6.10-log'  socket: '/tmp/mysql.sock'  port: 3306  MySQL Community Server (GPL)
2013-03-24 14:22:27 13058 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.000020' at position 449, relay log '/usr/local/mysql/data/relay-bin.000007' position: 612
[root@HOME002 data]#

参考サイト
2.11.1.1. Upgrading from MySQL 5.5 to 5.6


MYSQL5.6がリリースされてから2週間経過して色々と検証してくださる方がいて、
MYSQL5.6からはPerformance_SchemaがDefaultになっているので少しオーバーヘッドが高いのでは?
などVariableの値が増えているなど色々と出てきました。

Review of MySQL 5.6 Defaults Changes
MySQL 5.5 and 5.6 default variable values differences
Is MySQL 5.6 slower than MySQL 5.5 ?
Performance Schema overhead
MySQL5.6で増えたexplicit_defaults_for_timestamp

Performance検証は今後時間をとって行いますが、先ずはVariableの変更部分だけ調べてみました。

variableの値は”331 rows in set”が返ってくる。

mysql> select @@hostname;
+---------------------+
| @@hostname          |
+---------------------+
| HOME001.localdomain |
+---------------------+
1 row in set (0.00 sec)

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

[root@HOME001 ~]# mysql -u root -e "show variables" -p |  awk '{ print $1}' > /home/mysql/5.5.29.txt
Enter password:
[root@HOME001 ~]#

variableの値は”435 rows in set”が返ってくる。

mysql> select @@hostname;
+---------------------+
| @@hostname          |
+---------------------+
| HOME002.localdomain |
+---------------------+
1 row in set (0.00 sec)

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

mysql>

[root@HOME002 ~]# mysql -u root -e "show variables" -p |  awk '{ print $1}' > 5.6.10.txt
Enter password:
[root@HOME002 ~]#

上記で取得した値をdiffコマンドで、増加・変更のあった値を調査してみる。

[root@HOME002 mysql]# diff -y -y --suppress-common-lines 5.5.29.txt 5.6.10.txt
                                                              > bind_address
                                                              > binlog_checksum
                                                              > binlog_max_flush_queue_time
                                                              > binlog_order_commits
                                                              > binlog_row_image
                                                              > binlog_rows_query_log_events
                                                              > core_file
                                                              > default_tmp_storage_engine
                                                              > disconnect_on_expired_password
engine_condition_pushdown                                     | end_markers_in_json
                                                              > enforce_gtid_consistency
                                                              > eq_range_index_dive_limit
                                                              > explicit_defaults_for_timestamp
                                                              > gtid_executed
                                                              > gtid_mode
                                                              > gtid_next
                                                              > gtid_owned
                                                              > gtid_purged
have_csv                                                      <
have_innodb                                                   <
have_ndbcluster                                               <
have_partitioning                                             <
                                                              > host_cache_size
                                                              > ignore_db_dirs
                                                              > innodb_adaptive_flushing_lwm
                                                              > innodb_api_bk_commit_interval
                                                              > innodb_api_disable_rowlock
                                                              > innodb_api_enable_binlog
                                                              > innodb_api_enable_mdl
                                                              > innodb_api_trx_level
                                                              > innodb_buffer_pool_dump_at_shutdown
                                                              > innodb_buffer_pool_dump_now
                                                              > innodb_buffer_pool_filename
                                                              > innodb_buffer_pool_load_abort
                                                              > innodb_buffer_pool_load_at_startup
                                                              > innodb_buffer_pool_load_now
                                                              > innodb_change_buffer_max_size
                                                              > innodb_checksum_algorithm
                                                              > innodb_cmp_per_index_enabled
                                                              > innodb_compression_failure_threshold_pct
                                                              > innodb_compression_level
                                                              > innodb_compression_pad_pct_max
                                                              > innodb_disable_sort_file_cache
                                                              > innodb_flush_log_at_timeout
                                                              > innodb_flush_neighbors
                                                              > innodb_flushing_avg_loops
                                                              > innodb_ft_aux_table
                                                              > innodb_ft_cache_size
                                                              > innodb_ft_enable_diag_print
                                                              > innodb_ft_enable_stopword
                                                              > innodb_ft_max_token_size
                                                              > innodb_ft_min_token_size
                                                              > innodb_ft_num_word_optimize
                                                              > innodb_ft_server_stopword_table
                                                              > innodb_ft_sort_pll_degree
                                                              > innodb_ft_user_stopword_table
                                                              > innodb_io_capacity_max
                                                              > innodb_lru_scan_depth
                                                              > innodb_max_dirty_pages_pct_lwm
                                                              > innodb_max_purge_lag_delay
                                                              > innodb_monitor_disable
                                                              > innodb_monitor_enable
                                                              > innodb_monitor_reset
                                                              > innodb_monitor_reset_all
                                                              > innodb_online_alter_log_max_size
                                                              > innodb_optimize_fulltext_only
                                                              > innodb_page_size
                                                              > innodb_print_all_deadlocks
                                                              > innodb_read_only
                                                              > innodb_sort_buffer_size
                                                              > innodb_stats_auto_recalc
                                                              > innodb_stats_persistent
                                                              > innodb_stats_persistent_sample_pages
                                                              > innodb_stats_transient_sample_pages
                                                              > innodb_sync_array_size
                                                              > innodb_undo_directory
                                                              > innodb_undo_logs
                                                              > innodb_undo_tablespaces
log                                                           <
                                                              > log_bin_basename
                                                              > log_bin_index
                                                              > log_bin_use_v1_row_events
log_slow_queries                                              | log_throttle_queries_not_using_indexes
                                                              > master_info_repository
                                                              > master_verify_checksum
max_long_data_size                                            <
                                                              > metadata_locks_hash_instances
                                                              > optimizer_trace
                                                              > optimizer_trace_features
                                                              > optimizer_trace_limit
                                                              > optimizer_trace_max_mem_size
                                                              > optimizer_trace_offset
                                                              > performance_schema_accounts_size
                                                              > performance_schema_digests_size
                                                              > performance_schema_events_stages_history_long_size
                                                              > performance_schema_events_stages_history_size
                                                              > performance_schema_events_statements_history_long_size
                                                              > performance_schema_events_statements_history_size
                                                              > performance_schema_hosts_size
                                                              > performance_schema_max_socket_classes
                                                              > performance_schema_max_socket_instances
                                                              > performance_schema_max_stage_classes
                                                              > performance_schema_max_statement_classes
                                                              > performance_schema_session_connect_attrs_size
                                                              > performance_schema_setup_actors_size
                                                              > performance_schema_setup_objects_size
                                                              > performance_schema_users_size
                                                              > pseudo_slave_mode
                                                              > relay_log_basename
                                                              > relay_log_info_repository
rpl_recovery_rank                                             | rpl_semi_sync_slave_enabled
rpl_semi_sync_master_enabled                                  | rpl_semi_sync_slave_trace_level
rpl_semi_sync_master_timeout                                  <
rpl_semi_sync_master_trace_level                              <
rpl_semi_sync_master_wait_no_slave                            <
                                                              > server_id_bits
                                                              > server_uuid
                                                              > slave_allow_batching
                                                              > slave_checkpoint_group
                                                              > slave_checkpoint_period
                                                              > slave_parallel_workers
                                                              > slave_pending_jobs_size_max
                                                              > slave_rows_search_algorithms
                                                              > slave_sql_verify_checksum
sql_big_tables                                                <
sql_low_priority_updates                                      <
sql_max_join_size                                             <
                                                              > ssl_crl
                                                              > ssl_crlpath
                                                              > table_open_cache_instances
                                                              > transaction_allow_batching
                                                              > tx_read_only
[root@HOME002 mysql]#

variables


■MYSQLのセカンドノードを同じホストで立ち上げる■
※ mysqld_multi — 複数のMySQL サーバ管理とは違うやり方です。
単純にPortとデータディレクトリーを変更してMySQLインスタンスを立ち上げています。

前回の検証
複数MYSQLを設定

■インストール■

tarを解凍し/usr/local/mysql-5.6.7-rc-instance2としてフォルダー移動して、
mysql2としてシンボリックリンクを作成。

[root@192.168.11.36 local]# ln -s mysql-5.6.7-rc-instance2/ mysql2
[root@192.168.11.36 local]# ls -l
total 48
drwxr-xr-x  2 root   root   4096 Jun 11 15:42 bin
drwxr-xr-x  2 root   root   4096 Dec  7  2011 etc
drwxr-xr-x  2 root   root   4096 May 11  2011 games
drwxr-xr-x  2 root   root   4096 May 11  2011 include
drwxr-xr-x  2 root   root   4096 May 11  2011 lib
drwxr-xr-x  2 root   root   4096 May 11  2011 libexec
lrwxrwxrwx  1 root   root     29 Nov  2 18:15 mysql -> mysql-5.6.7-rc-linux2.6-i686/
lrwxrwxrwx  1 root   root     25 Nov  7 13:20 mysql2 -> mysql-5.6.7-rc-instance2/
drwxr-xr-x 13 root   root   4096 Nov  7 13:18 mysql-5.6.7-rc-instance2
drwxr-xr-x 13 mysql  mysql  4096 Nov  2 18:15 mysql-5.6.7-rc-linux2.6-i686
drwxrwxr-x  9 nagios nagios 4096 Aug  1 14:54 nagios
drwxr-xr-x  2 root   root   4096 May 11  2011 sbin
drwxr-xr-x  7 root   root   4096 Jun 11 15:42 share
drwxr-xr-x  2 root   root   4096 Nov  7 13:19 src
[root@192.168.11.36 local]# chown -R mysql:mysql mysql-5.6.7-rc-instance2/

■システムデータベースの作成■
※ –no-defaultsオプションを指定してOptionファイルが読まれないようにする。

[root@192.168.11.36 mysql2]# ./scripts/mysql_install_db --basedir=/usr/local/mysql2 --datadir=/usr/local/mysql2/data --user=mysql --socket=/tmp/mysqld2.sock --no-defaults
Installing MySQL system tables...
121107 13:22:01 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
121107 13:22:01 InnoDB: The InnoDB memory heap is disabled
121107 13:22:01 InnoDB: Mutexes and rw_locks use InnoDB's own implementation
121107 13:22:01 InnoDB: Compressed tables use zlib 1.2.3
121107 13:22:01 InnoDB: Using Linux native AIO
121107 13:22:01 InnoDB: CPU does not support crc32 instructions
121107 13:22:01 InnoDB: Error: Linux Native AIO is not supported on tmpdir.
InnoDB: You can either move tmpdir to a file system that supports native AIO
InnoDB: or you can set innodb_use_native_aio to FALSE to avoid this message.
121107 13:22:01 InnoDB: Error: Linux Native AIO check on tmpdir returned error[22]
121107 13:22:01 InnoDB: Warning: Linux Native AIO disabled.
121107 13:22:01 InnoDB: Initializing buffer pool, size = 128.0M
121107 13:22:01 InnoDB: Completed initialization of buffer pool
InnoDB: The first specified data file ./ibdata1 did not exist:
InnoDB: a new database to be created!
121107 13:22:01 InnoDB: Setting file ./ibdata1 size to 12 MB
InnoDB: Database physically writes the file full: wait...
121107 13:22:01 InnoDB: Log file ./ib_logfile0 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile0 size to 5 MB
InnoDB: Database physically writes the file full: wait...
121107 13:22:02 InnoDB: Log file ./ib_logfile1 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile1 size to 5 MB
InnoDB: Database physically writes the file full: wait...
121107 13:22:02 InnoDB: Doublewrite buffer not found: creating new
121107 13:22:02 InnoDB: Doublewrite buffer created
121107 13:22:02 InnoDB: 128 rollback segment(s) are active.
121107 13:22:02 [Warning] InnoDB: Creating foreign key constraint system tables.
121107 13:22:02 [Note] InnoDB: Foreign key constraint system tables created
121107 13:22:02 [Note] InnoDB: Creating tablespace and datafile system tables.
121107 13:22:02 [Note] InnoDB: Tablespace and datafile system tables created
121107 13:22:02 InnoDB: Waiting for the background threads to start
121107 13:22:02 InnoDB: 1.2.7 started; log sequence number 0
121107 13:22:02 [Note] Binlog end
121107 13:22:02 InnoDB: FTS optimize thread exiting.
121107 13:22:02 InnoDB: Starting shutdown...
121107 13:22:03 InnoDB: Shutdown completed; log sequence number 1629432
OK
Filling help tables...
121107 13:22:03 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
121107 13:22:03 InnoDB: The InnoDB memory heap is disabled
121107 13:22:03 InnoDB: Mutexes and rw_locks use InnoDB's own implementation
121107 13:22:03 InnoDB: Compressed tables use zlib 1.2.3
121107 13:22:03 InnoDB: Using Linux native AIO
121107 13:22:03 InnoDB: CPU does not support crc32 instructions
121107 13:22:03 InnoDB: Error: Linux Native AIO is not supported on tmpdir.
InnoDB: You can either move tmpdir to a file system that supports native AIO
InnoDB: or you can set innodb_use_native_aio to FALSE to avoid this message.
121107 13:22:03 InnoDB: Error: Linux Native AIO check on tmpdir returned error[22]
121107 13:22:03 InnoDB: Warning: Linux Native AIO disabled.
121107 13:22:03 InnoDB: Initializing buffer pool, size = 128.0M
121107 13:22:03 InnoDB: Completed initialization of buffer pool
121107 13:22:03 InnoDB: highest supported file format is Barracuda.
121107 13:22:03 InnoDB: 128 rollback segment(s) are active.
121107 13:22:03 InnoDB: 1.2.7 started; log sequence number 1629432
121107 13:22:03 [Note] Binlog end
121107 13:22:03 InnoDB: FTS optimize thread exiting.
121107 13:22:03 InnoDB: Starting shutdown...
121107 13:22:05 InnoDB: Shutdown completed; log sequence number 1629442
OK

To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system
PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:
/usr/local/mysql2/bin/mysqladmin -u root password 'new-password'
/usr/local/mysql2/bin/mysqladmin -u root -h 192.168.11.36.variable.jp password 'new-password'
Alternatively you can run:
/usr/local/mysql2/bin/mysql_secure_installation
which will also give you the option of removing the test
databases and anonymous user created by default.  This is
strongly recommended for production servers.
See the manual for more instructions.
You can start the MySQL daemon with:
cd /usr/local/mysql2 ; /usr/local/mysql2/bin/mysqld_safe &
You can test the MySQL daemon with mysql-test-run.pl
cd /usr/local/mysql2/mysql-test ; perl mysql-test-run.pl
Please report any problems with the /usr/local/mysql2/scripts/mysqlbug script!

[root@192.168.11.36 mysql2]#

■セカンドインスタンスの起動■

[root@192.168.11.36 mysql2]# /usr/local/mysql2/bin/mysqld_safe --no-defaults --basedir=/usr/local/mysql2 --datadir=/usr/local/mysql2/data --user=mysql --socket=/tmp/mysqld2.sock --port=3308 &
[1] 27588
[root@192.168.11.36 mysql2]# 121107 13:22:43 mysqld_safe Logging to '/usr/local/mysql2/data/192.168.11.36.variable.jp.err'.
121107 13:22:43 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql2/data
[root@192.168.11.36 mysql2]#

■セカンドインスタンスへのログインとアカウント設定■
※ Socketの指定してログイン

[root@192.168.11.36 mysql2]# mysql --socket=/tmp/mysqld2.sock -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.7-rc MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

mysql> GRANT ALL PRIVILEGES  ON *.* TO root@localhost IDENTIFIED BY 'AWS-PASS-sQL2012' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL PRIVILEGES  ON *.* TO root@127.0.0.1 IDENTIFIED BY 'AWS-PASS-sQL2012' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL PRIVILEGES  ON *.* TO root@`192.168.11.36.variable.jp` IDENTIFIED BY 'AWS-PASS-sQL2012' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)

mysql>


mysql> show variables like 'port%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port          | 3308  |
+---------------+-------+
1 row in set (0.00 sec)

mysql>

■起動状態確認■
※2つインスタンスが立ち上がっている事が確認出来る。

[root@192.168.11.36 local]# netstat -an | egrep '3306|3308'
tcp        0      0 :::3306                     :::*                        LISTEN
tcp        0      0 :::3308                     :::*                        LISTEN
[root@192.168.11.36 local]#


[root@192.168.11.36 local]# netstat -an | grep '3306\|3308'
tcp        0      0 :::3306                     :::*                        LISTEN
tcp        0      0 :::3308                     :::*                        LISTEN
[root@192.168.11.36 local]#

■Optionファイル指定■
2ndインスタンスのMySQLをKillして停止してから、
Socketやポートを指定してあるOptionファイルを指定してMYSQLを起動してみる。

[root@192.168.11.36 mysql2]# /usr/local/mysql2/bin/mysqld_safe --defaults-file=/etc/my2.cnf --basedir=/usr/local/mysql2 --datadir=/usr/local/mysql2/data --user=mysql --socket=/tmp/mysqld2.sock &
[1] 3320
[root@192.168.11.36 mysql2]# 121107 15:39:11 mysqld_safe Logging to '/usr/local/mysql2/data/192.168.11.36.variable.jp.err'.
121107 15:39:11 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql2/data
[root@192.168.11.36 mysql2]#


■ログインし動作確認■

※1st,2nd共にログイン出来るか確認。

[root@192.168.11.36 mysql2]# mysql --socket=/tmp/mysqld2.sock -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.7-rc MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show variables like 'port%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port          | 3308  |
+---------------+-------+
1 row in set (0.00 sec)

mysql> exit
Bye

[root@192.168.11.36 mysql2]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.7-rc-log MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show variables like 'port%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port          | 3306  |
+---------------+-------+
1 row in set (0.00 sec)

mysql>

■ログファイルサイズ変更■
追加なので、マルチインスタンスとは関係無いです。

[root@192.168.11.36 mysql2]# vi /etc/my2.cnf
[root@192.168.11.36 mysql2]# cd data/
[root@192.168.11.36 data]# ls -l
total 22648
-rw-rw---- 1 mysql root     60642 Nov  7 15:43 192.168.11.36.variable.jp.err
-rw-rw---- 1 mysql mysql       56 Nov  7 13:25 auto.cnf
-rw-rw---- 1 mysql mysql 12582912 Nov  7 15:43 ibdata1
-rw-rw---- 1 mysql mysql  5242880 Nov  7 15:43 ib_logfile0
-rw-rw---- 1 mysql mysql  5242880 Nov  7 13:22 ib_logfile1
drwxr-xr-x 2 mysql mysql     4096 Nov  7 13:22 mysql
-rw-rw---- 1 mysql mysql        0 Nov  7 13:22 mysql-bin.index
drwx------ 2 mysql mysql     4096 Nov  7 13:22 performance_schema
drwxr-xr-x 2 mysql mysql     4096 Nov  7 13:18 test
[root@192.168.11.36 data]# mv ib_logfile0 ib_logfile0.bk
[root@192.168.11.36 data]# mv ib_logfile1 ib_logfile1.bk
[root@192.168.11.36 data]# /usr/local/mysql2/bin/mysqld_safe --defaults-file=/etc/my2.cnf --basedir=/usr/local/mysql2 --datadir=/usr/local/mysql2/data --user=mysql --socket=/tmp/mysqld2.sock &
[1] 3960
[root@192.168.11.36 data]# 121107 15:44:53 mysqld_safe Logging to '/usr/local/mysql2/data/192.168.11.36.variable.jp.err'.
121107 15:44:53 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql2/data
[root@192.168.11.36 data]# ls -lh
total 43M
-rw-rw---- 1 mysql root   63K Nov  7 15:44 192.168.11.36.variable.jp.err
-rw-rw---- 1 mysql mysql    5 Nov  7 15:44 192.168.11.36.variable.jp.pid
-rw-rw---- 1 mysql mysql   56 Nov  7 13:25 auto.cnf
-rw-rw---- 1 mysql mysql  12M Nov  7 15:44 ibdata1
-rw-rw---- 1 mysql mysql  10M Nov  7 15:44 ib_logfile0
-rw-rw---- 1 mysql mysql 5.0M Nov  7 15:43 ib_logfile0.bk
-rw-rw---- 1 mysql mysql  10M Nov  7 15:44 ib_logfile1
-rw-rw---- 1 mysql mysql 5.0M Nov  7 13:22 ib_logfile1.bk
drwxr-xr-x 2 mysql mysql 4.0K Nov  7 13:22 mysql
-rw-rw---- 1 mysql mysql    0 Nov  7 13:22 mysql-bin.index
drwx------ 2 mysql mysql 4.0K Nov  7 13:22 performance_schema
drwxr-xr-x 2 mysql mysql 4.0K Nov  7 13:18 test
[root@192.168.11.36 data]#