pg_stat_activity-如何查看正在运行的存储过程中的当前活动 [英] pg_stat_activity - how to see current activity inside a running stored procedure

问题描述

我有一个postgres环境，其中存储过程被广泛使用，并且这些过程很复杂，因此需要调用其他存储过程.

I have a postgres environment where stored procedures are used extensively and these procedures are complex and call other stored procedures.

查询pg_stat_activity表时，会得到一条记录，该记录显示了正在调用的过程，但未给出有关过程中正在调用哪些sql语句的指示.

When I query the pg_stat_activity table, I get a record that shows the procedure being called, but no indication is given on what sql statements are being called within the procedure.

例如选择myprocname($ 1，$ 2，$ 3，$ 4)

eg. select myprocname($1,$2,$3,$4)

结果，我无法隔离可能导致性能问题的特定SQL语句.有问题的RDS是一个Amazon RDS实例，因此无法进行操作系统级别的对该进程的访问(据我所能确定的那样).

As a result, I am unable to isolate specific SQL statements which may be causing a performance issue. The RDS in question is an amazon RDS instance, so OS level access to the process is not available (as far as I have been able to determine).

到目前为止，我在google上搜索了有关该主题的信息，但运气不佳.

I have searched for information on google on this subject without much luck to this point.

这个问题不是关于特定场景的，因为代码是专有的，我无法直接讨论，而是关于当我习惯使用的主要源代码没有提供足够详细信息时如何执行这种调整.

This question is not about the specific scenario as the code is proprietary and I can't discuss it directly, but on how to perform such tuning when the main source I am used to using is not providing enough detail.

推荐答案

有两种方法可以查看函数中的SQL语句花费多长时间:

There are two ways to see how long SQL statements inside functions take:

1. 启用 auto_explain ，其中 auto_explain.log_nested_statements = on .这样您就可以在PostgreSQL日志文件中的函数中查看SQL语句的持续时间和执行计划.

1. Enable auto_explain with auto_explain.log_nested_statements = on. That will allow you to see the duration and the execution plans of the SQL statements inside the function in the PostgreSQL log file.

启用 pg_stat_statements 和设置参数 pg_stat_statements.track = all .

然后 pg_stat_statements 将跟踪函数中SQL语句的信息.这样，您可以查看哪些语句消耗了数据库中的时间.

Then pg_stat_statements will track information for the SQL statements inside a function. That way you can see which of your statements consume how much time in the database.

这篇关于pg_stat_activity-如何查看正在运行的存储过程中的当前活动的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！