星期四, 6月 07, 2018

MySQL 5.6 install sys schema from Github

繼前一篇
已經把MySQL 5.6安裝完畢了...接著來安裝sys schema...

安裝sys schema主要是為了監控並觀察效能相關的view
5.7以上就有內附sys schema...(還是推薦使用5.7)

mysql> SHOW VARIABLES LIKE 'perf%';
+--------------------------------------------------------+-------+
| Variable_name                                          | Value |
+--------------------------------------------------------+-------+
| performance_schema                                     | ON    |
| performance_schema_accounts_size                       | 100   |
| performance_schema_digests_size                        | 10000 |
| performance_schema_events_stages_history_long_size     | 10000 |
| performance_schema_events_stages_history_size          | 10    |
| performance_schema_events_statements_history_long_size | 10000 |
| performance_schema_events_statements_history_size      | 10    |
| performance_schema_events_waits_history_long_size      | 10000 |  -> 設定為15000
| performance_schema_events_waits_history_size           | 10    |  -> 設定為20
| performance_schema_hosts_size                          | 100   |
| performance_schema_max_cond_classes                    | 80    |
| performance_schema_max_cond_instances                  | 3504  |
| performance_schema_max_digest_length                   | 1024  |
| performance_schema_max_file_classes                    | 50    |
| performance_schema_max_file_handles                    | 32768 |
| performance_schema_max_file_instances                  | 7693  |
| performance_schema_max_mutex_classes                   | 200   |
| performance_schema_max_mutex_instances                 | 15906 |
| performance_schema_max_rwlock_classes                  | 40    |
| performance_schema_max_rwlock_instances                | 9102  |
| performance_schema_max_socket_classes                  | 10    |
| performance_schema_max_socket_instances                | 322   |
| performance_schema_max_stage_classes                   | 150   |
| performance_schema_max_statement_classes               | 168   |
| performance_schema_max_table_handles                   | 4000  |
| performance_schema_max_table_instances                 | 12500 |
| performance_schema_max_thread_classes                  | 50    |
| performance_schema_max_thread_instances                | 402   |
| performance_schema_session_connect_attrs_size          | 512   |
| performance_schema_setup_actors_size                   | 100   |
| performance_schema_setup_objects_size                  | 100   |
| performance_schema_users_size                          | 100   |
+--------------------------------------------------------+-------+
32 rows in set (0.00 sec)

#ref: https://dev.mysql.com/doc/refman/5.7/en/performance-schema-startup-configuration.html
#Initial Setup for my.cnf
performance_schema=ON
performance_schema_events_waits_history_size=20
performance_schema_events_waits_history_long_size=15000
max_connections
open_files_limit
table_definition_cache
table_open_cache

#MySQL 5.7 default parameter check
mysql -u root -p <<MYEOF
show variables like 'max_connections';
show variables like 'open_files_limit';
show variables like 'table_definition_cache';
show variables like 'table_open_cache';
MYEOF
Variable_name   Value
max_connections 151
Variable_name   Value
open_files_limit        5000
Variable_name   Value
table_definition_cache  1400
Variable_name   Value
table_open_cache        2000


#ref: https://github.com/mysql/mysql-sys
#ref: https://blog.csdn.net/leonpenn/article/details/78223549

安裝MySQLsys database from github

[root@centos7 tmp]# yum -y install git

[root@centos7 tmp]# git clone https://github.com/mysql/mysql-sys.git
Cloning into 'mysql-sys'...
remote: Counting objects: 3009, done.
remote: Total 3009 (delta 0), reused 0 (delta 0), pack-reused 3009
Receiving objects: 100% (3009/3009), 1.17 MiB | 611.00 KiB/s, done.
Resolving deltas: 100% (1770/1770), done.


[root@centos7 mysql-sys]# mysql -u root -p < ./sys_56.sql
Enter password:
[root@centos7 mysql-sys]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 29
Server version: 5.6.40-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use sys;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> 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                                |
| metrics                                       |
| processlist                                   |
| ps_check_lost_instrumentation                 |
| schema_auto_increment_columns                 |
| schema_index_statistics                       |
| schema_object_overview                        |
| schema_redundant_indexes                      |
| schema_table_statistics                       |
| schema_table_statistics_with_buffer           |
| schema_tables_with_full_table_scans           |
| schema_unused_indexes                         |
| session                                       |
| 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$processlist                                 |
| x$ps_digest_95th_percentile_by_avg_us         |
| x$ps_digest_avg_latency_distribution          |
| x$ps_schema_table_statistics_io               |
| x$schema_flattened_keys                       |
| x$schema_index_statistics                     |
| x$schema_table_statistics                     |
| x$schema_table_statistics_with_buffer         |
| x$schema_tables_with_full_table_scans         |
| x$session                                     |
| 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                     |
+-----------------------------------------------+
88 rows in set (0.00 sec)

SELECT file, count_write cw,
total_written tw
FROM sys.io_global_by_file_by_bytes
WHERE count_write>0 ORDER BY count_write DESC;

mysql> SELECT file, count_write cw,
    ->  total_written tw
    ->  FROM sys.io_global_by_file_by_bytes
    ->  WHERE count_write>0 ORDER BY count_write DESC;
