MySQL 執行個體空間使用率過高的原因和解決方案

標籤：dml   tail   OLE   UI   版本   改變   src   nlog   class   

使用者在使用 MySQL 執行個體時，會遇到空間使用警示甚至超過執行個體限額被鎖定的情況。在 RDS 控制台的執行個體基本資料中，即會出現如下資訊：

本文將介紹造成空間使用率過高的常見原因及其相應的解決方案。對於MySQL 5.6版本的執行個體，升級執行個體規格和儲存空間後即可解鎖執行個體，關於如何升級執行個體配置，請參見變更配置。

常見原因

造成 MySQL 執行個體空間使用率過高，主要有如下四種原因：

• Binlog 檔案佔用高。

• 資料檔案佔用高。

• 臨時檔案佔用高。

• 系統檔案佔用高。

查看空間使用狀況

您可以通過 DMS 中的診斷報告來查看執行個體空間的使用方式。

1. 在 DMS 控制台上登入資料庫。

2. 選擇效能 > 診斷報告。

3. 單擊發起診斷，即可建立一個針對當前執行個體運行情況的報告，如所示：

4. 單擊查看報告，即可在診斷報告中查看到執行個體空間使用方式，如所示：

   圖示說明：

   • ins_size：執行個體整體空間。

   • other_size：系統檔案和臨時檔案使用空間。

   • data_size：資料檔案使用空間。

   • binlog_size - Binlog：檔案佔用空間。

解決方案升級執行個體規格

升級執行個體規格是解決空間問題的有效方式之一。目前，RDS已支援獨佔物理機規格的執行個體，最大儲存空間可達到3T。建議您將執行個體規格升級至獨佔物理機，關於獨佔物理機執行個體的計費詳情，請參見雲資料庫RDS詳細價格資訊，升級執行個體規格的操作步驟如下。

1. 登入RDS管理主控台。

2. 選擇目標執行個體所在地區。

3. 單擊目標執行個體的ID，進入基本資料頁面。

4. 在配置資訊列中，單擊變更配置。

   說明：若是訂用帳戶執行個體，請再單擊立即升級配置或者續約降配/續約升配。

5. 在變更執行個體頁面，將執行個體規格變更為獨佔物理機，並選擇較大的儲存空間，如所示。

6. 單擊確認變更。

其它方法

本章節將介紹在不升級執行個體規格的情況下解決空間問題的方法。

Binlog 檔案佔用高的解決方案

Binlog 檔案記錄執行個體的事務資訊，是 MySQL 執行個體 HA 架構以及高可用性、可恢複性的基礎，是不可以關閉的。

RDS 執行個體會以一定時間間隔自動清理（上傳到 OSS 並從執行個體空間中刪除）最近 18 小時外的 Binlog 檔案。如果短時間內執行個體 DML 操作產生了大量 Binlog 資料，有可能會導致超過執行個體磁碟空間上限而被鎖定。

在這種情況下，可以通過 RDS 控制台來清理（將 Binlog 檔案上傳到 OSS 並從執行個體空間中刪除）Binlog 資料。

操作步驟

1. 登入 RDS 管理主控台。

2. 選擇目標執行個體所在地區。

3. 單擊目標執行個體的 ID，進入基本資料頁面。

4. 選擇左側功能表列中的備份恢複。

5. 單擊一鍵上傳 Binlog。

   說明：

   • 一鍵上傳 Binlog 會在後台非同步提交清理任務，因此單擊後會很快返回。清理任務會先將完成寫入的 Binlog 上傳到 RDS 的 OSS （非使用者購買的 OSS）上，然後再從執行個體空間中刪除 Binlog 檔案，當前正在被寫入的 Binlog 檔案由於未完成寫入，是不可以被清理的。因此，清理過程會有一定延遲，建議您單擊後耐心等待一定時間，請勿多次單擊該按鈕。

   • 對於由於 DML 等操作（比如涉及大欄位的 DML 操作）導致快速產生 Binlog 的情況，可能會出現多次單擊一鍵上傳 Binlog 按鈕但 Binlog 空間佔有率依舊上漲的情況，這是因為上傳 Binlog 檔案到備份空間並且從執行個體空間中刪除的處理速度跟不上執行個體產生 Binlog 檔案的速度，在這種情況下，建議考慮升級磁碟空間，並且排查 Binlog 快速產生的原因。

