On Thu, Mar 28, 2024 at 12:55 PM Masahiko Sawada <sawada.m...@gmail.com> wrote: > > Pushed the refactoring patch. > > I've attached the rebased vacuum improvement patch for cfbot. I > mentioned in the commit message that this patch eliminates the 1GB > limitation. > > I think the patch is in good shape. Do you have other comments or > suggestions, John?
I'll do another pass tomorrow, but first I wanted to get in another slightly-challenging in-situ test. On my humble laptop, I can still fit a table large enough to cause PG16 to choke on multiple rounds of index cleanup: drop table if exists test; create unlogged table test (a int, b uuid) with (autovacuum_enabled=false); insert into test (a,b) select i, gen_random_uuid() from generate_series(1,1000*1000*1000) i; create index on test (a); create index on test (b); delete from test; vacuum (verbose, truncate off, parallel 2) test; INFO: vacuuming "john.public.test" INFO: launched 1 parallel vacuum worker for index vacuuming (planned: 1) INFO: finished vacuuming "john.public.test": index scans: 1 pages: 0 removed, 6369427 remain, 6369427 scanned (100.00% of total) tuples: 999997174 removed, 2826 remain, 0 are dead but not yet removable tuples missed: 2826 dead from 18 pages not removed due to cleanup lock contention removable cutoff: 771, which was 0 XIDs old when operation ended new relfrozenxid: 767, which is 4 XIDs ahead of previous value frozen: 0 pages from table (0.00% of total) had 0 tuples frozen index scan needed: 6369409 pages from table (100.00% of total) had 999997174 dead item identifiers removed index "test_a_idx": pages: 2741898 in total, 2741825 newly deleted, 2741825 currently deleted, 0 reusable index "test_b_idx": pages: 3850387 in total, 3842056 newly deleted, 3842056 currently deleted, 0 reusable avg read rate: 159.740 MB/s, avg write rate: 161.726 MB/s buffer usage: 26367981 hits, 14958634 misses, 15144601 dirtied WAL usage: 3 records, 1 full page images, 2050 bytes system usage: CPU: user: 151.89 s, system: 193.54 s, elapsed: 731.59 s Watching pg_stat_progress_vacuum, dead_tuple_bytes got up to 398458880. About the "tuples missed" -- I didn't expect contention during this test. I believe that's completely unrelated behavior, but wanted to mention it anyway, since I found it confusing.