🦭

PostgreSQLの愛好家がMySQL 5.7の大規模なテーブルにカラムを追加しようとした話

kasumi

2023/12/23に公開

こんにちは！アルダグラムでエンジニアをしている森下霞です。

本記事は株式会社アルダグラム Advent Calendar 2023 23日目の記事です。

私は10年間のソフトウェア開発経験を持っており、そのうち6年間はPostgreSQLを使用していました。

前々職では、PostgreSQL が強く推奨され、開発者はみんなそれの設定と工夫を学びました。
その経験を持つ私は、アルダグラムで「非常に大きなテーブルに新しいカラムを追加したいが、注意点はあるか」という課題が出たとき、すぐに PostgreSQL で得た知識を共有し、それによりマイグレーションがサービスに影響を与えずに迅速に実行できると考えていました。

しかし、MySQL 5.7 は異なる考え方を持っていました。

背景

どうして大きいテーブルにカラム追加時に注意すべきかというと、新しいカラムの追加はテーブルの全行を更新することがあり、これは非常に時間がかかるためです。さらに、この操作はテーブルをロックする可能性もあり、その間、テーブルは読み書きできなくなることがあります。したがって、この操作は非常に注意が必要です。

この記事でいう「大きいテーブル」とは、100-500万行程度のものを指します。

実行しようとしたマイグレーションの例は以下になります。

ALTER TABLE very_big_table ADD COLUMN a_new_column INT(10) UNSIGNED NOT NULL DEFAULT 0 AFTER some_other_column, ALGORITHM=INPLACE, LOCK=NONE;

PostgreSQL から持ってきた知識

大きいテーブルにカラム追加すると伴う全列の更新とテーブルをロックを避けるのに、メタデータしか更新しないデフォルトバリューを用いないALTER TABLEをし、その次に新列にデフォルト値をつけるようにSET DEFAULTを実行します。次に、スクリプトで細かめのトランザクションで既存の列を更新します。全部が更新したら、SET NOT NULLを行います。

考慮事項

しかし、その知識は2018年時点だったみたいです。PostgreSQL 11 以降、ALTER TABLE文を使用して列に定数のデフォルト値を追加しても、テーブルの各行を更新する必要はもはやありません。代わりに、デフォルト値は次に行にアクセスされる際に返され、テーブルが書き換えられる際に適用されます。そのため、大規模なテーブルでもALTER TABLE文が非常に高速に実行されます。

ただし、デフォルト値が揮発性である場合（例：clock_timestamp()）、各行はALTER TABLEが実行された時点で計算された値で更新する必要があります。長時間にわたる更新操作を避けるため、特に列を主に非デフォルト値で埋めるつもりであれば、上記のようにデフォルトなしで列を追加し、UPDATEを使用して正しい値を挿入し、その後、以下に説明するように任意のデフォルトを追加することが望ましいかもしれません。

参考：https://www.postgresql.org/docs/current/ddl-alter.html

MySQL 5.7 と Amazon Aurora 2 の事実

MySQL 5.7

MySQL 5.7 では、忙しい本番環境におけるテーブルを数分または数時間利用できなくすることは実用的ではない状況で、「Online DDL」という方式が追加されています。

通常、オンラインDDLを有効にするためには特別な手順は必要ありませんが、確実に古いDDL方式にフォールバックによるテーブルが利用できなくなるリスクを避けるためには、ALTER TABLEステートメントにLOCK=NONE（読み取りと書き込みを許可）やLOCK=SHARED（読み取りを許可）などのCLAUSEを指定します。また、テーブルをコピーするALTER TABLE操作でサーバーの負荷を避けるためには、ALGORITHM=INPLACEを含めます。要求された同時性のレベルが利用できない場合、操作は即座に中止されます。

ALTER TABLE tbl_name ADD PRIMARY KEY (column), ALGORITHM=INPLACE, LOCK=NONE;

参考：https://dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl.html

Amazon Aurora 2 の高速DDL

自社では、MySQL を Amazon Aurora 2 にデプロイされています。

Amazon Aurora 2 では、「高速 DDL」という方式が用意されており、これを使用するとカラムの追加が従来よりも何倍も速く行えます。制限事項として、NULL を許容する列 (デフォルト値を持たない) を、既存テーブルの末尾に追加する場合にのみ使用できるそうです。

ただし、本番環境では高速 DDL を使用することはお勧めされないそうです。

残念でした。

参考：

https://docs.aws.amazon.com/ja_jp/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.Managing.FastDDL.html

