検証記事の要約

Aurora MySQLとRedshiftのゼロETL統合が本番導入出来るか検証しました

クラウドワークスでは、MySQLのテーブルをDMS経由でRedshiftにニアリアルタイムで同期し、データ分析を行っていました。しかし、DMSでレコード件数やデータサイズの大きいテーブルを再同期する際に半日以上かかってしまう問題がありました。この問題を解決するため、Aurora MySQLとRedshiftのゼロETL統合の利用可能性について検証を行いました。検証の結果、いくつかのエラーや制約は見つかりましたが、それらは回避可能であったため、本番導入に向けて取り組むことにしました。

本番導入を阻んだ原因

MySQLのMEDIUMTEXT/LONGTEXT型を含むテーブルや無効なUTF-8 文字コードを含むテーブルで同期エラーなど、さまざまな問題が発生しましたが、いずれも運用で回避できる程度のものでした。本番環境と同等のデータ量でパフォーマンス検証を行なったところ、1〜2時間で同期が完了するという好ましい結果が得られました。本番環境にゼロETL統合の設定を適用したところ、初回の同期は問題なく完了しました。しかし、その後、テーブル同期の最新状態への追従を待っていると、同期のラグが一向に減少しない事象が発生しました。

ラグが増加し続ける原因を特定するため、いくつかの検証を実施しました。

再度、フルロードする
Aurora MySQLのインスタンスタイプを上げる
同期対象のテーブル数を減らす

1と2では特に変化が見られませんでした。3については、ラグが増加し続けるものの、ラグの増加量が減ったことを確認できました。

以上のことから、この事象は再現性があり、Aurora MySQLのスペックには関係なく、テーブル数に比例することがわかりました。ゼロETL統合の環境はクライアント側からは確認することができないため、この事象の原因をAWSサポートに問い合わせることにしました。

AWSサポートからの回答

AWSサポートに問い合わせしたところ、ラグの原因はSAVEPOINTであることがわかりました。

要点をまとめると以下のとおりです。

問題となっているワークロードでは、SAVEPOINTが1秒間に1回以上実行されている
Aurora MySQLとRedshiftのゼロETL統合は、主にDML処理のレプリケーション用に最適化されており、DDLが多く実行されるワークロードは想定していない
ゼロETL統合では、SAVEPOINTが内部的にDDLとして実行され、ラグの原因になる
現状、SAVEPOINTをなるべく利用しないようにアプリケーションを変更する以外に回避策はない

MySQLのSAVEPOINTとは

普段、SQLを触る機会は数多くありますが、MySQLコンソール上でSAVEPOINTを実行した経験はありませんでした。MySQLのSAVEPOINTとは、一体何なのでしょうか。

公式ドキュメントを参照してみます。

https://dev.mysql.com/doc/refman/8.0/en/savepoint.html

SAVEPOINT文は、識別子を名前とするトランザクション・セーブポイントを設定します。現在のトランザクションが同じ名前のセーブポイントを持っている場合、古いセーブポイントは削除され、新しいセーブポイントが設定されます。

ROLLBACK TO SAVEPOINT文は、トランザクションを終了することなく、名前付きセーブポイントまでトランザクションをロールバックします。セーブポイントが設定された後に現在のトランザクションが行に行った変更はロールバックで元に戻されますが、InnoDBはセーブポイントの後にメモリに格納された行ロックを解放しません。 (新しく挿入された行の場合、ロック情報は行に格納されたトランザクションIDによって伝えられます。この場合、行のロックはアンドゥで解放されます)。指定されたセーブポイントよりも後の時間に設定されたセーブポイントは削除されます。

どうやら、トランザクションの文脈でSAVEPOINTが利用されているようです。

トランザクション内にSAVEPOINT文でセーブポイントを作成し、ROLLBACK TO SAVEPOINT文でセーブポイントを設定した箇所までロールバックするという機能です。これはよく耳にするネストしたトランザクションのことですね。

現状、MySQLでネストしたトランザクションはサポートされていないため、トランザクション内でSAVEPOINTを利用することでネストしたトランザクションを再現しています。

Active Recordで言うところのtransaction(requires_new: true)でしょうか。

下記の記事でも、そのことが触れられています。

https://techracho.bpsinc.jp/hachi8833/2018_03_16/53811

Active Recordは、MySQL/PostgreSQL/MS-SQL（真のネステッドトランザクションをサポートする場合のみ）でSAVEPOINTを用いることでトランザクションのネストをエミュレーションします。

回避策は

SAVEPOINTをなるべく利用しないようにアプリケーションを変更することが回避策とされていますが、余程シンプルなアプリケーションでない限り、ネストしたトランザクションを利用しないのは難しいです。ネストしたトランザクションの利用を減らすにしても、クラウドワークスのコア機能に深く関わっているため、減らすことも厳しい状況でした。

結果的に、ネストしたトランザクションを多用しているクラウドワークスでは、ゼロETL統合は適さないと判断しました。