Write Amplification Calculator

If you purchased an enterprise SSD because of its high capacity and blazingly fast reads, there’s a good chance you’re overlooking the metric that reveals how many years your SSD will last under load of your workload. This metric is called write amplification. It is the ratio of physical bytes written to the drive versus the logical bytes your database thinks it sent. It is expressed as the ratio between the logical bytes that your database believes it has sent to the drive, compared to the number of physical bytes that actualy get written on the drive.

For example, if your app sends a single gigabyte of data but the drive winds up sending five then you have a write amplification factor of five. The difference is where endurance die. Use the calculator at the top. Plug in your system’s daily load and type of engine it runs. It will do all the math for you, so you don’t have to guess how much overhead your storage stack adds.

What Is Write Amplification?

Naturaly, most engineers considers only the logical writes; it’s the physical writes that wear out that NAND. Between your silicon and your SQL query is a series of layers of indirection, each adding weight to final count. Let’s begin with the database engine itself. In MySQL or PostgreSQL, for example, the system use a B-tree structure. An update causes indexes to be rebalanced, which leads to page splits that rewrite blocks of unchanged data. LSM-based engines such as Cassandra or RocksDB append your writes and compact them out eventually. They shift the extra work from random I/O during writes to a background merge process. So both mechanisms results in overhead, but at different times and in different ways. And that’s what most folks forget when benchmarking storage engine.

Oh yeah, then there’s the journaling layer. You write ahead some logs called write-ahead logs (WAL). Other people call them binlogs, and others might call them redo logs. I won’t get into that mess here. To put it simply, these things record updates before committing those update to the main table. You’re effectively writing the same data twice. You are writing to both the tablespace and to the log. On top of everything else, if you have a high WAL factor, then you’re effectively doubling your physical write load. Compression will help lessen that to an extent because it makes what you write smaller, but it can never completely remove the underlying duplication caused by need for durability.

Another big amplifier is secondary indexes. Every one of them is its own data structure which needs to be kept up-to-date with main table. What happens when you update or insert into a high churn table? Five spots on disk get touched if you have four secondary index. Reduce your amplification by dropping unused indexes; this is pretty much always the first thing you do. Getting rid of an index you might want some day sounds scary, but maintaining it mean wearing out your hardware and slowing down your queries every day. How fast do you want your query? vs. How fast do you want your hardware to run?

The flash translation layer add its own tax. Because an SSD can’t overwrite cells in-place, it has to erase whole blocks then write new data into them. If there isn’t much free space, the drive will have to move other valid data around to create some free space to accommodate new writes. That garbage collection process increase the write amplification even more. Keeping some amount of free space available allows the drive to work more efficiently and also helps data re-arranging.

This provides a breakdown based off what is contributing to this and allows you to see where your largest leak is. Is it too many indexes? Is it your aggressive compaction settings? Or is it that your SSD just isn’t up to snuff for the workload? Being able to understand the cause of the amplification means being able to fix the correct problem rather than just throwing more drives at it. You can adjust your replication strategy, change the storage engine parameters, or tune the database itself.

The bottom line: Write amplification is a life-vs.-speed-and-safety tradeoff. Turning off index(es) or journal(s) will lower the amplification factor, but slow down queries and/or increase risk of losing data. It’s about controlling write amplification, not removing all overhead. Do so only as far as your hardware allows. Monitor the wear rate daily. When you’re outlasting endurance, examine the breakdown. A minor configuration adjustment may show you have a large surplus in physical writes.