RDS MySQL空間優化最佳實踐

摘要： 在前三期介紹了RDSfor MySQL參數優化，鎖問題以及延遲優化最佳實踐之後，本期將介紹儲存空間相關的最佳實踐。

在前三期介紹了RDS for MySQL參數優化，鎖問題以及延遲優化最佳實踐之後，本期將介紹儲存空間相關的最佳實踐。

儲存空間是RDS很重要的一個指標，在RDS的工單問題中，空間問題的查閱可以排在top 5，當RDS的實際使用空間超過了購買的空間後，實例就會被鎖定了，這樣就會導致套用無法再寫入，更新資料，造成套用的報錯。在RDS的主控台中可以設定空間的報警閥值，當實例空間到達報警閥值後用戶就會收到報警簡訊，這個時候使用者則需要對判斷本期的空間增長是否合理。如果增長合理則需要對實例的進行彈性升級，這裡需要指出的是彈性升級分為兩種，一種是本地升降級，該類升級是實例所在的主機磁碟空間充足，足以容納升級所需的空間，這個時候的升級非常迅速，而且對套用毫無影響；第二種是跨機升級，該類升級時實例所在的主機剩餘磁碟空間不足以容納升級所需的空間，那麼就需要將實例遷移到其他磁碟資源足夠的主機上，這時需要的時間可能會很長，取決於實例的總空間大小，因為遷移程序涉及將備份拷貝還原到新的主機上，同時還要考慮新實例與舊實例的資料同步問題，那麼這一些因素都會導致升級時間較長，最後升級結束時候資料庫中的已有串連會全部斷開（備忘：高安全存取鏈路沒有此問題）。如果增長不合理，則需要進行快速的判斷問題出現在那，

則需要我們瞭解RDS的空間組成到底包括了哪些。在RDS主控台中可以看到空間的組成分為了5部份，分別為：磁碟總空間，資料空間，日誌空間，暫存檔案空間，系統檔案空間。

接下來我們將一一介紹一下這些檔案群組成。
一.資料檔案：
顧名思義該檔案空間則是指的存放用資料的檔案，對應到資料庫中就是一張張的表，表的組成主要包括：資料和索引兩類，所以當你看到你的資料檔案佔用實例的空間非常多的時候，你需要看一下到底是哪一張表佔用了我的空間，用戶可以通過資料庫的資料字典找到系統中佔用最大的表：
select TABLE_SCHEMA,TABLE_NAME,INDEX_LENGTH/1024/1024 asindex_M,DATA_LENGTH/1024/1024 as data_M from TABLES order by(INDEX_LENGTH+DATA_LENGTH) desc limit 10
凡事預則立，不預則廢，我們可以未雨綢繆，在設計套用的初期就考慮好系統的隱藏：
1. 未來資料的增長趨勢，決定磁碟的空間大小；
2. 資料的生命保留週期，決定是否進行資料移除或歸檔；
3. 設計表選用合理的資料類型，欄位大小，儲存引擎，進行分區還是分表；
下圖的案例中，資料空間佔用了實例大量的空間，那麼可以通過上述方法尋找資料庫中到底是那些張表佔用空間導致的問題：

常見的空間問題：
1. 對表進行資料移除後空間不會進行釋放
最佳實踐：需要對表進行重建，重建的方法：Optimizetable xxxxx，該方法在5.6以下會導致鎖表，RDS5.6支援線上重建。
2. 大表索引佔用的空間比資料空間還大
最佳實踐：需要將表中無用或者重複的索引移除，移除索引需要特別注意該索引是否還在使用。
3. 大表主要用作日誌型商務資料存放區，基本都是插入，很少查詢
最佳實踐：可以使用tokudb引擎將表中的資料進行壓縮，通常壓縮效率在3倍以上，注意使用tokudb引擎需要調整tokudb的buffer，可參考參數優化loose_tokudb_buffer_pool_ratio。
二.記錄檔：
RDS MySQL採用主從M-M的高可用架構，其主備之間的資料同步依靠binlog日誌。為了減少binlog日誌對用戶的空間的佔用，RDS會定時把記錄備份到oss中，然後將本地的binlog清除。當日誌空間出現異常的時候，如下圖，由於套用寫入資料壓力過大，導致binlog日誌增加的速度大於了RDS上傳到oss的速度，造成了binlog日誌增長迅猛，這時候需要使用者對資料庫進行優化，減小對資料庫的變更動作。

1. 曾經看到這樣的案例，套用頻繁的對表進行更新，但是在該表上有較多的大欄位，由於在row格式下，binlog會記錄整行記錄，這樣就導致了binlog增長非常迅猛，詳細可以參考Mysql大欄位的頻繁更新導致binlog暴增。所以在套用的設計初期，就要避免使用大欄位：varchar(8000),text,blob,clob等。
2. 還有一種情況可能是主備的複製卡主或者中斷，則會導致主庫的binlog沒有傳遞到備庫，那麼這個時候binlog會一直在主庫堆積，那麼就需要提工單要求儘快處理了。
三.暫存檔案：
暫存檔案通常可以理解為資料庫做一個大的動作，由於記憶體不足，資料庫需要將記憶體中的檔案寫到磁碟上，這樣則有可能導致暫存檔案寫的非常大，通常出現這種情況的時候，資料庫在做大的排序動作（orderby，groupby，distinct）。下圖的案例中，由於資料庫中一條orderby的語句頻繁的執行，但是排序sql沒有索引，導致了暫存檔案的頻繁寫動作：

1. 當臨時空間上漲原因是SQL排序導致的時候，可以通過showprocesslist快速找出排序的SQL，然後kill掉SQL；
2. 同時對排序的sql新增合適的索引，避免排序，這是治根的辦法，避免資料庫中出現排序的SQL；
3. 為了避免排序消耗的空間過大，可以設定臨時空間的大小，具體可以參考RDS參數優化loose_rds_max_tmp_disk_space；
四.系統檔案：
系統檔案是每個資料庫在安裝的時候會初始化一些系統檔案，這些系統檔案是資料庫正常啟動並執行前提，mysql：ibdata1，ib_logfile0，下面的這幅圖反映了“其他檔案”佔用達到了非常多的問題，可以參考：ibdata1檔案持續增加的問題尋找

1. ibdata1檔案中大量的都是undo_log，建議將組建升級到5.6以上有硬地的purge執行緒可以很快的回收掉undolog，可以單獨設定undotablespace檔案，避免與ibdata1混用在一起；
2. 同時也可以採用邏輯遷移的方式，重建ibdata1檔案；
3. 資料庫中要注意未提交的交易對undo的影響，監控資料庫中的INNODB_TRX檢視。

綜上所述，空間問題也是一個比較複雜的問題，但是如果我們能夠在系統設計之初遵循一些最佳實踐，那麼我們還是能夠很好的處理掉這些問題，祝你玩得開心，用得放心。

相關產品：

1. 雲資料庫RDS
2. 安全管家
3. 資料管理
4. 雲端服務器ECS