MySQL·最佳實踐·空間優化

db匠發表於2016-06-21
MySql優化

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

儲存空間是RDS很重要的一個指標，在RDS的工單問題中，空間問題的諮詢可以排在top 5，當RDS的實際使用空間超過了購買的空間後，例項就會被鎖定了，這樣就會導致應用無法再寫入，更新資料，造成應用的報錯。在RDS的控制檯中可以設定空間的報警閥值，當例項空間到達報警閥值後使用者就會收到報警簡訊， 這個時候使用者則需要對判斷當前的空間增長是否合理。

如果增長合理則需要對例項的進行彈性升級，這裡需要指出的是彈性升級分為兩種：

  • 本地升降級：該類升級是例項所在的主機磁碟空間充足，足以容納升級所需的空間，這個時候的升級非常迅速，而且對應用毫無影響；
  • 跨機升級：該類升級時例項所在的主機剩餘磁碟空間不足以容納升級所需的空間，那麼就需要將例項遷移到其他磁碟資源足夠的主機上，這時需要的時間可能會很長，取決於例項的總空間大小，因為遷移過程涉及將備份拷貝還原到新的主機上，同時還要考慮新例項與舊例項的資料同步問題，那麼這一些因素都會導致升級時間較長，最後升級結束時候資料庫中的已有連線會全部斷開（備註：高安全訪問鏈路沒有此問題）。

如果增長不合理，則需要進行快速的判斷問題出現在哪，

screenshot

也需要我們瞭解RDS的空間組成到底包括了哪些。在RDS控制檯中可以看到空間的組成分為了5部分，分別為：

  • 磁碟總空間
  • 資料空間
  • 日誌空間
  • 臨時檔案空間
  • 系統檔案空間

screenshot

接下來我們將一一介紹一下這些檔案組成：

資料檔案

顧名思義該檔案空間是指的存放資料的檔案，對應到資料庫中就是一張張的表，表的組成主要包括：資料和索引兩類，所以當你看到你的資料檔案佔用 例項的空間非常多的時候，你需要看一下到底是哪一張表佔用了我的空間，使用者可以通過資料庫的資料字典找到系統中佔用最大的表：

select TABLE_SCHEMA,TABLE_NAME,INDEX_LENGTH/1024/1024 as index_M,DATA_LENGTH/1024/1024 as data_M from  TABLES   order by (INDEX_LENGTH+DATA_LENGTH) desc limit 10;

凡事預則立，不預則廢，我們可以未雨綢繆，在設計應用的初期就考慮好系統的儲存：

  1. 未來資料的增長趨勢，決定磁碟的空間大小；

  2. 資料的生命保留週期，決定是否進行資料刪除或歸檔；

  3. 設計表選用合理的資料型別，欄位大小，儲存引擎，進行分割槽還是分表；

下圖的案例中，資料空間佔用了例項大量的空間，那麼可以通過上述方法定位資料庫中到底是那些張表佔用空間導致的問題：

screenshot

常見的空間問題：

  1. 對錶進行資料刪除後空間不會進行釋放 
    最佳實踐：需要對錶進行重建，重建的方法：Optimize table xxxxx，該方法在5.6以下會導致鎖表，RDS 5.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日誌增長迅猛，這時候需要使用者對資料庫進行優化，減小對資料庫的變更操作。

screenshot

  1. 曾經看到這樣的案例，應用頻繁的對錶進行更新，但是在該表上有較多的大欄位，由於在row格式下，binlog會記錄整行記錄， 這樣就導致了binlog增長非常迅猛，詳細可以參考Mysql大欄位的頻繁更新導致binlog暴增。所以在應用的設計初期，就要避免使用大欄位：varchar(8000),text,blob,clob等。

  2. 還有一種情況可能是主備的複製卡主或者中斷，則會導致主庫的binlog沒有傳遞到備庫，那麼這個時候binlog會一直在主庫堆積， 那麼就需要提工單要求儘快處理了。

臨時檔案：

臨時檔案通常可以理解為資料庫做一個大的操作，由於記憶體不足，資料庫需要將記憶體中的檔案寫到磁碟上，這樣則有可能導致臨時檔案寫的非常大， 通常出現這種情況的時候，資料庫在做大的排序操作（order by，group by，distinct）。下圖的案例中，由於資料庫中一條order by的語句頻繁的執行， 但是排序sql沒有索引，導致了臨時檔案的頻繁寫操作：

screenshot

  1. 當臨時空間上漲原因是SQL排序導致的時候，可以通過show processlist快速找出排序的SQL，然後kill 掉SQL；
  2. 同時對排序的sql新增合適的索引，避免排序，這是治根的辦法，避免資料庫中出現排序的SQL；
  3. 為了避免排序消耗的空間過大，可以設定臨時空間的大小，具體可以參考RDS引數優化loose_rds_max_tmp_disk_space

系統檔案：

系統檔案是每個資料庫在安裝的時候會初始化一些系統檔案，這些系統檔案是資料庫正常執行的前提，mysql：ibdata1，ib_logfile0，下面的這幅圖 反映了 “其他檔案”佔用達到了非常多的問題，可以參考：ibdata1檔案持續增加的問題定位

screenshot

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

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


相關文章