Re: pg_stat_wal_write statistics view

On Mon, Mar 27, 2017 at 1:27 PM, Haribabu Kommi <kommi(dot)haribabu(at)gmail(dot)com>
wrote:

>
>
> On Sat, Mar 25, 2017 at 6:40 AM, Fujii Masao <masao(dot)fujii(at)gmail(dot)com>
> wrote:
>
>> On Wed, Feb 15, 2017 at 12:53 PM, Haribabu Kommi
>> <kommi(dot)haribabu(at)gmail(dot)com> wrote:
>> >
>> >
>> > On Wed, Feb 8, 2017 at 9:36 PM, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com>
>> wrote:
>> >>
>> >> On Tue, Feb 7, 2017 at 11:47 AM, Haribabu Kommi
>> >> <kommi(dot)haribabu(at)gmail(dot)com> wrote:
>> >> > Hi Hackers,
>> >> >
>> >> > I just want to discuss adding of a new statistics view that provides
>> >> > the information of wal writing details as follows
>> >> >
>> >>
>> >> +1. I think it will be useful to observe WAL activity.
>> >
>> >
>> > Thanks for your opinion.
>> >
>> >> > postgres=# \d pg_stat_wal_writer
>> >> > View "pg_catalog.pg_stat_wal_writer"
>> >> > Column | Type | Collation |
>> Nullable
>> >> > |
>> >> > Default
>> >> >
>> >> > -----------------------+--------------------------+---------
>> --+----------+---------
>> >> > num_backend_writes | bigint | |
>> >> > |
>> >> > num_total_writes | bigint | |
>> |
>> >> > num_blocks | bigint | | |
>> >> > total_write_time | bigint| | |
>> >> > stats_reset | timestamp with time zone | |
>> >> > |
>> >> >
>> >> > The columns of the view are
>> >> > 1. Total number of xlog writes that are called from the backend.
>> >> > 2. Total number of xlog writes that are called from both backend
>> >> > and background workers. (This column can be changed to just
>> >> > display on the background writes).
>> >> > 3. The number of the blocks that are written.
>> >> > 4. Total write_time of the IO operation it took, this variable data
>> is
>> >> > filled only when the track_io_timing GUC is enabled.
>> >>
>> >> So, here is *write_time* the total time system has spent in WAL
>> >> writing before the last reset?
>> >
>> >
>> > total write_time spent in WAL writing "after" the last reset in
>> > milliseconds.
>> >
>> >> I think there should be a separate column for write and sync time.
>> >>
>> >
>> > Added.
>> >
>> >>
>> >> > Or it is possible to integrate the new columns into the existing
>> >> > pg_stat_bgwriter view also.
>> >> >
>> >>
>> >> I feel separate view is better.
>> >
>> >
>> > Ok.
>> >
>> > Following the sample out of the view after regress run.
>> >
>> > postgres=# select * from pg_stat_walwrites;
>> > -[ RECORD 1 ]--+------------------------------
>> > backend_writes | 19092
>> > writes | 663
>> > write_blocks | 56116
>> > write_time | 0
>> > sync_time | 3064
>> > stats_reset | 2017-02-15 13:37:09.454314+11
>> >
>> > Currently, writer, walwriter and checkpointer processes
>> > are considered as background processes that can do
>> > the wal write mainly.
>>
>
> Thanks for the review.
>
> I'm not sure if this categorization is good. You told that this view is
>> useful
>> to tune walwriter parameters at the top of this thread. If so, ISTM that
>> the information about walwriter's activity should be separated from
>> others.
>>
>
> Yes, that's correct. First I thought of providing the statistics of
> walwriter, but
> later in development, it turned into showing statistics of all wal write
> activity
> of background processes also to differentiate the actual write by the
> backends.
>
>
>> What about other processes which *can* write WAL, for example walsender
>> (e.g., BASE_BACKUP can cause WAL record), startup process (e.g., end-of-
>> recovery checkpoint) and logical replication worker (Not sure if it always
>> works with synchronous_commit=off, though). There might be other processes
>> which can write WAL
>
>
> It is possible to add the walsender, stratup and other processes easily,
> but not
> background workers that does some wal write operations until unless they
> report the stats with pgstat_report_stat(). Is it fine to ignore the
> workers that
> does not report the stats?
>

Added stats collection for walsender, statrup and autovacuum processes.
The background workers that call pgstat_report_stat() function will
automatically
included.

>
>
>> Why didn't you separate "write_blocks", "write_time" and "sync_time" per
>> the process category, like "backend_writes" and "writes"?
>>
>
> Ok. I will add those columns.
>

Added the new columns to differentiate backend and background processes.

This view doesn't seem to take into consideration the WAL writing and
>> flushing
>> during creating new WAL segment file.
>>
>> I think that it's better to make this view report also the number of WAL
>> pages
>> which are written when wal_buffer is full. This information is useful to
>> tune the size of wal_buffers. This was proposed by Nagayasu before.
>> https://www.postgresql.org/message-id/4FF824F3.5090407@uptime.jp
>
>
> Ok. But this new column just shows how many times the WAL buffers are
> flushed
> because of wal buffers are full. Not the WAL pages that are actually
> flushed because
> of wal buffers full as a separate column.
>

Added a new column to display the dirty writes from both backend and
background processes.

update patch attached.

Regards,
Hari Babu
Fujitsu Australia