MySQL8 中文参考（八十二）（3）-阿里云开发者社区

MySQL8 中文参考（八十二）（2）https://developer.aliyun.com/article/1565902

20.7.9 使用 Performance Schema 内存仪表化监控 Group Replication 内存使用情况

原文：dev.mysql.com/doc/refman/8.0/en/mysql-gr-memory-monitoring-ps-instruments.html

20.7.9.1 启用或禁用 Group Replication 仪表化

20.7.9.2 示例查询

从 MySQL 8.0.30 开始，Performance Schema 提供了用于监控 Group Replication 内存使用情况的仪表化。要查看可用的 Group Replication 仪表，执行以下查询：

mysql> SELECT NAME,ENABLED FROM performance_schema.setup_instruments
       WHERE NAME LIKE 'memory/group_rpl/%';
+-------------------------------------------------------------------+---------+
| NAME                                                              | ENABLED |
+-------------------------------------------------------------------+---------+
| memory/group_rpl/write_set_encoded                                | YES     |
| memory/group_rpl/certification_data                               | YES     |
| memory/group_rpl/certification_data_gc                            | YES     |
| memory/group_rpl/certification_info                               | YES     |
| memory/group_rpl/transaction_data                                 | YES     |
| memory/group_rpl/sql_service_command_data                         | YES     |
| memory/group_rpl/mysql_thread_queued_task                         | YES     |
| memory/group_rpl/message_service_queue                            | YES     |
| memory/group_rpl/message_service_received_message                 | YES     |
| memory/group_rpl/group_member_info                                | YES     |
| memory/group_rpl/consistent_members_that_must_prepare_transaction | YES     |
| memory/group_rpl/consistent_transactions                          | YES     |
| memory/group_rpl/consistent_transactions_prepared                 | YES     |
| memory/group_rpl/consistent_transactions_waiting                  | YES     |
| memory/group_rpl/consistent_transactions_delayed_view_change      | YES     |
| memory/group_rpl/GCS_XCom::xcom_cache                             | YES     |
| memory/group_rpl/Gcs_message_data::m_buffer                       | YES     |
+-------------------------------------------------------------------+---------+

有关 Performance Schema 内存仪表化和事件的更多信息，请参阅第 29.12.20.10 节，“内存摘要表”。

Performance Schema Group Replication 为 Group Replication 的内存分配提供仪表化。

memory/group_rpl/ Performance Schema 仪表化在 8.0.30 中进行了更新，以扩展对 Group Replication 内存使用情况的监控。memory/group_rpl/ 包含以下仪表：

write_set_encoded: 在广播到组成员之前对写入集进行编码分配的内存。
Gcs_message_data::m_buffer: 为发送到网络的事务数据负载分配的内存。
certification_data: 为传入事务的认证分配的内存。
certification_data_gc: 为每个成员发送的 GTID_EXECUTED 进行垃圾回收分配的内存。
certification_info: 为解决并发事务之间冲突分配的认证信息存储内存。
transaction_data: 为排队等待插件管道的传入事务分配的内存。
message_service_received_message: 为从 Group Replication 传递消息服务接收消息分配的内存。
sql_service_command_data: 为处理内部 SQL 服务命令队列分配的内存。
mysql_thread_queued_task: 当将 MySQL 线程相关任务添加到处理队列时分配的内存。
message_service_queue: 为 Group Replication 传递消息服务的排队消息分配的内存。
GCS_XCom::xcom_cache: 为组成员之间作为共识协议的一部分交换的消息和元数据分配的 XCOM 缓存内存。
consistent_members_that_must_prepare_transaction: 为保存为 Group Replication 事务一致性保证准备事务的成员列表分配的内存。
consistent_transactions: 为保存事务和必须为 Group Replication 事务一致性保证准备该事务的成员列表分配的内存。
consistent_transactions_prepared: 为保存为 Group Replication 事务一致性保证准备的事务信息列表分配的内存。
consistent_transactions_waiting：用于保存在处理具有AFTER和BEFORE_AND_AFTER一致性的准备事务之前的事务列表信息的内存分配。
consistent_transactions_delayed_view_change：用于保存由于准备一致性事务等待准备确认而延迟的视图更改事件（view_change_log_event）列表的内存分配。
group_member_info：用于保存组成员属性的内存分配。属性如主机名、端口、成员权重和角色等。

