暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / MySQL的诊断利器——MySQL Shell 8.0.31的诊断实用程序

MySQL的诊断利器——MySQL Shell 8.0.31的诊断实用程序

MySQL解决方案工程师 2022-10-20
270

MySQL Shell诊断实用程序能够分析MySQL服务器的性能，并能够生成运行状况、性能和单个查询的诊断报告。注意，这是MySQL Shell在8.0.31提供的新功能，用户必须使用8.0.31之后的版本。

诊断工具包括三个：

util.debug.collectDiagnostics()

util.debug.collectHighLoadDiagnostics()

util.debug.collectSlowQueryDiagnostics()

util.debug.collectDiagnostics()用于从MySQL服务器、InnoDB Cluster收集原始的诊断数据，并生成tsv和yaml格式的报告文件。（文件使用zip压缩）

util.debug.collectHighLoadDiagnostics()在MySQL服务器上运行多次迭代的诊断报告，使用户能够在负载下从多个方面分析服务器，并生成tsv和yaml格式的报告文件。（文件使用zip压缩）

util.debug.collectSlowQueryDiagnostics()在MySQL服务器上运行多次迭代的诊断报告，使用户能够在执行指定查询的情况下从多个方面分析服务器，并生成tsv和yaml格式的报告文件。（文件使用zip压缩）

工具适用于MySQL5.7之后的服务器版本，并且需要使用root用户执行。

