From: Fujii Masao <masao.fujii@oss.nttdata.com>
To: Masahiro Ikeda <ikedamsh@oss.nttdata.com>
To: pgsql-hackers <pgsql-hackers@postgresql.org>
Subject: Re: Add statistics to pg_stat_wal view for wal related parameter tuning
Date: Fri, 30 Oct 2020 12:00:27 +0900
Message-ID: <a7e4a0b8-7341-9393-2d24-ffd9a3b2f852@oss.nttdata.com> (raw)
In-Reply-To: <35ef960128b90bfae3b3fdf60a3a860f@oss.nttdata.com>
References: <35ef960128b90bfae3b3fdf60a3a860f@oss.nttdata.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: masao.fujii@oss.nttdata.com, ikedamsh@oss.nttdata.com
Subject: Re: Add statistics to pg_stat_wal view for wal related parameter tuning
In-Reply-To: <a7e4a0b8-7341-9393-2d24-ffd9a3b2f852@oss.nttdata.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox