Database Performance

2 posts

kakaoOriginal article

12 Reasons to Upgrade to MongoDB (opens in new tab)

MongoDB 8.0 marks a significant shift in the database's evolution, moving away from simple feature expansion to prioritize architectural stability and substantial performance gains. By addressing historical criticisms regarding write latency and query overhead, this release establishes a robust foundation for enterprise-scale applications requiring high throughput and long-term reliability. ### Extended Support and Release Strategy * MongoDB 8.0 is designated for five years of support (until October 2029), offering a stable "LTS-like" window that reduces the resource burden of frequent major upgrades. * The "Rapid Release" policy, previously exclusive to MongoDB Atlas, now extends to on-premise environments, allowing self-managed users to access minor release features and improvements more quickly. * This policy change provides DBAs with greater strategic flexibility to choose between prioritizing stability or adopting new features. ### Optimized "Majority" Write Concern * The criteria for "majority" write acknowledgment has shifted from `lastApplied` (when data is written to the data file) to `lastWritten` (when the entry is recorded in the `oplog.rs` collection). * This change bypasses the wait time for secondary nodes to physically apply changes to their storage engines, resulting in a 30–47% improvement in write throughput. * While this improves speed, applications that read from secondaries immediately after a write may need to implement Causally Consistent Sessions to ensure they see the most recent data. ### Efficient Bulk Operations * A new database-level `bulkWrite` command allows for operations across multiple collections within a single request, reducing network round-trip costs. * The system now groups multiple document inserts (up to a default of 500) into a single oplog entry instead of creating individual entries for every document. * This grouping aligns the oplog process with the WiredTiger storage engine’s internal batching, significantly reducing replication lag and improving overall write efficiency. ### High-Speed Indexing with Express Plan * MongoDB 8.0 introduces the "Express Plan" to optimize high-frequency, simple queries by bypassing the traditional multi-stage query optimizer. * Queries are eligible for this fast-track execution if they are point queries on the `_id` field or equality searches on fields with unique indexes (or queries using `limit: 1`). * By skipping the overhead of query parsing, normalization, and plan stage construction, the Express Plan maximizes CPU efficiency for the most common database interaction patterns. For organizations managing large-scale production environments, MongoDB 8.0 is a highly recommended upgrade. The combination of a five-year support lifecycle and fundamental improvements to replication and query execution makes it the most performant and operationally sound version of the database to date.

figma3 min readCurated summary

Postmortem: Service disruption on January 21-22, 2020 | Figma Blog

Figma’s January 21–22, 2020 outages were caused by separate database failures that compounded one another. A long-running query triggered the first incident and created a vacuuming backlog; the next day, PostgreSQL 9 produced a severely mis-planned query after database statistics changed, while aggressive autovacuuming increased write and lock pressure. Upgrading to PostgreSQL 11 restored stability and addressed both the query-planning and autovacuum performance issues. ## Incident Timeline ### January 21: Long-Running Query - Automated alerts reported elevated error rates at 6:11 AM PST. - Engineers found an expensive, long-running database query driving CPU usage. - Canceling the query at 6:54 AM restored normal performance. - The terminated query left behind a backlog of data requiring vacuuming. ### January 22: Database Saturation - Increased write IOPS and lock contention appeared, despite database CPU being below normal. - Queued API requests eventually made Figma unavailable to some users. - Engineers canceled nonessential queries and increased allocated IOPS, providing only temporary relief. - Performance deteriorated again in the afternoon. - Restarting the database temporarily disabled a suspected background process. - Figma performed an emergency upgrade from PostgreSQL 9 to PostgreSQL 11. - The service returned online at 8:15 PM, with metrics back to normal. ## Aggressive Autovacuuming - The vacuuming backlog crossed the threshold for PostgreSQL’s more aggressive transaction-ID wraparound protection. - This mode generated substantial locking and write activity, particularly in the PostgreSQL version Figma was using. - Canceling autovacuum operations on large tables temporarily improved metrics, but the operations resumed. - Fully suppressing the aggressive behavior required changing `autovacuum_freeze_max_age` and rebooting the database. - Autovacuum was a significant contributor, but disabling it did not eliminate all performance problems. ## PostgreSQL Query Planner Failure - A complex query repeatedly appeared in lock-contention reports. - PostgreSQL estimated that the query would return more than 20 million rows, while the actual result contained only three. - The incorrect plan used full table scans instead of expected indexes. - It also wrote large amounts of data to temporary buffers, matching the observed increases in write IOPS and temporary-byte metrics. - The issue was likely caused by inaccurate statistics or a PostgreSQL planner defect or limitation following a routine statistics change. ## Upgrade and Preventive Measures - PostgreSQL 11 generated a substantially better plan for the problematic query. - Newer PostgreSQL versions improve autovacuum performance and query planning. - PostgreSQL 10+ also provides more advanced performance-analysis tools through Amazon RDS. - Figma had already tested the PostgreSQL 11 upgrade in staging and prepared a detailed production rollout plan, allowing the emergency upgrade to succeed safely. - The company planned to improve monitoring for expensive queries and impose stricter limits on query execution time. Figma concluded that upgrading PostgreSQL, improving query monitoring, and enforcing tighter runtime limits were necessary to prevent similar database-driven outages.

Read original(opens in new tab)