Re: pg_stat_wal_write statistics view

From: Haribabu Kommi <kommi(dot)haribabu(at)gmail(dot)com>
To: Kuntal Ghosh <kuntalghosh(dot)2007(at)gmail(dot)com>
Cc: Peter Eisentraut <peter(dot)eisentraut(at)2ndquadrant(dot)com>, Fujii Masao <masao(dot)fujii(at)gmail(dot)com>, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com>, "pgsql-hackers(at)postgresql(dot)org" <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: pg_stat_wal_write statistics view
Date: 2017-09-13 01:01:44
Message-ID: CAJrrPGe7tzoQ-6idmYVYibWeHY7wDK0M_SjP_wkN8_wy8tO-nQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox
Thread:
Lists: pgsql-hackers

On Tue, Sep 12, 2017 at 3:14 PM, Kuntal Ghosh <kuntalghosh(dot)2007(at)gmail(dot)com>
wrote:

> On Tue, Sep 12, 2017 at 9:06 AM, Haribabu Kommi
> <kommi(dot)haribabu(at)gmail(dot)com> wrote:
> >
> >
> > On Tue, Sep 12, 2017 at 2:04 AM, Kuntal Ghosh <
> kuntalghosh(dot)2007(at)gmail(dot)com>
> > wrote:
> >
> Thanks for the patch.
> + * Check whether the current process is a normal backend or not.
> + * This function checks for the background processes that does
> + * some WAL write activity only and other background processes
> + * are not considered. It considers all the background workers
> + * as WAL write activity workers.
> + *
> + * Returns false - when the current process is a normal backend
> + * true - when the current process a background process/worker
> + */
> +static bool
> +am_background_process()
> +{
> + /* check whether current process is a background process/worker? */
> + if (!AmBackgroundWriterProcess() ||
> + !AmCheckpointerProcess() ||
> + !AmStartupProcess() ||
> + !IsBackgroundWorker ||
> + !am_walsender ||
> + !am_autovacuum_worker)
> + {
> + return false;
> + }
> +
> + return true;
> +}
> I think you've to do AND operation here instead of OR. Isn't it?
> Another point is that, the function description could start with
> 'Check whether the current process is a background process/worker.'
>

Yes, it should be AND, while correcting a review comment, I messed
up that function.

> There is an overhead with IO time calculation. Is the view is good
> > enough without IO columns?
> I'm not sure how IO columns are useful for tuning the WAL write/fsync
> performance from an user's perspective. But, it's definitely useful
> for developing/improving stuffs in XLogWrite.
>

I ran the latest performance tests with and without IO times, there is an
overhead involved with IO time calculation and didn't observe any
performance
overhead with normal stats. May be we can enable the IO stats only in the
development environment to find out the IO stats?

> >
> > And also during my tests, I didn't observe any other background
> > processes performing the xlogwrite operation, the values are always
> > zero. Is it fine to merge them with backend columns?
> >
> Apart from wal writer process, I don't see any reason why you should
> track other background processes separately from normal backends.
> However, I may be missing some important point.

I added the other background stats to find out how much WAL write is
carried out by the other background processes. Now I am able to collect
the stats for the other background processes also after the pgbench test.
So I feel now the separate background stats may be useful.

Attached latest patch, performance test results and stats details with
separate background stats and also combine them with backend including
the IO stats also.

Regards,
Hari Babu
Fujitsu Australia

Attachment Content-Type Size
pg_stat_walwrites_stats.txt text/plain 3.5 KB
0001-pg_stat_walwrites-statistics-view_v8.patch application/octet-stream 35.2 KB
pg_stat_walwrites_perf.xlsx application/vnd.openxmlformats-officedocument.spreadsheetml.sheet 11.5 KB

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Kyotaro HORIGUCHI 2017-09-13 01:04:21 Re: WAL logging problem in 9.4.3?
Previous Message Amit Langote 2017-09-13 01:01:40 Re: dropping partitioned tables without CASCADE