[MySQL] 揪出MySQL磁盘消耗迅猛的真凶

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;

mysql> delete from tablename_v2 where id <2577754125;

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

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

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

• 不能alter操作，因为这会阻塞dml操作。
  
• 对于本案例，空间本就不足，也不能使用pt-online工具来完成。
  

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)

INSERT ... ON DUPLICATE KEY UPDATE