MySQL SYS Schema 5.7

by ⋅ Leave a Comment

MySQL WorkbenchからMySQL5.7に対して、SYSスキーマを作成した場合。
5.6の場合よりもViewの数が多い事が確認出来る。

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

Workbenchから作成する場合とScriptから作成した場合の差分は以下の4つのViewの様です。
innodb_lock_waits
memory_by_thread_by_current_bytes
memory_by_user_by_current_bytes
sys_config

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

root@localhost [sys]>select * from sys.memory_by_thread_by_current_bytes;
+-----------+---------------------------------+--------------------+-------------------+-------------------+-------------------+-----------------+
| thread_id | user                            | current_count_used | current_allocated | current_avg_alloc | current_max_alloc | total_allocated |
+-----------+---------------------------------+--------------------+-------------------+-------------------+-------------------+-----------------+
|         7 | innodb/io_write_thread          |                  0 | 0 bytes           | 0 bytes           | 0 bytes           | 0 bytes         |
|         8 | innodb/io_write_thread          |                  0 | 0 bytes           | 0 bytes           | 0 bytes           | 0 bytes         |
|         9 | innodb/io_write_thread          |                  0 | 0 bytes           | 0 bytes           | 0 bytes           | 0 bytes         |
|        10 | innodb/io_read_thread           |                  0 | 0 bytes           | 0 bytes           | 0 bytes           | 0 bytes         |
|        11 | innodb/io_ibuf_thread           |                  0 | 0 bytes           | 0 bytes           | 0 bytes           | 0 bytes         |
|        12 | innodb/io_log_thread            |                  0 | 0 bytes           | 0 bytes           | 0 bytes           | 0 bytes         |
|        13 | innodb/io_read_thread           |                  0 | 0 bytes           | 0 bytes           | 0 bytes           | 0 bytes         |
|        15 | innodb/srv_error_monitor_thread |                  0 | 0 bytes           | 0 bytes           | 0 bytes           | 0 bytes         |
|        16 | innodb/srv_monitor_thread       |                  0 | 0 bytes           | 0 bytes           | 0 bytes           | 0 bytes         |
|        17 | innodb/srv_master_thread        |                  0 | 0 bytes           | 0 bytes           | 0 bytes           | 0 bytes         |
|        18 | innodb/srv_lock_timeout_thread  |                  0 | 0 bytes           | 0 bytes           | 0 bytes           | 0 bytes         |
|        19 | innodb/srv_purge_thread         |                  0 | 0 bytes           | 0 bytes           | 0 bytes           | 0 bytes         |
|        20 | innodb/buf_dump_thread          |                  0 | 0 bytes           | 0 bytes           | 0 bytes           | 0 bytes         |
|        21 | innodb/dict_stats_thread        |                  0 | 0 bytes           | 0 bytes           | 0 bytes           | 0 bytes         |
|        22 | sql/signal_handler              |                  0 | 0 bytes           | 0 bytes           | 0 bytes           | 0 bytes         |
|        23 | root@localhost                  |                  0 | 0 bytes           | 0 bytes           | 0 bytes           | 0 bytes         |
|         1 | sql/main                        |                  0 | 0 bytes           | 0 bytes           | 0 bytes           | 0 bytes         |
|         2 | sql/thread_timer_notifier       |                  0 | 0 bytes           | 0 bytes           | 0 bytes           | 0 bytes         |
|         3 | innodb/page_cleaner_thread      |                  0 | 0 bytes           | 0 bytes           | 0 bytes           | 0 bytes         |
|         4 | innodb/io_write_thread          |                  0 | 0 bytes           | 0 bytes           | 0 bytes           | 0 bytes         |
|         5 | innodb/io_read_thread           |                  0 | 0 bytes           | 0 bytes           | 0 bytes           | 0 bytes         |
|         6 | innodb/io_read_thread           |                  0 | 0 bytes           | 0 bytes           | 0 bytes           | 0 bytes         |
+-----------+---------------------------------+--------------------+-------------------+-------------------+-------------------+-----------------+
22 rows in set (0.07 sec)

root@localhost [sys]>select * from sys.memory_by_user_by_current_bytes;
+------------+--------------------+-------------------+-------------------+-------------------+-----------------+
| user       | current_count_used | current_allocated | current_avg_alloc | current_max_alloc | total_allocated |
+------------+--------------------+-------------------+-------------------+-------------------+-----------------+
| root       |                  0 | 0 bytes           | 0 bytes           | 0 bytes           | 0 bytes         |
| background |                  0 | 0 bytes           | 0 bytes           | 0 bytes           | 0 bytes         |
+------------+--------------------+-------------------+-------------------+-------------------+-----------------+
2 rows in set (0.00 sec)

