Running a query against production is routine right up until it is not. This lesson is the habits
that keep an ad-hoc query from becoming an incident — measured, where it matters, against
pizza_lab at 400,000 orders.
Set the session up first
SET SESSION sql_safe_updates = 1;
SET SESSION max_execution_time = 10000; -- 10s, SELECTs only
SET SESSION innodb_lock_wait_timeout = 10;Three seatbelts, each stopping a different accident:
sql_safe_updatesrefuses anUPDATEorDELETEwhoseWHEREdoes not use a key — the shape of the statement that empties a table.max_execution_timekills a runawaySELECT. It does not apply to writes.innodb_lock_wait_timeoutmeans a statement that cannot get its lock gives up in ten seconds rather than fifty.
Put those in ~/.my.cnf under [client] so they are on by default. Making
yourself override a seatbelt deliberately is the entire mechanism.
EXPLAIN before you run it
Against a large table, on anything you have not run before:
EXPLAIN DELETE FROM customer_order WHERE status = 'CANCELLED' AND created_at < '2023-06-01' ORDER BY id LIMIT 1000;+----+-------------+----------------+------------+-------+---------------------------------------------------------+---------------------------+---------+-------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------------+------------+-------+---------------------------------------------------------+---------------------------+---------+-------+-------+----------+-------------+
| 1 | DELETE | customer_order | NULL | range | idx_customer_order_created_at,idx_customer_order_status | idx_customer_order_status | 122 | const | 49096 | 100.00 | Using where |
+----+-------------+----------------+------------+-------+---------------------------------------------------------+---------------------------+---------+-------+-------+----------+-------------+EXPLAIN works on UPDATE and DELETE, not just
SELECT, and it does not run them. type: range and an index chosen is what
you want to see; ALL on a table this size is the signal to stop and think. See
EXPLAIN — and remember rows is an estimate.
Read before you write
-- 1. exactly which rows?
SELECT COUNT(*) FROM customer_order WHERE status = 'CANCELLED' AND created_at < '2023-06-01';-- session 1: destructive if committed. Shown, not run.
-- 2. the same WHERE, now changing them
START TRANSACTION;
DELETE FROM customer_order WHERE status = 'CANCELLED' AND created_at < '2023-06-01' LIMIT 1000;
-- check the reported row count against what you expected
ROLLBACK; -- or COMMITTwo safety nets: the count tells you the blast radius before anything changes, and the transaction gives you an undo if you look at the row count before committing. A transaction you commit reflexively is not a safety net.
⚠️ And keep it short. An open transaction holds locks; walking away from one at the prompt blocks other writers and stalls replication. Do not leave one open while you think.
Work in batches
-- session 1: destructive. Shown, not run.
-- repeat until it reports 0 rows
DELETE FROM customer_order
WHERE status = 'CANCELLED' AND created_at < '2023-06-01'
ORDER BY id
LIMIT 1000;One statement changing 400,000 rows holds locks for its whole duration, builds an enormous undo log, and produces one giant transaction that a replica applies single-threaded — so replication lag grows for as long as it runs, and every read-from-replica in the application sees stale data.
Batches of 1,000 to 10,000 with a pause between them keep each transaction short. The
WHERE must shrink as you go, or the loop never ends. See
DELETE.
When something is already wrong
SHOW FULL PROCESSLIST;The first thing to run when the database is slow. Sort by Time, look at the top, and
read the State column — Sending data is ordinary work,
Waiting for table metadata lock means a DDL statement is blocked behind an open
transaction and is now blocking everything else.
-- unavailable: 1234 is an illustrative thread id. Take a real one from PROCESSLIST.
KILL QUERY 1234; -- stop the statement, keep the connection
KILL 1234; -- stop bothPrefer KILL QUERY. Killing the connection makes an application's pool notice a dead
connection and can produce a retry storm at the worst moment.
Be aware that killing a long UPDATE or DELETE is not instant — InnoDB
has to roll it back, which can take longer than the statement had been running. That is
another argument for batches: a killed batch rolls back in a moment.
The slow query log
SELECT @@global.slow_query_log AS slow_log, @@global.long_query_time AS threshold_seconds;+----------+-------------------+
| slow_log | threshold_seconds |
+----------+-------------------+
| 0 | 10.000000 |
+----------+-------------------+Off by default, with a 10-second threshold that is far too generous for a web application. Turn it on and lower it:
-- session 1: changes server-wide settings. Shown, not run.
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 0.5;mysqldumpslow -s t -t 10 /var/lib/mysql/slow.log # the 10 slowest, grouped
pt-query-digest /var/lib/mysql/slow.log # better, if you have Percona ToolkitGroup before you optimise. A query taking 200ms and running 50,000 times an hour costs far more
than one taking 4 seconds twice a day, and only a digest shows you that. sys has live
views over the same ground — sys.statement_analysis,
sys.statements_with_full_table_scans.
MAX_EXECUTION_TIME per query
-- ERROR 3024 (HY000): Query execution was interrupted, maximum statement
-- execution time exceeded
SELECT /*+ MAX_EXECUTION_TIME(1) */ COUNT(*)
FROM customer_order o
JOIN order_item i ON i.order_id = o.id
JOIN order_item_topping t ON t.order_item_id = i.id;An optimizer hint capping one statement, in milliseconds. Useful for an exploratory query on a big table when you want to find out whether it is fast without finding out by waiting.
Schema changes
The riskiest thing on this page. Many ALTER TABLE operations rebuild the entire
table, and they take a metadata lock — so the ALTER waits for open transactions, and
every subsequent query on that table queues behind the ALTER. A table
that was fine a second ago is now completely blocked, and the queue grows until connections run out.
This is a common way to take an application down with a statement that looked harmless.
-- session 1: alters a shared table. Shown, not run.
-- state the algorithm, and let MySQL refuse rather than surprise you
ALTER TABLE customer_order ADD COLUMN note VARCHAR(200), ALGORITHM=INSTANT;
ALTER TABLE customer_order ADD INDEX idx_note (note), ALGORITHM=INPLACE, LOCK=NONE;Naming ALGORITHM makes MySQL error out if it cannot do it that way,
which is far better than discovering mid-outage that it chose COPY. In MySQL 8 adding a
column is usually INSTANT — metadata only.
For anything that does rebuild a large table, use pt-online-schema-change or
gh-ost: they build a copy, keep it in step with triggers or the binlog, and swap it in.
And run it in a maintenance window regardless.
Before you touch production
- Run it on a replica or a restored backup first. There is usually one.
- Have the undo written down before you start.
- Know how you will verify success — the exact query and the expected number.
- Do it when someone else is awake.
- Say what you are doing in a channel where a colleague can see it.
What to remember
sql_safe_updates,max_execution_timeand a short lock timeout, on by default in your client config.EXPLAINworks on writes too. Use it before, not after.SELECTthe rows first; batch anything large.SHOW FULL PROCESSLISTthenKILL QUERY, and expect rollback to take time.- The slow query log is off by default at 10s. Turn it on, lower it, and read a digest.
ALTER TABLEtakes a metadata lock and can queue every query on the table.