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

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

背景

Part1:寫在最前

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

Part2:背景介紹

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

我們首先來看下該表的表結(jié)構(gòu)：

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;

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

Part3:案例分析

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

經(jīng)過與業(yè)務(wù)協(xié)商，我們確定了可以將id作為刪除條件，刪除id<2577754125之前的數(shù)據(jù)

也就是說，此時的delete語句變?yōu)榱耍?/p>

mysql> delete from tablename_v2 where id <2577754125;

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

實戰(zhàn)

Part1:監(jiān)控

從監(jiān)控圖我們能看出磁盤下降的趨勢：

監(jiān)控顯示，從6月14日-6月18日期間，磁盤消耗最為嚴重，與業(yè)務(wù)溝通得知，618期間大促引發(fā)該表存儲量激增導(dǎo)致。

Part2:實戰(zhàn)操作

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

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

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

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

Part3:pt-archiver

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

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

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

簡單說下常用的參數(shù)：

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

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

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

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

因此最終的命令為：

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不會轉(zhuǎn)為row格式導(dǎo)致磁盤消耗繼續(xù)激增。

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

Part4:困惑

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

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

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

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

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

再有就是遷移TiDB，TiDB相較MySQL更適合存儲這類業(yè)務(wù)。

Part5:再談binlog_format

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

這里這篇文章又拋出一個新的案例，在官網(wǎng)那篇何時mixed轉(zhuǎn)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)

我們這個案例中又出現(xiàn)了一個新的因素就是：

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

INSERT ... ON DUPLICATE KEY UPDATE

這時，mysql binlog_format就會被轉(zhuǎn)為row格式，這個內(nèi)容也是記錄在官網(wǎng)的其他章節(jié)：

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

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

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

——總結(jié)——

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