MySQL

配置

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          |
+--------------------------------------+--------------+
上一页Performance Schema下一页使用

最后更新于