Date: Mon, 30 Jan 2023 11:51:18 -0600
From: Justin Pryzby <pryzby@telsasoft.com>
To: Mok <gurmokh@gmail.com>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: Database Stalls
Message-ID: <20230130175118.GE22427@telsasoft.com>
References: 
 <CADko==RNKpURpjfLDOKDLo5uaAh9gNC9y+nkTcrvjp_nxGf_FQ@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: 
 <CADko==RNKpURpjfLDOKDLo5uaAh9gNC9y+nkTcrvjp_nxGf_FQ@mail.gmail.com>
User-Agent: Mutt/1.9.4 (2018-02-28)
Archived-At: 
 <https://www.postgresql.org/message-id/20230130175118.GE22427%40telsasoft.com>
Precedence: bulk

On Mon, Jan 30, 2023 at 05:47:49PM +0000, Mok 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?

What version postgres?  What settings have non-default values ?                                                                                                     
What OS/version?  What environment/hardware?  VM/image/provider/...                                                                                                 

Have you enabled logging for vacuum/checkpoints/locks ?

https://wiki.postgresql.org/wiki/Slow_Query_Questions