ALGORITHM=INPLACE – variable.jp [データベース,パフォーマンス,運用]

オンラインで色々なDDL処理が出来るのは、サービス提供側も、利用者も、DBAとしても
非常に便利な機能かと思います。MySQLは4.1からUnicode対応していたので、
10年弱程利用してますが、Online DDLが利用可能になるまでは、
夜中のユーザーが少ない時間にテーブル定義を変更していたりする事も多々ありました。
MySQL5.6以降のオンラインDDLはそういったメンテナンス対応の方には、非常に便利で有難い機能かと思います。

オンライン処理可能かどうかは、14.11.1. オンライン DDL の概要で確認する事が出来ます。
手元で簡単に確認したい場合は、以下のオプションで処理を指定して確認する事が可能です。

ALGORITHM=INPLACE
ALGORITHM=COPY

インプレース DDL 操作とテーブルコピー DDL 操作のパフォーマンスの比較
オンライン DDL 操作の raw パフォーマンスは、その操作がインプレースで実行されるか、
またはテーブル全体のコピーと再構築が必要かによってほとんど決定されます。
インプレースで実行できる操作の種類や、テーブルコピー操作を行わないための
何らかの要件を確認するには、表14.5「DDL 操作のオンラインステータスのサマリー」を参照してください。

例）これは5.7の場合(varcharサイズ変更)


ALTER TABLE T_ONLINE_DDL ALGORITHM=INPLACE, CHANGE COLUMN text text VARCHAR(255);

※　オンライン DDL のロックオプション
LOCK=EXCLUSIVE　
クエリーと DML 操作の両方がブロックされます。
LOCK=SHARED
DDL 操作では、テーブルへの書き込みがすべてブロックされますが、
そのテーブル内のデータは読み取ることができます。
LOCK=NONE
DDL 操作では、クエリーと並列 DML の両方が許可されます。
LOCK=DEFAULT
LOCK 句が省略された DDL 操作では、MySQL はその種類の操作で
使用可能なもっとも低いレベルのロックを使用することにより、
可能な場合は常に並列クエリー、DML、またはその両方を許可します。

参照：
14.11.2. オンライン DDL でのパフォーマンスと並列性に関する考慮事項

参照：MySQL5.7におけるオンライン処理の拡張
Online ALTER TABLE Enhance Varchar Size

オンライン DDL
DDL (主に ALTER TABLE) 操作中の InnoDB テーブルのパフォーマンス、並列性、および可用性を改善する機能。
詳細は、操作の種類に応じて異なります。場合によっては、ALTER TABLE の進行中にテーブルを同時に変更できます。この操作はテーブルコピーを行わずに、または特別に最適化されたタイプのテーブルコピーを使用せずに実行できる場合があります。
領域の使用量は、innodb_online_alter_log_max_size 構成オプションで制御されます。

innodb_online_alter_log_max_sizeに関しては、こちらの資料が参考になります。
https://www.percona.com/live/mysql-conference-2014/sites/default/files/slides/Online%20schema%20changes%20for%20maximizing%20uptime.pdf

——————————————————————————————-
抜粋：
14.11. InnoDB とオンライン DDL
——————————————————————————————-
MySQL 5.6 で導入されたオンライン DDL 機能は、ほかのタイプの多くの ALTER TABLE 操作を、
テーブルコピー、DDL が進行中の DML 操作のブロック化、またはその両方を行わないように拡張しています。

オンライン DDL 機能には、次の利点があります。
1)インデックスやカラム定義を変更する場合は常に、テーブルを数分または数時間にわたって使用できなくすることが現実的でないビジー状態の本番環境での応答性と可用性を向上させます。
2)テーブルへのアクセスを完全にブロックするか (LOCK=EXCLUSIVE 句)、クエリーを許可するが、
DML は許可しないか (LOCK=SHARED 句)、またはテーブルへの完全なクエリーおよび DML アクセスを許可するか (LOCK=NONE 句)どうかを選択することによって、DDL 操作中のパフォーマンスと並列性のバランスを調整できます。
3)LOCK 句を省略するか、または LOCK=DEFAULT を指定すると、MySQL は、操作の種類に応じてできるだけ高い並列性を許可します。
4)テーブルの新しいコピーを作成するのではなく、可能な場合はインプレースで変更を行うことによって、
テーブルのコピーおよびすべてのセカンダリインデックスの再構築のためのディスク領域の使用量やI/O オーバーヘッドの一時的な増加が回避されます。
——————————————————————————————-

本日は、ALTER TABLEにてストレージエンジンをINNODBに変換する場合の挙動について確認してみます。

MySQL 5.6.17 の時点では、ALTER TABLE tbl_name ENGINE=INNODB とALTER TABLE tbl_name FORCE の両方が
オンライン DDL (ALGORITHM=COPY) を使用します。
http://dev.mysql.com/doc/refman/5.6/ja/alter-table.html

ALGORITHM=COPY に関する注意
ALGORITHM=COPY 句で実行される ALTER TABLE 操作はすべて、並列 DML 操作を妨げます。
並列クエリーは、引き続き許可されます。つまり、テーブルコピー操作には常に、
少なくとも LOCK=SHARED (クエリーを許可するが、DML は許可しない) の並列性の制限が含まれます。

初期設定確認


root@localhost [test]> select @@version;
+-------------------------------------------+
| @@version                                 |
+-------------------------------------------+
| 5.6.24-enterprise-commercial-advanced-log |
+-------------------------------------------+
1 row in set (0.00 sec)

