【MySQL】巨大テーブルへのALTER TABLEで苦労した話

あらすじ

とあるサービスの機能追加にて、以下３つのテーブルにカラムを追加する必要があった
（レコード数、カラム数は一応ボカします）

• プロフィール情報テーブル
  • レコード数：数十万
  • カラム数：多
  • インデックス数：多
• ログテーブル
  • レコード数：数億
  • カラム数：少
  • インデックス数：少
• 投稿データテーブル
  • レコード数：数千万
  • カラム数：中
  • インデックス数：中

レコード数が多いテーブルへのALTER TABLEを実行することとなる為実行時間が長くことが予想される。
特に数億レコードを持つログテーブルへの実行が大変そう...と思いきやこちらはすんなり完了し３つ目のデータテーブルで苦労することとなる。

環境

• AWS RDS（MySQL 8.0.31）

最初に結論

• 根本解決には至っておらず、検索ロジックの作り直しが必要
• 新機能を早めにリリースする必要があった為一時対応を行なった

対応

テスト環境の作成

ALTER TABLE実行中はレコードの追加・削除は行わないべきなのでメンテナンス中に完了する必要があった。
その為まずは本番DBと同じデータ量の検証環境を用意し実行時間の計測を行う。

実行時間見積方法

まず調べると出てくるのはHandler_writeのレコード数を監視するという方法

ALTER TABLEを実行すると対象テーブルへのレコード数分増えるので、一定時間の増加量と総レコード数から見積もりを行うという方法。

カラム追加SQL

ALTER TABLE data_table
    ADD COLUMN `new_column_1` TINYINT(1) NULL DEFAULT NULL,
    ADD COLUMN `new_column_2` TINYINT(1) NULL DEFAULT NULL,
    ADD COLUMN `new_column_3` INT NULL DEFAULT NULL;

カラム数監視

SHOW GLOBAL STATUS LIKE 'Handler_write';

この方法だとざっくり10hかかる想定に。
思ってたより早く終わりそうなのでちょっと安心してしまったが、この時は最初の1hのデータで推計していた。
今だから言えることですがインデックスを利用している場合ALTER TABLEはデータが増える毎に実行速度が落ちていくのでそれを計算に入れるべきであった。

余談だが、同じテーブル定義を持ち実際より少ないレコードでALTER TABLE実行時間を見積もる場合は注意が必要だ。例えばインデックスの再構築を行う時、Btreeインデックスを利用していればレコード１行挿入の計算量はO(logn)になる。全てのレコードを挿入することを考えると計算量はO(nlogn)となる。レコード量に対して実行時間も線形に増加するわけではない。

実行結果

• プロフィール情報テーブル：問題なく完了
• ログテーブル：想定よりもかなり早く完了
• 投稿データテーブル: 24時間経過も完了せず

ステータスを確認すると

SHOW PROCESSLIST;

テーブルがロック状態Waiting for table metadata lockとなってしまっていた。
これが発生した原因は一部バッチ処理が止まっておらずカラム追加中に書き込みが発生したことであったが、ロック状態になる前でも見積もり時間を超過していたのでどのみち完了は出来なかった。

メンテ時間を超過してしまうのでやむなくkillを実行。

原因と対策

まず疑ったのはデータ量が多すぎることによる弊害。
特にインデックスを張っている場合はその作成によりかなり時間がかかる場合がある。

ただ数億レコードが入ったテーブルもインデックスを貼っていたがこちらはすんなりと完了した。
違いがあるとすればカラム数がかなり多いのでデータ量自体が多すぎたか...？

ともかく推定ではなく検証環境にて実際にかかる時間を計測することに。

対策その１：マシンパワーでゴリ押し

と言うことでまずは力技で一時的にインスタンス自体のスペックを上げてなんとか入れる方法を試してみる。
ログを見るとメモリが枯渇していたので倍にしてみるが...結局はメモリをあるだけ喰ったところで止まってしまった。

結果：あまり変わらず

対策その２：同じ構造のテーブルにカラム追加後リネーム

今のテーブルへのALTER TABLEが無理なら同じ構造のテーブルを作成しカラムを追加、そこに現在のデータを流し込んで完了後リネームだといけるかも？

1. 投稿データ(仮にdata_tableとする)テーブルのコピーdata_table_copyを作成する
2. data_table_copyに新規カラムの追加を行う(仮にnew_column_1,new_column_2,new_column_3とする)
3. data_tableからdata_table_copyへレコードをコピーする

INSERT INTO 
	data_table_copy 
SELECT 
	*, 
    NULL AS new_column_1, 
    NULL AS new_column_2,
    NULL AS new_column_3
FROM 
	data_table WHERE id BETWEEN 1 AND 10000000;

まずは1000万レコード入れてみる

結果

開始: 09:30:00
終了: 11:28:00
---
10,000,000record / 2h 

