ablog

不器用で落着きのない技術者のメモ

一定間隔で pg_stat_statements を tsv に出力

PostgreSQL

一定間隔で pg_stat_statements を取得して tsv に出力する。

$ export LANG=C
$ psql "host=aurora-postgres117.cluster-******.ap-northeast-1.rds.amazonaws.com dbname=mydb port=5432 user=awsuser"
  • 10 秒間隔で pg_stat_statements を tsv に出力
\o pg_stat_statements.tsv
\a
\pset fieldsep '\t'
select b.usename, 
a.userid,
a.dbid,
a.queryid,
a.calls,
a.total_time,
a.min_time,
a.max_time,
a.mean_time,
a.stddev_time,
a.rows,
a.shared_blks_hit,
a.shared_blks_read,
a.shared_blks_dirtied,
a.shared_blks_written,
a.local_blks_hit,
a.local_blks_read,
a.local_blks_dirtied,
a.local_blks_written,
a.temp_blks_read,
a.temp_blks_written,
a.blk_read_time,
a.blk_write_time,
regexp_replace(a.query, '\n|\r|\r\n', ' ', 'g')
from pg_stat_statements a join pg_user b
on a.userid = b.usesysid
where b.usename = 'awsuser'
order by total_time desc
limit 50; \watch 10

参考