首页 > 数据库 >使用 MySQL Shell 获取 MySQL 诊断信息(译)

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

时间：2024-08-23 17:27:14浏览次数：9  
标签：... Gathering ROWS 诊断 Shell MySQL performance SELECT schema

收集全面的诊断信息可能会让人望而却步。知道要运行哪些查询以获取所需数据更像是一种艺术形式，而非其他什么。幸运的是，对于那些不太擅长艺术的人来说，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> show variables like '%slow_query_log%';
+---------------------+---------------------------------+
| Variable_name | Value |
+---------------------+---------------------------------+
| slow_query_log | ON |
| slow_query_log_file | /var/lib/mysql/centos7-slow.log |
+---------------------+---------------------------------+
2 rows in set (0.00 sec)

确认log_output参数是否为'TABLE':
mysql> set global log_output='TABLE';
Query OK, 0 rows affected (0.01 sec)
mysql> show variables like '%log_output%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output | TABLE |
+---------------+-------+
1 row in set (0.00 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
————————————————————————————

标签：...,Gathering,ROWS,诊断,Shell,MySQL,performance,SELECT,schema
From： https://www.cnblogs.com/shukuinfo/p/18376660

相关文章

  • docker对的tomcat、mysql、redis、nginx的安装
    本章篇章主要讲解了docker对常用软件的安装说明总体步骤：搜索镜像、拉取镜像、查看镜像、启动镜像、停止容器、移除容器tomcatdockerseachertomcat//也可以在dockerhub上面查找tomcat镜像dockerpulltomcat从dockerhub上拉取tomcat镜像到本地dockerimages//查看是否......
  • mysql8.x通过备份文件及binlog日志恢复数据
    问题简述记一次mysql数据库被误删（是整个库被删了）后的还原前提条件数据库版本为mysql8.x以上具有库被删除前的完整备份数据库开启binlog最近备份时间不能超过日志删除时间#查看数据库是否开启binlogshowvariableslike'log_bin';#默认binlog存储位置/var/lib/mysql......
  • docker mysql导入导出 nginx
    导出MySQL文件mysqldump--no-tablespaces-uroot-pabc>abc.sql导入MySQL文件mysql-uwpp-pvGgM701wWSBNwj8--default-character-set=utf8wpp<D:\wpp\nest\tmp\wpp.sql问题：docker中MySQL无法输入中文解决：dockerexec-itmysqlenvLANG=C.UTF-8/bin/bash将权限json......
  • MySQL日志
    MySQL日志MySQL日志类型MySQL日志有以下几种类型：错误日志：用于记录MySQL服务器启动、运行和停止时发生的错误的相关信息。二进制日志：记录了所有DDL和DML语句。查询日志：查询日志记录了客户端所有操作语句。慢查询日志：慢查询日志记录了所有执行时间超过参数long_que......
  • MySQL 持久化系统变量
    setpersist会将变量持久化到文件mysqld-auto.cnf文件中，该文件位于数据目录下。resetpersist会移除mysqld-auto.cnf文件中持久化的变量。 MySQL可以在运行时持久化全局系统变量。虽然许多系统变量可以在启动时通过my.cnf配置文件设置，或在运行时使用set语句设置，但这......
  • 重置MySQL表中自增字段的起始id
    导言在进行项目开发的时候，往往会自己编辑一些测试用例，但是这些用例会影响我们自增字段的起始id，虽然对我们项目没有多大影响，但是对于强迫症来说，不是从1顺着一个一个开始，感觉很不适应，所以特意搜了一下方法，然后记录一下处理方法mysql给我们提供了强大的函数功能，其中ALTERTABLE......
  • 自定义安装Mysql版本
    自定义安装Mysql版本mysql下载地址：https://downloads.mysql.com/archives/community/1.下载wgethttps://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.28-1.el7.x86_64.rpm-bundle.tar--2024-08-2010:15:39--https://downloads.mysql.com/archives/get/p/23/......
  • Python3测试mysql插入数据代码（chatgpt生成）
      实现的功能：先连接mysql数据库，然后读取某个目录所有以txt文件命名后缀的json内容文件，解析出对应的key和value，然后插入数据到mysql数据库，最后关闭数据库连接 importosimportjsonimportpymysqlimportre"""尝试插入json文件到MySQL数据库。dbInfo:MySQL数据库......
  • 利用ibd2sql直接读取mysql8数据文件数据
    MySQL8.0之后，表结构和表数据统一放到了ibd文件中，该文件包含了表的结构和索引以及数据信息。MySQL支持利用ibd2sdi来进行解析ibd文件下载地址：https://github.com/ddcw/ibd2sql/archive/refs/tags/v1.5.tar.gz[[email protected]]#ibd2sdi/data/mysqldata/data/test01/t......
  • Kettle: create_sampledata_mysql
    USEsampledate;CREATETABLEsampledata.QUADRANT_ACTUALS(REGIONVARCHAR(50)NOTNULL,DEPARTMENTVARCHAR(50)NOTNULL,POSITIONTITLEVARCHAR(50)NOTNULL,ACTUALDECIMAL(18,4),BUDGETDECIMAL(18,4),VARIANCEDECIMAL(18,4));CREATETABLEsampledata.DEPARTME......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99