Thread

1. [issue] wal_buffers_full increases depending on the values of wal_buffers and wal-segsize

   와따가따 <lght2000@gmail.com> — 2022-04-25T07:51:43Z

   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.
   

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

   Masahiko Sawada <sawada.mshk@gmail.com> — 2022-04-28T03:40:08Z

   Hi,
   
   On Mon, Apr 25, 2022 at 9:19 PM 와따가따 <lght2000@gmail.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/