Horton

Re: WAL usage calculation patch

Amit Kapila <amit.kapila16@gmail.com>

View thread
From: Amit Kapila <amit.kapila16@gmail.com>
To: Dilip Kumar <dilipbalaut@gmail.com>
Cc: Julien Rouhaud <rjuju123@gmail.com>, Kuntal Ghosh <kuntalghosh.2007@gmail.com>, Fujii Masao <masao.fujii@oss.nttdata.com>, Kirill Bychik <kirill.bychik@gmail.com>, PostgreSQL Hackers <pgsql-hackers@postgresql.org>, Thomas Munro <thomas.munro@gmail.com>
Date: 2020-04-03T04:10:34Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Change the display of WAL usage statistics in Explain.

  2. Cosmetic fixups for WAL usage work.

  3. Allow parallel create index to accumulate buffer usage stats.

  4. Allow autovacuum to log WAL usage statistics.

  5. Add the option to report WAL usage in EXPLAIN and auto_explain.

  6. Allow pg_stat_statements to track WAL usage statistics.

  7. Add infrastructure to track WAL usage.

  8. Include information on buffer usage during planning phase, in EXPLAIN output, take two.

  9. Include information on buffer usage during planning phase, in EXPLAIN output.

  10. Allow parallel vacuum to accumulate buffer usage.

  11. Allow pg_stat_statements to track planning statistics.

On Fri, Apr 3, 2020 at 9:35 AM Dilip Kumar <dilipbalaut@gmail.com> wrote:
>
> On Fri, Apr 3, 2020 at 9:17 AM Dilip Kumar <dilipbalaut@gmail.com> wrote:
> >
> > On Fri, Apr 3, 2020 at 9:02 AM Amit Kapila <amit.kapila16@gmail.com> wrote:
> > >
> > > On Fri, Apr 3, 2020 at 8:55 AM Dilip Kumar <dilipbalaut@gmail.com> wrote:
> > > >
> > > > I think now I got the reason.  Basically, both of these records are
> > > > storing the FPW, and FPW size can vary based on the hole size on the
> > > > page.  If hold size is smaller the image length will be more, the
> > > > image_len= BLCKSZ-hole_size.  So in subsequent records, the image size
> > > > is bigger.
> > > >
> > >
> > > This means if we always re-create the database or may be keep
> > > full_page_writes to off, then we should get consistent WAL usage data
> > > for all tests.
> >
> > With new database, it is always the same.  But, with full-page write,
> > I could see one of the create index is writing extra wal and if we
> > change the older then the new create index at that place will write
> > extra wal.  I guess that could be due to a non-in place update in some
> > of the system tables.
>
> I have analyzed the WAL and there could be multiple reasons for the
> same.  With small data, I have noticed that while inserting in the
> system index there was a Page Split and that created extra WAL.
>

Thanks for the investigation.  I think it is clear that we can't
expect the same WAL size even if we repeat the same operation unless
it is a fresh database.


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