MySQL OCP888题解022-performance_schema.threads表和SHOW FULL PROCESSLIST语句

最新推荐文章于 2023-07-13 15:11:33 发布

豪杰笑开怀

最新推荐文章于 2023-07-13 15:11:33 发布

阅读量566

点赞数 1

分类专栏： MySQL OCP888题解(MySQL5.7) 文章标签： mysql 数据库 ocp

本文链接：https://blog.csdn.net/oddrock/article/details/129121207

版权

MySQL OCP888题解(MySQL5.7) 专栏收录该内容

78 篇文章 13 订阅

订阅专栏

1、原题

1.1、英文原题

You want to use the Performance Schema to get the equivalent of SHOW FULL PROCESSLIST.Which three statements are true?
A. The events_statements_current consumer must be enabled.
B. The PROCESS privilege is required.
C. The sys.session view provides the process list with additional information.
D. The threads table contains all the required columns.
E. A connection must be instrumented to be included in the list of processes.
F. The performance impact is less than SHOW FULL PROCESSLIST.

1.2、中文翻译

您希望使用性能模式来获得SHOW FULL PROCESSLIST的等价内容，哪三个陈述是正确的？
A. 必须启用events_statements_current consumer。
B. 需要PROCESS权限。
C. sys.session视图为进程列表提供了附加信息。
D. 线程表包含所有必需的列。
E. 必须对连接进行仪器化，以便将其包含在进程列表中。
F. 性能影响小于SHOW FULL PROCESSLIST。

1.3、答案

D、E、F

2、题目解析

2.1、题干解析

本题考察性能模式相关的知识。

2.2、选项解析

2.2.1、选项A解析

events_statements_current表包含当前正在执行的语句，而不是线程，所以和SHOW FULL PROCESSLIST没有直接关系。所以选项A错误。

2.2.2、选项B解析

如果有PROCESS权限，用户可以看到所有的线程，包括属于其他用户的线程。如果没有PROCESS权限，非匿名用户可以看到自己的线程信息，但不能看到其他用户的线程。所以PROCESS权限并不是使用性能模式必须的，也不是使用SHOW FULL PROCESSLIST语句必须的。所以选项B错误。

2.2.3、选项C解析

sys. session视图与processlist相似，但是session视图过滤掉了后台进程，只显示用户会话。所以选项C错误。

3、知识点

3.1、知识点1：processlist表

MySQL的processlist(进程列表)表明当前正在由服务器内执行的线程组。对于新安装的MySQL 5.7.39或更高版本，processlist表会在性能模式中自动创建。processlist表有如下列：

ID：连接标识符，对于Performance Schema的线程(threads)表中的PROCESSLIST_ID列中。
USER：发出该语句的MySQL用户。
- 如果值为system，代表是由服务器生成的处理内部任务的非客户线程，例如，延迟行处理程序线程或复制主机上使用的I/O或SQL线程。对于system，在Host列中没有指定主机。
- unauthenticated user指的是一个与客户连接相关的线程，但是客户用户的认证还没有发生。
- event_scheduler指的是监视计划事件的线程。
HOST：发出语句的客户端的主机名（system用户除外，它没有主机）。TCP/IP连接的主机名以host_name:client_port格式报告，以方便确定哪个客户在做什么。
DB：该线程的默认数据库，如果没有选择，则为空。
COMMAND：线程代表客户执行的命令的类型，如果会话是空闲的，则为Sleep。
TIME：线程处于当前状态的时间，单位是秒。对于一个复制的SQL线程，该值是最后一个复制事件的时间戳和复制主机的实际时间之间的秒数。
STATE：表示线程正在做什么的一个动作、事件或状态。
INFO：线程正在执行的语句，如果它没有执行任何语句，则为NULL。该语句可能是发送到服务器的语句，或者是最内层的语句，如果该语句执行其他语句。例如，如果一个CALL语句执行一个正在执行SELECT语句的存储过程，INFO值显示SELECT语句。

默认的SHOW PROCESSLIST实现从线程管理器中遍历活动线程，同时持有一个全局互斥。这对性能有负面影响，特别是在繁忙的系统上。另一种SHOW PROCESSLIST实现是基于Performance Schema processlist表的。这个实现从Performance Schema而不是线程管理器中查询活动线程数据，并且不需要互斥。