問題なく入ったのでこの調子で全レコードを！
...と思いきや数を大きくするとINSERTが遅くなり2000万レコードを超える頃にはほぼ動かなくなる。

ログを見るとメモリが枯渇して止まってしまった模様？
じゃあ分割して一度に使用するメモリを抑えるのはどうかとループ処理を試してみる

DROP PROCEDURE IF EXISTS insert_data_table;
DELIMITER //
CREATE PROCEDURE insert_data_table (in i int)
BEGIN

	DECLARE cnt int default 0;

	  while cnt < (i * 10000) do
        
		INSERT INTO 
			data_table_copy
		SELECT 
			*, 
			NULL AS new_column_1, 
			NULL AS new_column_2, 
			NULL AS new_column_3
		FROM 
			data_table WHERE no BETWEEN cnt AND (cnt + 9999);
            
		set cnt = cnt + 10000;
        
	  end while;
    
END//
DELIMITER ;

call insert_data_table(1000);

上記は1000万レコードを1万レコード毎に処理する形

これが検証環境ではいい感じに動き、レコード数を見ることで正確な進捗確認も可能

SELECT COUNT(*) FROM data_table_copy;

ということで本番でも試してみるが、本番だとループ毎のsending dataにやたら時間がかかり断念...

結果：途中から動かなくなる

対策その３：実行タイプ変更

色々試すもどうにも分からないのでチームメンバーに相談してみると、オンラインDDLというものを使えばいけるのでは？との提案が

こちらのブログがとても分かり易かったです。

　従来の「COPY」や「INPLACE」と異なり、メタデータの更新だけ行うことで高速かつ負荷をかけずにカラムの追加などが行えるようになりました。

ALGORITHM=INSTANTを使うとALTERの実行速度が上がるとのことで、検証環境にて試していただくことに。

ALTER TABLE data_table
    ADD COLUMN `new_column_1` TINYINT(1) NULL DEFAULT NULL,
    ADD COLUMN `new_column_2` TINYINT(1) NULL DEFAULT NULL,
    ADD COLUMN `new_column_3` INT NULL DEFAULT NULL,
algorithm=instant;

結果：エラーが出た

ERROR 1846 (0A000): ALGORITHM=INSTANT is not supported. Reason: InnoDB presently supports one FULLTEXT index creation at a time. Try ALGORITHM=COPY/INPLACE.

むむ..FULLTEXT INDEX...!?

mysql> show index in data_table where Index_type = 'FULLTEXT';
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table      | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| data_table |          1 | caption  |            1 | caption     | NULL      |    xxxxxxxx |     NULL |   NULL | YES  | FULLTEXT   |         |               | NO      | NULL       |
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

そういえば全文検索を利用している機能があり使ってました。。

オンラインDDLのADD COLUMNにはいくつか制約があり、そのうちの一つが

FULLTEXTインデックスを含むテーブルでは使用できません

となっている為追加できず。
解決には至りませんでしたがFULLTEXT INDEXが使われているという事に気がつけました。
なんやかんや調べてみるとALTER TABLEの実行がやたら遅いのもこれが原因では？という推論に辿り着く。

対策その４：新規カラムを別テーブルで管理

おおよその原因は分かったとはいえすぐに修正出来る物ではなかった。
その為パフォーマスが落ちる危惧はあったが一旦新規カラムは別テーブルで管理、JOIN句によるデータ結合で対応することに。
暫定対策だがとりあえずこれで動く状態にはなった。

根本原因

ということで根本原因はおそらく以下

FULLTEXT INDEX

FULLTEXT INDEXとは？

FULLTEXT インデックスは、テキストベースのカラム (CHAR、VARCHAR または TEXT カラム) に作成され、それらのカラムに含まれるデータに対するクエリーおよび DML 操作を高速化し、ストップワードとして定義されている単語を省略します。

これ自体は使い所を考えていれば別に悪い物ではない。
ただレコード数が増えるとインデックスサイズが肥大化してデータ挿入時の負荷が増えたり検索が遅くなったりする模様。
何より先のオンラインDDLが利用できなくなるのが結構痛い。

今回は数千万レコードのテーブルに貼られてしまっている為再構築が困難な状態になってる可能性あり。。。
さらには過去に物理削除されたレコードによる断片化が発生しておりこれも一役噛んでそうでした。

課題だらけである。

根本対策

まだ調査しきれていませんが、以下が候補。

• テーブル設計を見直す
• Elasticsearchを使う（こちらが良さそう？）

最後に

こういった類のトラブルは技術記事を眺めてると結構出てくるもので知識としては持ってるかと思います。
それをやるなら常識的に知っておくべき事だろ！と後からは何とでもいえますが、突然遭遇するとどの知識を使えば良いか判断に悩み、うまく解決できずに焦るとさらに沼に嵌ってしまう。。
そうなった時は客観的に状況を見られるチームメイト等にヘルプを求めるとあっさり解決することがあります。一人で抱え込まずに助けを求めましょう。