memory/sql/ 分组中的以下工具也用于监视组复制内存：

Log_event：用于将事务数据编码为二进制日志格式的内存分配；这是组复制传输数据的相同格式。
write_set_extraction：在提交之前为事务生成的写入集分配的内存。
Gtid_set::to_string：用于存储 GTID 集合的字符串表示的内存分配。
Gtid_set::Interval_chunk：用于存储 GTID 对象的内存分配。

原文：dev.mysql.com/doc/refman/8.0/en/mysql-gr-memory-monitoring-ps-instruments-enable.html

20.7.9.1 启用或禁用组复制仪器

要从命令行启用所有组复制仪器，请在您选择的 SQL 客户端中运行以下命令：

UPDATE performance_schema.setup_instruments SET ENABLED = 'YES' 
        WHERE NAME LIKE 'memory/group_rpl/%';

要从命令行禁用所有组复制仪器，请在您选择的 SQL 客户端中运行以下命令：

UPDATE performance_schema.setup_instruments SET ENABLED = 'NO' 
        WHERE NAME LIKE 'memory/group_rpl/%';

要在服务器启动时启用所有组复制仪器，请将以下内容添加到您的选项文件中：

[mysqld]
 performance-schema-instrument='memory/group_rpl/%=ON'

要在服务器启动时禁用所有组复制仪器，请将以下内容添加到您的选项文件中：

[mysqld]
 performance-schema-instrument='memory/group_rpl/%=OFF'

要启用或禁用该组中的单个仪器，请用该仪器的全名替换通配符（%）。

欲了解更多信息，请参阅 Section 29.3, “性能模式启动配置”和 Section 29.4, “性能模式运行时配置”。

原文：dev.mysql.com/doc/refman/8.0/en/mysql-gr-memory-monitoring-ps-sample-queries.html

20.7.9.2 示例查询

本节描述了使用工具和事件监视组复制内存使用情况的示例查询。这些查询从memory_summary_global_by_event_name表中检索数据。

内存数据可以查询单个事件，例如：

SELECT * FROM performance_schema.memory_summary_global_by_event_name
WHERE EVENT_NAME = 'memory/group_rpl/write_set_encoded'\G
*************************** 1\. row ***************************
                  EVENT_NAME: memory/group_rpl/write_set_encoded
                 COUNT_ALLOC: 1
                  COUNT_FREE: 0
   SUM_NUMBER_OF_BYTES_ALLOC: 45
    SUM_NUMBER_OF_BYTES_FREE: 0
              LOW_COUNT_USED: 0
          CURRENT_COUNT_USED: 1
             HIGH_COUNT_USED: 1
    LOW_NUMBER_OF_BYTES_USED: 0
CURRENT_NUMBER_OF_BYTES_USED: 45
   HIGH_NUMBER_OF_BYTES_USED: 45

更多关于列的信息，请参阅第 29.12.20.10 节，“内存摘要表”。

您还可以定义查询，对各种事件求和，以提供特定内存使用领域的概述。

下面描述了以下示例：

用于捕获事务的内存
用于广播事务的内存
组复制中的总内存使用量
认证中使用的内存
认证中使用的内存
复制管道中使用的内存
一致性中使用的内存
交付消息服务中使用的内存
用于广播和接收事务的内存

记忆用于捕获事务

用于捕获用户事务的内存分配是write_set_encoded、write_set_extraction和Log_event事件值的总和。例如：