https://dba.stackexchange.com/questions/321761/is-it-safe-to-enable-fast-ddl-in-production/321764#321764

実践

では、本番環境からデータをコピーして、マイグレーション調査を行います。

調査項目としては、

マイグレーションにかかる時間
テーブルがロックされてないかどうか

検査ツール

ロック検査

ロックの調査ツールとして、「SHOW ENGINE INNODB STATUS」を使用します。マイグレーションを行いながら、定期的にロックの状態を確認します。

mysql > SHOW ENGINE INNODB STATUS;

特に、"LIST OF TRANSACTIONS FOR EACH SESSION"というセクションで、現在のトランザクションステータスとそのロックの状態が見られます。結果の例：

LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 10615846, ACTIVE 35 sec reading clustered index
mysql tables in use 1, locked 1
0 lock struct(s), heap size 1136, 0 row lock(s)
MySQL thread id 34982, OS thread handle 70369946386400, query id 6948441 10.117.1.187 db_user altering table
ALTER TABLE very_big_table ADD COLUMN a_new_column INT(10) UNSIGNED NOT NULL DEFAULT 0 AFTER some_other_column
Trx read view will not see trx with id >= 10615848, sees < 10615846

負担の模擬

$ bundle exec rails c
# very_big_tableテーブルの模擬負担をかける関数を用意します
def imitate_very_big_table_usage
	# ３分間動きます
	duration = 3 * 60  
	
	start_time = Time.now
	
	# 結果を保存するファイルを用意します
	file = File.open("benchmark_results_#{start_time}.txt", "a")
	
	loop do
		# 実行時間を測ります
	  execution_time = Benchmark.realtime do
			# ランダムの行を取り出します
	    row_to_copy = VeryBigTable.where(id: rand(1500000)).first
	    next unless row_to_copy
			# 行のコピーを作成します
	    new_row = VeryBigTable.new(row_to_copy.attributes)
	    new_row.id = nil
	    new_row.uuid = SecureRandom.uuid
	    new_row.note = "copy from #{new_row} from stress-test"

			# DBに追加します
	    new_row.save!

			# 更新します
	    VeryBigTable.where(uuid: new_row.uuid).update_all(note: "updated note copy from #{new_row.uuid} from stress-test")
			# 削除します
	    VeryBigTable.where(uuid: new_row.uuid).delete_all
	  end

		# 時間をログします
	  file.puts "Iteration: #{Time.now}, Execution Time: #{execution_time} seconds"
	
		# ３分間経っていないまでに実行を続きます
	  elapsed_time = Time.now - start_time
	  break if elapsed_time >= duration
	
		#　リクエストを１秒ごとに送ります
	  sleep 1  # Sleep for 1 seconds before checking again
	end
	
	# ファイルを閉じます
	file.close
end

# マイグレーションを行いながら、こちらで実行します
imitate_very_big_table_usage

負担なしの追加検証

そのままやってみる

ALTER TABLE very_big_table ADD COLUMN a_new_column INT(10) UNSIGNED NOT NULL DEFAULT 0 AFTER some_other_column, ALGORITHM=INPLACE, LOCK=NONE;

実行時間：1 min 14.1 sec

AFTER idにカラム追加

カラムの位置によってマイグレーションの実行時間が変わるかどうかの確認です。

ALTER TABLE very_big_table ADD COLUMN a_new_column INT(10) UNSIGNED NOT NULL DEFAULT 0 AFTER id, ALGORITHM=INPLACE, LOCK=NONE;

実行時間：1 min 12.01 sec

AFTER some_other_columnのクエリと特に変わりません。

AFTERなしでカラム追加

ALTER TABLE very_big_table ADD COLUMN a_new_column INT(10) UNSIGNED NOT NULL DEFAULT 0, ALGORITHM=INPLACE, LOCK=NONE;

実行時間：1 min 11.75 sec

AFTER some_other_columnのクエリと特に変わりません。

ALGORITHMとLOCKを指定なし

ALTER TABLE very_big_table ADD COLUMN a_new_column INT(10) UNSIGNED NOT NULL DEFAULT 0 AFTER some_other_column;

実行時間：1 min 11.12 sec

ALGORITHMとLOCKを指定ありのクエリと特に変わりません。

負担ありの検証

ALGORITHMとLOCKを指定なしのクエリで検証します。

ユーザークエリが早い

負担をかけるのに、上記のimitate_very_big_table_usageを使用します。

ロックがかかっていないそうです。

