使用 MySQL Shell 获取 MySQL 诊断信息(译)

收集全面的诊断信息可能会让人望而却步。知道要运行哪些查询以获取所需数据更像是一种艺术形式，而非其他什么。幸运的是，对于那些不太擅长艺术的人来说，MySQL Shell 使得获取这些信息变得更加容易。让我们来看一下。

设置
在我们开始之前，我们需要连接到一个 MySQL 实例。在本演示中，我正在使用一个生产 MySQL 数据库，用于我编写的帮助我管理高尔夫联赛的 Web 应用程序。

当我首次尝试获取诊断信息时，我收到一条消息，告诉我需要更改两个全局变量：slow_query_log 需要设置为 ON，log_output 需要设置为 TABLE。

将slow_query_log配置为ON：
mysql> set global slow_query_log=on;
Query OK, 0 rows affected (0.02 sec)

命令
我们运行的 MySQL Shell 命令以收集诊断信息是 util.debug.collectDiagnostics()。该方法接受两个参数。

数据文件将保存的路径。
这个参数是必需的。
这是运行 MySQL Shell 的机器上的路径，而不是我们连接到的服务器上的路径。
如果提供路径，将创建一个类似于以下名称的文件：mysql-diagnostics-<时间戳信息>.zip。
在这里您需要使用绝对路径。如果使用类似于 ~/path/to/folder 的路径，您将收到错误提示。
这是一个已知问题，并已报告。

一个选项 JSON 对象。
这个参数是可选的。
我针对我的 MySQL 实例运行的命令是：

需要将mysql shell切换到js模式下：
PROD MySQL:9.0.1 localhost:3306 ssl SQL > \js
PROD MySQL:9.0.1 localhost:3306 ssl JS > util.debug.collectDiagnostics("/root/", {slowQueries: true, });

完成后，此命令将在我的用户主目录下的 diag 文件夹中创建一个名为 mysql-diagnostics-<时间戳信息>.zip 的文件。收集的数据还将包括关于运行缓慢的查询的信息。

