Re: Add pg_walinspect function with block info columns

On Fri, Mar 17, 2023 at 8:51 PM Michael Paquier <michael(at)paquier(dot)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;

per iostat, the write latency was double for the query which output fpi
from the one that didn't and the wkB/s was much higher. This is probably
obvious, but I'm just wondering if it makes sense to have such a
parameter to avoid impacting a system which is doing concurrent I/O with
walinspect.

I have had use for block info without seeing the FPIs, personally.

- Melanie

[1] https://www.postgresql.org/message-id/CAAKRu_bJvbcYBRj2cN6G2xV7B7-Ja%2BpjTO1nEnEhRR8OXYiABA%40mail.gmail.com