Storage, Performance, and Concurrency Considerations
In most cases,
ALTER TABLE
makes a temporary copy of the original table. MySQL waits for other operations that are modifying the table, then proceeds. It incorporates the alteration into the copy, deletes the original table, and renames the new one. WhileALTER TABLE
is executing, the original table is readable by other sessions. Updates and writes to the table that begin after theALTER TABLE
operation begins are stalled until the new table is ready, then are automatically redirected to the new table without any failed updates. The temporary table is created in the database directory of the new table. This can differ from the database directory of the original table forALTER TABLE
operations that rename the table to a different database.
An
ALTER TABLE
operation that uses theCOPY
algorithm prevents concurrent DML operations. Concurrent queries are still allowed. That is, a table-copying operation always includes at least the concurrency restrictions ofLOCK=SHARED
(allow queries but not DML). You can further restrict concurrency for operations that support the LOCK clause by specifyingLOCK=EXCLUSIVE
, which prevents DML and queries.
Getting the size of the databases in a server in MB:
SELECT table_schema, sum((data_length+index_length)/1024/1024) AS MB
FROM information_schema.tables
GROUP BY 1;