pgbench で PostgreSQL に半永久的に負荷をかけ続ける

pgbench で PostgreSQL に半永久的に負荷をかけ続ける手順をメモ。

スクリプト

• pgbench_infinite_loop.sh

#!/bin/bash
export LANG=C
export PGPASSWORD=<パスワード>
ENDPOINT=<ホスト名>

BASE_NAME=$(basename $0)
STEM="${BASE_NAME%.*}"
DATE=`date '+%Y-%m-%d-%H%M%S'`

while :
do
	pgbench -n -r -c 8 -j 8 -t 100000 -U awsuser -h ${ENDPOINT} -d mydb >> ${STEM}-${DATE}.log 2>/dev/null
done

• pgbench をインストール

$ sudo yum -y install postgresql
$ sudo yum -y install postgresql-contrib

• データを登録する

$ pgbench -i -s 1000 -U awsuser -h ******.******.ap-northeast-1.rds.amazonaws.com -d mydb

実行

• 実行する

$ nohup ./pgbench_infinite_loop.sh &

モニタリング

• ログをモニタリングする。

$ tail -f pgbench_infinite_loop-2019-03-03-175614.log
pghost: aurora-posgres10-r5l.cluster-*****.ap-northeast-1.rds.amazonaws.com pgport:  nclients: 8 nxacts: 100000 dbName: mydb
transaction type: TPC-B (sort of)
scaling factor: 1000
query mode: simple
number of clients: 8
number of threads: 8
number of transactions per client: 100000
number of transactions actually processed: 800000/800000
tps = 359.656559 (including connections establishing)
tps = 359.660528 (excluding connections establishing)
statement latencies in milliseconds:
	0.034531	\set nbranches 1 * :scale
	0.029953	\set ntellers 10 * :scale
	0.030349	\set naccounts 100000 * :scale
	0.024340	\setrandom aid 1 :naccounts
	0.022874	\setrandom bid 1 :nbranches
	0.020391	\setrandom tid 1 :ntellers
	0.018264	\setrandom delta -5000 5000
	2.382775	BEGIN;
	3.217786	UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
	2.441472	SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
	2.465225	UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
	2.486259	UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
	2.445141	INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
	6.599387	END;

• Performance Insights でモニタリングする（今回は Aurora PostgreSQL互換で実行したため）。

補足

pgbench でパスワードの自動入力は PGPASSWORD 環境変数などでできる。

You have four choices regarding the password prompt:

1. set the PGPASSWORD environment variable. For details see the manual:
http://www.postgresql.org/docs/current/static/libpq-envars.html
2. use a .pgpass file to store the password. For details see the manual:
http://www.postgresql.org/docs/current/static/libpq-pgpass.html
3. use "trust authentication" for that specific user:
http://www.postgresql.org/docs/current/static/auth-methods.html#AUTH-TRUST
4. use a connection URI that contains everything:
http://www.postgresql.org/docs/current/static/libpq-connect.html#AEN42532

postgresql - How to use psql with no password prompt? - Database Administrators Stack Exchange