pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Les <nagylzs@gmail.com>
Cc: pgsql-general@lists.postgresql.org
Subject: Re: replication primary writting infinite number of WAL files
Date: Fri, 24 Nov 2023 18:31:29 +0100
Message-ID: <6097c54582a91b40eaab2d3d24152d98fcaf998f.camel@cybertec.at> (raw)
In-Reply-To: <CAKXe9UDj27Z=YiNMU8DwtzVARy_D-umarOBY2OEfKYFwzbjJwg@mail.gmail.com>
References: <CAKXe9UDZKGevV6t+Y0adUgy2-k-1gMdRUi4Baz=4BMKX1Oig_A@mail.gmail.com>
	<d0e8eda7fbfd52ee70c783bffe6fdabfda885e35.camel@cybertec.at>
	<CAKXe9UDj27Z=YiNMU8DwtzVARy_D-umarOBY2OEfKYFwzbjJwg@mail.gmail.com>

On Fri, 2023-11-24 at 16:59 +0100, Les wrote:
> 
> 
> Laurenz Albe <laurenz.albe@cybertec.at>  (2023. nov. 24., P, 16:00):
> > On Fri, 2023-11-24 at 12:39 +0100, Les wrote:
> > > Under normal circumstances, the number of write operations is relatively low, with an
> > > average of 4-5 MB/sec total write speed on the disk associated with the data directory.
> > > Yesterday, the primary server suddenly started writing to the pg_wal directory at a
> > > crazy pace, 1.5GB/sec, but sometimes it went up to over 3GB/sec.
> > > [...]
> > > Upon further analysis of the database, we found that we did not see any mass data
> > > changes in any of the tables. The only exception is a sequence value that was moved
> > > millions of steps within a single minute.
> > 
> > That looks like some application went crazy and inserted millions of rows, but the
> > inserts were rolled back.  But it is hard to be certain with the clues given.
> 
> Writing of WAL files continued after we shut down all clients, and restarted the primary PostgreSQL server.
> 
> How can the primary server generate more and more WAL files (writes) after all clients have
> been shut down and the server was restarted? My only bet was the autovacuum. But I ruled
> that out, because removing a replication slot has no effect on the autovacuum (am I wrong?).

It must have been autovacuum.  Removing a replication slot has an influence, since then
autovacuum can do more work.  If the problem stopped when you dropped the replication slot,
it could be a coincidence.

> Now you are saying that this looks like a huge rollback.

It could have been many small rollbacks.

> Does rolling back changes require even more data to be written to the WAL after server
> restart?

No.  My assumption would be that something generated lots of INSERTs that were all
rolled back.  That creates WAL, even though you see no change in the table data.


> Does removing a replication slot lessen the amount of data needed to be written for
> a rollback (or for anything else)?

No: the WAL is generated by whatever precedes the ROLLBACK, and the ROLLBACK does
not create a lot of WAL.

> It is a fact that the primary stopped writing at 1.5GB/sec the moment we removed the slot.

I have no explanation for that, except a coincidence.
Replication slots don't generate WAL.

Yours,
Laurenz Albe





view thread (20+ messages)  latest in thread

Message-ID: <6097c54582a91b40eaab2d3d24152d98fcaf998f.camel@cybertec.at>
Permalink:  ../6097c54582a91b40eaab2d3d24152d98fcaf998f.camel@cybertec.at/
Also on:    postgresql.org/message-id/6097c54582a91b40eaab2d3d24152d98fcaf998f.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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: laurenz.albe@cybertec.at, nagylzs@gmail.com
  Subject: Re: replication primary writting infinite number of WAL files
  In-Reply-To: <6097c54582a91b40eaab2d3d24152d98fcaf998f.camel@cybertec.at>
  References: <CAKXe9UDZKGevV6t+Y0adUgy2-k-1gMdRUi4Baz=4BMKX1Oig_A@mail.gmail.com> <d0e8eda7fbfd52ee70c783bffe6fdabfda885e35.camel@cybertec.at> <CAKXe9UDj27Z=YiNMU8DwtzVARy_D-umarOBY2OEfKYFwzbjJwg@mail.gmail.com> <6097c54582a91b40eaab2d3d24152d98fcaf998f.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