ノバセル株式会社 CoreDevelopmentチームの田村です。 今年の４月に新卒入社しましたが、もう半年経っているのが信じられず時の流れの早さを感じてます。笑
趣味は筋トレで現在マッスル部の部長をしています！💪
今回は、業務中にチームで行ったDWHからRDSへのデータロード処理を高速化した話をします！
データロードの流れ
ノバセルでは競合他社のテレビCM「効果」を可視化するノバセルトレンドというサービスを展開しています。
テレビCM「効果」の分析データをアプリケーション上で表示させるために、DWHからRDSへリバースETLをしています。
この処理は、SnowflakeからS3へアンロードし、その後RDSへロードさせています。
ロード処理時間の問題
現状ロードさせるデータ量は10GBほどですが、この処理の中で特にS3 → RDSまでの処理が当初6時間ほど掛かっていました。
さらに、データ量は日々増え続けており、1日あたり2分ほどロード時間が長くなっていました。近い将来、開発のボトルネックになることが予想されることから高速化する必要がありました。
予想された主な原因
Snowflake から S3 にアンロードされた各ファイルのサイズを見ると16MBほどとなっていました。加えて、多数のファイルができていることを確認しました。
これを踏まえ、サイズの小さいファイルが多数あることでデータ転送時のIO処理に無駄があるのではないかと考えました。
この仮説に関して、ファイルサイズが効率性に関係していることはAWSのこちらのブログにも記載されています。
ファイルサイズが非常に小さい場合、特に 128MB 未満の場合には、実行エンジンは S３ファイルのオープン、ディレクトリのリスト表示、オブジェクトメタデータの取得、データ転送のセットアップ、ファイルヘッダーの読み込み、圧縮ディレクトリの読み込み、といった処理に余分な時間がかかります
アンロードされるファイルのサイズを上げ、ファイル数をへらす
仮説をもとに『DWHからS3へアンロードする際のチャンクサイズを上げ、ファイル数を下げる』ということを念頭に高速化を行いました。
以下は具体的にSnowflakeで行った内容になります。
COPY INTOのCopyOptionsのMAX_FILE_SIZEを上げる
SnowflakeからS3へアンロードする際にCOPY INTOを行いますが、その際のcopyOptionの1つであるMAX_FILE_SIZEをデフォルト値の16MBから最大の5GBにしました。(参考: SnowflakeDocs CopyOptions)
実行するウェアハウス(以後WH)設定を変更する
加えて、実行するWHの設定を WAREHOUSE_SIZE='SMALL'(デフォルト値) から WAREHOUSE_SIZE='XSMALL'へ変更させました。
SnowflakeDocs のCopyOptions部分を読むと、
スレッドごとに並列に生成される各ファイルの上限サイズ（バイト単位）を指定する数値（> 0）。実際のファイルサイズとアンロードされるファイルの数は、並列処理に使用可能なデータの合計量とノードの数によって決定されることに注意してください。
と記載されている通り、単純にMAX_FILE_SIZEを下げるだけではファイルサイズをあげられず、結果としてファイル数を下げられない可能性があります。
こちらの解決方法としてはSNOWFLAKE FORUMSで言及されています。
using a smaller WH helps us with this goal. Using an XS WH will result in the fewest files, and if you reduce the MAX_CONCURRENCY_LEVEL that should result in even fewer files.
より小さいWHを使用することで、この目標を達成することができます。XS WHを使用すると、最も少ないファイル数になり、MAX_CONCURRENCY_LEVELを下げれば、さらに少ないファイル数になるはずです。
こちらをもとにさまざまなパラメータで検証し、WHサイズを下げてもShowflakeからS3へアンロードする時間がある程度許される設定値を調べました。
我々の環境では、 WAREHOUSE_SIZE='XSMALL' がパフォーマンスを維持しつつチャンクサイズを上げファイル数を下げるために良い設定値となりました。
（他にも MAX_CONCURRENCY_LEVEL を下げる方法も検証しました。しかしSnowflakeからS3へアンロードする処理時間が許容時間を超えてしまったため、デフォルトのままにしました。）
そのほか高速化のために行ったこと
RDSへのロード時にデータロード後にINDEXを追加する
RDSへのロード時の処理も見直しました。
当初のロード処理では、インデックスを貼った状態のテーブルにデータをInsertしていました。
しかし調査したところ、開発者のためのSQLチューニングへのガイドにも記載されているとおり、データInsert前よりデータInsert後にINDEXを追加したほうが、一般的にパフォーマンスが良くなるということがわかりました。
こちらに基づき、高速化のために、データInsert後にINDEXを追加するように変更を行いました。
結果
上記のことを中心に行い、6時間掛かっていた処理を1時間に収めることができました！
尚、データロード全体にかかる総時間と、サンプルとしてある１つのテーブルをアンロードした際のファイル数とファイルサイズを以下の表にまとめました。
ファイルサイズの変更とindex前貼りがそれぞれどれくらい寄与したかは計測していないため、時間がある際に計測したいと考えています！
