![variable.jp [データベース,パフォーマンス,運用]](https://variable.jp/wp-content/uploads/2013/09/cropped-variable.jp_.jpg)
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