RocksDB write_buffer_size and max_write_buffer_number tuning under a fixed memory budget

Answer

Under a fixed RAM budget, allocate a target fraction of the process memory to memtables, then derive write_buffer_size and max_write_buffer_number from that budget and the number of column families. When many column families share a DB instance, a shared WriteBufferManager with db_write_buffer_size is usually safer because it enforces a global cap and prevents the per‑CF multiplication that can exceed the budget.

Confirmed facts

• Total memtable memory ≈ write_buffer_size × max_write_buffer_number × column_family_count when each column family uses its own buffers.
• If a WriteBufferManager is enabled, the memtable footprint equals db_write_buffer_size regardless of column family count.
• RocksDB defaults: write_buffer_size = 64 MiB, max_write_buffer_number = 2 per column family.

Likely explanation

Increasing write_buffer_size lets each memtable grow larger before a flush, which reduces flush frequency and write amplification but raises the peak memory that may be resident simultaneously. Raising max_write_buffer_number allows more memtables to stay alive before a forced flush, smoothing bursty writes but also increasing the worst‑case memtable footprint. The stall you observed occurs when the limit on the number of live memtables is reached while new writes keep arriving.

Sizing steps

1. Decide what fraction f of the total process RAM you want to reserve for memtables (a common starting point is 0.30–0.40).
2. Compute the memtable budget: memtable_budget = f × total_RAM.
3. Choose a max_write_buffer_number that matches your write‑burst tolerance (2 for steady load, 3–4 for bursty workloads).
4. If you know the column family count N, calculate per‑CF buffer size: write_buffer_size = memtable_budget / (max_write_buffer_number × N).
5. Round the result to a convenient power‑of‑two multiple (e.g., 64 MiB, 128 MiB) and restart the DB to apply the new values.

When many column families are present

If N is large or variable, the per‑CF calculation can lead to very small buffers or, conversely, to an OOM risk if the product write_buffer_size × max_write_buffer_number × N exceeds the budget. In that case enable a WriteBufferManager and set db_write_buffer_size to the memtable budget from step 2. This caps total memtable memory globally, letting each column family draw from the shared pool as needed.

Could you confirm the exact number of column families (or an upper bound) so the per‑CF buffer size can be calculated?