root@localhost [sys]>select * from sys.sys_config;
+------------------------+-------+---------------------+----------------+
| variable               | value | set_time            | set_by         |
+------------------------+-------+---------------------+----------------+
| statement_truncate_len | 64    | 2014-12-23 08:50:02 | root@localhost |
+------------------------+-------+---------------------+----------------+
1 row in set (0.00 sec)

root@localhost [sys]>

sys.innodb_lock_waitsに関しては、実際にLockを発生させてみると現在Lockになっている状況が確認出来ました。

lock-wait

root@localhost [sys]>select * from sys.innodb_lock_waits;
+----------------+----------------+------------------------------------------------------+-----------------+-------------------+-------------------+--------------------+--------------------+-----------------+-----------------+----------------+------------------+--------------------+--------------------+---------------------+---------------------+
| waiting_trx_id | waiting_thread | waiting_query                                        | waiting_lock_id | waiting_lock_mode | waiting_lock_type | waiting_lock_table | waiting_lock_index | blocking_trx_id | blocking_thread | blocking_query | blocking_lock_id | blocking_lock_mode | blocking_lock_type | blocking_lock_table | blocking_lock_index |
+----------------+----------------+------------------------------------------------------+-----------------+-------------------+-------------------+--------------------+--------------------+-----------------+-----------------+----------------+------------------+--------------------+--------------------+---------------------+---------------------+
| 42525          |              3 | update store set address_id = 101 where store_id = 2 | 42525:109:3:3   | X                 | RECORD            | `sakila`.`store`   | PRIMARY            | 42524           |               2 | NULL           | 42524:109:3:3    | X                  | RECORD             | `sakila`.`store`    | PRIMARY             |
+----------------+----------------+------------------------------------------------------+-----------------+-------------------+-------------------+--------------------+--------------------+-----------------+-----------------+----------------+------------------+--------------------+--------------------+---------------------+---------------------+
1 row in set (0.00 sec)

root@localhost [sys]>

WorkBenchから作成した場合


root@localhost [sys]>select @@version;
+---------------------+
| @@version           |
+---------------------+
| 5.7.5-labs-http-log |
+---------------------+
1 row in set (0.00 sec)

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

root@localhost [sys]>show tables;
+-----------------------------------------------+
| Tables_in_sys                                 |
+-----------------------------------------------+
| host_summary                                  |
| host_summary_by_file_io                       |
| host_summary_by_file_io_type                  |
| host_summary_by_stages                        |
| host_summary_by_statement_latency             |
| host_summary_by_statement_type                |
| innodb_buffer_stats_by_schema                 |
| innodb_buffer_stats_by_table                  |
| io_by_thread_by_latency                       |
| io_global_by_file_by_bytes                    |
| io_global_by_file_by_latency                  |
| io_global_by_wait_by_bytes                    |
| io_global_by_wait_by_latency                  |
| latest_file_io                                |
| memory_by_user_by_current_bytes               |
| memory_global_by_current_allocated            |
| memory_global_total                           |
| processlist                                   |
| ps_check_lost_instrumentation                 |
| schema_index_statistics                       |
| schema_object_overview                        |
| schema_table_statistics                       |
| schema_table_statistics_with_buffer           |
| schema_tables_with_full_table_scans           |
| schema_unused_indexes                         |
| statement_analysis                            |
| statements_with_errors_or_warnings            |
| statements_with_full_table_scans              |
| statements_with_runtimes_in_95th_percentile   |
| statements_with_sorting                       |
| statements_with_temp_tables                   |
| user_summary                                  |
| user_summary_by_file_io                       |
| user_summary_by_file_io_type                  |
| user_summary_by_stages                        |
| user_summary_by_statement_latency             |
| user_summary_by_statement_type                |
| version                                       |
| wait_classes_global_by_avg_latency            |
| wait_classes_global_by_latency                |
| waits_by_host_by_latency                      |
| waits_by_user_by_latency                      |
| waits_global_by_latency                       |
| x$host_summary                                |
| x$host_summary_by_file_io                     |
| x$host_summary_by_file_io_type                |
| x$host_summary_by_stages                      |
| x$host_summary_by_statement_latency           |
| x$host_summary_by_statement_type              |
| x$innodb_buffer_stats_by_schema               |
| x$innodb_buffer_stats_by_table                |
| x$io_by_thread_by_latency                     |
| x$io_global_by_file_by_bytes                  |
| x$io_global_by_file_by_latency                |
| x$io_global_by_wait_by_bytes                  |
| x$io_global_by_wait_by_latency                |
| x$latest_file_io                              |
| x$memory_by_user_by_current_bytes             |
| x$memory_global_by_current_allocated          |
| x$memory_global_total                         |
| x$processlist                                 |
| x$ps_digest_95th_percentile_by_avg_us         |
| x$ps_digest_avg_latency_distribution          |
| x$ps_schema_table_statistics_io               |
| x$schema_index_statistics                     |
| x$schema_table_statistics                     |
| x$schema_table_statistics_with_buffer         |
| x$statement_analysis                          |
| x$statements_with_errors_or_warnings          |
| x$statements_with_full_table_scans            |
| x$statements_with_runtimes_in_95th_percentile |
| x$statements_with_sorting                     |
| x$statements_with_temp_tables                 |
| x$user_summary                                |
| x$user_summary_by_file_io                     |
| x$user_summary_by_file_io_type                |
| x$user_summary_by_stages                      |
| x$user_summary_by_statement_latency           |
| x$user_summary_by_statement_type              |
| x$wait_classes_global_by_avg_latency          |
| x$wait_classes_global_by_latency              |
| x$waits_by_host_by_latency                    |
| x$waits_by_user_by_latency                    |
| x$waits_global_by_latency                     |
+-----------------------------------------------+
84 rows in set (0.01 sec)

