Message-ID: <63f24de419af530402a16a97c5e5ec977f4092dc.camel@cybertec.at>
Subject: Re: WALWriteLocks
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Don Seiler <don@seiler.us>, pgsql-admin <pgsql-admin@postgresql.org>
Date: Thu, 29 Apr 2021 08:38:31 +0200
In-Reply-To: 
 <CAHJZqBDXNEOZDYQXB+xUK2ie-Tjzmn72RHVec-NsCNyU4TeydQ@mail.gmail.com>
References: 
	<CAHJZqBDXNEOZDYQXB+xUK2ie-Tjzmn72RHVec-NsCNyU4TeydQ@mail.gmail.com>
Content-Type: text/plain; charset="UTF-8"
User-Agent: Evolution 3.36.5 (3.36.5-2.fc32) 
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/63f24de419af530402a16a97c5e5ec977f4092dc.camel%40cybertec.at>
Precedence: bulk

On Wed, 2021-04-28 at 13:53 -0500, Don Seiler wrote:
> We're running a few PostgreSQL 12 instances in VMs in the MS Azure cloud. We have one
>  instance in particular that will seemingly lock up for a few seconds (and on rare
>  occasions a few minutes). When looking at the metrics, it looks like most of the
>  active sessions all begin waiting on WALWriteLocks. Shortly after we'll see a flurry
>  of COMMITs in the PG log. For example, we would observe 75-90 sessions all waiting
>  on WALWriteLock for 30 seconds and then suddenly they would all write the COMMIT
>  action to the PG log (showing the ~30 second duration). When this wait goes on for
>  minutes, then it is definitely noticed by the app and users.
> 
> We have moved the WAL files to separate Azure UltraSSD storage and are working with
>  Azure support to try to identify the bottleneck here.
> 
> My understanding is that the WALWriteLock is solely due to the session's commit
>  waiting to write to the WAL file, so our focus has been on the storage under the
>  WAL files. I'm curious if there are any other events inside PostgreSQL that would
>  be behind the WALWriteLock, to make sure we aren't chasing a red herring.

My gues is that you have too many active client connections, and you are suffering
from contention between the many backends that all want to write WAL.

In that case, use a connection pool to limit the number of active connections.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com