工具执行非常简单，只需要指定一些参数或选项即可，以util.debug.collectDiagnostics()为例：

    util.debug.collectDiagnostics("path/",{options})

    这里的"path"是输出报告的位置和文件名称。如果用户没有提供位置和名称，默认会输出在当前路径，文件名为mysql-diagnostics-YYYYMMDD-HHMMSS
    .zip。

    选项包括：allMembers、innodbMutex、schemaStats、slowQueries、ignoreErrors、customSql，及customShell，详细的指定方法可以参阅官网的在线手册“https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-utilities-debug-diagnostics.html”

    执行该工具，我们实际来看一看能够输出哪些内容。下面是执行过程：

      MySQL Shell 8.0.31


      Copyright (c) 2016, 2022, Oracle and/or its affiliates.
      Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
      Other names may be trademarks of their respective owners.


      Type '\help' or '\?' for help; '\quit' to exit.
      WARNING: Permissions 0644 for log file "/Users/yitao.xu/.mysqlsh/mysqlsh.log" are too open. Permissions have been adjusted for user only access.
       MySQL  JS > \c user@ip:3306
      Creating a session to 'user@ip:3306'
      Fetching schema names for auto-completion... Press ^C to stop.
      Your MySQL connection id is 965
      Server version: 8.0.30 MySQL Community Server - GPL
      No default schema selected; type \use <schema> to set one.
       MySQL  ip:3306 ssl  JS > util.debug.collectDiagnostics("/Users/yitao.xu/Downloads/Dia_report.zip")
      Collecting diagnostics information from mysql://user@ip:3306...
      Copying shell log file...
       - Gathering schema tables without a PK...
       - Gathering schema routine size...
       - Gathering schema table count...
       - Gathering schema unused indexes...
       - Gathering error_log
       - Gathering performance_schema.host_cache...
       - Gathering performance_schema.persisted_variables...
       - Gathering performance_schema.replication_applier_configuration...
       - Gathering performance_schema.replication_applier_filters...
       - Gathering performance_schema.replication_applier_global_filters...
       - Gathering performance_schema.replication_applier_status...
       - Gathering performance_schema.replication_applier_status_by_coordinator...
       - Gathering performance_schema.replication_applier_status_by_worker...
       - Gathering performance_schema.replication_asynchronous_connection_failover...
       - Gathering performance_schema.replication_asynchronous_connection_failover_managed...
       - Gathering performance_schema.replication_connection_configuration...
       - Gathering performance_schema.replication_connection_status...
       - Gathering performance_schema.replication_group_member_stats...
       - Gathering performance_schema.replication_group_members...
       - Gathering global variables...
       - Gathering XA RECOVER CONVERT xid...
       - Gathering SHOW BINARY LOGS...
       - Gathering SHOW REPLICAS...
       - Gathering SHOW MASTER STATUS...
       - Gathering SHOW REPLICA STATUS...
       - Gathering replication master_info...
       - Gathering replication relay_log_info...
       - Gathering pfs actors...
       - Gathering pfs objects...
       - Gathering pfs consumers...
       - Gathering pfs instruments...
       - Gathering pfs threads...
       - Gathering performance_schema.metadata_locks...
       - Gathering performance_schema.threads...
       - Gathering sys.schema_table_lock_waits...
       - Gathering sys.session_ssl_status...
       - Gathering sys.session...
       - Gathering sys.processlist...
       - Gathering performance_schema.events_waits_current...
       - Gathering information_schema.innodb_trx...
       - Gathering information_schema.innodb_metrics...
       - Gathering sys.memory_by_host_by_current_bytes...
       - Gathering sys.memory_by_thread_by_current_bytes...
       - Gathering sys.memory_by_user_by_current_bytes...
       - Gathering sys.memory_global_by_current_bytes...
       - Gathering SHOW GLOBAL STATUS...
       - Gathering SHOW ENGINE INNODB STATUS...
       - Gathering SHOW ENGINE PERFORMANCE_SCHEMA STATUS...
       - Gathering SHOW FULL PROCESSLIST...
       - Gathering SHOW OPEN TABLES...


      Diagnostics information was written to Users/yitao.xu/Downloads/Dia_report.zip


      输出报告一览：

        yitao.xu@yitao Dia_report % ls
        0.SHOW_BINARY_LOGS.tsv
        0.SHOW_BINARY_LOGS.yaml
        0.SHOW_ENGINE_INNODB_STATUS.tsv
        0.SHOW_ENGINE_INNODB_STATUS.yaml
        0.SHOW_ENGINE_PERFORMANCE_SCHEMA_STATUS.tsv
        0.SHOW_ENGINE_PERFORMANCE_SCHEMA_STATUS.yaml
        0.SHOW_FULL_PROCESSLIST.tsv
        0.SHOW_FULL_PROCESSLIST.yaml
        0.SHOW_GLOBAL_STATUS.tsv
        0.SHOW_GLOBAL_STATUS.yaml
        0.SHOW_MASTER_STATUS.tsv
        0.SHOW_MASTER_STATUS.yaml
        0.SHOW_OPEN_TABLES.tsv
        0.SHOW_OPEN_TABLES.yaml
        0.SHOW_REPLICAS.tsv
        0.SHOW_REPLICAS.yaml
        0.SHOW_REPLICA_STATUS.tsv
        0.SHOW_REPLICA_STATUS.yaml
        0.XA_RECOVER_CONVERT_xid.tsv
        0.XA_RECOVER_CONVERT_xid.yaml
        0.error_log.tsv
        0.global_variables.tsv
        0.global_variables.yaml
        0.information_schema.innodb_metrics.tsv
        0.information_schema.innodb_metrics.yaml
        0.information_schema.innodb_trx.tsv
        0.information_schema.innodb_trx.yaml
        0.instance
        0.metrics.tsv
        0.performance_schema.events_waits_current.tsv
        0.performance_schema.events_waits_current.yaml
        0.performance_schema.host_cache.tsv
        0.performance_schema.host_cache.yaml
        0.performance_schema.metadata_locks.tsv
        0.performance_schema.metadata_locks.yaml
        0.performance_schema.persisted_variables.tsv
        0.performance_schema.persisted_variables.yaml
        0.performance_schema.replication_applier_configuration.tsv
        0.performance_schema.replication_applier_configuration.yaml
        0.performance_schema.replication_applier_filters.tsv
        0.performance_schema.replication_applier_filters.yaml
        0.performance_schema.replication_applier_global_filters.tsv
        0.performance_schema.replication_applier_global_filters.yaml
        0.performance_schema.replication_applier_status.tsv
        0.performance_schema.replication_applier_status.yaml
        0.performance_schema.replication_applier_status_by_coordinator.tsv
        0.performance_schema.replication_applier_status_by_coordinator.yaml
        0.performance_schema.replication_applier_status_by_worker.tsv
        0.performance_schema.replication_applier_status_by_worker.yaml
        0.performance_schema.replication_asynchronous_connection_failover.tsv
        0.performance_schema.replication_asynchronous_connection_failover.yaml
        0.performance_schema.replication_asynchronous_connection_failover_managed.tsv
        0.performance_schema.replication_asynchronous_connection_failover_managed.yaml
        0.performance_schema.replication_connection_configuration.tsv
        0.performance_schema.replication_connection_configuration.yaml
        0.performance_schema.replication_connection_status.tsv
        0.performance_schema.replication_connection_status.yaml
        0.performance_schema.replication_group_member_stats.tsv
        0.performance_schema.replication_group_member_stats.yaml
        0.performance_schema.replication_group_members.tsv
        0.performance_schema.replication_group_members.yaml
        0.performance_schema.threads.tsv
        0.performance_schema.threads.yaml
        0.pfs_actors.tsv
        0.pfs_actors.yaml
        0.pfs_consumers.tsv
        0.pfs_consumers.yaml
        0.pfs_instruments.tsv
        0.pfs_instruments.yaml
        0.pfs_objects.tsv
        0.pfs_objects.yaml
        0.pfs_threads.tsv
        0.pfs_threads.yaml
        0.replication_master_info.tsv
        0.replication_master_info.yaml
        0.replication_relay_log_info.tsv
        0.replication_relay_log_info.yaml
        0.sys.memory_by_host_by_current_bytes.tsv
        0.sys.memory_by_host_by_current_bytes.yaml
        0.sys.memory_by_thread_by_current_bytes.tsv
        0.sys.memory_by_thread_by_current_bytes.yaml
        0.sys.memory_by_user_by_current_bytes.tsv
        0.sys.memory_by_user_by_current_bytes.yaml
        0.sys.memory_global_by_current_bytes.tsv
        0.sys.memory_global_by_current_bytes.yaml
        0.sys.processlist.tsv
        0.sys.processlist.yaml
        0.sys.schema_table_lock_waits.tsv
        0.sys.schema_table_lock_waits.yaml
        0.sys.session.tsv
        0.sys.session.yaml
        0.sys.session_ssl_status.tsv
        0.sys.session_ssl_status.yaml
        0.uri
        mysqlsh.log
        schema_routine_size.tsv
        schema_routine_size.yaml
        schema_table_count.tsv
        schema_table_count.yaml
        schema_tables_without_a_PK.tsv
        schema_tables_without_a_PK.yaml
        schema_unused_indexes.tsv
        schema_unused_indexes.yaml
        shell_info.yaml

        查看其中一个内容

          yitao.xu@yitao Dia_report % cat schema_tables_without_a_PK.yaml
          # Query:
          #  SELECT t.table_schema, t.table_name, t.table_rows, t.engine, t.data_length, t.index_length
          #              FROM information_schema.tables t
          #                LEFT JOIN information_schema.statistics s on t.table_schema=s.table_schema and t.table_name=s.table_name and s.index_name='PRIMARY'
          #              WHERE s.index_name is NULL and t.table_type = 'BASE TABLE'
          #                  and t.table_schema not in ('performance_schema', 'sys', 'mysql', 'information_schema')
          #
          # Started: 2022-10-19T10:09:59.931152
          # Execution Time: 0.3573 sec

          以上内容是关于MySQL诊断工具的一个简介，感兴趣的读者可以继续挖掘它的潜力。

          感谢关注“MySQL解决方案工程师”

          我的新书《MySQL高可用解决方案——从主从复制到InnoDB Cluster》已经上市，感兴趣的朋友可以通过下面的链接购买。京东现在有活动满100减50，满300再减50，可以叠加使用。


          mysqlshellschema
          文章转载自MySQL解决方案工程师，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

          评论