Re: Gigantic load average spikes

On Mon, 1 Apr 2019 at 18:08, rihad <rihad(at)mail(dot)ru> wrote:
>
> > What exactly do you mean by "running processes"? I don't think I've ever
> > seen a Unix with only 1 to 3 running processes in total, so you are
> > probably referring to processes in a certain state. Runnable (R)?
> > Uninterruptible sleep (D)? Both? Something else?
>
> Just that, 250-300 running processes in top shown for a second. 250-300
> is the number of postgres worker processes used, but normally only 1-3
> of them are running according to top. At times of load FreeBSD (?)
> schedules all of them to run. This doesn't really put the machine on its
> knees, it just impacts load avg.

Perhaps a bunch of processes waiting on the access exclusive lock on
the materialized view being released?

log_lock_waits might help you if the MV takes more than a second to
refresh, otherwise, you might need to have a look at ungranted locks
in pg_locks and see if the number of locks spikes during the refresh.

--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services