官方参考文档

3.2、知识点2：SHOW PROCESSLIST语句

SHOW PROCESSLIST语句能够显示MySQL进程列表，表明当前正在由服务器内执行的线程集执行的操作。

如果你有PROCESS权限，你可以看到所有的线程，甚至那些属于其他用户的线程。否则（没有PROCESS权限），非匿名用户可以看到自己的线程信息，但不能看到其他用户的线程，而匿名用户不能看到线程信息。
如果没有FULL关键字，SHOW PROCESSLIST在INFO字段中只显示每个语句的前100个字符。
如果你得到 "too many connections "的错误信息并想知道发生了什么，SHOW PROCESSLIST语句非常有用。MySQL保留了一个额外的连接，由拥有SUPER权限的账户使用，以确保管理员应该总是能够连接和检查系统。线程可以用KILL语句杀死。

官方参考文档

3.3、知识点3：events_statements_current表

events_statements_current表包含当前的语句事件。该表每个线程存储一行，显示线程最近监测的语句事件的当前状态。在包含语句事件行的表中，events_statements_current是最基本的。其他包含语句事件行的表在逻辑上是由当前事件派生的。例如，events_statements_history和events_statements_history_long表是最近结束的语句事件的集合，分别达到了每个线程和全局所有线程的最大行数。

官方参考文档

对于等待、阶段、语句和事务事件，性能模式可以监控和存储当前的事件。此外，当事件结束时，性能模式可以将它们存储在历史表中。对于每个事件类型，性能模式使用三个表来存储当前和历史事件。这些表有以下形式的名称，其中xxx表示事件类型（等待、阶段、语句、事务）。

events_xxx_current：“当前事件”表存储每个线程的当前监控事件（每个线程有一行）。
events_xxx_history：“最近的历史” 表存储每个线程已经结束的最近的事件（每个线程最多有几行）。
events_xxx_history_long：“长历史”表存储了全局范围内已经结束的最新事件（跨越所有线程，每张表最多可有几行）。

官方参考文档

3.4、知识点4：threads表

线程表中的每一行代表一个服务器线程，并指出是否为其启用了监控和历史事件记录。

当性能模式初始化时，它根据当时存在的线程来填充线程表。
此后，每当服务器创建一个线程时，就会添加一条新行。
当线程结束时，会从线程表中删除行。

threads表的列如下所示：

官方参考文档

3.5、知识点5：setup_actors表

setup_actors表包含的信息决定了是否为新的前台服务器线程（与客户端连接相关的线程）启用监控和历史事件记录。这个表的最大尺寸默认为100行。要改变表的大小，请在服务器启动时修改performance_schema_setup_actors_size系统变量。
对于每个新的前台线程，性能模式将线程的用户和主机与setup_actors表的行进行匹配。如果该表的某条记录相匹配，其ENABLED和HISTORY列的值将被用于设置线程表行的INSTRUMENTED和HISTORY列。这使得仪表和历史事件记录可以有选择地应用于每个主机、用户或账户（用户和主机组合）。如果没有匹配，线程的INSTRUMENTED和HISTORY列被设置为NO。
对于后台线程，没有相关的用户。INSTRUMENTED和HISTORY默认为YES，setup_actors不被查询。
setup_actors表的初始内容与任何用户和主机的组合相匹配，因此监控和历史事件收集默认为所有前台线程启用，如下所示：

mysql> SELECT * FROM performance_schema.setup_actors;
+------+------+------+---------+---------+
| HOST | USER | ROLE | ENABLED | HISTORY |
+------+------+------+---------+---------+
| %    | %    | %    | YES     | YES     |

官方参考文档

3.6、知识点6：性能模式

官方参考文档

3.6.1、be instrumented（被仪器化）

所谓be instrumented，意思是被仪器化，也就是类似被埋了点，可以进行监控。

3.6.2、性能模式对服务器性能的影响

