Re: Add pg_walinspect function with block info columns

On Wed, Mar 22, 2023 at 11:35 AM Melanie Plageman
<melanieplageman@gmail.com> wrote:
>
> On Fri, Mar 17, 2023 at 8:51 PM Michael Paquier <michael@paquier.xyz> wrote:
> >
> > On Fri, Mar 17, 2023 at 04:36:58PM -0700, Peter Geoghegan wrote:
> > > I'm sure that they will do that much more than they would have
> > > otherwise. Since we'll have made pg_get_wal_block_info() so much more
> > > useful than pg_get_wal_records_info() for many important use cases.
> > > Why is that a bad thing? Are you concerned about the overhead of
> > > pulling in FPIs when pg_get_wal_block_info() is run, if Bharath's
> > > patch is committed? That could be a problem, I suppose -- but it would
> > > be good to get more data on that. Do you think that this will be much
> > > of an issue, Bharath?
> >
> > Yes.  The CPU cost is one thing, but I am also worrying about the
> > I/O cost with a tuplestore spilling to disk a large number of FPIs,
> > and some workloads can generate WAL so as FPIs is what makes for most
> > of the contents stored in the WAL.  (wal_compression is very effective
> > in such cases, for example.)
>
> I had done some analysis about CPU costs for decompressing FPI upthread
> in [1], finding that adding a parameter to allow skipping outputting FPI
> would not have much impact when FPI are compressed, as decompressing the
> images comprised very little of the overall time.
>
> After reading what you said, I was interested to see how substantial the
> I/O cost with non-compressed FPI would be.
>
> Using a patch with a parameter to pg_get_wal_block_info() to skip
> outputting FPI, I found that on a fast local nvme ssd, the timing
> difference between doing so and not still isn't huge -- 9 seconds when
> outputting the FPI vs 8.5 seconds when skipping outputting FPI. (with
> ~50,000 records all with non-compressed FPIs).
>
> However, perhaps obviously, the I/O cost is worse.
> Doing nothing but
>
>   SELECT *  FROM pg_get_wal_block_info(:start_lsn, :end_lsn, true)
> where fpi is not null;

Sorry, I should have been more clear: similar results with a select list
simply excluding fpi and no where clause.

- Melanie