在完成此命令后，控制台的输出如下：
Collecting diagnostics information from mysql://root@localhost:3306...
Copying shell log file...
- Gathering schema tables without a PK...
- Gathering schema routine size...
- Gathering schema table count...
- Gathering schema unused indexes...
- Copying MySQL error log file (/var/log/mysqld.log)
- Gathering slow queries in 95 pctile...
- Gathering slow queries summary by rows examined...
- Gathering slow_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 BINARY LOG 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...
Collecting system information for centos7.9 (linux)
-> Executing date
-> Executing uname -a
-> Executing getenforce
-> Executing free -m
-> Executing swapon -s
-> Executing lsb_release -a
-> Executing mount -v
-> Executing df -h
-> Executing cat /proc/cpuinfo
-> Executing cat /proc/meminfo
-> Executing cat /etc/fstab
-> Executing mpstat -P ALL 1 4
-> Executing iostat -m -x 1 4
-> Executing vmstat 1 4
-> Executing top -b -n 4 -d 1
-> Executing ps aux
-> Executing ulimit -a
-> Executing for PID in `pidof mysqld`;do echo "# numastat -p $PID";numastat -p $PID;echo "# /proc/$PID/limits";cat /proc/$PID/limits;echo;done
-> Executing dmesg
-> Executing egrep -i 'err|fault|mysql' /var/log/*
-> Executing pvs
-> Executing pvdisplay
-> Executing vgs
-> Executing vgdisplay
-> Executing lvs
-> Executing lvdisplay
-> Executing netstat -lnput
-> Executing numactl --hardware
-> Executing numastat -m
-> Executing sysctl -a
-> Executing dmidecode -s system-product-name
-> Executing lsblk -i
-> Executing sudo sosreport
Diagnostics information was written to /root/mysql-diagnostics-20240823-142926.zip

这些信息
运行此命令后，在 diag 文件夹中我看到的文件如下：
[root@centos7 ~]# pwd
/root
[root@centos7 ~]# ls
anaconda-ks.cfg mysql9.0.1 mysql-diagnostics-20240823-142926.zip

诊断文件信息
我不得不承认，我对这个压缩文件的大小感到惊讶（略大于10MB）。尽管这个数据库已经使用了十多年，但它并不包含大量数据。

将ZIP文件解压缩：
[root@centos7 ~]# pwd
/root
[root@centos7 ~]# ls
anaconda-ks.cfg mysql9.0.1 mysql-diagnostics-20240823-142926.zip
[root@centos7 ~]# unzip mysql-diagnostics-20240823-142926.zip

文件列表
以下是包含在 .zip 文件中的文件列表。
0.error_log
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_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_applier_status.tsv
0.performance_schema.replication_applier_status.yaml
0.performance_schema.replication_asynchronous_connection_failover_managed.tsv
0.performance_schema.replication_asynchronous_connection_failover_managed.yaml
0.performance_schema.replication_asynchronous_connection_failover.tsv
0.performance_schema.replication_asynchronous_connection_failover.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.SHOW_BINARY_LOG_STATUS.tsv
0.SHOW_BINARY_LOG_STATUS.yaml
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_OPEN_TABLES.tsv
0.SHOW_OPEN_TABLES.yaml
0.SHOW_REPLICA_STATUS.tsv
0.SHOW_REPLICA_STATUS.yaml
0.SHOW_REPLICAS.tsv
0.SHOW_REPLICAS.yaml
0.slow_log.tsv
0.slow_log.yaml
0.slow_queries_in_95_pctile.tsv
0.slow_queries_in_95_pctile.yaml
0.slow_queries_summary_by_rows_examined.tsv
0.slow_queries_summary_by_rows_examined.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_ssl_status.tsv
0.sys.session_ssl_status.yaml
0.sys.session.tsv
0.sys.session.yaml
0.uri
0.XA_RECOVER_CONVERT_xid.tsv
0.XA_RECOVER_CONVERT_xid.yaml
host_info
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

即使考虑到许多文件是重复的（一个是 yaml 格式，一个是tsv制表符分隔格式），仅通过一个命令收集了大量信息。我们可以看到全局变量、复制信息、内存使用情况、二进制日志状态、慢查询信息（因为我们要求了）、以及大量其他数据的文件。

查看数据
让我们来看一下其中一些文件的内容。

全局变量
我将从 0.global_variables.tsv 开始。这是该文件的前几行（我不打算展示整个文件，因为它相当长）。
# Query:
# SELECT g.variable_name name, g.variable_value value /*!80000, i.variable_source source*/
# FROM performance_schema.global_variables g
# /*!80000 JOIN performance_schema.variables_info i ON g.variable_name = i.variable_name */
# ORDER BY name
#
# Started: 2024-08-23T14:29:26.582685
# Execution Time: 0.0216 sec
#
# name value source
activate_all_roles_on_login OFF COMPILED
admin_address COMPILED
admin_port 33062 COMPILED
admin_ssl_ca COMPILED
admin_ssl_capath COMPILED
admin_ssl_cert COMPILED
admin_ssl_cipher COMPILED
admin_ssl_crl COMPILED
admin_ssl_crlpath COMPILED
admin_ssl_key COMPILED
admin_tls_ciphersuites COMPILED
admin_tls_version TLSv1.2 COMPILED
authentication_policy *,, COMPILED
auto_generate_certs ON COMPILED
auto_increment_increment 1 COMPILED
auto_increment_offset 1 COMPILED

完整文件列出了我的服务器的所有全局变量。

当我第一次打开这个文件时，我很高兴地看到获取这些信息的查询包含在文件顶部。这样，如果我需要对系统进行任何更改，我可以通过运行查询来检查这些更改，而不是重新运行整个诊断收集过程。

我认为 yaml 文件更容易阅读。以下是与上述相同的变量，但以 yaml 格式显示。

# Query:
# SELECT g.variable_name name, g.variable_value value /*!80000, i.variable_source source*/
# FROM performance_schema.global_variables g
# /*!80000 JOIN performance_schema.variables_info i ON g.variable_name = i.variable_name */
# ORDER BY name
#
# Started: 2024-08-23T14:29:26.582685
# Execution Time: 0.0216 sec
#
name: activate_all_roles_on_login
source: COMPILED
value: 'OFF'
---
name: admin_address
source: COMPILED
value: ''
---
name: admin_port
source: COMPILED
value: '33062'
---
name: admin_ssl_ca
source: COMPILED
value: ''
---
name: admin_ssl_capath

