Adding Indexes to Large MySQL Tables Online

A reporting query needs an index on events (account_id, created_at), and events holds 1.2 billion rows in 600 GB. InnoDB can build secondary indexes online — ALTER TABLE ... ADD INDEX defaults to the in-place algorithm and allows concurrent reads and writes — so in principle this is a single statement. In practice three things decide whether it is invisible or an incident: whether the statement really runs in place without a lock (and fails loudly if it cannot), whether the online change log survives the write volume during a build that takes hours, and what happens to replicas, which receive the ALTER as a single statement only after the primary finishes and then spend hours applying it. This guide covers all three and when to hand the job to gh-ost instead. It belongs to Online Index Management.

Primary and Replica During an In-Place Index Build Timeline over 8 hours. The primary builds the index in place for 3.5 hours while writes continue. The replica receives the ALTER only after the primary commits, then applies it for another 3.5 hours on its SQL thread; with a single-threaded applier, other replicated changes queue behind it and lag grows. Primary and Replica During an In-Place Index Build Primary build ADD INDEX, INPLACE, LOCK=NONE Primary writes continue Replica apply applies the same ALTER Replica lag grows to ~3.5 h 0 h 2 h 4 h 6 h 8 h build unaffected replica busy lag
Online on the primary, a long queue on the replica — the replica's lag equals the build time unless the build goes through gh-ost.

Symptom / Error Signatures

These are the failure modes of an online index build on a large MySQL table:

ERROR 1799 (HY000): Creating index 'idx_events_account_created' required more than 'innodb_online_alter_log_max_size' bytes of modification log. Please try again.
ERROR 1846 (0A000): LOCK=NONE is not supported. Reason: ... Try LOCK=SHARED.
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

The first means writes during the build exceeded the temporary log that records concurrent changes; the build is rolled back after hours of work. The second means this particular change cannot run without a lock (for example, some full-text or spatial index cases). The third is the metadata lock the ALTER needs at its start and end, waiting behind a long transaction. On replicas, the symptom is Seconds_Behind_Source climbing for hours after the primary finished, and replica threads showing altering table.

Root Cause Analysis

For a secondary B-tree index, InnoDB’s in-place algorithm scans the clustered index, sorts the keys and builds the new index, while concurrent DML continues. Changes made during the build are recorded in an online alter log and applied at the end; if that log grows beyond innodb_online_alter_log_max_size (128 MB by default), the operation fails. The ALTER also takes an exclusive metadata lock briefly at its start and end, which queues behind open transactions like any DDL — see diagnosing “Waiting for table metadata lock”.

Replication is the bigger problem for very large tables. The ALTER is written to the binlog when it completes on the primary, and each replica then runs the same build. With a single-threaded applier — or when the ALTER blocks parallel application behind it — replica lag grows for the entire build. Tools like gh-ost avoid this by building a new table with the index through ordinary row copies that replicate as normal traffic, then swapping tables, as explained in gh-ost vs pt-online-schema-change.

Table size / write rate Approach Main risk
small (minutes to build) ADD INDEX ..., ALGORITHM=INPLACE, LOCK=NONE metadata lock queue
large, modest writes, no lag-sensitive replicas in-place with a larger online log replica lag for the build duration
large, heavy writes or lag-sensitive replicas gh-ost / pt-online-schema-change long total duration, cut-over
In-Place ALTER or gh-ost? Decision tree. If the build takes minutes, use ALTER with ALGORITHM=INPLACE, LOCK=NONE and a short lock_wait_timeout. If it takes hours, check whether replicas serve reads that cannot tolerate hours of lag; if they do, use gh-ost; if not, use in-place with innodb_online_alter_log_max_size raised for the write volume. In-Place ALTER or gh-ost? Will the build take only minutes? yes no INPLACE, LOCK=NONE, short lock_wait_timeout Replicas serve lag-sensitive reads? yes no gh-ost (replicates as rows) INPLACE + larger online alter log
Replica tolerance, not primary capacity, usually decides between a native online ALTER and gh-ost.

Immediate Mitigation

1. Always state the algorithm and lock. This guarantees the build is online or fails immediately; without the clauses, MySQL may silently choose a blocking method.