+-------------------------------------------------------------------------------------+------+------------+
| file                                                                                | cw   | tw         |
+-------------------------------------------------------------------------------------+------+------------+
| @@datadir/mysql/#sql-4e4e_14.MYI                                                    | 1352 | 28.01 KiB  |
| @@datadir/mysql/proc.MYD                                                            |  508 | 885.72 KiB |
| @@datadir/mysql/#sql-4e4e_14.frm                                                    |  269 | 45.09 KiB  |
| @@datadir/mysql/proc.MYI                                                            |  173 | 24.58 KiB  |
| @@datadir/performance_schema/table_lock_waits_summary_by_table.frm                  |  161 | 10.50 KiB  |
| @@datadir/ibdata1                                                                   |  101 | 3.31 MiB   |
| @@datadir/performance_schema/events_statements_current.frm                          |   96 | 8.48 KiB   |
| @@datadir/performance_schema/events_statements_history.frm                          |   96 | 8.48 KiB   |
| @@datadir/performance_schema/events_statements_history_long.frm                     |   96 | 8.48 KiB   |
| @@datadir/performance_schema/table_io_waits_summary_by_index_usage.frm              |   93 | 7.51 KiB   |
| @@datadir/performance_schema/table_io_waits_summary_by_table.frm                    |   91 | 7.23 KiB   |
| @@datadir/performance_schema/host_cache.frm                                         |   74 | 7.51 KiB   |
| @@datadir/performance_schema/events_statements_summary_by_digest.frm                |   73 | 6.50 KiB   |
| @@datadir/performance_schema/events_statements_summary_by_account_by_event_name.frm |   69 | 6.69 KiB   |
| @@datadir/performance_schema/events_statements_summary_by_host_by_event_name.frm    |   67 | 6.62 KiB   |
| @@datadir/performance_schema/events_statements_summary_by_user_by_event_name.frm    |   67 | 6.49 KiB   |
| @@datadir/performance_schema/events_statements_summary_by_thread_by_event_name.frm  |   67 | 6.46 KiB   |
| @@datadir/performance_schema/file_summary_by_instance.frm                           |   65 | 7.81 KiB   |
| @@datadir/performance_schema/events_statements_summary_global_by_event_name.frm     |   65 | 6.41 KiB   |
| @@datadir/performance_schema/socket_summary_by_instance.frm                         |   63 | 6.27 KiB   |
| @@datadir/mysql/#sql-4e4e_14.MYD                                                    |   61 | 392.29 KiB |
| @@datadir/performance_schema/file_summary_by_event_name.frm                         |   61 | 6.20 KiB   |
| @@datadir/performance_schema/socket_summary_by_event_name.frm                       |   61 | 6.20 KiB   |
| @@datadir/performance_schema/events_waits_current.frm                               |   54 | 8.11 KiB   |
| @@datadir/performance_schema/events_waits_history_long.frm                          |   54 | 8.11 KiB   |
| @@datadir/performance_schema/events_waits_history.frm                               |   54 | 8.11 KiB   |
| @@datadir/performance_schema/threads.frm                                            |   44 | 6.40 KiB   |
| @@datadir/performance_schema/events_stages_current.frm                              |   36 | 5.53 KiB   |
| @@datadir/performance_schema/events_stages_history.frm                              |   36 | 5.53 KiB   |
| @@datadir/performance_schema/events_stages_history_long.frm                         |   36 | 5.53 KiB   |
| @@datadir/performance_schema/events_waits_summary_by_account_by_event_name.frm      |   31 | 5.43 KiB   |
| @@datadir/performance_schema/events_stages_summary_by_account_by_event_name.frm     |   31 | 5.43 KiB   |
| @@datadir/performance_schema/objects_summary_global_by_type.frm                     |   31 | 5.43 KiB   |
| @@datadir/performance_schema/socket_instances.frm                                   |   30 | 5.32 KiB   |
| @@datadir/ib_logfile0                                                               |   29 | 43.50 KiB  |
| @@datadir/performance_schema/events_waits_summary_by_host_by_event_name.frm         |   29 | 5.35 KiB   |
| @@datadir/performance_schema/events_stages_summary_by_host_by_event_name.frm        |   29 | 5.35 KiB   |
| @@datadir/performance_schema/events_waits_summary_by_user_by_event_name.frm         |   29 | 5.22 KiB   |
| @@datadir/performance_schema/events_stages_summary_by_user_by_event_name.frm        |   29 | 5.22 KiB   |
| @@datadir/performance_schema/events_waits_summary_by_thread_by_event_name.frm       |   29 | 5.19 KiB   |
| @@datadir/performance_schema/events_waits_summary_by_instance.frm                   |   29 | 5.21 KiB   |
| @@datadir/performance_schema/events_stages_summary_by_thread_by_event_name.frm      |   29 | 5.19 KiB   |
| @@datadir/performance_schema/events_waits_summary_global_by_event_name.frm          |   27 | 5.14 KiB   |
| @@datadir/performance_schema/events_stages_summary_global_by_event_name.frm         |   27 | 5.14 KiB   |
| @@datadir/performance_schema/setup_objects.frm                                      |   26 | 5.04 KiB   |
| @@datadir/performance_schema/performance_timers.frm                                 |   24 | 4.71 KiB   |
| @@datadir/performance_schema/session_connect_attrs.frm                              |   23 | 7.73 KiB   |
| @@datadir/sys/sys_config.frm                                                        |   23 | 5.73 KiB   |
| @@datadir/performance_schema/session_account_connect_attrs.frm                      |   23 | 7.73 KiB   |
| @@datadir/performance_schema/accounts.frm                                           |   23 | 4.86 KiB   |
| @@datadir/performance_schema/rwlock_instances.frm                                   |   23 | 5.07 KiB   |
| @@datadir/performance_schema/setup_instruments.frm                                  |   22 | 4.93 KiB   |
| @@datadir/performance_schema/file_instances.frm                                     |   21 | 6.45 KiB   |
| @@datadir/performance_schema/mutex_instances.frm                                    |   21 | 4.99 KiB   |
| @@datadir/performance_schema/setup_actors.frm                                       |   21 | 4.80 KiB   |
| @@datadir/performance_schema/hosts.frm                                              |   21 | 4.78 KiB   |
| @@datadir/performance_schema/users.frm                                              |   21 | 4.65 KiB   |
| @@datadir/performance_schema/setup_timers.frm                                       |   20 | 4.75 KiB   |
| @@datadir/performance_schema/setup_consumers.frm                                    |   20 | 4.71 KiB   |
| @@datadir/performance_schema/cond_instances.frm                                     |   19 | 4.92 KiB   |
| @@datadir/sys/sys_config.TRG~                                                       |   10 | 5.61 KiB   |
| @@datadir/sys/sys_config.ibd                                                        |    7 | 176.00 KiB |
| @@datadir/mysql/innodb_index_stats.ibd                                              |    5 | 80.00 KiB  |
| @@datadir/mysql/innodb_table_stats.ibd                                              |    5 | 80.00 KiB  |
| @@datadir/sys/schema_auto_increment_columns.frm~                                    |    4 | 25.12 KiB  |
| @@datadir/sys/innodb_lock_waits.frm~                                                |    4 | 21.47 KiB  |
| @@datadir/sys/schema_redundant_indexes.frm~                                         |    4 | 21.21 KiB  |
| @@datadir/sys/x@0024innodb_lock_waits.frm~                                          |    4 | 20.89 KiB  |
| @@datadir/sys/schema_object_overview.frm~                                           |    4 | 14.58 KiB  |
| @@datadir/sys/innodb_buffer_stats_by_table.frm~                                     |    4 | 11.10 KiB  |
| @@datadir/sys/x@0024innodb_buffer_stats_by_table.frm~                               |    4 | 10.86 KiB  |
| @@datadir/sys/innodb_buffer_stats_by_schema.frm~                                    |    4 | 9.94 KiB   |
| @@datadir/sys/x@0024schema_flattened_keys.frm~                                      |    4 | 9.84 KiB   |
| @@datadir/sys/x@0024innodb_buffer_stats_by_schema.frm~                              |    4 | 9.70 KiB   |
| @@datadir/sys/version.frm~                                                          |    4 | 1.82 KiB   |
| @@datadir/mysql/#sql-4e4e_14.CSM                                                    |    4 | 140 bytes  |
| @@datadir/sys/sys_config_insert_set_user.TRN~                                       |    4 | 168 bytes  |
| @@datadir/sys/sys_config_update_set_user.TRN~                                       |    4 | 168 bytes  |
| @@datadir/sys/db.opt                                                                |    3 | 183 bytes  |
| @@datadir/sys/statement_analysis.frm~                                               |    2 | 14.11 KiB  |
| @@datadir/sys/x@0024statement_analysis.frm~                                         |    2 | 13.50 KiB  |
| @@datadir/sys/statements_with_full_table_scans.frm~                                 |    2 | 11.27 KiB  |
| @@datadir/sys/schema_table_statistics_with_buffer.frm~                              |    2 | 10.97 KiB  |
| @@datadir/sys/x@0024statements_with_full_table_scans.frm~                           |    2 | 10.93 KiB  |
| @@datadir/sys/metrics.frm~                                                          |    2 | 10.91 KiB  |
| @@datadir/sys/processlist.frm~                                                      |    2 | 10.43 KiB  |
| @@datadir/sys/io_global_by_wait_by_bytes.frm~                                       |    2 | 10.29 KiB  |
| @@datadir/sys/io_global_by_wait_by_latency.frm~                                     |    2 | 9.95 KiB   |
| @@datadir/sys/x@0024processlist.frm~                                                |    2 | 9.68 KiB   |
| @@datadir/sys/x@0024schema_table_statistics_with_buffer.frm~                        |    2 | 9.56 KiB   |
| @@datadir/sys/x@0024io_global_by_wait_by_bytes.frm~                                 |    2 | 9.23 KiB   |
| @@datadir/sys/host_summary_by_statement_type.frm~                                   |    2 | 8.85 KiB   |
| @@datadir/sys/user_summary_by_statement_type.frm~                                   |    2 | 8.79 KiB   |
| @@datadir/sys/io_by_thread_by_latency.frm~                                          |    2 | 8.78 KiB   |
| @@datadir/sys/x@0024io_global_by_wait_by_latency.frm~                               |    2 | 8.77 KiB   |
| @@datadir/sys/statements_with_temp_tables.frm~                                      |    2 | 8.61 KiB   |
| @@datadir/sys/statements_with_sorting.frm~                                          |    2 | 8.60 KiB   |
| @@datadir/sys/x@0024host_summary_by_statement_type.frm~                             |    2 | 8.51 KiB   |
| @@datadir/sys/x@0024user_summary_by_statement_type.frm~                             |    2 | 8.44 KiB   |
| @@datadir/sys/x@0024statements_with_temp_tables.frm~                                |    2 | 8.35 KiB   |
| @@datadir/sys/x@0024statements_with_sorting.frm~                                    |    2 | 8.34 KiB   |
| @@datadir/sys/x@0024io_by_thread_by_latency.frm~                                    |    2 | 8.32 KiB   |
| @@datadir/sys/io_global_by_file_by_bytes.frm~                                       |    2 | 8.27 KiB   |
| @@datadir/sys/host_summary_by_statement_latency.frm~                                |    2 | 8.14 KiB   |
| @@datadir/sys/user_summary_by_statement_latency.frm~                                |    2 | 8.14 KiB   |
| @@datadir/sys/schema_table_statistics.frm~                                          |    2 | 8.09 KiB   |
| @@datadir/sys/x@0024host_summary_by_statement_latency.frm~                          |    2 | 7.79 KiB   |
| @@datadir/sys/x@0024user_summary_by_statement_latency.frm~                          |    2 | 7.79 KiB   |
| @@datadir/sys/x@0024io_global_by_file_by_bytes.frm~                                 |    2 | 7.55 KiB   |
| @@datadir/sys/statements_with_errors_or_warnings.frm~                               |    2 | 7.49 KiB   |
| @@datadir/sys/x@0024statements_with_errors_or_warnings.frm~                         |    2 | 7.35 KiB   |
| @@datadir/sys/user_summary.frm~                                                     |    2 | 7.27 KiB   |
| @@datadir/sys/schema_index_statistics.frm~                                          |    2 | 7.20 KiB   |
| @@datadir/sys/wait_classes_global_by_avg_latency.frm~                               |    2 | 7.09 KiB   |
| @@datadir/sys/statements_with_runtimes_in_95th_percentile.frm~                      |    2 | 7.04 KiB   |
| @@datadir/sys/x@0024schema_table_statistics.frm~                                    |    2 | 6.93 KiB   |
| @@datadir/sys/x@0024user_summary.frm~                                               |    2 | 6.92 KiB   |
| @@datadir/sys/latest_file_io.frm~                                                   |    2 | 6.88 KiB   |
| @@datadir/sys/wait_classes_global_by_latency.frm~                                   |    2 | 6.77 KiB   |
| @@datadir/sys/waits_by_user_by_latency.frm~                                         |    2 | 6.76 KiB   |
| @@datadir/sys/x@0024schema_index_statistics.frm~                                    |    2 | 6.74 KiB   |
| @@datadir/sys/host_summary.frm~                                                     |    2 | 6.60 KiB   |
| @@datadir/sys/x@0024statements_with_runtimes_in_95th_percentile.frm~                |    2 | 6.55 KiB   |
| @@datadir/sys/x@0024latest_file_io.frm~                                             |    2 | 6.53 KiB   |
| @@datadir/sys/x@0024waits_by_user_by_latency.frm~                                   |    2 | 6.42 KiB   |
| @@datadir/sys/x@0024wait_classes_global_by_avg_latency.frm~                         |    2 | 6.41 KiB   |
| @@datadir/sys/waits_by_host_by_latency.frm~                                         |    2 | 6.36 KiB   |
| @@datadir/sys/x@0024wait_classes_global_by_latency.frm~                             |    2 | 6.32 KiB   |
| @@datadir/sys/x@0024host_summary.frm~                                               |    2 | 6.13 KiB   |
| @@datadir/sys/x@0024waits_by_host_by_latency.frm~                                   |    2 | 6.01 KiB   |
| @@datadir/sys/host_summary_by_file_io_type.frm~                                     |    2 | 5.89 KiB   |
| @@datadir/sys/x@0024ps_schema_table_statistics_io.frm~                              |    2 | 5.86 KiB   |
| @@datadir/sys/user_summary_by_file_io_type.frm~                                     |    2 | 5.79 KiB   |
| @@datadir/sys/x@0024host_summary_by_file_io_type.frm~                               |    2 | 5.66 KiB   |
| @@datadir/sys/x@0024user_summary_by_file_io_type.frm~                               |    2 | 5.55 KiB   |
| @@datadir/sys/host_summary_by_stages.frm~                                           |    2 | 5.45 KiB   |
| @@datadir/sys/user_summary_by_stages.frm~                                           |    2 | 5.39 KiB   |
| @@datadir/sys/x@0024host_summary_by_stages.frm~                                     |    2 | 5.22 KiB   |
| @@datadir/sys/x@0024user_summary_by_stages.frm~                                     |    2 | 5.16 KiB   |
| @@datadir/sys/io_global_by_file_by_latency.frm~                                     |    2 | 5.13 KiB   |
| @@datadir/sys/x@0024session.frm~                                                    |    2 | 4.95 KiB   |
| @@datadir/sys/waits_global_by_latency.frm~                                          |    2 | 4.81 KiB   |
| @@datadir/sys/session.frm~                                                          |    2 | 4.75 KiB   |
| @@datadir/sys/host_summary_by_file_io.frm~                                          |    2 | 4.62 KiB   |
| @@datadir/sys/user_summary_by_file_io.frm~                                          |    2 | 4.62 KiB   |
| @@datadir/sys/x@0024io_global_by_file_by_latency.frm~                               |    2 | 4.55 KiB   |
| @@datadir/sys/schema_unused_indexes.frm~                                            |    2 | 4.54 KiB   |
| @@datadir/sys/x@0024host_summary_by_file_io.frm~                                    |    2 | 4.50 KiB   |
| @@datadir/sys/x@0024user_summary_by_file_io.frm~                                    |    2 | 4.50 KiB   |
| @@datadir/sys/x@0024waits_global_by_latency.frm~                                    |    2 | 4.47 KiB   |
| @@datadir/sys/schema_tables_with_full_table_scans.frm~                              |    2 | 3.93 KiB   |
| @@datadir/sys/x@0024schema_tables_with_full_table_scans.frm~                        |    2 | 3.81 KiB   |
| @@datadir/sys/x@0024ps_digest_95th_percentile_by_avg_us.frm~                        |    2 | 3.45 KiB   |
| @@datadir/sys/ps_check_lost_instrumentation.frm~                                    |    2 | 2.14 KiB   |
| @@datadir/sys/x@0024ps_digest_avg_latency_distribution.frm~                         |    2 | 1.66 KiB   |
| @@datadir/mysql/slow_log.CSM                                                        |    2 | 70 bytes   |
| @@datadir/mysql/general_log.CSM                                                     |    2 | 70 bytes   |
| @@datadir/performance_schema/db.opt                                                 |    1 | 61 bytes   |
| @@datadir/centos7.pid                                                               |    1 | 6 bytes    |
+-------------------------------------------------------------------------------------+------+------------+
159 rows in set (0.06 sec)

