On Tue, Sep 12, 2017 at 3:14 PM, Kuntal Ghosh <kuntalghosh.2...@gmail.com> wrote:
> On Tue, Sep 12, 2017 at 9:06 AM, Haribabu Kommi
> <kommi.harib...@gmail.com> wrote:
> >
> >
> > On Tue, Sep 12, 2017 at 2:04 AM, Kuntal Ghosh <
> kuntalghosh.2...@gmail.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
stats with seperate background process stats info:
writes | walwriter_writes | backend_writes | dirty_writes |
walwriter_dirty_writes | backend_dirty_writes | write_blocks |
walwriter_write_blocks | backend_write_blocks | write_time |
walwriter_write_time | backend_write_time | sync_time | walwriter_sync_time |
backend_sync_time | stats_reset
--------+------------------+----------------+--------------+------------------------+----------------------+--------------+------------------------+----------------------+------------+----------------------+--------------------+-----------+---------------------+-------------------+-------------------------------
256004 | 14223300 | 439408129 | 0 |
0 | 65933 | 3018749 | 287733552 |
1756612506 | 0 | 0 | 0 | 0
| 0 | 0 | 2017-09-12 19:21:03.103784+10
(1 row)
stats with background info with IO time:
writes | walwriter_writes | backend_writes | dirty_writes |
walwriter_dirty_writes | backend_dirty_writes | write_blocks |
walwriter_write_blocks | backend_write_blocks | write_time |
walwriter_write_time | backend_write_time | sync_time | walwriter_sync_time |
backend_sync_time | stats_reset
--------+------------------+----------------+--------------+------------------------+----------------------+--------------+------------------------+----------------------+------------+----------------------+--------------------+-----------+---------------------+-------------------+-------------------------------
458362 | 27245324 | 881576768 | 0 |
0 | 65933 | 3551641 | 304509489 |
2767649450 | 0 | 0 | 0 | 3366091
| 173043798 | 5855747060 | 2017-09-12 19:21:03.103784+10
(1 row)
stats info with combined background process info:
walwriter_writes | backend_writes | walwriter_dirty_writes |
backend_dirty_writes | walwriter_write_blocks | backend_write_blocks |
walwriter_write_time | backend_write_time | walwriter_sync_time |
backend_sync_time | stats_reset
------------------+----------------+------------------------+----------------------+------------------------+----------------------+----------------------+--------------------+---------------------+-------------------+-------------------------------
11954325 | 405757691 | 0 |
68887 | 296636494 | 1623566649 | 0 |
0 | 0 | 0 | 2017-09-12
20:25:02.045657+10
(1 row)
stats info with combined background process info and IO time:
walwriter_writes | backend_writes | walwriter_dirty_writes |
backend_dirty_writes | walwriter_write_blocks | backend_write_blocks |
walwriter_write_time | backend_write_time | walwriter_sync_time |
backend_sync_time | stats_reset
------------------+----------------+------------------------+----------------------+------------------------+----------------------+----------------------+--------------------+---------------------+-------------------+-------------------------------
25846285 | 844341893 | 0 |
68887 | 314435932 | 2626574325 | 0 |
0 | 190245460 | 5804902755 | 2017-09-12
20:25:02.045657+10
(1 row)
0001-pg_stat_walwrites-statistics-view_v8.patch
Description: Binary data
pg_stat_walwrites_perf.xlsx
Description: MS-Excel 2007 spreadsheet
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
