Why Is MySQL Insert So Slow? Uncovering Cache, BBU, and Storage Bottlenecks

Problem Background

In MySQL, simple INSERT statements can take unexpectedly long even for a single row. The slowdown is often caused by storage write‑path issues.

1. Storage Architecture

MySQL reads data from memory → cache → disk and writes data first to the cache (volatile memory) and later flushes it to disk. The cache speed dominates I/O performance.

Cache characteristics : extremely fast, loses data on power loss, limited capacity (typically 4‑32 GB).

Battery Backup Unit (BBU) : provides power to the RAID controller’s cache during power loss, allowing cached data to be written to disk. If the BBU fails, writes fall back to direct disk writes, which are orders of magnitude slower.

Cache capacity management : when the cache fills, the system periodically flushes data to disk to avoid saturation.

2. Diagnosing Slow Write Performance

When the write path becomes a bottleneck, the whole database may appear to hang. The following steps help identify the root cause.

Check BBU status : verify that the BBU is present and healthy; restart or replace a faulty BBU.

Determine whether the cache is full : iostat -x and SHOW GLOBAL STATUS LIKE 'Handler_write'; show write I/O and handler counts.

Measure cache‑to‑disk flush rate : high disk I/O or heavy load can make the flush rate far lower than the write rate. Use SHOW STATUS LIKE 'Innodb_buffer_pool_reads'; to see how often pages are read from disk because they were not in the buffer pool.

Inspect storage hardware : replace disks or RAID controllers that exhibit high latency or error rates.

Consider disaster‑recovery synchronization : replication lag or synchronous copy can add additional write latency.

3. Battery Backup Units (BBU)

A BBU supplies power to the RAID controller’s cache during an outage, giving the cache enough time to write its contents to persistent storage. Without a functional BBU, cached writes are forced directly to disk, causing a dramatic slowdown (often a factor of 10,000). Some systems use super‑capacitors instead of batteries; super‑capacitors retain charge longer and do not degrade as quickly, providing more reliable protection.