---TRANSACTION 10868962, ACTIVE 40 sec reading clustered index
mysql tables in use 1, locked 1
0 lock struct(s), heap size 1136, 0 row lock(s)
MySQL thread id 42105, OS thread handle 70369935081440, query id 7565815 10.117.1.187 db_user altering table
ALTER TABLE very_big_table ADD COLUMN a_new_column INT(10) UNSIGNED NOT NULL DEFAULT 0 AFTER some_other_column
Trx read view will not see trx with id >= 10868964, sees < 10868962

ユーザークエリが特に長くなったこともないみたいです。

実行時間：1 min 12.18 sec

負担ない時と特に時間が変わりません。

ユーザークエリが遅い

負担をかけるのに、上記のimitate_very_big_table_usageを長いトランザクションに書き換えて使用します。

def imitate_very_big_table_usage
	# ３分間動きます
	duration = 3 * 60  
	
	start_time = Time.now

	loop do
		# トランザクションで処理を行います
		ActiveRecord::Base.transaction do
			<....>
			
			### 下記を追加
			# 長いトランザクションを模擬するために、１０秒スリープします
			sleep 10
		end
	
		# ３分間経っていないまでに実行を続きます
	  elapsed_time = Time.now - start_time
	  break if elapsed_time >= duration
	end
end

マイグレーションはロックがかかっていないそうです。長いトランザクションが行ロックがかかっているが、マイグレーションに邪魔しないみたいです。

---TRANSACTION 10866677, ACTIVE 2 sec
13 lock struct(s), heap size 1136, 8 row lock(s), undo log entries 4
MySQL thread id 42048, OS thread handle 18446744073709551615, query id 7560863 10.117.1.187 db_user
Trx read view will not see trx with id >= 10866677, sees < 10866538
---TRANSACTION 10866538, ACTIVE 44 sec
mysql tables in use 1, locked 1
0 lock struct(s), heap size 1136, 0 row lock(s)
MySQL thread id 42032, OS thread handle 70369946656736, query id 7560436 10.117.1.187 db_user altering table
alter table very_big_table drop column a_new_column
Trx read view will not see trx with id >= 10866540, sees < 10866538

実行時間：1 min 14.46 sec

早いユーザークエリと特に時間が変わりません。

マイグレーション作業

調査した結果でマイグレーションがテーブルロックをかけないにしても、使用数が少ない時間でマイグレーションを行った方が一番安全です。自社のSREチームが負荷が少ない時間にデプロイを行い、マイグレーション実行を見守ってくださいました。
本番環境でのマイグレーションは2分45秒かかったようです。流石にテストデータよりも長かったが、無事にテーブルロックなしで成功しました。

ところで、マイグレーション中にCPU使用を確認していましたが、一瞬に通常より2.7倍上がりました。

MySQL 8 で変わること

MySQL 5.7 は 2023年10月21日にサポート終了 (EOL) を迎えて、MySQL 8 にアップグレードが重要になります。

MySQL 8 は MySQL 5.7 に比べてALTER TABLE ADD COLUMNが大幅に高速化されています。これは主に「Instant ADD COLUMN」機能は、新しい列をデータベースのメタデータに追加するだけで、実際のテーブルデータは変更されません。これにより、列の追加が非常に高速になります。しかし、新しい列にデフォルト値が設定されている場合、MySQL は各行にデフォルト値を設定するためにテーブルデータを走査する必要があります。この操作は時間がかかるため、大きなテーブルでは列の追加が遅くなる可能性があります。

参考：https://dev.mysql.com/doc/refman/8.0/ja/innodb-online-ddl-operations.html#online-ddl-column-operations

まだ MySQL 5.7 を使用している際、できれば早く MySQL 8 にアップグレードしましょう。

EOL 参考：

https://www.mysql.com/jp/support/eol-notice.html

https://endoflife.software/applications/databases/mysql

要旨

PostgreSQL であれ、MySQL であれ、大規模なテーブルに新しいカラムを追加する際には十分な注意が必要です。データベースごとに異なるポイントが存在するため、本番に実行する前に予習することが重要だと思います。

それに間違いなく肝要なのは、常に学習し、最新情報を確認し続けることです。

もっとアルダグラムエンジニア組織を知りたい人、ぜひ下記の情報をチェックしてみてください！

アルダグラム Tech BlogPublication

株式会社アルダグラムのTech Blogです。世界中のノンデスクワーク業界における現場の生産性アップを実現する現場DXサービス「KANNA」を開発しています。採用情報はこちら: herp.careers/v1/aldagram0508/