Databases · Database tuning under live load

🛒 How to play Query Quest

You are the DBAs of a UAE online store, and White Friday is about to hit your database.

Join a game Run it as facilitator Print this guide

Your goal

Serve the most good queries per second over three rounds. Build the indexes your busiest queries need, keep writes cheap when checkouts surge, stay inside the AED 12k budget, keep stock correct with the right isolation level, and recover quickly from each round's failure card.

Your team runs one store database on a 9-core server (9,000 ms of CPU per second). Every team faces the same workload each round. A query-cost model turns your design into CPU load, and the load into queries answered on time.

Join in 30 seconds

Scan the QR code on the big screen with your phone camera, or open the game page and type the 4-letter code.
Type your first name. No app, no account.
Join a team or create one and name it. Your teammates get a role each. In individual mode you run your own database team.
If your phone locks or the page reloads, open it again: you return to the same seat.

Team roles

Lead: Chairs the team and submits the final decision. If the Lead's phone dies, any teammate can take over. In this game: Own the index list: match each index to a busy query, and drop indexes nobody reads.
Analyst: Owns the numbers and the team's prediction for the round. In this game: Multiply each query's rate by its cost to find the query that eats most CPU.
Treasurer: Watches the budget and costs before the team commits. In this game: Keep cache, indexes and backups inside AED 12k: every AED 1k over costs 150 QPS.
Risk and ethics officer: Flags harms and pushes back before the team commits. In this game: Choose the isolation level and backup plan: oversold stock and lost data are your problem.
Voice: Talks to other teams and presents the team's story at the debrief. In this game: Explain to the room which query was your bottleneck and why your index choice fixed it.

The three rounds

Each round you predict your own result, make your decisions within about 8 minutes, and then the model reveals what happened. An event card lands between rounds.

1
Round 1 · Browsing season
About 6,000 reads per second and only about 180 writes. The failure card is a disk failure on the primary.
Watch for: Your most expensive query, CPU utilisation, and how long the disk failure kept you down.
2
Round 2 · White Friday
Checkouts and stock updates jump to 700 a second each and 30% of stock writes hit the same hot deals. The failure card is a bad script that zeroes prices.
Watch for: Checkout cost per index on orders, the cache hit rate falling, and oversold orders under your isolation level.
3
Round 3 · Reports during the sale
The orders table reaches 2.8 million rows and managers refresh a sales report by category 40 times a second. The failure card is a primary crash at peak.
Watch for: Whether the report join became your biggest CPU eater, and how a materialised summary or a date index changed it.

Your decisions

What each control on your phone does, and how to think about it.

Normalisation level	Every round	Normalised (3NF): product pages and reports need joins, writes are cheap. Denormalised: product pages cost 0.2 ms with no reviews index, but checkouts cost 1.5×, reviews +1 ms and price updates 4×. 3NF + materialised summary: the report costs 1 ms, each checkout +0.6 ms. Denormalising frees an index slot in Round 1; it hurts on White Friday. The materialised summary pays off when the report runs 40 times a second.
Indexes to build	Every round	Up to 4 of 7 indexes, AED 0.3k each. With an index a read costs 0.15 ms + rows matched × row cost; without it, a full scan at 0.00004 ms per row. Each index adds 1.0 ms to every insert or update on its table. Multiply rate × cost for each query. The reviews and customer-history scans run 40–112 ms each without an index, so they dominate. Indexes on orders slow every checkout.
Cache size	Every round	0 to 64 GB at AED 0.12k per GB. Hit rate rises with cache ÷ (cache + half the working set) and falls as the write share grows, because writes invalidate cached pages. A hit costs 0.05 ms. Cache helps most in the read-heavy Round 1. On White Friday writes invalidate it, so a big cache buys less and eats budget.
Isolation level	Every round	Read uncommitted (writes ×1.0, most oversold), read committed (×1.05), repeatable read (×1.2, rare anomalies) or serializable (×1.4, no anomalies, but hot writes abort and retry). Oversold orders are refunded and cut trust. Under heavy contention on hot deals, read committed oversells about 189 orders in Round 2. Repeatable read is a good middle; serializable costs CPU.
Backup and replication	Every round	No backup (free), nightly backup (AED 0.8k), hot replica (AED 3.5k) or replica + point-in-time recovery (AED 4.5k). Replicas add up to 3,000 ms/s of read capacity and fail over in minutes. Only point-in-time recovery undoes a bad change. A replica copies mistakes instantly, so it is not a backup. Compare the extra read capacity with the cost before the White Friday rush.

