Subject: Re: Use COPY for populating all pgbench tables

From: "Tristan Partin" <[email-masked]>
Date: Tue, 13 Jun 2023 12:47:33 -0500

On Thu Jun 8, 2023 at 11:38 AM CDT, Tristan Partin wrote:
> On Thu Jun 8, 2023 at 12:33 AM CDT, David Rowley wrote:
> > On Thu, 8 Jun 2023 at 07:16, Tristan Partin <[email-masked]> wrote:
> > >
> > > master:
> > >
> > > 50000000 of 50000000 tuples (100%) done (elapsed 260.93 s, remaining 0.00 s))
> > > vacuuming...
> > > creating primary keys...
> > > done in 1414.26 s (drop tables 0.20 s, create tables 0.82 s, client-side generate 1280.43 s, vacuum 2.55 s, primary keys 130.25 s).
> > >
> > > patchset:
> > >
> > > 50000000 of 50000000 tuples (100%) of pgbench_accounts done (elapsed 243.82 s, remaining 0.00 s))
> > > vacuuming...
> > > creating primary keys...
> > > done in 375.66 s (drop tables 0.14 s, create tables 0.73 s, client-side generate 246.27 s, vacuum 2.77 s, primary keys 125.75 s).
> >
> > I've also previously found pgbench -i to be slow.  It was a while ago,
> > and IIRC, it was due to the printfPQExpBuffer() being a bottleneck
> > inside pgbench.
> >
> > On seeing your email, it makes me wonder if PG16's hex integer
> > literals might help here.  These should be much faster to generate in
> > pgbench and also parse on the postgres side.
> >
> > I wrote a quick and dirty patch to try that and I'm not really getting
> > the same performance increases as I'd have expected. I also tested
> > with your patch too and it does not look that impressive either when
> > running pgbench on the same machine as postgres.
>
> I didn't expect my patch to increase performance in all workloads. I was
> mainly aiming to fix high-latency connections. Based on your results
> that looks like a 4% reduction in performance of client-side data
> generation. I had thought maybe it is worth having a flag to keep the
> old way too, but I am not sure a 4% hit is really that big of a deal.
>
> > pgbench copy speedup
> >
> > ** master
> > drowley@amd3990x:~$ pgbench -i -s 1000 postgres
> > 100000000 of 100000000 tuples (100%) done (elapsed 74.15 s, remaining 0.00 s)
> > vacuuming...
> > creating primary keys...
> > done in 95.71 s (drop tables 0.00 s, create tables 0.01 s, client-side
> > generate 74.45 s, vacuum 0.12 s, primary keys 21.13 s).
> >
> > ** David's Patched
> > drowley@amd3990x:~$ pgbench -i -s 1000 postgres
> > 100000000 of 100000000 tuples (100%) done (elapsed 69.64 s, remaining 0.00 s)
> > vacuuming...
> > creating primary keys...
> > done in 90.22 s (drop tables 0.00 s, create tables 0.01 s, client-side
> > generate 69.91 s, vacuum 0.12 s, primary keys 20.18 s).
> >
> > ** Tristan's patch
> > drowley@amd3990x:~$ pgbench -i -s 1000 postgres
> > 100000000 of 100000000 tuples (100%) of pgbench_accounts done (elapsed
> > 77.44 s, remaining 0.00 s)
> > vacuuming...
> > creating primary keys...
> > done in 98.64 s (drop tables 0.00 s, create tables 0.01 s, client-side
> > generate 77.47 s, vacuum 0.12 s, primary keys 21.04 s).
> >
> > I'm interested to see what numbers you get.  You'd need to test on
> > PG16 however. I left the old code in place to generate the decimal
> > numbers for versions < 16.
>
> I will try to test this soon and follow up on the thread. I definitely
> see no problems with your patch as is though. I would be more than happy
> to rebase my patches on yours.

Finally got around to doing more benchmarking. Using an EC2 instance
hosted in Ireland, and my client laptop in Austin, Texas.

Workload: pgbench -i -s 500

master (9aee26a491)
done in 1369.41 s (drop tables 0.21 s, create tables 0.72 s, client-side generate 1336.44 s, vacuum 1.02 s, primary keys 31.03 s).
done in 1318.31 s (drop tables 0.21 s, create tables 0.72 s, client-side generate 1282.67 s, vacuum 1.02 s, primary keys 33.69 s).

copy
done in 307.42 s (drop tables 0.21 s, create tables 0.82 s, client-side generate 270.95 s, vacuum 1.02 s, primary keys 34.42 s).

david
done in 1311.14 s (drop tables 0.72 s, create tables 0.72 s, client-side generate 1274.98 s, vacuum 0.94 s, primary keys 33.79 s).
done in 1340.18 s (drop tables 0.14 s, create tables 0.59 s, client-side generate 1304.78 s, vacuum 0.92 s, primary keys 33.75 s).

copy + david
done in 348.70 s (drop tables 0.23 s, create tables 0.72 s, client-side generate 312.94 s, vacuum 0.92 s, primary keys 33.90 s).

I ran two tests for master and your patch David. For the last test, I
adapted your patch onto mine. I am still seeing the huge performance
gains on my branch.

-- 
Tristan Partin
Neon (https://neon.tech)