Re: WAL usage calculation patch - Mailing list pgsql-hackers

On Thu, Apr 02, 2020 at 02:32:07PM +0530, Amit Kapila wrote:
> On Thu, Apr 2, 2020 at 2:00 PM Julien Rouhaud <rjuju123@gmail.com> wrote:
> >
> > On Thu, Apr 02, 2020 at 11:07:29AM +0530, Amit Kapila wrote:
> > > On Wed, Apr 1, 2020 at 8:00 PM Julien Rouhaud <rjuju123@gmail.com> wrote:
> > > >
> > > > On Wed, Apr 01, 2020 at 04:29:16PM +0530, Amit Kapila wrote:
> > > > > 3. Doing some testing with and without parallelism to ensure WAL usage
> > > > > data is correct would be great and if possible, share the results?
> > > >
> > > >
> > > > I just saw that Dilip did some testing, but just in case here is some
> > > > additional one
> > > >
> > > > - vacuum, after a truncate, loading 1M row and a "UPDATE t1 SET id = id"
> > > >
> > > > =# select query, calls, wal_bytes, wal_records, wal_num_fpw from pg_stat_statements where query ilike
'%vacuum%';
> > > >          query          | calls | wal_bytes | wal_records | wal_num_fpw
> > > > ------------------------+-------+-----------+-------------+-------------
> > > >  vacuum (parallel 3) t1 |     1 |  20098962 |       34104 |           2
> > > >  vacuum (parallel 0) t1 |     1 |  20098962 |       34104 |           2
> > > > (2 rows)
> > > >
> > > > - create index, overload t1's parallel_workers, using the 1M line just
> > > >   vacuumed:
> > > >
> > > > =# alter table t1 set (parallel_workers = 2);
> > > > ALTER TABLE
> > > >
> > > > =# create index t1_parallel_2 on t1(id);
> > > > CREATE INDEX
> > > >
> > > > =# alter table t1 set (parallel_workers = 0);
> > > > ALTER TABLE
> > > >
> > > > =# create index t1_parallel_0 on t1(id);
> > > > CREATE INDEX
> > > >
> > > > =# select query, calls, wal_bytes, wal_records, wal_num_fpw from pg_stat_statements where query ilike '%create
index%';
> > > >                 query                 | calls | wal_bytes | wal_records | wal_num_fpw
> > > > --------------------------------------+-------+-----------+-------------+-------------
> > > >  create index t1_parallel_0 on t1(id) |     1 |  20355540 |        2762 |        2745
> > > >  create index t1_parallel_2 on t1(id) |     1 |  20406811 |        2762 |        2758
> > > > (2 rows)
> > > >
> > > > It all looks good to me.
> > > >
> > >
> > > Here the wal_num_fpw and wal_bytes are different between parallel and
> > > non-parallel versions.  Is it due to checkpoint or something else?  We
> > > can probably rule out checkpoint by increasing checkpoint_timeout and
> > > other checkpoint related parameters.
> >
> > I think this is because I did a checkpoint after the VACUUM tests, so the 1st
> > CREATE INDEX (with parallelism) induced some FPW on the catalog blocks.  I
> > didn't try to investigate more since:
> >
> 
> We need to do this.
> 
> > On Thu, Apr 02, 2020 at 11:22:16AM +0530, Amit Kapila wrote:
> > >
> > > Also, I forgot to mention that let's not base this on buffer usage
> > > patch for create index
> > > (v10-0002-Allow-parallel-index-creation-to-accumulate-buff) because as
> > > per recent discussion I am not sure about its usefulness.  I think we
> > > can proceed with this patch without
> > > v10-0002-Allow-parallel-index-creation-to-accumulate-buff as well.
> >
> >
> > Which is done in attached v11.
> >
> 
> Hmm, I haven't suggested removing the WAL usage from the parallel
> create index.   I just told not to use the infrastructure of another
> patch.  We bypass the buffer manager but do write WAL.  See
> _bt_blwritepage->log_newpage.  So we need to accumulate WAL usage even
> if we decide not to do anything about BufferUsage which means we need
> to investigate the above inconsistency in wal_num_fpw and wal_bytes
> between parallel and non-parallel version.


Oh, I thought that you wanted to wait on that part, as we'll probably change
the parallel create index to report buffer access eventually.

v12 attached with an adaptation of Sawada-san's original patch but only dealing
with WAL activity.

I did some more experiment, ensuring as much stability as possible:

=# create table t1(id integer);
CREATE TABLE
=# insert into t1 select * from generate_series(1, 1000000);
INSERT 0 1000000
=# select * from pg_stat_statements_reset() ;
 pg_stat_statements_reset
--------------------------

(1 row)

=# alter table t1 set (parallel_workers = 0);
ALTER TABLE
=# vacuum;checkpoint;
VACUUM
CHECKPOINT
=# create index t1_idx_parallel_0 ON t1(id);
CREATE INDEX

