Received: from malur.postgresql.org ([217.196.149.56])
	by arkaria.postgresql.org with esmtps
 (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256)
	(Exim 4.92)
	(envelope-from <pgsql-hackers-owner+archive@lists.postgresql.org>)
	id 1l8Vpw-0005Ay-99
	for pgsql-hackers@arkaria.postgresql.org; Sat, 06 Feb 2021 22:13:53 +0000
Received: from localhost ([127.0.0.1] helo=malur.postgresql.org)
	by malur.postgresql.org with esmtp (Exim 4.92)
	(envelope-from <pgsql-hackers-owner+archive@lists.postgresql.org>)
	id 1l8Vpt-0007Ho-Rh
	for pgsql-hackers@arkaria.postgresql.org; Sat, 06 Feb 2021 22:13:49 +0000
Received: from makus.postgresql.org ([2001:4800:3e1:1::229])
	by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256)
	(Exim 4.92)
	(envelope-from <hlinnaka@iki.fi>)
	id 1l8Vpt-0007Hh-Ca
	for pgsql-hackers@lists.postgresql.org; Sat, 06 Feb 2021 22:13:49 +0000
Received: from meesny.iki.fi ([2001:67c:2b0:1c1::201])
	by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256)
	(Exim 4.92)
	(envelope-from <hlinnaka@iki.fi>)
	id 1l8Vpq-0002Tb-1z
	for pgsql-hackers@postgresql.org; Sat, 06 Feb 2021 22:13:48 +0000
Received: from [192.168.1.113] (dsl-hkibng22-54faa4-119.dhcp.inet.fi
 [84.250.164.119])
	(using TLSv1.3 with cipher TLS_AES_128_GCM_SHA256 (128/128 bits)
	 key-exchange X25519 server-signature RSA-PSS (2048 bits) server-digest
 SHA256)
	(No client certificate requested)
	(Authenticated sender: hlinnaka)
	by meesny.iki.fi (Postfix) with ESMTPSA id 4E7ED20380
	for <pgsql-hackers@postgresql.org>; Sun,  7 Feb 2021 00:13:39 +0200 (EET)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=iki.fi; s=meesny;
	t=1612649619;
	h=from:from:reply-to:subject:subject:date:date:message-id:message-id:
	 to:to:cc:mime-version:mime-version:content-type:content-type;
	bh=8a786ru7Dw8hsMa+CpfTaxrtMYcSyao0tB0uSUV72Jw=;
	b=BdWHgBZo2KGZUovyEnLTgw1xFSHGaHYw8n2AqAdTcQxm58CCsz1cbJsxAlaItUhbRJy7yt
	XJCoW55iopvtU8NqnjfX73cHY9a89YrwSQc/l76if0dFYYqy+UvT3e3oFWplSAEFlc4cCf
	3ae3bp3b+tHcpRQ+Jmn7uqIe4hzmpDk=
From: Heikki Linnakangas <hlinnaka@iki.fi>
Subject: psql \copy from sends a lot of packets
To: pgsql-hackers <pgsql-hackers@postgresql.org>
Message-ID: <40b2cec0-d0fb-3191-2ae1-9a3fe16a7e48@iki.fi>
Date: Sun, 7 Feb 2021 00:13:38 +0200
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:78.0) Gecko/20100101
 Thunderbird/78.6.1
MIME-Version: 1.0
Content-Type: multipart/mixed;
 boundary="------------20DC7BBD34247104D0B00618"
Content-Language: en-US
ARC-Seal: i=1; s=meesny; d=iki.fi; t=1612649619; a=rsa-sha256; cv=none;
	b=DUCSnTK8sFLugngI7GjHsMA4dkxhzaCmCzQoMUnQQtGSrZlkAshokkybyNz7omRNXiBCBk
	YfBHjTyIHg3O0aeFddv3JjKmS83QaxnXrNHnXFUL0KKk9Te7tsiVP1POd5nA0PAo/AirE+
	q5vicMVjQFXUuAQ+YnJA77GOOiiIU28=
ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=iki.fi;
	s=meesny; t=1612649619;
	h=from:from:reply-to:subject:subject:date:date:message-id:message-id:
	 to:to:cc:mime-version:mime-version:content-type:content-type;
	bh=8a786ru7Dw8hsMa+CpfTaxrtMYcSyao0tB0uSUV72Jw=;
	b=OJMB4TuFR9K4Hcee8isaG3ZWuQd6bdZ3/vReKnWPXUJ/qnYaFWgh7wP43NQsSVXaZTscPT
	G98OQYNk/Kk25l32UD7KTtjrlXfCuPyRwRWmILB3QJpFWIYb4CSJKkkXe9IMkdUrxj56PR
	UWabmCQ4gr8hnVSgVOz/bfaUoaAkDnQ=