二进制日志状态
有些文件可能包含很少的数据，但这并不意味着数据不重要。让我们来看看 0.SHOW_BINARY_LOG_STATUS.tsv。
# Query:
# SHOW BINARY LOG STATUS
#
# Started: 2024-08-23T14:29:26.844772
# Execution Time: 0.0004 sec
#
# File Position Binlog_Do_DB Binlog_Ignore_DB Executed_Gtid_Set
binlog.000004 158

这里是 yaml 版本。
# Query:
# SHOW BINARY LOG STATUS
#
# Started: 2024-08-23T14:29:26.844772
# Execution Time: 0.0004 sec
#
Binlog_Do_DB: ''
Binlog_Ignore_DB: ''
Executed_Gtid_Set: ''
File: binlog.000004
Position: 158
这些文件都向我们展示了当前二进制日志的名称以及文件中引用最后已提交事务的位置。

内存使用情况
有几个文件涉及内存使用情况。让我们来看看 0.sys.memory_by_user_by_current_bytes.tsv，该文件显示了每个数据库用户的内存使用情况。
# Query:
# select * from sys.memory_by_user_by_current_bytes
#
# Started: 2024-08-23T14:29:27.815393
# Execution Time: 0.0046 sec
#
# user current_count_used current_allocated current_avg_alloc current_max_alloc total_allocated
db_user 8893 6.15 MiB 724 bytes 3.00 MiB 62.96 MiB
background 8332 1.74 MiB 219 bytes 521.77 KiB 49.59 MiB
event_scheduler 3 16.27 KiB 5.42 KiB 16.04 KiB 16.27 KiB
root 8893 0.00 MiB 0 bytes 0.00 MiB 0.00 MiB

我们可以看到名为 db_user 的用户利用了最多的内存。这很可能是因为它是唯一一个在数据库上执行读写操作的定义用户。因为除非绝对必要，我不使用 root 用户（我甚至都记不得上次以 root 用户登录是什么时候），所以该用户当前并未使用任何资源。

再次强调，我觉得 yaml 文件更容易阅读。以下是该文件的内容。
# Query:
# Query:
# select * from sys.memory_by_user_by_current_bytes
#
# Started: 2024-08-23T14:29:27.815393
# Execution Time: 0.0046 sec
#
current_allocated: 6.15 MiB
current_avg_alloc: ' 724 bytes'
current_count_used: '8893'
current_max_alloc: 3.00 MiB
total_allocated: 62.96 MiB
user: db_user
---
current_allocated: 1.74 MiB
current_avg_alloc: ' 219 bytes'
current_count_used: '8332'
current_max_alloc: 521.77 KiB
total_allocated: 49.59 MiB
user: background
---
current_allocated: 16.27 KiB
current_avg_alloc: 5.42 KiB
current_count_used: '3'
current_max_alloc: 16.04 KiB
total_allocated: 16.27 KiB
user: event_scheduler
---
current_allocated: 0.00 MiB
current_avg_alloc: ' 0 bytes'
current_count_used: '0'
current_max_alloc: 0.00 MiB
total_allocated: 0.00 MiB
user: root

