> On Mar 7, 2024, at 03:09, Stephen Frost <sfr...@snowman.net> wrote: > > External Email > > From: Stephen Frost <sfr...@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 <alvhe...@alvh.no-ip.org> > Cc: "Li, Yong" <y...@ebay.com>, Aleksander Alekseev > <aleksan...@timescale.com>, PostgreSQL Hackers > <pgsql-hackers@lists.postgresql.org>, "Bagga, Rishu" <bagri...@amazon.com>, > Robert Haas <robertmh...@gmail.com>, "Debnath, Shawn" <s...@ebay.com>, Andrey > Borodin <x4...@yandex-team.ru>, "Shyrabokau, Anton" <ant...@ebay.com> > > > Greetings, > > * Alvaro Herrera (alvhe...@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
