作者：dbapower
鏈接：https://blog.51cto.com/suifu/2135599

背景

Part1:寫(xiě)在最前

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

Part2:背景介紹

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

我們首先來(lái)看下該表的表結(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ù)了解得知，該表幾乎沒(méi)有刪除操作，由于數(shù)據(jù)量過(guò)大，我們模糊使用auto_increment來(lái)作為表數(shù)量預(yù)估值，避免count(）操作對(duì)線上造成的影響。

Part3:案例分析

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

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

也就是說(shuō)，此時(shí)的delete語(yǔ)句變?yōu)榱?#xff1a;

mysql> delete from tablename_v2 where id <2577754125;

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

實(shí)戰(zhàn)

Part1:監(jiān)控

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

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

Part2:實(shí)戰(zhàn)操作

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

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

同時(shí)，清理一些能夠清理的無(wú)用日志、廢舊文件等等。

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

Part3:pt-archiver

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

閑話(huà)不多說(shuō)，一向本著實(shí)戰(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

簡(jiǎn)單說(shuō)下常用的參數(shù)：

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

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

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

如果您之前關(guān)注過(guò)筆者的文章，應(yīng)該知道，當(dāng)使用了delete from xxx where xxx limit 語(yǔ)法時(shí)，會(huì)將binlog_format從mixed轉(zhuǎn)為row，這樣的話(huà)，刪除的同時(shí)，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，這樣的話(huà)就能夠保證正常的delete，而不加limit，binlog不會(huì)轉(zhuǎn)為row格式導(dǎo)致磁盤(pán)消耗繼續(xù)激增。

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

Part4:困惑

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

不能alter操作，因?yàn)檫@會(huì)阻塞dml操作。

對(duì)于本案例，空間本就不足，也不能使用pt-online工具來(lái)完成。

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

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

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

Part5:再談binlog_format

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

這里這篇文章又拋出一個(gè)新的案例，在官網(wǎng)那篇何時(shí)mixed轉(zhuǎn)row格式中又一個(gè)沒(méi)有記錄的情況

官方文檔：

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)

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

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

INSERT ... ON DUPLICATE KEY UPDATE

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

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

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

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

總結(jié)

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

總結(jié)

以上是生活随笔為你收集整理的delete mysql 大表_无语了，直到今天，我才揪出MySQL磁盘消耗迅猛的“真凶”！的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。