Scenario: the database is overloaded
15 investigations into a database at its limit: repeated reads, missing or excess indexes, replication lag, hot spots, locks, pagination, cache stampedes. For each, the cause then the typical solution, with the alternative and the pitfall.
- 37 cards
- 3 topics
- Free
The outline, topic by topic
Each topic is weighted the way the exam weighs it.
- Reads
- Writes
- Operations
A few cards from the deck
The database is at 100%, always for the same products
- Database CPU at 100%
- 95% of queries are reads
- The top 100 products account for 80% of reads
What is the most likely cause?- Application servers are missing
- The database keeps rereading the same data
- Writes overload the database
- The database disk is full
Show the answer
The database keeps rereading the same data
Reads dominate and focus on little data: they do not need to reach the database every time.
The database is at 100%, always for the same products
Cause: the database keeps rereading the same data.
What is the typical solution?- A cache in front of the database: read the cache first, else the database, then fill the cache (cache-aside)
- Add application servers
- Partition the database by date
- Add a CDN for images
Show the answer
A cache in front of the database: read the cache first, else the database, then fill the cache (cache-aside)
The most requested data is served from memory; the database only sees a small share of reads.
Alternative: Read replicas sharing the reads.
Pitfall: When a product changes, its cache entry must be invalidated, or a stale price is shown.“My orders” takes 6 seconds
- The orders table has 10 million rows
- The query filters on the customer id
- The query plan shows a full table scan
What is the most likely cause?- The database is short of memory
- The network is saturated
- An index is missing on the filtered column
- Too many customers are connected
Show the answer
An index is missing on the filtered column
Without an index, the database reads all 10 million rows to find a few.