Go to Qiita Advent Calendar 2024 Top
LoginSignup
12
2

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 1 year has passed since last update.

Craft EggAdvent Calendar 2021

Day 1
@shirai_suguru(白井 英)

Amazon Aurora(MySQL)のALTER文のLockを調べてみた

Last updated at Posted at 2021-11-30

本記事は、Craft Egg Advent Calendar 2021の12/1の記事です。

はじめに

MySQLを運用していく中で、ALTER文を流したいタイミングがたまにあります。
しかしLockがかかって大変なことになるのでは？とおもい、ALTER文ながすことをためらうことばかりでした。
Amazon Aurora MySQL3(MySQL8.0互換）がリリースされたこともあり、最近のMySQLのALTER文の改善をみていこうとおもいます。

調査対象のAmazon Auroa(MySQL)のバージョン

以下の３つのバージョンです。

###Engine version

(a) 5.6.mysql_aurora.1.23.4

(b) 5.7.mysql_aurora.2.10.1

(c) 8.0.mysql_aurora.3.01.0

調査するALTER文(LockがかかりそうなDDL）

次の４つのパターンを調査します。

(1)　カラム追加

次のようのなSQLです。

alter table `テーブル名` add `カラム名` '型';

(2) インデックスの作成

次のようなSQLです。

CREATE INDEX `インデックス名` ON `テーブル名` (`カラム名`) ;

(3) VARACHAR型のカラムサイズの伸長

次のようなSQLです。

ALTER TABLE `テーブル名` CHANGE COLUMN `カラム名`  `カラム名`  VARCHAR(6);

(4) パーティションの追加

次のようなSQLです(RANGE PARTITIONを想定)

ALTER TABLE `テーブル名` ADD PARTITION (PARTITION `パーティション名` VALUES LESS THAN (5000)) ;

選出理由

ALTER文を流そうと思った時にためらいそうなもののなかで、MySQLのバージョンがあがるにつれ、改善されたALTER文を選んでいます。

それぞれの期待する結果は、次の通りです。

MySQL5.6 MySQL5.7 MySQL8.0
(1)カラム追加 ○ ○ ○
(2)インデックスの作成 ○ ○ ○
(3)VARACHAR型のカラムサイズの伸長 × ○ ○
(4)パーティションの追加 × × ○

※○はLockがかからないことを示します（Meta Data Lockをのぞく）

####参考
・MySQL5.6に関してのLock
・MySQL5.7に関してのLock
・MySQL8.0に関してのLock

テスト対象のテーブルの準備

今回検証するにあたり、データの準備をしていきます。

公式ドキュメントのオンライン DDL 実験のためのスキーマ設定コード
の手順にしたがってテーブルとデータを用意します。

せっかく検証するのであれば、データ量がおおいほうがいいよね、とおもい（後々の後悔しましたが）
上記のドキュメントを参考に
big_table というテーブルに 122,486,784 レコード（約1億レコード）データをいれました。(mysqldumpの出力結果が 22GB ほどになりました・・・）

big_table の show create table の出力は次のとおりです。

Create Table: CREATE TABLE `big_table` (
`TABLE_CATALOG` varchar(512) CHARACTER SET utf8 NOT NULL DEFAULT '',
`TABLE_SCHEMA` varchar(64) CHARACTER SET utf8 NOT NULL DEFAULT '',
`TABLE_NAME` varchar(64) CHARACTER SET utf8 NOT NULL DEFAULT '',
`COLUMN_NAME` varchar(64) CHARACTER SET utf8 NOT NULL DEFAULT '',
`ORDINAL_POSITION` bigint(21) unsigned NOT NULL DEFAULT '0',
`COLUMN_DEFAULT` longtext CHARACTER SET utf8,
`IS_NULLABLE` varchar(3) CHARACTER SET utf8 NOT NULL DEFAULT '',
`DATA_TYPE` varchar(64) CHARACTER SET utf8 NOT NULL DEFAULT '',
`CHARACTER_MAXIMUM_LENGTH` bigint(21) unsigned DEFAULT NULL,
`CHARACTER_OCTET_LENGTH` bigint(21) unsigned DEFAULT NULL,
`NUMERIC_PRECISION` bigint(21) unsigned DEFAULT NULL,
`NUMERIC_SCALE` bigint(21) unsigned DEFAULT NULL,
`DATETIME_PRECISION` bigint(21) unsigned DEFAULT NULL,
`CHARACTER_SET_NAME` varchar(32) CHARACTER SET utf8 DEFAULT NULL,
`COLLATION_NAME` varchar(32) CHARACTER SET utf8 DEFAULT NULL,
`COLUMN_TYPE` longtext CHARACTER SET utf8 NOT NULL,
`COLUMN_KEY` varchar(3) CHARACTER SET utf8 NOT NULL DEFAULT '',
`EXTRA` varchar(30) CHARACTER SET utf8 NOT NULL DEFAULT '',
`PRIVILEGES` varchar(80) CHARACTER SET utf8 NOT NULL DEFAULT '',
`COLUMN_COMMENT` varchar(1024) CHARACTER SET utf8 NOT NULL DEFAULT '',
`id` bigint(21) unsigned NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

実際に検証

それでは、データの準備もできたので、実際にLockの有無を検証していきます。
データ量がおおくなってしまったので、検証につかうインスタンスは、 db.r5.xlarge を使用しています。

また、Lockがかかっているか、いないかをわかりやすくするために、INSERT文を１秒毎にbig_tableに対してながしていきます。

(1)　カラム追加 の検証

実際に流すのは次のSQLです。

alter table big_table add addcolumn int ,LOCK=NONE, ALGORITHM=INPLACE;

(a) 5.6.mysql_aurora.1.23.4

INSERT文をながしながら、カラム追加を実行している様子です。
（左：INSERT文、右：カラム追加）
MySQL5_6_ADD_COLUMN.gif
こちらLockかかることなく実行できました。

(b) 5.7.mysql_aurora.2.10.1

INSERT文をながしながら、カラム追加を実行している様子です。
（左：INSERT文、右：カラム追加）
MySQL5_7_ADD_COLUMN.gif
こちらLockかかることなく実行できました。

(c) 8.0.mysql_aurora.3.01.0

INSERT文をながしながら、カラム追加を実行している様子です。
（左：INSERT文、右：カラム追加）
MySQL8_ADD_COLUMN.gif
こちらLockかかることなく実行できました。

(2) インデックスの作成 の検証

実際に流すのは次のSQLです。

CREATE INDEX idx_1 ON big_table (`ORDINAL_POSITION`) ;

(a) 5.6.mysql_aurora.1.23.4

INSERT文をながしながら、インデックスの作成をしている様子です。
（左：INSERT文、右：インデックスの作成）
MySQL5_6_CREATE_INDEX.gif
こちらLockかかることなく実行できました。

(b) 5.7.mysql_aurora.2.10.1

INSERT文をながしながら、インデックスの作成をしている様子です。
（左：INSERT文、右：インデックスの作成）
MySQL5_7_CREATE_INDEX.gif
こちらLockかかることなく実行できました。

(c) 8.0.mysql_aurora.3.01.0

INSERT文をながしながら、インデックスの作成をしている様子です。
（左：INSERT文、右：インデックスの作成）
MySQL8_CREATE_INDEX.gif
こちらLockかかることなく実行できました。

(3) VARACHAR型のカラムサイズの伸長 の検証

実際に流すのは次のSQLです。

alter table big_table modify `TABLE_SCHEMA` varchar(128);

(a) 5.6.mysql_aurora.1.23.4

INSERT文をながしながら、VARCHAR型のカラムサイズの伸長をしている様子です。
（左：INSERT文、右：VARCHAR型のカラムサイズの伸長）
MySQL5_6_ALTER_VARCHAR_EXTEND.gif
想定通り、Lockがかかりました(左のINSERT文がとまっている）。

(b) 5.7.mysql_aurora.2.10.1

INSERT文をながしながら、VARCHAR型のカラムサイズの伸長をしている様子です。
（左：INSERT文、右：VARCHAR型のカラムサイズの伸長）
MySQL5_7_ALTER_VARCHAR_EXTEND.gif
！なんと、想定外に、Lockがかかってしまいました。
理由は後述します。(※１)

(c) 8.0.mysql_aurora.3.01.0

INSERT文をながしながら、VARCHAR型のカラムサイズの伸長をしている様子です。
（左：INSERT文、右：VARCHAR型のカラムサイズの伸長）
MySQL8_ALTER_VARCHAR_EXTEND.gif
！なんと、想定外に、Lockがかかってしまいました。
こちらも理由は後述します。(※１)

※１ Lockがかかってしまった理由

正確な理由はわからなかったのですが、Lockがかからず実行できるケースもありました。
そもそもまず、SQLの見直しからです。

ALTER TABLE small_table CHANGE COLUMN `IS_NULLABLE`  `IS_NULLABLE`  VARCHAR(6), ALGORITHM=INPLACE, LOCK=NONE;

こちらのSQLはLockがかからず実行できました（テーブル名に注目）。
実は、テーブルのレコード件数が少ないときは（4000レコード）、Lockがかららず実行できたのですが、
今回の検証用のテーブル(1億レコード）では、エラーになってしまいました。
マニュアルをよむとVARCHARの伸長には、制約があることがわかります。
レコード件数が増えたことによって、制約にひっかかっていそうです。

(4) パーティションの追加　の検証

実際に流すのは次のSQLです。

事前に次のSQLを実行してパーティションを作成します。

ALTER TABLE `small_table`  PARTITION BY RANGE (`id`)  (
PARTITION p1000 VALUES LESS THAN (1000),
PARTITION p4000 VALUES LESS THAN (4000)
) ;

その後、次のパーティションの追加のSQLを実行して検証します。

ALTER TABLE small_table LOCK=NONE, ALGORITHM=INPLACE, ADD PARTITION (PARTITION p5000 VALUES LESS THAN (5000)) ;

(a) 5.6.mysql_aurora.1.23.4

INSERT文をながしながら、パーティションを追加している様子です。
（左：INSERT文、右：パーテシションの追加）
MySQL5_6_ADD_PARTION_FAIL.gif
構文エラーとなり、SQLが実行できませんでした（想定通り）。

(b) 5.7.mysql_aurora.2.10.1

INSERT文をながしながら、パーティションを追加している様子です。
（左：INSERT文、右：パーテシションの追加）
MySQL5_7_ADD_PARTITION_FAIL.gif
こちらも、サポートしていないLockというエラーがでて、実行できませんでした（想定通り）。

(c) 8.0.mysql_aurora.3.01.0

INSERT文をながしながら、パーティションを追加している様子です。
（左：INSERT文、右：パーテシションの追加）
MySQL8_ADD_PARTION_RIGHT.gif
無事、構文エラーになることもなく、Lockもかかることもなく、実行できました。

補足

検証前は、マニュアルを読んで、次のSQL（パーティションの作成）がLockなしに実行できるものだと思っていました。

ALTER TABLE `small_table` ALGORITHM=INPLACE PARTITION BY RANGE (`id`)  (
PARTITION p10 VALUES LESS THAN (10),
PARTITION pmax VALUES LESS THAN MAXVALUE
) ;

しかし、サポートされているのは、あくまで ADD PARTITION のため、上記SQLは構文エラーとなります。

まとめ

実際に検証した結果をまとめると次のようになります。

MySQL5.6 MySQL5.7 MySQL8.0
(1)カラム追加 ○ ○ ○
(2)インデックスの作成 ○ ○ ○
(3)VARACHAR型のカラムサイズの伸長 × △ △
(4)パーティションの追加 × × ○

△：実行できる場合もある（制約によって実行に失敗する）

おわりに

昔からのMySQLの先入観で、基本的にALTER文はLockがかかってしまうとおもっていました。
しかし今回あらためて、マニュアルを見て、LockのかからないALTER文が増えていることに気づきました。
実際に実行してみると、Lockは、かからず、MySQLに対する先入観が覆されました。

また、新しいバージョンでLockがかからないALTER文は増えているのは検証を進める中でわかったのですが、
制約があるために、使えそうな機会は狭そうというのが正直なところです。

今回の検証が皆様の役に立てればと思います。

明日は@ishiguro_takuyaの記事です！

12
2
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
12
2

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?