PostgreSQL で nextval() のスループットを検証する

準備

PostgreSQL に接続する。

$ psql "host=aurora-postgres117.cluster-******.ap-northeast-1.rds.amazonaws.com dbname=writer port=5432 user=awsuser"

シーケンスを作成する。

create sequence seqtest1 start with 1 increment by 1 cache 1;
create sequence seqtest100 start with 1 increment by 1 cache 100;

シーケンスの値を取得する。

select nextval('seqtest1');
 nextval
---------
       1
(1 row)

seqtest1.sql を作成する

select nextval('seqtest1');

seqtest100.sql を作成する

select nextval('seqtest100');

負荷をかける

pgbench で負荷をかける（cache=1）。

$ export PGPASSWORD=******
$ pgbench -r -c 100 -j 100 -n -t 100000 -f seqtest1.sql -U awsuser -h aurora-postgres117.cluster-******.ap-northeast-1.rds.amazonaws.com -d writer -p 5432  2>/dev/null

（中略）

transaction type: Custom query
scaling factor: 1
query mode: simple
number of clients: 100
number of threads: 100
number of transactions per client: 100000
number of transactions actually processed: 10000000/10000000
tps = 13757.569525 (including connections establishing)
tps = 13761.584387 (excluding connections establishing)
statement latencies in milliseconds:
	7.116529	select nextval('seqtest1');

pgbench で負荷をかける（cache=100）。

$ export PGPASSWORD=******
$ pgbench -r -c 100 -j 100 -n -t 100000 -f seqtest100.sql -U awsuser -h aurora-postgres117.cluster-******.ap-northeast-1.rds.amazonaws.com -d writer -p 5432  2>/dev/null

（中略）

transaction type: Custom query
scaling factor: 1
query mode: simple
number of clients: 100
number of threads: 100
number of transactions per client: 100000
number of transactions actually processed: 10000000/10000000
tps = 16653.325937 (including connections establishing)
tps = 16658.057594 (excluding connections establishing)
statement latencies in milliseconds:
	5.872230	select nextval('seqtest100');

結果

cache=1

f:id:yohei-a:20201202024504p:plain
f:id:yohei-a:20201202024612p:plain

cache=100

f:id:yohei-a:20201202031101p:plain
f:id:yohei-a:20201202031530p:plain

pg_stat_statements から平均実行時間を求める

select b.usename,
trunc(a.total_time) "total_time(s)",
a.calls,
trunc(cast(a.total_time as numeric) / a.calls, 6) "avg_time(s)",
a.query
from pg_stat_statements a join pg_user b
on a.userid = b.usesysid
where b.usename = 'awsuser'
order by total_time desc
limit 5;

 usename | total_time(s) |  calls   | avg_time(s) |                               query
---------+---------------+----------+-------------+--------------------------------------------------------------------
 awsuser |       1672291 | 40245873 |    0.041551 | select nextval($1)
 awsuser |           319 |    31729 |    0.010081 | DISCARD ALL
 awsuser |           279 |    36406 |    0.007673 | SET application_name TO 'psql'
 awsuser |           120 |    26417 |    0.004561 | SELECT current_setting($1)
 awsuser |            99 |       14 |    7.083443 | select b.usename, trunc(a.total_time) total_time, a.calls, a.query+
         |               |          |             | from pg_stat_statements a join pg_user b                          +
         |               |          |             | on a.userid = b.usesysid                                          +
         |               |          |             | where b.usename = $1                                              +
         |               |          |             | order by total_time desc                                          +
         |               |          |             | limit $2
(5 行)