Subject: Re: Use COPY for populating all pgbench tables

From: "Tristan Partin" <[email-masked]>
Date: Wed, 07 Jun 2023 14:16:00 -0500

On Tue May 23, 2023 at 12:33 PM CDT, Tristan Partin wrote:
> I wanted to come with benchmarks, but unfortunately I won't have them
> until next month. I can follow-up in a future email.

I finally got around to benchmarking.

master:

$ ./build/src/bin/pgbench/pgbench -i -s 500 CONNECTION_STRING
dropping old tables...
NOTICE:  table "pgbench_accounts" does not exist, skipping
NOTICE:  table "pgbench_branches" does not exist, skipping
NOTICE:  table "pgbench_history" does not exist, skipping
NOTICE:  table "pgbench_tellers" does not exist, skipping
creating tables...
generating data (client-side)...
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:

$ ./build/src/bin/pgbench/pgbench -i -s 500 CONNECTION_STRING
dropping old tables...
NOTICE:  table "pgbench_accounts" does not exist, skipping
NOTICE:  table "pgbench_branches" does not exist, skipping
NOTICE:  table "pgbench_history" does not exist, skipping
NOTICE:  table "pgbench_tellers" does not exist, skipping
creating tables...
generating data (client-side)...
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 am located in Austin, Texas. The server was located in Ireland. Just
wanted to put some distance between the client and server. To summarize,
that is about an 80% reduction in client-side data generation times.

Note that I used Neon to collect these results.

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

---

From: David Rowley <[email-masked]>
Date: Thu, 8 Jun 2023 17:33:57 +1200

> 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.

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.

David
diff --git a/src/bin/pgbench/pgbench.c b/src/bin/pgbench/pgbench.c
index 1d1670d4c2..189670ffb8 100644
--- a/src/bin/pgbench/pgbench.c
+++ b/src/bin/pgbench/pgbench.c
@@ -4869,6 +4869,54 @@ initTruncateTables(PGconn *con)
 					 "pgbench_tellers");
 }
 
+static inline char *
+uint64_to_hex(char *buffer, uint64 num)
+{
+	char   *p = buffer;
+	char   *hexnum = "0123456789ABCDEF";
+
+	*p++ = '0';
+	*p++ = 'x';
+
+	if (num != 0)
+	{
+		unsigned long result;
+		char   *end;
+
+#if defined(_MSC_VER) && (defined(_M_AMD64) || defined(_M_ARM64))
+		_BitScanReverse64(&result, num);
+#elif defined(HAVE__BUILTIN_CLZ)
+
+#if defined(HAVE_LONG_INT_64)
+		result = 63 - __builtin_clzl(num);
+#elif defined(HAVE_LONG_LONG_INT_64)
+		result = 63 - __builtin_clzll(num);
+#else
+#error must have a working 64-bit integer datatype
+#endif
+
+#else
+		/* XXX what to do? */
+#error unable to find method to find left-most 1 bit
+#endif
+		end = &p[(result / 4) + 1];
+		p = &end[-1];
+
+		do {
+			*p-- = hexnum[num % 16];
+			num /= 16;
+		} while (num != 0);
+
+		return end;
+	}
+	else
+	{
+		*p++ = '0';
+		return p;
+	}
+
+}
+
 /*
  * Fill the standard tables with some data generated and sent from the client
  */
@@ -4880,6 +4928,7 @@ initGenerateDataClientSide(PGconn *con)
 	int			i;
 	int64		k;
 	char	   *copy_statement;
+	int			server_version = PQserverVersion(con);
 
 	/* used to track elapsed time and estimate of the remaining time */
 	pg_time_usec_t start;
@@ -4928,7 +4977,7 @@ initGenerateDataClientSide(PGconn *con)
 	 */
 
 	/* use COPY with FREEZE on v14 and later without partitioning */
-	if (partitions == 0 && PQserverVersion(con) >= 140000)
+	if (partitions == 0 && server_version >= 140000)
 		copy_statement = "copy pgbench_accounts from stdin with (freeze on)";
 	else
 		copy_statement = "copy pgbench_accounts from stdin";
@@ -4945,13 +4994,39 @@ initGenerateDataClientSide(PGconn *con)
 	{
 		int64		j = k + 1;
 
-		/* "filler" column defaults to blank padded empty string */
-		printfPQExpBuffer(&sql,
-						  INT64_FORMAT "\t" INT64_FORMAT "\t%d\t\n",
-						  j, k / naccounts + 1, 0);
-		if (PQputline(con, sql.data))
-			pg_fatal("PQputline failed");
-
+		if (server_version < 160000)
+		{
+			/* "filler" column defaults to blank padded empty string */
+			printfPQExpBuffer(&sql,
+							  INT64_FORMAT "\t" INT64_FORMAT "\t%d\t\n",
+							  j, k / naccounts + 1, 0);
+			if (PQputline(con, sql.data))
+				pg_fatal("PQputline failed");
+		}
+		else
+		{
+			/*
+			 * For PostgreSQL 16 and onward, we can form the COPY line with
+			 * the numerical values formatted in hex.  These are more
+			 * efficient to build than forming them as decimal strings.
+			 */
+			char buffer[18 + 1 + 18 + 1 + 10 + 2];
+			char *p = buffer;
+
+			p = uint64_to_hex(p, j);
+			*p++ = '\t';
+			p = uint64_to_hex(p, k / naccounts + 1);
+			*p++ = '\t';
+			*p++ = '0';
+			*p++ = '\t';
+			*p++ = '\n';
+			*p = '\0';
+
+			Assert(strlen(buffer) == p - buffer);
+
+			if (PQputnbytes(con, buffer, p - buffer))
+				pg_fatal("PQputline failed");
+		}
 		if (CancelRequested)
 			break;

---

From: Hannu Krosing <[email-masked]>
Date: Thu, 8 Jun 2023 13:37:11 +0200

I guess that COPY will still be slower than  generating the data
server-side ( --init-steps=...G... ) ?

What I'd really like to see is providing all the pgbench functions
also on the server. Specifically the various random(...) functions -
random_exponential(...), random_gaussian(...), random_zipfian(...) so
that also custom data generationm could be easily done server-side
with matching distributions.

---

From: "Tristan Partin" <[email-masked]>
Date: Thu, 08 Jun 2023 11:38:01 -0500

> pgbench and also parse on the postgres side.

Do you have a link to some docs? I have not heard of the feature.
Definitely feels like a worthy cause.

> 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.

> 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.

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