Große Tabellen ändern ohne lange Downtime

1. DDL-Algorithmen: INSTANT, INPLACE, COPY

MySQL bietet drei Algorithmen für ALTER TABLE-Operationen. Der Unterschied zwischen ihnen ist fundamental: INSTANT ändert nur Metadaten (Millisekunden), INPLACE baut die Tabelle online mit laufendem DML neu, und COPY erstellt eine vollständige Kopie der Tabelle mit einem exklusiven Lock — was bei großen Tabellen zu Ausfallzeiten führt.

MySQL wählt automatisch den schnellstmöglichen Algorithmus. Wer sicherstellen will, dass ein bestimmter Algorithmus verwendet wird (oder die Operation fehlschlägt statt auf COPY zu fallen), gibt ihn explizit an:

-- Syntax for specifying DDL algorithm explicitly
ALTER TABLE sales_order_item
ADD COLUMN custom_field VARCHAR(255) NULL COMMENT 'Custom integration field',
ALGORITHM=INSTANT,  -- fail if INSTANT is not supported for this operation
LOCK=NONE;          -- fail if an exclusive lock would be required

-- Check what MySQL would choose (without executing)
-- Run EXPLAIN on the ALTER to see planned algorithm (MySQL 8.0.27+)
-- Or test with ALGORITHM=INSTANT and catch the error:
ALTER TABLE sales_order_item
ADD COLUMN test_field INT NULL,
ALGORITHM=INSTANT;
-- If this fails: operation does not support INSTANT, must use INPLACE or COPY

2. ALGORITHM=INSTANT: Sofortige Schemaänderungen

INSTANT wurde in MySQL 8.0 eingeführt und ist die wichtigste DDL-Verbesserung der letzten Jahre. Bei INSTANT-fähigen Operationen wird nur der Data Dictionary aktualisiert — die tatsächlichen Tabellenzeilen bleiben unberührt. Das dauert Millisekunden, unabhängig von der Tabellengröße.

Welche Operationen INSTANT unterstützen (MySQL 8.0):

• Hinzufügen von nullable Spalten (am Ende der Tabelle)
• Löschen von Spalten
• Hinzufügen und Löschen von virtuellen generierten Spalten
• Umbenennen von Spalten (ab MySQL 8.0.28)
• Ändern des DEFAULT-Werts einer Spalte
• Ändern des ENUM/SET-Typs (neue Werte am Ende hinzufügen)

-- These operations support ALGORITHM=INSTANT in MySQL 8.0+

-- Add nullable column (instant, no table rebuild)
ALTER TABLE sales_order
ADD COLUMN is_gdpr_deleted TINYINT(1) DEFAULT 0 COMMENT 'GDPR deletion flag',
ALGORITHM=INSTANT;

-- Drop a column (instant in MySQL 8.0.29+)
ALTER TABLE catalog_product_entity
DROP COLUMN deprecated_field,
ALGORITHM=INSTANT;

-- Rename a column (instant in MySQL 8.0.28+)
ALTER TABLE customer_entity
RENAME COLUMN old_column_name TO new_column_name,
ALGORITHM=INSTANT;

-- Add virtual generated column (instant)
ALTER TABLE sales_order
ADD COLUMN order_year YEAR AS (YEAR(created_at)) VIRTUAL,
ALGORITHM=INSTANT;

-- Verify table instant-compatibility by checking innodb_instant_add_column
SELECT TABLE_NAME, CREATE_OPTIONS
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME = 'sales_order';

3. ALGORITHM=INPLACE: Online-Rebuild mit DML

INPLACE baut die Tabelle im Hintergrund neu auf, während Lese- und Schreiboperationen (DML) weiterhin erlaubt sind. Das dauert je nach Tabellengröße Minuten bis Stunden, aber die Applikation läuft weiter. Am Ende gibt es einen kurzen exklusiven Lock für den finalen Swap.

INPLACE wird für Operationen gebraucht, die INSTANT nicht unterstützen: Hinzufügen von NOT NULL-Spalten, Erstellen von Nicht-Volltextindizes, Änderungen am Primärschlüssel oder am Spaltentyp.

-- Adding a NOT NULL column requires INPLACE (table rebuild)
ALTER TABLE sales_order_item
ADD COLUMN integration_ref VARCHAR(64) NOT NULL DEFAULT '',
ALGORITHM=INPLACE,
LOCK=NONE;  -- allows concurrent DML during rebuild

