Re: New statistics for tuning WAL buffer size

On 2020-10-06 15:57, Masahiro Ikeda wrote:
> Hi,
> 
> I think it's better to add other WAL statistics to the pg_stat_wal 
> view.
> I'm thinking to add the following statistics. Please let me know your 
> thoughts.
> 
> 1.  Basic wal statistics
> 
> * 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 preprocess the statistics.
> 
> Is it useful to add the sum of the above statistics to the pg_stat_wal 
> view?
> 
> 
> 2.  Number of when new WAL file is created and zero-filled.
> 
> As Fujii-san already commented, I think it's good for tuning.
> 
>> Just idea; it may be worth exposing the number of when new WAL file is 
>> created and zero-filled. This initialization may have impact on the 
>> performance of 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 hold.
> 
> 
> 3.  Number of when to switch the WAL logfile segment.
> 
> This is similar to 2, but this counts the number of when WAL file is
> recylcled too.
> I think it's useful for tuning "wal_segment_size"
> if the number is high　relative to the startup time, "wal_segment_size"
> must be bigger.
> 
> 
> 4. Number of when WAL is flushed
> 
> 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.
> 
> Also, it's useful for tuning "wal_writer_delay" and
> "wal_writer_flush_after" for wal writer.
> If the number is high, users can change the parameter for performance.
> 
> I think it's better to separate this for backends and wal writer.
> 
> 
> 5.  Wait time when WAL is flushed.
> 
> This is the accumulated time when wal is flushed.
> If the time becomes much higher,　users can detect the possibility of
> disk failure.
> 
> Since users can see how much flash time occupies of the query execution 
> time,
> it may lead to query tuning and so on.
> 
> Since there is the above reason, I think it's better to separate this
> for backends and wal writer.

I made a patch for collecting the above statistics.
If you have any comments, please let me know.

I think it's better to separate some statistics for backend and 
backgrounds because
tuning target parameters like "synchronous_commit", "wal_writer_delay" 
and so on are different.
But first, I want to get a consensus to collect them.

Best regards,
-- 
Masahiro Ikeda
NTT DATA CORPORATION