ARC-Authentication-Results: i=1;
	ORIGINATING;
	auth=pass smtp.auth=hlinnaka smtp.mailfrom=hlinnaka@iki.fi
List-Id: <pgsql-hackers.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-hackers@lists.postgresql.org>
List-Owner: <mailto:pgsql-hackers-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-hackers>
Precedence: bulk

This is a multi-part message in MIME format.
--------------20DC7BBD34247104D0B00618
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 7bit

Hi,

I just noticed that if you load a file using psql:

\copy <table> from <local file>

it sends every line as a separate FE/BE protocol CopyData packet. That's 
pretty wasteful if the lines are narrow. The overhead of each CopyData 
packet is 5 bytes.

To demonstrate, I generated a simple test file with the string "foobar" 
repeated 10 million times:

$ perl -le 'for (1..10000000) { print "foobar" }' > /tmp/testdata

and loaded that into a temp table with psql:

create temporary table copytest (t text) on commit delete rows;
\copy copytest from '/tmp/testdata';

I repeated and timed the \copy a few times; it takes about about 3 
seconds on my laptop:

postgres=# \copy copytest from '/tmp/testdata';
COPY 10000000
Time: 3039.625 ms (00:03.040)

Wireshark says that that involved about 120 MB of network traffic. The 
size of the file on disk is only 70 MB.

The attached patch modifies psql so that it buffers up 8 kB of data into 
each CopyData message, instead of sending one per line. That makes the 
operation faster:

postgres=# \copy copytest from '/tmp/testdata';
COPY 10000000
Time: 2490.268 ms (00:02.490)

And wireshark confirms that there's now only a bit over 70 MB of network 
traffic.

I'll add this to the next commitfest. There's similar inefficiency in 
the server side in COPY TO, but I'll leave that for another patch.

- Heikki

--------------20DC7BBD34247104D0B00618
Content-Type: text/x-patch; charset=UTF-8;
 name="0001-In-psql-copy-from-send-data-to-server-in-larger-chun.patch"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
 filename*0="0001-In-psql-copy-from-send-data-to-server-in-larger-chun.pa";
 filename*1="tch"

From e49334de4eb51f5ba061bf48d1979a7ab8ef0de9 Mon Sep 17 00:00:00 2001
From: Heikki Linnakangas <heikki.linnakangas@iki.fi>
Date: Sun, 7 Feb 2021 00:10:05 +0200
Subject: [PATCH 1/1] In psql \copy from, send data to server in larger chunks.

Previously, we would send each line as a separate CopyData message.
That's pretty wasteful if the table is narrow, as each CopyData message
has 5 bytes of overhead. For efficiency, buffer up and pack 8 kB of input
data into each CopyData message.
---
 src/bin/psql/copy.c | 114 +++++++++++++++++++++++++++-----------------
 1 file changed, 69 insertions(+), 45 deletions(-)