-- Adding a regular index uses INPLACE by default
ALTER TABLE sales_order
ADD INDEX idx_customer_created (customer_id, created_at),
ALGORITHM=INPLACE,
LOCK=NONE;

-- Check if operation supports INPLACE with LOCK=NONE
-- If MySQL needs more than LOCK=NONE, it will error with:
-- "LOCK=NONE is not supported. Reason: ..."
-- Then use LOCK=SHARED (reads allowed) or LOCK=DEFAULT

-- Changing column type usually requires COPY — verify first
ALTER TABLE sales_order
MODIFY COLUMN customer_note TEXT,  -- may require COPY
ALGORITHM=COPY,                    -- explicit — will lock the table
LOCK=SHARED;                       -- allow reads during copy

4. pt-online-schema-change: Shadow Table Strategie

pt-online-schema-change (pt-osc) ist ein Percona-Tool das ein COPY-basiertes ALTER Table ohne langen Lock ermöglicht. Es erstellt eine Shadow Table (_tablename_new), kopiert Daten in Batches, nutzt Trigger um währenddessen laufende DML-Operationen zu synchronisieren, und tauscht die Tabellen am Ende atomar via RENAME.

-- pt-online-schema-change usage (shell command, not SQL)
-- Syntax: pt-online-schema-change [OPTIONS] DSN

-- Example: Add column to large Magento table
-- pt-online-schema-change \
--   --alter "ADD COLUMN supplier_code VARCHAR(32) NULL" \
--   --host=127.0.0.1 \
--   --user=root \
--   --password=secret \
--   --database=magento \
--   --table=sales_order_item \
--   --chunk-size=1000 \
--   --max-load="Threads_running=25" \
--   --critical-load="Threads_running=50" \
--   --execute

-- pt-osc creates triggers on the original table:
-- CREATE TRIGGER pt_osc_magento_sales_order_item_ins AFTER INSERT ...
-- CREATE TRIGGER pt_osc_magento_sales_order_item_upd AFTER UPDATE ...
-- CREATE TRIGGER pt_osc_magento_sales_order_item_del AFTER DELETE ...

-- At the end, pt-osc renames tables atomically:
-- RENAME TABLE sales_order_item TO _sales_order_item_old,
--              _sales_order_item_new TO sales_order_item;

-- Verify pt-osc progress by checking the shadow table row count vs original
SELECT
    (SELECT COUNT(*) FROM sales_order_item)        AS original_count,
    (SELECT COUNT(*) FROM _sales_order_item_new)   AS copied_so_far;

Ein Nachteil von pt-osc: Die drei Trigger erzeugen zusätzliche Last auf dem Primary Server. Bei sehr hohem Write-Throughput kann das zum Problem werden. Außerdem kann pt-osc Probleme mit Foreign Keys verursachen.

5. gh-ost: Binlog-basiert und replizierungsfreundlich

