ぱと隊長日誌

ブログ運用もエンジニアとしての生き方も模索中

PostgreSQLのトランザクション分離レベル毎のパフォーマンス測定(に失敗しました)

要約

PostgreSQLベンチマーク試験コマンドである pgbench を利用して、トランザクション(TX)分離レベル毎のパフォーマンス測定にチャレンジしました。

残念ながら、今回の検証手法でTX分離レベルのパフォーマンスを比較することに意味がないと言わざるを得ない結果となりました。

ただ、この過程で得られた知見は今後の検証の糧になると考えています。そこで、検証記録をここに公開します。

検証環境

WindowsHyper-V による仮想マシンを検証環境としました。本来は物理マシンを使うのが理想なのですが、機材を準備できなかったためです。

ホスト

プロセッサ Intel Core i5-6600 CPU @ 3.30GHz
メモリ 20.0 GB
OS Windows 10 Pro バージョン 1909

Hyper-V

プロセッサ 4個の仮想プロセッサ
メモリ 8.0 GB
OS CentOS 8.0.1905
DB PostgreSQL 12.1

PostgreSQL設定 (postgresql.conf)

デフォルトの設定から変更した箇所を示します。

logging_collector = on
autovacuum = off
default_transaction_isolation = {'read uncommitted' | 'read committed' | 'repeatable read' | 'serializable'}

autovacuum は pgbench のマニュアルを参考に OFF としました。

自動バキュームが有効な場合、性能を測定する上で結果は予測できないほど変わる可能性があります。

pgbench

default_transaction_isolation は試験条件に応じて都度変更しました。

マニュアル

現時点で PostgreSQL 12 の日本語版マニュアルは公開されていません。
ただ、英語版マニュアルの差分から今回の検証の範囲では 11 と 12 で大きな差異はないと判断し、11 の日本語版マニュアルを引用することにしました。

検証方法

pgbench のデフォルトである TPC-B に似た試験シナリオを採用しました。

以下の条件を組み合わせて測定を行いました。

  • TX分離レベル
    • read uncommitted
    • read committed
    • repeatable read
    • serializable
  • クライアント数及びワーカースレッド数
    • 1
    • 4

測定の度にテーブルの初期化をやり直しています。

デフォルトの試験シナリオはまた、テーブルを初期化してからの経過時間に非常に敏感です。 テーブル内の不要行や不要空間の累積により結果が変わります。

pgbench

初期データの scale は 4 で固定としました。4 としたのは測定条件のクライアント数の最大値が 4 であることからです。

デフォルトのTPC-Bのような試験シナリオでは、初期倍率(-s)を試験予定のクライアント数(-c)の最大値と同程度にしなければなりません。 pgbench_branchesテーブルには-s行しかありません。 また、全トランザクションはその内の1つを更新しようとします。 ですので、-c値を-sより大きくすると、他のトランザクションを待機するためにブロックされるトランザクションが多くなることは間違いありません。

pgbench

pgbench をデータベースサーバ上で実行しています。マニュアルでは pgbench とデータベースサーバは分けることを推奨するような記述があることに注意が必要です。

pgbenchの制限は、多くのクライアントセッションを試験しようとする際にpgbench自身がボトルネックになる可能性があることです。 これは、データベースサーバとは別のマシンでpgbenchを実行することで緩和させることが可能です。

pgbench

初期化時に unlogged-tables オプションを指定しました。これにより WAL ファイルが出力されなくなります。
当初はオプション無し(通常通り WAL ファイルが出力される)で試験を行いましたが、ディスクアクセスがボトルネックとなりました。今回のTX分離レベルによるボトルネックがディスクアクセスになるとは想定しがたいため、無用なボトルネックを回避するために unlogged-tables オプションを指定しました。

測定は複数回行い、ばらつきが大きい結果については妥当と思われる結果を採用しました。
なお、今回の測定ではクライアント数及びワーカースレッド数が大きいときにばらつきが大きかったようです。また、実行・測定タイミングによっても結果は上下しました。

TPSは "excluding connections establishing" の値を採用しています。

検証結果

TX分離レベル クライアント数 完了 / 予定TX数 平均レイテンシー TPS
read uncommitted 4 2,000,000 / 2,000,000 1.049 ms 3,813
read uncommitted 1 500,000 / 500,000 0.832 ms 1,202
read committed 4 2,000,000 / 2,000,000 1.050 ms 3,809
read committed 1 500,000 / 500,000 0.840 ms 1,191
repeatable read 4 500,001 / 2,000,000 3.343 ms 1,196
repeatable read 1 500,000 / 500,000 0.837 ms 1,195
serializable 4 500,000 / 2,000,000 3.405 ms 1,175
serializable 1 500,000 / 500,000 0.851 ms 1,175

read uncommitted と read committed の比較

read uncommitted と read committed ではほとんど同じ結果となりました。これはPostgreSQLの実装を踏まえると妥当な結果といえます。

PostgreSQLでは、4つの標準トランザクション分離レベルを全て要求することができます。 しかし、内部的には3つの分離レベルしか実装されていません。 つまり、PostgreSQLのリードアンコミッティドモードは、リードコミッティドのように動作します。

13.2. トランザクションの分離

クライアント数が増えるとTPSも増えますが、クライアント数が4倍に増えたからといってTPSが4倍になるわけではありません。これはTX間で処理の競合があることを踏まえると妥当と思われます。

repeatable read, serializable の比較

repeatable read, serializable の クライアント数 = 4 における完了TX数はクライアントごとに割り当てられたTX数 ( = 予定TX数 / 4 ) とほぼ同じになっています。また、実行時の記録からは直列化異常が発生していることが分かります。

これを踏まえ pgbench の実装を確認したところ、コマンドの実行に失敗するとクライアントが終了し、そのクライアントに割り当てられた残りのTXが実行されないようです。今回の場合では早々に1クライアントしか残らず、実質的にシングル処理となっています。よって、今回の結果をパラレル処理の評価には使うことができません。

また クライアント数 ≧ 2 かつ ワーカースレッド数 = 1 でも直列化異常は起きます。当初はワーカースレッド数が 1 であれば逐次的な実行になるのではと推測したのですが、pgbench のデバッグオプション (--debug) で実行して確認したところ、各クライアントのステップが互い違いに実行されていました。
⇒これに関しては私のC言語のリーディング力が足りず、コードレベルでは読み切れていません。そんなに難しいことではないはずなのですが…。残念です。

クライアント数 = 1 であれば直列化異常は起きませんが、TX分離の目的を考えるとあまり意味のある測定とは言えないでしょう。

全てのTX分離レベルの比較

repeatable read, serializable でクライアント数 ≧ 2 だと直列化異常が発生して abort するため、read uncommitted, read committed とパフォーマンスの比較をすることができません。直列化異常時にリトライする処理を含んだシナリオとする必要があります。

ただ、直列化異常が発生しているということはアノマリーが発生しているということです。アノマリーが発生するシナリオでTX分離レベル間のパフォーマンスを比較することに意味がないと思われます。

アノマリーについては以下の記事を参照ください。
いろんなAnomaly - Qiita

クライアント数 = 1 では各TX分離レベルとも大差のない結果となっています。ただ、クライアント数 = 1 であればTXも1つであり、分離する必要がありません。また、現実的なアプリケーションでは想定しがたい状況です。よって、このケースのパフォーマンスを議論することはあまり意味がないと思われます。

まとめ

今回採用した「TPC-B に似た試験シナリオ」による検証では以下の問題点がありました。

  • アノマリーが発生するシナリオであること。
  • 直列化異常が発生した時にリトライしていないこと。
  • クライアントでエラーが発生すると残りのTXが実行されないこと。

このことを踏まえると、今回の手法で異なるTX分離レベルのパフォーマンスを比較することは不適切と考えられます。

TX分離レベルのパフォーマンス測定を意味のあるものとするためには、アノマリーが発生しない、もしくは発生しても許容できるシナリオである必要があります。また、それが実際のアプリケーションに近いシナリオである必要があります。

もしくはTX分離レベルに応じて測定シナリオを変えることも考えられます。TX分離レベルが異なれば実際のアプリケーションでも設計が異なるはずだからです。ただ、一般化した測定シナリオを作ることはかなり困難でしょう。

測定記録

本節では実際の測定データを掲載します。

実際のコマンド、測定結果、発生したエラーなどの参考になさってください。

read uncommitted

$ pgbench -i -s 4 --unlogged-tables testdb

$ pgbench -c 4 -j 4 -t 500000 testdb
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 4
query mode: simple
number of clients: 4
number of threads: 4
number of transactions per client: 500000
number of transactions actually processed: 2000000/2000000
latency average = 1.022 ms
tps = 3912.509879 (including connections establishing)
tps = 3912.558738 (excluding connections establishing)
$ pgbench -i -s 4 --unlogged-tables testdb

$ pgbench -c 4 -j 4 -t 500000 testdb
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 4
query mode: simple
number of clients: 4
number of threads: 4
number of transactions per client: 500000
number of transactions actually processed: 2000000/2000000
latency average = 1.024 ms
tps = 3906.769702 (including connections establishing)
tps = 3906.787669 (excluding connections establishing)
$ pgbench -i -s 4 --unlogged-tables testdb

$ pgbench -c 4 -j 4 -t 500000 testdb
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 4
query mode: simple
number of clients: 4
number of threads: 4
number of transactions per client: 500000
number of transactions actually processed: 2000000/2000000
latency average = 1.049 ms
tps = 3813.112466 (including connections establishing)
tps = 3813.152381 (excluding connections establishing)
$ pgbench -i -s 4 --unlogged-tables testdb

$ pgbench -c 4 -j 4 -t 500000 testdb
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 4
query mode: simple
number of clients: 4
number of threads: 4
number of transactions per client: 500000
number of transactions actually processed: 2000000/2000000
latency average = 1.050 ms
tps = 3808.567856 (including connections establishing)
tps = 3808.588580 (excluding connections establishing)
$ pgbench -i -s 4 --unlogged-tables testdb

$ pgbench -c 1 -j 1 -t 500000 testdb
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 4
query mode: simple
number of clients: 1
number of threads: 1
number of transactions per client: 500000
number of transactions actually processed: 500000/500000
latency average = 0.831 ms
tps = 1204.027044 (including connections establishing)
tps = 1204.043443 (excluding connections establishing)
$ pgbench -i -s 4 --unlogged-tables testdb

$ pgbench -c 1 -j 1 -t 500000 testdb
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 4
query mode: simple
number of clients: 1
number of threads: 1
number of transactions per client: 500000
number of transactions actually processed: 500000/500000
latency average = 0.832 ms
tps = 1201.563732 (including connections establishing)
tps = 1201.569588 (excluding connections establishing)

read committed

$ pgbench -i -s 4 --unlogged-tables testdb

$ pgbench -c 4 -j 4 -t 500000 testdb
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 4
query mode: simple
number of clients: 4
number of threads: 4
number of transactions per client: 500000
number of transactions actually processed: 2000000/2000000
latency average = 1.043 ms
tps = 3834.281733 (including connections establishing)
tps = 3834.307153 (excluding connections establishing)
$ pgbench -i -s 4 --unlogged-tables testdb

$ pgbench -c 4 -j 4 -t 500000 testdb
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 4
query mode: simple
number of clients: 4
number of threads: 4
number of transactions per client: 500000
number of transactions actually processed: 2000000/2000000
latency average = 1.060 ms
tps = 3772.517305 (including connections establishing)
tps = 3772.546641 (excluding connections establishing)
$ pgbench -i -s 4 --unlogged-tables testdb

$ pgbench -c 4 -j 4 -t 500000 testdb
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 4
query mode: simple
number of clients: 4
number of threads: 4
number of transactions per client: 500000
number of transactions actually processed: 2000000/2000000
latency average = 1.052 ms
tps = 3803.228359 (including connections establishing)
tps = 3803.268465 (excluding connections establishing)
$ pgbench -i -s 4 --unlogged-tables testdb

$ pgbench -c 4 -j 4 -t 500000 testdb
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 4
query mode: simple
number of clients: 4
number of threads: 4
number of transactions per client: 500000
number of transactions actually processed: 2000000/2000000
latency average = 1.050 ms
tps = 3809.376424 (including connections establishing)
tps = 3809.413435 (excluding connections establishing)
$ pgbench -i -s 4 --unlogged-tables testdb

$ pgbench -c 1 -j 1 -t 500000 testdb
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 4
query mode: simple
number of clients: 1
number of threads: 1
number of transactions per client: 500000
number of transactions actually processed: 500000/500000
latency average = 0.839 ms
tps = 1191.747452 (including connections establishing)
tps = 1191.761725 (excluding connections establishing)
pgbench -i -s 4 --unlogged-tables testdb

pgbench -c 1 -j 1 -t 500000 testdb
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 4
query mode: simple
number of clients: 1
number of threads: 1
number of transactions per client: 500000
number of transactions actually processed: 500000/500000
latency average = 0.840 ms
tps = 1190.629978 (including connections establishing)
tps = 1190.640484 (excluding connections establishing)
$ pgbench -i -s 4 --unlogged-tables testdb

$ pgbench -c 1 -j 1 -t 500000 testdb
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 4
query mode: simple
number of clients: 1
number of threads: 1
number of transactions per client: 500000
number of transactions actually processed: 500000/500000
latency average = 0.840 ms
tps = 1190.574792 (including connections establishing)
tps = 1190.590501 (excluding connections establishing)

repeatable read

$ pgbench -i -s 4 --unlogged-tables testdb

$ pgbench -c 4 -j 4 -t 500000 testdb
starting vacuum...end.
client 2 script 0 aborted in command 8 query 0: ERROR:  could not serialize access due to concurrent update
client 3 script 0 aborted in command 8 query 0: ERROR:  could not serialize access due to concurrent update
client 0 script 0 aborted in command 8 query 0: ERROR:  could not serialize access due to concurrent update
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 4
query mode: simple
number of clients: 4
number of threads: 4
number of transactions per client: 500000
number of transactions actually processed: 500001/2000000
latency average = 3.343 ms
tps = 1196.411218 (including connections establishing)
tps = 1196.424449 (excluding connections establishing)
Run was aborted; the above results are incomplete.
$ pgbench -i -s 4 --unlogged-tables testdb