mysql> SELECT * FROM (
                   SELECT
                     (CASE
                        WHEN EVENT_NAME LIKE 'memory/group_rpl/write_set_encoded'
                        THEN 'memory/group_rpl/memory_gr'
                        WHEN EVENT_NAME = 'memory/sql/write_set_extraction'
                        THEN 'memory/group_rpl/memory_gr'
                        WHEN EVENT_NAME = 'memory/sql/Log_event'
                        THEN 'memory/group_rpl/memory_gr'
                        ELSE 'memory_gr_rest'
                     END) AS EVENT_NAME, SUM(COUNT_ALLOC), SUM(COUNT_FREE),
                   SUM(SUM_NUMBER_OF_BYTES_ALLOC),
                   SUM(SUM_NUMBER_OF_BYTES_FREE), SUM(LOW_COUNT_USED),
                   SUM(CURRENT_COUNT_USED), SUM(HIGH_COUNT_USED),
                   SUM(LOW_NUMBER_OF_BYTES_USED), SUM(CURRENT_NUMBER_OF_BYTES_USED),
                   SUM(HIGH_NUMBER_OF_BYTES_USED)
                 FROM performance_schema.memory_summary_global_by_event_name
                 GROUP BY (CASE
                              WHEN EVENT_NAME LIKE 'memory/group_rpl/write_set_encoded'
                              THEN 'memory/group_rpl/memory_gr'
                              WHEN EVENT_NAME = 'memory/sql/write_set_extraction'
                              THEN 'memory/group_rpl/memory_gr'
                              WHEN EVENT_NAME = 'memory/sql/Log_event'
                              THEN 'memory/group_rpl/memory_gr'
                              ELSE 'memory_gr_rest'
                            END)
      ) f
      WHERE f.EVENT_NAME != 'memory_gr_rest'
      *************************** 1\. row ***************************
                             EVENT_NAME: memory/group_rpl/memory_gr
                       SUM(COUNT_ALLOC): 127
                        SUM(COUNT_FREE): 117
         SUM(SUM_NUMBER_OF_BYTES_ALLOC): 54808
          SUM(SUM_NUMBER_OF_BYTES_FREE): 52051
                    SUM(LOW_COUNT_USED): 0
                SUM(CURRENT_COUNT_USED): 10
                   SUM(HIGH_COUNT_USED): 35
          SUM(LOW_NUMBER_OF_BYTES_USED): 0
      SUM(CURRENT_NUMBER_OF_BYTES_USED): 2757
         SUM(HIGH_NUMBER_OF_BYTES_USED): 15630

记忆用于广播事务

用于广播事务的内存分配是Gcs_message_data::m_buffer、transaction_data和GCS_XCom::xcom_cache事件值的总和。例如：

mysql> SELECT * FROM (
                  SELECT
                    (CASE
                       WHEN EVENT_NAME =  'memory/group_rpl/Gcs_message_data::m_buffer'
                       THEN 'memory/group_rpl/memory_gr'
                       WHEN EVENT_NAME = 'memory/group_rpl/GCS_XCom::xcom_cache'
                       THEN 'memory/group_rpl/memory_gr'
                       WHEN EVENT_NAME = 'memory/group_rpl/transaction_data'
                       THEN 'memory/group_rpl/memory_gr'
                       ELSE 'memory_gr_rest'
                    END) AS EVENT_NAME, SUM(COUNT_ALLOC), SUM(COUNT_FREE),
                    SUM(SUM_NUMBER_OF_BYTES_ALLOC),
                    SUM(SUM_NUMBER_OF_BYTES_FREE), SUM(LOW_COUNT_USED),
                    SUM(CURRENT_COUNT_USED), SUM(HIGH_COUNT_USED),
                    SUM(LOW_NUMBER_OF_BYTES_USED), SUM(CURRENT_NUMBER_OF_BYTES_USED),
                    SUM(HIGH_NUMBER_OF_BYTES_USED)
                  FROM performance_schema.memory_summary_global_by_event_name
                  GROUP BY (CASE
                              WHEN EVENT_NAME =  'memory/group_rpl/Gcs_message_data::m_buffer'
                              THEN 'memory/group_rpl/memory_gr'
                              WHEN EVENT_NAME = 'memory/group_rpl/GCS_XCom::xcom_cache'
                              THEN 'memory/group_rpl/memory_gr'
                              WHEN EVENT_NAME = 'memory/group_rpl/transaction_data'
                              THEN 'memory/group_rpl/memory_gr'
                              ELSE 'memory_gr_rest'
                            END)
       ) f
       WHERE f.EVENT_NAME != 'memory_gr_rest'\G
       *************************** 1\. row ***************************
                              EVENT_NAME: memory/group_rpl/memory_gr
                        SUM(COUNT_ALLOC): 84
                         SUM(COUNT_FREE): 31
          SUM(SUM_NUMBER_OF_BYTES_ALLOC): 1072324
           SUM(SUM_NUMBER_OF_BYTES_FREE): 7149
                     SUM(LOW_COUNT_USED): 0
                 SUM(CURRENT_COUNT_USED): 53
                    SUM(HIGH_COUNT_USED): 59
           SUM(LOW_NUMBER_OF_BYTES_USED): 0
       SUM(CURRENT_NUMBER_OF_BYTES_USED): 1065175
          SUM(HIGH_NUMBER_OF_BYTES_USED): 1065809

