To start with, you can set log_min_duration_statement to 1500ms and log_statement to the required one which will give you the statement that ran for more than 1.5 s. Then you know what to do!
For tools:
1. pgcluu
2. PoWA
Best Regards,
Rijo Roy
On Thursday, 4 April, 2019, 11:07:35 pm IST, Mamet, Eric (GfK) <Eric.Mamet@gfk.com> wrote:
Hi there,
I would like to monitor our postgresql instance under AWS-RDS to get some alert (or log) if any query runs over a certain amount of time, like 1.5 seconds.
I would like to know which query took over that time (and how long), when and which parameters it used.
The exact parameters are important because the amount of data retrieved varies a lot depending on parameters.
I would like to know when it happened to be able to correlate it with the overall system activity.
I came across
·pg_stat_statements is very useful BUT it gives me stats rather than specific executions. In particular, I don’t know the exact time it happened and the parameters used
·log_statement but this time I don’t see how I would filter on “slow” queries and it seems dumped into the RDS log… not very easy to use and maybe too heavy for a production system
·pg_hero is great but looks like an interactive tool (unless I missed something) and I don’t think it gives me the exact parameters and time (not sure…)
