Обсуждение: explosive WAL growth

Something is causing our WAL to grow 160GB/hour *faster* than archiving. (Archiving appears to be working normally.)
Thisstarted in the past couple of days. 

I am having some trouble finding the cause of this. I am looking at pg_stat_statements, # calls, time, shared blocks
written.I am also looking at recent client app deployments. 

My next step might be to use something like pg_waldump to see what's in this WAL.

Any suggestions?

--
Scott Ribe
scott_ribe@elevated-dev.com
https://www.linkedin.com/in/scottribe/

On 2024-Feb-27, Scott Ribe wrote:

> Something is causing our WAL to grow 160GB/hour *faster* than archiving. (Archiving appears to be working normally.)
Thisstarted in the past couple of days.
 
> 
> I am having some trouble finding the cause of this. I am looking at pg_stat_statements, # calls, time, shared blocks
written.I am also looking at recent client app deployments.
 

It could be autovacuum processing large tables that haven't been
processed in a long time, in emergency mode.  This happens if you turn
it off and do nothing to substitute it.

Some backup tools implement parallel archiving of WAL files, which helps
you deal with massive accumulation of them.

-- 
Álvaro Herrera               48°01'N 7°57'E  —  https://www.EnterpriseDB.com/

Found it. Kubernetes cronjob task.

> On Feb 27, 2024, at 11:22 AM, Alvaro Herrera <alvherre@alvh.no-ip.org> wrote:
>
> It could be autovacuum processing large tables that haven't been
> processed in a long time, in emergency mode.  This happens if you turn
> it off and do nothing to substitute it.

We have autovacuum on, and pretty well tuned.

> Some backup tools implement parallel archiving of WAL files, which helps
> you deal with massive accumulation of them.

We'll get that in an upgrade we're getting ready to deploy. Ironically, it might have just hidden this problem ;-)

> On Feb 27, 2024, at 11:58 AM, Karthik Yellapragada <karthik.yellapragada@gmail.com> wrote:
>
> Just out of curiosity, what was the job doing?

Large batch upsert. But something is wrong for it to be generating WAL that quickly. Having identified it, now we can
drilldown to see what's wrong with it.