資料檔案佔用高的解決方案

對於資料檔案佔用空間高的情況，可以通過清理資料的方式來減少空間佔用情況，比如通過 drop table 和 truncate table 命令來清理不再需要的資料。

另外，下面是兩種使用者常用於清除空間內資料檔案的方法，但實際並為達到預期效果，原因及解決方案如下所示：

• 用 delete 操作刪除資料

  delete 操作不能夠直接回收被刪除資料佔用的資料檔案空間，這就好比排空泳池中的水但泳池的佔地面積不會發生改變一樣。

  在用 delete 操作刪除資料後，需要通過 optimize table tab_name; 操作來回收空間，詳情請參見 RDS for MySQL 刪除資料後顯示空間沒有減少。

• 刪除備份

  RDS 備份是放置在後台 OSS 上，不佔用使用者的 RDS 執行個體空間，因此刪除備份不能解決執行個體的空間問題。而且刪除備份會影響執行個體的可恢複性，強烈建議在任何情況下都不要考慮刪除備份。

資料檔案佔用空間的查詢方法

方法一：

資料檔案在頻繁的 DML 後會出現資料空洞的現象，通過如下查詢擷取的資料檔案佔用的空間比較接近實際資料檔案佔用空間的計算方式：

1. select sum(data_length + index_length + data_free) / 1024 / 1024 from information_schema.tables;

方法二：

information_schema.tables 提供的是根據採樣擷取的表的部分統計資訊，因此通過如下查詢擷取的表、庫資料尺寸和實際資料檔案佔用尺寸間會有出入（通常要小於實際資料檔案佔用空間）。

說明：因為 information_schema.tables 中提供的是採樣統計資料，因此該計算方式在統計資料比較接近實際的情況下，才會比較接近真實空間佔用情況。

1. select table_name, concat(round((data_length + index_length) / 1024 / 1024,2),’MB’)from information_schema.tableswhere table_schema = ‘rd_test’and table_name = ‘large_tab_01’;

查詢結果如所示：

從可以看出，在收集表的統計資訊前後反饋出的表資料量大小存在差異。即使通過 analyze table 命令重新收集統計資訊，得到的數值通常也小於實際資料檔案佔用空間，例如本例的 16143 MB 也小於該表的資料檔案實際佔用的空間。

臨時檔案佔用高的解決方案

臨時檔案會隨查詢的結束或者會話的終止而自動釋放，因此如果是臨時檔案導致執行個體空間滿而鎖定，可以通過終止會話來釋放空間。

關於如何終止會話，請參見 RDS for MySQL 如何終止會話。

關於臨時檔案的常見問題，請參見 RDS for MySQL the table ‘/home/mysql/xxxx/xxxx/#tab_name’ is full 的原因和處理。

https://help.aliyun.com/knowledge_detail/51682.html

系統檔案佔用高的解決方案

系統檔案涉及到 ibdata1 系統資料表的空間檔案和 ib_logfile0、ib_logfile1 記錄檔。

ibdata1 檔案

InnoDB 引擎表由於支援多版本並發控制（MVCC），因此會將查詢所需的 Undo 資訊儲存在系統檔案 ibdata1 中。

如果存在對一個 InnoDB 表長時間不結束的查詢，而且在查詢過程中表有大量的資料變化，則會產生大量的 Undo 資訊，導致 ibdata1 檔案尺寸增加。由於 MySQL 內部機制的限制，ibdata1 檔案目前是不支援收縮的。

因此，若出現這種情況，在不升級磁碟空間的前提下，比較好的解決方案是在同地區同可用性區域購買相同配置的 RDS 執行個體，通過 DTS 工具將資料移轉到新執行個體中。

建議您監控和清理執行時間過長的會話或事務，詳情請參見 RDS MySQL 管理長時間執行查詢。

ib_logfile 記錄檔

ib_logfile0 和 ib_logfile1 記錄檔儲存 InnoDB 引擎表的交易記錄資訊，其檔案大小尺寸固定，不可以改變。較大的尺寸在高並發事務的情境下有利於減少交易記錄檔切換的次數，提高執行個體效能。

MySQL 執行個體空間使用率過高的原因和解決方案