性能模式的目的是提供对服务器执行的有用信息的访问，同时对服务器性能的影响最小。该实现遵循以下这些设计目标。
- 激活性能模式不会导致服务器行为的改变。例如，它不会导致线程调度的改变，也不会导致查询执行计划（如EXPLAIN所示）的改变。
- 服务器监控持续地、不引人注意地发生，而且开销非常小。激活性能模式并不会使服务器无法使用。
- 解析器没有变化。没有新的关键字或语句。
- 即使性能模式在内部失败，服务器代码的执行也会正常进行。

3.6.3、性能表的特性

performance_schema数据库的名称是小写的，其中的表的名称也是小写的。查询应该用小写来指定名称。
performance_schema数据库中的许多表是只读的，不能被修改。
要从performance_schema表中检索，你必须有SELECT权限。
要改变那些可以修改的列，你必须有UPDATE的权限。
要截断那些可以被截断的表，你必须有DROP权限。
使用GRANT ALL授权会出现错误，应该使用如下授权方式：

mysql> GRANT SELECT ON performance_schema.* TO 'u1'@'localhost';
mysql> GRANT SELECT, UPDATE ON performance_schema.setup_instruments TO 'u2'@'localhost';

官方参考文档

3.7、知识点7：访问进程列表的方法

如下方法都可以访问进程列表：

SHOW PROCESSLIST 语句。
mysqladmin processlist命令。
INFORMATION_SCHEMA的PROCESSLIST表。
性能模式的processlist表。
性能模式的Threads表的列，其名称的前缀是PROCESSLIST_。
sys模式的processlist和session视图。

线程表与SHOW PROCESSLIST、INFORMATION_SCHEMA PROCESSLIST和mysqladmin processlist相比，如下所示：

对线程表的访问不需要mutex（锁），对服务器性能的影响很小。其他来源会对性能产生负面影响，因为它们需要一个mutex。
线程表显示后台线程，而其他来源则没有。它还为每个线程提供了其他来源所没有的额外信息，例如，该线程是前台还是后台线程，以及与该线程相关的服务器内位置。这意味着，线程表可以用来监控其他来源无法监控的线程活动。由于这些原因，使用其他线程信息源之一进行服务器监控的DBA可能希望使用线程表进行监控。
sys.processlist视图以一种更容易理解的格式显示来自Performance Schema线程表的信息。sys. session视图与sys.processlist视图一样，显示了关于用户会话的信息，但是过滤掉了后台进程。

官方参考文档

3.8、知识点8：sys模式

3.8.1、sys模式的基本介绍

sys模式包含了一组对象，帮助DBA和开发人员解释由performance_schema收集的数据。

sys模式的对象可用于典型的调整和诊断用例，这些对象包括：
- 视图：将performance_schema数据总结为更容易理解的形式。
- 存储过程：执行诸如performance_schema配置和生成诊断报告等操作。
- 存储函数：可以查询performance_schema配置并提供格式化服务。
对于新的安装，如果你使用mysqld的-initialize或-initialize-insecure选项，sys模式默认在数据目录初始化期间安装。如果不需要，你可以在初始化后手动删除sys模式。

官方参考文档

3.8.2、sys模式的使用

sys模式包含了许多视图，这些视图以不同的方式总结了Performance模式的表。这些视图中的大多数都是成对出现的，比如一对中的一个成员与另一个成员的名字相同，再加上一个x$前缀。

例如，host_summary_by_file_io视图总结了按主机分组的文件I/O，并显示了从皮秒转换为更易读的值（带单位）的延迟：SELECT * FROM sys.host_summary_by_file_io;
x $host_summary_by_file_io视图总结了相同的数据，但显示未格式化的皮秒延迟：SELECT * FROM sys.x$ host_summary_by_file_io;
不含x $前缀的视图旨在提供更友好的输出，使人类更容易阅读。带有 x$ 前缀的视图以原始形式显示相同的值，目的是为了与其他对数据进行处理的工具一起使用。

要检查sys schema对象的定义，使用适当的SHOW语句或INFORMATION_SCHEMA查询。例如，要检查会话视图和 format_bytes()函数的定义，使用这些语句。这些语句以相对无格式化的形式显示定义。要查看具有更多可读格式的对象定义，可访问MySQL源分发中scripts/sys_schema下发现的单个.sql文件。

