Mitigate double writing and storage overhead
Using a "memory hot storage" is actually the foundation of how most modern databases handle this, but there are also newer hardware and software architectures designed to solve these exact trade-offs.
To use several advanced architectural solutions to mitigate double writing and storage overhead. The idea of using a "memory hot storage" is actually the foundation of how most modern databases handle this, but there are also newer hardware and software architectures designed to solve these exact trade-offs. Here is how modern systems tackle these two problems:
While you can never completely eliminate writing twice if you want full durability, you can change how and where those writes happen so they don't bottleneck the system.
- In-Memory "Hot Storage" (WAL Buffering): Databases do not write directly to the disk for every single transaction. They write to an in-memory WAL Buffer first.
- The Catch: To ensure safety, this buffer must still be flushed to physical disk when a transaction commits.
- The Optimization: Databases use Group Committing. If 100 users commit at the exact same millisecond, the database combines all 100 transactions into a single, highly efficient sequential write to the disk, rather than 100 individual writes.
- Log-Structured Merge-Trees (LSM Trees): Used by databases like RocksDB and Cassandra. Instead of writing to a log and then updating a random B-Tree index on disk, they write to the WAL and a memory structure called a MemTable. When the MemTable fills up, it is simply flushed to disk as a single, immutable file. This replaces random database disk writes with sequential ones, making the "second write" incredibly fast.
- Non-Volatile Memory (NVM / PMEM): This is a hardware solution. Databases can place the WAL on ultra-fast, byte-addressable persistent memory (like storage-class memory). Writing to it is almost as fast as RAM, but the data survives a power outage.
To keep WAL files from consuming all your disk space, databases use automated lifecycle management.
- Automatic Checkpointing & Truncation: A checkpoint is a recurring event where the database forces all modified data in memory to be written to the actual database files on disk. Once a checkpoint is complete, the database knows that everything before that point is safely saved. It automatically truncates (deletes or recycles) the older WAL files, keeping the log size tightly bounded.
- Log Compaction / Log Cleansing: Instead of storing every single historical change, some systems (like Redis with AOF rewrite or Apache Kafka) periodically rewrite the log in the background. If a key was updated 1,000 times, compaction deletes the first 999 states and only keeps the final, current state in the log.
- Cloud-Native Log Offloading: In modern cloud databases (like Amazon Aurora), the database engine only writes the log stream across the network. The storage tier receives this log and generates the database pages asynchronously in the cloud background. This completely offloads the storage overhead and secondary disk writes from the primary database compute node.
When scaling a system for massive write throughput, dealing with MySQL's standard data-flushing behavior vs. designing a custom high-throughput application requires two entirely different approaches.
When scaling a system for massive write throughput, dealing with MySQL's standard data-flushing behavior vs. designing a custom high-throughput application requires two entirely different approaches. Here is how you solve the double-writing and storage overhead problems across both systems.
MySQL uses two types of double writing: the Redo Log (WAL) and the Doublewrite Buffer (DWB). In standard setups, MySQL writes your data three times: once to the Redo Log, once to the DWB (to prevent partial-page corruption), and once to the final tablespace. For high-throughput workloads, apply these structural configuration tweaks:
- Leverage Hardware Atomic Writes: If your database runs on cloud environments with atomic write support (such as AWS RDS Optimized Writes and instance-scheduler-on-aws or physical Fusion-io NVMe drives), MySQL can safely turn off its Doublewrite Buffer. The hardware itself guarantees that a 16KB database page cannot be partially written, cutting your data-file write overhead roughly in half.
- Loosen the Flush Policy (Trade Safety for Speed): By default, MySQL flushes the log to disk on every single transaction commit (innodb_flush_log_at_trx_commit = 1).
- Change this to 2. This tells MySQL to write logs to the OS cache every commit, but flush to physical disk only once per second. You achieve a massive boost in write throughput at the cost of losing up to 1 second of transactions only if the physical OS/hardware crashes (safe from a standard mysqld process crash).
- Use O_DIRECT Bypass: Set innodb_flush_method = O_DIRECT. This completely bypasses the OS page cache for data file writes, eliminating double-caching in memory and freeing up CPU overhead during massive write spikes.
- Drastically Increase Redo Capacity: If your WAL capacity is too small, MySQL forces aggressive checkpoints, slowing down your app. Set innodb_redo_log_capacity (MySQL 8.0.30+) or innodb_log_file_size to hold roughly 60 to 90 minutes of peak write traffic. This stretches out the duration between flushes, giving MySQL plenty of time to recycle log blocks smoothly.
- Increase the Log Buffer: Set innodb_log_buffer_size to a higher value (e.g., 64MB or 128MB). This gives heavy transactions a massive chunk of RAM to store changes before MySQL has to write them out to disk.
If you are building your own high-throughput application or streaming engine, you are not bound by relational constraints. You can completely sidestep traditional double writing by choosing data structures designed for memory-first ingestion.
- Implement an LSM-Tree Architecture: Instead of using B-Trees (which update files in place and require a WAL), structure your application's storage like RocksDB or Apache Cassandra.
- Incoming writes are written once to an append-only WAL, and immediately written to a MemTable (RAM).
- When the MemTable fills up, it is sequentially dumped onto disk as an immutable SSTable file. There is no traditional "in-place update double-writing."
[Incoming Write] ───┬───> Write 1: Append-Only WAL (Disk)
└───> Write 2: MemTable (RAM) ───[When Full]───> Immutable File (Disk)
- Group Committing & Vectorized I/O: Rather than writing to the log on every network request, create a memory buffer queue. Let your worker threads gather writes over a short window (e.g., 5ms or 1,000 requests) and execute a single batch write using writev() system calls. This heavily reduces disk context switching.
- Log Compaction (State Collapsing): If your custom application tracks rapid state updates (like tracking a user's changing GPS location or account balance), implement Log Compaction. Periodically run a background thread to look at old log files and merge records. If User A has 500 location updates in the log, erase the first 499 updates and only keep the last known position.
- Log Ring Buffers (Fixed Ring Allocation): Instead of letting log files grow infinitely, allocate a fixed-size set of pre-allocated files on disk (e.g., ten 1GB files) forming a ring. Once File 10 fills up, the application wraps back around to overwrite File 1, provided the data from File 1 has already been cleanly indexed and consolidated into your application's permanent analytical files.
To refine this further:
- For MySQL, what is your current server memory size?
- For your custom app, what type of data are you storing (e.g., time-series metrics, financial ledger events, or key-value states)?
For example our server has 64GB of RAM, this production configuration optimizes memory allocation to give MySQL maximum runway while leaving enough overhead for the Operating System and active connection buffers.
To eliminate the traditional database bottleneck of double-writing (writing a WAL and searching/updating a B-Tree structure on disk simultaneously), high-throughput engines use an LSM-Tree (Log-Structured Merge-Tree) approach. The chart below shows how data transitions through memory structures immediately, allowing you to decouple client responses from heavy disk writes:
Go is an exceptional choice for this because its native channels act as highly optimized, thread-safe memory ring buffers. This runnable pattern handles high-speed write ingestion, buffers incoming payloads in memory, and writes them to disk in sequential chunks:
- Never use In-Place File Updates: If a value changes, append the new value to the log with a newer timestamp. Do not search through files to update old data.
- Handle Storage Overhead with Compaction: As your memTable hits its capacity target (e.g., 512MB), spawn a background goroutine to snapshot it to an immutable file structure on disk, clear out old versions of matching keys, and cycle out old WAL log segments.
If you would like to expand either system further, let me know:
- For MySQL, are you running on a cloud provider like AWS EC2/RDS or on-premises hardware?
- For Go, what is the approximate write load you are planning for (e.g., 10,000 or 100,000+ operations per second)?