jsonbでpgbench
そういえば、今までなんでやってなかったのかな、と思っていたけどpgbenchのカスタムクエリ機能を使って、pgbenchデータをJSONB化したテーブルにアクセスして、どのくらいの性能が出るのかを試してみた。
今回は、検索キーをbtree式インデックスで検索する方式と、ginインデックスで検証する方式の二通りを試してみた。
準備
pgbenchでスケール=10のデータをロードしておき、そのデータから以下のようなSQLでJSONデータを作成する。
COPY (SELECT row_to_json(pgbench_accounts.*) FROM pgbench_accounts) TO '/tmp/accounts.txt' ; COPY (SELECT row_to_json(pgbench_branches.*) FROM pgbench_branches) TO '/tmp/branches.txt' ; COPY (SELECT row_to_json(pgbench_tellers.*) FROM pgbench_tellers) TO '/tmp/tellers.txt' ; COPY (SELECT row_to_json(pgbench_history.*) FROM pgbench_history) TO '/tmp/history.txt' ;
まあ、pgbench_historyは最初は0件だけど、いちおー作っておく。
btree式インデックス方式
こんな感じでテーブルの定義とインデックスを定義し、スケール10(pgbench_accountsが100万件)のデータをロードして、btree式インデックスを設定しておく。
テーブル定義とインデックス定義
[nuko@localhost jsonb]$ cat jsonb_bench_i.sh
#!/bin/sh
DBNAME=bench_jsonb
cp accounts.txt /tmp
cp branches.txt /tmp
cp history.txt /tmp
cp tellers.txt /tmp
dropdb ${DBNAME}
createdb ${DBNAME}
psql ${DBNAME} -c "CREATE TABLE pgbench_accounts (data jsonb);"
psql ${DBNAME} -c "CREATE TABLE pgbench_branches (data jsonb);"
psql ${DBNAME} -c "CREATE TABLE pgbench_tellers (data jsonb);"
psql ${DBNAME} -c "CREATE TABLE pgbench_history (data jsonb);"
psql ${DBNAME} -c "COPY pgbench_accounts (data) FROM '/tmp/accounts.txt';"
psql ${DBNAME} -c "COPY pgbench_branches (data) FROM '/tmp/branches.txt';"
psql ${DBNAME} -c "COPY pgbench_tellers (data) FROM '/tmp/tellers.txt';"
psql ${DBNAME} -c "COPY pgbench_history (data) FROM '/tmp/history.txt';"
psql ${DBNAME} -c "CREATE INDEX pgbench_accounts_pkey ON pgbench_accounts USING btree (((data->>'aid')::int));"
psql ${DBNAME} -c "CREATE INDEX pgbench_branches_pkey ON pgbench_branches USING btree (((data->>'bid')::int));"
psql ${DBNAME} -c "CREATE INDEX pgbench_tellers_pkey ON pgbench_tellers USING btree (((data->>'tid')::int));"
rm /tmp/accounts.txt
rm /tmp/branches.txt
rm /tmp/history.txt
rm /tmp/tellers.txt
[nuko@localhost jsonb]$
実行スクリプト
この状態で以下の2つのファイルを作成して、pgbenchのカスタムクエリモードで実行する。
どちらもpgbenchの参照のみ/参照更新ありで実行されるクエリをベースにしている。
- 参照のみ
[nuko@localhost jsonb]$ cat bench-jsonb-select.sql \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; SELECT data->>'abalance' FROM pgbench_accounts WHERE (data->>'aid')::int = :aid; END; [nuko@localhost jsonb]$
- 更新あり
[nuko@localhost jsonb]$ cat bench-jsonb.sql
\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 pgbench_accounts SET data = json_build_object('aid', (data->>'aid')::int, 'bid', (data->>'bid')::int, 'abalance', (data->>'abalance')::int + :delta , 'filler', data->>'filler')::jsonb WHERE (data->>'aid')::int = :aid;
SELECT data->>'abalance' FROM pgbench_accounts WHERE (data->>'aid')::int = :aid;
UPDATE pgbench_tellers SET data = json_build_object('tid', (data->>'tid')::int, 'bid', (data->>'bid')::int, 'tbalance', (data->>'tbalance')::int + :delta, 'filler', data->>'filler')::jsonb WHERE (data->>'tid')::int = :tid;
UPDATE pgbench_branches SET data = json_build_object('bid', (data->>'bid')::int, 'bbalance', (data->>'bbalance')::int + :delta, 'filler', data->>'filler')::jsonb WHERE (data->>'bid')::int = :bid;
INSERT INTO pgbench_history (data) VALUES ( json_build_object('tid', :tid, 'bid', :bid, 'aid', :aid, 'delta', :delta, 'mtime', CURRENT_TIMESTAMP, 'filler', ' ')::jsonb);
END;
[nuko@localhost jsonb]$ 更新がちょい長くて如何にも遅そうなんだけど仕方がない。
ginインデックス版
今度はginインデックスと条件判定に @> を使うパターン。
テーブル定義とインデックス定義
[nuko@localhost jsonb]$ cat jsonb_bench_gin_i.sh
#!/bin/sh
DBNAME=bench_jsonb
cp accounts.txt /tmp
cp branches.txt /tmp
cp history.txt /tmp
cp tellers.txt /tmp
dropdb ${DBNAME}
createdb ${DBNAME}
psql ${DBNAME} -c "CREATE TABLE pgbench_accounts (data jsonb);"
psql ${DBNAME} -c "CREATE TABLE pgbench_branches (data jsonb);"
psql ${DBNAME} -c "CREATE TABLE pgbench_tellers (data jsonb);"
psql ${DBNAME} -c "CREATE TABLE pgbench_history (data jsonb);"
psql ${DBNAME} -c "COPY pgbench_accounts (data) FROM '/tmp/accounts.txt';"
psql ${DBNAME} -c "COPY pgbench_branches (data) FROM '/tmp/branches.txt';"
psql ${DBNAME} -c "COPY pgbench_tellers (data) FROM '/tmp/tellers.txt';"
psql ${DBNAME} -c "COPY pgbench_history (data) FROM '/tmp/history.txt';"
psql ${DBNAME} -c "CREATE INDEX pgbench_accounts_gin ON pgbench_accounts USING gin (data);"
psql ${DBNAME} -c "CREATE INDEX pgbench_branches_gin ON pgbench_branches USING gin (data);"
psql ${DBNAME} -c "CREATE INDEX pgbench_tellers_gin ON pgbench_tellers USING gin (data);"
rm /tmp/accounts.txt
rm /tmp/branches.txt
rm /tmp/history.txt
rm /tmp/tellers.txt
[nuko@localhost jsonb]$
実行スクリプト
- 参照のみ
\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;
SELECT data->>'abalance' FROM pgbench_accounts WHERE data @> json_build_object('aid', :aid )::jsonb;
END;
- 更新あり
\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 pgbench_accounts SET data = json_build_object('aid', (data->>'aid')::int, 'bid', (data->>'bid')::int, 'abalance', (data->>'abalance')::int + :delta , 'filler', data->>'filler')::jsonb WHERE data @> json_build_object('aid', :aid )::jsonb;
SELECT data->>'abalance' FROM pgbench_accounts WHERE data @> json_build_object('aid', :aid )::jsonb;
UPDATE pgbench_tellers SET data = json_build_object('tid', (data->>'tid')::int, 'bid', (data->>'bid')::int, 'tbalance', (data->>'tbalance')::int + :delta, 'filler', data->>'filler')::jsonb WHERE data @> json_build_object('tid', :tid )::jsonb;
UPDATE pgbench_branches SET data = json_build_object('bid', (data->>'bid')::int, 'bbalance', (data->>'bbalance')::int + :delta, 'filler', data->>'filler')::jsonb WHERE data @> json_build_object('bid', :bid )::jsonb;
INSERT INTO pgbench_history (data) VALUES ( json_build_object('tid', :tid, 'bid', :bid, 'aid', :aid, 'delta', :delta, 'mtime', CURRENT_TIMESTAMP, 'filler', ' ')::jsonb);
END;
比較用
通常のデータ型を使うpgbench用のスクリプト。今回は、このファイルをカスタムクエリモードで動かす。
[nuko@localhost jsonb]$ cat ../bench bench-select.sql bench.sql [nuko@localhost jsonb]$ cat ../bench.sql \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 pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid; SELECT abalance FROM pgbench_accounts WHERE aid = :aid; UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid; UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid; INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP); END; [nuko@localhost jsonb]$
結果
- 手元の環境だと -c 2 くらいがピークらしいｗので、とりあえず -c 2 -t 10000 で動作させた。
- とりあえずtpsのグラフ。
- 参照のみなら、JSONB型＋btree式インデックスも、通常の型と遜色ない性能が出せているかも。
- ginインデックスの参照は、btree式インデックスと比較すると少々遅い・・・
- 更新が入るとやはりJSONB型は苦手・・・
- 特にginインデックスを設定したJSONB型の更新は鬼門ともいえる遅さ。
- ginインデックスは、ログ蓄積モデルのような、INSERTとSELECTのみを使う用途向きなのかな。
