Move wal_buffers_full to WalUsage (and report it in pgss/explain)

Hi,

On Thu, Feb 06, 2025 at 10:27:17AM +0000, Bertrand Drouvot wrote:

=== Patch's structure

The patch series is made of 3 "small" sub-patches:

0001: to move wal_buffers_full to WalUsage
0002: to report wal_buffers_full in pg_stat_statements
0003: to report wal_buffers_full in explain/auto_explain

While at it, adding 0004 to report wal_buffers_full in VACUUM/ANALYZE (VERBOSE).

Regards,

--
Bertrand Drouvot
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com

Hi,

Thank you for your work!

1. Perhaps In EXPLAIN you forget to check that usage->wal_buffers_full > 0:

if ((usage->wal_records > 0) || (usage->wal_fpi > 0) ||
(usage->wal_bytes > 0))

2. I have a small suggestion for pg_stat_statements: would it make sense
to move wal_buffers_full next to wal_records, wal_fpi and wal_bytes?
This way, all WAL-related information would be grouped together.

--
Best regards,
Ilia Evdokimov,
Tantor Labs LLC.

Hi,

On Tue, Feb 11, 2025 at 03:30:09PM +0300, Ilia Evdokimov wrote:

Hi,

Thank you for your work!

Thanks for the review!

1. Perhaps In EXPLAIN you forget to check that usage->wal_buffers_full > 0:

if ((usage->wal_records > 0) || (usage->wal_fpi > 0) || (usage->wal_bytes >
0))

I don't think that's possible to have wal_buffers_full > 0 if the above returns
false. A check is done at appendStringInfo() time so I think that's ok as it is.

2. I have a small suggestion for pg_stat_statements:�would it make sense to
move wal_buffers_full next to wal_records, wal_fpi and wal_bytes? This way,
all WAL-related information would be grouped together.

I think I prefer to add it in "append" order. That way, that does not break
queries that rely on ordinal numbers.

Regards,

--
Bertrand Drouvot
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com

On Tue, Feb 11, 2025 at 02:05:18PM +0000, Bertrand Drouvot wrote:

2. I have a small suggestion for pg_stat_statements: would it make sense to
move wal_buffers_full next to wal_records, wal_fpi and wal_bytes? This way,
all WAL-related information would be grouped together.

I think I prefer to add it in "append" order. That way, that does not break
queries that rely on ordinal numbers.

Not sure. FWIW, it makes sense to me to group them by "family" in
this case, as they would belong to the same instrument structure.
--
Michael

On Tue, Feb 11, 2025 at 09:37:37AM +0000, Bertrand Drouvot wrote:

While at it, adding 0004 to report wal_buffers_full in VACUUM/ANALYZE (VERBOSE).

Thanks for summarizing the history behind WalUsage and
wal_buffers_full. FWIW, 0001 that moves wal_buffers_full from
PgStat_PendingWalStats to WalUsage is going to make our lives much
easier for your pending patch to adds backend statistics for WAL. WAL
write/sync numbers/times will be covered in the backend stats by
pg_stat_io, allowing us to remove entirely the dependency to
PgStat_PendingWalStats.

I have been wondering for a bit if the comment at the top of WalUsage
should be updated, but the current description fits as well with the
follow-up patch series.

Anyway, if there are no objections, I am planning to apply this one to
lift this barrier and help with the follow-up work for the backend
stats. That's the mandatory piece to makes the backend WAL stats
integration easier.

0002, 0003 and 0004 are straight-forward follow-ups. It's IMO one of
these things where extra data is cheap to have access to, and can be
useful to be aware when distributed across multiple contexts like
queries, plans or even EXPLAIN. So no real objections here. If other
have comments, feel free.

show_wal_usage() should have its check on (usage->wal_buffers_full) in
explain.c, as Ilia has mentioned. It's true that you would not get a
(wal_buffers_full > 0) if at least the condition on wal_bytes is not
satisfied, but the addition makes sense on consistency grounds, at
least.

Agreed about the attribute ordering in pgss with everything associated
to WalUsage grouped together, btw.
--
Michael

Hi,

On Fri, Feb 14, 2025 at 03:24:22PM +0900, Michael Paquier wrote:

On Tue, Feb 11, 2025 at 09:37:37AM +0000, Bertrand Drouvot wrote:

While at it, adding 0004 to report wal_buffers_full in VACUUM/ANALYZE (VERBOSE).

Thanks for summarizing the history behind WalUsage and
wal_buffers_full.

Thanks for looking at it.

FWIW, 0001 that moves wal_buffers_full from
PgStat_PendingWalStats to WalUsage is going to make our lives much
easier for your pending patch to adds backend statistics for WAL. WAL
write/sync numbers/times will be covered in the backend stats by
pg_stat_io, allowing us to remove entirely the dependency to
PgStat_PendingWalStats.

Yup.

I have been wondering for a bit if the comment at the top of WalUsage
should be updated, but the current description fits as well with the
follow-up patch series.

Agree that the comment is "still" fine with the extra struct member.

0002, 0003 and 0004 are straight-forward follow-ups. It's IMO one of
these things where extra data is cheap to have access to, and can be
useful to be aware when distributed across multiple contexts like
queries, plans or even EXPLAIN. So no real objections here.

Yeah and that makes the comment at the top of WalUsage accurate ;-)

"
and is displayed by EXPLAIN command, pg_stat_statements extension, etc
"

show_wal_usage() should have its check on (usage->wal_buffers_full) in
explain.c, as Ilia has mentioned. It's true that you would not get a
(wal_buffers_full > 0) if at least the condition on wal_bytes is not
satisfied, but the addition makes sense on consistency grounds, at
least.

I'm thinking the opposite and think that the current check could be simplfied to
"usage->wal_bytes > 0" only. I don't think that wal_records and wal_fpi and
wal_buffers_full can be = 0 if wal_bytes > 0. I don't think that's worth a dedicated
thread and could be done in passing instead of adding the check on wal_buffers_full.

Done that way in the attached (added a comment in the commit message though).

Agreed about the attribute ordering in pgss with everything associated
to WalUsage grouped together, btw.

Ok, you have the majority then and looking at the ordering of the "api_version >="
checks in pg_stat_statements_internal() it looks like it's not the first time
we'd break queries relying on ordinal numbers. Done that way in the attached.
Note that the attached also changes the ordering in the Counters struct (to be
consistent with what 5147ab1dd34a did for example).

Regards,

--
Bertrand Drouvot
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com

On Fri, Feb 14, 2025 at 03:02:51PM +0900, Michael Paquier wrote:

Not sure. FWIW, it makes sense to me to group them by "family" in
this case, as they would belong to the same instrument structure.

Looked at the full set again, and in the PGSS patch I have finished by
grouping all the WAL fields together, like we do for the rest. In
EXPLAIN, we have other fields that use spaces rather than underscores,
see "actual time" for example, so I've done the same for buffers full.

Nothing much more to say, so I've applied the set. Now let's see what
happens for the WAL stats patch at backend level.
--
Michael