Re: pg_stat_wal_write statistics view

On Wed, Mar 29, 2017 at 5:10 AM, Fujii Masao <masao(dot)fujii(at)gmail(dot)com> wrote:

> On Tue, Mar 28, 2017 at 1:40 PM, Haribabu Kommi
> <kommi(dot)haribabu(at)gmail(dot)com> wrote:
> >
> >
> > Added stats collection for walsender, statrup and autovacuum processes.
> > The background workers that call pgstat_report_stat() function will
> > automatically
> > included.
>
> So you added pgstat_send_walwrites() into several places. But WAL activity
> by the background workers which don't call pgstat_report_stat() is still
> not
> considered in pg_stat_walwrites view. This design looks fragile to me.
> I think that we should capture all the WAL activities (even by such
> background
> workers) in XLogWrite(), instead.
>
> For example, we can add the "shared" counters (protected by WALWriteLock)
> into XLogCtl and make XLogWrite() update those counters if the process is
> NOT walwriter. OTOH, if it's walwriter, XLogWrite() updates its local
> counters.
> Then walwriter periodically packs those counters in the message and sends
> it to the stats collector.
>

Updated patch to use shared counter instead of adding pg_stat_ calls to send
the statistics from each background process/worker.

All the processes updates the shared counters, the walwriter process fill
the
local structure with the shared counters and send it to the stats collector.

> >
> > update patch attached.
>
> Thanks for updating the patch!
>
> + <entry><structfield>writes</></entry>
> + <entry><type>bigint</type></entry>
> + <entry>
> + Number of WAL writes that are carried out by background
> processes and workers.
> + </entry>
> <snip>
> + <entry><structfield>write_blocks</></entry>
> + <entry><type>bigint</type></entry>
> + <entry>Number of WAL pages written to the disk by the
> background processes/workers</entry>
> + </row>
>
> Could you tell me why both numbers of WAL writes and pages written need to
> be reported? How can we tune the system by using those information?
>

With the column of write_blocks, it is useful to find out how many number
of blocks
that gets written from backend along with the number of writes. I feel
1. If we only get the count of number of writes and the write amount is
very less,
so it may not need any tuning.
2. If the amount of write is good even for less number of writes, then it
needs
some tuning.

Updated patch attached.

Regards,
Hari Babu
Fujitsu Australia