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

On 2020/10/20 11:31, Masahiro Ikeda 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.
> 
> I think it is useful to add the sum of the basic statistics.
> 
> 
> 2.  WAL segment file creation
> 
> - wal_init_file: Total number of WAL segment files created.
> 
> To create a new WAL file may have an impact on the performance of
> a write-heavy workload generating lots of WAL. If this number is reported high,
> to reduce the number of this initialization, we can tune WAL-related parameters
> so that more "recycled" WAL files can be held.
> 
> 
> 
> 3. Number of when WAL is flushed
> 
> - wal_write_backend : Total number of WAL data written to the disk by backends
> - wal_write_walwriter : Total number of WAL data written to the disk by walwriter
> - wal_sync_backend : Total number of WAL data synced to the disk by backends
> - wal_sync_walwriter : Total number of WAL data synced to the disk by walwrite
> 
> I think it's useful for tuning "synchronous_commit" and "commit_delay" for query executions.
> If the number of WAL is flushed is high, users can know "synchronous_commit" is useful for the workload.

I just wonder how useful these counters are. Even without these counters,
we already know synchronous_commit=off is likely to cause the better
performance (but has the risk of data loss). So ISTM that these counters are
not so useful when tuning synchronous_commit.

Regards,

-- 
Fujii Masao
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATION