例子:
长事务 30 min
select datname,usename,query,xact_start,now()-xact_start xact_duration,query_start,now()-query_start query_duration,state from pg_stat_activity where state<>
$$
idle
$$
and (backend_xid is not null or backend_xmin is not null) and now()-xact_start > interval
$$
30 min
$$
order by xact_start;
2PC长事务 30 min
select name,statement,prepare_time,now()-prepare_time,parameter_types,from_sql from pg_prepared_statements where now()-prepare_time > interval
$$
30 min
$$
order by prepare_time;
可以写成脚本
https://github.com/digoal/pgsql_admin_script/blob/master/generate_report.sh
echo "----->>>---->>> 长事务, 2PC: "
psql -h $PGHOST -p $PGPORT -U $PGUSER -d $PGDATABASE --pset=pager=off -q -x -c 'select datname,usename,query,xact_start,now()-xact_start xact_duration,query_start,now()-query_start query_duration,state from pg_stat_activity where state<>
$$
idle
$$
and (backend_xid is not null or backend_xmin is not null) and now()-xact_start > interval
$$
30 min
$$
order by xact_start'
psql -h $PGHOST -p $PGPORT -U $PGUSER -d $PGDATABASE --pset=pager=off -q -x -c 'select name,statement,prepare_time,now()-prepare_time,parameter_types,from_sql from pg_prepared_statements where now()-prepare_time > interval
$$
30 min
$$
order by prepare_time'
echo "建议: "
echo " 长事务过程中产生的垃圾, 无法回收, 建议不要在数据库中运行LONG SQL, 或者错开DML高峰时间去运行LONG SQL. 2PC事务一定要记得尽快结束掉, 否则可能会导致数据库膨胀. "
echo " 参考: http://blog.163.com/digoal@126/blog/static/1638770402015329115636287/ "
echo -e "\n"
版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。