=# alter table t1 set (parallel_workers = 1);
ALTER TABLE
=# vacuum;checkpoint;
VACUUM
CHECKPOINT
=# create index t1_idx_parallel_1 ON t1(id);
CREATE INDEX

=# alter table t1 set (parallel_workers = 2);
ALTER TABLE
=# vacuum;checkpoint;
VACUUM
CHECKPOINT
=# create index t1_idx_parallel_2 ON t1(id);
CREATE INDEX

=# alter table t1 set (parallel_workers = 3);
ALTER TABLE
=# vacuum;checkpoint;
VACUUM
CHECKPOINT
=# create index t1_idx_parallel_3 ON t1(id);
CREATE INDEX

=# alter table t1 set (parallel_workers = 4);
ALTER TABLE
=# vacuum;checkpoint;
VACUUM
CHECKPOINT
=# create index t1_idx_parallel_4 ON t1(id);
CREATE INDEX

=# alter table t1 set (parallel_workers = 5);
ALTER TABLE
=# vacuum;checkpoint;
VACUUM
CHECKPOINT
=# create index t1_idx_parallel_5 ON t1(id);
CREATE INDEX

=# alter table t1 set (parallel_workers = 6);
ALTER TABLE
=# vacuum;checkpoint;
VACUUM
CHECKPOINT
=# create index t1_idx_parallel_6 ON t1(id);
CREATE INDEX

=# alter table t1 set (parallel_workers = 7);
ALTER TABLE
=# vacuum;checkpoint;
VACUUM
CHECKPOINT
=# create index t1_idx_parallel_7 ON t1(id);
CREATE INDEX

=# alter table t1 set (parallel_workers = 8);
ALTER TABLE
=# vacuum;checkpoint;
VACUUM
CHECKPOINT
=# create index t1_idx_parallel_8 ON t1(id);
CREATE INDEX

=# alter table t1 set (parallel_workers = 0);
ALTER TABLE
=# vacuum;checkpoint;
VACUUM
CHECKPOINT
=# create index t1_idx_parallel_0_bis ON t1(id);
CREATE INDEX
=# vacuum;checkpoint;
VACUUM
CHECKPOINT
=# create index t1_idx_parallel_0_ter ON t1(id);
CREATE INDEX

=# select query, calls, wal_bytes, wal_records, wal_num_fpw from pg_stat_statements where query ilike '%create
index%';
                  query                       | calls | wal_bytes | wal_records | wal_num_fpw
----------------------------------------------+-------+-----------+-------------+-------------
 create index t1_idx_parallel_0 ON t1(id)     |     1 |  20389743 |        2762 |        2758
 create index t1_idx_parallel_0_bis ON t1(id) |     1 |  20394391 |        2762 |        2758
 create index t1_idx_parallel_0_ter ON t1(id) |     1 |  20395155 |        2762 |        2758
 create index t1_idx_parallel_1 ON t1(id)     |     1 |  20388335 |        2762 |        2758
 create index t1_idx_parallel_2 ON t1(id)     |     1 |  20389091 |        2762 |        2758
 create index t1_idx_parallel_3 ON t1(id)     |     1 |  20389847 |        2762 |        2758
 create index t1_idx_parallel_4 ON t1(id)     |     1 |  20390603 |        2762 |        2758
 create index t1_idx_parallel_5 ON t1(id)     |     1 |  20391359 |        2762 |        2758
 create index t1_idx_parallel_6 ON t1(id)     |     1 |  20392115 |        2762 |        2758
 create index t1_idx_parallel_7 ON t1(id)     |     1 |  20392871 |        2762 |        2758
 create index t1_idx_parallel_8 ON t1(id)     |     1 |  20393627 |        2762 |        2758
(11 rows)

=# select relname, pg_relation_size(oid) from pg_class where relname like '%t1_id%';
      relname          | pg_relation_size
-----------------------+------------------
 t1_idx_parallel_0     |         22487040
 t1_idx_parallel_0_bis |         22487040
 t1_idx_parallel_0_ter |         22487040
 t1_idx_parallel_2     |         22487040
 t1_idx_parallel_1     |         22487040
 t1_idx_parallel_4     |         22487040
 t1_idx_parallel_3     |         22487040
 t1_idx_parallel_5     |         22487040
 t1_idx_parallel_6     |         22487040
 t1_idx_parallel_7     |         22487040
 t1_idx_parallel_8     |         22487040
(9 rows)


So while the number of WAL records and full page images stay constant, we can
see some small fluctuations in the total amount of generated WAL data, even for
multiple execution of the sequential create index.  I'm wondering if the
fluctuations are due to some other internal details or if the WalUsage support
is just completely broken (although I don't see any obvious issue ATM).