阿里云MySQL 實(shí)例空間使用率過高的原因和解決方法
用戶在使用 MySQL 實(shí)例時(shí)，會(huì)遇到空間使用告警甚至超過實(shí)例限額被鎖定的情況。在 RDS 控制臺(tái)的實(shí)例基本信息中，即會(huì)出現(xiàn)如下信息：
本文將介紹造成空間使用率過高的常見原因及其相應(yīng)的解決方法。對(duì)于MySQL 5.6版本的實(shí)例，升級(jí)實(shí)例規(guī)格和存儲(chǔ)空間后即可解鎖實(shí)例，關(guān)于如何升級(jí)實(shí)例配置，請(qǐng)參見變更配置。
?常見原因
造成 MySQL 實(shí)例空間使用率過高，主要有如下四種原因：
-
Binlog 文件占用高。
-
數(shù)據(jù)文件占用高。
-
臨時(shí)文件占用高。
-
系統(tǒng)文件占用高。
?查看空間使用狀況
您可以通過 DMS 中的診斷報(bào)告來查看實(shí)例空間的使用情況。
-
在 DMS 控制臺(tái)上登錄數(shù)據(jù)庫。
-
選擇性能 > 診斷報(bào)告。
-
單擊發(fā)起診斷，即可創(chuàng)建一個(gè)針對(duì)當(dāng)前實(shí)例運(yùn)行情況的報(bào)告，如下圖所示：
-
單擊查看報(bào)告，即可在診斷報(bào)告中查看到實(shí)例空間使用情況，如下圖所示：
圖示說明：
-
ins_size：實(shí)例整體空間。
-
other_size：系統(tǒng)文件和臨時(shí)文件使用空間。
-
data_size：數(shù)據(jù)文件使用空間。
-
binlog_size - Binlog：文件占用空間。
-
?解決方法
?升級(jí)實(shí)例規(guī)格
升級(jí)實(shí)例規(guī)格是解決空間問題的有效方式之一。目前，RDS已支持獨(dú)占物理機(jī)規(guī)格的實(shí)例，最大存儲(chǔ)空間可達(dá)到3T。建議您將實(shí)例規(guī)格升級(jí)至獨(dú)占物理機(jī)，關(guān)于獨(dú)占物理機(jī)實(shí)例的計(jì)費(fèi)詳情，請(qǐng)參見云數(shù)據(jù)庫RDS詳細(xì)價(jià)格信息，升級(jí)實(shí)例規(guī)格的操作步驟如下。
-
選擇目標(biāo)實(shí)例所在地域。
-
單擊目標(biāo)實(shí)例的ID，進(jìn)入基本信息頁面。
-
在配置信息欄中，單擊變更配置。
說明：若是包年包月實(shí)例，請(qǐng)?jiān)賳螕袅⒓?span style="font-weight:700;">升級(jí)配置或者續(xù)費(fèi)降配/續(xù)費(fèi)升配。
-
在變更實(shí)例頁面，將實(shí)例規(guī)格變更為獨(dú)占物理機(jī)，并選擇較大的存儲(chǔ)空間，如下圖所示。
-
單擊確認(rèn)變更。
?其它方法
本章節(jié)將介紹在不升級(jí)實(shí)例規(guī)格的情況下解決空間問題的方法。
?Binlog 文件占用高的解決方法
Binlog 文件記錄實(shí)例的事務(wù)信息，是 MySQL 實(shí)例 HA 架構(gòu)以及高可用性、可恢復(fù)性的基礎(chǔ)，是不可以關(guān)閉的。
RDS 實(shí)例會(huì)以一定時(shí)間間隔自動(dòng)清理（上傳到 OSS 并從實(shí)例空間中刪除）最近 18 小時(shí)外的 Binlog 文件。如果短時(shí)間內(nèi)實(shí)例 DML 操作生成了大量 Binlog 數(shù)據(jù)，有可能會(huì)導(dǎo)致超過實(shí)例磁盤空間上限而被鎖定。
在這種情況下，可以通過 RDS 控制臺(tái)來清理（將 Binlog 文件上傳到 OSS 并從實(shí)例空間中刪除）Binlog 數(shù)據(jù)。
?操作步驟
-
登錄 RDS 管理控制臺(tái)。
-
選擇目標(biāo)實(shí)例所在地域。
-
單擊目標(biāo)實(shí)例的 ID，進(jìn)入基本信息頁面。
-
選擇左側(cè)菜單欄中的備份恢復(fù)。
-
單擊一鍵上傳 Binlog。
說明：
-
一鍵上傳 Binlog 會(huì)在后臺(tái)異步提交清理任務(wù)，因此單擊后會(huì)很快返回。清理任務(wù)會(huì)先將完成寫入的 Binlog 上傳到 RDS 的 OSS （非用戶購買的 OSS）上，然后再從實(shí)例空間中刪除 Binlog 文件，當(dāng)前正在被寫入的 Binlog 文件由于未完成寫入，是不可以被清理的。因此，清理過程會(huì)有一定延遲，建議您單擊后耐心等待一定時(shí)間，請(qǐng)勿多次單擊該按鈕。
-
對(duì)于由于 DML 等操作（比如涉及大字段的 DML 操作）導(dǎo)致快速生成 Binlog 的情況，可能會(huì)出現(xiàn)多次單擊一鍵上傳 Binlog 按鈕但 Binlog 空間占有率依舊上漲的情況，這是因?yàn)樯蟼?Binlog 文件到備份空間并且從實(shí)例空間中刪除的處理速度跟不上實(shí)例生成 Binlog 文件的速度，在這種情況下，建議考慮升級(jí)磁盤空間，并且排查 Binlog 快速生成的原因。
-
?數(shù)據(jù)文件占用高的解決方法
對(duì)于數(shù)據(jù)文件占用空間高的情況，可以通過清理數(shù)據(jù)的方式來減少空間占用情況，比如通過 drop table 和 truncate table 命令來清理不再需要的數(shù)據(jù)。
另外，下面是兩種用戶常用于清除空間內(nèi)數(shù)據(jù)文件的方法，但實(shí)際并為達(dá)到預(yù)期效果，原因及解決方法如下所示：
-
用 delete 操作刪除數(shù)據(jù)
delete 操作不能夠直接回收被刪除數(shù)據(jù)占用的數(shù)據(jù)文件空間，這就好比排空泳池中的水但泳池的占地面積不會(huì)發(fā)生改變一樣。
在用 delete 操作刪除數(shù)據(jù)后，需要通過 optimize table tab_name; 操作來回收空間，詳情請(qǐng)參見 RDS for MySQL 刪除數(shù)據(jù)后顯示空間沒有減少。
-
刪除備份
RDS 備份是放置在后臺(tái) OSS 上，不占用用戶的 RDS 實(shí)例空間，因此刪除備份不能解決實(shí)例的空間問題。而且刪除備份會(huì)影響實(shí)例的可恢復(fù)性，強(qiáng)烈建議在任何情況下都不要考慮刪除備份。
?數(shù)據(jù)文件占用空間的查詢方法
方法一：
數(shù)據(jù)文件在頻繁的 DML 后會(huì)出現(xiàn)數(shù)據(jù)空洞的現(xiàn)象，通過如下查詢獲取的數(shù)據(jù)文件占用的空間比較接近實(shí)際數(shù)據(jù)文件占用空間的計(jì)算方式：
-
select sum(data_length + index_length + data_free) / 1024 / 1024 from information_schema.tables;
方法二：
information_schema.tables 提供的是根據(jù)采樣獲取的表的部分統(tǒng)計(jì)信息，因此通過如下查詢獲取的表、庫數(shù)據(jù)尺寸和實(shí)際數(shù)據(jù)文件占用尺寸間會(huì)有出入（通常要小于實(shí)際數(shù)據(jù)文件占用空間）。
說明：因?yàn)?information_schema.tables 中提供的是采樣統(tǒng)計(jì)數(shù)據(jù)，因此該計(jì)算方式在統(tǒng)計(jì)數(shù)據(jù)比較接近實(shí)際的情況下，才會(huì)比較接近真實(shí)空間占用情況。
-
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’;
查詢結(jié)果如下圖所示：
從上圖可以看出，在收集表的統(tǒng)計(jì)信息前后反饋出的表數(shù)據(jù)量大小存在差異。即使通過 analyze table 命令重新收集統(tǒng)計(jì)信息，得到的數(shù)值通常也小于實(shí)際數(shù)據(jù)文件占用空間，例如本例的 16143 MB 也小于該表的數(shù)據(jù)文件實(shí)際占用的空間。
?臨時(shí)文件占用高的解決方法
臨時(shí)文件會(huì)隨查詢的結(jié)束或者會(huì)話的終止而自動(dòng)釋放，因此如果是臨時(shí)文件導(dǎo)致實(shí)例空間滿而鎖定，可以通過終止會(huì)話來釋放空間。
關(guān)于如何終止會(huì)話，請(qǐng)參見 RDS for MySQL 如何終止會(huì)話。
關(guān)于臨時(shí)文件的常見問題，請(qǐng)參見 RDS for MySQL the table ‘/home/mysql/xxxx/xxxx/#tab_name’ is full 的原因和處理。
?系統(tǒng)文件占用高的解決方法
系統(tǒng)文件涉及到 ibdata1 系統(tǒng)表的空間文件和 ib_logfile0、ib_logfile1 日志文件。
?ibdata1 文件
InnoDB 引擎表由于支持多版本并發(fā)控制（MVCC），因此會(huì)將查詢所需的 Undo 信息保存在系統(tǒng)文件 ibdata1 中。
如果存在對(duì)一個(gè) InnoDB 表長時(shí)間不結(jié)束的查詢，而且在查詢過程中表有大量的數(shù)據(jù)變化，則會(huì)生成大量的 Undo 信息，導(dǎo)致 ibdata1 文件尺寸增加。由于 MySQL 內(nèi)部機(jī)制的限制，ibdata1 文件目前是不支持收縮的。
因此，若出現(xiàn)這種情況，在不升級(jí)磁盤空間的前提下，比較好的解決方法是在同地域同可用區(qū)購買相同配置的 RDS 實(shí)例，通過 DTS 工具將數(shù)據(jù)遷移到新實(shí)例中。
建議您監(jiān)控和清理執(zhí)行時(shí)間過長的會(huì)話或事務(wù)，詳情請(qǐng)參見 RDS MySQL 管理長時(shí)間運(yùn)行查詢。
?ib_logfile 日志文件
ib_logfile0 和 ib_logfile1 日志文件保存 InnoDB 引擎表的事務(wù)日志信息，其文件大小尺寸固定，不可以改變。較大的尺寸在高并發(fā)事務(wù)的場景下有利于減少事務(wù)日志文件切換的次數(shù)，提高實(shí)例性能。