--5.7 才查的到
mysql> SELECT * FROM sys.memory_global_total;
ERROR 1146 (42S02): Table 'sys.memory_global_total' doesn't exist
mysql>


底下放一些例子
更多實際案例直接參考MySQL sys schema github上面的example









































查詢沒有使用到索引或者索引效率低下的語句:
SELECT OBJECT_SCHEMA,  THREAD_ID TID, SUBSTR(SQL_TEXT, 1, 50) SQL_TEXT, ROWS_SENT RS, ROWS_EXAMINED RE,CREATED_TMP_TABLES,NO_INDEX_USED,NO_GOOD_INDEX_USED FROM performance_schema.events_statements_history WHERE (NO_INDEX_USED=1 OR NO_GOOD_INDEX_USED=1) and sql_text  NOT LIKE '%performance_schema%'\G

查看哪些索引沒有被使用過

SELECT
OBJECT_SCHEMA,
OBJECT_NAME,
INDEX_NAME
FROM table_io_waits_summary_by_index_usage
WHERE INDEX_NAME IS NOT NULL
AND COUNT_STAR = 0
AND OBJECT_SCHEMA <> 'mysql'
ORDER BY OBJECT_SCHEMA,OBJECT_NAME;


查看SQL語句在哪個階段消耗最大

SELECT eshl.event_name, sql_text, eshl.timer_wait/1000000000000 w_s
FROM performance_schema.events_stages_history_long eshl
JOIN performance_schema.events_statements_history_long esthl
ON (eshl.nesting_event_id = esthl.event_id)
WHERE eshl.timer_wait > 1*10000000000\G

ref:
Sys Schema for MySQL 5.6 and MySQL 5.7
  https://www.percona.com/blog/2014/11/20/sys-schema-mysql-5-6-5-7/

Performance Schema簡介
  http://m.blog.itpub.net/28218939/viewspace-2141605/


沒有留言:

LinkWithin-相關文件

Related Posts Plugin for WordPress, Blogger...