root@localhost [sys]>

スクリプトをダウンロードして作成した場合

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

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

root@localhost [sys]>

root@localhost [sys]>show tables;
+-----------------------------------------------+
| Tables_in_sys                                 |
+-----------------------------------------------+
| host_summary                                  |
| host_summary_by_file_io                       |
| host_summary_by_file_io_type                  |
| host_summary_by_stages                        |
| host_summary_by_statement_latency             |
| host_summary_by_statement_type                |
| innodb_buffer_stats_by_schema                 |
| innodb_buffer_stats_by_table                  |
| innodb_lock_waits                             |
| io_by_thread_by_latency                       |
| io_global_by_file_by_bytes                    |
| io_global_by_file_by_latency                  |
| io_global_by_wait_by_bytes                    |
| io_global_by_wait_by_latency                  |
| latest_file_io                                |
| memory_by_host_by_current_bytes               |
| memory_by_thread_by_current_bytes             |
| memory_by_user_by_current_bytes               |
| memory_global_by_current_allocated            |
| memory_global_total                           |
| processlist                                   |
| ps_check_lost_instrumentation                 |
| schema_index_statistics                       |
| schema_object_overview                        |
| schema_table_statistics                       |
| schema_table_statistics_with_buffer           |
| schema_tables_with_full_table_scans           |
| schema_unused_indexes                         |
| statement_analysis                            |
| statements_with_errors_or_warnings            |
| statements_with_full_table_scans              |
| statements_with_runtimes_in_95th_percentile   |
| statements_with_sorting                       |
| statements_with_temp_tables                   |
| sys_config                                    |
| user_summary                                  |
| user_summary_by_file_io                       |
| user_summary_by_file_io_type                  |
| user_summary_by_stages                        |
| user_summary_by_statement_latency             |
| user_summary_by_statement_type                |
| version                                       |
| wait_classes_global_by_avg_latency            |
| wait_classes_global_by_latency                |
| waits_by_host_by_latency                      |
| waits_by_user_by_latency                      |
| waits_global_by_latency                       |
| x$host_summary                                |
| x$host_summary_by_file_io                     |
| x$host_summary_by_file_io_type                |
| x$host_summary_by_stages                      |
| x$host_summary_by_statement_latency           |
| x$host_summary_by_statement_type              |
| x$innodb_buffer_stats_by_schema               |
| x$innodb_buffer_stats_by_table                |
| x$innodb_lock_waits                           |
| x$io_by_thread_by_latency                     |
| x$io_global_by_file_by_bytes                  |
| x$io_global_by_file_by_latency                |
| x$io_global_by_wait_by_bytes                  |
| x$io_global_by_wait_by_latency                |
| x$latest_file_io                              |
| x$memory_by_host_by_current_bytes             |
| x$memory_by_thread_by_current_bytes           |
| x$memory_by_user_by_current_bytes             |
| x$memory_global_by_current_allocated          |
| x$memory_global_total                         |
| x$processlist                                 |
| x$ps_digest_95th_percentile_by_avg_us         |
| x$ps_digest_avg_latency_distribution          |
| x$ps_schema_table_statistics_io               |
| x$schema_index_statistics                     |
| x$schema_table_statistics                     |
| x$schema_table_statistics_with_buffer         |
| x$schema_tables_with_full_table_scans         |
| x$statement_analysis                          |
| x$statements_with_errors_or_warnings          |
| x$statements_with_full_table_scans            |
| x$statements_with_runtimes_in_95th_percentile |
| x$statements_with_sorting                     |
| x$statements_with_temp_tables                 |
| x$user_summary                                |
| x$user_summary_by_file_io                     |
| x$user_summary_by_file_io_type                |
| x$user_summary_by_stages                      |
| x$user_summary_by_statement_latency           |
| x$user_summary_by_statement_type              |
| x$wait_classes_global_by_avg_latency          |
| x$wait_classes_global_by_latency              |
| x$waits_by_host_by_latency                    |
| x$waits_by_user_by_latency                    |
| x$waits_global_by_latency                     |
+-----------------------------------------------+
92 rows in set (0.00 sec)

