Lazy Diary @ Hatena Blog

PowerShell / Java / miscellaneous things about software development, Tips & Gochas. CC BY-SA 4.0/Apache License 2.0

pgbenchでINSERT中心のトランザクションのTPSをできるだけ高くする

PostgreSQL

動機

DBのチューニング方法なんかの書籍を見てみても、性能向上方法として語られているのはSELECTのチューニングが主で、INSERTに関する記載はあまり見当たりませんでした。ですが、大量のデバイスから飛んできたデータをMQTTやらHTTPやらで受けて、それを生のままDBMSに登録する……というワークロードの場合、INSERTがトランザクションの中心になるので、DBMSのベンチマークもINSERT中心のワークロードにする必要があります。

DBMSがPostgreSQLの場合、性能単価の測定にはpgbenchを使うと思いますが、ここで設定を誤っていたりすると、PostgreSQLの実力値を見誤ったりします（結果、DBサーバにオーバースペックなマシンを用意してしまったりする）。ハードウェアのリソース設計はそれはそれで行うとして、ここでは主にpgbenchによる性能単価測定の際にTPSをできるだけ高くする方法（＝pgbenchの設定がマズくてPostgreSQLの性能を過少に見積らない方法）をまとめておきます。

前提とするワークロード

  • テーブルはpgbench組込みのトランザクションで使われるpgbench_historyを使います。
  • 1回のトランザクションで8レコードをINSERTします。
  • INSERT以外のトランザクションは実行しません（無風測定です）。
  • 同時接続クライアント数は20、実行時間20秒で確認しました。
  • ここではオプションの有効性の確認を目的として、pgbenchとPostgreSQLを同じマシンで実行しています。正確なTPSを知りたい場合はpgbenchはPostgreSQLとは別マシンで実行してください（後述）。
  • OSはWindows 10です。
  • CPUはIntel(R) Core(TM) i7-8750H CPU @ 2.20GHz（6コア12スレッド）、ストレージはADATA SU800NS38を使っています。

何もしない場合

以下のようなスクリプトファイルを作成して実行します。

\set nbranches 1 * :scale
\set ntellers 10 * :scale
\set naccounts 100000 * :scale
\set aid random(1,:naccounts)
\set bid random(1,:nbranches)
\set tid random(1,:ntellers)
\set delta random(-5000,5000)
BEGIN;
INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta+1, CURRENT_TIMESTAMP);
INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta+2, CURRENT_TIMESTAMP);
INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta+3, CURRENT_TIMESTAMP);
INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta+4, CURRENT_TIMESTAMP);
INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta+5, CURRENT_TIMESTAMP);
INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta+6, CURRENT_TIMESTAMP);
INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta+7, CURRENT_TIMESTAMP);
END;

pgbenchの実行オプションは以下の通りです。

pgbench.exe -U postgres -c 20 -T 20 -f C:\tmp\insert.pgbench test

この場合のTPSは3863TPSでした。

transaction type: C:\tmp\insert.pgbench
scaling factor: 1
query mode: simple
number of clients: 20
number of threads: 1
duration: 20 s
number of transactions actually processed: 77323
latency average = 5.177 ms
tps = 3863.290450 (including connections establishing)
tps = 3873.891699 (excluding connections establishing)

いろいろ対策をほどこす

CPUの割り当てコア数を調整する（プロセッサアフィニティを設定している場合・DBと同じマシンでpgbenchを実行する場合）

PostgreSQLインスタンスに対するCPUの割り当てに応じて、TPSが高くなります。プロセッサアフィニティを設定している場合、PostgreSQLに割り当てるCPUコアを増やします。ただし、DBと同じマシンでpgbenchを実行する場合、すべてのCPUをPostgreSQLに割り当てると逆にTPSが下がるようです（pgbenchに割り当てるCPUがなくなるせい？）。そのためCPUアフィニティを調整して、いちばん高い性能が出るポイントを探ります。

Windowsの場合、サービスプロセスにはプロセッサアフィニティは設定できないので、PostgreSQLサービスは停止し、ユーザプロセスとしてPostgreSQLインスタンスを実行します。以下のようなPowerShellスクリプトを作成し、割り当てるCPUのスレッド数を1～12まで変化させて実行しました。

@('1','3','7','F','1F','3F','7F','FF','1FF','3FF','7FF','FFF') | ForEach-Object {
  Write-Host "`nCPU Affinity:" $_
  cmd.exe /c "start /affinity $_ /B c:\opt\PostgreSQL\12\bin\pg_ctl.exe start -w -s -D C:\opt\PostgreSQL\12\data"
  $env:PGPASSWORD='postgres'
  c:\opt\PostgreSQL\12\bin\pgbench.exe -U postgres -c 20 -T 20 -f C:\tmp\insert2.pgbench test
  c:\opt\PostgreSQL\12\bin\pg_ctl.exe stop -D "C:\opt\PostgreSQL\12\data"
}

結果は以下のグラフのようになりました。以下、設定を変えながら上記のスクリプトを実行していきます。 何もしない場合、いちばん性能がよいのは全6コア・12スレッドを使った場合で、3863TPSでした。

f:id:satob:20200518033138p:plain
設定ごとのpgbench TPS数

バルクインサート

スクリプトファイルを修正し、レコードごとにINSERTを実行していたところを、バルクインサートを行うよう修正します。

\set nbranches 1 * :scale
\set ntellers 10 * :scale
\set naccounts 100000 * :scale
\set aid random(1,:naccounts)
\set bid random(1,:nbranches)
\set tid random(1,:ntellers)
\set delta random(-5000,5000)
BEGIN;
INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP),
                                                                 (:tid, :bid, :aid, :delta+1, CURRENT_TIMESTAMP),
                                                                 (:tid, :bid, :aid, :delta+2, CURRENT_TIMESTAMP),
                                                                 (:tid, :bid, :aid, :delta+3, CURRENT_TIMESTAMP),
                                                                 (:tid, :bid, :aid, :delta+4, CURRENT_TIMESTAMP),
                                                                 (:tid, :bid, :aid, :delta+5, CURRENT_TIMESTAMP),
                                                                 (:tid, :bid, :aid, :delta+6, CURRENT_TIMESTAMP),
                                                                 (:tid, :bid, :aid, :delta+7, CURRENT_TIMESTAMP);
END;

PostgreSQLに3コア・6スレッドを使った場合が最も性能が高く、9375TPSになりました。

pgbenchのオプションに--protocol=preparedを指定

pgbenchとPostgreSQLの間の通信プロトコルは、デフォルトでは簡易問い合わせプロトコルになっています。ここで--protocol=extendedを設定すると拡張問い合わせプロトコルが、--protocol=preparedを指定するとプリペアドステートメントを伴う拡張問い合わせプロトコルが使用されます。ここではもっとも性能が高くなる--protocol=preparedを指定します。

バルクインサートと合わせて指定した場合、PostgreSQLに3コア・6スレッドを使った場合が最も性能が高く、11823TPSになりました。

postgresql.confでsynchronous_commit = offを指定

postgresql.confでsynchronous_commit = offを指定することで、DBがクラッシュした際に直近のコミット分のデータがロストするのと引き換えに、PostgreSQLの性能を上げることができます（データ破壊のリスクはありません）。

バルクインサート・--protocol=preparedと合わせて指定した場合、PostgreSQLに4コア・7スレッドを使った場合が最も性能が高く、13349TPSになりました。

pgbenchのオプションに-j nを指定

pgbenchはデフォルトではシングルスレッドで動作します。-jオプションで同時実行スレッド数を変更できます。 pgbenchのスレッド数を変更しながら試した結果、pgbenchに4スレッド、PostgreSQLに4コア・8スレッドを割り当てた場合が最も性能が高く、24007TPSになりました。

pgbenchをDBと別マシン（または別のCPUコア）で実行する

pgbenchでは10000TPS程度で1CPUを1スレッド使いきってしまうようです（パフォーマンスモニタでpgbenchのProcessor %が100程度になる）。上記の通りINSERTの性能はCPU-boundなようです。pgbenchがCPUを食うとそれだけベンチマークの結果に影響するので、（今回は行えませんでしたが）pgbenchはDBとは別マシン（または別のCPUコア）で実行した方がよいTPSが出るようです。上記のグラフが8スレッド以降で伸びていないのは、pgbenchの実行にまわせるCPU数が少なくなってしまっているためと思われます。