阿里云數(shù)據(jù)庫(kù)MySQL臨時(shí)文件導(dǎo)致實(shí)例空間滿(mǎn)的解決辦法
MySQL實(shí)例可能會(huì)由于查詢(xún)語(yǔ)句的排序、分組、關(guān)聯(lián)表產(chǎn)生的臨時(shí)表文件，或者大事務(wù)未提交前產(chǎn)生的binlog cache文件，導(dǎo)致實(shí)例空間滿(mǎn)，為避免數(shù)據(jù)丟失，RDS會(huì)對(duì)實(shí)例進(jìn)行自動(dòng)鎖定，磁盤(pán)鎖定之后，將無(wú)法進(jìn)行寫(xiě)入操作。
背景信息
當(dāng)實(shí)例由于實(shí)例空間滿(mǎn)自動(dòng)鎖定時(shí)，控制臺(tái)可以在 基本信息 > 運(yùn)行狀態(tài)看到如下信息：
前提條件
?對(duì)于MySQL 5.6版本的實(shí)例，升級(jí)實(shí)例存儲(chǔ)空間后即可解鎖實(shí)例，關(guān)于如何升級(jí)實(shí)例配置，請(qǐng)參見(jiàn)變更配置，若實(shí)例存儲(chǔ)空間已到最大值，請(qǐng)?zhí)峤还温?lián)系客服臨時(shí)解鎖實(shí)例，再進(jìn)行后續(xù)操作。
?對(duì)于MySQL 5.5/5.7版本的實(shí)例，請(qǐng)?zhí)峤还温?lián)系客服臨時(shí)解鎖實(shí)例，再進(jìn)行后續(xù)操作。
實(shí)施步驟
注意事項(xiàng)
清理臨時(shí)文件有延遲，請(qǐng)耐心等待實(shí)例已使用空間的下降。
操作步驟
1.通過(guò) DMS登錄數(shù)據(jù)庫(kù)。
2.選擇SQL操作 > SQL窗口，執(zhí)行如下命令查看數(shù)據(jù)庫(kù)的會(huì)話(huà)。
show processlist
3.單擊顯示結(jié)果中的State進(jìn)行狀態(tài)排序，在狀態(tài)欄查看是否有大量 Copy to tmp table 、Sending data等信息，再根據(jù)Info列的語(yǔ)句確定是哪個(gè)SQL語(yǔ)句在建立臨時(shí)表，記下該語(yǔ)句的Id。
4.可以通過(guò)命令行或者混合云數(shù)據(jù)庫(kù)管理平臺(tái)HDM（Hybrid Cloud Database Management）來(lái)終止會(huì)話(huà)。
?命令行：
a.在SQL窗口執(zhí)行如下命令終止會(huì)話(huà)。
kill <會(huì)話(huà)Id>
?混合云數(shù)據(jù)庫(kù)管理平臺(tái)：
a.選擇性能 > 空間，跳轉(zhuǎn)到混合云數(shù)據(jù)庫(kù)管理平臺(tái)（Hybrid Cloud Database Management，HDM）。
說(shuō)明 對(duì)于第一次進(jìn)入的用戶(hù)需要對(duì)HDM進(jìn)行授權(quán)，若已授權(quán)，請(qǐng)?zhí)降?步。
b.在授權(quán)HDM訪(fǎng)問(wèn)您的云資源信息頁(yè)面單擊確定，并在彈出的云資源訪(fǎng)問(wèn)授權(quán)頁(yè)面單擊同意授權(quán)。
c.在實(shí)例會(huì)話(huà)中找到之前記下的Id，單擊該行任意位置選中該行，在右上角單擊kill選中會(huì)話(huà)。
后續(xù)維護(hù)
?針對(duì)查詢(xún)產(chǎn)生的臨時(shí)文件，應(yīng)該優(yōu)化SQL語(yǔ)句，避免頻繁使用 order by、group by 操作，可以適當(dāng)調(diào)大tmp_table_size和max_heap_table_size，但是為了減少磁盤(pán)使用而調(diào)高 tmp_table_size 和 max_heap_table_size 并不明智，因?yàn)閮?nèi)存資源遠(yuǎn)比磁盤(pán)資源寶貴；可以通過(guò)explain+SQL語(yǔ)句查看是否使用內(nèi)部臨時(shí)表，在 Extra 字段中有 Using temporary 字樣的代表會(huì)使用內(nèi)部臨時(shí)表。示例如下：
explain select * from alarm group by created_on order by default;
?針對(duì)binlog cache，應(yīng)該少執(zhí)行大事務(wù)，尤其應(yīng)該減少在多個(gè)連接同時(shí)執(zhí)行大事務(wù)，如果大事務(wù)比較多，可以適當(dāng)調(diào)大binlog_cache_size，但是同樣不應(yīng)該為了節(jié)省磁盤(pán)調(diào)整這個(gè)參數(shù)，使用短連接執(zhí)行大事務(wù)可以有效降低臨時(shí)空間開(kāi)銷(xiāo)。