root@localhost [sys]>

MySQL SYS Schema 5.6

by ⋅ Leave a Comment

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

MYSQL VIEW (CHECK OPTION)

by ⋅ Leave a Comment

MYSQLのCHECK OPTION

それに対するselect_statement中のWHERE節が真実であるものを除く行に、
更新不能なビューが挿入されるか、当該行が更新されるのを回避するため、
WITH CHECK OPTION節を附与することができます。
更新可能なビューに対するWITH CHECK OPTION節に基づき、
LOCALとCASCADEDキーワードはビューが他のビューに対して定義される場合、
チェックテストの範囲を決めます｡
定義されているビューだけに対して、LOCALキーワードはCHECK OPTIONを制限します。
CASCADEDは同様に基礎ビューを評価するチェックを起動させます。キーワードが附与されない場合、
デフォルト設定はCASCADEDとなります。

CREATE TABLE t1 (a INT);
CREATE VIEW view_1 AS SELECT * FROM t1 WHERE a < 2 WITH CHECK OPTION;

CREATE VIEW view_2 AS SELECT * FROM view_1 WHERE a > 0
WITH LOCAL CHECK OPTION;


CREATE VIEW view_3 AS SELECT * FROM view_1 WHERE a > 0
WITH CASCADED CHECK OPTION;

view_check

ここでは、view_2ビューとview_3ビューが他のビューに対して定義され、
view_1. view_3 にはLOCAL チェックオプションが含まれています。
従って、挿入はview_2チェックだけに対してテストされます。
view_3にはCASCADED チェックオプションが含まれているので、
挿入は、自身のチェックのみならず、基礎ビューに対してもテストされます。
以下のステートメントはこれらの違いを例示したものです。

view_insert_check

[その他TIP]
ビューを生成させてから、システム変数を変えることによってクエリ処理環境を変えると、
ビューから得る結果が影響されることがあります。

CREATE VIEW V_DATE_TIME AS
select current_date,current_time;

current_date_time

以下のような変数も同様にユーザーによって違う結果を返します。
mysql> select CURRENT_USER;
+----------------+
| CURRENT_USER |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

mysql> select CURRENT_USER();
+----------------+
| CURRENT_USER() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

mysql>

5.0.13/5.1.2以前のMySQLで生成されたビューを呼び出す場合、それは、SQL SECURITY DEFINER
節並びにユーザのアカウントと同じDEFINER値を使って生成されたものとして処理されます。しかし、
実際のデファイナーが未知なので、MySQLは警告を発行します。警告を撤去するには、
ビューを再び生成させて、ビュー定義にDEFINER節を含めれば十分です｡

MYSQL VIEW の基本

by ⋅ Leave a Comment

MS SQL, Oracleなどではずいぶんと前からVIEWが利用可能でしたが
MYSQLでも5.xからVIEWが利用可能になってます。


CREATE
[OR REPLACE]
[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
[DEFINER = { user | CURRENT_USER }]



    VIEW view_name [(column_list)]

    AS select_statement

    [WITH [CASCADED | LOCAL] CHECK OPTION]





view_sample


 ビュー定義は以下の制限に規定されます。


    *      SELECTステートメントはFROM節の中にサブ・クエリーを含めることができません。

    *      SELECTステートメントはシステム変数もしくはユーザー変数を参照することができません。

    *      SELECTステートメントは準備されたステートメントパラメータを参照することができません。

    *      ストアド ルーチン内で、定義はルーチン・パラメータもしくはローカル変数を参照することができません。

    *      定義で参照したテーブルもしくはビューは存在しなければいけません。

            ただし、ビューを生成し終えた後に、定義が参照するテーブルまたはビューを撤去することができます。

            この場合、ビューの使用はエラーとなります。この類の問題に対してビュー定義をチェックするには、

            CHECK TABLEステートメントを使用。

    *      定義はTEMPORARYテーブルを参照できない上、TEMPORARYビューを生成させることができません。

    *      ビュー定義内で名称を持つテーブルは存在していなければいけません。

    *      トリガにビューを関連させることはできません。


ORDER BYはビュー定義の中で許容されていますが、それ自身ORDER BYを持つステートメントを使って

ビューから選択すると無視されます。 


view_order_by


UNDEFINEDの場合、MySQLは使用すべきアルゴリズムを選択します。それは出来るだけTEMPTABLE

よりMERGEを優先します。これは、MERGEは通常より効率的で、ビューはテンポラリテーブルを使用すると

更新可能ではなくなるためです。 


明確にTEMPTABLEを選択する理由は、テンポラリテーブルを選んだ後ステートメントの処理終了に使用する前に、

内在するテーブルのロックを解放することができるからです。その結果、ロックをMERGEアルゴリズムよりも速やかに

解除し、ビューを使う他のクライアントが長時間ブロックされないようにします。 


 以下に示す３つの理由によって、ビューアルゴリズムをUNDEFINEDにすることができます。


1) CREATE VIEWステートメントの中にALGORITHM節が現れない。

