📝 面试求职： 「面试试题小程序」 ，内容涵盖 测试基础、Linux操作系统、MySQL数据库、Web功能测试、接口测试、APPium移动端测试、Python知识、Selenium自动化测试相关、性能测试、性能测试、计算机网络知识、Jmeter、HR面试，命中率杠杠的。（大家刷起来…）
📝 职场经验干货：
当你在MySQL高并发情况下的进行性能调优时，需要知道调整后的影响。例如查询是否变快了？锁是否会减慢运行速度？内存使用情况如何？磁盘IO等待时间变了吗？
Performance Schema就有一个存储回答上述问题所需数据的数据库。本篇文章将帮你了解Performance Schema的工作原理、局限性，以及如何更好地使用它和sys Schema来搞清楚MySQL内部的运行细节。
01 Performance Schema介绍
介绍Performance Schema的工作机制前，先解释两个概念：
-
程序插桩(instrument)： 指在MySQL代码中插入探测代码，以获取我们想了解的信息(也就是生产数据的代码，即所需数据的提供者、生产者)。就像一般的的链路监控框架，如Google的Dapper就是通过在代码中插入探测代码的方式获取信息。(一般Java链路监控框架是通过代理的方式修改字节码文件, 在代码中间插入获取信息的代码。)
-
消费者表(consumer)： 指的是存储关于程序插桩代码信息的表。如果我们为查询模块添加插桩，相应的消费者表将记录如执行总数、未使用索引次数、耗时等信息。如图MySQL存在名为performance_schema的内置数据库，里面有各种数据表存储了有关MySQL内部运行的操作上的底层指标。
Performance Schema的一般功能如下图所示
当应用程序用户连接到MySQL并执行被测量的插桩指令时，performance_schema将每个检查的调用封装到两个宏中，然后将结果记录在相应的消费者表中。这里的要点是，启用插桩会调用额外的代码，这意味着插桩会消耗CPU资源。
1.1 插桩元件
在这里插桩元件可以理解为收集信息的组件(也就是 数据生产者 )，是插入的“桩”与“桩的底座”的组合元件，每一个组件都代表着一些代码(收集信息💻的代码)。 在performance_schema库的setup_instruments表中包含MySQL所有的插桩组件。
MySQL官方手册setup_instruments表介绍
-- setup_instruments表信息
mysql> SELECT * FROM performance_schema.setup_instruments where DOCUMENTATION is not null limit 5,5\G;
*************************** 1. row ***************************
NAME: wait/synch/mutex/refcache/refcache_channel_mutex
ENABLED: NO
TIMED: NO
PROPERTIES:
VOLATILITY: 0
DOCUMENTATION: A mutex to guard access to the channels list
*************************** 2. row ***************************
NAME: wait/synch/rwlock/pfs/LOCK_pfs_tls_channels
ENABLED: NO
TIMED: NO
PROPERTIES: singleton
VOLATILITY: 0
DOCUMENTATION: This lock protects list of instrumented TLS channels.
*************************** 3. row ***************************
NAME: statement/sql/error
ENABLED: YES
TIMED: YES
PROPERTIES:
VOLATILITY: 0
DOCUMENTATION: Invalid SQL queries (syntax error).
*************************** 4. row ***************************
NAME: statement/abstract/Query
ENABLED: YES
TIMED: YES
PROPERTIES: mutable
VOLATILITY: 0
DOCUMENTATION: SQL query just received from the network. At this point, the real statement type is unknown, the type will be refined after SQL parsing.
*************************** 5. row ***************************
NAME: statement/abstract/new_packet
ENABLED: YES
TIMED: YES
PROPERTIES: mutable
VOLATILITY: 0
DOCUMENTATION: New packet just received from the network. At this point, the real command type is unknown, the type will be refined after reading the packet header.NAME列： 组件名称构成: [插桩类型 /通用系统/子系统/孙系统/…], 例如 statement/sql/select 表示 statement类型sql系统下的select,select是sql子系统的一部分，属于statement类型。
DOCUMENTATION列： 由上面查询结果可见setup_instruments表有一个DOCUMENTATION列，包含详细信息。但许多插桩而言，该列可能为空，因此需要根据插桩的名称、你的直觉以及对MySQL源代码的认识来理解特定插桩检查的内容。
1.2 消费者表的分类
正如前面提到的，消费者表是插桩发送信息的目的地。结果存储在performance_schema数据库的多个表中；在MySQL 8.0.25社区版的performance_schema中由110个消费者表。基于它们的用途，大概可分为以下几个类别。
1、当前和历史数据 __history和history_long表的大小是可配置的
2、汇总表和摘要
汇总表 存储的是有关数据的统计信息，
例如，memory_summary_by_thread_by_event_name表保存了用户连接或任何后台线程的每个MySQL线程的内存聚合结果。
摘要 是一种通过删除查询中的变量来聚合查询的方法。
例如以下查询：
SELECT user,birthdate FROM users WHERE user_id=19;
SELECT user,birthdate FROM users WHERE user_id=13;
SELECT user,birthdate FROM users WHERE user_id=27;该查询的摘要是：
SELECT user,birthdate FROM users WHERE user_id=?;这允许Performance Schema跟踪摘要的延迟等指标，而无须单独保留 查询的每个变体。
3、实例表(Instance)
实例是指对象实例，用于MySQL安装程序。例如，file_instances表包含文件名和访问这些文件的线程数。
4、设置表(Setup)
设置表用于performance_schema的运行时设置。
5、其他表
还有一些表的名称没有遵循严格的模式。例如，metadata_locks表保存关于元数据锁的数据。在本章后面讨论performance_schema可以解决的问题时，将介绍其中几个表。
1.3 资源消耗
消费者表使用的内存量可以设置。performance_schema中的一些表支持自动伸缩，这意味着它们在启动时分配最小数量的内存，并根据需要调整其大小。然而，一旦分配了内存，即使禁用了特定的插桩并截断了表，也不会再释放该内存。
如前所述，每个插桩指令的调用都会再添加两个宏调用，以将数据存储在performance_schema中。这意味着插桩越多，CPU的使用率就越高。对CPU使用率的实际影响取决于特定的插桩。例如，与statement相关的插桩在查询过程中只能被调用一次，而wait类插桩的被调用频率要高得多。例如，要扫描一个有一百万行的InnoDB表，引擎需要设置和释放一百万行锁。如果对锁使用wait类插桩，CPU使用率可能会显著增加。但是，同一查询本来就需要一次调用来确定是否是statement/sql/select。因此，如果启用statement类插桩，你不会注意到CPU负载的任何增加。内存或元数据锁类型的插桩也是如此。
1.4 局限性
-
它只在特定的插桩和用户启用后才收集数据。
例如，如果在禁用所有插桩的情况下启动服务器，然后决定检测内存使用情况，则无法知道全局缓冲区（如InnoDB缓冲池）分配的确切数量，因为在启用内存插桩之前已分配了该缓冲区。 （就像csdn个人主页的点赞数，如果你发了一篇文章之后才开启对你的数据统计，那么之前那篇文章的数据将统计不到。）
-
很难释放内存
可以在启动时限制消费者表的大小，也可以让其自动调整大小。在后一种情况下，它们不会在启动时分配内存，而是仅在收集启用的数据时分配内存。但是，即使以后禁用了特定的插桩或消费者表，也不会释放内存，除非重新启动服务器。
1.5 sys Schema
自5.7版以来，标准MySQL发行版包括一个和performance_schema数据配套使用的sys schema，它全部基于performance_schema上的视图和存储组成。它的设计目的是让performance_schema体验更加流畅 ，它本身并不存储任何数据。sys schema的使用非常方便，但需要记住 ，它只访问存储在performance_schema表中的数据。如果在sys schema中找不到你想看的数据，可尝试在performance_schema的基表中查找。
1.6 理解线程
MySQL服务端是多线程软件。它的每个组件都使用线程。例如，由主线程或存储引擎创建的，也可以是为用户连接创建的前台线程。每个线程至少有两个唯一标识符：一个是操作系统线程ID，另一个是MySQL内部线程ID。操作系统线程ID可以通过相关工具查看，如在Linux系统中可使用ps-eLf命令查看。而MySQL内部线程ID在大多数performance_schema表中以THREAD_ID命名。此外，每个前台线程都有一个指定的PROCESSLIST_ID。
注意：THREAD_ID不等于PROCESSLIST_ID！
performance_schema中的threads表包含了服务器中存在的所有线程：
mysql> SELECT NAME, THREAD_ID, PROCESSLIST_ID, THREAD_OS_ID FROM performance_schema. threads;
+---------------------------------------------+-----------+----------------+--------------+
| NAME | THREAD_ID | PROCESSLIST_ID | THREAD_OS_ID |
+---------------------------------------------+-----------+----------------+--------------+
| thread/sql/main | 1 | NULL | 1 |
| thread/innodb/io_ibuf_thread | 3 | NULL | 369 |
| thread/innodb/io_log_thread | 4 | NULL | 370 |
| thread/innodb/io_read_thread | 5 | NULL | 371 |
| thread/innodb/io_read_thread | 6 | NULL | 372 |
| thread/innodb/io_read_thread | 7 | NULL | 373 |
| thread/innodb/io_read_thread | 8 | NULL | 374 |
| thread/innodb/io_write_thread | 9 | NULL | 375 |
| thread/innodb/io_write_thread | 10 | NULL | 376 |
| thread/innodb/io_write_thread | 11 | NULL | 377 |
| thread/innodb/io_write_thread | 12 | NULL | 378 |
| thread/innodb/page_flush_coordinator_thread | 13 | NULL | 379 |
| thread/innodb/log_checkpointer_thread | 14 | NULL | 380 |
| thread/innodb/log_flush_notifier_thread | 15 | NULL | 381 |
| thread/innodb/log_flusher_thread | 16 | NULL | 382 |
| thread/innodb/log_write_notifier_thread | 17 | NULL | 383 |
| thread/innodb/log_writer_thread | 18 | NULL | 384 |
| thread/innodb/log_files_governor_thread | 19 | NULL | 385 |
| thread/innodb/srv_lock_timeout_thread | 24 | NULL | 390 |
| thread/innodb/srv_error_monitor_thread | 25 | NULL | 391 |
| thread/innodb/srv_monitor_thread | 26 | NULL | 392 |
| thread/innodb/buf_resize_thread | 27 | NULL | 393 |
| thread/innodb/srv_master_thread | 28 | NULL | 394 |
| thread/innodb/dict_stats_thread | 29 | NULL | 395 |
| thread/innodb/fts_optimize_thread | 30 | NULL | 396 |
| thread/mysqlx/worker | 31 | NULL | 397 |
| thread/mysqlx/worker | 32 | NULL | 398 |
| thread/mysqlx/acceptor_network | 33 | NULL | 399 |
| thread/innodb/buf_dump_thread | 37 | NULL | 403 |
| thread/innodb/clone_gtid_thread | 38 | NULL | 404 |
| thread/innodb/srv_purge_thread | 39 | NULL | 405 |
| thread/innodb/srv_worker_thread | 40 | NULL | 406 |
| thread/innodb/srv_worker_thread | 41 | NULL | 407 |
| thread/innodb/srv_worker_thread | 42 | NULL | 408 |
| thread/sql/event_scheduler | 43 | 5 | 409 |
| thread/sql/signal_handler | 44 | NULL | 410 |
| thread/mysqlx/acceptor_network | 45 | NULL | 411 |
| thread/sql/compress_gtid_table | 47 | 7 | 413 |
| thread/sql/one_connection | 51 | 11 | 414 |
| thread/sql/one_connection | 52 | 12 | 425 |
+---------------------------------------------+-----------+----------------+--------------+
40 rows in set (0.11 sec)注意： Performance Schema到处使用THREAD_ID，而PROCESSLIST_ID只在threads表中可用。如果需要获取PROCESSLIST_ID，例如，要杀死持有锁的连接，则需要查询threads表来获取。
02 Performance Schema - 配置
2.1 启用与禁用-Performance Schema
要启用或禁用Performance Schema，可以将变量performance_schema设置为ON或OFF。这是一个只读变量，要么在配置文件中更改，要么在MySQL服务器启动时通过命令行参数更改。
2.2 启用与禁用-插桩
通过setup_instruments表查看插桩的状态：
启用或禁用performance_schema插桩的三个方法：
-
直接修改setup_instruments表
使用这种方法设置的参数，在数据库重启后就会失效。
UPDATE performance_schema.setup_instruments
SET ENABLED = 'YES' WHERE NAME = 'stage/sql/starting'也可以使用通配符来启用所有的SQL语句的插桩：
UPDATE performance_schema.setup_instruments
SET ENABLED = 'YES' WHERE NAME = 'stage/sql/%'-
调用sys schema中的ps_setup_enable_instrument存储过程
使用这种方法设置的参数，在数据库重启后同样会失效。
也支持通配符匹配
MySQL通配符：
% 表示任何字符出现任意次数
_ 总是匹配一个字符
# 打开
CALL sys.ps_setup_enable_instrument('stage/sql/%');
# 关闭
CALL sys.ps_setup_disable_instrument('stage/sql/%');-
使用performance-schema-instrument启动参数
如前所述，两种方法都允许在线更改performance_schema配置，但是数据库重启之后配置就会失效。如果要在重启之后保留特定插桩的配置，需要使用performance-schema-instrument配置参数。
该方法同样支持通配符。
performance-schema-instrument='statement/sql/select=ON'如果指定了多个选项，则无论顺序如何，较长的插桩字符串优先于较短的插桩字符串。
2.3 启用与禁用-消费者表
与插桩一样，消费者表也可以通过以下三种方式启用或禁用：
-
使用Performance Schema中的setup_consumers表。
-
调用sys schema中的ps_setup_enable_consumer或ps_setup_disable_consuper存储过程。
-
使用performance-schema-consumer启动参数。
2.4 优化特定对象的监控
Performance Schema可以针对特定对象类型、schema和对象名称启用或禁用监控。这在setup_objects表中完成。对象类型（OBJECT_TYPE列）可以是下面的五个值之一：EVENT、FUNCTION、PROCEDURE、TABLE和TRIGGER。 此外，还可以指定OBJECT_SCHEMA和OBJECT_NAME，并且支持通配符。
例：关闭test数据库中触发器的performance_schema信息采集
INSERT INTO performance_schema.setup_objects
(OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, ENABLED)
VALUES ('TRIGGER',"test','%', 'NO')例：保留名为my_trigger的触发器的信息采集
INSERT INTO performance_schema.setup_objects
(OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, ENABLED)
VALUES ('TRIGGER', "test', 'my_trigger', 'YES")这些对象没有配置文件的选项。如果需要在重新启动后持久化更改，那么需要将这些INSERT语句写入SQL文件中，并在启动时使用init_file选项加载该SQL文件。
2.5 优化线程的监控
setup_threads表包含可以监控的后台线程
例: 要禁用事件调度程序(thread/sql/event_scheduler)的历史日志记录。
mysql> UPDATE performance_schema.setup_threads SET HISTORY='NO'
-> WHERE NAME= 'thread/sql/event_scheduler';用户线程的设置不在setup_threads表中，而是在setup_actors表中
例：启用了yihui＠localhost和yihui＠example.com的监测，禁用yihui＠localhost的历史记录，并禁用从localhost连接的所有用户的监测和历史记录。
mysql> INSERT INTO performance_schema.setup_actors
-> (HOST, USER, ENABLED, HISTORY)
-> VALUES ('localhost', 'yihui', 'YES', 'NO'),
-> ('example.com', 'yihui', 'YES', 'YES"),
- >('localhost'. "%', 'NO', 'NO');与对象监控一样，线程和actor都没有配置文件的选项。如果需要在重新启动后保留表中的更改，需要将这些INSERT语句写入SQL文件，并使用init_file选项在启动时加载该SQL文件。
2.6 调整Performance Schema的内存大小
默认情况下，某些performance_schema表会自动调整大小，其他的则有固定数量的行。可以通过更改启动变量来调整这些选项。变量的名称遵循p erformance_schema_object_[size|instances|classes|length|handles]的模式，其中对象要么是消费者表，要么是设置表，要么是特定事件的插桩实例
例:
-
配置变量performance_schema_events_stages_history_size定义了performance_schema_events_stages_history表将存储的每个线程的阶段数。
-
配置变量performance_schema_max_memory_classes定义了可以使用的最大内存插桩数量。
2.7 默认值
从5.7版开始，Performance Schema在默认情况下是启用的。大多数插桩默认是禁用的，只启用了全局、线程、语句和事务插桩。从8.0版本开始，默认情况下还启用了元数据锁和内存插桩。
mysql、information_schema和performance_schema数据库没有启用插桩，但所有其他对象、线程和actor都启用了插桩。
MySQL不同部分的默认值会随着版本的不同而改变，最好阅读一下官方的用户参考手册。
最后： 下方这份完整的软件测试视频教程已经整理上传完成，需要的朋友们可以自行领取【保证100%免费】
扫一扫
848
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
