Online ALTER TABLE in MySQL 5.6
- by Marko Mäkelä
This is the low-level view of data dictionary language (DDL)
operations in the InnoDB storage engine in MySQL 5.6. John Russell
gave a more high-level view in his blog
post April
2012 Labs Release – Online DDL Improvements.
MySQL before the InnoDB Plugin
Traditionally, the MySQL storage engine interface has taken a
minimalistic approach to data definition language. The only natively
supported operations were CREATE TABLE, DROP
TABLE and RENAME TABLE. Consider the following
example:
CREATE TABLE t(a INT);
INSERT INTO t VALUES (1),(2),(3);
CREATE INDEX a ON t(a);
DROP TABLE t;
The CREATE INDEX statement would be executed roughly
as follows:
CREATE TABLE temp(a INT, INDEX(a));
INSERT INTO temp SELECT * FROM t;
RENAME TABLE t TO temp2;
RENAME TABLE temp TO t;
DROP TABLE temp2;
You could imagine that the database could crash when copying all
rows from the original table to the new one. For example, it could run
out of file space. Then, on restart, InnoDB would roll back the
huge INSERT transaction. To fix things a little, a hack
was added to ha_innobase::write_row for committing the
transaction every 10,000 rows.
Still, it was frustrating that even a simple DROP
INDEX would make the table unavailable for modifications for a
long time.
Fast Index Creation in the InnoDB Plugin of MySQL 5.1
MySQL 5.1 introduced a new interface for CREATE INDEX
and DROP INDEX. The old table-copying approach can still
be forced by SET old_alter_table=0.
This interface is used in MySQL 5.5 and in the InnoDB Plugin for
MySQL 5.1. Apart from the ability to do a quick DROP
INDEX, the main advantage is that InnoDB will execute a
merge-sort algorithm before inserting the index records into each
index that is being created. This should speed up the insert into the
secondary index B-trees and potentially result in a better B-tree fill
factor.
The 5.1 ALTER TABLE interface was not perfect. For
example, DROP FOREIGN KEY still invoked the table
copy. Renaming columns could conflict with InnoDB foreign key
constraints. Combining ADD KEY and DROP KEY
in ALTER TABLE was problematic and not atomic inside the
storage engine.
The ALTER TABLE interface in MySQL 5.6
The ALTER TABLE storage engine interface was
completely rewritten in MySQL 5.6. Instead of introducing a method
call for every conceivable operation, MySQL 5.6 introduced a handful
of methods, and data structures that keep track of the requested
changes.
In MySQL 5.6, online ALTER TABLE operation can be
requested by
specifying LOCK=NONE. Also LOCK=SHARED
and LOCK=EXCLUSIVE are available. The old-style table
copying can be requested by ALGORITHM=COPY. That one will
require at least LOCK=SHARED. From the InnoDB point of
view, anything that is possible with LOCK=EXCLUSIVE is
also possible with LOCK=SHARED.
Most ALGORITHM=INPLACE operations inside InnoDB can be
executed online (LOCK=NONE). InnoDB will always require
an exclusive table lock in two phases of the operation. The execution
phases are tied to a number of methods:
handler::check_if_supported_inplace_alter Checks if the storage engine can perform all requested operations,
and if so, what kind of locking is needed. handler::prepare_inplace_alter_table InnoDB uses this method to set up the data dictionary cache for
upcoming CREATE INDEX operation. We need stubs
for the new indexes, so that we can keep track of changes to the table
during online index creation. Also, crash recovery would drop any
indexes that were incomplete at the time of the crash. handler::inplace_alter_table In InnoDB, this method is used for creating secondary indexes or
for rebuilding the table. This is the ‘main’ phase that can be executed
online (with concurrent writes to the table). handler::commit_inplace_alter_table This is where the operation is committed or rolled back. Here,
InnoDB would drop any indexes, rename any columns, drop or add
foreign keys, and finalize a table rebuild or index creation. It
would also discard any logs that were set up for online index
creation or table rebuild.
The prepare and commit phases require an
exclusive lock, blocking all access to the table. If MySQL times out
while upgrading the table meta-data lock for the commit
phase, it will roll back the ALTER TABLE operation.
In MySQL 5.6, data definition language operations are still not
fully atomic, because the data dictionary is split. Part of it is
inside InnoDB data dictionary tables. Part of the information is only
available in the *.frm file, which is not covered by any
crash recovery log. But, there is a single commit phase inside the
storage engine.
Online Secondary Index Creation
It may occur that an index needs to be created on a new column to
speed up queries. But, it may be unacceptable to block modifications
on the table while creating the index.
It turns out that it is conceptually not so hard to support online
index creation. All we need is some more execution phases:
Set up a stub for the index, for logging changes.
Scan the table for index records.
Sort the index records.
Bulk load the index records.
Apply the logged changes.
Replace the stub with the actual index.
Threads that modify the table will log the operations to the logs
of each index that is being created. Errors, such as log overflow or
uniqueness violations, will only be flagged by the ALTER
TABLE thread. The log is conceptually similar to
the InnoDB change
buffer.
The bulk load of index records will bypass record locking. We still
generate redo log for writing the index pages. It would suffice to log
page allocations only, and to flush the index pages from the buffer
pool to the file system upon completion.
Native ALTER TABLE
Starting with MySQL 5.6, InnoDB supports most ALTER
TABLE operations natively. The notable exceptions are changes
to the column type, ADD FOREIGN KEY except
when foreign_key_checks=0, and changes to tables that
contain FULLTEXT indexes.
The keyword ALGORITHM=INPLACE is somewhat misleading,
because certain operations cannot be performed in-place. For example,
changing the ROW_FORMAT of a table requires a
rebuild.
Online operation (LOCK=NONE) is not allowed in the
following cases:
when adding an AUTO_INCREMENT column,
when the table contains FULLTEXT indexes
or a hidden FTS_DOC_ID column, or
when there are FOREIGN KEY constraints referring to
the table, with ON…CASCADE or ON…SET NULL
option.
The FOREIGN KEY limitations are needed, because MySQL
does not acquire meta-data locks on the child or parent tables when
executing SQL statements.
Theoretically, InnoDB could support operations like ADD
COLUMN and DROP COLUMN in-place, by lazily
converting the table to a newer format. This would require that the
data dictionary keep multiple versions of the table definition. For
simplicity, we will copy the entire table, even for DROP
COLUMN.
The bulk copying of the table will bypass record locking and undo
logging. For facilitating online operation, a temporary log will be
associated with the clustered index of table. Threads that modify the
table will also write the changes to the log.
When altering the table, we skip all records that have been marked
for deletion. In this way, we can simply discard any undo log records
that were not yet purged from the original table.
Off-page columns, or BLOBs, are an important consideration. We
suspend the purge of delete-marked records if it would free any
off-page columns from the old table. This is because the BLOBs can be
needed when applying changes from the log. We have special logging for
handling the ROLLBACK of an INSERT that
inserted new off-page columns. This is because the columns will be
freed at rollback.
