本文為英文版的機器翻譯版本，如內容有任何歧義或不一致之處，概以英文版為準。 # 針對 Aurora MySQL 資料庫的記憶體用量問題進行故障診斷 雖然 CloudWatch、增強型監控和 Performance Insights 在作業系統層級提供記憶體用量的良好概觀，例如資料庫程序使用的記憶體用量，但它們不允許您細分引擎內可能導致此記憶體用量的連線或元件。 若要對此進行故障診斷，您可以使用效能結構描述和 `sys` 結構描述。在 Aurora MySQL 第 3 版中，啟用效能結構描述時，預設會啟用記憶體檢測。在 Aurora MySQL 第 2 版中，預設只會啟用效能結構描述記憶體用量的記憶體檢測。如需效能結構描述中可用資料表的相關資訊，以追蹤記憶體用量並啟用效能結構描述記憶體檢測，請參閱 MySQL 文件中的[記憶體摘要資料表](https://dev.mysql.com/doc/refman/8.3/en/performance-schema-memory-summary-tables.html)。如需使用效能結構描述搭配 Performance Insights 的詳細資訊，請參閱 [Aurora MySQL 上 Performance Insights 的效能結構描述概觀](USER_PerfInsights.EnableMySQL.md)。 雖然效能結構描述中提供了詳細資訊來追蹤目前的記憶體用量，但 MySQL [sys 結構描述](https://dev.mysql.com/doc/refman/8.0/en/sys-schema.html)具有效能結構描述資料表的檢視，可讓您快速找出記憶體的使用位置。 在 `sys` 結構描述中，下列檢視可用於依連線、元件和查詢來追蹤記憶體用量。 | 檢視 | 描述 | | --- | --- | | [memory\$1by\$1host\$1by\$1current\$1bytes](https://dev.mysql.com/doc/refman/8.0/en/sys-memory-by-host-by-current-bytes.html) | 提供主機引擎記憶體用量的相關資訊。這有助於識別哪些應用程式伺服器或用戶端主機正在耗用記憶體。 | | [memory\$1by\$1thread\$1by\$1current\$1bytes](https://dev.mysql.com/doc/refman/8.0/en/sys-memory-by-thread-by-current-bytes.html) | 依執行緒 ID 提供引擎記憶體用量的相關資訊。MySQL 中的執行緒 ID 可以是用戶端連線或背景執行緒。您可以使用 [sys.processlist](https://dev.mysql.com/doc/refman/8.0/en/sys-processlist.html) 檢視或 [performance\$1schema.threads](https://dev.mysql.com/doc/refman/8.0/en/performance-schema-threads-table.html) 資料表，將執行 ID 映射至 MySQL 連線 ID。 | | [memory\$1by\$1user\$1by\$1current\$1bytes](https://dev.mysql.com/doc/refman/8.0/en/sys-memory-by-user-by-current-bytes.html) | 依使用者提供引擎記憶體用量的相關資訊。這有助於識別哪些使用者帳戶或用戶端正在耗用記憶體。 | | [memory\$1global\$1by\$1current\$1bytes](https://dev.mysql.com/doc/refman/8.0/en/sys-memory-global-by-current-bytes.html) | 依引擎元件提供引擎記憶體用量的相關資訊。這有助於依引擎緩衝區或元件全域識別記憶體用量。例如，您可能會看到 InnoDB 緩衝集區的 `memory/innodb/buf_buf_pool` 事件，或預備陳述式的 `memory/sql/Prepared_statement::main_mem_root` 事件。 | | [memory\$1global\$1total](https://dev.mysql.com/doc/refman/8.0/en/sys-memory-global-total.html) | 提供資料庫引擎中總追蹤記憶體用量的概觀。 | 在 Aurora MySQL 3.05 版及更高版本中，您也可以在[效能結構描述陳述式摘要表格中](https://dev.mysql.com/doc/refman/8.0/en/performance-schema-statement-summary-tables.html)，依陳述式摘要追蹤記憶體用量上限。陳述式摘要資料表包含標準化陳述式摘要及其執行的彙總統計資料。`MAX_TOTAL_MEMORY` 欄可協助您識別自上次重設統計資料後，或資料庫執行個體重新啟動後，查詢摘要所使用的記憶體上限。這有助於識別可能耗用大量記憶體的特定查詢。 **注意** 效能結構描述和 `sys` 結構描述會顯示伺服器上目前的記憶體使用量，以及每個連線和引擎元件所耗用記憶體的高水位標記。由於效能結構描述會保留在記憶體中，因此資料庫執行個體重新啟動時會重設資訊。若要隨時間維護歷程記錄，建議您在效能結構描述之外設定此資料的擷取和儲存。 **Topics** + [範例 1：持續高記憶體用量](#ams-workload-memory.example1) + [範例 2：暫時性記憶體峰值](#ams-workload-memory.example2) + [範例 3：可釋放的記憶體持續下降，且不會回收](#ams-workload-memory.example3) ## 範例 1：持續高記憶體用量 在 CloudWatch 中全域查看 `FreeableMemory`，我們可以看到記憶體用量在 2024-03-26 02:59 UTC 大幅增加。 ![\[FreeableMemory 圖表顯示高記憶體用量。\]](http://docs.aws.amazon.com/zh_tw/AmazonRDS/latest/AuroraUserGuide/images/ams-freeable-memory.png) 這不會告訴我們整體情況。若要判斷哪個元件使用最多記憶體，您可以登入資料庫並查看 `sys.memory_global_by_current_bytes`。此資料表包含 MySQL 追蹤的記憶體事件清單，以及每個事件的記憶體配置資訊。每個記憶體追蹤事件都以 `memory/%` 開頭，後面接著與事件相關聯之引擎元件/功能的其他資訊。 例如，`memory/performance_schema/%` 適用於與效能結構描述相關的記憶體事件，而 `memory/innodb/%` 適用於 InnoDB，以此類推。如需關於事件命名慣例的詳細資訊，請參閱 MySQL 文件中的[效能結構描述檢測命名慣例](https://dev.mysql.com/doc/refman/8.0/en/performance-schema-instrument-naming.html)。 從下列查詢中，我們可以根據 `current_alloc` 找出可能的問題點，但我們也可以看到許多 `memory/performance_schema/%` 事件。 ``` mysql> SELECT * FROM sys.memory_global_by_current_bytes LIMIT 10; +-----------------------------------------------------------------------------+---------------+---------------+-------------------+------------+------------+----------------+ | event_name | current_count | current_alloc | current_avg_alloc | high_count | high_alloc | high_avg_alloc | +-----------------------------------------------------------------------------+---------------+---------------+-------------------+------------+------------+----------------+ | memory/sql/Prepared_statement::main_mem_root | 512817 | 4.91 GiB | 10.04 KiB | 512823 | 4.91 GiB | 10.04 KiB | | memory/performance_schema/prepared_statements_instances | 252 | 488.25 MiB | 1.94 MiB | 252 | 488.25 MiB | 1.94 MiB | | memory/innodb/hash0hash | 4 | 79.07 MiB | 19.77 MiB | 4 | 79.07 MiB | 19.77 MiB | | memory/performance_schema/events_errors_summary_by_thread_by_error | 1028 | 52.27 MiB | 52.06 KiB | 1028 | 52.27 MiB | 52.06 KiB | | memory/performance_schema/events_statements_summary_by_thread_by_event_name | 4 | 47.25 MiB | 11.81 MiB | 4 | 47.25 MiB | 11.81 MiB | | memory/performance_schema/events_statements_summary_by_digest | 1 | 40.28 MiB | 40.28 MiB | 1 | 40.28 MiB | 40.28 MiB | | memory/performance_schema/memory_summary_by_thread_by_event_name | 4 | 31.64 MiB | 7.91 MiB | 4 | 31.64 MiB | 7.91 MiB | | memory/innodb/memory | 15227 | 27.44 MiB | 1.85 KiB | 20619 | 33.33 MiB | 1.66 KiB | | memory/sql/String::value | 74411 | 21.85 MiB | 307 bytes | 76867 | 25.54 MiB | 348 bytes | | memory/sql/TABLE | 8381 | 21.03 MiB | 2.57 KiB | 8381 | 21.03 MiB | 2.57 KiB | +-----------------------------------------------------------------------------+---------------+---------------+-------------------+------------+------------+----------------+ 10 rows in set (0.02 sec) ``` 我們先前提到效能結構描述存放在記憶體中，這表示它也會在 `performance_schema` 記憶體檢測中受到追蹤。 **注意** 如果您發現效能結構描述正在使用大量記憶體，並且想要限制其記憶體用量，您可以根據您的需求調整資料庫參數。如需詳細資訊，請參閱 MySQL 文件中的[效能結構描述記憶體配置模型](https://dev.mysql.com/doc/refman/8.0/en/performance-schema-memory-model.html)。 為了方便閱讀，您可以重新執行相同的查詢，但排除效能結構描述事件。輸出會顯示下列內容： + 主要記憶體取用者為 `memory/sql/Prepared_statement::main_mem_root`。 + `current_alloc` 欄會通知我們，MySQL 目前已配置 4.91 GiB 給此事件。 + `high_alloc column` 告訴我們 4.91 GiB 是自上次重設統計資料後或伺服器重新啟動後 `current_alloc` 的高水位標記。這表示 `memory/sql/Prepared_statement::main_mem_root` 處於其最高值。 ``` mysql> SELECT * FROM sys.memory_global_by_current_bytes WHERE event_name NOT LIKE 'memory/performance_schema/%' LIMIT 10; +-----------------------------------------------+---------------+---------------+-------------------+------------+------------+----------------+ | event_name | current_count | current_alloc | current_avg_alloc | high_count | high_alloc | high_avg_alloc | +-----------------------------------------------+---------------+---------------+-------------------+------------+------------+----------------+ | memory/sql/Prepared_statement::main_mem_root | 512817 | 4.91 GiB | 10.04 KiB | 512823 | 4.91 GiB | 10.04 KiB | | memory/innodb/hash0hash | 4 | 79.07 MiB | 19.77 MiB | 4 | 79.07 MiB | 19.77 MiB | | memory/innodb/memory | 17096 | 31.68 MiB | 1.90 KiB | 22498 | 37.60 MiB | 1.71 KiB | | memory/sql/String::value | 122277 | 27.94 MiB | 239 bytes | 124699 | 29.47 MiB | 247 bytes | | memory/sql/TABLE | 9927 | 24.67 MiB | 2.55 KiB | 9929 | 24.68 MiB | 2.55 KiB | | memory/innodb/lock0lock | 8888 | 19.71 MiB | 2.27 KiB | 8888 | 19.71 MiB | 2.27 KiB | | memory/sql/Prepared_statement::infrastructure | 257623 | 16.24 MiB | 66 bytes | 257631 | 16.24 MiB | 66 bytes | | memory/mysys/KEY_CACHE | 3 | 16.00 MiB | 5.33 MiB | 3 | 16.00 MiB | 5.33 MiB | | memory/innodb/sync0arr | 3 | 7.03 MiB | 2.34 MiB | 3 | 7.03 MiB | 2.34 MiB | | memory/sql/THD::main_mem_root | 815 | 6.56 MiB | 8.24 KiB | 849 | 7.19 MiB | 8.67 KiB | +-----------------------------------------------+---------------+---------------+-------------------+------------+------------+----------------+ 10 rows in set (0.06 sec) ``` 從事件的名稱，我們可以指出此記憶體正用於預備陳述式。如果您想要查看哪些連線使用此記憶體，您可以檢查 [memory\$1by\$1thread\$1by\$1current\$1bytes](https://dev.mysql.com/doc/refman/8.0/en/sys-memory-by-thread-by-current-bytes.html)。 在下列範例中，每個連線配置了大約 7 MiB，高水位標記約為 6.29 MiB (`current_max_alloc`)。這很合理，因為範例使用 `sysbench` 搭配 80 個資料表和 800 個連線以及預備陳述式。如果您想要在此案例中減少記憶體使用量，您可以最佳化應用程式對預備陳述式的使用量，以減少記憶體使用量。 ``` mysql> SELECT * FROM sys.memory_by_thread_by_current_bytes; +-----------+-------------------------------------------+--------------------+-------------------+-------------------+-------------------+-----------------+ | thread_id | user | current_count_used | current_allocated | current_avg_alloc | current_max_alloc | total_allocated | +-----------+-------------------------------------------+--------------------+-------------------+-------------------+-------------------+-----------------+ | 46 | rdsadmin@localhost | 405 | 8.47 MiB | 21.42 KiB | 8.00 MiB | 155.86 MiB | | 61 | reinvent@10.0.4.4 | 1749 | 6.72 MiB | 3.93 KiB | 6.29 MiB | 14.24 MiB | | 101 | reinvent@10.0.4.4 | 1845 | 6.71 MiB | 3.72 KiB | 6.29 MiB | 14.50 MiB | | 55 | reinvent@10.0.4.4 | 1674 | 6.68 MiB | 4.09 KiB | 6.29 MiB | 14.13 MiB | | 57 | reinvent@10.0.4.4 | 1416 | 6.66 MiB | 4.82 KiB | 6.29 MiB | 13.52 MiB | | 112 | reinvent@10.0.4.4 | 1759 | 6.66 MiB | 3.88 KiB | 6.29 MiB | 14.17 MiB | | 66 | reinvent@10.0.4.4 | 1428 | 6.64 MiB | 4.76 KiB | 6.29 MiB | 13.47 MiB | | 75 | reinvent@10.0.4.4 | 1389 | 6.62 MiB | 4.88 KiB | 6.29 MiB | 13.40 MiB | | 116 | reinvent@10.0.4.4 | 1333 | 6.61 MiB | 5.08 KiB | 6.29 MiB | 13.21 MiB | | 90 | reinvent@10.0.4.4 | 1448 | 6.59 MiB | 4.66 KiB | 6.29 MiB | 13.58 MiB | | 98 | reinvent@10.0.4.4 | 1440 | 6.57 MiB | 4.67 KiB | 6.29 MiB | 13.52 MiB | | 94 | reinvent@10.0.4.4 | 1433 | 6.57 MiB | 4.69 KiB | 6.29 MiB | 13.49 MiB | | 62 | reinvent@10.0.4.4 | 1323 | 6.55 MiB | 5.07 KiB | 6.29 MiB | 13.48 MiB | | 87 | reinvent@10.0.4.4 | 1323 | 6.55 MiB | 5.07 KiB | 6.29 MiB | 13.25 MiB | | 99 | reinvent@10.0.4.4 | 1346 | 6.54 MiB | 4.98 KiB | 6.29 MiB | 13.24 MiB | | 105 | reinvent@10.0.4.4 | 1347 | 6.54 MiB | 4.97 KiB | 6.29 MiB | 13.34 MiB | | 73 | reinvent@10.0.4.4 | 1335 | 6.54 MiB | 5.02 KiB | 6.29 MiB | 13.23 MiB | | 54 | reinvent@10.0.4.4 | 1510 | 6.53 MiB | 4.43 KiB | 6.29 MiB | 13.49 MiB | . . . . . . | 812 | reinvent@10.0.4.4 | 1259 | 6.38 MiB | 5.19 KiB | 6.29 MiB | 13.05 MiB | | 214 | reinvent@10.0.4.4 | 1279 | 6.38 MiB | 5.10 KiB | 6.29 MiB | 12.90 MiB | | 325 | reinvent@10.0.4.4 | 1254 | 6.38 MiB | 5.21 KiB | 6.29 MiB | 12.99 MiB | | 705 | reinvent@10.0.4.4 | 1273 | 6.37 MiB | 5.13 KiB | 6.29 MiB | 13.03 MiB | | 530 | reinvent@10.0.4.4 | 1268 | 6.37 MiB | 5.15 KiB | 6.29 MiB | 12.92 MiB | | 307 | reinvent@10.0.4.4 | 1263 | 6.37 MiB | 5.17 KiB | 6.29 MiB | 12.87 MiB | | 738 | reinvent@10.0.4.4 | 1260 | 6.37 MiB | 5.18 KiB | 6.29 MiB | 13.00 MiB | | 819 | reinvent@10.0.4.4 | 1252 | 6.37 MiB | 5.21 KiB | 6.29 MiB | 13.01 MiB | | 31 | innodb/srv_purge_thread | 17810 | 3.14 MiB | 184 bytes | 2.40 MiB | 205.69 MiB | | 38 | rdsadmin@localhost | 599 | 1.76 MiB | 3.01 KiB | 1.00 MiB | 25.58 MiB | | 1 | sql/main | 3756 | 1.32 MiB | 367 bytes | 355.78 KiB | 6.19 MiB | | 854 | rdsadmin@localhost | 46 | 1.08 MiB | 23.98 KiB | 1.00 MiB | 5.10 MiB | | 30 | innodb/clone_gtid_thread | 1596 | 573.14 KiB | 367 bytes | 254.91 KiB | 970.69 KiB | | 40 | rdsadmin@localhost | 235 | 245.19 KiB | 1.04 KiB | 128.88 KiB | 808.64 KiB | | 853 | rdsadmin@localhost | 96 | 94.63 KiB | 1009 bytes | 29.73 KiB | 422.45 KiB | | 36 | rdsadmin@localhost | 33 | 36.29 KiB | 1.10 KiB | 16.08 KiB | 74.15 MiB | | 33 | sql/event_scheduler | 3 | 16.27 KiB | 5.42 KiB | 16.04 KiB | 16.27 KiB | | 35 | sql/compress_gtid_table | 8 | 14.20 KiB | 1.77 KiB | 8.05 KiB | 18.62 KiB | | 25 | innodb/fts_optimize_thread | 12 | 1.86 KiB | 158 bytes | 648 bytes | 1.98 KiB | | 23 | innodb/srv_master_thread | 11 | 1.23 KiB | 114 bytes | 361 bytes | 24.40 KiB | | 24 | innodb/dict_stats_thread | 11 | 1.23 KiB | 114 bytes | 361 bytes | 1.35 KiB | | 5 | innodb/io_read_thread | 1 | 144 bytes | 144 bytes | 144 bytes | 144 bytes | | 6 | innodb/io_read_thread | 1 | 144 bytes | 144 bytes | 144 bytes | 144 bytes | | 2 | sql/aws_oscar_log_level_monitor | 0 | 0 bytes | 0 bytes | 0 bytes | 0 bytes | | 4 | innodb/io_ibuf_thread | 0 | 0 bytes | 0 bytes | 0 bytes | 0 bytes | | 7 | innodb/io_write_thread | 0 | 0 bytes | 0 bytes | 0 bytes | 0 bytes | | 8 | innodb/io_write_thread | 0 | 0 bytes | 0 bytes | 0 bytes | 0 bytes | | 9 | innodb/io_write_thread | 0 | 0 bytes | 0 bytes | 0 bytes | 0 bytes | | 10 | innodb/io_write_thread | 0 | 0 bytes | 0 bytes | 0 bytes | 0 bytes | | 11 | innodb/srv_lra_thread | 0 | 0 bytes | 0 bytes | 0 bytes | 0 bytes | | 12 | innodb/srv_akp_thread | 0 | 0 bytes | 0 bytes | 0 bytes | 0 bytes | | 18 | innodb/srv_lock_timeout_thread | 0 | 0 bytes | 0 bytes | 0 bytes | 248 bytes | | 19 | innodb/srv_error_monitor_thread | 0 | 0 bytes | 0 bytes | 0 bytes | 0 bytes | | 20 | innodb/srv_monitor_thread | 0 | 0 bytes | 0 bytes | 0 bytes | 0 bytes | | 21 | innodb/buf_resize_thread | 0 | 0 bytes | 0 bytes | 0 bytes | 0 bytes | | 22 | innodb/btr_search_sys_toggle_thread | 0 | 0 bytes | 0 bytes | 0 bytes | 0 bytes | | 32 | innodb/dict_persist_metadata_table_thread | 0 | 0 bytes | 0 bytes | 0 bytes | 0 bytes | | 34 | sql/signal_handler | 0 | 0 bytes | 0 bytes | 0 bytes | 0 bytes | +-----------+-------------------------------------------+--------------------+-------------------+-------------------+-------------------+-----------------+ 831 rows in set (2.48 sec) ``` 如前所述，此處的執行緒 ID (`thd_id`) 值可以參考伺服器背景執行緒或資料庫連線。如果您想要將執行緒 ID 值映射至資料庫連線 ID，您可以使用 `performance_schema.threads` 資料表或 `sys.processlist` 檢視，其中 `conn_id` 是連線 ID。 ``` mysql> SELECT thd_id,conn_id,user,db,command,state,time,last_wait FROM sys.processlist WHERE user='reinvent@10.0.4.4'; +--------+---------+-------------------+----------+---------+----------------+------+-------------------------------------------------+ | thd_id | conn_id | user | db | command | state | time | last_wait | +--------+---------+-------------------+----------+---------+----------------+------+-------------------------------------------------+ | 590 | 562 | reinvent@10.0.4.4 | sysbench | Execute | closing tables | 0 | wait/io/redo_log_flush | | 578 | 550 | reinvent@10.0.4.4 | sysbench | Sleep | NULL | 0 | idle | | 579 | 551 | reinvent@10.0.4.4 | sysbench | Execute | closing tables | 0 | wait/io/redo_log_flush | | 580 | 552 | reinvent@10.0.4.4 | sysbench | Execute | updating | 0 | wait/io/table/sql/handler | | 581 | 553 | reinvent@10.0.4.4 | sysbench | Execute | updating | 0 | wait/io/table/sql/handler | | 582 | 554 | reinvent@10.0.4.4 | sysbench | Sleep | NULL | 0 | idle | | 583 | 555 | reinvent@10.0.4.4 | sysbench | Sleep | NULL | 0 | idle | | 584 | 556 | reinvent@10.0.4.4 | sysbench | Execute | updating | 0 | wait/io/table/sql/handler | | 585 | 557 | reinvent@10.0.4.4 | sysbench | Execute | closing tables | 0 | wait/io/redo_log_flush | | 586 | 558 | reinvent@10.0.4.4 | sysbench | Execute | updating | 0 | wait/io/table/sql/handler | | 587 | 559 | reinvent@10.0.4.4 | sysbench | Execute | closing tables | 0 | wait/io/redo_log_flush | . . . . . . | 323 | 295 | reinvent@10.0.4.4 | sysbench | Sleep | NULL | 0 | idle | | 324 | 296 | reinvent@10.0.4.4 | sysbench | Execute | updating | 0 | wait/io/table/sql/handler | | 325 | 297 | reinvent@10.0.4.4 | sysbench | Execute | closing tables | 0 | wait/io/redo_log_flush | | 326 | 298 | reinvent@10.0.4.4 | sysbench | Execute | updating | 0 | wait/io/table/sql/handler | | 438 | 410 | reinvent@10.0.4.4 | sysbench | Execute | System lock | 0 | wait/lock/table/sql/handler | | 280 | 252 | reinvent@10.0.4.4 | sysbench | Sleep | starting | 0 | wait/io/socket/sql/client_connection | | 98 | 70 | reinvent@10.0.4.4 | sysbench | Query | freeing items | 0 | NULL | +--------+---------+-------------------+----------+---------+----------------+------+-------------------------------------------------+ 804 rows in set (5.51 sec) ``` 現在，我們停止 `sysbench` 工作負載，這會關閉連線並釋放記憶體。再次檢查事件，我們可以確認記憶體已釋出，但 `high_alloc` 仍告訴我們什麼是高水位標記。`high_alloc` 欄在識別記憶體用量中的短峰值時非常有用，其中您可能無法立即識別來自 `current_alloc` 的用量，這只會顯示目前配置的記憶體。 ``` mysql> SELECT * FROM sys.memory_global_by_current_bytes WHERE event_name='memory/sql/Prepared_statement::main_mem_root' LIMIT 10; +----------------------------------------------+---------------+---------------+-------------------+------------+------------+----------------+ | event_name | current_count | current_alloc | current_avg_alloc | high_count | high_alloc | high_avg_alloc | +----------------------------------------------+---------------+---------------+-------------------+------------+------------+----------------+ | memory/sql/Prepared_statement::main_mem_root | 17 | 253.80 KiB | 14.93 KiB | 512823 | 4.91 GiB | 10.04 KiB | +----------------------------------------------+---------------+---------------+-------------------+------------+------------+----------------+ 1 row in set (0.00 sec) ``` 如果您想要重設 `high_alloc`，您可以截斷 `performance_schema` 記憶體摘要資料表，但這會重設所有記憶體檢測。如需詳細資訊，請參閱 MySQL 文件中的[效能結構描述一般資料表特性](https://dev.mysql.com/doc/refman/8.0/en/performance-schema-table-characteristics.html)。 在下列範例中，我們可以看到 `high_alloc` 會在截斷後重設。 ``` mysql> TRUNCATE `performance_schema`.`memory_summary_global_by_event_name`; Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM sys.memory_global_by_current_bytes WHERE event_name='memory/sql/Prepared_statement::main_mem_root' LIMIT 10; +----------------------------------------------+---------------+---------------+-------------------+------------+------------+----------------+ | event_name | current_count | current_alloc | current_avg_alloc | high_count | high_alloc | high_avg_alloc | +----------------------------------------------+---------------+---------------+-------------------+------------+------------+----------------+ | memory/sql/Prepared_statement::main_mem_root | 17 | 253.80 KiB | 14.93 KiB | 17 | 253.80 KiB | 14.93 KiB | +----------------------------------------------+---------------+---------------+-------------------+------------+------------+----------------+ 1 row in set (0.00 sec) ``` ## 範例 2：暫時性記憶體峰值 另一個常見情況是資料庫伺服器上記憶體用量的短暫峰值。這些可能是在 `sys.memory_global_by_current_bytes` 中使用 `current_alloc` 時難以故障診斷的可用記憶體中定期下降，因為記憶體已釋放。 **注意** 如果已重設效能結構描述統計資料，或資料庫執行個體已重新啟動，此資訊將無法在 `sys` 或 p`erformance_schema` 中使用。若要保留此資訊，建議您設定外部指標集合。 增強型監控中 `os.memory.free` 指標的下圖顯示記憶體用量的短暫 7 秒峰值。增強型監控可讓您以最短 1 秒的間隔進行監控，這非常適合用於捕捉像這樣的暫時性峰值。 ![\[顯示暫時性記憶體用量隨時間遽增的圖表，週期性模式表示潛在的記憶體管理問題。\]](http://docs.aws.amazon.com/zh_tw/AmazonRDS/latest/AuroraUserGuide/images/ams-free-memory-spikes.png) 為了協助診斷此處記憶體用量的原因，我們可以在 `sys` 記憶體摘要檢視和[效能結構描述陳述式摘要資料表](https://dev.mysql.com/doc/refman/8.0/en/performance-schema-statement-summary-tables.html)中使用 `high_alloc` 的組合，嘗試識別違規的工作階段和連線。 如預期，由於記憶體使用量目前不高，因此在 `current_alloc` 下的 `sys` 結構描述檢視中看不到任何主要違規項目。 ``` mysql> SELECT * FROM sys.memory_global_by_current_bytes LIMIT 10; +-----------------------------------------------------------------------------+---------------+---------------+-------------------+------------+------------+----------------+ | event_name | current_count | current_alloc | current_avg_alloc | high_count | high_alloc | high_avg_alloc | +-----------------------------------------------------------------------------+---------------+---------------+-------------------+------------+------------+----------------+ | memory/innodb/hash0hash | 4 | 79.07 MiB | 19.77 MiB | 4 | 79.07 MiB | 19.77 MiB | | memory/innodb/os0event | 439372 | 60.34 MiB | 144 bytes | 439372 | 60.34 MiB | 144 bytes | | memory/performance_schema/events_statements_summary_by_digest | 1 | 40.28 MiB | 40.28 MiB | 1 | 40.28 MiB | 40.28 MiB | | memory/mysys/KEY_CACHE | 3 | 16.00 MiB | 5.33 MiB | 3 | 16.00 MiB | 5.33 MiB | | memory/performance_schema/events_statements_history_long | 1 | 14.34 MiB | 14.34 MiB | 1 | 14.34 MiB | 14.34 MiB | | memory/performance_schema/events_errors_summary_by_thread_by_error | 257 | 13.07 MiB | 52.06 KiB | 257 | 13.07 MiB | 52.06 KiB | | memory/performance_schema/events_statements_summary_by_thread_by_event_name | 1 | 11.81 MiB | 11.81 MiB | 1 | 11.81 MiB | 11.81 MiB | | memory/performance_schema/events_statements_summary_by_digest.digest_text | 1 | 9.77 MiB | 9.77 MiB | 1 | 9.77 MiB | 9.77 MiB | | memory/performance_schema/events_statements_history_long.digest_text | 1 | 9.77 MiB | 9.77 MiB | 1 | 9.77 MiB | 9.77 MiB | | memory/performance_schema/events_statements_history_long.sql_text | 1 | 9.77 MiB | 9.77 MiB | 1 | 9.77 MiB | 9.77 MiB | +-----------------------------------------------------------------------------+---------------+---------------+-------------------+------------+------------+----------------+ 10 rows in set (0.01 sec) ``` 展開檢視以依 `high_alloc` 排序，我們現在可以在這裡看到 `memory/temptable/physical_ram` 元件是很好的候選項目。其最高耗用 515.00 MiB。 顧名思義，`memory/temptable/physical_ram` 會在 MySQL 8.0 中引入的 MySQL 中檢測 `TEMP` 儲存引擎的記憶體用量。如需有關 MySQL 如何使用暫存資料表的詳細資訊，請參閱 MySQL 文件中的 [MySQL 中的內部暫存資料表使用](https://dev.mysql.com/doc/refman/8.0/en/internal-temporary-tables.html)。 **注意** 我們在此範例中使用 `sys.x$memory_global_by_current_bytes` 檢視。 ``` mysql> SELECT event_name, format_bytes(current_alloc) AS "currently allocated", sys.format_bytes(high_alloc) AS "high-water mark" FROM sys.x$memory_global_by_current_bytes ORDER BY high_alloc DESC LIMIT 10; +-----------------------------------------------------------------------------+---------------------+-----------------+ | event_name | currently allocated | high-water mark | +-----------------------------------------------------------------------------+---------------------+-----------------+ | memory/temptable/physical_ram | 4.00 MiB | 515.00 MiB | | memory/innodb/hash0hash | 79.07 MiB | 79.07 MiB | | memory/innodb/os0event | 63.95 MiB | 63.95 MiB | | memory/performance_schema/events_statements_summary_by_digest | 40.28 MiB | 40.28 MiB | | memory/mysys/KEY_CACHE | 16.00 MiB | 16.00 MiB | | memory/performance_schema/events_statements_history_long | 14.34 MiB | 14.34 MiB | | memory/performance_schema/events_errors_summary_by_thread_by_error | 13.07 MiB | 13.07 MiB | | memory/performance_schema/events_statements_summary_by_thread_by_event_name | 11.81 MiB | 11.81 MiB | | memory/performance_schema/events_statements_summary_by_digest.digest_text | 9.77 MiB | 9.77 MiB | | memory/performance_schema/events_statements_history_long.sql_text | 9.77 MiB | 9.77 MiB | +-----------------------------------------------------------------------------+---------------------+-----------------+ 10 rows in set (0.00 sec) ``` 在 [範例 1：持續高記憶體用量](#ams-workload-memory.example1) 中，我們檢查了每個連線目前的記憶體用量，以判斷哪些連線負責使用有問題的記憶體。在此範例中，記憶體已釋放，因此檢查目前連線的記憶體用量並不實用。 為了深入了解並尋找違規的陳述式、使用者和主機，我們會使用效能結構描述。效能結構描述包含多個由不同維度分割的陳述式摘要資料表，例如事件名稱、陳述式摘要、主機、執行緒和使用者。每個檢視都可讓您深入了解特定陳述式的執行位置及其執行方式。本節著重於 `MAX_TOTAL_MEMORY`，但您可以在[效能結構描述陳述式摘要資料表](https://dev.mysql.com/doc/refman/8.0/en/performance-schema-statement-summary-tables.html)文件中，找到所有可用資料欄的詳細資訊。 ``` mysql> SHOW TABLES IN performance_schema LIKE 'events_statements_summary_%'; +------------------------------------------------------------+ | Tables_in_performance_schema (events_statements_summary_%) | +------------------------------------------------------------+ | events_statements_summary_by_account_by_event_name | | events_statements_summary_by_digest | | events_statements_summary_by_host_by_event_name | | events_statements_summary_by_program | | events_statements_summary_by_thread_by_event_name | | events_statements_summary_by_user_by_event_name | | events_statements_summary_global_by_event_name | +------------------------------------------------------------+ 7 rows in set (0.00 sec) ``` 首先，我們會檢查 `events_statements_summary_by_digest` 以查看 `MAX_TOTAL_MEMORY`。 在這裡，我們可以看到以下內容： + 具有摘要 `20676ce4a690592ff05debcffcbc26faeb76f22005e7628364d7a498769d0c4a` 的查詢似乎適合此記憶體用量。`MAX_TOTAL_MEMORY` 是 537450710，符合我們在 `sys.x$memory_global_by_current_bytes` 中看到 `memory/temptable/physical_ram` 事件的高水位標記。 + 它已執行四次 (`COUNT_STAR`)，第一個是 2024-03-26 04:08:34.943256，最後一個是 2024-03-26 04:43:06.998310。 ``` mysql> SELECT SCHEMA_NAME,DIGEST,COUNT_STAR,MAX_TOTAL_MEMORY,FIRST_SEEN,LAST_SEEN FROM performance_schema.events_statements_summary_by_digest ORDER BY MAX_TOTAL_MEMORY DESC LIMIT 5; +-------------+------------------------------------------------------------------+------------+------------------+----------------------------+----------------------------+ | SCHEMA_NAME | DIGEST | COUNT_STAR | MAX_TOTAL_MEMORY | FIRST_SEEN | LAST_SEEN | +-------------+------------------------------------------------------------------+------------+------------------+----------------------------+----------------------------+ | sysbench | 20676ce4a690592ff05debcffcbc26faeb76f22005e7628364d7a498769d0c4a | 4 | 537450710 | 2024-03-26 04:08:34.943256 | 2024-03-26 04:43:06.998310 | | NULL | f158282ea0313fefd0a4778f6e9b92fc7d1e839af59ebd8c5eea35e12732c45d | 4 | 3636413 | 2024-03-26 04:29:32.712348 | 2024-03-26 04:36:26.269329 | | NULL | 0046bc5f642c586b8a9afd6ce1ab70612dc5b1fd2408fa8677f370c1b0ca3213 | 2 | 3459965 | 2024-03-26 04:31:37.674008 | 2024-03-26 04:32:09.410718 | | NULL | 8924f01bba3c55324701716c7b50071a60b9ceaf17108c71fd064c20c4ab14db | 1 | 3290981 | 2024-03-26 04:31:49.751506 | 2024-03-26 04:31:49.751506 | | NULL | 90142bbcb50a744fcec03a1aa336b2169761597ea06d85c7f6ab03b5a4e1d841 | 1 | 3131729 | 2024-03-26 04:15:09.719557 | 2024-03-26 04:15:09.719557 | +-------------+------------------------------------------------------------------+------------+------------------+----------------------------+----------------------------+ 5 rows in set (0.00 sec) ``` 現在我們知道違規摘要，我們可以取得更多詳細資訊，例如查詢文字、執行該摘要的使用者，以及執行該摘要的位置。根據傳回的摘要文字，我們可以看到這是一個常見的資料表運算式 (CTE)，可建立四個暫存資料表並執行四個資料表掃描，這非常沒有效率。 ``` mysql> SELECT SCHEMA_NAME,DIGEST_TEXT,QUERY_SAMPLE_TEXT,MAX_TOTAL_MEMORY,SUM_ROWS_SENT,SUM_ROWS_EXAMINED,SUM_CREATED_TMP_TABLES,SUM_NO_INDEX_USED FROM performance_schema.events_statements_summary_by_digest WHERE DIGEST='20676ce4a690592ff05debcffcbc26faeb76f22005e7628364d7a498769d0c4a'\G; *************************** 1. row *************************** SCHEMA_NAME: sysbench DIGEST_TEXT: WITH RECURSIVE `cte` ( `n` ) AS ( SELECT ? FROM `sbtest1` UNION ALL SELECT `id` + ? FROM `sbtest1` ) SELECT * FROM `cte` QUERY_SAMPLE_TEXT: WITH RECURSIVE cte (n) AS ( SELECT 1 from sbtest1 UNION ALL SELECT id + 1 FROM sbtest1) SELECT * FROM cte MAX_TOTAL_MEMORY: 537450710 SUM_ROWS_SENT: 80000000 SUM_ROWS_EXAMINED: 80000000 SUM_CREATED_TMP_TABLES: 4 SUM_NO_INDEX_USED: 4 1 row in set (0.01 sec) ``` 如需 `events_statements_summary_by_digest` 資料表和其他效能結構描述陳述式摘要資料表的詳細資訊，請參閱 MySQL 文件中的[陳述式摘要資料表](https://dev.mysql.com/doc/refman/8.0/en/performance-schema-statement-summary-tables.html)。 您也可以執行 [EXPLAIN](https://dev.mysql.com/doc/refman/8.0/en/explain.html) 或 [EXPLAIN ANALYZE](https://dev.mysql.com/doc/refman/8.0/en/explain.html#explain-analyze) 陳述式，以查看更多詳細資訊。 **注意** `EXPLAIN ANALYZE` 可以提供比 `EXPLAIN` 更多的資訊，但它也會執行查詢，因此請注意。 ``` -- EXPLAIN mysql> EXPLAIN WITH RECURSIVE cte (n) AS (SELECT 1 FROM sbtest1 UNION ALL SELECT id + 1 FROM sbtest1) SELECT * FROM cte; +----+-------------+------------+------------+-------+---------------+------+---------+------+----------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+------------+-------+---------------+------+---------+------+----------+----------+-------------+ | 1 | PRIMARY | | NULL | ALL | NULL | NULL | NULL | NULL | 19221520 | 100.00 | NULL | | 2 | DERIVED | sbtest1 | NULL | index | NULL | k_1 | 4 | NULL | 9610760 | 100.00 | Using index | | 3 | UNION | sbtest1 | NULL | index | NULL | k_1 | 4 | NULL | 9610760 | 100.00 | Using index | +----+-------------+------------+------------+-------+---------------+------+---------+------+----------+----------+-------------+ 3 rows in set, 1 warning (0.00 sec) -- EXPLAIN format=tree mysql> EXPLAIN format=tree WITH RECURSIVE cte (n) AS (SELECT 1 FROM sbtest1 UNION ALL SELECT id + 1 FROM sbtest1) SELECT * FROM cte\G; *************************** 1. row *************************** EXPLAIN: -> Table scan on cte (cost=4.11e+6..4.35e+6 rows=19.2e+6) -> Materialize union CTE cte (cost=4.11e+6..4.11e+6 rows=19.2e+6) -> Index scan on sbtest1 using k_1 (cost=1.09e+6 rows=9.61e+6) -> Index scan on sbtest1 using k_1 (cost=1.09e+6 rows=9.61e+6) 1 row in set (0.00 sec) -- EXPLAIN ANALYZE mysql> EXPLAIN ANALYZE WITH RECURSIVE cte (n) AS (SELECT 1 from sbtest1 UNION ALL SELECT id + 1 FROM sbtest1) SELECT * FROM cte\G; *************************** 1. row *************************** EXPLAIN: -> Table scan on cte (cost=4.11e+6..4.35e+6 rows=19.2e+6) (actual time=6666..9201 rows=20e+6 loops=1) -> Materialize union CTE cte (cost=4.11e+6..4.11e+6 rows=19.2e+6) (actual time=6666..6666 rows=20e+6 loops=1) -> Covering index scan on sbtest1 using k_1 (cost=1.09e+6 rows=9.61e+6) (actual time=0.0365..2006 rows=10e+6 loops=1) -> Covering index scan on sbtest1 using k_1 (cost=1.09e+6 rows=9.61e+6) (actual time=0.0311..2494 rows=10e+6 loops=1) 1 row in set (10.53 sec) ``` 但誰執行了查詢？ 我們可以在效能結構描述中看到 `destructive_operator` 使用者擁有 537450710 的 `MAX_TOTAL_MEMORY`，這再次符合先前的結果。 **注意** 效能結構描述存放在記憶體中，因此不應依賴而作為稽核的唯一來源。如果您需要維護陳述式執行的歷程記錄，以及從哪些使用者執行，我們建議您啟用 [Aurora 進階稽核](AuroraMySQL.Auditing.md)。如果您還需要維護記憶體用量的相關資訊，建議您設定監控以匯出和儲存這些值。 ``` mysql> SELECT USER,EVENT_NAME,COUNT_STAR,MAX_TOTAL_MEMORY FROM performance_schema.events_statements_summary_by_user_by_event_name ORDER BY MAX_CONTROLLED_MEMORY DESC LIMIT 5; +----------------------+---------------------------+------------+------------------+ | USER | EVENT_NAME | COUNT_STAR | MAX_TOTAL_MEMORY | +----------------------+---------------------------+------------+------------------+ | destructive_operator | statement/sql/select | 4 | 537450710 | | rdsadmin | statement/sql/select | 4172 | 3290981 | | rdsadmin | statement/sql/show_tables | 2 | 3615821 | | rdsadmin | statement/sql/show_fields | 2 | 3459965 | | rdsadmin | statement/sql/show_status | 75 | 1914976 | +----------------------+---------------------------+------------+------------------+ 5 rows in set (0.00 sec) mysql> SELECT HOST,EVENT_NAME,COUNT_STAR,MAX_TOTAL_MEMORY FROM performance_schema.events_statements_summary_by_host_by_event_name WHERE HOST != 'localhost' AND COUNT_STAR>0 ORDER BY MAX_CONTROLLED_MEMORY DESC LIMIT 5; +------------+----------------------+------------+------------------+ | HOST | EVENT_NAME | COUNT_STAR | MAX_TOTAL_MEMORY | +------------+----------------------+------------+------------------+ | 10.0.8.231 | statement/sql/select | 4 | 537450710 | +------------+----------------------+------------+------------------+ 1 row in set (0.00 sec) ``` ## 範例 3：可釋放的記憶體持續下降，且不會回收 InnoDB 資料庫引擎針對不同的元件採用一系列專門的記憶體追蹤事件。這些特定事件允許精細追蹤索引鍵 InnoDB 子系統中的記憶體用量，例如： + `memory/innodb/buf0buf` – 專用於監控 InnoDB 緩衝集區的記憶體配置。 + `memory/innodb/ibuf0ibuf` – 專門追蹤與 InnoDB 變更緩衝區相關的記憶體變更。 為了識別記憶體的最高取用者，我們可以查詢 `sys.memory_global_by_current_bytes`： ``` mysql> SELECT event_name,current_alloc FROM sys.memory_global_by_current_bytes LIMIT 10; +-----------------------------------------------------------------+---------------+ | event_name | current_alloc | +-----------------------------------------------------------------+---------------+ | memory/innodb/memory | 5.28 GiB | | memory/performance_schema/table_io_waits_summary_by_index_usage | 495.00 MiB | | memory/performance_schema/table_shares | 488.00 MiB | | memory/sql/TABLE_SHARE::mem_root | 388.95 MiB | | memory/innodb/std | 226.88 MiB | | memory/innodb/fil0fil | 198.49 MiB | | memory/sql/binlog_io_cache | 128.00 MiB | | memory/innodb/mem0mem | 96.82 MiB | | memory/innodb/dict0dict | 96.76 MiB | | memory/performance_schema/rwlock_instances | 88.00 MiB | +-----------------------------------------------------------------+---------------+ 10 rows in set (0.00 sec) ``` 結果顯示 `memory/innodb/memory` 是目前配置記憶體中使用 5.28 GiB 的最高取用者。此事件可做為與更具體等待事件無關之各種 InnoDB 元件的記憶體配置類別，如先前 `memory/innodb/buf0buf` 所述。 在確定 InnoDB 元件是記憶體的主要取用者之後，我們可以使用下列 MySQL 命令深入探討細節： ``` SHOW ENGINE INNODB STATUS \G; ``` [SHOW ENGINE INNODB STATUS](https://dev.mysql.com/doc/refman/8.4/en/show-engine.html) 命令提供 InnoDB 儲存引擎的完整狀態報告，包括不同 InnoDB 元件的詳細記憶體用量統計資料。它有助於識別哪些特定的 InnoDB 結構或操作耗用最多的記憶體。如需詳細資訊，請參閱 MySQL 文件中的 [InnoDB 記憶體內結構](https://dev.mysql.com/doc/refman/8.0/en/innodb-in-memory-structures.html)。 分析 InnoDB 狀態報告的 `BUFFER POOL AND MEMORY` 區段時，我們看到 5,051,647,748 個位元組 (4.7 GiB) 配置到[字典物件快取](https://dev.mysql.com/doc/refman/8.0/en/data-dictionary-object-cache.html)，該快取佔了 `memory/innodb/memory` 所追蹤記憶體的 89%。 ``` ---------------------- BUFFER POOL AND MEMORY ---------------------- Total large memory allocated 0 Dictionary memory allocated 5051647748 Buffer pool size 170512 Free buffers 142568 Database pages 27944 Old database pages 10354 Modified db pages 6 Pending reads 0 ``` 字典物件快取是共用的全域快取，可將先前存取的資料字典物件存放在記憶體中，以啟用物件重複使用並改善效能。字典物件快取的高記憶體配置會建議資料字典快取中的大量資料庫物件。 現在我們知道資料字典快取是主要取用者，我們繼續檢查資料字典快取是否有開啟的資料表。若要尋找資料表定義快取中的資料表數目，請查詢全域狀態變數 [open\$1table\$1definitions](https://dev.mysql.com/doc/refman/8.4/en/server-status-variables.html#statvar_Open_table_definitions)。 ``` mysql> show global status like 'open_table_definitions'; +------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | Open_table_definitions | 20000 | +------------------------+-------+ 1 row in set (0.00 sec) ``` 如需詳細資訊，請參閱 MySQL 文件中的 [MySQL 如何開啟及關閉資料表](https://dev.mysql.com/doc/refman/8.0/en/table-cache.html)。 您可以透過限制資料庫叢集或資料庫執行個體參數群組中的 `table_definition_cache` 參數，來限制資料字典快取中的資料表定義數目。對於 Aurora MySQL，此值可作為資料表定義快取中資料表數量的軟性限制。預設值取決於執行個體類別，並設定為以下內容： ``` LEAST({DBInstanceClassMemory/393040}, 20000) ``` 當資料表數量超過 `table_definition_cache` 限制時，至少最近最少使用 (LRU) 機制會從快取中移出和移除資料表。不過，涉及外部索引鍵關係的資料表不會放置在 LRU 清單中，以防止其移除。 在我們目前的案例中，我們會執行 [FLUSH TABLES](https://dev.mysql.com/doc/refman/8.4/en/flush.html) 來清除資料表定義快取。此動作會導致 [Open\$1table\$1definitions](https://dev.mysql.com/doc/refman/8.0/en/server-status-variables.html#statvar_Open_table_definitions) 全域狀態變數從 20,000 大幅降至 12，如下所示： ``` mysql> show global status like 'open_table_definitions'; +------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | Open_table_definitions | 12 | +------------------------+-------+ 1 row in set (0.00 sec) ``` 儘管如此減少，我們仍觀察到 `memory/innodb/memory` 的記憶體配置在 5.18 GiB 維持很高，而配置的字典記憶體也保持不變。這在下列查詢結果中很明顯： ``` mysql> SELECT event_name,current_alloc FROM sys.memory_global_by_current_bytes LIMIT 10; +-----------------------------------------------------------------+---------------+ | event_name | current_alloc | +-----------------------------------------------------------------+---------------+ | memory/innodb/memory | 5.18 GiB | | memory/performance_schema/table_io_waits_summary_by_index_usage | 495.00 MiB | | memory/performance_schema/table_shares | 488.00 MiB | | memory/sql/TABLE_SHARE::mem_root | 388.95 MiB | | memory/innodb/std | 226.88 MiB | | memory/innodb/fil0fil | 198.49 MiB | | memory/sql/binlog_io_cache | 128.00 MiB | | memory/innodb/mem0mem | 96.82 MiB | | memory/innodb/dict0dict | 96.76 MiB | | memory/performance_schema/rwlock_instances | 88.00 MiB | +-----------------------------------------------------------------+---------------+ 10 rows in set (0.00 sec) ``` ``` ---------------------- BUFFER POOL AND MEMORY ---------------------- Total large memory allocated 0 Dictionary memory allocated 5001599639 Buffer pool size 170512 Free buffers 142568 Database pages 27944 Old database pages 10354 Modified db pages 6 Pending reads 0 ``` 這種持續高的記憶體用量可以歸因於涉及外部索引鍵關係的資料表。這些資料表不會放在 LRU 清單中進行移除，這解釋了即使在清除資料表定義快取之後，記憶體配置仍保持很高的原因。 解決此問題： 1. 檢閱和最佳化資料庫結構描述，特別是外部索引鍵關係。 1. 請考慮移至具有更多記憶體的大型資料庫執行個體類別，以容納您的字典物件。 透過遵循這些步驟並了解記憶體配置模式，您可以更好地管理 Aurora MySQL 資料庫執行個體中的記憶體用量，並防止因記憶體壓力導致的潛在效能問題。