root@localhost [test]> show variables like '%old_alter_table%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| old_alter_table | OFF   |
+-----------------+-------+
1 row in set (0.00 sec)

以下、オンラインでの変更可否の確認
INPLACEを明示的に指定して、ストレージエンジンを変換してますが、処理がエラーになっています。
COPYでは問題無く終了しています。LOCKもSHAREDで無ければ許可されません。

root@localhost [USER01]> select TABLE_SCHEMA,TABLE_NAME,ENGINE from information_schema.TABLES where TABLE_SCHEMA = 'USER01' and TABLE_NAME = 'T_STORAGE_CONV';
+--------------+----------------+--------+
| TABLE_SCHEMA | TABLE_NAME     | ENGINE |
+--------------+----------------+--------+
| USER01       | T_STORAGE_CONV | MyISAM |
+--------------+----------------+--------+
1 row in set (0.00 sec)

root@localhost [USER01]> ALTER TABLE T_STORAGE_CONV ALGORITHM=INPLACE, ENGINE=InnoDB;
ERROR 1845 (0A000): ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY.　

root@localhost [test]> ALTER TABLE T_STORAGE_CONV ALGORITHM=COPY,LOCK=NONE,ENGINE=InnoDB;
ERROR 1846 (0A000): LOCK=NONE is not supported. Reason: COPY algorithm requires a lock. Try LOCK=SHARED.

root@localhost [USER01]> ALTER TABLE T_STORAGE_CONV ALGORITHM=COPY, ENGINE=InnoDB;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

root@localhost [USER01]> select TABLE_SCHEMA,TABLE_NAME,ENGINE from information_schema.TABLES where TABLE_SCHEMA = 'USER01' and TABLE_NAME = 'T_STORAGE_CONV';
+--------------+----------------+--------+
| TABLE_SCHEMA | TABLE_NAME     | ENGINE |
+--------------+----------------+--------+
| USER01       | T_STORAGE_CONV | InnoDB |
+--------------+----------------+--------+
1 row in set (0.00 sec)

root@localhost [USER01]>

SHAREDロックになってしまう処理
データを3件入れて、変換してみると3行程影響を受けている事が確認出来ます。
こにより、テーブルがCOPYされている事が確認出来ます。

root@localhost [test]> CREATE TABLE `T_STORAGE_CONV` (
    ->   `ID` int(11) NOT NULL AUTO_INCREMENT,
    ->   `MEMO` char(255) DEFAULT NULL,
    ->   PRIMARY KEY (`ID`)
    -> ) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.01 sec)

root@localhost [test]> insert into T_STORAGE_CONV(MEMO) values('TEST ONLINE DDL 1');
Query OK, 1 row affected (0.00 sec)

root@localhost [test]> insert into T_STORAGE_CONV(MEMO) values('TEST ONLINE DDL 2');
Query OK, 1 row affected (0.00 sec)

root@localhost [test]> insert into T_STORAGE_CONV(MEMO) values('TEST ONLINE DDL 3');
Query OK, 1 row affected (0.00 sec)

root@localhost [test]> ALTER TABLE T_STORAGE_CONV ALGORITHM=COPY,LOCK=SHARED,ENGINE=InnoDB;
Query OK, 3 rows affected (0.04 sec)
Records: 3  Duplicates: 0  Warnings: 0

root@localhost [test]>

Secondaryインデックスの追加や列の追加はONLINE出来る。
rows affectedの値が0であることからも確認出来る。

root@localhost [test]> ALTER TABLE T_STORAGE_CONV ALGORITHM=INPLACE,ADD INDEX IDX_MEMO(MEMO);                       
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

root@localhost [test]> show create table T_STORAGE_CONV\G
*************************** 1. row ***************************
       Table: T_STORAGE_CONV
Create Table: CREATE TABLE `T_STORAGE_CONV` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `MEMO` char(100) DEFAULT NULL,
  PRIMARY KEY (`ID`),
  KEY `IDX_MEMO` (`MEMO`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4
1 row in set (0.01 sec)

root@localhost [test]> desc T_STORAGE_CONV;
+-------+-----------+------+-----+---------+----------------+
| Field | Type      | Null | Key | Default | Extra          |
+-------+-----------+------+-----+---------+----------------+
| ID    | int(11)   | NO   | PRI | NULL    | auto_increment |
| MEMO  | char(100) | YES  | MUL | NULL    |                |
+-------+-----------+------+-----+---------+----------------+
2 rows in set (0.01 sec)

root@localhost [test]> ALTER TABLE T_STORAGE_CONV ALGORITHM=INPLACE,
    -> ADD CATEGORY varchar(10) after ID;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

root@localhost [test]> desc T_STORAGE_CONV;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| ID       | int(11)     | NO   | PRI | NULL    | auto_increment |
| CATEGORY | varchar(10) | YES  |     | NULL    |                |
| MEMO     | char(100)   | YES  | MUL | NULL    |                |
+----------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

root@localhost [test]>

マニュアルにも載っているが、もしオンラインで処理可能か？もしくはDMLがブロックされるかどうか不明な場合は、
上記のようにオプションを付けて確認して見る事も可能です。

参考：　14.11.1. オンライン DDL の概要

Tag Archives: Algorithm=inplace

MySQLにおけるオンラインDDL機能拡張