今川館

都内勤務の地味OLです

AWS RDS Aurora MySQL で膨大な件数をSUMしてSELECT INSERTするときのプラクティス

データコンバートは楽しい

この度データコンバートの仕事が来たので、そのときに行ったチューニングのことをここにメモしておく。
データコンバートの仕事は何度か経験があるが、チューニングに結構頭を使う仕事なので好きな方だ。
さて、前置きはほどほどにして本題に入るとしよう。

今回のお仕事の概要

  • 8億件入っているテーブルをSUMして、同じ定義の別のテーブルにSELECT INSERTする(6億件程度)
    • 集約関数を伴うので、クエリのソート処理は避けられない
    • 移行先のテーブルには1つの複合UNIQUEインデックスと、7つの単一インデックスの合計8つのインデックス作成が必要
  • データベースはAWS RDS AuroraのMySQLエンジン

少し考えれえば何もチューニングせずに現実的な時間内にコンバートを終えることは難しいことがわかる。
今回はSELECT文のクエリのチューニングは割愛して、それ以外の部分で行った事項を説明したい。

ソート処理の高速化が必要

コンバートの方法がSUMした結果を別のテーブルに投入することなので、集約関数に伴うソート処理を避けることはできない。
よくあるSELECT文のチューニングとしては無駄なソートを避けろと言われることが多いが、今回はそれは無理なので、ソートする前提でなるべく高速に処理する方針でチューニングが必要。

そうすると、まず検討すべきはMySQLのパラメータをチューニング。

ORDER BY や GROUP BYするクエリのパラメータチューニング

MySQLでチューニングするのは何と7年ぶりのことで、そもそもこういうときにどのパラメータを変えれば効果的なのか調べるところからスタートだった。
調べていくと、どうやらsort_buffer_sizeを変えると効果がありそうだ。

MySQL :: MySQL 5.6 リファレンスマニュアル :: 5.1.4 サーバーシステム変数

ソートを実行する必要がある各セッションは、このサイズのバッファーを割り当てます。

SHOW GLOBAL STATUS の出力に表示される秒あたりの Sort_merge_passes の数が多い場合、sort_buffer_size 値を増やすことで、クエリー最適化またはインデックスの改善によって改善できない ORDER BY または GROUP BY 操作を高速化することを検討できます。

現在の設定値はいくらなのか?と思い、show variables like '%sort_buf%' と叩いてみると何と256 KiB。これは少ない。
ここに設定されたサイズを超えるソートが必要になった場合、ディスクIOに回されるのでソート対象のデータが大きくなればなるほど遅くなる。
思い切って6 GiBに拡大した。

大量のレコードをSELECT INSERTするときはインデックスを後で作る方がトータルで速い

何しろ今回は8億件のデータを6億件にSUMして別のテーブルに挿入しなければならないので、挿入のコストもできるだけ減らす必要がある。
カットしやすいのはインデックスの作成コストなので、まず移行先テーブルはCREATE TABLEだけ行い後でインデックスを作成することにした。

そもそも、インデックスのお約束として

  • SELECTの頻度がINSERT/UPDATE/DELETEよりも相対的に多い場合に作っておくと有利。
  • 逆の場合は不利。

というルールがある。
このため、新規テーブルを作って膨大な件数を挿入する場合はインデックス無しで行い、後でインデックスを作った方がトータルの所要時間は短くなる。
合計8つもインデックスがあるので挿入時のインデックス更新コストは馬鹿にならないだろう。

実際に試すと、

説明 所要時間
CREATE TABLE時に8つのインデックスも作って70万件のSELECT INSERTした場合 17分10秒
CREATE TABLEだけ行い、インデックスなしでSELECT INSERTした場合 9分5秒
インデックスなし && sort_buffer_size=6 GiBに拡大して70万件のSELECT INSERTした場合 5分30秒

(Auroraのサイズ=db.r3.xlarge)

良いじゃないか、3分の1以下まで処理速度が向上した。

net_write_timeoutも延ばしておいた

MySQL :: MySQL 5.6 リファレンスマニュアル :: 5.1.4 サーバーシステム変数

書き込みを中止する前にブロックが接続に書き込まれるのを待機する秒数

SELECT INSERTの途中でタイムアウトされたくないので、これも600->3600(秒)にのばしておいた。

RDSのパラメータの変更が反映されるタイミングはいつなのか?

RDSでMySQLのパラメータを変えた場合、反映されるのはどのタイミングなのか?要は再起動が必要か否かということだが、これはちゃんとドキュメントに書いてあった。

DB パラメータグループを使用する - Amazon Relational Database Service

動的なパラメータを変更し、DB パラメータグループを保存する場合、[Apply Immediately] の設定にかかわらず、変更は直ちに適用されます。静的パラメータを変更し、DB パラメータグループを保存する場合、パラメータの変更は DB インスタンスを手動で再起動した後に有効になります。

sort_buffer_sizeもnet_write_timeoutもdynamicなので、即座に適用され、再起動は不要。