2)CREATE VIEWステートメントにALGORITHM = UNDEFINED節が明確に含まれている。

3) テンポラリテーブルだけを使って処理できるビューに対して、ALGORITHM = MERGEが規定される。

    この場合、MySQL は警告を発し、アルゴリズムをUNDEFINEDにセットします。


例)

view_merge


MERGEアルゴリズムは、ビュー中の行と基礎テーブル内の行の間に１対１の関係が要求されます。

この関係が保持されない場合、代わりにテンポラリーテーブルを使用しければなりません。

ビューに多くの生成子が含まれると、一対一の関係に不足が生じます。 


    *      集約ファンクション (SUM()、 MIN()、 MAX()、COUNT()等)

    *      DISTINCT

    *      GROUP BY

    *      HAVING

    *      UNION もしくはUNION ALL

    *      選択リスト中のサブ・クエリ

    *      文字値だけを参照(この場合、基礎となるテーブルは存在しません）


 幾つかのビューは更新可能です。すなわち、基礎をなすテーブルの内容を更新するため、UPDATE、DELETEもしくはINSERTのようなステートメントの中でそれらを使うことができます。ビューを更新可能にするため、ビュー中の行と基礎テーブル中の行の間に1対1の関係が存在しなければなりません。ビューを更新不能にするその他の生成子もあります。もっと具体的に言うと、それが以下のいずれかを含んでいるとビューは更新可能となりません。


    *      集約ファンクション(SUM()、 MIN()、 MAX()、COUNT()等)

    *      DISTINCT

    *      GROUP BY

    *      HAVING

    *      UNION もしくはUNION ALL

    *      選択リスト中のサブ・クエリ

    *      特定結合（このセクション中の後の部分に追加した結合の説明参照）

    *      FROM節中の更新不能ビュー

    *      FROM節中のテーブルを参照するWHERE 節中のサブ・クエリ

    *      文字値だけを参照(この場合、更新する基礎となるテーブルは存在しません)

    *      ALGORITHM = TEMPTABLE (テンポラリテーブルの使用は常にビューを更新不能にする)


(INSERTステートメントで更新不能となる)挿入性に関して、それがビューカラムに対するこれらの追加条件

も満たすと、更新不能ビューが挿入可能になります。


    *      ビューカラム名に重複があってはなりません。

    *      ビューに、デフォルト値を持っていないベーステーブル内にあるすべてのカラムを含んでいなくてはなりません。

    *      ビューカラムは派生カラムではなく、単純なカラムリファレンスでなければなりません。

           派生カラムは単純なカラムリファレンスでなく、表現から派生したものです。

           これらは派生カラムの例です。



      3.14159

      col1 + 3

      UPPER(col2)

      col3 / col4

      (subquery)
    


単純なカラムリファレンスと派生カラムを混合して持つビューは挿入できません。

しかし、当該ビューは、派生したものでないこれらのカラムだけをアップデートする場合に限り更新する

ことができます。このビューを想定すると:


CREATE VIEW v AS SELECT col1, 1 AS col2 FROM t;


このビューは、col2が表現から派生しているので挿入できません。

しかし、col2を更新しようとしていない場合、それはアップデートすることができます。

このアップデートは許容されます:


UPDATE v SET col1 = 0;


このアップデートは、それが派生カラムをアップデートしようとしているので許容されません｡


UPDATE v SET col2 = 0;


MERGEアルゴリズムで処理することができると仮定すると、場合によってマルチ・テーブルビューを

アップデートすることが可能です。これを実現するには、ビューに(外部結合またはUNIONでなく)内部結合

を使用しなければなりません。また、ビュー定義に含まれている１つのテーブルだけがアップデート可能です。

よって、SET節に基づき、ビュー中の１つのテーブルからカラムだけ選択して名前をつけなくてはなりません。

UNION ALLを使用しているビューは、理論的に更新可能かもしれませんが、実装は処理にテンポラリテーブル

を使用するので拒絶されます。 


参考サイト

20.2. CREATE VIEW 構文

  
 
  
    


            
                        
              

  
  

    

          

    

      
ViewとSummaryﾃｰﾌﾞﾙの使い分け

      
    
        by  ⋅ Leave a Comment      

    

  

  
  
	
	  	  

Viewでもサマリーテーブルでも同じ答えを得ることは出来るが、それぞれメリット/デメリットがある。

サマリーテーブルは一度作成すれば計算が不要なので、Viewと比較すると処理が早い。

反対にViewの処理は毎回計算されるので遅いが常に最新のデータを返してくれる。

用途によって上手に使い分ければよい。


1) The advantage of using a summary table over using a view is that a summary table is often faster.

