Re: pg_stat_statements issue with parallel maintenance (Was Re: WAL usage calculation patch)

On Sun, Mar 29, 2020 at 1:44 PM Julien Rouhaud <rjuju123(at)gmail(dot)com> wrote:
>
> On Sun, Mar 29, 2020 at 9:52 AM Masahiko Sawada
> <masahiko(dot)sawada(at)2ndquadrant(dot)com> wrote:
> >
> > I've run vacuum with/without parallel workers on the table having 5
> > indexes. The vacuum reads all blocks of table and indexes.
> >
> > * VACUUM command with no parallel workers
> > =# select total_time, shared_blks_hit, shared_blks_read,
> > shared_blks_hit + shared_blks_read as total_read_blks,
> > shared_blks_dirtied, shared_blks_written from pg_stat_statements where
> > query ~ 'vacuum';
> >
> > total_time | shared_blks_hit | shared_blks_read | total_read_blks |
> > shared_blks_dirtied | shared_blks_written
> > --------------+-----------------+------------------+-----------------+---------------------+---------------------
> > 19857.217207 | 45238 | 226944 | 272182 |
> > 225943 | 225894
> > (1 row)
> >
> > * VACUUM command with 4 parallel workers
> > =# select total_time, shared_blks_hit, shared_blks_read,
> > shared_blks_hit + shared_blks_read as total_read_blks,
> > shared_blks_dirtied, shared_blks_written from pg_stat_statements where
> > query ~ 'vacuum';
> >
> > total_time | shared_blks_hit | shared_blks_read | total_read_blks |
> > shared_blks_dirtied | shared_blks_written
> > -------------+-----------------+------------------+-----------------+---------------------+---------------------
> > 6932.117365 | 45205 | 73079 | 118284 |
> > 72403 | 72365
> > (1 row)
> >
> > The total number of blocks of table and indexes are about 182243
> > blocks. As Julien reported, obviously the total number of read blocks
> > during parallel vacuum is much less than single process vacuum's
> > result.
> >
> > Parallel create index has the same issue but it doesn't exist in
> > parallel queries for SELECTs.
> >
> > I think we need to change parallel maintenance commands so that they
> > report buffer usage like what ParallelQueryMain() does; prepare to
> > track buffer usage during query execution by
> > InstrStartParallelQuery(), and report it by InstrEndParallelQuery()
> > after parallel maintenance command. To report buffer usage of parallel
> > maintenance command correctly, I'm thinking that we can (1) change
> > parallel create index and parallel vacuum so that they prepare
> > gathering buffer usage, or (2) have a common entry point for parallel
> > maintenance commands that is responsible for gathering buffer usage
> > and calling the entry functions for individual maintenance command.
> > I'll investigate it more in depth.
>
> As I just mentioned, (2) seems like a better design as it's quite
> likely that the number of parallel-aware utilities will probably
> continue to increase. One problem also is that parallel CREATE INDEX
> has been introduced in pg11, so (2) probably won't be packpatchable
> (and (1) seems problematic too).
>

I am not sure if we can decide at this stage whether it is
back-patchable or not. Let's first see the patch and if it turns out
to be complex, then we can try to do some straight-forward fix for
back-branches. In general, I don't see why the fix here should be
complex?

--
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com