慢查询
由于我们特别要求慢查询数据，让我们来检查其中一个慢查询文件。以下是 0.slow_queries_in_95_pctile.tsv 文件的内容。
# Query:
# SELECT DIGEST, substr(DIGEST_TEXT, 1, 50), COUNT_STAR, SUM_ROWS_EXAMINED, SUM_ROWS_SENT, round(SUM_ROWS_SENT/SUM_ROWS_EXAMINED, 5) ratio FROM performance_schema.events_statements_summary_by_digest where DIGEST_TEXT like 'select%' and (SUM_ROWS_SENT/SUM_ROWS_EXAMINED) < .5 ORDER BY SUM_ROWS_EXAMINED/SUM_ROWS_SENT desc limit 20
#
# Started: 2024-08-23T14:29:26.467084
# Execution Time: 0.0480 sec
#
# DIGEST substr(DIGEST_TEXT, 1, 50) COUNT_STAR SUM_ROWS_EXAMINED SUM_ROWS_SENT ratio
e4651f6b5088748ce1023c151 SELECT ( SELECT COUNT ( `mrh` . `score` ) FROM `ma 2 2801588 2 0.0000
251d5e4fe1f4ae855c156c9dc SELECT ( SELECT COUNT ( `mrh` . `score` ) FROM `ma 740 728029115 740 0.0000
1af7c4e865a71bc17194c2ff3 SELECT DISTINCTROW `t` . `id` `teamid` FROM `team` 6 3606908 6 0.0000
50c8ff309af18cf64c6891391 SELECT ( SELECT COUNT ( `mrh` . `score` ) FROM `ma 211 28111768 211 0.0000
457c87a65271caa7673015854 SELECT DISTINCTROW `concat` ( `u` . `firstName` , 2 624724 10 0.0000
5872d26e9c861fe4b890f1706 SELECT `h` . `number` , `h` . `par` , AVG ( CASE W 4 2367448 72 0.0000
aa82179f2b7820ec7e073a0bd SELECT `h` . `number` , `m` . `datePlayed` , `conc 213 30731988 1704 0.0001
f354a030bb8eedcc7bf12c6f1 SELECT SUM ( CASE WHEN `match0_` . `hometeamId` = 264 4748832 264 0.0001
354003ae085979c9939b8125f SELECT ( SELECT COUNT ( `mrh` . `score` ) FROM `ma 211 2139541 211 0.0001
f354068ea3bc9cf3ae09e724e SELECT `mr` . `score` - `mr` . `handicap` `score` 740 28920334 3559 0.0001
79646e8a4b0fb1569508e8497 SELECT DISTINCTROW `mr` . `score` `score` , `m` . 2 91162 20 0.0002
4130fa152d490e4e31aab9f1b SELECT DISTINCTROW `mr` . `score` - `mr` . `handic 2 91162 20 0.0002
4269116398b6c366722d8075a SELECT `mr` . `score` , `DATE_FORMAT` ( `m` . `dat 740 15530542 3559 0.0002
9908e99c3924647a6d8e0665a SELECT COUNT ( * ) AS `col_0_0_` FROM `login_attem 120 353762 120 0.0003
2dc085027dc47d76e0f2d27e7 SELECT `u` . `full_name` , `gs` . `golfer_id` `id` 402 1115283 402 0.0004
7e4ae601e10d06aa56843671a SELECT COUNT ( * ) AS `col_0_0_` FROM `login_attem 2 5543 2 0.0004
5f7077e441cadcace2e154b54 SELECT `h` . `number` , `h` . `par` , AVG ( CASE W 2 82892 36 0.0004
d7f2a4c4fa9c98c475d5de9ad SELECT `h` . `number` , `h` . `par` , AVG ( CASE W 2 82892 36 0.0004
4f20adb6f7fd8603bd5aff769 SELECT COUNT ( DISTINCTROW `user1_` . `id` ) AS `c 2 4397 2 0.0005
e38ec7bca0dc715113e556fed SELECT `h` . `number` , `h` . `par` , AVG ( CASE W 422 12355884 7596 0.0006

对于较长的查询，我们可能看不到整个查询，但我们可以获取足够的信息来在代码中识别查询。以下是 yaml 版本，这样您可以更轻松地查看数据。
# Query:
# SELECT DIGEST, substr(DIGEST_TEXT, 1, 50), COUNT_STAR, SUM_ROWS_EXAMINED, SUM_ROWS_SENT, round(SUM_ROWS_SENT/SUM_ROWS_EXAMINED, 5) ratio FROM performance_schema.events_statements_summary_by_digest where DIGEST_TEXT like 'select%' and (SUM_ROWS_SENT/SUM_ROWS_EXAMINED) < .5 ORDER BY SUM_ROWS_EXAMINED/SUM_ROWS_SENT desc limit 20
#
# Started: 2024-08-23T14:29:26.467084
# Execution Time: 0.0480 sec
#
COUNT_STAR: 2
DIGEST: e4651f6b5088748ce1023c151
SUM_ROWS_EXAMINED: 2801588
SUM_ROWS_SENT: 2
ratio: '0.0000'
substr(DIGEST_TEXT, 1, 50): SELECT ( SELECT COUNT ( `mrh` . `score` ) FROM `ma
---
COUNT_STAR: 740
DIGEST: 251d5e4fe1f4ae855c156c9dc
SUM_ROWS_EXAMINED: 728029115
SUM_ROWS_SENT: 740
ratio: '0.0000'
substr(DIGEST_TEXT, 1, 50): SELECT ( SELECT COUNT ( `mrh` . `score` ) FROM `ma
---
COUNT_STAR: 6
DIGEST: 1af7c4e865a71bc17194c2ff3
SUM_ROWS_EXAMINED: 3606908
SUM_ROWS_SENT: 6
ratio: '0.0000'
substr(DIGEST_TEXT, 1, 50): SELECT DISTINCTROW `t` . `id` `teamid` FROM `team`
---
COUNT_STAR: 211
DIGEST: 50c8ff309af18cf64c6891391
SUM_ROWS_EXAMINED: 28111768
SUM_ROWS_SENT: 211
ratio: '0.0000'
substr(DIGEST_TEXT, 1, 50): SELECT ( SELECT COUNT ( `mrh` . `score` ) FROM `ma
---
COUNT_STAR: 2
DIGEST: 457c87a65271caa7673015854
SUM_ROWS_EXAMINED: 624724
SUM_ROWS_SENT: 10
ratio: '0.0000'
substr(DIGEST_TEXT, 1, 50): 'SELECT DISTINCTROW `concat` ( `u` . `firstName` , '
---
COUNT_STAR: 4
DIGEST: 5872d26e9c861fe4b890f1706
SUM_ROWS_EXAMINED: 2367448
SUM_ROWS_SENT: 72
ratio: '0.0000'
substr(DIGEST_TEXT, 1, 50): SELECT `h` . `number` , `h` . `par` , AVG ( CASE W
---
COUNT_STAR: 213
DIGEST: aa82179f2b7820ec7e073a0bd
SUM_ROWS_EXAMINED: 30731988
SUM_ROWS_SENT: 1704
ratio: '0.0001'
substr(DIGEST_TEXT, 1, 50): SELECT `h` . `number` , `m` . `datePlayed` , `conc
---
COUNT_STAR: 264
DIGEST: f354a030bb8eedcc7bf12c6f1
SUM_ROWS_EXAMINED: 4748832
SUM_ROWS_SENT: 264
ratio: '0.0001'
substr(DIGEST_TEXT, 1, 50): 'SELECT SUM ( CASE WHEN `match0_` . `hometeamId` = '
---
COUNT_STAR: 211
DIGEST: 354003ae085979c9939b8125f
SUM_ROWS_EXAMINED: 2139541
SUM_ROWS_SENT: 211
ratio: '0.0001'
substr(DIGEST_TEXT, 1, 50): SELECT ( SELECT COUNT ( `mrh` . `score` ) FROM `ma
---
COUNT_STAR: 740
DIGEST: f354068ea3bc9cf3ae09e724e
SUM_ROWS_EXAMINED: 28920334
SUM_ROWS_SENT: 3559
ratio: '0.0001'
substr(DIGEST_TEXT, 1, 50): 'SELECT `mr` . `score` - `mr` . `handicap` `score` '
---
COUNT_STAR: 2
DIGEST: 79646e8a4b0fb1569508e8497
SUM_ROWS_EXAMINED: 91162
SUM_ROWS_SENT: 20
ratio: '0.0002'
substr(DIGEST_TEXT, 1, 50): 'SELECT DISTINCTROW `mr` . `score` `score` , `m` . '
---
COUNT_STAR: 2
DIGEST: 4130fa152d490e4e31aab9f1b
SUM_ROWS_EXAMINED: 91162
SUM_ROWS_SENT: 20
ratio: '0.0002'
substr(DIGEST_TEXT, 1, 50): SELECT DISTINCTROW `mr` . `score` - `mr` . `handic
---
COUNT_STAR: 740
DIGEST: 4269116398b6c366722d8075a
SUM_ROWS_EXAMINED: 15530542
SUM_ROWS_SENT: 3559
ratio: '0.0002'
substr(DIGEST_TEXT, 1, 50): SELECT `mr` . `score` , `DATE_FORMAT` ( `m` . `dat
---
COUNT_STAR: 120
DIGEST: 9908e99c3924647a6d8e0665a
SUM_ROWS_EXAMINED: 353762
SUM_ROWS_SENT: 120
ratio: '0.0003'
substr(DIGEST_TEXT, 1, 50): SELECT COUNT ( * ) AS `col_0_0_` FROM `login_attem
---
COUNT_STAR: 402
DIGEST: 2dc085027dc47d76e0f2d27e7
SUM_ROWS_EXAMINED: 1115283
SUM_ROWS_SENT: 402
ratio: '0.0004'
substr(DIGEST_TEXT, 1, 50): SELECT `u` . `full_name` , `gs` . `golfer_id` `id`
---
COUNT_STAR: 2
DIGEST: 7e4ae601e10d06aa56843671a
SUM_ROWS_EXAMINED: 5543
SUM_ROWS_SENT: 2
ratio: '0.0004'
substr(DIGEST_TEXT, 1, 50): SELECT COUNT ( * ) AS `col_0_0_` FROM `login_attem
---
COUNT_STAR: 2
DIGEST: 5f7077e441cadcace2e154b54
SUM_ROWS_EXAMINED: 82892
SUM_ROWS_SENT: 36
ratio: '0.0004'
substr(DIGEST_TEXT, 1, 50): SELECT `h` . `number` , `h` . `par` , AVG ( CASE W
---
COUNT_STAR: 2
DIGEST: d7f2a4c4fa9c98c475d5de9ad
SUM_ROWS_EXAMINED: 82892
SUM_ROWS_SENT: 36
ratio: '0.0004'
substr(DIGEST_TEXT, 1, 50): SELECT `h` . `number` , `h` . `par` , AVG ( CASE W
---
COUNT_STAR: 2
DIGEST: 4f20adb6f7fd8603bd5aff769
SUM_ROWS_EXAMINED: 4397
SUM_ROWS_SENT: 2
ratio: '0.0005'
substr(DIGEST_TEXT, 1, 50): SELECT COUNT ( DISTINCTROW `user1_` . `id` ) AS `c
---
COUNT_STAR: 422
DIGEST: e38ec7bca0dc715113e556fed
SUM_ROWS_EXAMINED: 12355884
SUM_ROWS_SENT: 7596
ratio: '0.0006'
substr(DIGEST_TEXT, 1, 50): SELECT `h` . `number` , `h` . `par` , AVG ( CASE W

我注意到一些查询中检查的行数与返回的行数相比有很大差异。我需要进一步调查并调整查询或架构以提高性能。

总结
通过 MySQL，我们可以收集大量关于数据库服务器和架构的信息。通常我们会运行多个查询来收集这些信息。使用 MySQL Shell，我们可以通过一个命令获取比我们可能需要的更多诊断信息。这些信息以制表符分隔的格式和 yaml 格式提供。要了解更多关于收集诊断信息或运行 util.debug.collectDiagnostics() 时可用选项，请查看文档。

转载：https://blogs.oracle.com/mysql/post/using-mysql-shell-to-get-mysql-diagnostic-information

文章看完了，如果觉得本文对您的工作或生活有用，希望分享给你身边的朋友，一起学习，共同进步哈~~~

欢迎关注我的公众号【数库信息技术】，你的关注是我写作的动力源泉

各大平台都可以找到我：
————————————————————————————
公众号：数库信息技术
墨天轮：https://www.modb.pro/u/427810
百家号：https://author.baidu.com/home/1780697309880431
CSDN ：https://blog.csdn.net/rscpass
51CTO: https://blog.51cto.com/u_16068254
博客园：https://www.cnblogs.com/shukuinfo
知乎:https://www.zhihu.com/people/shukuinfo
————————————————————————————

posted on 2024-08-23 17:28 数据库帮帮团阅读(17) 评论(0) 编辑收藏举报

刷新页面返回顶部

shukuinfo

使用 MySQL Shell 获取 MySQL 诊断信息(译)

导航

公告