Help us understand the problem. What is going on with this article?

Aurora PostgreSQL 11.4 互換版がひっそりと出ていたので試してみた

こちらは PostgreSQL Advent Calendar 2019 17 日目のエントリです。

昨日(16 日目)は tom-sato さんでした。

実は、元々 Alibaba Cloud の POLARDB PostgreSQL 互換版で oss_fdw 機能を試そうとしたのですが、残念ながら実装が追い付いていなかったようなので、今回は AWS Aurora に戻ってきました。

そして、当アドベントカレンダーの中で一番内容の薄いエントリとなります(このアドベントカレンダー作ったの誰だよ?←私だ!)。

Aurora PostgreSQL 11.4 互換版

re:Invent 2019 開催直前の 2019/11 下旬にリリースされていました。

今回は東京リージョン(ap-northeast-1)も同時リリースだったようです。

PostgreSQL 11 の新機能については、こちらにあるページのリンク先を参照してください。

とはいえ、本家 PostgreSQL 11 は 2018/10 にリリース、次バージョンの PostgreSQL 12 も 2019/10 にリリースされているので、ほぼ 1 年遅れのタイミングとなります。

試す内容(1):10.5 互換版登場時に試したパラレルスキャンを R5 インスタンスで

こちらの記事と同じ内容を実行してみます。

ただし、Aurora は、2019/2 に R5 インスタンスをサポートしています。

※PostgreSQL 互換版の R5 インスタンスサポートが同時だったかどうは記憶にございません…T3 インスタンスは遅れてサポートされたようですが。

前回は R4 インスタンスで 10.5 互換版を試しましたが、今回は、R5 インスタンス(db.r5.large)で

  • 10.7 互換版
  • 11.4 互換版

を試します。

また、

の結果(POLARDB PostgreSQL 11.2 互換版)も引用します。

結果

インスタンスタイプ/バージョン シーケンシャル 1 回目 シーケンシャル 2 回目 インデックス 1 回目 インデックス 2 回目
db.r4.large/Aurora 10.5 ※昨年 5,707.766 2,921.991 42,457.903 493.760
4C16GB/POLARDB 11.2 5,388.365 968.011 6,091.151 264.254
db.r5.large/Aurora 10.7 4,405.818 2,462.651 29,931.351 439.518
db.r5.large/Aurora 11.4 4,048.994 2,314.962 32,709.778 426.766

それぞれの 1 回目が共有バッファにデータが載っていない状態、2 回目が載っている状態です。

  • R5 インスタンスは R4 と比べて高速(特にストレージアクセス)
  • PostgreSQL 10 と 11 の間では、大きな差は無し
  • Aurora と POLARDB の差は CPU コア数とストレージ構成の違いによるもの?

なお、Aurora 10.7 と 11.4 で 11.4 のほうが結果が悪くなっていますが、何度か実行していると「外れインスタンス」を掴むことがあり、これが影響している可能性があります(時間の都合で少ない試行回数の平均を取っています)。

Aurora R5 と POLARDB の違いですが、

  • CPU Core は Aurora が 2 に対し POLARDB が 4(AWS での m5.xlarge に近い)
  • ストレージは Aurora が 3AZ-6Copy、POLARDB が 1AZ-3Copy。
  • 当該インスタンスのストレージ I/O 帯域は Aurora が 4.75Gbps に対して POLARDB が 4Gbps
    • 2019/12/16 現在、AWS の日本語マニュアルの情報は古いです(× 3.5Gbps →〇 4.75Gbps)

なので、

  • メモリ上のデータを使った処理は CPU Core 数の分 POLARDB 有利(パラレルスキャンの負荷など)
  • ランダムで細かいストレージアクセスがある場合も POLARDB 有利(別 AZ からの ACK を待たなくても良いから?)
  • シーケンシャルなストレージアクセスの場合は Aurora のほうが多少有利?(遅延よりもI/O 帯域?)

ということでしょうか。

試す内容(2):パーティショニング

PostgreSQL 10 / 11 の新機能と言えば「宣言的パーティショニング」ですね(やや強引)。

というわけで、今回は、

  • 先のテストパターンのうち、インデックススキャンのほうのテーブル定義にパーティショニングを含める
  • 10 の宣言的パーティショニングでは PK が設定できないため number 列の PK を外す
  • 11 では親テーブルに PK を設定できるのでそちらの形で試す(PK の一部にパーティションキーを含める必要があるのでid列を単独のインデックスではなく PK の 2 列目に含める)

で試してみます。

なお、完全に手抜きですが、WHERE id < 10の条件に合わせてid09のパーティションと10以上のパーティションの 2 つに分けただけ、です。

※元記事に書いた通り、idというネーミングが完全に罠になっています(ごめんなさい)。

10用のテーブル定義
CREATE TABLE table_i (number SERIAL NOT NULL, id INT, value INT) PARTITION BY RANGE (id);
CREATE TABLE table_i_1 PARTITION OF table_i FOR VALUES FROM (0) TO (10);
CREATE TABLE table_i_2 PARTITION OF table_i FOR VALUES FROM (10) TO (100);
CREATE INDEX idx_id_1 ON table_i_1 (id);
CREATE INDEX idx_id_2 ON table_i_2 (id);
11用のテーブル定義
CREATE TABLE table_i (number SERIAL NOT NULL, id INT, value INT) PARTITION BY RANGE (id);
CREATE TABLE table_i_1 PARTITION OF table_i FOR VALUES FROM (0) TO (10);
CREATE TABLE table_i_2 PARTITION OF table_i FOR VALUES FROM (10) TO (100);
ALTER TABLE table_i ADD PRIMARY KEY(number, id);

ちなみに、パーティショニングで気になるINSERTの速度ですが、パーティショニング無しと比較して概ね 10 ~ 20% 程度余分に時間が掛かりました(ばらつきが大きいので値を掲示するのは控えます)。

結果

バージョン/パーティショニング有無 インデックス 1 回目 インデックス 2 回目
Aurora 10.7/無 29,931.351 439.518
Aurora 10.7/有 540.669 331.789
Aurora 11.4/無 32,709.778 426.766
Aurora 11.4/有 539.339 355.797

パーティショニング無しの結果は(1)からの引用です。

  • 共有バッファに載っていないと劇的に高速化
    • WHERE句の条件と完全にマッチする形で分割したため
  • パーティショニング有りでも 10 と 11 の差はほぼ無し

でした。

Aurora の場合、テーブル空間を別ストレージに分離する手法は取れないため、10 に対する 11 のアドバンテージは性能向上ではなくて

  • パーティション間のデータ移動ができる
  • デフォルトパーティションを作ることができる
  • 外部キーなどの制約を使うことができる

あたりでしょうか。

余談

Aurora PostgreSQL 互換版、この↓機能のおかげで便利にはなったものの、「共有バッファにデータが載っていない」状態でテストすることが難しくなってしまいました(設定で OFF にしても「あれ?載ってるのでは?」という挙動が何度も…)。


PostgreSQL Advent Calendar 2019 の明日(18 日目)は sira さんです。

Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away