Re: Proposal to add page headers to SLRU pages

> On Mar 7, 2024, at 03:09, Stephen Frost <sfrost@snowman.net> wrote:
> 
> External Email
> 
> From: Stephen Frost <sfrost@snowman.net>
> Subject: Re: Proposal to add page headers to SLRU pages
> Date: March 7, 2024 at 03:09:59 GMT+8
> To: Alvaro Herrera <alvherre@alvh.no-ip.org>
> Cc: "Li, Yong" <yoli@ebay.com>, Aleksander Alekseev <aleksander@timescale.com>, PostgreSQL Hackers
<pgsql-hackers@lists.postgresql.org>,"Bagga, Rishu" <bagrishu@amazon.com>, Robert Haas <robertmhaas@gmail.com>,
"Debnath,Shawn" <sdn@ebay.com>, Andrey Borodin <x4mmm@yandex-team.ru>, "Shyrabokau, Anton" <antons@ebay.com>
 
> 
> 
> Greetings,
> 
> * Alvaro Herrera (alvherre@alvh.no-ip.org) wrote:
>> I suppose this is important to do if we ever want to move SLRUs into
>> shared buffers.  However, I wonder about the extra time this adds to
>> pg_upgrade.  Is this something we should be concerned about?  Is there
>> any measurement/estimates to tell us how long this would be?  Right now,
>> if you use a cloning strategy for the data files, the upgrade should be
>> pretty quick ... but the amount of data in pg_xact and pg_multixact
>> could be massive, and the rewrite is likely to take considerable time.
> 
> While I definitely agree that there should be some consideration of
> this concern, it feels on-par with the visibility-map rewrite which was
> done previously.  Larger systems will likely have more to deal with than
> smaller systems, but it's still a relatively small portion of the data
> overall.
> 
> The benefit of this change, beyond just the possibility of moving them
> into shared buffers some day in the future, is that this would mean that
> SLRUs will have checksums (if the cluster has them enabled).  That
> benefit strikes me as well worth the cost of the rewrite taking some
> time and the minor loss of space due to the page header.
> 
> Would it be useful to consider parallelizing this work?  There's already
> parts of pg_upgrade which can be parallelized and so this isn't,
> hopefully, a big lift to add, but I'm not sure if there's enough work
> being done here CPU-wise, compared to the amount of IO being done, to
> have it make sense to run it in parallel.  Might be worth looking into
> though, at least, as disks have gotten to be quite fast.
> 
> Thanks!
> 
> Stephen
> 


Thank Alvaro and Stephen for your thoughtful comments.

I did a quick benchmark regarding pg_upgrade time, and here are the results.

Hardware spec:
MacBook Pro M1 Max - 10 cores, 64GB memory, 1TB Apple SSD

Operating system:
macOS 14.3.1

Complier:
Apple clang 15.0.0

Compiler optimization level: -O2

====
PG setups:
Old cluster:  PG 16.2 release (source build)
New cluster:  PG Git HEAD plus the patch (source build)

====
Benchmark steps:

1. Initdb for PG 16.2.
2. Initdb for PG HEAD.
3. Run pg_upgrade on the above empty database, and time the overall wall clock time.
4. In the old cluster, write 512MB all-zero dummy segment files (2048 segments) under pg_xact.
5. In the old cluster, write 512MB all-zero dummy segment files under pg_multixact/members.
6. In the old cluster, write 512MB all-zero dummy segment files under pg_multixact/offsets.
7. Purge the OS page cache.
7. Run pg_upgrade again, and time the overall wall clock time.

====
Test result:

On the empty database, pg_upgrade took 4.8 seconds to complete.

With 1.5GB combined SLRU data to convert, pg_upgrade took 11.5 seconds to complete.

It took 6.7 seconds to convert 1.5GB SLRU files for pg_upgrade.

====

For clog, 2048 segments can host about 2 billion transactions, right at the limit for wraparound.
That’s the maximum we can have.  2048 segments are also big for pg_multixact SLRUs.

Therefore, on a modern hardware, in the worst case, pg_upgrade will run for 7 seconds longer.


Regards,

Yong