Online DDL
===========
The online DDL feature builds on the InnoDB Fast Index Creation feature that is available in MySQL 5.1 and MySQL 5.5. The InnoDB Fast Index Creation feature optimized CREATE INDEX and DROP INDEX to avoid table-copying behavior. The online DDL feature, introduced in MySQL 5.6, enhances many other types of ALTER TABLE operations to avoid table copying or blocking DML operations while DDL is in progress, or both.
The online DDL feature has the following benefits:
- It improves responsiveness and availability in busy production environments, where making a table unavailable for minutes or hours is not practical.
- It lets you adjust the balance between performance and concurrency during the DDL operation, by choosing whether to block access to the table entirely (LOCK=EXCLUSIVE clause), allow queries but not DML (LOCK=SHARED clause), or allow full query and DML access to the table (LOCK=NONE clause). When you omit the LOCK clause or specify LOCK=DEFAULT, MySQL allows as much concurrency as possible depending on the type of operation.
- Performing changes in-place where possible, rather than creating a new copy of the table, avoids temporary increases in disk space usage and I/O overhead associated with copying the table and reconstructing secondary indexes.
Earlier:
--------
Many ALTER TABLE operations worked by creating a new, empty table defined with the requested table options and indexes, then copying the existing rows to the new table one-by-one, updating the indexes as the rows were inserted. After all rows from the original table were copied, the old table was dropped and the copy was renamed with the name of the original table.
Now:
-----
MySQL 5.6 enhances many other types of ALTER TABLE operations to avoid copying the table. Another enhancement allows SELECT queries and INSERT, UPDATE, and DELETE (DML) statements to proceed while the table is being altered. This combination of features is now known as online DDL. This mechanism also means that you can generally speed the overall process of creating and loading a table and associated indexes by creating the table without any secondary indexes, then adding the secondary indexes after the data is loaded. The online DDL enhancements in MySQL 5.6 improve many DDL operations that formerly required a table copy or blocked DML operations on the table, or both.
Online DDL improves several aspects of MySQL operation, such as performance, concurrency, availability, and scalability:
- Because queries and DML operations on the table can proceed while the DDL is in progress, applications that access the table are more responsive. Reduced locking and waiting for other resources throughout the MySQL server leads to greater scalability, even for operations not involving the table being altered.
- For in-place operations, by avoiding the disk I/O and CPU cycles to rebuild the table, you minimize the overall load on the database and maintain good performance and high throughput during the DDL operation.
- For in-place operations, because less data is read into the buffer pool than if all the data was copied, you avoid purging frequently accessed data from memory, which formerly could cause a temporary performance dip after a DDL operation.
Algorithm Used:
---------------------
ALGORITHM=INPLACE
ALGORITHM=COPY
4 aspects of Online DDL:
--------------------------------
- Algorithm=INPLACE (Preferred Value=Yes)
- Copies Table (Preferred Value=No)
- Allows Concurrent DML (Preferred Value=Yes)
- Allows Concurrent Query (Preferred Value=Yes)
Note:
- Any ALTER TABLE operation run with the ALGORITHM=COPY clause prevents concurrent DML operations. Concurrent queries are still allowed. That is, a table-copying operation always includes at least the concurrency restrictions of LOCK=SHARED (allow queries but not DML).
- Some other ALTER TABLE operations allow concurrent DML but still require a table copy.
:: Adding, dropping, or reordering columns.
:: Changing the nullable status for a column.
:: Adding or dropping a primary key.
:: OPTIMIZE TABLE
:: Rebuilding a table with the FORCE option
References:
https://dev.mysql.com/doc/refman/5.6/en/innodb-online-ddl.html
https://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-overview.html
https://dev.mysql.com/doc/refman/5.6/en/glossary.html#glos_online_ddl
https://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-concurrency.html
===========
The online DDL feature builds on the InnoDB Fast Index Creation feature that is available in MySQL 5.1 and MySQL 5.5. The InnoDB Fast Index Creation feature optimized CREATE INDEX and DROP INDEX to avoid table-copying behavior. The online DDL feature, introduced in MySQL 5.6, enhances many other types of ALTER TABLE operations to avoid table copying or blocking DML operations while DDL is in progress, or both.
The online DDL feature has the following benefits:
- It improves responsiveness and availability in busy production environments, where making a table unavailable for minutes or hours is not practical.
- It lets you adjust the balance between performance and concurrency during the DDL operation, by choosing whether to block access to the table entirely (LOCK=EXCLUSIVE clause), allow queries but not DML (LOCK=SHARED clause), or allow full query and DML access to the table (LOCK=NONE clause). When you omit the LOCK clause or specify LOCK=DEFAULT, MySQL allows as much concurrency as possible depending on the type of operation.
- Performing changes in-place where possible, rather than creating a new copy of the table, avoids temporary increases in disk space usage and I/O overhead associated with copying the table and reconstructing secondary indexes.
Earlier:
--------
Many ALTER TABLE operations worked by creating a new, empty table defined with the requested table options and indexes, then copying the existing rows to the new table one-by-one, updating the indexes as the rows were inserted. After all rows from the original table were copied, the old table was dropped and the copy was renamed with the name of the original table.
Now:
-----
MySQL 5.6 enhances many other types of ALTER TABLE operations to avoid copying the table. Another enhancement allows SELECT queries and INSERT, UPDATE, and DELETE (DML) statements to proceed while the table is being altered. This combination of features is now known as online DDL. This mechanism also means that you can generally speed the overall process of creating and loading a table and associated indexes by creating the table without any secondary indexes, then adding the secondary indexes after the data is loaded. The online DDL enhancements in MySQL 5.6 improve many DDL operations that formerly required a table copy or blocked DML operations on the table, or both.
Online DDL improves several aspects of MySQL operation, such as performance, concurrency, availability, and scalability:
- Because queries and DML operations on the table can proceed while the DDL is in progress, applications that access the table are more responsive. Reduced locking and waiting for other resources throughout the MySQL server leads to greater scalability, even for operations not involving the table being altered.
- For in-place operations, by avoiding the disk I/O and CPU cycles to rebuild the table, you minimize the overall load on the database and maintain good performance and high throughput during the DDL operation.
- For in-place operations, because less data is read into the buffer pool than if all the data was copied, you avoid purging frequently accessed data from memory, which formerly could cause a temporary performance dip after a DDL operation.
Algorithm Used:
---------------------
ALGORITHM=INPLACE
ALGORITHM=COPY
4 aspects of Online DDL:
--------------------------------
- Algorithm=INPLACE (Preferred Value=Yes)
- Copies Table (Preferred Value=No)
- Allows Concurrent DML (Preferred Value=Yes)
- Allows Concurrent Query (Preferred Value=Yes)
Note:
- Any ALTER TABLE operation run with the ALGORITHM=COPY clause prevents concurrent DML operations. Concurrent queries are still allowed. That is, a table-copying operation always includes at least the concurrency restrictions of LOCK=SHARED (allow queries but not DML).
- Some other ALTER TABLE operations allow concurrent DML but still require a table copy.
:: Adding, dropping, or reordering columns.
:: Changing the nullable status for a column.
:: Adding or dropping a primary key.
:: OPTIMIZE TABLE
:: Rebuilding a table with the FORCE option
References:
https://dev.mysql.com/doc/refman/5.6/en/innodb-online-ddl.html
https://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-overview.html
https://dev.mysql.com/doc/refman/5.6/en/glossary.html#glos_online_ddl
https://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-concurrency.html
No comments:
Post a Comment