From: | Rob Sargent <robjsargent(at)gmail(dot)com> |
---|---|
To: | Rene Romero Benavides <rene(dot)romero(dot)b(at)gmail(dot)com> |
Cc: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: stale WAL files? |
Date: | 2019-04-11 00:38:53 |
Message-ID: | c4cd4459-053e-d391-0d9d-1d7b5af0da9c@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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(at)gmail(dot)com
> <mailto:robjsargent(at)gmail(dot)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)
From | Date | Subject | |
---|---|---|---|
Next Message | Prakash Ramakrishnan | 2019-04-11 02:46:43 | os upgrade 7.3 to 7.5 (postgres version 10.5) |
Previous Message | Rene Romero Benavides | 2019-04-11 00:03:11 | Re: stale WAL files? |