Share via

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

  • 發行項

適用於：適用於 MySQL 的 Azure 資料庫 - 單一伺服器適用於 MySQL 的 Azure 資料庫 - 彈性伺服器

重要

適用於 MySQL 的 Azure 資料庫單一伺服器位於淘汰路徑上。 強烈建議您升級至適用於 MySQL 的 Azure 資料庫彈性伺服器。 如需移轉至適用於 MySQL 的 Azure 資料庫 - 彈性伺服器的詳細資訊，請參閱適用於 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 資料庫彈性伺服器提供多種計量，以量測資料庫執行個體的效能。 若要進一步了解資料庫伺服器的記憶體使用率，請檢視 [主機記憶體百分比] 或 [記憶體百分比] 計量。

Viewing memory utilization metrics.

如果您注意到記憶體使用率突然增加，而且可用的記憶體快速下降，則請監視其他計量 (例如 [主機 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

請思考一下下列範例。

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

mysql> 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。