云數(shù)據(jù)庫Mysql磁盤使用率高排查優(yōu)化建議

【場景】

MySQL實例可能長時間運行，未進行磁盤及數(shù)據(jù)管理，導致磁盤使用率升高。尤其當實例顯示“磁盤空間滿”狀態(tài)，且數(shù)據(jù)庫不可進行寫入操作，會導致實例異常、數(shù)據(jù)庫備份失敗潛在風險、數(shù)據(jù)庫實例只讀狀態(tài)。從而影響業(yè)務正常運行。

本文檔通過分析及解決以下引起磁盤使用率高的場景，提前預防磁盤使用率高導致實例異常，只讀狀態(tài)的問題。

1)????????磁盤利用率月/季度平均增長過快。

2)????????delete刪除數(shù)據(jù)后磁盤空間使用率無變化

3)????????冷數(shù)據(jù)遷移

4)????????大事務快速生成Binlog文件

5)????????查詢產(chǎn)生臨時文件過大

6)???大事務未提交前產(chǎn)生binlog cache文件

【場景1】每月/每季度磁盤利用率平均增長過快

例如MySql數(shù)據(jù)庫總?cè)萘?60G，根據(jù)近期3個月的磁盤監(jiān)控數(shù)據(jù)分析每月的數(shù)據(jù)增長率占磁盤容量約10%（56G），當前磁盤使用率81.06%，按每月10%的增長率，當前容量2月后磁盤使用率到100%，存在磁盤滿后數(shù)據(jù)庫備份失敗潛在風險，數(shù)據(jù)庫狀態(tài)只讀風險。

【分析】：查詢數(shù)據(jù)庫磁盤監(jiān)控指標

【解決方法】：建議擴容磁盤容量，擴容大小=（當前磁盤總?cè)萘浚?60G*10%（月數(shù)據(jù)增量率）*X（月），根據(jù)實際情況制定擴容計劃（按月擴容、按季度擴容）

操作步驟:https://support.huaweicloud.com/usermanual-rds/zh-cn_topic_scale_cluster.html

【場景2】delete刪除數(shù)據(jù)后磁盤空間使用率無變化

【分析】：DELETE只是將數(shù)據(jù)標識位刪除，并沒有整理或者清理數(shù)據(jù)文件，當插入新數(shù)據(jù)后，會再次使用這些被置為刪除標識的記錄空間

【解決方法】：innodb引擎的表通過以下方法回收空間：

步驟一：show variables like 'innodb_file_per_table'; --返回值ON時使用步驟二、返回值OFF時使用步驟三

步驟二：

ALTER TABLE '表名'?ENGINE=InnoDB;

ANALYZE TABLE '表名';

步驟三、OPTIMIZE TABLE '表名';或者如圖所示。

Mysql官網(wǎng)參考https://dev.mysql.com/doc/refman/5.7/en/optimize-table.html

步驟四：通過以下SQL命令返回結(jié)果，分析查看數(shù)據(jù)庫的文件大小，分析其中可以刪除的歷史數(shù)據(jù)文件或無用數(shù)據(jù)文件。

SELECT file_name, concat(TOTAL_EXTENTS,'M') as 'FIle_size' FROM INFORMATION_SCHEMA.FILES? order by TOTAL_EXTENTS

注：如果您使用的delete from tablename語句清空當前表的所有記錄時，建議使用truncate table tablename語句可以清空表空間、索引文件。大批量刪除事務會使表長時間鎖定，建議在維護時間清理空間。

【場景3】冷數(shù)據(jù)遷移

冷數(shù)據(jù)是對于離線類不經(jīng)常訪問的數(shù)據(jù)，比如企業(yè)備份數(shù)據(jù)、業(yè)務與操作日志數(shù)據(jù)、話單與統(tǒng)計數(shù)據(jù)怎么遷移。

【解決方法】使用DAS工具導出功能或者mysqldump工具，將數(shù)據(jù)備份到其他服務器上。參考如下鏈接。

方法一：https://support.huaweicloud.com/usermanual-das/das_04_0041.html

方法二：https://support.huaweicloud.com/eu-west-0-usermanual-rds/zh-cn_topic_0042423727.html

