排查实战：磁盘告警背后的隐形写入与优化)
1. 一次磁盘告警背后的“隐形写入”COW 离我们并不远1.1 那个让我整晚盯着磁盘告警的迁移前阵子帮一个朋友的项目做数据迁移A同学负责的那套系统要从一台老机器迁到新机器数据量大概两百多G想着预留了百分之五十的余量怎么都够了。结果导入刚开始没到一个小时监控就报警了磁盘使用率直奔百分之九十。我当时第一反应是某个日志文件写疯了跑上去一看du结果显示数据目录里那个表文件大小倒是正常但磁盘可用空间却在以肉眼可见的速度往下掉而且掉得非常规律——每隔几十秒就回落一截。更诡异的还在后面。我用lsof查了 MySQL 进程打开的文件发现有一个#sql开头的临时文件正在疯狂增长。检查 SQL 日志确认是在做一个大表的ORDER BY ... LIMIT查询内存临时表一满就转成了磁盘临时表排序过程里 MySQL 会对临时表的数据页做反复的读改写。A同学的第一反应是“读操作怎么就写出这么多 IO 呢”我告诉他这就是标题里那个词MySQL 写时复制COW——你在逻辑上觉得只是读物理上却已经把数据复制了一份又一份。那次之后我把 COW 相关的场景重新梳理了一遍发现很多人都在这上面栽过跟头只是当时不知道这个现象叫什么名字。1.2 这篇文章要帮你解决什么问题这篇文章不是念概念我想用排查现场的方式来拆解 MySQL 写时复制COW这件事。你会看到它到底复制了什么、哪些操作会触发、为什么磁盘会突然翻倍、以及怎么用参数和语句把这种“隐形写入”摁下去。适合谁来读两类人。第一类是 DBA 和运维工程师你手底下的库一旦出现磁盘突发增长、导入变慢、备份占用异常这文章能帮你快速定位。第二类是业务研发你写的 SQL 如果经常出现大排序、大临时表、大字段更新那么 COW 导致的额外写入成本迟早会通过“服务器卡顿”“磁盘告警”的形式反馈到你的工单里。我先把结论摆在前头COW 不是一个开关不是某个参数它更像是一种存储行为模式。而 MySQL 在文件系统、存储引擎、SQL 执行三个层面都有和 COW 纠缠不清的地方。理解了这三层你才能真的读懂那些“莫名其妙变大”的磁盘空间。2. 写时复制COW到底复制了什么三套语义必须分清2.1 文件系统层的 COW按块复制的老实人最经典、也最符合字面意思的 COW发生在文件系统层。像 ZFS、Btrfs、以及很多云硬盘的快照机制都采用了 copy-on-write 的策略当你对某个文件做快照或克隆时并不会真的立刻复制整份数据而是先把元数据记下来数据块继续共用等到某个数据块第一次被修改才把原来的块复制一份把修改写到新的块上旧的块留给快照。这个设计有个特别反直觉的特征在修改发生的那一瞬间磁盘会同时存在新旧两份数据块。哪怕你只改了一个字节文件系统也可能复制一整个 4KB 块甚至 64KB、1MB 的簇。类比来说你手头有一本被图书馆锁定的书系统让你在书页上改一句话但规定不能动原书于是你只能把那一整页复印出来在复印件上改完再放回去。复印件没用了也不能扔因为原书还要保持在没改动的状态给别的读者看。MySQL 在文件系统层的 COW 就是这样底层存储为了一张数据页的修改额外写了一张旧页和一张新页。而这两张页的写入对 MySQL 的用户态来说几乎完全不可见。所以当你在一个 COW 文件系统上跑 MySQL或者用文件系统快照、克隆功能做备份时磁盘空间的使用量会呈现出“数据量 变更量总和”的增长形态。你给数据目录留了 1.5 倍余量可能还是会被撑满。2.2 存储引擎层的页复制InnoDB 的“复制加点改”第二个层面的 COW严格来说不应该叫 COW但在表现上非常相似就是 InnoDB 存储引擎在页面拆分、合并、重建时产生的“旧页复制”。InnoDB 的 B 树页大小默认是 16KB数据页在内存中刷到磁盘时如果遇到 Page Split旧页和新页会同时落盘某些情况下还会触发对相邻页的重写。举个例子你在一个自增主键的表里插入一批数据B 树叶子节点满了以后需要分裂。表面上你只是插入了 N 行实际上引擎要把节点里的部分记录复制到一个新页并且修改旧页的指针域。这个操作是引擎内部完成的MySQL 用户看不出来但磁盘写量和 redo log 增长都会明显高于“插入行数 × 行大小”的理论值。这种“复制加点改”的模式在很多官方文档里不叫 COW通常会叫写放大write amplification。但对排查的人来说现象是一样的数据文件在某个操作前后突然变大一截或者 redo log 疯狂增长。你不需要纠结术语精确到哪个级别只需要知道修改一行数据不等于只写那一行。2.3 别把 COW 和 MVCC 混为一谈还有一种常见的误解是把 COW 和 InnoDB 的 MVCC 多版本并发控制混在一起说。MVCC 靠的是 undo log旧版本的行被放到 undo 里数据页本身还是那一个页不会被复制出来。这是“变更记录”而不是“复制副本”两者的 IO 成本差异极大。有段时间内部讨论有人说“InnoDB 的 MVCC 就是写时复制”我专门纠正过MVCC 的 undo log 是顺序写而且可以 purge回收成本低而真正意义上的 COW 是块级复制旧块和旧块之后的地址都不可复用直到快照释放。你在performance_schema里看到innodb_data_writes和innodb_data_fsyncs飙升但rows_updated并不高那就要往页复制、块复制的方向想了。分清这三层语义排查就不会歪。文件系统层的 COW表现为整个磁盘空间被吃、但 MySQL 数据文件变化不大引擎层的页复制表现为.ibd文件增长、redo log 膨胀而 MVCC 引发的 undo 膨胀则是另一种问题不要混在一起。3. 触发 COW 的常见操作哪些行为在偷偷放大写入3.1 大字段更新溢出页的隐形负担先说一个最容易被业务忽略的场景更新大字段比如TEXT、BLOB、超长VARCHAR。InnoDB 对超过一定长度的字段不会全部塞在聚簇索引叶子节点里而是把大值放到溢出页off-page叶子节点里只保留 768 字节的前缀指针。问题就出在这里。当你去更新这个字段的值即使是全量替换InnoDB 也可能把原来的溢出页标记为可回收再分配一个新的页写入新值。这个过程涉及旧页的更新、新页的分配以及可能的页拆分。如果字段值很大一次更新可能带来好几个 16KB 页的写入而逻辑上你只是 update 了一条记录。我做过的性能测试里一个十多万行的表每行有一个 10KB 左右的TEXT字段做全表更新。理论写入量大概是 1GB 多实际观察到的数据文件写量在 3GB 左右加起来接近 2 到 3 倍。这还没算 redo log。也就是说如果你负责的业务表里有大字段并且更新频繁COW 效应会被极度放大。处理方案后面会讲这里先说一句大字段更新前别只看行数要按“字段最大长度 × 更新行数 × 2”来估算写量磁盘和监控都得按这个预期来配。3.2 磁盘临时表与大排序最容易忽略的 COW 源头回到开头那个场景磁盘临时表是 COW 最容易出现、也最容易被忽略的地方。MySQL 里GROUP BY、ORDER BY、DISTINCT、多表JOIN都可能产生临时表。临时表优先用内存tmp_table_size控制的是内存临时表的最大大小默认 16MB或者 1MB不同版本有差异超过这个限制之后隐式临时表引擎会转为磁盘上的 InnoDB 临时表。磁盘临时表的问题在于它不是一次写完再排序的。排序算法在归并阶段会反复读写临时文件每一轮合并都会把数据页读出来、写回去甚至复制。如果数据量是 1GB一轮合并写 1GB两轮就写 2GB再加上原来临时表的落盘最终写量轻松到 3 倍以上。我在定位 A 同学那个问题时执行过一条查询EXPLAIN里Extra列明显写着Using temporary; Using filesort。临时表已经转成磁盘表而磁盘表引擎是 InnoDB意味着 InnoDB 页复制、写放大这一整套机制在临时表上也会完整发生一遍。这里还要提醒一句MySQL 8.0 里 InnoDB 临时表有自己的表空间ibtmp1如果它一直不回缩不是 bug而是临时表频繁创建、页复制导致的增长结果。很多人问我这个文件为什么越来越大其实背后就是 COW 在折腾。3.3 在线 DDL 与表空间克隆明刀明枪的全量复制第三种触发 COW 的操作是 DDL。MySQL 的ALTER TABLE有两种算法ALGORITHMCOPY和ALGORITHMINPLACE。COPY 算法很好理解新建一张一模一样的表把原表的数据一行行复制过去完成后删旧表。这个过程中新表占据的磁盘空间和旧表几乎一样大数据文件瞬时翻倍。INPLACE 算法虽然不用复制整表但它可能要重建索引。索引重建意味着把索引页读出来、排序、再写入新的索引树期间旧索引页还在写量一样会翻倍。所以不管哪种算法大表 DDL 的隐藏前提是磁盘至少要有表大小的一倍以上空闲。如果你还开着gh-ost这类工具做在线变更它的逻辑是复制全量数据写量几乎必翻倍。此外MySQL 的 CLONE 插件CLONE LOCAL/CLONE REMOTE创建克隆表空间时如果底层文件系统支持快速克隆会有类似文件系统快照的 COW 行为克隆瞬间很快但之后任意一个实例写入数据都会触发块级复制导致两个实例的数据文件合计占用持续增长。这个场景很多人没提前想到等发现时磁盘已经爆了。3.4 不同触发场景的影响对比把常见触发场景放在一起对比更清晰触发场景逻辑写入实际写量放大倍数最容易观察到的现象大字段更新更新行数 × 字段大小3 倍左右数据文件增长 redo log 膨胀磁盘临时表排序查询结果集大小3 倍以上ibtmp1增长、磁盘告警COPY 算法 DDL原表大小2 倍新表 旧表数据目录瞬时翻倍INPLACE 算法 DDL索引大小1.5~2 倍对应索引表空间增长CLONE 克隆 写操作变更量持续增长两个实例磁盘合计异常文件系统快照备份备份期间增量增量 × 2备份卷剩余空间骤减这张表不是精确的数学公式只是一个经验区间。我见过很多同学只盯着逻辑 IO 看忽略了物理写量结果就是磁盘和性能双双出问题。记住一点MySQL 对磁盘的消耗从来不是“你改了多少”这么简单。4. 一次从“猜测”到“证据”的完整排查链路4.1 第一步按文件定位空间去向遇到磁盘告警不要一上来就rm日志。先搞清楚空间到底去哪儿了。我惯用的命令组合是df -h du -sh /var/lib/mysql/*看数据目录下每个文件和子目录的大小。如果看到ibtmp1临时表空间涨到几十 G 甚至上百 G而业务数据文件不大那基本可以确定是临时表触发了 COW 写放大。如果看到某个具体的.ibd文件在快速变大说明有高频 DML 正在让它持续扩容。还有一种情况数据目录大小看着正常但整个磁盘还是满了。这时候要查看操作系统的日志和挂载情况确认 MySQL 是不是把临时文件写到了别的分区。我遇到过一次tmpdir配置没改临时文件全写到了系统盘/tmp下数据盘倒是干净系统盘却爆了。ls -lhS /var/lib/mysql/ | head -20 ls -lhS /tmp/ | head -20按文件大小倒序排列重点看那些你印象中不该存在的超大文件。4.2 第二步找出打开中的临时文件与写入进程文件定位到了还要确认是谁在写。lsof是最直接的东西。lsof -p $(pidof mysqld) | grep -E sql|tmp|ibtmp|ibd如果你和我一样不喜欢装多余工具也可以用/proc文件系统for fd in /proc/$(pidof mysqld)/fd/*; do ls -l $fd done | grep -E deleted|tmp|ibtmp看到#sql开头的文件正在增长就说明某个 SQL 正在使用磁盘临时表。这时候回到 MySQL 里用SHOW PROCESSLIST找出正在执行的查询看看它的State是不是Copying to tmp table或者Sorting result。如果有iotop或pidstat可以同时观察块设备写入速率确认到底是 MySQL 在写还是日志清理等其他进程在搞事pidstat -d 1 5这个阶段的目标是把“嫌疑进程”锁死不要被同事口中“是不是有人在跑什么大任务”这种说法带偏。你要的是证据。4.3 第三步用状态计数器和日志锁定根因进程锁定了再进 MySQL 内部用状态变量确认临时表的情况SHOW GLOBAL STATUS LIKE Created_tmp_disk_tables; SHOW GLOBAL STATUS LIKE Created_tmp_tables; SHOW GLOBAL STATUS LIKE Innodb_rows_read; SHOW GLOBAL STATUS LIKE Innodb_data_writes;重点看Created_tmp_disk_tables和Created_tmp_tables的比值。如果磁盘临时表的创建次数高说明内存临时表频繁超过阈值COW 放大基本实锤。再配合慢查询日志或者 performance_schema 来看具体的 SQL。先确认影响行数不大但 IO 很大通常就是临时表引擎在刷盘。A 同学那个案例里慢查询日志捕获到了一条语句单次ORDER BY搬运了近千万行实际返回结果只有一百条。这种 SQL 在 MySQL 眼里就是“把千万行塞进临时表再排序”磁盘临时表加上页复制写量根本压不住。4.4 第四步验证优化效果根因找到优化完之后一定要回头验证不然等于白干。一种验证方法是对比优化前后的状态变量SHOW GLOBAL STATUS LIKE Created_tmp_disk_tables; SHOW GLOBAL STATUS LIKE Innodb_data_writes;跑同样的查询观察这两个值是否明显下降。更直观的做法是在优化前后分别记录磁盘可用空间行数和执行语句一致看磁盘消耗是否还在快速增长。A同学当时的修复就是先把那条 SQL 改成了只取必要的字段绕过大临时表然后调大了tmp_table_size。之后同样的查询Created_tmp_disk_tables不再涨了磁盘告警也消停了。5. 驯服 COW 的参数与实操策略5.1 临时表参数别让排序把磁盘拖垮临时表引发 COW 的核心原因是内存放不下所以最直接的策略是让临时表尽量留在内存里。MySQL 里控制这个行为的参数是两个tmp_table_size和max_heap_table_size。注意实际生效的上限是两者之间的较小值。[mysqld] tmp_table_size 128M max_heap_table_size 128M不过真要调大先看业务需求别无脑设几百兆。内存临时表用的朴素内存不是共享缓存太大容易把内存吃光。128MB 是我常用的一个起点如果查询结果集经常超过这个值更该从 SQL 优化或者分页上下手。另外MySQL 8.0 的 Internal Temporary Table 引擎可以配置为TempTable它比旧版MEMORY引擎支持更多字段类型也能避免一些类型兼容性问题导致的隐式磁盘临时表。可以在配置里设置[mysqld] internal_tmp_mem_storage_engine TempTable而磁盘临时表的空间也有限制MySQL 8.0 里innodb_temp_tablespaces_dir可以控制临时表空间文件位置你可以把它放到一块更充裕的独立磁盘上避免和主数据争抢 IO。5.2 在线 DDL 策略能用 INPLACE 就不要 COPYDDL 场景的 COW主要靠算法选择来控制。MySQL 5.6 以后支持ALGORITHMINPLACE但在某些操作上仍会退化到 COPY。我的经验是大表 DDL 前先查官方文档确认你要做的变更是否支持 INPLACE如果不支持再考虑用在线变更工具。在 DDL 语句里显式指定算法和锁粒度别让 MySQL 自己猜ALTER TABLE big_table ADD INDEX idx_name (column_name), ALGORITHMINPLACE, LOCKNONE;LOCKNONE允许 DDL 期间并发 DML但对 IO 的压力不会消失。数据库层面唯一能做的是把 DDL 安排在业务低峰期并且提前给磁盘留足空间。我个人的预留标准是大表 DDL 前保证数据目录可用空间两倍以上如果是 COPY 算法就是三倍以上。顺带把 binlog 的因素也算进去。DDL 期间产生的 DML 会全部写进 binlog临时表和重做日志的 IO 也会叠加。如果空间紧张可以考虑在 DDL 窗口关闭非核心表的 binlog 记录用临时变量控制sql_log_bin0但这个操作要慎重会影响增量恢复。大多数情况下我不建议为了节省空间牺牲一致性。5.3 存储与文件系统选型把 COW 用在刀刃上写时复制本身不是坏事文件系统快照、克隆、备份都是它的优点。问题出在把 COW 存储当作普通磁盘去跑繁忙的 OLTP。如果实例压力大、写入频繁而数据卷恰好用的是 ZFS、Btrfs 这类 COW 文件系统那么每一次数据页刷盘都可能多产生一次块复制。表现在延迟上就是偶尔出现几百毫秒的尖刺很多 DBA 完全查不出原因最后才发现是存储层的写放大。这不是说 COW 文件系统不能用于 MySQL。更合理的做法是把热数据放在非 COW 卷比如传统文件系统的磁盘阵列把备份、快照、归档放在 COW 卷上。这样既利用了 COW 的即时快照优势又避免了线上实例的写放大。至于日志redo log 和 binlog 的写入模式本来就是顺序追加放在非 COW 卷上也能减少额外开销建议和数据文件分盘。5.4 变更前的三件套检查最后分享一个实操习惯我在做任何可能触发 COW 的变更前固定会执行三条检查权当“三件套”第一看磁盘余量。df -h确认数据目录所在分区至少还有数据量两倍以上的空间。如果要做克隆、迁移、DDL这个数字自动上调到三倍。第二看状态计数器。SHOW GLOBAL STATUS LIKE Created_tmp_disk_tables记下一个基准值变更过程中再查一次增长量太大就说明临时表在刷盘。第三看进程现场。SHOW ENGINE INNODB STATUS里看一眼LOG部分注意Log sequence number的增长速度。如果 LSN 增长明显快于逻辑写入的速度那就是页级复制或者说写放大在起作用需要回查操作和参数。三件事都不难但每次都能帮我提前发现问题。很多 DBA 习惯等到告警来了才去查其实 COW 的写入从变更一开始就已经发生了只是前几分钟的增量还不够明显。6. 写在最后的实操习惯COW 这个概念听起来像是存储专家才会研究的底层机制但实际碰到的频率远比想象高。我这些年排查过不少“诡异”性能问题最后都归结到写时复制在背后捣鬼。它不一定是故障而是一种成本模型同样的逻辑操作物理资源消耗可能成倍上涨而这一切对上层透明。我自己现在养成了一个习惯只要遇到下列三种情况就会主动往 COW 上想第一种大表 DML 后磁盘使用率增速远超预估第二种ibtmp1异常膨胀查慢查询总能发现Using temporary第三种迁移或备份期间出现了莫名其妙的写延迟尖刺。这些场景的共同点是逻辑 IO 和物理 IO 严重不匹配而 COW 就是一种非常典型的导致不匹配的机制。文章最后补一个在实操中总结的小技巧在低峰期给performance_schema打开相关的等待事件统计观察wait/io/file/innodb/innodb_data_file的等待时间。如果一次查询中这个等待时间占比异常高同时你的 SQL 计划里存在排序或临时表那基本就是 COW 写放大在拖后腿。判断依据不需要很玄多来几次对比实验你对 COW 的理解会比任何文档都准确。
1. 一次磁盘告警背后的“隐形写入”COW 离我们并不远1.1 那个让我整晚盯着磁盘告警的迁移前阵子帮一个朋友的项目做数据迁移A同学负责的那套系统要从一台老机器迁到新机器数据量大概两百多G想着预留了百分之五十的余量怎么都够了。结果导入刚开始没到一个小时监控就报警了磁盘使用率直奔百分之九十。我当时第一反应是某个日志文件写疯了跑上去一看du结果显示数据目录里那个表文件大小倒是正常但磁盘可用空间却在以肉眼可见的速度往下掉而且掉得非常规律——每隔几十秒就回落一截。更诡异的还在后面。我用lsof查了 MySQL 进程打开的文件发现有一个#sql开头的临时文件正在疯狂增长。检查 SQL 日志确认是在做一个大表的ORDER BY ... LIMIT查询内存临时表一满就转成了磁盘临时表排序过程里 MySQL 会对临时表的数据页做反复的读改写。A同学的第一反应是“读操作怎么就写出这么多 IO 呢”我告诉他这就是标题里那个词MySQL 写时复制COW——你在逻辑上觉得只是读物理上却已经把数据复制了一份又一份。那次之后我把 COW 相关的场景重新梳理了一遍发现很多人都在这上面栽过跟头只是当时不知道这个现象叫什么名字。1.2 这篇文章要帮你解决什么问题这篇文章不是念概念我想用排查现场的方式来拆解 MySQL 写时复制COW这件事。你会看到它到底复制了什么、哪些操作会触发、为什么磁盘会突然翻倍、以及怎么用参数和语句把这种“隐形写入”摁下去。适合谁来读两类人。第一类是 DBA 和运维工程师你手底下的库一旦出现磁盘突发增长、导入变慢、备份占用异常这文章能帮你快速定位。第二类是业务研发你写的 SQL 如果经常出现大排序、大临时表、大字段更新那么 COW 导致的额外写入成本迟早会通过“服务器卡顿”“磁盘告警”的形式反馈到你的工单里。我先把结论摆在前头COW 不是一个开关不是某个参数它更像是一种存储行为模式。而 MySQL 在文件系统、存储引擎、SQL 执行三个层面都有和 COW 纠缠不清的地方。理解了这三层你才能真的读懂那些“莫名其妙变大”的磁盘空间。2. 写时复制COW到底复制了什么三套语义必须分清2.1 文件系统层的 COW按块复制的老实人最经典、也最符合字面意思的 COW发生在文件系统层。像 ZFS、Btrfs、以及很多云硬盘的快照机制都采用了 copy-on-write 的策略当你对某个文件做快照或克隆时并不会真的立刻复制整份数据而是先把元数据记下来数据块继续共用等到某个数据块第一次被修改才把原来的块复制一份把修改写到新的块上旧的块留给快照。这个设计有个特别反直觉的特征在修改发生的那一瞬间磁盘会同时存在新旧两份数据块。哪怕你只改了一个字节文件系统也可能复制一整个 4KB 块甚至 64KB、1MB 的簇。类比来说你手头有一本被图书馆锁定的书系统让你在书页上改一句话但规定不能动原书于是你只能把那一整页复印出来在复印件上改完再放回去。复印件没用了也不能扔因为原书还要保持在没改动的状态给别的读者看。MySQL 在文件系统层的 COW 就是这样底层存储为了一张数据页的修改额外写了一张旧页和一张新页。而这两张页的写入对 MySQL 的用户态来说几乎完全不可见。所以当你在一个 COW 文件系统上跑 MySQL或者用文件系统快照、克隆功能做备份时磁盘空间的使用量会呈现出“数据量 变更量总和”的增长形态。你给数据目录留了 1.5 倍余量可能还是会被撑满。2.2 存储引擎层的页复制InnoDB 的“复制加点改”第二个层面的 COW严格来说不应该叫 COW但在表现上非常相似就是 InnoDB 存储引擎在页面拆分、合并、重建时产生的“旧页复制”。InnoDB 的 B 树页大小默认是 16KB数据页在内存中刷到磁盘时如果遇到 Page Split旧页和新页会同时落盘某些情况下还会触发对相邻页的重写。举个例子你在一个自增主键的表里插入一批数据B 树叶子节点满了以后需要分裂。表面上你只是插入了 N 行实际上引擎要把节点里的部分记录复制到一个新页并且修改旧页的指针域。这个操作是引擎内部完成的MySQL 用户看不出来但磁盘写量和 redo log 增长都会明显高于“插入行数 × 行大小”的理论值。这种“复制加点改”的模式在很多官方文档里不叫 COW通常会叫写放大write amplification。但对排查的人来说现象是一样的数据文件在某个操作前后突然变大一截或者 redo log 疯狂增长。你不需要纠结术语精确到哪个级别只需要知道修改一行数据不等于只写那一行。2.3 别把 COW 和 MVCC 混为一谈还有一种常见的误解是把 COW 和 InnoDB 的 MVCC 多版本并发控制混在一起说。MVCC 靠的是 undo log旧版本的行被放到 undo 里数据页本身还是那一个页不会被复制出来。这是“变更记录”而不是“复制副本”两者的 IO 成本差异极大。有段时间内部讨论有人说“InnoDB 的 MVCC 就是写时复制”我专门纠正过MVCC 的 undo log 是顺序写而且可以 purge回收成本低而真正意义上的 COW 是块级复制旧块和旧块之后的地址都不可复用直到快照释放。你在performance_schema里看到innodb_data_writes和innodb_data_fsyncs飙升但rows_updated并不高那就要往页复制、块复制的方向想了。分清这三层语义排查就不会歪。文件系统层的 COW表现为整个磁盘空间被吃、但 MySQL 数据文件变化不大引擎层的页复制表现为.ibd文件增长、redo log 膨胀而 MVCC 引发的 undo 膨胀则是另一种问题不要混在一起。3. 触发 COW 的常见操作哪些行为在偷偷放大写入3.1 大字段更新溢出页的隐形负担先说一个最容易被业务忽略的场景更新大字段比如TEXT、BLOB、超长VARCHAR。InnoDB 对超过一定长度的字段不会全部塞在聚簇索引叶子节点里而是把大值放到溢出页off-page叶子节点里只保留 768 字节的前缀指针。问题就出在这里。当你去更新这个字段的值即使是全量替换InnoDB 也可能把原来的溢出页标记为可回收再分配一个新的页写入新值。这个过程涉及旧页的更新、新页的分配以及可能的页拆分。如果字段值很大一次更新可能带来好几个 16KB 页的写入而逻辑上你只是 update 了一条记录。我做过的性能测试里一个十多万行的表每行有一个 10KB 左右的TEXT字段做全表更新。理论写入量大概是 1GB 多实际观察到的数据文件写量在 3GB 左右加起来接近 2 到 3 倍。这还没算 redo log。也就是说如果你负责的业务表里有大字段并且更新频繁COW 效应会被极度放大。处理方案后面会讲这里先说一句大字段更新前别只看行数要按“字段最大长度 × 更新行数 × 2”来估算写量磁盘和监控都得按这个预期来配。3.2 磁盘临时表与大排序最容易忽略的 COW 源头回到开头那个场景磁盘临时表是 COW 最容易出现、也最容易被忽略的地方。MySQL 里GROUP BY、ORDER BY、DISTINCT、多表JOIN都可能产生临时表。临时表优先用内存tmp_table_size控制的是内存临时表的最大大小默认 16MB或者 1MB不同版本有差异超过这个限制之后隐式临时表引擎会转为磁盘上的 InnoDB 临时表。磁盘临时表的问题在于它不是一次写完再排序的。排序算法在归并阶段会反复读写临时文件每一轮合并都会把数据页读出来、写回去甚至复制。如果数据量是 1GB一轮合并写 1GB两轮就写 2GB再加上原来临时表的落盘最终写量轻松到 3 倍以上。我在定位 A 同学那个问题时执行过一条查询EXPLAIN里Extra列明显写着Using temporary; Using filesort。临时表已经转成磁盘表而磁盘表引擎是 InnoDB意味着 InnoDB 页复制、写放大这一整套机制在临时表上也会完整发生一遍。这里还要提醒一句MySQL 8.0 里 InnoDB 临时表有自己的表空间ibtmp1如果它一直不回缩不是 bug而是临时表频繁创建、页复制导致的增长结果。很多人问我这个文件为什么越来越大其实背后就是 COW 在折腾。3.3 在线 DDL 与表空间克隆明刀明枪的全量复制第三种触发 COW 的操作是 DDL。MySQL 的ALTER TABLE有两种算法ALGORITHMCOPY和ALGORITHMINPLACE。COPY 算法很好理解新建一张一模一样的表把原表的数据一行行复制过去完成后删旧表。这个过程中新表占据的磁盘空间和旧表几乎一样大数据文件瞬时翻倍。INPLACE 算法虽然不用复制整表但它可能要重建索引。索引重建意味着把索引页读出来、排序、再写入新的索引树期间旧索引页还在写量一样会翻倍。所以不管哪种算法大表 DDL 的隐藏前提是磁盘至少要有表大小的一倍以上空闲。如果你还开着gh-ost这类工具做在线变更它的逻辑是复制全量数据写量几乎必翻倍。此外MySQL 的 CLONE 插件CLONE LOCAL/CLONE REMOTE创建克隆表空间时如果底层文件系统支持快速克隆会有类似文件系统快照的 COW 行为克隆瞬间很快但之后任意一个实例写入数据都会触发块级复制导致两个实例的数据文件合计占用持续增长。这个场景很多人没提前想到等发现时磁盘已经爆了。3.4 不同触发场景的影响对比把常见触发场景放在一起对比更清晰触发场景逻辑写入实际写量放大倍数最容易观察到的现象大字段更新更新行数 × 字段大小3 倍左右数据文件增长 redo log 膨胀磁盘临时表排序查询结果集大小3 倍以上ibtmp1增长、磁盘告警COPY 算法 DDL原表大小2 倍新表 旧表数据目录瞬时翻倍INPLACE 算法 DDL索引大小1.5~2 倍对应索引表空间增长CLONE 克隆 写操作变更量持续增长两个实例磁盘合计异常文件系统快照备份备份期间增量增量 × 2备份卷剩余空间骤减这张表不是精确的数学公式只是一个经验区间。我见过很多同学只盯着逻辑 IO 看忽略了物理写量结果就是磁盘和性能双双出问题。记住一点MySQL 对磁盘的消耗从来不是“你改了多少”这么简单。4. 一次从“猜测”到“证据”的完整排查链路4.1 第一步按文件定位空间去向遇到磁盘告警不要一上来就rm日志。先搞清楚空间到底去哪儿了。我惯用的命令组合是df -h du -sh /var/lib/mysql/*看数据目录下每个文件和子目录的大小。如果看到ibtmp1临时表空间涨到几十 G 甚至上百 G而业务数据文件不大那基本可以确定是临时表触发了 COW 写放大。如果看到某个具体的.ibd文件在快速变大说明有高频 DML 正在让它持续扩容。还有一种情况数据目录大小看着正常但整个磁盘还是满了。这时候要查看操作系统的日志和挂载情况确认 MySQL 是不是把临时文件写到了别的分区。我遇到过一次tmpdir配置没改临时文件全写到了系统盘/tmp下数据盘倒是干净系统盘却爆了。ls -lhS /var/lib/mysql/ | head -20 ls -lhS /tmp/ | head -20按文件大小倒序排列重点看那些你印象中不该存在的超大文件。4.2 第二步找出打开中的临时文件与写入进程文件定位到了还要确认是谁在写。lsof是最直接的东西。lsof -p $(pidof mysqld) | grep -E sql|tmp|ibtmp|ibd如果你和我一样不喜欢装多余工具也可以用/proc文件系统for fd in /proc/$(pidof mysqld)/fd/*; do ls -l $fd done | grep -E deleted|tmp|ibtmp看到#sql开头的文件正在增长就说明某个 SQL 正在使用磁盘临时表。这时候回到 MySQL 里用SHOW PROCESSLIST找出正在执行的查询看看它的State是不是Copying to tmp table或者Sorting result。如果有iotop或pidstat可以同时观察块设备写入速率确认到底是 MySQL 在写还是日志清理等其他进程在搞事pidstat -d 1 5这个阶段的目标是把“嫌疑进程”锁死不要被同事口中“是不是有人在跑什么大任务”这种说法带偏。你要的是证据。4.3 第三步用状态计数器和日志锁定根因进程锁定了再进 MySQL 内部用状态变量确认临时表的情况SHOW GLOBAL STATUS LIKE Created_tmp_disk_tables; SHOW GLOBAL STATUS LIKE Created_tmp_tables; SHOW GLOBAL STATUS LIKE Innodb_rows_read; SHOW GLOBAL STATUS LIKE Innodb_data_writes;重点看Created_tmp_disk_tables和Created_tmp_tables的比值。如果磁盘临时表的创建次数高说明内存临时表频繁超过阈值COW 放大基本实锤。再配合慢查询日志或者 performance_schema 来看具体的 SQL。先确认影响行数不大但 IO 很大通常就是临时表引擎在刷盘。A 同学那个案例里慢查询日志捕获到了一条语句单次ORDER BY搬运了近千万行实际返回结果只有一百条。这种 SQL 在 MySQL 眼里就是“把千万行塞进临时表再排序”磁盘临时表加上页复制写量根本压不住。4.4 第四步验证优化效果根因找到优化完之后一定要回头验证不然等于白干。一种验证方法是对比优化前后的状态变量SHOW GLOBAL STATUS LIKE Created_tmp_disk_tables; SHOW GLOBAL STATUS LIKE Innodb_data_writes;跑同样的查询观察这两个值是否明显下降。更直观的做法是在优化前后分别记录磁盘可用空间行数和执行语句一致看磁盘消耗是否还在快速增长。A同学当时的修复就是先把那条 SQL 改成了只取必要的字段绕过大临时表然后调大了tmp_table_size。之后同样的查询Created_tmp_disk_tables不再涨了磁盘告警也消停了。5. 驯服 COW 的参数与实操策略5.1 临时表参数别让排序把磁盘拖垮临时表引发 COW 的核心原因是内存放不下所以最直接的策略是让临时表尽量留在内存里。MySQL 里控制这个行为的参数是两个tmp_table_size和max_heap_table_size。注意实际生效的上限是两者之间的较小值。[mysqld] tmp_table_size 128M max_heap_table_size 128M不过真要调大先看业务需求别无脑设几百兆。内存临时表用的朴素内存不是共享缓存太大容易把内存吃光。128MB 是我常用的一个起点如果查询结果集经常超过这个值更该从 SQL 优化或者分页上下手。另外MySQL 8.0 的 Internal Temporary Table 引擎可以配置为TempTable它比旧版MEMORY引擎支持更多字段类型也能避免一些类型兼容性问题导致的隐式磁盘临时表。可以在配置里设置[mysqld] internal_tmp_mem_storage_engine TempTable而磁盘临时表的空间也有限制MySQL 8.0 里innodb_temp_tablespaces_dir可以控制临时表空间文件位置你可以把它放到一块更充裕的独立磁盘上避免和主数据争抢 IO。5.2 在线 DDL 策略能用 INPLACE 就不要 COPYDDL 场景的 COW主要靠算法选择来控制。MySQL 5.6 以后支持ALGORITHMINPLACE但在某些操作上仍会退化到 COPY。我的经验是大表 DDL 前先查官方文档确认你要做的变更是否支持 INPLACE如果不支持再考虑用在线变更工具。在 DDL 语句里显式指定算法和锁粒度别让 MySQL 自己猜ALTER TABLE big_table ADD INDEX idx_name (column_name), ALGORITHMINPLACE, LOCKNONE;LOCKNONE允许 DDL 期间并发 DML但对 IO 的压力不会消失。数据库层面唯一能做的是把 DDL 安排在业务低峰期并且提前给磁盘留足空间。我个人的预留标准是大表 DDL 前保证数据目录可用空间两倍以上如果是 COPY 算法就是三倍以上。顺带把 binlog 的因素也算进去。DDL 期间产生的 DML 会全部写进 binlog临时表和重做日志的 IO 也会叠加。如果空间紧张可以考虑在 DDL 窗口关闭非核心表的 binlog 记录用临时变量控制sql_log_bin0但这个操作要慎重会影响增量恢复。大多数情况下我不建议为了节省空间牺牲一致性。5.3 存储与文件系统选型把 COW 用在刀刃上写时复制本身不是坏事文件系统快照、克隆、备份都是它的优点。问题出在把 COW 存储当作普通磁盘去跑繁忙的 OLTP。如果实例压力大、写入频繁而数据卷恰好用的是 ZFS、Btrfs 这类 COW 文件系统那么每一次数据页刷盘都可能多产生一次块复制。表现在延迟上就是偶尔出现几百毫秒的尖刺很多 DBA 完全查不出原因最后才发现是存储层的写放大。这不是说 COW 文件系统不能用于 MySQL。更合理的做法是把热数据放在非 COW 卷比如传统文件系统的磁盘阵列把备份、快照、归档放在 COW 卷上。这样既利用了 COW 的即时快照优势又避免了线上实例的写放大。至于日志redo log 和 binlog 的写入模式本来就是顺序追加放在非 COW 卷上也能减少额外开销建议和数据文件分盘。5.4 变更前的三件套检查最后分享一个实操习惯我在做任何可能触发 COW 的变更前固定会执行三条检查权当“三件套”第一看磁盘余量。df -h确认数据目录所在分区至少还有数据量两倍以上的空间。如果要做克隆、迁移、DDL这个数字自动上调到三倍。第二看状态计数器。SHOW GLOBAL STATUS LIKE Created_tmp_disk_tables记下一个基准值变更过程中再查一次增长量太大就说明临时表在刷盘。第三看进程现场。SHOW ENGINE INNODB STATUS里看一眼LOG部分注意Log sequence number的增长速度。如果 LSN 增长明显快于逻辑写入的速度那就是页级复制或者说写放大在起作用需要回查操作和参数。三件事都不难但每次都能帮我提前发现问题。很多 DBA 习惯等到告警来了才去查其实 COW 的写入从变更一开始就已经发生了只是前几分钟的增量还不够明显。6. 写在最后的实操习惯COW 这个概念听起来像是存储专家才会研究的底层机制但实际碰到的频率远比想象高。我这些年排查过不少“诡异”性能问题最后都归结到写时复制在背后捣鬼。它不一定是故障而是一种成本模型同样的逻辑操作物理资源消耗可能成倍上涨而这一切对上层透明。我自己现在养成了一个习惯只要遇到下列三种情况就会主动往 COW 上想第一种大表 DML 后磁盘使用率增速远超预估第二种ibtmp1异常膨胀查慢查询总能发现Using temporary第三种迁移或备份期间出现了莫名其妙的写延迟尖刺。这些场景的共同点是逻辑 IO 和物理 IO 严重不匹配而 COW 就是一种非常典型的导致不匹配的机制。文章最后补一个在实操中总结的小技巧在低峰期给performance_schema打开相关的等待事件统计观察wait/io/file/innodb/innodb_data_file的等待时间。如果一次查询中这个等待时间占比异常高同时你的 SQL 计划里存在排序或临时表那基本就是 COW 写放大在拖后腿。判断依据不需要很玄多来几次对比实验你对 COW 的理解会比任何文档都准确。
平方复杂度无法被常规优化消除｜TaoToken 统一 Key 通道实测)