mysql> SHOW CREATE VIEW sys.session \G
mysql> SHOW CREATE FUNCTION sys.format_bytes \G’

mysqldump和mysqlpump默认都不转储sys模式。要生成一个转储文件，请在命令行中使用这些命令中的任何一个明确命名sys模式。

mysqldump --databases --routines sys > sys_dump.sql
mysqlpump sys > sys_dump.sql
然后再加载，使用这个命令：mysql < sys_dump.sql

session/ x $p rocess l i s t 视图与 p rocess l i s t / x$ processlist相似，但是session/ x$processlist视图过滤掉了后台进程，只显示用户会话。

4、实验

4.1、实验1

4.1.1、实验目的

验证threads表的字段包含了SHOW FULL PROCESSLIST的所有字段

4.1.2、实验前准备

已安装并正常运行的MySQL5.7实例。

4.1.3、实验步骤

查询threads表：
mysql> SELECT * FROM performance_schema.threads\G
*************************** 1. row ***************************
THREAD_ID: 26
NAME: thread/sql/slave_sql
TYPE: FOREGROUND
PROCESSLIST_ID: 2
PROCESSLIST_USER: NULL
PROCESSLIST_HOST: NULL
PROCESSLIST_DB: NULL
PROCESSLIST_COMMAND: Connect
PROCESSLIST_TIME: 3671
PROCESSLIST_STATE: Slave has read all relay log; waiting for more updates
PROCESSLIST_INFO: NULL
PARENT_THREAD_ID: 1
ROLE: NULL
INSTRUMENTED: YES
HISTORY: YES
CONNECTION_TYPE: NULL
THREAD_OS_ID: 1703
运行SHOW FULL PROCESSLIST语句：
mysql> SHOW FULL PROCESSLIST \G
*************************** 2. row ***************************
Id: 2
User: system user
Host:
db: NULL
Command: Connect
Time: 3675
State: Slave has read all relay log; waiting for more updates
Info: NULL
可以看到threads表包含了SHOW FULL PROCESSLIST语句的所有列

4.1.4、实验结论

threads表的字段包含了SHOW FULL PROCESSLIST的所有字段

4.2、实验2

4.2.1、实验目的

验证查询performance_schema的表不需要PROCESS权限，只需要SELECT权限。

4.2.2、实验前准备

已安装并正常运行的MySQL5.7实例。

4.2.3、实验步骤

创建一个新用户user2，授予其对performance_schema的查询权限：

mysql> CREATE USER user2 IDENTIFIED BY '000000';
mysql> GRANT SELECT ON performance_schema.* TO user2;

查看user2已拥有的权限，可以看到user2只有SELECT和USAGE权限：

mysql> SHOW GRANTS FOR user2;
+-------------------------------------------------------+
| Grants for user2@%                                    |
+-------------------------------------------------------+
| GRANT USAGE ON *.* TO 'user2'@'%'                     |
| GRANT SELECT ON `performance_schema`.* TO 'user2'@'%' |

使用user2登录，再查看Threads表，发现可以查询成功。说明只要有SELECT权限，不需要有PROCESS权限，就可以查询性能表。

shell> mysql -uuser2 -p000000
mysql: [Warning] Using a password on the command line interface can be insecure.
......
mysql> SELECT THREAD_ID,NAME FROM performance_schema.threads LIMIT 1;
+-----------+-----------------+
| THREAD_ID | NAME            |
+-----------+-----------------+
|         1 | thread/sql/main |

4.2.4、实验结论

查询performance_schema的表不需要PROCESS权限，只需要SELECT权限。

5、总结

要想查看MySQL中正在活动的线程，可以通过performance模式的threads表、SHOW FULL PROCESSLIST语句、sys模式的processlist视图等方式，但字段最全、对服务器性能影响最小的方式是threads表。
MySQL的sys模式包含了很多视图，是对performance模式中的表的内容的优化展示，看起来更方便。

豪杰笑开怀

关注

1
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
MySQL OCP888题解022-performance_schema.threads表和SHOW FULL PROCESSLIST语句

MySQL OCP888题解022-performance_schema.threads表和SHOW FULL PROCESSLIST语句
复制链接

扫一扫