Synthetic Industry

Platform · updated 2026-10-11

MySQL performance and locking: read the plan, the slow log and the lock waits before changing anything

For teams on MySQL 8.x: where to look for slow statements, how InnoDB handles schema changes and deadlocks, and which bounded jobs fit.

Start with evidence the server already keeps

MySQL gives you three places to look before you change anything. The slow query log records statements that take longer than long_query_time seconds and examine at least min_examined_row_limit rows; it is disabled by default, and the default threshold is 10 seconds, far above what a web page tolerates, so many servers log nothing useful until the threshold is lowered. The log writes a statement after it has finished and its locks are released, so entries can appear out of execution order. The mysqldumpslow utility summarises the file.

For one statement, EXPLAIN shows the plan. In the tabular form, access type ALL is a full table scan, the rows column is an estimate, and Using filesort or Using temporary signal extra work. EXPLAIN ANALYZE runs the statement and shows estimated and actual figures together, so use it on a copy, not on production.

  • Lower long_query_time for a limited period and keep the log for a full business cycle.
  • Run plans on a restored copy with realistic data and personal data removed.

Schema changes: what InnoDB can do online

The 8.4 manual documents which operations run online. Adding a column can use ALGORITHM=INSTANT, a metadata-only change with listed limits; adding or dropping a secondary index runs in place while reads and writes continue; changing a column's data type needs a table copy and allows no concurrent changes. A single supported InnoDB statement is atomic in 8.4, committed or rolled back as a whole, but each DDL statement commits on its own and cannot sit inside a transaction with others. A migration of several steps therefore cannot be rolled back as a unit, which is why a rehearsed rollback and a tested restore point matter more here than on engines with transactional DDL.

Ask for the algorithm explicitly in the statement so it fails instead of silently doing something heavier, and check the table against the documented exclusions first.

  • Rehearse every change on a copy of realistic size under write load.
  • Keep a restore point you have actually restored.

Deadlocks and lock waits

InnoDB detects a deadlock and rolls back one transaction, called the victim. SHOW ENGINE INNODB STATUS shows the most recent deadlock, and setting innodb_print_all_deadlocks writes each one to the error log, which the manual recommends when they are frequent. For waits, the sys.innodb_lock_waits view lists the waiting and blocking queries and their processlist IDs. The manual's advice is to keep transactions small, access rows in a consistent order, index the columns used in locking statements, and be ready to retry a rolled-back transaction.

A retry is the last step. First find the two code paths and fix their order.

  • Capture the full deadlock record, with values removed, before changing code.
  • Check for a blocking session that is idle inside a transaction.

Bounded jobs on MySQL, and their limits

Four fixed-scope jobs fit a MySQL application. One slow query fixed with before and after plans. An index review with up to five approved changes measured on staging. One new column added and backfilled with a rehearsed rollback. One deadlock workload reproduced and removed. A monthly review keeps watching statements and growth afterwards. All of them work on a restored copy you prepare, with personal data removed or replaced, and hand back scripts or pull requests. None connects to your production server, and you apply any change after a restore point you have tested.

This page is about operating MySQL, not about moving off it: a migration to another engine is outside these jobs. Each price is a published test price, untested with buyers.

  • Choose the job by the symptom: one slow statement, many slow statements, a schema change, a lock error.
  • Send versions and invented examples first, never credentials or real data.

Sources and limits

  • MySQL 8.4: The slow query log Checked 2026-10-11.
    • The slow query log is disabled by default, long_query_time defaults to 10 seconds, statements are logged after they finish, and mysqldumpslow summarises the file.
  • MySQL 8.4: EXPLAIN output format Checked 2026-10-11.
    • Type ALL is a full table scan; rows is an estimate for InnoDB; Using filesort and Using temporary are extra work worth checking.
  • MySQL 8.4: EXPLAIN Checked 2026-10-11.
    • EXPLAIN ANALYZE runs the statement and shows estimated cost and rows beside actual time, rows and loops.
  • MySQL 8.4: InnoDB online DDL operations Checked 2026-10-11.
    • Adding a column can use ALGORITHM=INSTANT, adding or dropping a secondary index runs in place with concurrent changes, and changing a column data type needs a table copy with no concurrent changes.
  • MySQL 8.4: Deadlocks in InnoDB Checked 2026-10-11.
    • InnoDB detects deadlocks by default and rolls back a victim; SHOW ENGINE INNODB STATUS shows the latest and innodb_print_all_deadlocks logs all.
  • Django 5.2: Migrations Checked 2026-10-11.
    • MySQL lacks transaction support around schema alterations, so a failed migration must be unpicked by hand.
  • MySQL 8.4: sys.innodb_lock_waits Checked 2026-10-11.
    • The view lists waiting and blocking transactions with their queries and processlist IDs.
  • MySQL 8.4: Atomic Data Definition Statement Support Checked 2026-10-11.
    • A supported InnoDB DDL statement is committed or rolled back as a whole, even if the server halts; atomic DDL is not transactional DDL, because DDL statements implicitly end any active transaction and cannot be combined with other statements in one transaction.