What the development box still gets wrong about production

The box that the playbook builds matches production in every way that is easy to check: same PHP version, same extensions, same nginx configuration, same MySQL build with the same my.cnf. It took about two months to find out how much that leaves out.

The symptom

A report page shipped after a week of work and timed out on the first afternoon. The query behind it had been measured at 20 ms during development and was not subtly slower in production — it never returned at all.

-- development, 1,400 orders
mysql> EXPLAIN SELECT ... FROM orders o JOIN order_items i ...G
         type: ALL
         rows: 1394
        Extra: Using where; Using temporary; Using filesort

1 row in set (0.02 sec)

-- production, 4.1 million orders
         type: ALL
         rows: 4118207
        Extra: Using where; Using temporary; Using filesort

The plan is identical. It was always a full scan with a temporary table and a filesort — that is not a plan that degrades at scale, it is a plan that was wrong from the start and could not be seen to be wrong against 1,400 rows.

Why it happens

Parity of configuration is not parity of data. The provisioning work made every machine agree about versions and settings, and then everyone quietly assumed the agreement extended further than it does. It does not cover row counts, and it does not cover distribution: a development database seeded with a fixture generator gives every customer three orders, so a query that behaves badly when one customer has nine thousand looks fine.

It also does not cover latency or concurrency. Locally the database is on the same machine, there is one user, and nothing else is competing for the buffer pool. Every N+1 query costs 0.1 ms instead of 2 ms, which is the difference between a page that is imperceptibly wasteful and one that falls over at forty concurrent users.

The fix

A subset with the real shape

Copying production wholesale is not an option — it is 40 GB, it contains real customers, and it would not fit on a laptop anyway. What is needed is the smallest extract that keeps the query planner honest: full copies of the small tables, and a coherent slice of the large ones that preserves the skew rather than sampling it away.

#!/usr/bin/env bash
# subset.sh — runs on the replica, never on the primary
set -euo pipefail

SINCE='2013-11-01'

# small tables in full: products, categories, everything referenced
mysqldump --single-transaction shop products categories brands tax_rates 
  > /tmp/shop-reference.sql

# the large ones by date, plus the customers those rows point at
mysqldump --single-transaction shop orders 
  --where="placed_at >= '$SINCE'" > /tmp/shop-orders.sql

mysqldump --single-transaction shop order_items 
  --where="order_id IN (SELECT id FROM orders WHERE placed_at >= '$SINCE')" 
  > /tmp/shop-items.sql

Six months of orders is about 900,000 rows — enough that the planner makes the same choices it makes in production, small enough to restore in four minutes. The customers with thousands of orders are still in it, because they are selected by their orders rather than by a random sample, and they are the rows that expose the bad plans.

The anonymisation runs on the extract, before it leaves the replica, and it is deliberately destructive rather than reversible.

UPDATE customers SET
  email      = CONCAT('customer', id, '@example.invalid'),
  phone      = CONCAT('0700', LPAD(id, 6, '0')),
  first_name = CONCAT('Given', id),
  last_name  = CONCAT('Family', id),
  password   = '$2y$10$invalidhashinvalidhashinvalidhashinvalidhashin';

UPDATE payment_tokens SET token = NULL, last_four = '0000';
DELETE FROM email_log;

Mail, cron and TLS

Three things are absent locally and load-bearing in production, and each one hides a different class of bug.

Mail is the easy one. Rather than disabling it — which means the templates are never seen — the box catches everything and shows it in a browser, so a broken order confirmation is visible during development instead of after release.

; the playbook writes this into the development php.ini only
sendmail_path = /usr/bin/env catchmail -f [email protected]

Cron is the one that surprises people. Every scheduled task ran only in production, so failures were discovered by their absence: a nightly export that had not run since February, noticed in April because somebody asked for the file. Installing the same crontab on the development box, with the same environment, means a task that depends on a PATH nobody set fails on a laptop rather than silently at three in the morning.

# roles/cron/tasks/main.yml installs this on both machines
PATH=/usr/local/bin:/usr/bin:/bin
[email protected]

15 3 * * *  cd /var/www/shop && php bin/export-catalogue.php
*/5 * * * * cd /var/www/shop && php bin/process-queue.php

TLS is third and mostly about honesty. The certificate on the development box is self-signed, because certificates are bought and nobody is buying one for shop.dev. What matters is that the site is reached over HTTPS at all, so that mixed-content warnings, protocol-relative asset URLs and cookies missing the secure flag are all found locally rather than in the ten minutes after a launch.

# roles/nginx generates it once, on the development box only
openssl req -x509 -nodes -newkey rsa:2048 -days 3650 
  -keyout /etc/ssl/private/shop.dev.key 
  -out    /etc/ssl/certs/shop.dev.crt 
  -subj   '/CN=shop.dev'

The browser warning it produces every morning is mildly irritating and worth leaving in place, because the alternative — trusting the certificate locally — hides the one thing this is meant to expose, which is that the redirect and the cookie flags are wrong.

Verifying it worked

The check is the slowest query from last week’s production slow log, run against both databases, comparing plans rather than times. Times will never match — the laptop has a tenth of the buffer pool — but the plan is what the development box is being asked to predict.

$ mysqldumpslow -s t -t 1 /var/log/mysql/slow.log | head -3
Count: 812  Time=3.91s  SELECT ... FROM orders o JOIN order_items i ...

$ ./bin/compare-plan.sh 'SELECT ... FROM orders o JOIN order_items i ...'
  local       type=ref  key=idx_customer_placed  rows=1102   Using where
  production  type=ref  key=idx_customer_placed  rows=1180   Using where

plans match

Same access path, same index, row estimates within seven per cent. That is the property worth having: a query that is going to be slow in production is now slow on the box where it is being written.

What this costs

A nightly job that has to keep working, running on the replica so the extract never touches the primary, plus about 3 GB per developer and a restore that is no longer instant. When it breaks, it breaks quietly — so it writes its row counts to a log somebody actually looks at, and a subset that is a week stale is treated as a bug rather than as normal.

The other cost is a rule, and rules about data need to be written down rather than understood. Nothing leaves the replica un-anonymised; payment tokens are nulled rather than scrambled, because a scrambled token still looks like a token to the next person who finds it; and the extract is never restored onto anything reachable from outside the office. The shared folder makes it convenient to keep the dump inside the project directory, which is exactly where it must not be, because the project directory is the thing that gets committed.