【場景4】大事務快速生成Binlog文件，導致磁盤占滿

【分析】：查詢磁盤監(jiān)控指標，在1個小時內(nèi)的磁盤使用率明顯異常時，查詢近期binglog日志文件個數(shù)及大小。

【解決方法】修改binlog日志文件保存周期。

設置MySQL本地Binlog:https://support.huaweicloud.com/usermanual-rds/rds_05_0037.html

【場景5】查詢產(chǎn)生臨時文件過大

【分析】查詢數(shù)據(jù)庫慢日志，根據(jù)慢日志分析數(shù)據(jù)量大，行數(shù)多，響應時間長的sql語句分析這類sql查詢，易產(chǎn)生臨時文件。通過Explain解析sql的額外信息，當返回Extra字段當出現(xiàn)using index時，說明SQL使用覆蓋索引，性能較好；而當出現(xiàn)?using filesort、using temporary、using where時，說明查詢需要優(yōu)化。

【解決方法】應該優(yōu)化SQL語句。避免頻繁使用?order by、group by?操作，可以適當調(diào)大tmp_table_size和max_heap_table_size，但是為了減少磁盤使用而調(diào)高?tmp_table_size?和?max_heap_table_size，導致內(nèi)存使用率升高不劃算，因為內(nèi)存資源遠比磁盤資源寶貴；可以通過explain+SQL語句/或者使用DAS?“SQL診斷”工具查看是否使用內(nèi)部臨時表，在?Extra?字段中有?Using temporary?字樣的代表會使用內(nèi)部臨時表?例如：

explain?select?*?from test_table group by created_on order by default;

【場景6】大事務未提交前產(chǎn)生binlog cache文件，導致磁盤滿，主實例異常

【分析】????? ?未提交事務，指開啟事務后，長時間未向MySQL發(fā)出SQL執(zhí)行請求或事務處理(COMMIT/ROLLBACK)請求，在系統(tǒng)表`information_schema`.`INNODB_TRX`?中狀態(tài)為RUNNING，而在`information_schema`.`PROCESSLIST`中狀態(tài)為SLEEP。

這類問題一般產(chǎn)生原因都是應用一次性批量插入或者批量刪除大量的數(shù)據(jù)，事務執(zhí)行時間長，binglog cache緩存不足，產(chǎn)生大量臨時緩存文件。如果一個會話(連接)里面有一個未提交事務，然后不做任何操作，那么這個線程處于SLEEP態(tài)。例如通過下列SQL語句查詢當前數(shù)據(jù)庫中

SELECT t.trx_mysql_thread_id

,t.trx_state

,t.trx_tables_in_use

,t.trx_tables_locked

,t.trx_query

,t.trx_rows_locked

,t.trx_rows_modified

,t.trx_lock_structs

,t.trx_started

,t.trx_isolation_level

,p.time

,p.user

,p.host

,p.db

,p.command

FROM?? information_schema.innodb_trx t

INNER JOIN information_schema.processlist p

ON t.trx_mysql_thread_id = p.id

WHERE? t.trx_state = 'RUNNING'

AND p.time > 10

AND p.command = 'Sleep'

【解決方法】

臨時方法：Kill?進程ID?如上圖查詢中事務ID，trx_mysql_thread_id=6。?即Kill 6

長期解決方法：大事務拆分、分時、分批處理。

MySQL

版權(quán)聲明：本文內(nèi)容由網(wǎng)絡用戶投稿，版權(quán)歸原作者所有，本站不擁有其著作權(quán)，亦不承擔相應法律責任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實的內(nèi)容，請聯(lián)系我們jiasou666@gmail.com 處理，核實后本網(wǎng)站將在24小時內(nèi)刪除侵權(quán)內(nèi)容。

版權(quán)聲明：本文內(nèi)容由網(wǎng)絡用戶投稿，版權(quán)歸原作者所有，本站不擁有其著作權(quán)，亦不承擔相應法律責任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實的內(nèi)容，請聯(lián)系我們jiasou666@gmail.com 處理，核實后本網(wǎng)站將在24小時內(nèi)刪除侵權(quán)內(nèi)容。