gh-ost (GitHub's Online Schema Transmogrifier) ist die modernere Alternative zu pt-osc. Statt Trigger liest gh-ost den MySQL Binlog und appliziert die Changes auf die Shadow Table. Das macht es wesentlich sicherer für Replikations-Setups, erlaubt Pausierung und unterstützt Rate-Limiting.

-- gh-ost usage (shell command)
-- Key advantage over pt-osc: reads binlog instead of triggers
-- Safer for replication, pauseable, supports throttling

-- gh-ost \
--   --user=root --password=secret \
--   --host=replica.internal \
--   --database=magento \
--   --table=sales_order_item \
--   --alter="ADD COLUMN supplier_code VARCHAR(32) NULL AFTER sku" \
--   --chunk-size=500 \
--   --max-load=Threads_running=20 \
--   --throttle-control-replicas=replica1.internal,replica2.internal \
--   --execute

-- gh-ost creates the shadow table:
-- CREATE TABLE _sales_order_item_gho LIKE sales_order_item;
-- ALTER TABLE _sales_order_item_gho ADD COLUMN supplier_code ...;

-- Monitor gh-ost progress via its socket:
-- echo "status" | nc -U /tmp/gh-ost.sales_order_item.sock
-- Expected output: # Migrating ...; ETA: 2h13m; ...

-- gh-ost allows throttling at runtime:
-- echo "throttle" | nc -U /tmp/gh-ost.sales_order_item.sock  -- pause
-- echo "no-throttle" | nc -U /tmp/gh-ost.sales_order_item.sock  -- resume

6. ALTER-Fortschritt monitoren

Bei einem nativen ALTER TABLE (INPLACE oder COPY) kann der Fortschritt über performance_schema verfolgt werden:

-- Monitor native ALTER TABLE progress via performance_schema
SELECT
    EVENT_NAME,
    WORK_COMPLETED,
    WORK_ESTIMATED,
    ROUND(WORK_COMPLETED / WORK_ESTIMATED * 100, 1) AS pct_complete,
    TIMER_WAIT / 1e12 AS elapsed_seconds
FROM performance_schema.events_stages_current
WHERE EVENT_NAME LIKE 'stage/innodb/alter table%';
/*
Expected stages during INPLACE:
- stage/innodb/alter table (read PK and internal sort)
- stage/innodb/alter table (flush)
- stage/innodb/alter table (insert)
- stage/innodb/alter table (log apply table)
- stage/innodb/alter table (end)
*/

-- Also check PROCESSLIST for the ALTER TABLE thread
SELECT ID, USER, HOST, DB, COMMAND, TIME, STATE, INFO
FROM information_schema.PROCESSLIST
WHERE INFO LIKE 'ALTER TABLE%'
   OR STATE LIKE '%alter%';

7. Lock Wait Timeout während DDL

Auch INPLACE-Operationen brauchen am Anfang und am Ende kurze Locks (Metadata Locks). Wenn aktive Transaktionen die Tabelle halten, wartet der ALTER Table auf den Lock. Der Standardwert von innodb_lock_wait_timeout (50 Sekunden) kann dazu führen, dass der ALTER abbricht oder bestehende Queries blockiert werden.

-- Check current lock wait timeout
SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';
SHOW VARIABLES LIKE 'lock_wait_timeout';  -- metadata lock timeout

-- Before a long ALTER: check for long-running transactions
SELECT
    trx_id,
    trx_started,
    TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS duration_sec,
    trx_query
FROM information_schema.INNODB_TRX
ORDER BY duration_sec DESC;

-- Temporarily increase lock_wait_timeout for the session (DDL session)
SET SESSION lock_wait_timeout = 3600;  -- 1 hour

-- Kill blocking connections if necessary
SELECT ID, USER, TIME, STATE, INFO
FROM information_schema.PROCESSLIST
WHERE TIME > 60
  AND COMMAND != 'Sleep'
ORDER BY TIME DESC;

-- KILL [connection_id];

8. Praxisfall: Magento sales_order_item mit 50M+ Zeilen

In einem Magento-Produktivsystem mit mehreren Jahren Laufzeit kann sales_order_item 50 Millionen oder mehr Zeilen haben. Eine neue Spalte hinzuzufügen, einen Index zu ändern oder einen Spaltentyp anzupassen ist dann keine triviale Operation mehr.

Die Empfehlung für dieses Szenario ist klar: gh-ost ist die sicherste Option für Magento-Produktionsserver mit Replikation. Bei einem alleinstehenden MySQL-Server ohne Replikation ist pt-osc eine gute Alternative. Ein natives INPLACE mit LOCK=NONE ist nur für Operationen geeignet, die MySQL 8 nativ online durchführen kann.

-- Preparation: check table size before planning the approach
SELECT
    TABLE_NAME,
    TABLE_ROWS,
    ROUND(DATA_LENGTH / 1024 / 1024, 0)  AS data_mb,
    ROUND(INDEX_LENGTH / 1024 / 1024, 0) AS index_mb,
    ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024 / 1024, 2) AS total_gb
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME IN ('sales_order_item', 'sales_order', 'catalog_product_entity')
ORDER BY total_gb DESC;

-- Check existing indexes on sales_order_item before adding new ones
SHOW INDEX FROM sales_order_item;

-- For a new index on a 50M+ row table:
-- Option 1: ALGORITHM=INPLACE (native, concurrent DML allowed)
ALTER TABLE sales_order_item
ADD INDEX idx_sku_created (sku, created_at),
ALGORITHM=INPLACE,
LOCK=NONE;

-- Option 2: Use gh-ost (safer for replication environments)
-- gh-ost --alter="ADD INDEX idx_sku_created (sku, created_at)" ...

-- After any structural change: run ANALYZE TABLE to update statistics
ANALYZE TABLE sales_order_item;

FAQ: Online DDL MySQL — Große Tabellen ändern