ONLINE DDL

MySQL

テーブルの変更がオンライン(更新処理も問題無く実行出来るか?)を確認

ALGORITHM=INPLACE ≒ 読み込みも書き込みもオンラインで処理可能

ALGORITHM=COPY ≒ 参照のみが可能。(old_alter_table=1)

裏で対象テーブルをCOPYしてRENAMEしているので一時的に2倍のディスクサイズが一必要です。

root@localhost [CONFIRM]> desc t1;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| c1    | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)


root@localhost [CONFIRM]> alter table t1 ALGORITHM=INPLACE, add column comment varchar(100);
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0


root@localhost [CONFIRM]> desc t1;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| c1      | int(11)      | YES  |     | NULL    |       |
| comment | varchar(100) | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)


root@localhost [CONFIRM]> alter table t1 ALGORITHM=INPLACE, change column comment comment char(100);
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.
root@localhost [CONFIRM]> 



root@localhost [CONFIRM]> alter table t1 ALGORITHM=INPLACE, change column comment comment varchar(10);
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.

root@localhost [CONFIRM]> alter table t1 ALGORITHM=COPY, change column comment comment varchar(10);
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

root@localhost [CONFIRM]> desc t1;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| c1      | int(11)     | YES  |     | NULL    |       |
| comment | varchar(10) | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

メモ:INPLACE操作のDML ログ領域使用量は、innodb_online_alter_log_max_size によって制御されます。

例)ALTER TABLE中の参照処理はブロックされません

MySQLではDMLがブロックされない。
MySQL with Alter Table and Select

例)ALTER TABLE中にデータの更新処理も問題ありません。

 MySQL with Alter Table and Insert
MySQL with Alter Table and Insert

参照:15.12.1 オンライン DDL 操作

ONLINE DDL
MySQLにおけるオンラインDDL

どのようなALTER TABLE処理の場合に処理がブロックされるかは上記のテーブルを参考にしてください。また、上記のMySQLのALTERテーブルの処理の前にSTART TRANSACTIONでトランザクションを指定していますが、MySQLの場合は、DDLはトランザクション外の処理になっています。TRUNCATEも同じで、トランザクション外の処理の為、PostgreSQLの様にTRUNCATEをロールバックしたりする事は出来ません。

PostgreSQL

”ALTER TABLEは既存のテーブルの定義を変更します。 以下のようにいくつかの副構文があります。 要求されるロックレベルはそれぞれの副構文によって異なることに注意してください。 特に記述がなければACCESS EXCLUSIVEロックを取得します。 複数のサブコマンドが使われるときは、それらのサブコマンドが要求するうち、もっとも高いレベルのロックを取得します。”

MySQLとは異なり、オンラインDDLや列の間に追加の列を追加したりする事が出来ません。

ALTER TABLEを実行中の間はトランザクションが終了するまでは、SELECTを含むDMLは待たされる事になります。

参照:ALTER TABLE — テーブル定義を変更する

例:以下では、Alter Table中に参照処理が待たされています。

ALTER TABLE T1 ADD COLUMN who varchar(50);
Alter Table中の参照処理

Alter TableをCommitした時に参照処理も終了しています。ここでは、1行しかないテーブル参照に3分弱かかっています。こちらの例では、暗黙のトランザクションを利用していませんが、実際に大きなテーブルに対してAlter Tableを実行する場合には注意や工夫が必要ですね。

Select Wait During Alter Table
Select Wait During Alter Table

Create IndexとDrop Indexを実行した場合の挙動

■ Create Indexによるインデックスの追加

POC=# begin transaction;
BEGIN
POC=*# create index idx_trains_name on trains(name);
CREATE INDEX
POC=*# commit;
COMMIT

上記処理中は、参照は可能ですがInsert等の更新処理は待たされます。(以下、コミットするまで45秒Insert処理が待たされています。)

POC=# select * from trains limit 1 offset 100;
   id    |              name
---------+--------------------------------
 1100106 | sample data for unlogged table
(1 row)

Time: 0.663 ms
POC=# insert into trains(name) values('cresate index');
INSERT 0 1
Time: 45221.839 ms (00:45.222)

■ Drop IndexによるIndexの削除処理

POC=# begin transaction;
BEGIN
POC=*# drop index idx_trains_name;
DROP INDEX
POC=*# commit;
COMMIT

上記処理中は、参照も待たされます。(以下、コミットするまで30秒Insert処理が待たされています。)

POC=# select * from trains limit 1 offset 100;
   id    |              name
---------+--------------------------------
 1100106 | sample data for unlogged table
(1 row)

Time: 30793.880 ms (00:30.794)
POC=#

備考:Index作成中に書き込みを処理可能にする為には以下のオプションが利用可能との事。(CONS:処理時間とCPU負荷)

POC=*# create index CONCURRENTLY idx_trains_name on trains(name);
ERROR:  CREATE INDEX CONCURRENTLY cannot run inside a transaction block
POC=!# rollback;
ROLLBACK
POC=# create index CONCURRENTLY idx_trains_name on trains(name);
CREATE INDEX
POC=#

参照:インデックスの同時作成 (無効なIndexが作成される場合の注意事項があるので必読)

このオプションを使用すると、PostgreSQLは、対象テーブルに対する同時挿入、更新、削除を防止するようなロックを獲得せずにインデックスを作成します。 通常のインデックス作成処理では、完了するまで対象テーブルへの書き込みはできません(読み取りは可能です)。 このオプションを使用する際に注意しなければならない点が複数あります。

カテゴリー:

最近のコメント

表示できるコメントはありません。