Re: Autoanalyze CPU usage

Thanks for confirming that it is the end timestamp, the doc wasn't quite
clear if it was the start or end.

There is a gap in our monitoring that makes diagnosis of such events very
difficult after the fact. Something like a 10-sec periodic dump of
pg_stat_activity along with a similar dump of pg_top would have been very
helpful here.

-Habib

On Tue, Dec 19, 2017 at 11:15 PM, Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>
wrote:

> Habib Nahas wrote:
> > The CPU spike occurred between 13:05 - 13:15. last_autoanalyze for the
> table
> > shows a time of 12:49; last_autovacuum does not show any activity around
> > this time for any table. Checkpoint logs are also normal around this
> time.
> > I'd like to understand if there are any other sources of activity I
> > should be checking for that would account for the spike.
>
> last_autoanalyze is set after autoanalyze is done, so that would suggest
> that autoanalyze is not the problem.
>
> It can be tough to figure out where the activity is coming from unless
> cou can catch it in the act. You could log all statements (though the
> amount
> of log may be prohibitive and can cripple performance), you could log
> just long running statements in the hope that these are at fault, you
> could log connections and disconnections and hope to find the problem
> that way. Maybe logging your applications can help too.
>
> Yours,
> Laurenz Albe
>