Re: Identify root-cause for intermittent spikes

Thanks, Michael.

Due to reactive nature of the intermittent alerts, Is there any table which
stores the historical information / periodic snapshots captured from the
pg_stat_activity view?

On Tue, Oct 11, 2022 at 5:18 PM MichaelDBA <MichaelDBA(at)sqlexec(dot)com> wrote:

> Hello,
>
> Your problem is probably, too many active, concurrent connections. Get it
> from here the db directly:
> select datname, usename, application_name, substring(query, 1, 80) query
> from pg_stat_activity where state in ('active','idle in transaction');
>
> Compare the number of rows returned with the number of vCPUs. If it's
> more than double the number of vCPUs in your AWS instance class, then you
> are cpu saturated.
>
> Regards,
>
> Michael Vitale
>
>
> Sengottaiyan T wrote on 10/11/2022 7:06 AM:
>
> Hi All,
>>
>> I'm looking for suggestions:
>>
>> Environment: AWS PostgreSQL RDS instance - Version 14.3
>> Operations support gets intermittent alerts from the monitoring tool
>> through AWS cloud watch metrics on Disk Queue Depth, CPU burst-credit & CPU
>> Utilization.
>> I would like to understand what is causing the spike - is the number of
>> logon's increased, (or) number of transactions per second increased, (or)
>> SQL execution picked wrong plan and the long running (I/O, CPU or memory
>> intensive) SQL is increasing load on server (cause and effect scenario)
>> etc.,
>>
>> Due to the reactive nature of the issues, we rely on the metrics gathered
>> in the AWS cloud watch monitoring (for the underlying OS stats),
>> Performance Insights (for the DB performance) and correlate SQL queries
>> with pg_Stat_Statements view. But the data in the view is an aggregated
>> stats. And, I'm looking to see the deltas compared to normal runs.
>> How should I approach and get to the root-cause?
>>
>> AppDynamics is already configured for the RDS instance. Are there any
>> open source monitoring tools available which would help to capture and
>> visualize the deltas?
>>
>> Thanks,
>> Senko
>>
>
>
>
>