Re: [issue] wal_buffers_full increases depending on the values of wal_buffers and wal-segsize

Hi,

On Mon, Apr 25, 2022 at 9:19 PM 와따가따 <lght2000(at)gmail(dot)com> wrote:
>
> If the wal segment size is larger than the wal_buffers parameter value, the value of the wal_buffers_full column of pg_stat_wal increases after database shutdown and startup.
>
> pg_controldata | grep "Bytes per WAL segment"
> Bytes per WAL segment: 1073741824
>
> wal_buffers = 16MB
>
>
> ---- DB shutdown and restart
> postgres=# select * from pg_stat_wal;
> wal_records | wal_fpi | wal_bytes | wal_buffers_full | wal_write | wal_sync | wal_write_time | wal_sync_time | stats_reset
> -------------+---------+-----------+------------------+-----------+----------+----------------+---------------+-------------------------------
> 6165 | 462 | 14071380 | 126070 | 126115 | 40 | 0 | 0 | 2022-04-25 14:28:04.746772+09
> (1 row)
>
>
> If wal_buffers size and wal_segment size are the same, wal_buffers_full column does not increase.
>
> pg_controldata | grep "Bytes per WAL segment"
> Bytes per WAL segment: 16777216
>
> wal_buffers = 16MB
>
> postgres=# select * from pg_stat_wal;
> wal_records | wal_fpi | wal_bytes | wal_buffers_full | wal_write | wal_sync | wal_write_time | wal_sync_time | stats_reset
> -------------+---------+-----------+------------------+-----------+----------+----------------+---------------+-------------------------------
> 6161 | 461 | 14062929 | 0 | 39 | 36 | 0 | 0 | 2022-04-25 12:13:35.838092+09
> (1 row)
>
>
>
> Is this a bug?
>
> The postgreSQL version currently in use is 14.2, and the database was restarted without performing DML and DDL.
>
> I wonder if it doesn't matter if the values of wal_buffers and wal-segsize are different.

I've reproduced a similar symptom by calling pg_switch_wal() on the
database cluster that is initialized with --wal-segsize=1024 (1GB):

$ pg_controldata | grep "WAL seg"
Bytes per WAL segment: 1073741824

=# select wal_buffers_full, wal_write from pg_stat_wal;
wal_buffers_full | wal_write
------------------+-----------
0 | 0
(1 row)

=# select pg_switch_wal();
pg_switch_wal
---------------
0/40699860
(1 row)

=# select wal_buffers_full, wal_write from pg_stat_wal
wal_buffers_full | wal_write
------------------+-----------
128180 | 128181
(1 row)

I think it's not a bug. When switching a WAL segment, we have to
consume all the remaining space in the WAL segment. wal_buffers_full
is incremented whenever we have to write out one (8kB) WAL block of
the (16MB) WAL buffers after using up the WAL buffers. Therefore,
During filling up the remaining 1GB WAL segment, wal_buffers_full is
incremented about 130,000 times (=1GB / 8kB), which seems to be a
similar value you got. On the other hand, with the 16MB WAL segment,
the WAL buffers were not used up, wal_buffers_full was not
incremented. I guess something like this happened on your database.

Regards,

--
Masahiko Sawada
EDB: https://www.enterprisedb.com/