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 と強い互換性があります。

よくある質問 – Amazon Aurora

それはアプリケーションで扱う部分の話であって、ミドルウェアとしてはゼロから再設計されていて全く別物である、というのは有名な話です。前にも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のデータを作成。

こんなテーブルに対して

mysql> show create table sbtest01 \G
*************************** 1. row ***************************
       Table: sbtest01
Create Table: CREATE TABLE `sbtest01` (
  `id`  int(10)   unsigned NOT NULL AUTO_INCREMENT,
  `k`   int(10)   unsigned NOT NULL DEFAULT '0',
  `c`   char(120) NOT NULL DEFAULT '',
  `pad` char(60)  NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `k` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=4800001 DEFAULT CHARSET=latin1

mysql> show create table sbtest01 \G

*************************** 1. row ***************************

Table: sbtest01

Create Table: CREATE TABLE `sbtest01` (

`id` int(10) unsigned NOT NULL AUTO_INCREMENT,

`k` int(10) unsigned NOT NULL DEFAULT '0',

`c` char(120) NOT NULL DEFAULT '',

`pad` char(60) NOT NULL DEFAULT '',

PRIMARY KEY (`id`),

KEY `k` (`k`)

) ENGINE=InnoDB AUTO_INCREMENT=4800001 DEFAULT CHARSET=latin1

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

共有ロックの確認

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

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

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

Index追加

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

mysql> alter table sbtest01 add index test (c);

1	mysql> alter table sbtest01 add index test (c);

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

容量	Aurora	RDS
Index作成時間
1GB	1m19s	1m12s
2GB	5m54s	5m40s
4GB	14m35s	14m10s

RDSが誤差レベルにちょっと速いなーってのと、
容量比例以上に時間が膨れ上がりますね、というだけ。

Index作成中に更新クエリを実行しても、どちらも普通に通ることをまず確認し、それは通りましたが、副産物としてUPDATEの実行時間が結構違うというのがここでわかりました。

容量	Aurora	RDS
UPDATE実行時間
1GB	0.01s – 0.06s	0.20s – 0.98s
2GB	0.01s – 0.04s	0.66s – 2.00s

Auroraは通常とほぼ変わらずレスポンスが返りますが、RDSは実行直後はちょい遅くらいで、数秒後は秒単位に遅くなってしまいます。この辺の検証は後述とします。

カラム名変更

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

mysql> alter table sbtest01 change pad pad2 char(60) NOT NULL DEFAULT '';

1	mysql> alter table sbtest01 change pad pad2 char(60) NOT NULL DEFAULT '';

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

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

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

カラム追加

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

mysql> alter table sbtest01 add column gedow int after pad;

1	mysql> alter table sbtest01 add column gedow int after pad;

実行時間はこんな感じ。

容量	Aurora	RDS
カラム追加時間
1GB	4m05s	4m15s
2GB	9m40s	9m13s

Index追加よりはだいぶ時間がかかります。

カラム追加中の更新クエリのレスポンスタイムは……

容量	Aurora	RDS
UPDATE実行時間
1GB	0.01s – 0.03s	0.55s – 2.37s

Auroraは常に軽いですが、やはりRDSは序盤はちょい重で後から秒単位に遅くなります。

カラム削除

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

mysql> alter table sbtest01 drop column gedow;

1	mysql> alter table sbtest01 drop column gedow;

実行時間はこんな感じ。

容量	Aurora	RDS
カラム削除時間
1GB	4m02s	4m07s
2GB	9m14s	8m55s

カラム追加とだいたい同じですね。

カラム削除中の更新クエリのレスポンスタイムは……

容量	Aurora	RDS
UPDATE実行時間
1GB	0.01s – 0.02s	0.39s – 2.18s

こちらもカラム追加と同じ性質です。

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

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

型変換

適当に型を変換します。

mysql> alter table sbtest01 modify pad char(61) NOT NULL DEFAULT '';

1	mysql> alter table sbtest01 modify pad char(61) NOT NULL DEFAULT '';

実行時間はこんな感じ。

容量	Aurora	RDS
型変換時間
1GB	2m19s	1m30s
2GB	4m41s	3m35s

速くもなく遅すぎでもなくといったところ。

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

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

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

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

並列実行方法

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

host=gedow-aurora-01.cluster-asdfghjkl.ap-northeast-1.rds.amazonaws.com
host=gedow-rds-01-01.asdfghjkl.ap-northeast-1.rds.amazonaws.com

user=sbtest
size=4800000
thread=1
time=30
max=1000000
table=sbtest01

sysbench --db-driver=mysql --mysql-host=${host} --mysql-user=${user} \
         --test=oltp --oltp-table-size=${size} --oltp-index-updates \
         --num-threads=${thread} --max-time=${time} --max-requests=${max} \
         --oltp-table-name=${table} \
         --oltp-test-mode=complex \
         --oltp-point-selects=0 --oltp-simple-ranges=0 --oltp-sum-ranges=0 \
         --oltp-order-ranges=0  --oltp-distinct-ranges=0 \
         run

host=gedow-aurora-01.cluster-asdfghjkl.ap-northeast-1.rds.amazonaws.com

host=gedow-rds-01-01.asdfghjkl.ap-northeast-1.rds.amazonaws.com

user=sbtest

size=4800000

thread=1

time=30

max=1000000

table=sbtest01

sysbench --db-driver=mysql --mysql-host=${host} --mysql-user=${user} \

--test=oltp --oltp-table-size=${size} --oltp-index-updates \

--num-threads=${thread} --max-time=${time} --max-requests=${max} \

--oltp-table-name=${table} \

--oltp-test-mode=complex \

--oltp-point-selects=0 --oltp-simple-ranges=0 --oltp-sum-ranges=0 \

--oltp-order-ranges=0 --oltp-distinct-ranges=0 \

run

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

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

最初に ALTER TABLE が走ってない平穏な状態に対するベース値です。
まずは Aurora から。

スレッド数	QPS	approx95%	min	avg	max
Aurora 平時の更新クエリ性能
1	542	08.57ms	5.46ms	07.37ms	21.10ms
2	951	10.27ms	5.70ms	08.40ms	25.89ms
4	1697	11.48ms	6.03ms	09.43ms	21.45ms
8	2835	14.67ms	6.42ms	11.28ms	39.88ms

次にRDS。

スレッド数	QPS	approx95%	min	avg	max
RDS 平時の更新クエリ性能
1	969	6.30ms	1.75ms	4.12ms	023.97ms
2	2061	6.28ms	1.73ms	3.88ms	203.76ms
4	3403	7.26ms	1.93ms	4.70ms	031.59ms
8	5364	8.82ms	2.10ms	5.96ms	027.84ms

平時はRDSの方が結構速いことがわかります。これはこれで褒めるところですが、Auroraは高い並列性がウリの１つというのもありますので、これだけみて何かをウェーィするものではありません。ただの基準値であります。

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

ここからが本番。インデックス作成中のスループットは……

スレッド数	QPS	approx95%	min	avg	max
Aurora インデックス作成中の更新クエリ性能
1	547	08.89ms	4.97ms	07.30ms	35.89ms
2	949	10.74ms	5.56ms	08.42ms	29.01ms
4	1552	13.56ms	6.20ms	10.31ms	29.78ms
8	2506	17.29ms	6.80ms	12.76ms	67.54ms

次にRDS。

スレッド数	QPS	approx95%	min	avg	max
RDS インデックス作成中の更新クエリ性能
1	141	0120.43ms	001.84ms	028.31ms	1957.11ms
2	13	2164.68ms	183.41ms	594.87ms	2610.17ms
4	23	2158.85ms	026.60ms	653.61ms	4583.05ms
8	52	1608.54ms	254.35ms	601.50ms	1915.26ms

だいたい手動連続実行で得ていた感触と同じ結果が得られました。Auroraはほとんど劣化しませんが、RDSはレスポンス速度がすこぶる悪くなります。

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

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

続いてカラム追加中のスループット。

スレッド数	QPS	approx95%	min	avg	max
Aurora カラム追加中の更新クエリ性能
1	540	08.72ms	5.17ms	07.40ms	224.33ms
2	917	10.70ms	5.77ms	08.72ms	270.40ms
4	1552	13.81ms	6.38ms	10.30ms	211.57ms
8	2703	15.13ms	6.63ms	11.83ms	250.56ms

次にRDS。

スレッド数	QPS	approx95%	min	avg	max
RDS カラム追加中の更新クエリ性能
1	7	1248.68ms	035.86ms	0589.15ms	1949.19ms
2	49	0527.60ms	001.92ms	0160.07ms	2056.17ms
4	59	1355.41ms	006.28ms	0269.09ms	2541.05ms
8	24	1792.10ms	658.37ms	1329.45ms	2848.11ms

だいたいIndex追加の結果と似たり寄ったりですね。Auroraは微劣化でmaxが少々高め。RDSは高橋名人以下のQPSと、１クエリ１秒以上のレスポンスタイムという、昔からのMySQLでのイメージと同じではないでしょうか。

本番運用における ALTER TABLE

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

それが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していきたいところです:-)