Re: log_checkpoint's "WAL file(s) added" is misleading to the point of uselessness

On 7/24/21, 8:10 PM, "Fujii Masao" <masao(dot)fujii(at)oss(dot)nttdata(dot)com> wrote:
> On 2021/07/25 7:50, Andres Freund wrote:
>> Hi,
>>
>> I've been repeatedly confused by the the number of WAL files supposedly
>> added. Even when 100s of new WAL files are created the relevant portion
>> of log_checkpoints will only ever list zero or one added WAL file.
>>
>> The reason for that is that CheckpointStats.ckpt_segs_added is only
>> incremented in PreallocXlogFiles(). Which has the following comment:
>> * XXX this is currently extremely conservative, since it forces only one
>> * future log segment to exist, and even that only if we are 75% done with
>> * the current one. This is only appropriate for very low-WAL-volume systems.
>>
>> Whereas in real workloads WAL files are almost exclusively created via
>> XLogWrite()->XLogFileInit().
>>
>> I think we should consider just removing that field. Or, even better, show
>> something accurate instead.
>
> +1 to show something accurate instead.
>
> It's also worth showing them in monitoring stats view like pg_stat_wal?

+1. I was confused by this when working on a WAL pre-allocation
patch [0]. Perhaps it could be replaced by a new parameter and a new
field in pg_stat_wal. How about something like log_wal_init_interval,
where the value is the minimum amount of time between reporting the
number of WAL segments created since the last report?

Nathan

[0] https://www.postgresql.org/message-id/flat/20201225200953(dot)jjkrytlrzojbndh5(at)alap3(dot)anarazel(dot)de