On Mon, Mar 27, 2017 at 1:27 PM, Haribabu Kommi <kommi.harib...@gmail.com> wrote:
> > > On Sat, Mar 25, 2017 at 6:40 AM, Fujii Masao <masao.fu...@gmail.com> > wrote: > >> On Wed, Feb 15, 2017 at 12:53 PM, Haribabu Kommi >> <kommi.harib...@gmail.com> wrote: >> > >> > >> > On Wed, Feb 8, 2017 at 9:36 PM, Amit Kapila <amit.kapil...@gmail.com> >> wrote: >> >> >> >> On Tue, Feb 7, 2017 at 11:47 AM, Haribabu Kommi >> >> <kommi.harib...@gmail.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.5090...@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
pg_stat_walwrites_view_3.patch
Description: Binary data
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
