From: Tristan Partin <tristan@neon.tech>
To: Tristan Partin <tristan@neon.tech>
To: pgsql-hackers@postgresql.org
Subject: Re: Use COPY for populating all pgbench tables
Date: Wed, 14 Jun 2023 10:58:06 -0500
Message-ID: <CTCHWOCZ9X8Z.1PDXVDUXZ3D0P@gonk> (raw)
In-Reply-To: <CTCG9VOM6GJ4.3NDBRQ7U4EP4Z@gonk>
References: <CSTU5P82ONZ1.19XFUGHMXHBRY@c3po>
<CTCG9VOM6GJ4.3NDBRQ7U4EP4Z@gonk>
Again, I forget to actually attach. Holy guacamole.
--
Tristan Partin
Neon (https://neon.tech)
Attachments:download all (2 files, .tar.gz)v2-0001-Move-constant-into-format-string.patch (text/x-patch, 977B) download
From 8c4eb4849b1282f1a0947ddcf3f599e384a5a428 Mon Sep 17 00:00:00 2001
From: Tristan Partin <tristan@neon.tech>
Date: Tue, 23 May 2023 09:21:55 -0500
Subject: [PATCH v2 1/2] Move constant into format string
If we are always going to write a 0, just put the 0 in the format
string.
---
src/bin/pgbench/pgbench.c | 4 ++--
1 file changed, 2 insertions(+), 2 deletions(-)
diff --git a/src/bin/pgbench/pgbench.c b/src/bin/pgbench/pgbench.cindex 1d1670d4c2..320d348a0f 100644--- a/src/bin/pgbench/pgbench.c+++ b/src/bin/pgbench/pgbench.c@@ -4947,8 +4947,8 @@ initGenerateDataClientSide(PGconn *con)
/* "filler" column defaults to blank padded empty string */
printfPQExpBuffer(&sql,
- INT64_FORMAT "\t" INT64_FORMAT "\t%d\t\n",- j, k / naccounts + 1, 0);+ INT64_FORMAT "\t" INT64_FORMAT "\t0\t\n",+ j, k / naccounts + 1);
if (PQputline(con, sql.data))
pg_fatal("PQputline failed");
--
Tristan Partin
Neon (https://neon.tech)
v2-0002-Use-COPY-instead-of-INSERT-for-populating-all-tab.patch (text/x-patch, 7.6K) download
From 8c573cbede228a5b5570d3ca37055bd40d4a4025 Mon Sep 17 00:00:00 2001
From: Tristan Partin <tristan@neon.tech>
Date: Tue, 23 May 2023 11:48:16 -0500
Subject: [PATCH v2 2/2] Use COPY instead of INSERT for populating all tables
COPY is a better interface for bulk loading and/or high latency
connections. Previously COPY was only used for the pgbench_accounts
table because the amount of data was so much larger than the other
tables. However, as already said there are also gains to be had in the
high latency case, such as loading data across continents.
---
src/bin/pgbench/pgbench.c | 152 ++++++++++++++++++++++----------------
1 file changed, 87 insertions(+), 65 deletions(-)
diff --git a/src/bin/pgbench/pgbench.c b/src/bin/pgbench/pgbench.cindex 320d348a0f..9c57f7e4a7 100644--- a/src/bin/pgbench/pgbench.c+++ b/src/bin/pgbench/pgbench.c@@ -4869,17 +4869,46 @@ initTruncateTables(PGconn *con)
"pgbench_tellers");
}
-/*- * Fill the standard tables with some data generated and sent from the client- */+typedef void initRow(PGconn *con, PQExpBufferData *sql, int64 curr);+
static void
-initGenerateDataClientSide(PGconn *con)+initBranch(PGconn *con, PQExpBufferData *sql, int64 curr)+{+ /* "filler" column defaults to NULL */+ printfPQExpBuffer(sql,+ INT64_FORMAT "\t0\t\n",+ curr + 1);+}++static void+initTeller(PGconn *con, PQExpBufferData *sql, int64 curr)+{+ /* "filler" column defaults to NULL */+ printfPQExpBuffer(sql,+ INT64_FORMAT "\t" INT64_FORMAT "\t0\t\n",+ curr + 1, curr / ntellers + 1);+}++static void+initAccount(PGconn *con, PQExpBufferData *sql, int64 curr)+{+ /* "filler" column defaults to blank padded empty string */+ printfPQExpBuffer(sql,+ INT64_FORMAT "\t" INT64_FORMAT "\t0\t\n",+ curr + 1, curr / naccounts + 1);+}++static void+initPopulateTable(PGconn *con, const char *table, int64 base, initRow *init_row)
{
+ int n;+ int k;+ int chars = 0;+ PGresult *res;
PQExpBufferData sql;
- PGresult *res;- int i;- int64 k;- char *copy_statement;+ char copy_statement[256];+ const char *copy_statement_fmt;+ const int64 total = base * scale;
/* used to track elapsed time and estimate of the remaining time */
pg_time_usec_t start;
@@ -4888,50 +4917,19 @@ initGenerateDataClientSide(PGconn *con)
/* Stay on the same line if reporting to a terminal */
char eol = isatty(fileno(stderr)) ? '\r' : '\n';
- fprintf(stderr, "generating data (client-side)...\n");-- /*- * we do all of this in one transaction to enable the backend's- * data-loading optimizations- */- executeStatement(con, "begin");-- /* truncate away any old data */- initTruncateTables(con);-
initPQExpBuffer(&sql);
- /*- * fill branches, tellers, accounts in that order in case foreign keys- * already exist- */- for (i = 0; i < nbranches * scale; i++)- {- /* "filler" column defaults to NULL */- printfPQExpBuffer(&sql,- "insert into pgbench_branches(bid,bbalance) values(%d,0)",- i + 1);- executeStatement(con, sql.data);- }-- for (i = 0; i < ntellers * scale; i++)- {- /* "filler" column defaults to NULL */- printfPQExpBuffer(&sql,- "insert into pgbench_tellers(tid,bid,tbalance) values (%d,%d,0)",- i + 1, i / ntellers + 1);- executeStatement(con, sql.data);- }-- /*- * accounts is big enough to be worth using COPY and tracking runtime- */-
/* use COPY with FREEZE on v14 and later without partitioning */
if (partitions == 0 && PQserverVersion(con) >= 140000)
- copy_statement = "copy pgbench_accounts from stdin with (freeze on)";+ copy_statement_fmt = "copy %s from stdin with (freeze on)";
else
- copy_statement = "copy pgbench_accounts from stdin";+ copy_statement_fmt = "copy %s from stdin";++ n = pg_snprintf(copy_statement, sizeof(copy_statement), copy_statement_fmt, table);+ if (n >= sizeof(copy_statement))+ pg_fatal("invalid buffer size: must be at least %d characters long", n);+ else if (n == -1)+ pg_fatal("invalid format string");
res = PQexec(con, copy_statement);
@@ -4941,14 +4939,11 @@ initGenerateDataClientSide(PGconn *con)
start = pg_time_now();
- for (k = 0; k < (int64) naccounts * scale; k++)+ for (k = 0; k < total; k++)
{
- int64 j = k + 1;+ const int64 j = k + 1;- /* "filler" column defaults to blank padded empty string */- printfPQExpBuffer(&sql,- INT64_FORMAT "\t" INT64_FORMAT "\t0\t\n",- j, k / naccounts + 1);+ init_row(con, &sql, k);
if (PQputline(con, sql.data))
pg_fatal("PQputline failed");
@@ -4962,25 +4957,26 @@ initGenerateDataClientSide(PGconn *con)
if ((!use_quiet) && (j % 100000 == 0))
{
double elapsed_sec = PG_TIME_GET_DOUBLE(pg_time_now() - start);
- double remaining_sec = ((double) scale * naccounts - j) * elapsed_sec / j;+ double remaining_sec = ((double) total - j) * elapsed_sec / j;- fprintf(stderr, INT64_FORMAT " of " INT64_FORMAT " tuples (%d%%) done (elapsed %.2f s, remaining %.2f s)%c",- j, (int64) naccounts * scale,- (int) (((int64) j * 100) / (naccounts * (int64) scale)),- elapsed_sec, remaining_sec, eol);+ chars = fprintf(stderr, INT64_FORMAT " of " INT64_FORMAT " tuples (%d%%) of %s done (elapsed %.2f s, remaining %.2f s)%c",+ j, total,+ (int) ((j * 100) / total),+ table, elapsed_sec, remaining_sec, eol);
}
/* let's not call the timing for each row, but only each 100 rows */
else if (use_quiet && (j % 100 == 0))
{
double elapsed_sec = PG_TIME_GET_DOUBLE(pg_time_now() - start);
- double remaining_sec = ((double) scale * naccounts - j) * elapsed_sec / j;+ double remaining_sec = ((double) total - j) * elapsed_sec / j;
/* have we reached the next interval (or end)? */
- if ((j == scale * naccounts) || (elapsed_sec >= log_interval * LOG_STEP_SECONDS))+ if ((j == total) || (elapsed_sec >= log_interval * LOG_STEP_SECONDS))
{
- fprintf(stderr, INT64_FORMAT " of " INT64_FORMAT " tuples (%d%%) done (elapsed %.2f s, remaining %.2f s)%c",- j, (int64) naccounts * scale,- (int) (((int64) j * 100) / (naccounts * (int64) scale)), elapsed_sec, remaining_sec, eol);+ chars = fprintf(stderr, INT64_FORMAT " of " INT64_FORMAT " tuples (%d%%) of %s done (elapsed %.2f s, remaining %.2f s)%c",+ j, total,+ (int) ((j * 100) / total),+ table, elapsed_sec, remaining_sec, eol);
/* skip to the next interval */
log_interval = (int) ceil(elapsed_sec / LOG_STEP_SECONDS);
@@ -4988,8 +4984,8 @@ initGenerateDataClientSide(PGconn *con)
}
}
- if (eol != '\n')- fputc('\n', stderr); /* Need to move to next line */+ if (chars != 0 && eol != '\n')+ fprintf(stderr, "%*c\r", chars - 1, ' '); /* Clear the current line */
if (PQputline(con, "\\.\n"))
pg_fatal("very last PQputline failed");
@@ -4997,6 +4993,32 @@ initGenerateDataClientSide(PGconn *con)
pg_fatal("PQendcopy failed");
termPQExpBuffer(&sql);
+}++/*+ * Fill the standard tables with some data generated and sent from the client+ */+static void+initGenerateDataClientSide(PGconn *con)+{+ fprintf(stderr, "generating data (client-side)...\n");++ /*+ * we do all of this in one transaction to enable the backend's+ * data-loading optimizations+ */+ executeStatement(con, "begin");++ /* truncate away any old data */+ initTruncateTables(con);++ /*+ * fill branches, tellers, accounts in that order in case foreign keys+ * already exist+ */+ initPopulateTable(con, "pgbench_branches", nbranches, initBranch);+ initPopulateTable(con, "pgbench_tellers", ntellers, initTeller);+ initPopulateTable(con, "pgbench_accounts", naccounts, initAccount);
executeStatement(con, "commit");
}
--
Tristan Partin
Neon (https://neon.tech)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: tristan@neon.tech
Subject: Re: Use COPY for populating all pgbench tables
In-Reply-To: <CTCHWOCZ9X8Z.1PDXVDUXZ3D0P@gonk>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox