On 2020/10/13 11:57, Masahiro Ikeda wrote:
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
+1
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.
+1
But it might be better to track the number of when new WAL file is
created whether it's zero-filled or not, if file creation and sync itself
takes time.
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.
You're thinking to count all the WAL file switch? That number is equal
to the number of WAL files generated since the last reset of pg_stat_wal?
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.
+1
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.
This should be tracked, e.g., only when track_io_timing is enabled?
Otherwise, tracking that may cause performance overhead.
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'm afraid that this counter for a backend may be a bit confusing. Because
when the counter indicates small time, we may think that walwriter almost
write WAL data and a backend doesn't take time to write WAL. But a backend
may be just waiting for walwriter to write WAL.
Regards,
--
Fujii Masao
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATION
