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になっている状況が確認出来ました。
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]>