揪出MySQL磁盤消耗迅猛的真兇

背景

Part1:寫在最前

當一張單表10億數據量的表放在你面前，你將面臨著什么？

Part2:背景介紹

為了提升數據庫資源利用率，一個實例中，在不互相影響，保證業務高效的前提下，我們會將同一個大業務下的不同小業務放在一個實例中，我們的磁盤空間是2T，告警閾值為當磁盤剩余空間10%時發出短信告警。筆者接到某業務主庫磁盤剩余空間告警的短信后，經過一番查探，發現從幾天前開始，有一張表的數據量增長非常快，而在之前，磁盤空間下降率還是較為平緩的，該表存在大字段text，其大批量寫入更新，導致磁盤消耗迅猛。

我們首先來看下該表的表結構：

mysql> CREATE TABLE `tablename_v2` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `No` varchar(64) NOT NULL DEFAULT '',
  `Code` varchar(64) NOT NULL DEFAULT '' ,
  `log` varchar(64) DEFAULT '' ,
  `log1` varchar(64) DEFAULT '' ,
   .....
  `Phone` varchar(20) DEFAULT '',
  `createTime` bigint(20) unsigned NOT NULL DEFAULT '0',
  `updateTime` bigint(20) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_mailNo` (`No`,`Code`),
  KEY `idx_Phone` (`Phone`)
) ENGINE=InnoDB AUTO_INCREMENT=9794134664 DEFAULT CHARSET=utf8;

與業務了解得知，該表幾乎沒有刪除操作，由于數據量過大，我們模糊使用auto_increment來作為表數量預估值，避免count(）操作對線上造成的影響。

Part3:案例分析

與業務溝通了解后得知，該表可以清理4個月以前的老舊數據，因此可以使用delete的方式清除，而我們通過表結構可以看出，該表在設計之初，并沒有對updateTime列創建索引，因此以時間為范圍去進行delete操作，這顯然是不合理的。

經過與業務協商，我們確定了可以將id作為刪除條件，刪除id<2577754125之前的數據

也就是說，此時的delete語句變為了：

mysql> delete from tablename_v2 where id <2577754125;

且不說delete操作有多慢，直接執行這樣的SQL也會有諸如長事務告警，從庫大量延遲等并發癥產生，因此絕不能在生產庫上進行這種大批量的危險操作。

實戰

Part1:監控

從監控圖我們能看出磁盤下降的趨勢：

監控顯示，從6月14日-6月18日期間，磁盤消耗最為嚴重，與業務溝通得知，618期間大促引發該表存儲量激增導致。

Part2:實戰操作

我們通過查看binlog發現，集群中binlog的刷新量雖不說像筆者上個案例那樣多么迅猛，但也絕不是老實本分

我們可以看出，在高峰期間，binlog的刷新間隔最短達到了2分鐘寫滿1.1GB的binlog。因此筆者與業務溝通后，首先清理binlog日志，將 expire_logs_days從7天調整至3天。

同時，清理一些能夠清理的無用日志、廢舊文件等等。

我們也能在上面的監控圖看到在做完這些清理操作后，磁盤空間剩余從4%提升至12%,但隨后依舊保持原有速率下降。

Part3:pt-archiver

真兇找到了，我們怎么辦，別急，使用pt-archiver。pt-archiver工具是percona工具集的一員，是歸檔MySQL大表數據的最佳輕量級工具之一。他可以實現分chunk分批次歸檔和刪除數據，能避免一次性操作大量數據帶來的各種問題。

閑話不多說，一向本著實戰的原則，我們直接上命令：

pt-archiver --source
h=c3-helei-db01.bj,D=helei,t=tablename_v2,u=sys_admin,p=MANAGER
--where 'id<2577754125' --purge --progress 10000 --limit=10000
--no-check-charset --txn-size=10000 --bulk-delete --statistics --max-lag=20
--check-slave-lag c3-helei-db02.bj

簡單說下常用的參數：

Warning:警告這里就又有個小坑了，的確，我們使用bulk-delete參數能夠增加刪除速率，相比不使用bulk-delete速度能夠提升10倍左右，但問題也就顯現出來，在使用上述命令期間，發現binlog每秒寫入量激增，這又回到了我們說的，哪些情況會導致binlog轉為row格式。

首先我們需要了解到使用bulk-delete時，sql是如下執行的：

mysql> delete from tablename_v2 where id >xxx and id < xxx limit 10000.

如果您之前關注過筆者的文章，應該知道，當使用了delete from xxx where xxx limit 語法時，會將binlog_format從mixed轉為row，這樣的話，刪除的同時，binlog由于轉為了row格式也在激增，這與我們的預期是不符的。

因此最終的命令為：

pt-archiver --source
h=c3-helei-db01.bj,D=helei,t=tablename_v2,u=sys_admin,p=MANAGER
--where 'id<2577754125' --purge --progress 10000 --limit=10000
--no-check-charset --txn-size=10000  --statistics --max-lag=20
--check-slave-lag c3-helei-db02.bj

去掉了bulk-delete，這樣的話就能夠保證正常的delete，而不加limit，binlog不會轉為row格式導致磁盤消耗繼續激增。

對于Innodb引擎來說，delete操作并不會立即釋放磁盤空間，新的數據會優先填滿delete操作后的“空洞”，因此從監控來看就是磁盤不會進一步消耗了，說明我們的pt-archiver工具刪除是有效的。

Part4:困惑

首先我們要知道，當你面對一張數據量龐大的表的時候，有些東西就會受限制，例如：

1. 不能alter操作，因為這會阻塞dml操作。

2. 對于本案例，空間本就不足，也不能使用pt-online工具來完成。

對于不能alter其實是比較要命的，比如開發要求在某個時間段盡快上線新業務，而新業務需要新增列，此時面對這么龐大的量級，alter操作會異常緩慢。

因此，筆者與研發溝通，盡快采用物理分表的方式解決這個問題，使用物理分表，清理表的操作就會很容易，無需delete，直接drop 老表就可以了。其次，物理分表讓alter語句不會卡住太久，使用pt-online工具也不會一次性占據過多的磁盤空間誘發磁盤空間不足的告警。

再有就是遷移TiDB，TiDB相較MySQL更適合存儲這類業務。

Part5:再談binlog_format

我們選取其中高峰期的binlog發現其update操作轉為了row格式，記錄了所有列變更前后的所有信息，而binlog中并未出現update xxx limit這種操作，那又會是什么引發的row格式記錄呢？

這里這篇文章又拋出一個新的案例，在官網那篇何時mixed轉row格式中又一個沒有記錄的情況

官方文檔：

When running in MIXED logging format, the server automatically switches from statement-based to row-based logging under the following conditions:
When a DML statement updates an NDBCLUSTER table.
When a function contains UUID().
When one or more tables with AUTO_INCREMENT columns are updated and a trigger or stored function is invoked. Like all other unsafe statements, this generates a warning if binlog_format = STATEMENT.
When any INSERT DELAYED is executed.
When a call to a UDF is involved.
If a statement is logged by row and the session that executed the statement has any temporary tables, logging by row is used for all subsequent statements (except for those accessing temporary tables) until all temporary tables in use by that session are dropped.
This is true whether or not any temporary tables are actually logged.
Temporary tables cannot be logged using row-based format; thus, once row-based logging is used, all subsequent statements using that table are unsafe. The server approximates this condition by treating all statements executed during the session as unsafe until the session no longer holds any temporary tables.
When FOUND_ROWS() or ROW_COUNT() is used. (Bug #12092, Bug #30244)
When USER(), CURRENT_USER(), or CURRENT_USER is used. (Bug #28086)
When a statement refers to one or more system variables. (Bug #31168)

我們這個案例中又出現了一個新的因素就是：

當表結構中存在多個唯一索引（包括主鍵id），本案例中存在主鍵和UNIQUE KEY `uk_mailNo`這個唯一索引，且使用了

INSERT ... ON DUPLICATE KEY UPDATE

這時，mysql binlog_format就會被轉為row格式，這個內容也是記錄在官網的其他章節：

https://dev.mysql.com/doc/refman/5.5/en/insert-on-duplicate.html

也就是說，只要業務解決了使用這種語法插入的話，磁盤空間下降迅猛的原因也能夠緩解不少。我們統計發現，qps更高的其他業務中,binlog保留7天的磁盤消耗量在60GB

而該業務我們僅僅保留3天binlog，卻依舊消耗了430GB的磁盤空間，這已經超過了我們整個2T磁盤空間的5分之一了。

——總結——

通過這個案例，我們能夠了解到什么情況下binlog_format會由MIXED格式轉為ROW格式，以及觸發的一系列并發癥和解決辦法，還有pt工具pt-archiver的使用。由于筆者的水平有限，編寫時間也很倉促，文中難免會出現一些錯誤或者不準確的地方，不妥之處懇請讀者批評指正。喜歡筆者的文章，右上角點一波關注，謝謝！

另外有需要云服務器可以了解下創新互聯cdcxhl.cn，海內外云服務器15元起步，三天無理由+7*72小時售后在線，公司持有idc許可證，提供“云服務器、裸金屬服務器、高防服務器、香港服務器、美國服務器、虛擬主機、免備案服務器”等云主機租用服務以及企業上云的綜合解決方案，具有“安全穩定、簡單易用、服務可用性高、性價比高”等特點與優勢，專為企業上云打造定制，能夠滿足用戶豐富、多元化的應用場景需求。