如何监控PostgreSQL数据库的TPS和QPS值

PostgreSQL获取数据库的TPS值

• 概念简单了解
• pg_stat_database
• 函数或存储过程方式获取
• 参阅材料

看腻了就来听听视频演示吧：https://www.bilibili.com/video/BV1V2421M7ux/

概念简单了解

• QPS：Query Per Second，即每秒查询率，是对一个特定的查询服务器在规定时间内所处理流量多少的衡量标准。
• TPS：Transactions Per Second（每秒传输的事务处理个数），即服务器每秒处理的事务数。

QPS和TPS的概念介绍：
https://zhuanlan.zhihu.com/p/372015716

pg_stat_database

从 pg_stat_database 视图可以看到

• xact_commit：数据库中已经提交的事务数（累加）
• xact_rollback：数据库中已经回滚的事务数（累加）

只要获取当前值和下一秒的值相减，再把这一秒提交和回滚的事务加起来，即数据库的TPS值

-- 再开个窗口做操作，每0.01s操作一次
\watch 0.01

-- 问题SQL：由于在同一事务中查询的值不会变，所以TPS结果一直是0
with                                               
a as (select datname,xact_commit xc,xact_rollback xr from pg_stat_database),   
b as (select datname,xact_commit xc,xact_rollback xr from pg_stat_database , pg_sleep(1))   
select a.datname,b.xc,a.xc,b.xr,a.xr,(b.xc-a.xc)+(b.xr-a.xr) tps from a,b where a.datname=b.datname and a.datname=current_database();

-- 验证思路是对的,每次查询事务记录是会变动的
select datname,xact_commit,xact_rollback from pg_stat_database where datname=current_database();
复制

按照上面的思路如果是写shell脚本那是能实现的（查询 -> sleep 1s -> 再次查询），如果是要在数据库层实现可以用存储过程或函数来做

函数或存储过程方式获取

-- 方式一：存储过程之内置视图获取
create or replace procedure get_tps() as $$
declare
  xc1 int8;
  xr1 int8;
  xc2 int8;
  xr2 int8;
begin
  SELECT xact_commit,xact_rollback into xc1,xr1 FROM pg_stat_database where datname = current_database();
  commit;
  perform pg_sleep(1);
  SELECT xact_commit,xact_rollback into xc2,xr2 FROM pg_stat_database where datname = current_database();
  commit;
  raise notice 'tps: %', (xc2-xc1)+(xr2-xr1);
end;
$$ language plpgsql ;

-- 方式二：函数之内置函数获取【推荐使用】
create or replace function get_tps_fun() returns numeric as
$$
declare
  v1 int8;
  v2 int8;
begin
  select txid_snapshot_xmax(txid_current_snapshot()) into v1;
  perform pg_sleep(1);
  select txid_snapshot_xmax(txid_current_snapshot()) into v2;
  return v2-v1;
end;
$$language plpgsql;

-- 方式一改用函数的写法无法正常获取TPS值，原理同上：在同一事务中查询的值不会变，所以TPS结果一直是0 【BUG】
create or replace function get_tps_fun2() returns numeric as
$$
declare
  xc1 int8;
  xr1 int8;
  xc2 int8;
  xr2 int8;
begin
  SELECT xact_commit,xact_rollback into xc1,xr1 FROM pg_stat_database where datname = current_database();
  perform pg_sleep(1);
  SELECT xact_commit,xact_rollback into xc2,xr2 FROM pg_stat_database where datname = current_database();
  return (xc2-xc1)+(xr2-xr1);
end;
$$ language plpgsql ;
复制

参阅材料

捞到一位大佬整理的 PostgreSQL DBA最常用SQL：
https://github.com/digoal/blog/blob/master/202005/20200509_02.md

-- TPS：存储过程之函数方式获取
create or replace procedure get_tps() as $$
declare
  v1 int8;
  v2 int8;
begin
  select txid_snapshot_xmax(txid_current_snapshot()) into v1;
  commit;
  perform pg_sleep(1);
  select txid_snapshot_xmax(txid_current_snapshot()) into v2;
  commit;
  raise notice 'tps: %', v2-v1;
end;
$$ language plpgsql ;

-- QPS：通过pg_stat_statements插件获取
with                                                 
a as (select sum(calls) s from pg_stat_statements),     
b as (select sum(calls) s from pg_stat_statements , pg_sleep(1))    select b.s-a.s from a,b;   
复制