Result set is created only once (on table creation time).

2) The disadvantage of a summary table is that its content is static and might become stale


1) View’s result set has to be calculated each time the view is selected.

2) View always provides the most recent data.




CREATE VIEW View_Surface

(Name, ContinentSurface, CountryAvgSurface)

AS SELECT Continent, SUM(SurfaceArea),

AVG(SurfaceArea)

FROM Country GROUP BY Continent;






CREATE TABLE Table_Surface

AS SELECT Continent AS Name,

SUM(SurfaceArea) AS ContinentSurface,

AVG(SurfaceArea) AS CountryAvgSurface

FROM Country GROUP BY Continent;




view_vs_table

  
 
  
    


            
                        
              

  
  

    

          

    

      
MYSQL VIEWの確認

      
    
        by  ⋅ Leave a Comment      

    

  

  
  
	
	  	  



CREATE VIEW V_CITYN_COUNTRYN (CityName, CountryName)

AS SELECT City.Name, Country.Name FROM City, Country

WHERE City.CountryCode = Country.Code

AND City.CountryCode = 'JPN';




view_update_test




update V_CITYN_COUNTRYN SET CityName = 'Yokohama'

where CityName = 'Jokohama [Yokohama]';




update_view




update V_CITYN_COUNTRYN SET CityName = 'TOKYO',

CountryName = 'JAPAN'

where CityName = 'Tokyo'

AND CountryName = 'Japan';




上記UPDATE文は二つのテーブルからなるVIEWにて同時にそれぞれのテーブルを

更新しようとしている為エラーになっている。


ERROR 1393 (HY000): Can not modify more than one base table through a join view 

tables_two


update_view_multiple


 insert into V_CITYN_COUNTRYN values('Toukyou','Japan');

insert into V_CITYN_COUNTRYN(CityName,CountryName) values('Toukyou','Japan');


上記INSERTも2つのテーブルを同時にINSERTしようとしてエラーになっている。

(the view is insertable only if a single table is affected)


ERROR 1393 (HY000): Can not modify more than one base table through a join view ‘STUDY.V_CITYN_COUNTRYN’


view_insert_table


VIEW経由で一つのテーブルに対してINSERTしてみるとINSERTは出来たが、

VIEWは２つのテーブルをJOINして成り立っているのでVIEWをSELECTしてもINSERTしたデータは出てこない。


view_insert_single_table


VIEWは便利ですが、色々な制限があるので色々を参考になるサイトを読んでテストしてみた方がいいですね。

GROUP BY,COUNT,列+1などの関数を利用しているVIEWは他のRDBMS同様にINSERT、UPDATE出来ません。


以下のいずれかを含んでいるとビューは更新可能となりません。

——————————————————————————————

    * 集約ファンクション(SUM()、 MIN()、 MAX()、COUNT()等)

    *DISTINCT

    *GROUP BY

    *HAVING

    *UNION もしくはUNION ALL

    *選択リスト中のサブ・クエリ

      その他………………………..

——————————————————————————————


