Concurrency vs. Throughput: why more parallelism can make databases slower

This post details a critical incident where a production MySQL database experienced a 16-minute meltdown due to excessive concurrency and a lack of effective backpressure management. It explains how a long-running transaction, combined with a high number of concurrent read queries, overwhelmed the storage engine by forcing it to reconstruct increasingly long version histories for each read. The post contrasts the behavior of thread-pool-based connection management (Cloud SQL) with transaction-pool-based management (Vitess) and highlights how the migration to PlanetScale exacerbated the issue due to application stack unfamiliarity with pool-full errors. The solution involved reducing the Vitess transaction pool size to mimic the old thread pool's behavior and implementing a queuing mechanism with a longer timeout for requests arriving at a full pool. This change successfully absorbed high transaction volumes without performance degradation or errors, demonstrating the effectiveness of controlled backpressure in preventing system instability.