MYSQL的写放大问题一探

摘要：

传统数据库的写放大问题是怎么回事？它会带来哪些问题？应该如何规避写放大问题，本文将初步探索这个问题。

一、MYSQL的写放大问题

传统数据库的写放大问题

首先什么是写放大问题？写放大（Write Amplification）这个概念其实在很多系统（特别是存储）都存在。但写放大其实分为很多种，各种的写放大原理并不是很一样。所以首先要开宗明义的是，对于传统数据库而言，写放大问题的定义是什么？

对于传统数据库的写放大问题，其实是指在使用传统关系型数据库（如MySQL、PG、Oracle等）时，实际写入到数据库中的数据量大于应用程序实际写入的数据量。

MYSQL写放大的一个实际案例

在一次性能压测中，对一个部署在SSD盘上的MYSQL进行批量写入压测。发现SSD磁盘的监控指标数据已然打满（IOSTAT采集的数据表明，磁盘UTIL达到100%，写入带宽达到185MB/S，在之前的FIO测试中，这已经是当前SSD盘写入带宽的上限）。但实际的应用程序写入数据，仅为25MB/S（可从网络带宽和应用数据写入侧双重确认）。

换言之，应用数据写入之所以在25MB/S上达到上限，卡点在于后端磁盘的写入带宽为185MB，而这里的写放大系数达到6倍以上，令人意外。

MYSQL写放大的原理分析

写放大系数6以上，表示应用写入1MB的数据，后端数据库居然往磁盘写入6MB的数据？这是怎么回事？在这里引用一下AWS的Aurora数据库的论文《Amazon Aurora: Design Considerations for High Throughput Cloud-Native Relational Databases》中的内容来解析这个问题：

• 首先分析DATA。DATA就是应用写入的原始数据，存放在DATABLOCK之中，这点是大家可以直接理解的。但即使是DATA，也会有写放大的问题。WHY? 回忆一下INDEX的相关知识，MYSQL是索引组织表，如果这个表中的索引比较多，自然会有多份数据写入。而且索引 本身是B+树，如果写入并非是顺序写入，B+树一旦分裂，同样也会占用高于原始数据量的磁盘空间。这也是为什么对于一个运行久了的MYSQL，执行OPTIMIZE TABLE命令能达到磁盘空间优化的原因：B+树的空洞被填补了。
• LOG。MYSQL是支持ACID的。但如何做到DURABLE持久性。靠的是WAL(WRITE AHEAD LOG技术)，先写LOG，再写DATA。所以写一份数据之前，要把这个变更在REDO LOG中先写一份。
• BINLOG。如果你的MYSQL要做主从复制（防止单点问题），就要开启BINLOG作为主从复制的基础媒介。
• DOUBLE-WRITE，防止的是PARTIAL WRITE问题，否则虽然有REDO LOG，写入依然有可能会CORRUPTION。MySQL Innodb block块为16K，文件系统为4K，传统硬盘512字节，16K写入需要保证原子性，而在线日志无法恢复断页情况。MySQL通过Doublewrite双写先写一份到共享表空间，然后写入数据文件，恢复过程需要使用完整的16K恢复，但会带来IO两倍写入和性能影响。

二、如何规避写放大的影响

上文已经从原理上对写放大进行了分析。但这种理论分析对实际生产有什么指导意义呢？如果仅是理论，对生产实践没有指导意义，无异于纸上谈兵。

实际上，写入大带来的负面影响是非常明显的。对磁盘的空间占用，对性能的影响。AWS的Aurora正是基于减少写放大来提高数据库的性能。作为MYSQL的使用者，应该如何规避写放大的影响。

从DATA想办法，DATA本身是绝不能省的，但可以创建自增主键，使写入都是顺序写入，写入达到高性能，同时减少B+树的空洞。同时如果是数据迁移，可以先迁移数据，再创建索引。

从LOG想办法，基本没得优化。ACID中的D太重要了。

从BINLOG想办法，如果是数据迁移，而又不需要主从复制，可以暂时关闭BINLOG，等迁移完全量数据再开启BINLOG。

从DOUBLE-WRITE想办法。对于某些存储介质而言（NVMe SSD），会在物理层面保证一个页的写入的原子性，所以可以直接在MYSQL配置参数中关闭DOUBLEWRITE：设置innodb_doublewrite = 0

实际应用案例：

在一次数据迁移中，临时关闭了BINLOG和DOUBLE-WRITE，整体迁移性能上升一倍。