diff --git a/src/bin/psql/copy.c b/src/bin/psql/copy.c
index 78f0dc5a507..8c56f2470e2 100644
--- a/src/bin/psql/copy.c
+++ b/src/bin/psql/copy.c
@@ -581,77 +581,101 @@ handleCopyIn(PGconn *conn, FILE *copystream, bool isbinary, PGresult **res)
 	else
 	{
 		bool		copydone = false;
+		int			buflen;
+		bool		at_line_begin = true;
 
+		if (showprompt)
+		{
+			const char *prompt = get_prompt(PROMPT_COPY, NULL);
+
+			fputs(prompt, stdout);
+			fflush(stdout);
+		}
+
+		/*
+		 * In text mode, We have to read the input one line at a time, so that
+		 * we can stop reading at the EOF marker (\.).  We mustn't read beyond
+		 * the EOF marker, because if the data is inlined in a SQL script, we
+		 * would eat up the commands after the EOF marker.
+		 */
+		buflen = 0;
 		while (!copydone)
-		{						/* for each input line ... */
-			bool		firstload;
-			bool		linedone;
+		{
+			int			linelen;
+			char	   *fgresult;
 
-			if (showprompt)
+			if (at_line_begin && showprompt)
 			{
-				const char *prompt = get_prompt(PROMPT_COPY, NULL);
+				const char *prompt;
 
+				sigint_interrupt_enabled = false;
+
+				prompt = get_prompt(PROMPT_COPY, NULL);
 				fputs(prompt, stdout);
 				fflush(stdout);
-			}
 
-			firstload = true;
-			linedone = false;
-
-			while (!linedone)
-			{					/* for each bufferload in line ... */
-				int			linelen;
-				char	   *fgresult;
-
-				/* enable longjmp while waiting for input */
 				sigint_interrupt_enabled = true;
+			}
 
-				fgresult = fgets(buf, sizeof(buf), copystream);
-
-				sigint_interrupt_enabled = false;
+			/* enable longjmp while waiting for input */
+			sigint_interrupt_enabled = true;
 
-				if (!fgresult)
-				{
-					copydone = true;
-					break;
-				}
+			fgresult = fgets(&buf[buflen], COPYBUFSIZ - buflen, copystream);
 
-				linelen = strlen(buf);
+			sigint_interrupt_enabled = false;
 
-				/* current line is done? */
-				if (linelen > 0 && buf[linelen - 1] == '\n')
-					linedone = true;
+			if (!fgresult)
+				copydone = true;
+			else
+			{
+				linelen = strlen(fgresult);
+				buflen += linelen;
 
-				/* check for EOF marker, but not on a partial line */
-				if (firstload)
+				if (buf[buflen - 1] == '\n')
 				{
-					/*
-					 * This code erroneously assumes '\.' on a line alone
-					 * inside a quoted CSV string terminates the \copy.
-					 * https://www.postgresql.org/message-id/E1TdNVQ-0001ju-GO@wrigleys.postgresql.org
-					 */
-					if (strcmp(buf, "\\.\n") == 0 ||
-						strcmp(buf, "\\.\r\n") == 0)
+					/* check for EOF marker, but not on a partial line */
+					if (at_line_begin)
 					{
-						copydone = true;
-						break;
+						/*
+						 * This code erroneously assumes '\.' on a line alone
+						 * inside a quoted CSV string terminates the \copy.
+						 * https://www.postgresql.org/message-id/E1TdNVQ-0001ju-GO@wrigleys.postgresql.org
+						 */
+						if ((linelen == 3 && memcmp(fgresult, "\\.\n", 3) == 0) ||
+							(linelen == 4 && memcmp(fgresult, "\\.\r\n", 4) == 0))
+						{
+							copydone = true;
+						}
 					}
 
-					firstload = false;
+					if (copystream == pset.cur_cmd_source)
+					{
+						pset.lineno++;
+						pset.stmt_lineno++;
+					}
+					at_line_begin = true;
 				}
+				else
+					at_line_begin = false;
+			}
 
-				if (PQputCopyData(conn, buf, linelen) <= 0)
+			/*
+			 * If the buffer is full, or we've reached the EOF, flush it.
+			 *
+			 * Make sure there's always space for four more bytes in the buffer,
+			 * plus a NUL terminator. That way, an EOF marker is never split
+			 * across two fgets() calls, which simplies the logic.
+			 */
+			if (buflen >= COPYBUFSIZ - 5 || (copydone && buflen > 0))
+			{
+				if (PQputCopyData(conn, buf, buflen) <= 0)
 				{
 					OK = false;
 					copydone = true;
 					break;
 				}
-			}
 
-			if (copystream == pset.cur_cmd_source)
-			{
-				pset.lineno++;
-				pset.stmt_lineno++;
+				buflen = 0;
 			}
 		}
 	}
-- 
2.30.0


--------------20DC7BBD34247104D0B00618--