pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Don Seiler <don@seiler.us>
To: pgsql-admin <pgsql-admin@postgresql.org>
Subject: Re: WALWriteLocks
Date: Thu, 29 Apr 2021 08:38:31 +0200
Message-ID: <63f24de419af530402a16a97c5e5ec977f4092dc.camel@cybertec.at> (raw)
In-Reply-To: <CAHJZqBDXNEOZDYQXB+xUK2ie-Tjzmn72RHVec-NsCNyU4TeydQ@mail.gmail.com>
References: <CAHJZqBDXNEOZDYQXB+xUK2ie-Tjzmn72RHVec-NsCNyU4TeydQ@mail.gmail.com>

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






view thread (18+ messages)  latest in thread

Message-ID: <63f24de419af530402a16a97c5e5ec977f4092dc.camel@cybertec.at>
Permalink:  ../63f24de419af530402a16a97c5e5ec977f4092dc.camel@cybertec.at/
Also on:    postgresql.org/message-id/63f24de419af530402a16a97c5e5ec977f4092dc.camel@cybertec.at

 · 

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-admin@postgresql.org
  Cc: laurenz.albe@cybertec.at, don@seiler.us
  Subject: Re: WALWriteLocks
  In-Reply-To: <63f24de419af530402a16a97c5e5ec977f4092dc.camel@cybertec.at>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox