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
