履歴情報つきinteger型をpgbenchモデルで動かしてみた


ということで、先週作成した履歴情報付きinteger(疑似)型を、pgbenchモデルに組み込んで、どのくらい性能が劣化するものか確認してみた。

測定モデル

基本的にはpgbanechをベースにした。オリジナルのpgbenchとは以下のような違いがある。

  • historyテーブルを削除。
    • 更新記録をhistoryテーブルではなく、履歴情報付きinteger型自体に持たせるため。
  • accountsのabalance, branchesのbbalance, tellersのtbalanceをintegerではなく、履歴情報つきinteger型(jsonb)で定義する。
テーブル定義

実際の定義はこんな感じ。

CREATE UNLOGGED TABLE accounts (aid int primary key, bid int, abalance jsonb, filler char(84)) WITH (fillfactor = 90);
CREATE UNLOGGED TABLE branches (bid int primary key, bbalance jsonb, filler char(88)) WITH (fillfactor = 90);
CREATE UNLOGGED TABLE tellers (tid int primary key, bid int, tbalance jsonb, filler char(84)) WITH (fillfactor = 90);

例によって効果を見えやすくするために、テーブルはUNLOGGED TABLEで作成してWAL出力をスキップする。
また、fillfactorを90にしてフツーならHOT更新が効く状態にしておく。

初期データ

初期データは、以下の手順で生成する。

  • まず、フツーにpgbenchでデータを初期生成モード(-i -s スケール数)で生成する。
  • 以下のSELECT+COPY文でダンプする。
    • abalance, bbalance, tbalanceをそれぞれ create_tt_int()関数経由でJSONBデータ化した値で初期化する。
COPY (SELECT aid, bid, create_tt_int(abalance), filler FROM pgbench_accounts) TO '/tmp/accounts.txt';
COPY (SELECT bid, create_tt_int(bbalance), filler FROM pgbench_branches) TO '/tmp/branches.txt';
COPY (SELECT tid, bid, create_tt_int(tbalance), filler FROM pgbench_tellers) TO '/tmp/tellers.txt';

これをさっき作成したテーブルにCOPY FROMで叩き込む。

COPY accounts FROM '/tmp/accounts.txt';
COPY branches FROM '/tmp/branches.txt';
COPY tellers  FROM '/tmp/tellers.txt';

なお、各テーブルの初期レコード数は

テーブル名 レコード数
accounts 1000000
branches 10
tellers 100

今回のベンチマークモデルでは、この件数は最後まで変動しない。

実行トランザクション
  • pgbenchのカスタムクエリモードで実行する。
  • カスタムクエリのファイル内容は以下。
\set scale 10
\set nbranches :scale
\set ntellers 10 * :scale
\set naccounts 100000 * :scale
\setrandom aid 1 :naccounts
\setrandom bid 1 :nbranches
\setrandom tid 1 :ntellers
\setrandom delta -5000 5000
BEGIN;
UPDATE accounts SET abalance = add_tt_int(abalance, value(abalance) + :delta) WHERE aid = :aid;
SELECT value(abalance) FROM accounts WHERE aid = :aid;
UPDATE tellers SET tbalance = add_tt_int(tbalance, value(tbalance) + :delta) WHERE tid = :tid;
UPDATE branches SET bbalance = add_tt_int(bbalance, value(bbalance) + :delta) WHERE bid = :bid;
END;

オリジナルのpgebnchとの違いは以下。

  • accountsのabalance, branchesのbbalance, tellersのtbalanceの更新方法のみ。
    • オリジナルは元の値に :delta を加算した値でUPDATE
    • 履歴情報つきintの場合は、value()で取り出した値を :delta で加算し、その値を、add_tt_int()で更新(履歴追加)した結果をUPDATE
  • WHERE句評価用の各テーブルidはそのままなので、基本的にはUPDATE/SELECT対象のレコードを選択するまでの処理はオリジナルとは変わらないはず。
測定環境
  • 今回の測定環境も例によって、Let's note SX4上のVMWare＋CentOS7。
  • PostgreSQL 9.5.1(ビルドバージョン)
  • postgresql.confの設定は基本デフォルトで。
    • (悲観的ではあるが)たぶん、性能向上につながるようなパラメータはないような気がする。
    • UNLOGGED TABLEなのでWALまわりのパラメータも関係なし。
測定方式
  • 以下の測定を10回連続で行う。
    • pgbenchのカスタムモードで実行
    • 事前バキュームはなし
    • 同時実行数は1
    • トランザクション数は1000
  • 最終的には10000トランザクションが実行される。
  • 実行後に、tps、クエリのレイテンシ、テーブルサイズを測定する。

予測

  • branchesテーブルへの更新劣化が原因で、徐々に性能が劣化していくはず。
  • branchesテーブルが非常に巨大になるはず。

測定結果

tps

レイテンシ

  • やはりbranchesテーブルのレイテンシの劣化が際立っている。線形に劣化しているなあ。ここのレイテンシの悪化がtps低下の主要因ですね。
    • 更新結果の生成処理(tt_int_add())の時間と、UPDATE処理時間の合わせ技か。
    • 最終的に平均世代数は1000になる。
  • tellersもbranchesほどではないが、線形に劣化している。
    • 最終的に平均世代数は100になる。
  • accountsは1件あたりの世代数がほとんど増えないので、更新性能の劣化はほとんどない。
  • またaccountsへの参照も同様にほぼ劣化が見られない。
データサイズ
  • 今回データサイズとしてはpg_relation_size(), pg_indexes_size(), pg_total_relation_size()の3つを取得した。
  • accountsについては、10000トランザクションでは全くサイズの変動はなかったので、プロット対象外としている。
  • また、pg_indexes_size()についても(一応測定はしたけど)サイズの変動はなし。



  • branchesのpg_relation_size()の結果を見ると、早々に増加傾向が停まっている。
    • が、pg_total_reration_size()の結果を見ると順調に肥大化してますなｗ
    • つまり、branchesのレコードについては、早々に全レコードともTOAST対象になってしまったということかと。
  • tellersの場合は、branchesの1/10の速度で世代数が増えていくためか、途中までは段階的にpg_relation_sizeも増加していくが、8000トランザクション以降は(おそらくは)その値のままになりそう＝全レコード(100行)が全て、この時点でTOAST対象になったのかな。
    • なので、以降はpg_total_reration_size()が線形に増加していくのだろう。

結論

結論も何もないような気がするが

  • pgbenchの bbalance, tbalnceのような非常に更新が多いカラムに、履歴情報付き整数型を使うとカジュアルに性能劣化して死ぬ
  • 記録する世代数としてはやはり数十程度の用途にしないと、ちょっと厳しそう・・・
  • 今回の試作では対象のデータ型をinteger型にしたけど、それより数回〜数十回程度の更新があると思われるTEXT型を履歴情報付きデータ型のターゲットにしたほうがいいのかもなあ。