How to Fix MySQL "Out of Memory" Crash During Large ALTER TABLE Migration

Running an ALTER TABLE on a large table crashes the MySQL server outright with an out-of-memory error, rather than just running slowly. Certain schema changes require MySQL to build significant in-memory or temporary structures proportional to the table's size, and on a large enough table, this can exceed available memory entirely rather than just taking a long time.

The Problem

An ALTER TABLE on a large production table fails, sometimes taking down the whole server with it:

mysql> ALTER TABLE orders ADD COLUMN discount_code VARCHAR(20), ALGORITHM=INPLACE;
ERROR 2013 (HY000): Lost connection to MySQL server during query

Checking the MySQL error log around the same time confirms an out-of-memory condition, sometimes with the OS-level OOM killer directly involved:

$ dmesg | grep -i "out of memory"
Out of memory: Killed process 8821 (mysqld) total-vm:34823012kB, anon-rss:31921456kB

Why It Happens

Depending on the specific type of schema change and the chosen algorithm, MySQL's DDL operations can require building substantial temporary structures — a full table copy, an in-memory sort buffer for index rebuilding, or online DDL metadata tracking incoming changes during the operation — and all of this scales with the size of the table being altered. On a genuinely large table with limited available memory, this can exceed what the server can actually provide. Common contributing causes:

• The chosen ALGORITHM requires a full table rebuild — some schema changes (adding certain types of indexes, changing column types in ways that alter storage format) can't use the more memory-efficient in-place algorithm and fall back to copying the entire table, which is expensive on large tables.
• innodb_buffer_pool_size and other memory settings not accounting for the additional memory an active DDL operation needs on top of normal query workload memory usage, especially if the ALTER runs concurrently with regular production traffic.
• Insufficient temp directory space or memory for sort operations during index rebuilding specifically, which can spike memory usage significantly during that phase of the operation.
• Running the ALTER during a period of otherwise-normal load rather than during a lower-traffic maintenance window, compounding the DDL operation's own memory needs with concurrent query memory usage.

The Fix

First, check which algorithm MySQL is actually planning to use for your specific schema change, since some changes can use an in-place, lower-memory-overhead approach while others can't avoid a full table copy:

ALTER TABLE orders ADD COLUMN discount_code VARCHAR(20), ALGORITHM=INSTANT;

ALGORITHM=INSTANT, available for a specific set of simple changes (like adding a column at the end of a table in recent MySQL versions), avoids rebuilding the table entirely and completes almost immediately with minimal memory overhead. Check whether your specific change qualifies:

-- MySQL will error clearly if INSTANT isn't supported for your specific change,
-- telling you to use INPLACE or COPY instead
ALTER TABLE orders ADD COLUMN discount_code VARCHAR(20), ALGORITHM=INSTANT;

For changes that genuinely require a full rebuild, use a purpose-built online schema change tool rather than a raw ALTER TABLE — tools like gh-ost or Percona's pt-online-schema-change perform the migration by creating a shadow table, copying data in small batches, and using triggers or binlog-based replication to capture ongoing changes, all specifically designed to avoid the memory and locking spikes of a raw in-database ALTER:

gh-ost \
  --host=mydb.example.com \
  --database=mydb \
  --table=orders \
  --alter="ADD COLUMN discount_code VARCHAR(20)" \
  --execute

These tools process the table in configurable batches, which keeps peak memory usage bounded and predictable regardless of the total table size, rather than needing enough memory for the entire operation at once.

If you need to stick with a native ALTER TABLE, schedule it during a genuine low-traffic maintenance window to reduce competing memory demand from concurrent queries, and temporarily raise available memory headroom if your infrastructure allows for it:

SET SESSION sort_buffer_size = 4194304; -- reasonable session-level bump for this operation only
ALTER TABLE orders ADD COLUMN discount_code VARCHAR(20), ALGORITHM=INPLACE, LOCK=NONE;

Monitor memory usage in real time during the operation so you can intervene before an actual OOM crash if usage climbs dangerously close to the limit:

watch -n 5 'free -h && mysqladmin -u root -p status'

Still Not Working?

If even online schema change tools struggle due to genuinely constrained memory on the instance, consider temporarily upsizing the instance for the duration of the migration — many managed platforms let you scale up compute for a maintenance window and scale back down afterward, since the migration's memory cost is one-time while the ongoing benefit of the schema change is permanent:

aws rds modify-db-instance --db-instance-identifier mydb --db-instance-class db.r6g.2xlarge --apply-immediately

Run the migration on the temporarily larger instance, verify it completes successfully, and then scale back down to your normal instance size once the schema change is confirmed complete and stable — this avoids permanently paying for larger capacity than your steady-state workload actually needs, while still giving the one-time migration enough headroom to succeed reliably.