DBA: Performance Tuning at Scale

Took the Project 1 schema to a million rows, found a slow query, fixed it with one composite index, measured the gain two ways, and proved the backups restore. Everything was run on the live MySQL server, in its own database so the real tables stayed untouched.

Tools

Tool	Used for
MySQL 8.4	Test database project3_perf (same schema as Project 1)
EXPLAIN / EXPLAIN ANALYZE	Reading the query plan and its actual execution time
Stored procedure + NOW(6)	An in-session benchmark loop
mysqldump, gzip, docker exec -i	Backup and restore drill

Steps

1. Clone the schema and load 1,000,000 rows

What it does: copies Project 1's tables with CREATE TABLE … LIKE, copies the real rows, then generates a million synthetic jobs in one set-based statement. A six-way cross join of the digits 0–9 produces 1,000,000 sequence numbers, and modular arithmetic turns each number into a title, date, seniority and so on. Synthetic jobs reuse the real companies and locations, so only the jobs volume grows, which matches real data: many postings, comparatively few companies.

CREATE TABLE project3_perf.jobs LIKE project1_jobs.jobs;

INSERT INTO project3_perf.jobs (title, company_id, location_id, source_id, seniority, link, date_posted, notes)
SELECT CONCAT('Synthetic Role #', seq),
       1 + (seq % @max_company), 1 + (seq % @max_location), 1 + (seq % @max_source),
       ELT(1 + (seq % 4), 'Junior', 'Mid', 'Senior', 'Staff'),
       CONCAT('https://synthetic.example.com/jobs/', seq),
       DATE_SUB(CURDATE(), INTERVAL (seq % 1095) DAY),
       'synthetic:perf-test'
FROM (
  SELECT d1.d*100000 + d2.d*10000 + d3.d*1000 + d4.d*100 + d5.d*10 + d6.d AS seq
  FROM (SELECT 0 d UNION SELECT 1 UNION SELECT 2 /* … */ UNION SELECT 9) d1
  CROSS JOIN (SELECT 0 d UNION SELECT 1 /* … */ UNION SELECT 9) d2
  /* … d3 to d6 the same: 10^6 = 1,000,000 numbers */
) seqs;

Result: 1,001,567 rows in about 4 minutes, with no row-by-row loop.

2. Find a query worth tuning

What it does: a realistic filter, "senior roles posted in a date window, newest first". Project 1 had predicted this exact case: a composite index pays off once queries filter on a category and a date range.

EXPLAIN ANALYZE
SELECT id, title, date_posted FROM jobs
WHERE seniority = 'Senior' AND date_posted BETWEEN '2024-01-01' AND '2024-06-30'
ORDER BY date_posted DESC
LIMIT 50;

Result (before): MySQL walked idx_date_posted backwards over about 335,892 rows in the date range, checking seniority on each one. 26.8 ms.

3. Add the composite index

What it does: puts the equality column first and the range column second, so one index range scan answers both conditions and already returns rows in date order.

CREATE INDEX idx_seniority_date_posted ON jobs (seniority, date_posted);

Result (after): a single range scan over matching rows only. 1.6 ms, about 17× faster in EXPLAIN ANALYZE.

4. Benchmark it properly

What it does: runs the query 200 times inside one MySQL session and times the loop on the server, so connection and process start-up don't count.

CREATE PROCEDURE bench_query(IN iterations INT)
BEGIN
  DECLARE i INT DEFAULT 0; DECLARE dummy INT;
  WHILE i < iterations DO
    SELECT COUNT(*) INTO dummy FROM (
      SELECT id FROM jobs WHERE seniority = 'Senior'
        AND date_posted BETWEEN '2024-01-01' AND '2024-06-30'
      ORDER BY date_posted DESC LIMIT 50) t;
    SET i = i + 1;
  END WHILE;
END;

SET @t0 = NOW(6); CALL bench_query(200); SET @t1 = NOW(6);
SELECT TIMESTAMPDIFF(MICROSECOND, @t0, @t1) / 200 AS avg_us_per_call;

Result: 745 µs before, 416 µs after, about 1.8× faster.

Both numbers are true, but they answer different questions:

• 17×, single cold run: what a production query hitting cold data feels.
• 1.8×, warm repeated loop: after the first run, the buffer pool already holds the pages, which narrows the gap.

5. Backup and restore drill

What it does: dumps the live project1_jobs without locking tables, restores it into a fresh database, and compares row counts.

docker exec mysql sh -c "mysqldump --single-transaction --routines --triggers -uroot -p\"\$MYSQL_ROOT_PASSWORD\" project1_jobs" \
  | gzip > project1_jobs_backup_$(date +%Y%m%d).sql.gz

gunzip -c project1_jobs_backup_YYYYMMDD.sql.gz \
  | docker exec -i mysql sh -c "mysql -uroot -p\"\$MYSQL_ROOT_PASSWORD\" restore_test"

SELECT 'jobs' AS tbl, COUNT(*) FROM restore_test.jobs
UNION ALL SELECT 'companies', COUNT(*) FROM restore_test.companies;  -- … and the other two tables

Result: exact match on all four tables (1,567 jobs, 495 companies, 381 locations, 5 sources). The dump compressed to 72 KB. The test database was dropped afterwards.

Issues encountered

The first benchmark showed no improvement at all

• Symptom: about 67–72 ms per run, both before and after the index, contradicting EXPLAIN ANALYZE.
• Cause: each run started a new docker exec and a new mysql client. Process and connection start-up took around 70 ms and buried a query that only takes 1–2 ms either way.
• Fix: time the loop inside one session (step 4). Lesson: know what your benchmark actually measures.

A restore that "succeeded" but did nothing

• Symptom: docker exec mysql mysql … < file.sql exited 0, but nothing had been loaded.
• Cause: without -i, docker exec doesn't pass stdin into the container, so the client read an empty input and exited cleanly.
• Fix: always docker exec -i when piping a file in, then verify with row counts instead of trusting the exit code.

Limits of this test

• Warm-cache gap: at 1M rows the whole working set fits in memory. At tens of millions of rows, or with a smaller buffer pool, the warm benchmark should show more of the 17×.
• Load size: the 1M-row INSERT … SELECT is one big transaction. Fine for a one-off seed, but a real ongoing load would need chunking.
• Restore time: the drill was timed on the small database. The real recovery time for the 1M-row database still needs measuring. That's part of the planned backup/DR project.