Overview of Different ALTER Algorithms and Its Locking Scenarios in MySQL
summary
Demo uses Percona server 8.7.28 and sysbench against a ~4,000,000‑row InnoDB table to run ALTER with algorithms COPY, INPLACE and INSTANT under locks shared, exclusive and none. COPY creates a new .ibd temp file (~772 MB), takes ~1m56s, allows reads with shared and blocks everything with exclusive, and rejects none. INPLACE writes an alter log, finishes ~18s, supports none but needs a large alter log size to avoid failures. INSTANT changes only the data dictionary (adding a column in 0.73s) but supports a limited set of operations. Old_alter_table forces COPY when enabled.
AI-written summary · about
chapters (5)
related talks
40mMaking the most out of MySQL
47mMy SQL 5 6 - Online Operations and Improved Diagnostics
42mAnatomy of Table-Level Locks in PostgreSQL
39mMaking the most out of MySQL
21mSurvivor: MySQL Island – Outwit, Outplay, Outlast Metadata...
24mPercona Server for MySQL Advantage
36mMySQL & InnoDB Fundamentals and Configuration
43mOptimizing MySQL without SQL or touching my.cnf