What you see on your phone

This round: Reads and writes per second, the size of the orders table, the share of stock writes that hit the same hot deals, server CPU and the AED 12k budget.
Workload this round: Every query class with its rate per second and the index that serves it. Writes show which table they change, so you can see which indexes they must update.
Code box: Round 1 shows the lookup cost function, Round 2 the read-then-write checkout code that can oversell, Round 3 the sales report join.
Your database last round: Good queries per second, CPU utilisation, cache hit rate, your most expensive query, oversold orders and cost.
Heads up: Appears when an event changes this round: disk failure, price bug, doubled traffic, a flash deal, the new status dashboard or the audit rules.

How you are scored

Every game in the library uses the same scoreboard. Your round result is your Good queries per second (QPS); the leaderboard adds resilience and trust.

Score = 100 × (0.5 × performance + 0.25 × resilience + 0.25 × trust) + prediction points

Performance: Good queries per secondGood queries per second: queries served × the share answered within the service level (near 100% below 75% CPU, falling fast above 85%), minus one per oversold order and 150 for every AED 1k over the AED 12k budget.

Resilience (0–100)Recovery moves halfway each round toward the failure card's target. Disk failure: none 5, nightly 40, replica 85, PITR 88. Bad price script: none 10, nightly 45, replica 20, PITR 92 (8 less if denormalised). Primary crash: none 30, nightly 40, replica or PITR 90.

Trust (0–100)Data correctness moves halfway toward 95 minus 0.15 per oversold order (up to 55), minus drifting price copies in a denormalised schema, minus price-bug damage, and after the audit minus 10 for denormalised personal data and 6 for no backup.

Performance is your total Good queries per second over the rounds, scaled from the lowest to the highest in the room. Before each round you also predict “Your good queries per second this round”: land close and earn up to 10 bonus points; miss by the whole amount and earn none. The facilitator may change the weights before the game starts.

Tips

Read the workload table as rate × cost. A query that runs 2,000 times a second needs an index far more than one that runs twice a second.
Only four index slots: choose the ones that remove the biggest full scans, then check what each costs your writes.
On White Friday the orders table is write-hot. Every extra index on orders adds 1.0 ms to 700 checkouts a second.
Keep CPU below about 75%. Above 85% queueing delays push many queries past the service level.
Repeatable read is the usual sweet spot under contention: few oversold orders without serializable's retries.
Plan backups for the failure cards: a replica handles crashes, but only point-in-time recovery rewinds a bad change.

Common mistakes

Leaving the index list empty or picking indexes nobody reads. Without them the server needs about ten times its CPU.
Buying a 64 GB cache for White Friday. Writes invalidate it and the extra AED pushes you over budget.
Treating a replica as a backup. When the price bug hits, the replica holds the same zeroed prices.
Ignoring isolation because it does not change speed much. Oversold orders cost refunds and trust.

A worked example

In Round 1 a team chooses 3NF, indexes on category, name, reviews(product_id) and customer_id, an 8 GB cache, read committed and a nightly backup. Cost is AED 8.96k. CPU runs at 52%, so it serves 6,078 of 6,082 queries a second; the biggest CPU eater is sort by price at 8 ms per scan. 3 orders oversell and the disk failure takes 180 minutes to restore. A team with no indexes needs 1,011% of its CPU and serves only 317 queries a second.

Shocks that can hit

The facilitator draws one event card before rounds 2 and 3. It changes the market from that round on.

Disk fails on the primary. A storage disk on the main database server dies next round. Without a replica you are down while you restore: a nightly backup costs about 6% of the round, no backup about 25%.
Unindexed reporting query. Operations adds a dashboard that lists pending orders by status, 60 times a second, from next round to the end. Without an index on orders(status) it scans the whole orders table.
Price-update bug. A release next round contains a bug that sets some prices to zero for 20 minutes. Point-in-time recovery rewinds cleanly. A replica copies the bug instantly; a nightly restore loses the day's orders.
Data-protection audit. A regulator audits how customer data is stored. From next round, denormalised tables that copy personal data into many places cost trust, running with no backup costs trust, and encrypted backups add AED 0.5k a round.
Traffic doubles. A viral video sends shoppers to the store. Next round reads double and writes rise 50%.
Flash deal on one phone. Marketing launches a one-hour deal on a single phone model next round. 50% more stock writes hit the same hot rows.

