配置

Performance Schema的部分设置只能在服务器启动时更改，比如：

• 启用或禁用Performance Schema

• 与内存使用和数据收集的限制相关的变量

Performance Schema插桩和消费者表则可以被动态启用或禁用。

注意

可以在禁用所有消费者表和插桩的情况下启动Performance Schema，所以建议只启用那些解决特定问题所需的插桩。这样就不会在Performance Schema上白白消耗额外的资源，也不会因为过度检测消耗资源而导致系统饿死。

启用或禁用Performance Schema

要启用或禁用Performance Schema，可以将变量performance_schema设置为ON或OFF。这是一个只读变量，要么在配置文件中更改，要么在MySQL服务器启动时通过命令行参数更改。

启用或禁用插桩

有三种方法可用于启用或禁用performance_schema插桩：

1. 使用setup_instruments表：

   UPDATE setup_instruments
   SET enabled = 'YES'
   WHERE name LIKE 'statement/sql/%';

2. 调用sys schema中的存储过程：

   • ps_setup_enable_instrument：用于启用其参数所对应的插桩

   • ps_setup_disable_instrument：用于禁用其参数所对应的插桩

   这两个存储过程都支持通配符，如果要启用或禁用所有受支持的插桩，请使用通配符“%”：

   CALL sys.ps_setup_disable_instrument('statement/sql/%');
   CALL sys.ps_setup_enable_instrument('statement/sql/%');

3. 使用performance-schema-instrument启动参数： 这个变量支持performance-schema-instrument='instrument_name=value'这样的语法。其中，instrument_name是插桩名称，值为ON、TRUE或1表示启用，值为OFF、FALSE或0表示禁用。该选项还支持通配符：

   performance-schema-instrument='statement/sql/%=ON'

   如果指定了多个选项，则无论顺序如何，较长的插桩字符串优先于较短的插桩字符串。

注意

使用方法 1 和 2 设置的参数，在数据库重启后就会失效。

如果要在重启之后保留特定插桩的配置，需要使用performance-schema-instrument配置参数。

启用或禁用消费者表

消费者表也可以通过以下三种方式启用或禁用：

1. 使用Performance Schema中的setup_consumers表：

   UPDATE setup_consumers
   SET enabled = 'YES'
   WHERE name LIKE 'events_statements_%';

2. 调用sys schema中的存储过程：

   1. ps_setup_enable_consumer：用于启用其参数所对应的消费者表。

   2. ps_setup_disable_consumer：用于禁用其参数所对应的消费者表。

   这两个存储过程都支持通配符，如果要启用或禁用所有受支持的插桩，请使用通配符“%”：

   CALL sys.ps_setup_disable_consumer('events_statements_%');
   CALL sys.ps_setup_enable_consumer('events_statements_%');

3. 使用performance-schema-consumer启动参数：

   这个变量支持performance-schema-consumer='table_name=value'这样的语法。其中，table_name是消费者表名，值为ON、TRUE或1表示启用，值为OFF、FALSE或0表示禁用。该选项还支持通配符：

   performance-schema-consumer='events_statements_%=ON'

注意

使用方法 1 和 2 设置的参数，在数据库重启后就会失效。

如果要在重启之后保留特定插桩的配置，需要使用performance-schema-consumer配置参数。

优化特定对象的监控

Performance Schema可以针对特定对象类型（OBJECT_TYPE）、schema（OBJECT_SCHEMA）和对象名称（OBJECT_NAME）启用或禁用监控。这在setup_objects表中完成。

例如，要关闭 sakila 数据库中触发器的 performance_schema 信息采集，可以使用以下语句：

INSERT INTO setup_objects (
    object_type, object_schema, object_name, enabled, timed
) VALUES (
   'TRIGGER', 'sakila', '%', 'NO', 'NO'
);

如果要保留名为 my_trigger 的触发器的信息采集，可通过如下语句进行配置：

INSERT INTO setup_objects (
    object_type, object_schema, object_name, enabled, timed
) VALUES (
   'TRIGGER', 'sakila', 'my_trigger', 'YES', 'NO'
);

注意

这些对象没有配置文件选项。

如果需要在重启后保持配置的更改，可以将这些 INSERT 语句写入SQL文件中，并在启动时使用init_file 选项加载该SQL文件。

优化线程的监控

setup_threads表包含可以监控的后台线程列表：

• ENABLED列指定是否启用对特定线程的监测

• HISTORY列指定特定线程的检测事件是否应该存储在 *_history 和 *_history_long 表中

例如，要禁用事件调度程序(thread/sql/event_scheduler)的历史日志记录，可以运行：

UPDATE setup_actors
SET history = 'NO'
WHERE name = 'thread/sql/event_scheduler';

用户线程的设置不在setup_threads表中，而是在setup_actors表中，它包含如下表所描述的列：

列名
host	主机名，如 localhost、%、my.domain.com 或 192.168.10.110
user	用户名，如admin 或 %
role	未使用
enabled	是否启用
history	是否在 *_history 和 *_history_long 保存数据

例如，要为用户admin指定规则，可以使用以下命令：

INSERT INTO setup_actors(
    host, user, enabled, history
)
VALUES (
           'localhost', 'admin', 'YES', 'NO'
       ),
       (
           'example.com', 'admin', 'YES', 'YES'
       ),
       (
           'localhost', '%', 'NO', 'NO'
       );

注意

与object一样，thread和actor都没有配置文件的选项。

