Re: stale WAL files? - Mailing list pgsql-general

From Rob Sargent
Subject Re: stale WAL files?
Date
Msg-id c4cd4459-053e-d391-0d9d-1d7b5af0da9c@gmail.com
Whole thread Raw
In response to Re: stale WAL files?  (Rene Romero Benavides <rene.romero.b@gmail.com>)
List pgsql-general


On 4/10/19 6:03 PM, Rene Romero Benavides wrote:
What's your current max_wal_size parameter?
SHOW max_wal_size; 
If it's 8GB as your configuration's previous value, you would get a constant share of 512 WAL files. If it's a development environment set it to the desired size, the smaller the value, the more frequent the checkpoints, but your checkpoint_timeout value is 300 (5 minutes) which is likely to be happening first, and thus being the one triggering checkpoints that often.   

On Wed, Apr 10, 2019 at 1:12 PM Rob Sargent <robjsargent@gmail.com> wrote:



As per your configuration :
max_wal_size = 50GB
this seems to be the cause for the WAL files piling up.

this has been declared twice, the last one is taking effect.
--
El genio es 1% inspiración y 99% transpiración.
Thomas Alva Edison
http://pglearn.blogspot.mx/

I've manage to generate another 359 WAL files in a 10 minute span yesterday (now only 357 remain and I suspect they will wither away as before).  Are these being held simply because of the high max_wal_size value?

This is a development environment, wherein I'm loading 4M+ records, first into 41 staging tables 100K rows per.  In a loop over each staging table, the data is then placed into application tables via selects. First select * into "matching table" then select id into intersection record (id, fixed groupId).  Each such iteration is in it's own transaction.  I have dropped and recreate this same database numerous times working my way up from 100K to 4M records, dialing in application parameters according to number of primary records.  I have not, however, dropped the last incarnation.


We have not yet reconfigured the max_wal_size parameter, it is still 50GB

postgres-# show max_wal_size
postgres-# ;
 max_wal_size
--------------
 50GB
(1 row)

I'm sorry, I  don't follow your thesis that this setting would lead to "a constant" 512 WAL files.  There was a distinct burst of WALs in a 10 minute period yesterday, and no accumulation since then (though a manual checkpoint does generate another WAL)



pgsql-general by date:

Previous
From: Rene Romero Benavides
Date:
Subject: Re: stale WAL files?
Next
From: Prakash Ramakrishnan
Date:
Subject: os upgrade 7.3 to 7.5 (postgres version 10.5)