文档首页
MySQL 8.4 参考手册
相关文档 下载本手册
PDF (US Ltr) - 39.9Mb
PDF (A4) - 40.0Mb
手册页 (TGZ) - 258.5Kb
手册页 (Zip) - 365.5Kb
Info (Gzip) - 4.0Mb
Info (Zip) - 4.0Mb


20.7.9.2 示例查询

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

可以针对单个事件查询内存数据,例如

Press CTRL+C to copy
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_encodedwrite_set_extractionLog_event 事件值的总和。例如

Press CTRL+C to copy
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_buffertransaction_dataGCS_XCom::xcom_cache 事件值的总和。例如

Press CTRL+C to copy
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/ 组的所有事件来计算。例如

Press CTRL+C to copy
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_datacertification_data_gccertification_info 事件值的总和。例如

Press CTRL+C to copy
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_datatransaction_data 事件值的总和。例如

Press CTRL+C to copy
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
在一致性中使用的内存

用于事务一致性保证的内存分配是 consistent_members_that_must_prepare_transactionconsistent_transactionsconsistent_transactions_preparedconsistent_transactions_waitingconsistent_transactions_delayed_view_change 事件值的总和。例如

Press CTRL+C to copy
mysql> SELECT * FROM ( SELECT (CASE WHEN EVENT_NAME = 'memory/group_rpl/consistent_members_that_must_prepare_transaction' THEN 'memory/group_rpl/consistency' WHEN EVENT_NAME = 'memory/group_rpl/consistent_transactions' THEN 'memory/group_rpl/consistency' WHEN EVENT_NAME = 'memory/group_rpl/consistent_transactions_prepared' THEN 'memory/group_rpl/consistency' WHEN EVENT_NAME = 'memory/group_rpl/consistent_transactions_waiting' THEN 'memory/group_rpl/consistency' WHEN EVENT_NAME = 'memory/group_rpl/consistent_transactions_delayed_view_change' THEN 'memory/group_rpl/consistency' 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/consistent_members_that_must_prepare_transaction' THEN 'memory/group_rpl/consistency' WHEN EVENT_NAME = 'memory/group_rpl/consistent_transactions' THEN 'memory/group_rpl/consistency' WHEN EVENT_NAME = 'memory/group_rpl/consistent_transactions_prepared' THEN 'memory/group_rpl/consistency' WHEN EVENT_NAME = 'memory/group_rpl/consistent_transactions_waiting' THEN 'memory/group_rpl/consistency' WHEN EVENT_NAME = 'memory/group_rpl/consistent_transactions_delayed_view_change' THEN 'memory/group_rpl/consistency' ELSE 'memory_gr_rest' END) ) f WHERE f.EVENT_NAME != 'memory_gr_rest'\G *************************** 1. row *************************** EVENT_NAME: memory/group_rpl/consistency COUNT_ALLOC: 16 COUNT_FREE: 6 SUM_NUMBER_OF_BYTES_ALLOC: 1464 SUM_NUMBER_OF_BYTES_FREE: 528 LOW_COUNT_USED: 0 CURRENT_COUNT_USED: 10 HIGH_COUNT_USED: 11 LOW_NUMBER_OF_BYTES_USED: 0 CURRENT_NUMBER_OF_BYTES_USED: 936 HIGH_NUMBER_OF_BYTES_USED: 1024
在交付消息服务中使用的内存
注意

此工具仅适用于接收的数据,不适用于发送的数据。

组复制交付消息服务的内存分配是 message_service_received_messagemessage_service_queue 事件值的总和。例如

Press CTRL+C to copy
mysql> SELECT * FROM ( SELECT (CASE WHEN EVENT_NAME = 'memory/group_rpl/message_service_received_message' THEN 'memory/group_rpl/message_service' WHEN EVENT_NAME = 'memory/group_rpl/message_service_queue' THEN 'memory/group_rpl/message_service' 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/message_service_received_message' THEN 'memory/group_rpl/message_service' WHEN EVENT_NAME = 'memory/group_rpl/message_service_queue' THEN 'memory/group_rpl/message_service' ELSE 'memory_gr_rest' END) ) f WHERE f.EVENT_NAME != 'memory_gr_rest'\G *************************** 1. row *************************** EVENT_NAME: memory/group_rpl/message_service COUNT_ALLOC: 2 COUNT_FREE: 0 SUM_NUMBER_OF_BYTES_ALLOC: 1048664 SUM_NUMBER_OF_BYTES_FREE: 0 LOW_COUNT_USED: 0 CURRENT_COUNT_USED: 2 HIGH_COUNT_USED: 2 LOW_NUMBER_OF_BYTES_USED: 0 CURRENT_NUMBER_OF_BYTES_USED: 1048664 HIGH_NUMBER_OF_BYTES_USED: 1048664
用于广播和接收事务的内存

用于在网络之间广播和接收事务的内存分配是 wGcs_message_data::m_bufferGCS_XCom::xcom_cache 事件值的总和。例如

Press CTRL+C to copy
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' 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' 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): 73 SUM(COUNT_FREE): 20 SUM(SUM_NUMBER_OF_BYTES_ALLOC): 1070845 SUM(SUM_NUMBER_OF_BYTES_FREE): 5670 SUM(LOW_COUNT_USED): 0 SUM(CURRENT_COUNT_USED): 53 SUM(HIGH_COUNT_USED): 56 SUM(LOW_NUMBER_OF_BYTES_USED): 0 SUM(CURRENT_NUMBER_OF_BYTES_USED): 1065175 SUM(HIGH_NUMBER_OF_BYTES_USED): 1065175