如果需要在重新启动后保留表中的更改，需要将这些INSERT语句写入SQL文件，并使用init_file选项在启动时加载该SQL文件。

在版本5.7.35中，不存在 setup_threads 这张表！

SELECT version() AS version;

+----------+
|version   |
+----------+
|5.7.35-log|
+----------+

调整Performance Schema的内存大小

Performance Schema将数据存储在使用PERFORMANCE_SCHEMA引擎的表中，该引擎将数据存储在内存中。

可以通过更改变量来调整 performance schema 的内存大小，变量的名称遵循performance_schema_object_[size | instances | classes | length | handles ] 的模式，其中对象要么是消费者表，要么是设置表，要么是特定事件的插桩实例。例如：

• 配置变量 performance_schema_events_stages_history_size 定义了 events_stages_history 表可以存储的每个线程的阶段数。

  SELECT @@performance_schema_events_stages_history_size;
  
  +-----------------------------------------------+
  |@@performance_schema_events_stages_history_size|
  +-----------------------------------------------+
  |10                                             |
  +-----------------------------------------------+

• 变量 performance_schema_max_memory_classes 定义了可以使用的最大内存插桩数量。

  SELECT @@performance_schema_max_memory_classes;
  
  +---------------------------------------+
  |@@performance_schema_max_memory_classes|
  +---------------------------------------+
  |320                                    |
  +---------------------------------------+

注意

上述变量均是只读变量，因此只能在mysql服务器启动前进行设置！

配置的默认值

• 从5.7版开始，Performance Schema在默认情况下是启用的。大多数插桩默认是禁用的，只启用了全局、线程、语句和事务插桩。从8.0版本开始，默认情况下还启用了元数据锁和内存插桩。

  SELECT @@performance_schema;
  
  +--------------------+
  |@@performance_schema|
  +--------------------+
  |1                   |
  +--------------------+

• mysql、information_schema 和 performance_schema 数据库没有启用插桩，但所有其他对象、线程和 actor 都启用了插桩。

  SELECT *
  FROM setup_objects
  WHERE object_schema IN ('mysql', 'information_schema', 'performance_schema')
  ORDER BY object_schema;
  
  +-----------+------------------+-----------+-------+-----+
  |OBJECT_TYPE|OBJECT_SCHEMA     |OBJECT_NAME|ENABLED|TIMED|
  +-----------+------------------+-----------+-------+-----+
  |EVENT      |information_schema|%          |NO     |NO   |
  |FUNCTION   |information_schema|%          |NO     |NO   |
  |PROCEDURE  |information_schema|%          |NO     |NO   |
  |TABLE      |information_schema|%          |NO     |NO   |
  |TRIGGER    |information_schema|%          |NO     |NO   |
  |EVENT      |mysql             |%          |NO     |NO   |
  |FUNCTION   |mysql             |%          |NO     |NO   |
  |PROCEDURE  |mysql             |%          |NO     |NO   |
  |TABLE      |mysql             |%          |NO     |NO   |
  |TRIGGER    |mysql             |%          |NO     |NO   |
  |EVENT      |performance_schema|%          |NO     |NO   |
  |FUNCTION   |performance_schema|%          |NO     |NO   |
  |PROCEDURE  |performance_schema|%          |NO     |NO   |
  |TABLE      |performance_schema|%          |NO     |NO   |
  |TRIGGER    |performance_schema|%          |NO     |NO   |
  +-----------+------------------+-----------+-------+-----+

• 大多数实例、句柄和设置表都是自动调整大小的。

  SELECT *
  FROM session_variables
  WHERE variable_name LIKE 'performance_schema_setup_%';
  
  +-------------------------------------+--------------+
  |VARIABLE_NAME                        |VARIABLE_VALUE|
  +-------------------------------------+--------------+
  |performance_schema_setup_actors_size |-1            |
  |performance_schema_setup_objects_size|-1            |
  +-------------------------------------+--------------+

• *_history表会存储每个线程的最后 10 个事件；*_history_long表则存储每个线程的最后 10000 个事件。

  SELECT *
  FROM session_variables
  WHERE variable_name LIKE 'performance_schema_%_history_%';
  
  +--------------------------------------------------------+--------------+
  |VARIABLE_NAME                                           |VARIABLE_VALUE|
  +--------------------------------------------------------+--------------+
  |performance_schema_events_stages_history_long_size      |10000         |
  |performance_schema_events_stages_history_size           |10            |
  |performance_schema_events_statements_history_long_size  |10000         |
  |performance_schema_events_statements_history_size       |10            |
  |performance_schema_events_transactions_history_long_size|10000         |
  |performance_schema_events_transactions_history_size     |10            |
  |performance_schema_events_waits_history_long_size       |10000         |
  |performance_schema_events_waits_history_size            |10            |
  +--------------------------------------------------------+--------------+

• 存储的SQL文本的最大长度为1024字节，SQL摘要的最大长度也是1024字节，超出部分会被截断(right-trimmed)。

  SELECT *
  FROM session_variables
  WHERE variable_name LIKE 'performance_schema_%_digest_%'
     OR variable_name LIKE 'performance_schema_%_sql_text_%';
     
  +--------------------------------------+--------------+
  |VARIABLE_NAME                         |VARIABLE_VALUE|
  +--------------------------------------+--------------+
  |performance_schema_max_digest_length  |1024          |
  |performance_schema_max_sql_text_length|1024          |
  +--------------------------------------+--------------+