共用方式為

針對適用於 MySQL 的 Azure 資料庫 - 彈性伺服器中的記憶體不足問題進行疑難排解

  • 發行項

為了協助確保 適用於 MySQL 的 Azure 資料庫 彈性伺服器實例以最佳方式執行，擁有適當的記憶體配置和使用率非常重要。 根據預設，當您建立 適用於 MySQL 的 Azure 資料庫 彈性伺服器的實例時，可用的物理記憶體會相依於您為工作負載選取的層級和大小。 此外，系統會為緩衝區和快取配置記憶體，以改善資料庫作業。 如需詳細資訊，請參閱 MySQL 如何使用記憶體。

適用於 MySQL 的 Azure 資料庫 彈性伺服器會耗用記憶體，以達到盡可能多的快取命中。 因此，記憶體使用率通常可以暫留在執行個體可用實體記憶體的 80 - 90% 之間。 除非查詢工作負載的進度發生問題，否則其並不重要。 不過，您可能會因為下列原因而遇到記憶體不足的問題：

  • 已設定太大的緩衝區。
  • 執行中次佳查詢。
  • 執行聯結和排序大型資料集的查詢。
  • 設定資料庫伺服器上的連線數上限太高。

InnoDB 的全域緩衝區和快取會使用大部分的伺服器記憶體，其中包括innodb_buffer_pool_size、innodb_log_buffer_size、key_buffer_size和query_cache_size等元件。

innodb_buffer_pool_size 參數的值會指定 InnoDB 快取資料庫資料表和索引相關資料的記憶體區域。 MySQL 會嘗試盡可能在緩衝區集區中容納更多的資料表和索引相關資料。 較大的緩衝區集區需要將較少的 I/O 作業轉移至磁碟。

監視記憶體使用狀況

適用於 MySQL 的 Azure 資料庫 彈性伺服器提供一系列計量來測量資料庫實例的效能。 若要進一步了解資料庫伺服器的記憶體使用率，請檢視 [主機記憶體百分比] 或 [記憶體百分比] 計量。

檢視記憶體使用量計量的螢幕快照。

如果您注意到記憶體使用率突然增加，而且可用的記憶體快速下降，則請監視其他計量 (例如 [主機 CPU 百分比]、[連線總計] 和 [IO 百分比]) 以判斷工作負載突然激增是否為問題來源。

請務必注意，與資料庫伺服器建立的每個連線都需要配置一些記憶體。 因此，資料庫連線激增可能會導致記憶體不足。

高記憶體使用率的原因

讓我們看看 MySQL 中記憶體使用率較高的一些原因。 這些原因取決於工作負載的特性。

暫存資料表增加

MySQL 使用「臨時表」，這是一種特殊類型的數據表，其設計用來儲存暫存結果集。 暫存資料表可以在工作階段期間重複使用數次。 因為任何建立的暫存資料表都是工作階段的本機資料表，所以不同的工作階段可以有不同的暫存資料表。 在有多個執行大型暫存結果集編譯的工作階段的生產系統中，您應該定期檢查全域狀態計數器 created_tmp_tables，以追蹤在尖峰時段期間建立的暫存資料表數目。 大量記憶體內部臨時表可快速在 適用於 MySQL 的 Azure 資料庫 彈性伺服器的實例中，導致可用的記憶體不足。

使用 MySQL 時，暫存資料表大小取決於兩個參數的值，如下表所述。

參數 說明
tmp_table_size 指定內部記憶體內部暫存資料表的大小上限。
max_heap_table_size 指定使用者所建立 MEMORY 資料表可成長的大小上限。

注意

判斷內部記憶體內部暫存資料表的大小上限時，MySQL 會考慮針對 tmp_table_size 和 max_heap_table_size 參數所設定值中的較小者。

建議

若要針對與暫存資料表相關的記憶體不足問題進行疑難排解，請考慮下列建議。

  • 在增加 tmp_table_size 值之前，請確認已針對您的資料庫正確編制索引，特別是針對聯結中所涉及和依作業分組的資料行。 在基礎資料表上使用適當的索引可限制建立的暫存資料表數目。 增加此參數和 max_heap_table_size 參數的值，而不需要驗證索引，即可允許沒有效率的查詢在沒有索引的情況下執行，並建立比所需數目還要多的暫存資料表。
  • 微調 max_heap_table_size 和 tmp_table_size 參數的值，以解決工作負載的需求。
  • 如果您為max_heap_table_size和tmp_table_size參數設定的值太低，臨時表可能會定期溢出至記憶體，以增加查詢的延遲。 您可以使用全域狀態計數器 created_tmp_disk_tables 來追蹤溢出到磁碟的暫存資料表。 比較 created_tmp_disk_tables 和 created_tmp_tables 變數的值，即可檢視已建立的內部磁碟上暫存資料表數目與已建立的內部暫存資料表總數。

資料表快取

作為多執行緒系統，MySQL 會維護資料表檔案描述元的快取，讓多個工作階段可以同時分別開啟資料表。 MySQL 會使用一些記憶體和 OS 檔案描述元來維護此資料表快取。 table_open_cache 變數可定義資料表快取的大小。

建議