$ pgbench -c 4 -j 4 -t 500000 testdb
starting vacuum...end.
client 3 script 0 aborted in command 8 query 0: ERROR:  could not serialize access due to concurrent update
client 2 script 0 aborted in command 8 query 0: ERROR:  could not serialize access due to concurrent update
client 1 script 0 aborted in command 8 query 0: ERROR:  could not serialize access due to concurrent update
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 4
query mode: simple
number of clients: 4
number of threads: 4
number of transactions per client: 500000
number of transactions actually processed: 500001/2000000
latency average = 3.345 ms
tps = 1195.807328 (including connections establishing)
tps = 1195.822348 (excluding connections establishing)
Run was aborted; the above results are incomplete.
$ pgbench -i -s 4 --unlogged-tables testdb

$ pgbench -c 1 -j 1 -t 500000 testdb
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 4
query mode: simple
number of clients: 1
number of threads: 1
number of transactions per client: 500000
number of transactions actually processed: 500000/500000
latency average = 0.858 ms
tps = 1165.395653 (including connections establishing)
tps = 1165.411007 (excluding connections establishing)
$ pgbench -i -s 4 --unlogged-tables testdb

$ pgbench -c 1 -j 1 -t 500000 testdb
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 4
query mode: simple
number of clients: 1
number of threads: 1
number of transactions per client: 500000
number of transactions actually processed: 500000/500000
latency average = 0.837 ms
tps = 1195.096627 (including connections establishing)
tps = 1195.108248 (excluding connections establishing)

serializable

$ pgbench -i -s 4 --unlogged-tables testdb

$ pgbench -c 4 -j 4 -t 500000 testdb
starting vacuum...end.
client 1 script 0 aborted in command 8 query 0: ERROR:  could not serialize access due to concurrent update
client 2 script 0 aborted in command 9 query 0: ERROR:  could not serialize access due to read/write dependencies among transactions
DETAIL:  Reason code: Canceled on identification as a pivot, during conflict in checking.
HINT:  The transaction might succeed if retried.
client 3 script 0 aborted in command 9 query 0: ERROR:  could not serialize access due to read/write dependencies among transactions
DETAIL:  Reason code: Canceled on identification as a pivot, during conflict in checking.
HINT:  The transaction might succeed if retried.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 4
query mode: simple
number of clients: 4
number of threads: 4
number of transactions per client: 500000
number of transactions actually processed: 500000/2000000
latency average = 3.409 ms
tps = 1173.402161 (including connections establishing)
tps = 1173.414763 (excluding connections establishing)
Run was aborted; the above results are incomplete.
$ pgbench -i -s 4 --unlogged-tables testdb

$ pgbench -c 4 -j 4 -t 500000 testdb
starting vacuum...end.
client 1 script 0 aborted in command 9 query 0: ERROR:  could not serialize access due to read/write dependencies among transactions
DETAIL:  Reason code: Canceled on identification as a pivot, during conflict in checking.
HINT:  The transaction might succeed if retried.
client 3 script 0 aborted in command 9 query 0: ERROR:  could not serialize access due to read/write dependencies among transactions
DETAIL:  Reason code: Canceled on identification as a pivot, during conflict in checking.
HINT:  The transaction might succeed if retried.
client 0 script 0 aborted in command 7 query 0: ERROR:  could not serialize access due to read/write dependencies among transactions
DETAIL:  Reason code: Canceled on identification as a pivot, during write.
HINT:  The transaction might succeed if retried.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 4
query mode: simple
number of clients: 4
number of threads: 4
number of transactions per client: 500000
number of transactions actually processed: 500000/2000000
latency average = 3.405 ms
tps = 1174.618390 (including connections establishing)
tps = 1174.623363 (excluding connections establishing)
Run was aborted; the above results are incomplete.
$ pgbench -i -s 4 --unlogged-tables testdb

$ pgbench -c 1 -j 1 -t 500000 testdb
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 4
query mode: simple
number of clients: 1
number of threads: 1
number of transactions per client: 500000
number of transactions actually processed: 500000/500000
latency average = 0.850 ms
tps = 1175.877155 (including connections establishing)
tps = 1175.886246 (excluding connections establishing)
$ pgbench -i -s 4 --unlogged-tables testdb

$ pgbench -c 1 -j 1 -t 500000 testdb
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 4
query mode: simple
number of clients: 1
number of threads: 1
number of transactions per client: 500000
number of transactions actually processed: 500000/500000
latency average = 0.851 ms
tps = 1174.909483 (including connections establishing)
tps = 1174.917468 (excluding connections establishing)