(例）以下のVIEWは集計ファンクションやGroup byを使用しているので、更新処理は出来ません。

The view is not updatable because it uses aggregate functions and GROUP BY.




Create View V_Country_Area

(Continent,Total_Surface,Average_Surface)

as

select Continent,sum(SurfaceArea),avg(SurfaceArea)

from Country group by Continent;




view_group_by


またMYSQL5.1では以下のような固有の制限もあるようです。

ビューの FROM  句でサブクエリを使用することはできません。


    
この制限はいずれ取り除かれる予定です。 


参考サイト

D.4. ビューの規制

20.2. CREATE VIEW 構文

  
 
  
    


            
                        
              

  
  

    

          

    

      
MYSQL (VIEW WITH CHECK OPTION)

      
    
        by  ⋅ Leave a Comment      

    

  

  
  
	
	  	  

WITH CHECK OPTION


テーブルがAUTO_INCREMENTカラムを含んでいないテーブル上にある挿入可能なビューに

挿入するAUTO_INCREMENTカラムを含んでいる場合、ビューの一部でないカラムにデフォルト

値を挿入した副作用が見えないので、カラムはLAST_INSERT_ID()の値を変更しません。


それに対するselect_statement中のWHERE節が真実であるものを除く行に、

更新不能なビューが挿入されるか、当該行が更新されるのを回避するため、

WITH CHECK OPTION節を附与することができます。


更新可能なビューに対するWITH CHECK OPTION節に基づき、LOCALとCASCADEDキーワード

はビューが他のビューに対して定義される場合、チェックテストの範囲を決めます｡

定義されているビューだけに対して、LOCALキーワードはCHECK OPTIONを制限します。

CASCADEDは同様に基礎ビューを評価するチェックを起動させます。キーワードが附与されない場合、

デフォルト設定はCASCADEDとなります。以下のテーブル並びにビューのセットを考慮すると：


CREATE TABLE TABLE01 (ID INT);




CREATE VIEW VIEW01 AS SELECT * FROM TABLE01 WHERE ID < 2
WITH CHECK OPTION;





CREATE VIEW VIEW02 AS SELECT * FROM VIEW01 WHERE ID > 0

WITH LOCAL CHECK OPTION;






CREATE VIEW VIEW03 AS SELECT * FROM VIEW01 WHERE ID > 0

WITH CASCADED CHECK OPTION;




create_view_check_opt


v2ビューとv3ビューが他のビューに対して定義され、v1. v2 にはLOCAL  チェックオプションが含まれています。

従って、挿入はv2チェックだけに対してテストされます。v3にはCASCADED  チェックオプションが含まれているので、

挿入は、自身のチェックのみならず、基礎ビューに対してもテストされます。


WITH LOCAL CHECK OPTION　= ( ID > 0 )

INSERT INTO VIEW02 VALUES (2);


WITH CASCADED CHECK OPTION = (ID > 0   と ID < 2 )

INSERT INTO VIEW03 VALUES (2);


--------------------------------------------------------------------------------------

VIEW03にはCASCADEオプションでVIEW03のCHECK OPTIONも適用された。

--------------------------------------------------------------------------------------

mysql> INSERT INTO VIEW01 VALUES (2);

ERROR 1369 (HY000): CHECK OPTION failed 'STUDY.VIEW01'

mysql>

-------------------------------------------


view_cascade


関連システム変数



updatable_views_with_limit


更新の許可するかどうかを制御する。ビューに基準テーブルで定義したプライマリキー

のすべてのカラムが含まれていない場合に、更新ステートメントで LIMIT 節を含んでいたら、

そのビューを更新するかどうか、ということである。このような更新は GUI ツールなどから生成される。

ここでの更新は UPDATE または DELETE ステートメントのこと。ここでのプライマリ キーとは

PRIMARY KEY または UNIQUE インデックスのことで、NULL をカラムに含まない。


この変数の値は 2 種類ある。


    *      1 または YES:エラー メッセージではなく、警告だけを発行。(デフォルト値)

    *      0 または NO:更新禁止。



参考サイト




20.2. CREATE VIEW 構文


4.2.3. システム変数

  
 
  
    


            
                        
              

  
  

    

          

    

      
Updatable or Non-Updatable VIEW

      
    
        by  ⋅ Leave a Comment      

    

  

  
  
	
	  	  

 幾つかのビューは更新可能です。すなわち、基礎をなすテーブルの内容を更新するため、

UPDATE、DELETEもしくはINSERTのようなステートメントの中でそれらを使うことができます。

ビューを更新可能にするため、ビュー中の行と基礎テーブル中の行の間に1対1の関係が存在

しなければなりません。ビューを更新不能にするその他の生成子もあります。

もっと具体的に言うと、それが以下のいずれかを含んでいるとビューは更新可能となりません。


    *     集約ファンクション(SUM()、 MIN()、 MAX()、COUNT()等)

    *     DISTINCT

    *     GROUP BY

    *     HAVING

    *     UNION もしくはUNION ALL

    *     選択リスト中のサブ・クエリ

    *     特定結合（このセクション中の後の部分に追加した結合の説明参照）

    *     FROM節中の更新不能ビュー

    *     FROM節中のテーブルを参照するWHERE 節中のサブ・クエリ

    *     文字値だけを参照(この場合、更新する基礎となるテーブルは存在しません)

    *     ALGORITHM = TEMPTABLE (テンポラリテーブルの使用は常にビューを更新不能にする)


①　[更新不可能 ] Use of ALGORITHM=TEMPTABLE in the view definition


TEMPTABLEの場合、ビューの結果がテンポラリーテーブルの中に復元され、

その後、ステートメントを実行するために使用されます。

よってUPDATE文は、TEMPTABLEに影響するがベーステーブルには影響しない。


②　[更新可能 ] 　Use of ALGORITHM=MERGE in the view definition


UNDEFINEDの場合、MySQLは使用すべきアルゴリズムを選択します。

それは出来るだけTEMPTABLEよりMERGEを優先します。これは、MERGEは通常より効率的で、

ビューはテンポラリテーブルを使用すると更新可能ではなくなるためです。 


 明確にTEMPTABLEを選択する理由は、テンポラリテーブルを選んだ後ステートメントの

処理終了に使用する前に、内在するテーブルのロックを解放することができるからです。

その結果、ロックをMERGEアルゴリズムよりも速やかに解除し、ビューを使う他のクライアント

が長時間ブロックされないようにします。


————————–

補足

————————–

以下に示す３つの理由によって、ビューアルゴリズムをUNDEFINEDにすることができます。

    *      CREATE VIEWステートメントの中にALGORITHM節が現れない。

    *      CREATE VIEWステートメントにALGORITHM = UNDEFINED節が明確に含まれている。

    *      テンポラリテーブルだけを使って処理できるビューに対して、ALGORITHM = MERGEが規定される。

           この場合、MySQL は警告を発し、アルゴリズムをUNDEFINEDにセットします。


③ 　[更新不可能 ]  Use of aggregate functions in the view definition


④   [更新不可能 ]  Use of GROUP BY or HAVING clauses in the view definition


⑤ [更新可能 ]  Use of expressions like col = col + 1 in the view definition




mysql> UPDATE V_CountryPopCalc SET PopNew = 1000

    -> WHERE Name = 'United States';

ERROR 1348 (HY000): Column 'PopNew' is not updatable






mysql> UPDATE V_CountryPopCalc SET Name = 'Update Test for V_CountryPopCalc'

    -> WHERE Name = 'United States';

Query OK, 1 row affected (0.00 sec)

Rows matched: 1  Changed: 1  Warnings: 0




update


参考サイト


20.2. CREATE VIEW 構文



  
 
  
    


            
                        
              

  
  

    

          

    

      
ViewとTableそれぞれのメリット、デメリット

      
    
        by  ⋅ Leave a Comment      

    

  

  
  
	
	  	  

ViewとTableで同じデータを持ったオブジェクトを作成する事が出来るが、

それぞれメリットデメリットがある。Viewは常に最新のデータが反映されるという

メリットがあるが、毎回計算が入るので速度が遅い。Tableに関しては、速度は速いが

最新のデータが常に反映されない。用途にもよるので、それぞれ最適な方法を

選択する必要がある。最適なのは、テーブルのデータが常に最新のデータで反映

される事だが、Viewを必要とする時は対外テーブルが分散されている場合が多い…


以下のテーブルとビューは同じ結果を抽出することが出来る。



CREATE VIEW V_Surface

(Name, ContinentSurface, CountryAvgSurface)

AS SELECT Continent, SUM(SurfaceArea),

AVG(SurfaceArea)

FROM Country GROUP BY Continent;






CREATE TABLE T_Surface

AS SELECT Continent AS Name,

SUM(SurfaceArea) AS ContinentSurface,

AVG(SurfaceArea) AS CountryAvgSurface

FROM Country GROUP BY Continent;




table_vs_view

  
 
  
    


            
                        
              

  
  

    

          

    

      
MYSQL Update View(集合関数、Group by)

      
    
        by  ⋅ Leave a Comment      

    

  

  
  
	
	  	  

大陸の名前、面積が入っているテーブルをベースに大陸毎の面積や各大陸に存在する

国の平均面積を算出するViewを作成。


下記2つの内容は同じViewになります。




Create View V_Surface

(NAME, SUM_Surface, AVG_Suface) AS

select continent, sum(SurfaceArea),avg(SurfaceArea)

from Country Group by Continent;






Create View V_Surface2 AS

select continent as NAME, sum(SurfaceArea) as SUM_Surface,

avg(SurfaceArea) as AVG_Suface from Country Group by Continent;




create_view_surface


上記のViewはSUMなどのAggregate funcation(集合関数)を利用していることGroup byを

利用しているので、更新することは出来ない。


view_update_fail


——————————————————————-

・データベース構造を意識させない。

・正規化によって分解された表を、Viewにて組み合わせて一つのテーブルのように操作可能。

・Tableへ直接の権限を付与しないでViewにて権限を制限する事でｾｷｭﾘﾃｨ強化

・複数の表からなるビューは更新できない。

・集合関数や演算を使用して作成したViewはUpdateできない。