Re: Database Stalls

Consider creating a pg_stat_activity history table. This would allow you to
look back at the time of incident and verify if any unusual activity was
occurring in the database. Something like:

CREATE TABLE pg_stat_activity_hist ASSELECT
now() AS sample_time,
a.*FROM
pg_stat_activity a WITH NO data;

Then with a cron job or a pg job scheduler insert the pg_stat_activity
history at some desired interval (e.g 30s, 1m or 5m):

INSERT INTO pg_stat_activity_hist
SELECT
now(),
a.*
FROM
pg_stat_activity a
WHERE
state IN ('active', 'idle in transaction’);

Then regularly purge any sample_times older than some desired interval (1
day, 1 week, 1 month).

Not a perfect solution because the problem (if a db problem) could occur
between your pg_stat_activity samples. We keep this kind of history and it
is very helpful when trying to find a post-event root cause.

Craig

On Jan 30, 2023 at 10:47:49 AM, Mok <gurmokh(at)gmail(dot)com> wrote:

> Hi,
>
> We've started to observe instances of one of our databases stalling for a
> few seconds.
>
> We see a spike in wal write locks then nothing for a few seconds. After
> which we have spike latency as processes waiting to get to the db can do
> so.
>
> There is nothing in the postgres logs that give us any clues to what could
> be happening, no locks, unusually high/long running transactions, just a
> pause and resume.
>
> Could anyone give me any advice as to what to look for when it comes to
> checking the underlying disk that the db is on?
>
> Thanks,
>
> Gurmokh
>
>
>

--
This electronic communication and the information and any files transmitted
with it, or attached to it, are confidential and are intended solely for
the use of the individual or entity to whom it is addressed and may contain
information that is confidential, legally privileged, protected by privacy
laws, or otherwise restricted from disclosure to anyone else. If you are
not the intended recipient or the person responsible for delivering the
e-mail to the intended recipient, you are hereby notified that any use,
copying, distributing, dissemination, forwarding, printing, or copying of
this e-mail is strictly prohibited. If you received this e-mail in error,
please return the e-mail to the sender, delete it from your computer, and
destroy any printed copy of it.