AuroraのALTER TABLE性能検証とRDS比較

よく、Auroraを採用しました、安定しています、移行してよかったです!とか見かけますけど、
なんで快適なのかをちゃんとわかって使ってんのかこの野郎ッp(`・ω・´メ)q
俺も全然わかってねぇッ( ・`ω・´)

ということで、今回もいい感じに飽きてきたAuroraです。Aurora。少々気になっていた、ALTER TABLE周りについて興味深い数値が取れたので、その共有で御座います候。



MySQL5.6互換

AuroraはMySQL5.6互換というけれど・・・

Q:「MySQL と互換性がある」とはどういう意味ですか?
これは、現在お客様が MySQL データベースで使用しているほとんどのコード、アプリケーション、ドライバー、ツールをほとんど、またはまったく変更を加えなくても Aurora で使用できることを意味します。Amazon Aurora データベースエンジンは InnoDB ストレージエンジンを使用することで MySQL 5.6 と強い互換性があります。


それはアプリケーションで扱う部分の話であって、ミドルウェアとしてはゼロから再設計されていて全く別物である、というのは有名な話です。前にもQueryCacheなどの検証をしましたが、このAuroraというやつは、良い意味で強い妖かしな臭いが立ち込めています。

で、突っつき回してみたくなったのは ALTER TABLE です。最初は、この辺の内容

  • MySQL 5.6 リファレンスマニュアル :: 14.11 InnoDB とオンライン DDL
  • MySQL 5.6 のオンラインDDLについて調べた – takatoshiono’s blog
  • については、どのくらい実現されてるのかなと気になり、まぁ同じ程度ならいいやと思っていたのですが、少々深く突き刺してみると、なかなか香ばしい性能に出会えたので記録してみたくなったわけです。


    テスト環境の準備

    簡単に書きます。

    インスタンスタイプはCPUクレジットに関与しないよう r3.large で作成。
    aurora_version は 1.10.1、RDS MySQLの version は 5.6.27。
    同じAZに EC2:c4.2xlarge(spot) を作成し、sysbench で約1GBのデータを作成。

    こんなテーブルに対して

    1つ目のシェルから各種ALTER TABLEを実行、2つ目からsysbenchで更新クエリを数回実行、って感じでやっていきます。あとは・・・実行中にIOPS上限に張り付いていない確認とか、その程度はしています。


    共有ロックの確認

    よく使用しそうなALTER TABLEのみですが、MySQL5.6 と条件が同じかどうか、だけを確認していきます。○○系のカテゴリ名はワイが勝手に付けました。

    軽量系(書き込み可、テーブルコピー不要)

    こちらは、ALTER TABLE実行中でも更新クエリ実行可、かつテーブルコピーが発生しない分類となります。

    Index追加
    普通にIndex追加を実行します。

    だいたいですが、容量に対する実行時間(適当な平均値)はこんな感じ。

    Index作成時間
    容量AuroraRDS
    1GB1m19s1m12s
    2GB5m54s5m40s
    4GB14m35s14m10s
    RDSが誤差レベルにちょっと速いなーってのと、
    容量比例以上に時間が膨れ上がりますね、というだけ。

    Index作成中に更新クエリを実行しても、どちらも普通に通ることをまず確認し、それは通りましたが、副産物としてUPDATEの実行時間が結構違うというのがここでわかりました。
    UPDATE実行時間
    容量AuroraRDS
    1GB0.01s – 0.06s0.20s – 0.98s
    2GB0.01s – 0.04s0.66s – 2.00s
    Auroraは通常とほぼ変わらずレスポンスが返りますが、RDSは実行直後はちょい遅くらいで、数秒後は秒単位に遅くなってしまいます。この辺の検証は後述とします。


    カラム名変更
    普通にカラム名を変更します。

    これはAuroraもRDSも一瞬で完了しました。
    型を完全一致で書かないと変換になってしまうので注意ってくらい。


    重量系(書き込み可、テーブルコピー発生)

    次は、ALTER TABLE中も更新できるけど、テーブルコピーが発生するために重い変更なやつです。

    カラム追加
    普通に最後にカラムを追加します。

    実行時間はこんな感じ。

    カラム追加時間
    容量AuroraRDS
    1GB4m05s4m15s
    2GB9m40s9m13s
    Index追加よりはだいぶ時間がかかります。

    カラム追加中の更新クエリのレスポンスタイムは……
    UPDATE実行時間
    容量AuroraRDS
    1GB0.01s – 0.03s0.55s – 2.37s
    Auroraは常に軽いですが、やはりRDSは序盤はちょい重で後から秒単位に遅くなります。


    カラム削除
    普通にカラムを削除します。

    実行時間はこんな感じ。

    カラム削除時間
    容量AuroraRDS
    1GB4m02s4m07s
    2GB9m14s8m55s
    カラム追加とだいたい同じですね。

    カラム削除中の更新クエリのレスポンスタイムは……
    UPDATE実行時間
    容量AuroraRDS
    1GB0.01s – 0.02s0.39s – 2.18s
    こちらもカラム追加と同じ性質です。


    禁じ手(共有ロック、テーブルコピー発生)

    参照はできるけど、更新はALTERが終わるまで待機になるってやつです。

    型変換
    適当に型を変換します。

    実行時間はこんな感じ。

    型変換時間
    容量AuroraRDS
    1GB2m19s1m30s
    2GB4m41s3m35s
    速くもなく遅すぎでもなくといったところ。

    型変換中の更新クエリは……RDSはもちろん、Auroraも共有ロックで待機状態になりました。ワンチャンAuroraにロックなしを期待していたところですが、さすがにそこまで甘くはありませんでしたね。型変換が不要なように、ちゃんと設計しましょうそうしましょう:-)


    と、いうことで、Auroraも共有ロックやテーブルコピーの条件はだいたい同じだろうことがわかりました。ただし、実行中の更新クエリの隙のなさが今までにない部分なので、検証続行となります。


    更新クエリの平均レンスポンスタイム

    さぁここまでの茶番的な準備運動が終わったところで、本チャン検証に入るとします。Auroraでは ALTER TABLE 中の更新クエリがどうやら軽そうでしたが、並列実行くらいは試してみなくては判断できないのでゴリッとねじ込んでみましょう。

    並列実行方法

    sysbench を更新のみにするのは、こんなオプションです。

    これで、UPDATE2種とDELETE/INSERT1回ずつ、という内容になります。1GB/480万行に対して、ALTER TABLE実行中の傍らで、スレッド数を変更してQPSとレスポンスタイムを記録していきます。


    平時の平均レスポンスタイム

    最初に ALTER TABLE が走ってない平穏な状態に対するベース値です。
    まずは Aurora から。
    Aurora 平時の更新クエリ性能
    スレッド数QPSapprox95%minavgmax
    1542 08.57ms5.46ms07.37ms21.10ms
    2951 10.27ms5.70ms08.40ms25.89ms
    4169711.48ms6.03ms09.43ms21.45ms
    8283514.67ms6.42ms11.28ms39.88ms
    次にRDS。
    RDS 平時の更新クエリ性能
    スレッド数QPSapprox95%minavgmax
    1969 6.30ms1.75ms4.12ms023.97ms
    220616.28ms1.73ms3.88ms203.76ms
    434037.26ms1.93ms4.70ms031.59ms
    853648.82ms2.10ms5.96ms027.84ms
    平時はRDSの方が結構速いことがわかります。これはこれで褒めるところですが、Auroraは高い並列性がウリの1つというのもありますので、これだけみて何かをウェーィするものではありません。ただの基準値であります。


    インデックス作成中の平均レスポンスタイム

    ここからが本番。インデックス作成中のスループットは……
    Aurora インデックス作成中の更新クエリ性能
    スレッド数QPSapprox95%minavgmax
    1547 08.89ms4.97ms07.30ms35.89ms
    2949 10.74ms5.56ms08.42ms29.01ms
    4155213.56ms6.20ms10.31ms29.78ms
    8250617.29ms6.80ms12.76ms67.54ms
    次にRDS。
    RDS インデックス作成中の更新クエリ性能
    スレッド数QPSapprox95%minavgmax
    11410120.43ms001.84ms028.31ms1957.11ms
    213 2164.68ms183.41ms594.87ms2610.17ms
    423 2158.85ms026.60ms653.61ms4583.05ms
    852 1608.54ms254.35ms601.50ms1915.26ms
    だいたい手動連続実行で得ていた感触と同じ結果が得られました。Auroraはほとんど劣化しませんが、RDSはレスポンス速度がすこぶる悪くなります。

    RDSの結果にバラつきがあるのは、ALTER TABLE実行後・数秒は軽い状態が続いてから重くなる、という性質のせいです。タイミングを見計らえば、全て遅い結果になるわけですが、あえてこの結果のまま記載しました。MAX値とか見たら実態は理解できるでしょうから、そういうものだよ、というのを記録する意味合いを含めて。


    カラム追加中の平均レスポンスタイム

    続いてカラム追加中のスループット。
    Aurora カラム追加中の更新クエリ性能
    スレッド数QPSapprox95%minavgmax
    1540 08.72ms5.17ms07.40ms224.33ms
    2917 10.70ms5.77ms08.72ms270.40ms
    4155213.81ms6.38ms10.30ms211.57ms
    8270315.13ms6.63ms11.83ms250.56ms
    次にRDS。
    RDS カラム追加中の更新クエリ性能
    スレッド数QPSapprox95%minavgmax
    17 1248.68ms035.86ms0589.15ms1949.19ms
    2490527.60ms001.92ms0160.07ms2056.17ms
    4591355.41ms006.28ms0269.09ms2541.05ms
    8241792.10ms658.37ms1329.45ms2848.11ms
    だいたいIndex追加の結果と似たり寄ったりですね。Auroraは微劣化でmaxが少々高め。RDSは高橋名人以下のQPSと、1クエリ1秒以上のレスポンスタイムという、昔からのMySQLでのイメージと同じではないでしょうか。


    本番運用における ALTER TABLE

    これまでのMySQLでは、重いALTER TABLEでスキーマに手を加える時、サービスをメンテナンス入りさせるか、MASTERのコピーを先に編集しておいてサッと切り替えるか、というような手法が一般的だったと思います。それが必要な理由は、RDSの結果を見てわかるとおり、1HTTPリクエストが1秒ならまだしも、1クエリが1秒だと、1HTTPのレスポンスタイムがドエライことになるからです。

    それがAuroraになると、どうやらオンラインで実行しても影響が軽微な種類の ALTER TABLE があることがわかりました。全て検証したわけではないですが、おそらく共有ロックが発生しないタイプがそうであり、元々共有ロックが必要なタイプはそのままの挙動である、と推測できます。

    これが真ならばメンテインしなくても実行できる幸せを味わえるわけですが・・・
    あくまで一部の検証による推測であり、例えばQPSやデータ容量によっても変わる類のものかもしれないため、本番で初めて試みる際には、前に紹介したレプリケーションクラスタでも作成してそこでテストしてから、といった流れで運用するとほぼ事故をなくせて良いと思われます。それでダメなら、メンテイン、pt-online-schema-change、クラスタコピーなどを検討する、と。



    おそらく今回の話は公式的にはなさそうですが、もしかするとこの辺
  • Amazon Auroraアップデート – Parallel Read Ahead, Faster Indexing, NUMA Awareness | Amazon Web Services ブログ
  • Amazon Auroraが1.7へアップデートしてより高速に。並列プリフェッチ、NUMA対応など - Publickey

  • のアップデートで一緒に改善されたのか……どっちかというと最初からな気がする部分ですが、なんにせよ粋なはからいをしていただけましてありがとうございます、という感想でございます。



    ここ最近、連続でAuroraに粘着してますが、どうやら来月に
  • Amazon Aurora 事例祭り (2017 年 3 月 7 日開催) | AWS
  • お祭りがあるみたいなので、愉快なお話が聞けるとよいですね。

    おそらく中の人は公式外のことは、そうそう言えないはずなので、我々のようなパンピー勢の黒箱漁りに期待ageしていきたいところです:-)