The model behind the game

Query-cost model with index selectivity and write amplification. An index turns a full table scan into a short B-tree lookup, but every insert or update on that table must also update the index. The server has a fixed CPU budget: when utilisation passes 75%, response times break the service level and good queries fall.

read cost = 0.15 + rows matched × row cost with an index, else 0.00004 ms × table rows ; write cost = base + 1.0 ms × indexes on that table ; ρ = Σ λ·cost ÷ 9,000 ms/s ; good QPS = served × SLA(ρ) − oversold − 150 × AED k over budget

Index: A B-tree on a column that finds matching rows without reading the whole table.
Full table scan: Reading every row to answer a query. Here 0.00004 ms per row, so 80 ms for 2 million orders.
Write amplification: Extra work per write: each index on a table must also be updated on every insert or update.
Normalisation (3NF): Storing each fact once, linked by keys. Safe, cheap writes; reads need joins.
Materialised view: A stored, precomputed query result, here a sales summary refreshed on each checkout.
Isolation level: How strictly concurrent transactions are kept apart, from read uncommitted to serializable.
Lost update: Two transactions read the same value and both write, so one change is lost, for example stock sold twice.
Cache hit rate: Share of reads answered from memory instead of the database. Writes invalidate cached data.
RTO and RPO: Recovery time objective (how long you are down) and recovery point objective (how much data you lose).

For facilitators 3 rounds · about 50 minutes

Set-up

Team mode with 3–5 per team works best; individual mode also works because there are only five fields.
Allow 8 minutes per round; Round 1 may need 10 while teams read the workload table.
Bots fill to 3 databases. They pick sensible but imperfect indexes and mix isolation and backup choices.
Before Round 1, write cost = rate × ms per query on the board and work one row of the workload table together.

Modes

Team: Lead owns the indexes, Analyst sets the schema, Treasurer sizes the cache against the budget, Risk picks isolation and backups, Voice explains the bottleneck query.
Individual: Each student runs a whole database. Good for checking that everyone can read rate × cost from the workload table.
Hybrid: Each student runs a database and the team score is the average. Ask teammates to compare index lists before submitting.

Run sheet

Before class	Open the facilitator console on the projector laptop, choose the mode in Setup, and check the QR shows.
0–5 min	Students scan and join. Read the situation aloud while teams form. Point out the roles on their phones.
5–15 min	Round 1: 8 minutes to decide and predict, then Reveal. Read the headline and one surprising line from a team.
Between rounds	Draw or choose the event card. Give teams 30 seconds to read it before opening the next round.
15–25 min	Round 2, then Reveal.
27–37 min	Round 3, then Final results: podium, awards and the score chart.
Last 12 min	Debrief with the questions below, then open reflections (4 minutes). Download the CSV.

What usually happens

Teams with an empty or random index list in Round 1. Their CPU demand runs far above 100%.
Teams keeping a date or status index on orders through White Friday. Point to their checkout cost line.
Read uncommitted chosen for speed. Show the oversold orders in the room table.
Replicas bought as backups. The Round 2 failure card shows that replicas copy the price bug.

Debrief questions

Which query was your most expensive in Round 1, and which index fixed it? What did that same index cost you on White Friday?
Did any team oversell stock? Walk through the read-then-write code and show where two checkouts can both see the last item.
A replica protected you from the disk failure but not from the price bug. Why is a replica not a backup?

Follow-up probes

Which single index removed the most CPU for you, and how did you work it out?
Why did the cache hit rate fall in Round 2 even though your cache was the same size?
Walk through the checkout code: at which line can two buyers both see the last phone?
When is a denormalised schema worth its write cost?
What is the difference between a replica and a backup, in one sentence?
If the report ran 400 times a second, what would you change?

Reflection and evidence

Write a short design note for the store's CTO: which schema, indexes, cache size, isolation level and backup plan you would run during next year's White Friday, and why. Use numbers from your rounds: utilisation, cache hit rate, oversold orders and recovery time.

Students write 80–120 words on their phones. The Data tab exports every decision, result, prediction and reflection as one CSV for grading.

Console controls

Pause, extend or set the timer; step back a round
Draw, choose or write the event card
Move players, make a Lead, add computer players
Award or fine with a reason that appears on phones
Message every screen; hide names; spotlight a team
Keys: P projector mode, Q join QR