-- MySQL 8.0 · migration session · requires ALTER on the table
-- WARNING: replicas will apply this as one long statement after the primary finishes.
SET SESSION lock_wait_timeout = 5;
ALTER TABLE events ADD INDEX idx_events_account_created (account_id, created_at),
  ALGORITHM=INPLACE, LOCK=NONE;
-- ROLLBACK PATH: ALTER TABLE events DROP INDEX idx_events_account_created, ALGORITHM=INPLACE, LOCK=NONE;

2. Size the online alter log for the build’s duration and write rate. The variable is dynamic; raise it for the session’s build if writes are heavy.

-- MySQL 8.0 · requires SYSTEM_VARIABLES_ADMIN · global, dynamic; revert after the build
SET GLOBAL innodb_online_alter_log_max_size = 4 * 1024 * 1024 * 1024;   -- 4 GB

The log lives in temporary files under innodb_tmpdir (or the MySQL temp directory), so confirm there is disk space for it and for the sort files of the build.

3. Watch progress. Performance Schema stage events report how far the build has progressed.

-- MySQL 8.0 · read-only · requires stage instruments and consumers enabled
SELECT EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED,
       ROUND(100 * WORK_COMPLETED / NULLIF(WORK_ESTIMATED, 0), 1) AS pct
FROM performance_schema.events_stages_current
WHERE EVENT_NAME LIKE 'stage/innodb/alter%';

4. If replicas cannot tolerate the lag, stop and use gh-ost. Kill the ALTER on the primary before it completes (it rolls back without replicating), then run the same change through gh-ost with lag throttling, as in throttling gh-ost on replica lag.

Permanent Fix / Long-Term Pattern

Decide the method from the table’s size and the replicas’ tolerance, not case by case under pressure. A simple rule works for most teams: tables that build in under ten minutes use native in-place DDL with explicit ALGORITHM and LOCK clauses and a short lock_wait_timeout; larger tables, or any table whose replicas serve user reads, go through gh-ost with a postponed cut-over. Encode the rule in the migration pipeline — for example with Skeema’s alter-wrapper-min-size, described in managing MySQL schemas with Skeema — so nobody has to remember it.

For native builds, rehearse on a restored copy to measure duration and online-log growth, raise innodb_online_alter_log_max_size accordingly, and schedule the build in low-write hours. Enable multi-threaded replication (replica_parallel_workers with replica_preserve_commit_order) to limit how much other traffic queues behind a replicated ALTER, though the ALTER itself still runs single-threaded. And ship the index before the code that relies on it, verifying with EXPLAIN on the primary that the planner uses it.

MySQL Index Build Routing Pipeline. Estimate build time from a rehearsal; a gate routes builds over 10 minutes or on lag-sensitive tables to gh-ost; others run as native INPLACE, LOCK=NONE with a sized online log; a gate checks the index exists and EXPLAIN uses it; the dependent code ships next. MySQL Index Build Routing Rehearse duration, log growth route > 10 min? Native build INPLACE, LOCK=NONE used EXPLAIN ok? Ship code next release use gh-ost review index fail
Routing by estimated build time and replica sensitivity keeps both paths safe.

Verification Checklist

Frequently Asked Questions

Does adding a secondary index block writes in MySQL 8.0? Not with the in-place algorithm and LOCK=NONE, which is the default for secondary B-tree indexes. It needs a brief exclusive metadata lock at the start and end, which can queue behind long transactions.

Why did my index build fail after hours with error 1799? Writes during the build exceeded innodb_online_alter_log_max_size. Raise the limit (it is dynamic), make sure the temporary directory has space, and retry in a lower-write period.

Why are replicas so far behind after the build? Replicas execute the same ALTER after the primary commits it, taking roughly as long as the primary did, and other replicated changes wait behind it. gh-ost avoids this by replicating the change as ordinary row copies.

Can I build the index on replicas first? Building indexes independently on replicas (with binary logging disabled for the session) is possible but creates schema drift between servers and is easy to get wrong. Prefer gh-ost or a native build with accepted lag.

Related Articles