在组复制中使用的总内存

用于发送和接收事务、认证和所有其他主要进程的内存分配。通过查询memory/group_rpl/组的所有事件来计算。例如：

mysql> SELECT * FROM (
                  SELECT
                    (CASE
                       WHEN EVENT_NAME LIKE 'memory/group_rpl/%'
                       THEN 'memory/group_rpl/memory_gr'
                       ELSE 'memory_gr_rest'
                     END) AS EVENT_NAME, SUM(COUNT_ALLOC), SUM(COUNT_FREE),
                     SUM(SUM_NUMBER_OF_BYTES_ALLOC),
                     SUM(SUM_NUMBER_OF_BYTES_FREE), SUM(LOW_COUNT_USED),
                     SUM(CURRENT_COUNT_USED), SUM(HIGH_COUNT_USED),
                     SUM(LOW_NUMBER_OF_BYTES_USED), SUM(CURRENT_NUMBER_OF_BYTES_USED),
                     SUM(HIGH_NUMBER_OF_BYTES_USED)
                  FROM performance_schema.memory_summary_global_by_event_name
                  GROUP BY (CASE
                              WHEN EVENT_NAME LIKE 'memory/group_rpl/%'
                              THEN 'memory/group_rpl/memory_gr'
                              ELSE 'memory_gr_rest'
                            END)
       ) f
       WHERE f.EVENT_NAME != 'memory_gr_rest'\G
       *************************** 1\. row ***************************
                              EVENT_NAME: memory/group_rpl/memory_gr
                        SUM(COUNT_ALLOC): 190
                         SUM(COUNT_FREE): 127
          SUM(SUM_NUMBER_OF_BYTES_ALLOC): 1096370
           SUM(SUM_NUMBER_OF_BYTES_FREE): 28675
                     SUM(LOW_COUNT_USED): 0
                 SUM(CURRENT_COUNT_USED): 63
                    SUM(HIGH_COUNT_USED): 77
           SUM(LOW_NUMBER_OF_BYTES_USED): 0
       SUM(CURRENT_NUMBER_OF_BYTES_USED): 1067695
          SUM(HIGH_NUMBER_OF_BYTES_USED): 1069255

认证中使用的内存

认证过程中的内存分配是certification_data、certification_data_gc和certification_info事件值的总和。例如：

mysql> SELECT * FROM (
                  SELECT
                    (CASE
                       WHEN EVENT_NAME = 'memory/group_rpl/certification_data'
                       THEN 'memory/group_rpl/certification'
                       WHEN EVENT_NAME = 'memory/group_rpl/certification_data_gc'
                       THEN 'memory/group_rpl/certification'
                       WHEN EVENT_NAME = 'memory/group_rpl/certification_info'
                       THEN 'memory/group_rpl/certification'
                       ELSE 'memory_gr_rest'
                     END) AS EVENT_NAME, SUM(COUNT_ALLOC), SUM(COUNT_FREE),
                     SUM(SUM_NUMBER_OF_BYTES_ALLOC),
                     SUM(SUM_NUMBER_OF_BYTES_FREE), SUM(LOW_COUNT_USED),
                     SUM(CURRENT_COUNT_USED), SUM(HIGH_COUNT_USED),
                     SUM(LOW_NUMBER_OF_BYTES_USED), SUM(CURRENT_NUMBER_OF_BYTES_USED),
                     SUM(HIGH_NUMBER_OF_BYTES_USED)
                  FROM performance_schema.memory_summary_global_by_event_name
                  GROUP BY (CASE
                              WHEN EVENT_NAME = 'memory/group_rpl/certification_data'
                              THEN 'memory/group_rpl/certification'
                              WHEN EVENT_NAME = 'memory/group_rpl/certification_data_gc'
                              THEN 'memory/group_rpl/certification'
                              WHEN EVENT_NAME = 'memory/group_rpl/certification_info'
                              THEN 'memory/group_rpl/certification'
                              ELSE 'memory_gr_rest'
                           END)
       ) f
       WHERE f.EVENT_NAME != 'memory_gr_rest'\G
       *************************** 1\. row ***************************
                              EVENT_NAME: memory/group_rpl/certification
                        SUM(COUNT_ALLOC): 80
                         SUM(COUNT_FREE): 80
          SUM(SUM_NUMBER_OF_BYTES_ALLOC): 9442
           SUM(SUM_NUMBER_OF_BYTES_FREE): 9442
                     SUM(LOW_COUNT_USED): 0
                 SUM(CURRENT_COUNT_USED): 0
                    SUM(HIGH_COUNT_USED): 66
           SUM(LOW_NUMBER_OF_BYTES_USED): 0
       SUM(CURRENT_NUMBER_OF_BYTES_USED): 0
          SUM(HIGH_NUMBER_OF_BYTES_USED): 6561

