On Thu, 28 Dec 2023 at 01:48, Tom Lane <t...@sss.pgh.pa.us> wrote: > Robins Tharakan <thara...@gmail.com> writes: > > Applying all 4 patches, I also see good performance improvement. > > With more Large Objects, although pg_dump improved significantly, > > pg_restore is now comfortably an order of magnitude faster. > > Yeah. The key thing here is that pg_dump can only parallelize > the data transfer, while (with 0004) pg_restore can parallelize > large object creation and owner-setting as well as data transfer. > I don't see any simple way to improve that on the dump side, > but I'm not sure we need to. Zillions of empty objects is not > really the use case to worry about. I suspect that a more realistic > case with moderate amounts of data in the blobs would make pg_dump > look better. >
Thanks for elaborating, and yes pg_dump times do reflect that expectation. The first test involved a fixed number (32k) of Large Objects (LOs) with varying sizes - I chose that number intentionally since this was being tested on a 32vCPU instance and the patch employs 1k batches. We again see that pg_restore is an order of magnitude faster. LO Size (bytes) restore-HEAD restore-patched improvement (Nx) 1 24.182 1.4 17x 10 24.741 1.5 17x 100 24.574 1.6 15x 1,000 25.314 1.7 15x 10,000 25.644 1.7 15x 100,000 50.046 4.3 12x 1,000,000 281.549 30.0 9x pg_dump also sees improvements. Really small sized LOs see a decent ~20% improvement which grows considerably as LOs get bigger (beyond ~10-100kb). LO Size (bytes) dump-HEAD dump-patched improvement (%) 1 12.9 10.7 18% 10 12.9 10.4 19% 100 12.8 10.3 20% 1,000 13.0 10.3 21% 10,000 14.2 10.3 27% 100,000 32.8 11.5 65% 1,000,000 211.8 23.6 89% To test pg_restore scaling, 1 Million LOs (100kb each) were created and pg_restore times tested for increasing concurrency (on a 192vCPU instance). We see major speedup upto -j64 and the best time was at -j96, after which performance decreases slowly - see attached image. Concurrency pg_restore-patched 384 75.87 352 75.63 320 72.11 288 70.05 256 70.98 224 66.98 192 63.04 160 61.37 128 58.82 96 58.55 64 60.46 32 77.29 16 115.51 8 203.48 4 366.33 Test details: - Command used to generate SQL - create 1k LOs of 1kb each - echo "SELECT lo_from_bytea(0, '\x` printf 'ff%.0s' {1..1000}`') FROM generate_series(1,1000);" > /tmp/tempdel - Verify the LO size: select pg_column_size(lo_get(oid)); - Only GUC changed: max_connections=1000 (for the last test) - Robins Tharakan Amazon Web Services