Re: Add statistics to pg_stat_wal view for wal related parameter tuning

On Tue, Oct 20, 2020 at 8:01 AM Masahiro Ikeda <ikedamsh(at)oss(dot)nttdata(dot)com> wrote:
>
> Hi,
>
> I think we need to add some statistics to pg_stat_wal view.
>
> Although there are some parameter related WAL,
> there are few statistics for tuning them.
>
> I think it's better to provide the following statistics.
> Please let me know your comments.
>
> ```
> postgres=# SELECT * from pg_stat_wal;
> -[ RECORD 1 ]-------+------------------------------
> wal_records | 2000224
> wal_fpi | 47
> wal_bytes | 248216337
> wal_buffers_full | 20954
> wal_init_file | 8
> wal_write_backend | 20960
> wal_write_walwriter | 46
> wal_write_time | 51
> wal_sync_backend | 7
> wal_sync_walwriter | 8
> wal_sync_time | 0
> stats_reset | 2020-10-20 11:04:51.307771+09
> ```
>
> 1. Basic statistics of WAL activity
>
> - wal_records: Total number of WAL records generated
> - wal_fpi: Total number of WAL full page images generated
> - wal_bytes: Total amount of WAL bytes generated
>
> To understand DB's performance, first, we will check the performance
> trends for the entire database instance.
> For example, if the number of wal_fpi becomes higher, users may tune
> "wal_compression", "checkpoint_timeout" and so on.
>
> Although users can check the above statistics via EXPLAIN, auto_explain,
> autovacuum and pg_stat_statements now,
> if users want to see the performance trends for the entire database,
> they must recalculate the statistics.
>

Here, do you mean to say 'entire cluster' instead of 'entire database'
because it seems these stats are getting collected for the entire
cluster?

> I think it is useful to add the sum of the basic statistics.
>

There is an argument that it is better to view these stats at the
statement-level so that one can know which statements are causing most
WAL and then try to rate-limit them if required in the application and
anyway they can get the aggregate of all the WAL if they want. We have
added these stats in PG-13, so do we have any evidence that the
already added stats don't provide enough information? I understand
that you are trying to display the accumulated stats here which if
required users/DBA need to compute with the currently provided stats.
OTOH, sometimes adding more ways to do some things causes difficulty
for users to understand and learn.

I see that we also need to add extra code to capture these stats (some
of which is in performance-critical path especially in
XLogInsertRecord) which again makes me a bit uncomfortable. It might
be that it is all fine as it is very important to collect these stats
at cluster-level in spite that the same information can be gathered at
statement-level to help customers but I don't see a very strong case
for that in your proposal.

--
With Regards,
Amit Kapila.