复制管道中使用的内存

复制管道的内存分配是certification_data和transaction_data事件值的总和。例如：

mysql> SELECT * FROM (
                  SELECT
                    (CASE
                       WHEN EVENT_NAME LIKE 'memory/group_rpl/certification_data'
                       THEN 'memory/group_rpl/pipeline'
                       WHEN EVENT_NAME LIKE 'memory/group_rpl/transaction_data'
                       THEN 'memory/group_rpl/pipeline'
                       ELSE 'memory_gr_rest'
                     END) AS EVENT_NAME, SUM(COUNT_ALLOC), SUM(COUNT_FREE),
                     SUM(SUM_NUMBER_OF_BYTES_ALLOC),
                     SUM(SUM_NUMBER_OF_BYTES_FREE), SUM(LOW_COUNT_USED),
                     SUM(CURRENT_COUNT_USED), SUM(HIGH_COUNT_USED),
                     SUM(LOW_NUMBER_OF_BYTES_USED), SUM(CURRENT_NUMBER_OF_BYTES_USED),
                     SUM(HIGH_NUMBER_OF_BYTES_USED)
                   FROM performance_schema.memory_summary_global_by_event_name
                   GROUP BY (CASE
                              WHEN EVENT_NAME LIKE 'memory/group_rpl/certification_data'
                              THEN 'memory/group_rpl/pipeline'
                              WHEN EVENT_NAME LIKE 'memory/group_rpl/transaction_data'
                              THEN 'memory/group_rpl/pipeline'
                              ELSE 'memory_gr_rest'
                            END)
       ) f
       WHERE f.EVENT_NAME != 'memory_gr_rest'\G
       *************************** 1\. row ***************************
                         EVENT_NAME: memory/group_rpl/pipeline
                        COUNT_ALLOC: 17
                         COUNT_FREE: 13
          SUM_NUMBER_OF_BYTES_ALLOC: 2483
           SUM_NUMBER_OF_BYTES_FREE: 1668
                     LOW_COUNT_USED: 0
                 CURRENT_COUNT_USED: 4
                    HIGH_COUNT_USED: 4
           LOW_NUMBER_OF_BYTES_USED: 0
       CURRENT_NUMBER_OF_BYTES_USED: 815
          HIGH_NUMBER_OF_BYTES_USED: 815

MySQL8 中文参考（八十二）（4）https://developer.aliyun.com/article/1565904

MySQL8 中文参考（八十二）（3）

20.7.9 使用 Performance Schema 内存仪表化监控 Group Replication 内存使用情况

20.7.9.1 启用或禁用组复制仪器

20.7.9.2 示例查询

记忆用于捕获事务

记忆用于广播事务

在组复制中使用的总内存

认证中使用的内存

复制管道中使用的内存

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像

探索云世界

热门

云计算

大数据

云原生

人工智能

数据库

开发与运维

活动广场

任务中心

训练营

直播

乘风者计划

下载

镜像站

技术资料

MySQL8 中文参考（八十二）（3）

20.7.9 使用 Performance Schema 内存仪表化监控 Group Replication 内存使用情况

20.7.9.1 启用或禁用组复制仪器

20.7.9.2 示例查询

记忆用于捕获事务

记忆用于广播事务

在组复制中使用的总内存

认证中使用的内存

复制管道中使用的内存

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像