若要針對與資料表快取相關的記憶體不足問題進行疑難排解，請考慮下列建議。

  • table_open_cache 參數指定所有執行緒的已開啟資料表數目。 增加此值會增加 mysqld 所需的檔案描述元數目。 您可以檢查顯示全域狀態計數器中的 opened_tables 狀態變數，以檢查是否需要增加資料表快取。 遞增增加此參數的值，以容納您的工作負載。
  • 設定table_open_cache太低可能會導致 適用於 MySQL 的 Azure 資料庫 彈性伺服器花費更多時間開啟和關閉查詢處理所需的數據表。
  • 設定此值太高可能會導致使用更多記憶體，以及執行檔案描述元的操作系統導致拒絕連線或無法處理查詢。

其他緩衝區和查詢快取

針對記憶體不足相關問題進行疑難排解時，您可以使用更多緩衝區和快取來協助處理解決方式。

Net 緩衝區 (net_buffer_length)

net 緩衝區是每個用戶端執行緒的連線和執行緒緩衝區大小，而且可以成長為針對 max_allowed_packet 所指定的值。 如果查詢陳述式很大 (例如，所有插入/更新都有非常大的值)，則增加 net_buffer_length 參數的值有助於改善效能。

聯結緩衝區 (join_buffer_size)

聯結緩衝區會在聯結無法使用索引時，配置給快取數據表數據列。 如果您的資料庫已執行許多沒有索引的聯結，則請考慮新增索引以加快聯結速度。 如果您無法新增索引，請考慮增加 join_buffer_size 參數的值，以指定每個連接配置的記憶體數量。

排序緩衝區 (sort_buffer_size)

排序緩衝區用於執行某些 ORDER BY 和 GROUP BY 查詢的排序。 如果您在 SHOW GLOBAL STATUS 輸出中看到每秒許多Sort_merge_passes，請考慮增加sort_buffer_size值，以加速 ORDER BY 或 GROUP BY 作業，這些作業無法使用查詢優化或更好的編製索引來改善。

除非您有指出其他值的相關資訊，否則請避免任意增加 sort_buffer_size 值。 每個連線都會獲指派此緩衝區的記憶體。 在 MySQL 文件中，〈伺服器系統變數〉一文會在 Linux 上呼叫該項目，有兩個閾值 (256 KB 和 2 MB)，而使用較大的值可能會大幅降低記憶體配置。 因此，請避免增加超過 2M 的 sort_buffer_size 值，因為效能損失將會超過任何優點。

查詢快取 (query_cache_size)

查詢快取是用於快取查詢結果集的記憶體區域。 query_cache_size 參數決定配置以用於快取查詢結果的記憶體數量。 預設會停用查詢快取。 此外，查詢快取已在 MySQL 5.7.20 版中予以取代，並在 MySQL 8.0 版中予以移除。 如果解決方案中目前已啟用查詢快取，請先確認沒有任何依賴查詢的查詢。

計算緩衝區快取命中率

緩衝區快取命中率在 適用於 MySQL 的 Azure 資料庫 彈性伺服器環境中很重要，以瞭解緩衝池是否可以容納工作負載要求，而且作為一般經驗法則，最好一律擁有超過 99% 的緩衝池快取命中率。

若要計算讀取要求的 InnoDB 緩衝池命中率，您可以執行 SHOW GLOBAL STATUS 來擷取計數器 “Innodb_buffer_pool_read_requests” 和 “Innodb_buffer_pool_reads”，然後使用如下所示的公式來計算值。

InnoDB Buffer pool hit ratio = Innodb_buffer_pool_read_requests / (Innodb_buffer_pool_read_requests + Innodb_buffer_pool_reads) * 100

請思考一下下列範例。

show global status like "innodb_buffer_pool_reads";
+--------------------------+-------+
| Variable_name | Value |
| +--------------------------+-------+ |
| Innodb_buffer_pool_reads | 197 |
| +--------------------------+-------+ |
| 1 row in set (0.00 sec) |

show global status like "innodb_buffer_pool_read_requests";
+----------------------------------+----------+
| Variable_name | Value |
| +----------------------------------+----------+ |
| Innodb_buffer_pool_read_requests | 22479167 |
| +----------------------------------+----------+ |
| 1 row in set (0.00 sec) |

使用上述值，計算讀取要求的 InnoDB 緩衝集區命中率會產生下列結果：

InnoDB Buffer pool hit ratio = 22479167/(22479167+197) * 100
Buffer hit ratio = 99.99%

除了選取陳述式緩衝區快取命中率之外，針對任何 DML 陳述式，還會在背景中寫入至 InnoDB 緩衝區集區。 不過，如果需要讀取或建立頁面，而且沒有可用的全新頁面，則也需要先等候排清頁面。

Innodb_buffer_pool_wait_free 計數器會計算發生此狀況的次數。 Innodb_buffer_pool_wait_free 大於 0 的強指標是 InnoDB 緩衝區集區太小，而且需要增加緩衝區集區大小或執行個體大小，才能容納傳入資料庫的寫入。

建議

  • 請確定您的資料庫已配置足夠的資源來執行查詢。 有時候，您可能需要相應增加實例大小，以取得更多的物理記憶體，讓緩衝區和快取容納您的工作負載。
  • 將交易分成較小的交易，以避免出現大型或長時間執行的交易。
  • 使用警示「主機記憶體百分比」，以便在系統超過任何指定的臨界值時收到通知。
  • 使用查詢效能深入解析或 Azure Workbooks 來識別任何有問題或執行速度緩慢的查詢，然後將其最佳化。
  • 針對生產資料庫伺服器，定期收集診斷，以確保所有事項都能順利執行。 如果沒有，則請針對您識別的任何問題進行疑難排解，並加以解決。

後續步驟

Stack Overflow