MySQL 9.0 版本注意事項
本節說明使用儀器和事件來監控群組複製記憶體使用情況的範例查詢。這些查詢會從 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交易一致性保證的記憶體配置是 consistent_members_that_must_prepare_transaction、consistent_transactions、consistent_transactions_prepared、consistent_transactions_waiting 和 consistent_transactions_delayed_view_change 事件值的總和。例如
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_message 和 message_service_queue 事件值的總和。例如
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_buffer 和 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'
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