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]>

Comments are closed.

Post Navigation