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

01 概念介绍QPSQuery Per Second即每秒查询率是对一个特定的查询服务器在规定时间内所处理流量多少的衡量标准。TPSTransactions Per Second每秒传输的事务处理个数即服务器每秒处理的事务数。QPS和TPS的概念详细介绍https://zhuanlan.zhihu.com/p/37201571602 pg_stat_database从 pg_stat_database 视图可以看到xact_commit数据库中已经提交的事务数累加xact_rollback数据库中已经回滚的事务数累加只要获取当前值和下一秒的值相减再把这一秒提交和回滚的事务加起来即数据库的TPS值-- 再开个窗口做操作每0.01s操作一次\watch 0.01-- 问题SQL由于在同一事务中查询的值不会变所以TPS结果一直是0witha 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.datnameb.datname and a.datnamecurrent_database();-- 验证思路是对的,每次查询事务记录是会变动的select datname,xact_commit,xact_rollback from pg_stat_database where datnamecurrent_database();来即数据库的TPS值按照上面的思路如果是写shell脚本那是能实现的查询 - sleep 1s - 再次查询如果是要在数据库层实现可以用存储过程或函数来做-- 方式一存储过程之内置视图获取create or replace procedure get_tps() as $$declarexc1 int8;xr1 int8;xc2 int8;xr2 int8;beginSELECT 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$$declarev1 int8;v2 int8;beginselect 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$$declarexc1 int8;xr1 int8;xc2 int8;xr2 int8;beginSELECT 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 ;PART TWOPostgreSQL DBA最常用SQ捞到一位大佬整理的 PostgreSQL DBA最常用SQLhttps://github.com/digoal/blog/blob/master/202005/20200509_02.md-- TPS存储过程之函数方式获取create or replace procedure get_tps() as $$declarev1 int8;v2 int8;beginselect 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插件获取witha 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;