Hi hackers, I did some experiments with this patch, after previous discussions. This probably does not answer all questions, but would be happy to do more if needed.
First, I updated the patch according to what suggested here [1]. PSA v2. I tweaked the master branch a bit to not allow any buffering. I compared HEAD, this patch and no buffering at all. I also added a simple GUC to control PqSendBufferSize, this change only allows to modify the buffer size and should not have any impact on performance. I again ran the COPY TO STDOUT command and timed it. AFAIU COPY sends data row by row, and I tried running the command under different scenarios with different # of rows and row sizes. You can find the test script attached (see test.sh). All timings are in ms. 1- row size = 100 bytes, # of rows = 1000000 ┌───────────┬────────────┬──────┬──────┬──────┬──────┬──────┐ │ │ 1400 bytes │ 2KB │ 4KB │ 8KB │ 16KB │ 32KB │ ├───────────┼────────────┼──────┼──────┼──────┼──────┼──────┤ │ HEAD │ 1036 │ 998 │ 940 │ 910 │ 894 │ 874 │ ├───────────┼────────────┼──────┼──────┼──────┼──────┼──────┤ │ patch │ 1107 │ 1032 │ 980 │ 957 │ 917 │ 909 │ ├───────────┼────────────┼──────┼──────┼──────┼──────┼──────┤ │ no buffer │ 6230 │ 6125 │ 6282 │ 6279 │ 6255 │ 6221 │ └───────────┴────────────┴──────┴──────┴──────┴──────┴──────┘ 2- row size = half of the rows are 1KB and rest is 10KB , # of rows = 1000000 ┌───────────┬────────────┬───────┬───────┬───────┬───────┬───────┐ │ │ 1400 bytes │ 2KB │ 4KB │ 8KB │ 16KB │ 32KB │ ├───────────┼────────────┼───────┼───────┼───────┼───────┼───────┤ │ HEAD │ 25197 │ 23414 │ 20612 │ 19206 │ 18334 │ 18033 │ ├───────────┼────────────┼───────┼───────┼───────┼───────┼───────┤ │ patch │ 19843 │ 19889 │ 19798 │ 19129 │ 18578 │ 18260 │ ├───────────┼────────────┼───────┼───────┼───────┼───────┼───────┤ │ no buffer │ 23752 │ 23565 │ 23602 │ 23622 │ 23541 │ 23599 │ └───────────┴────────────┴───────┴───────┴───────┴───────┴───────┘ 3- row size = half of the rows are 1KB and rest is 1MB , # of rows = 1000 ┌───────────┬────────────┬──────┬──────┬──────┬──────┬──────┐ │ │ 1400 bytes │ 2KB │ 4KB │ 8KB │ 16KB │ 32KB │ ├───────────┼────────────┼──────┼──────┼──────┼──────┼──────┤ │ HEAD │ 3137 │ 2937 │ 2687 │ 2551 │ 2456 │ 2465 │ ├───────────┼────────────┼──────┼──────┼──────┼──────┼──────┤ │ patch │ 2399 │ 2390 │ 2402 │ 2415 │ 2417 │ 2422 │ ├───────────┼────────────┼──────┼──────┼──────┼──────┼──────┤ │ no buffer │ 2417 │ 2414 │ 2429 │ 2418 │ 2435 │ 2404 │ └───────────┴────────────┴──────┴──────┴──────┴──────┴──────┘ 3- row size = all rows are 1MB , # of rows = 1000 ┌───────────┬────────────┬──────┬──────┬──────┬──────┬──────┐ │ │ 1400 bytes │ 2KB │ 4KB │ 8KB │ 16KB │ 32KB │ ├───────────┼────────────┼──────┼──────┼──────┼──────┼──────┤ │ HEAD │ 6113 │ 5764 │ 5281 │ 5009 │ 4885 │ 4872 │ ├───────────┼────────────┼──────┼──────┼──────┼──────┼──────┤ │ patch │ 4759 │ 4754 │ 4754 │ 4758 │ 4782 │ 4805 │ ├───────────┼────────────┼──────┼──────┼──────┼──────┼──────┤ │ no buffer │ 4756 │ 4774 │ 4793 │ 4766 │ 4770 │ 4774 │ └───────────┴────────────┴──────┴──────┴──────┴──────┴──────┘ Some quick observations: 1- Even though I expect both the patch and HEAD behave similarly in case of small data (case 1: 100 bytes), the patch runs slightly slower than HEAD. 2- In cases where the data does not fit into the buffer, the patch starts performing better than HEAD. For example, in case 2, patch seems faster until the buffer size exceeds the data length. When the buffer size is set to something larger than 10KB (16KB/32KB in this case), there is again a slight performance loss with the patch as in case 1. 3- With large row sizes (i.e. sizes that do not fit into the buffer) not buffering at all starts performing better than HEAD. Similarly the patch performs better too as it stops buffering if data does not fit into the buffer. [1] https://www.postgresql.org/message-id/CAGECzQTYUhnC1bO%3DzNiSpUgCs%3DhCYxVHvLD2doXNx3My6ZAC2w%40mail.gmail.com Thanks, -- Melih Mutlu Microsoft
From 7f1b72a0948156f8e35ce3b07b5e763a5578d641 Mon Sep 17 00:00:00 2001
From: Melih Mutlu <m.melihmutlu@gmail.com>
Date: Mon, 26 Feb 2024 14:41:35 +0300
Subject: [PATCH] Added pq_send_buffer_size GUC
---
src/backend/libpq/pqcomm.c | 2 +-
src/backend/utils/misc/guc_tables.c | 11 +++++++++++
src/include/libpq/libpq.h | 1 +
3 files changed, 13 insertions(+), 1 deletion(-)
diff --git a/src/backend/libpq/pqcomm.c b/src/backend/libpq/pqcomm.c
index c606bf3447..92708e46e6 100644
--- a/src/backend/libpq/pqcomm.c
+++ b/src/backend/libpq/pqcomm.c
@@ -116,7 +116,7 @@ static List *sock_paths = NIL;
* enlarged by pq_putmessage_noblock() if the message doesn't fit otherwise.
*/
-#define PQ_SEND_BUFFER_SIZE 8192
+int PQ_SEND_BUFFER_SIZE = 8192;
#define PQ_RECV_BUFFER_SIZE 8192
static char *PqSendBuffer;
diff --git a/src/backend/utils/misc/guc_tables.c b/src/backend/utils/misc/guc_tables.c
index 527a2b2734..1f198b19ca 100644
--- a/src/backend/utils/misc/guc_tables.c
+++ b/src/backend/utils/misc/guc_tables.c
@@ -3595,6 +3595,17 @@ struct config_int ConfigureNamesInt[] =
NULL, NULL, NULL
},
+ {
+ {"pq_send_buffer_size", PGC_USERSET, DEVELOPER_OPTIONS,
+ gettext_noop("Sets the PqSendBufferSize"),
+ NULL,
+ GUC_NOT_IN_SAMPLE | GUC_UNIT_BYTE
+ },
+ &PQ_SEND_BUFFER_SIZE,
+ 8192, 0, MAX_KILOBYTES,
+ NULL, NULL, NULL
+ },
+
/* End-of-list marker */
{
{NULL, 0, 0, NULL, NULL}, NULL, 0, 0, 0, NULL, NULL, NULL
diff --git a/src/include/libpq/libpq.h b/src/include/libpq/libpq.h
index 6171a0d17a..37e91008fb 100644
--- a/src/include/libpq/libpq.h
+++ b/src/include/libpq/libpq.h
@@ -123,6 +123,7 @@ extern PGDLLIMPORT char *SSLECDHCurve;
extern PGDLLIMPORT bool SSLPreferServerCiphers;
extern PGDLLIMPORT int ssl_min_protocol_version;
extern PGDLLIMPORT int ssl_max_protocol_version;
+extern PGDLLIMPORT int PQ_SEND_BUFFER_SIZE;
enum ssl_protocol_versions
{
--
2.34.1
#!/bin/bash
DESTDIR="/home/melih-desktop/code/build/"
TESTDIR="/home/melih-desktop/pqcomm_test"
test_cases=(
"100 100 1000000" # only 100 bytes
"1024 10240 1000000" # 1Kb and 10Kb
"1024 1048576 1000" # 1Kb and 1Mb
"1048576 1048576 1000" # all 1Mb
)
branches=(
"master"
"pqcomm_buf"
"pqcomm_no_buffer"
)
buffer_sizes=(
1400
2048
4096
8192
16384
32768
)
insert_rows(){
psql -d postgres -p 5432 -c "
DO \$\$
DECLARE
counter INT;
BEGIN
FOR counter IN 1..$3 LOOP
IF counter % 2 = 1 THEN
INSERT INTO test_table VALUES (repeat('a', $1)::text);
ELSE
INSERT INTO test_table VALUES (repeat('b', $2)::text);
END IF;
END LOOP;
END \$\$;
"
}
for branch in "${branches[@]}"
do
git checkout $branch
rm -rf $DESTDIR
meson setup --buildtype debug -Dcassert=true --prefix="$DESTDIR/usr/local/pgsql" $DESTDIR && \
ninja -C $DESTDIR && \
meson install --quiet -C $DESTDIR
for case in "${test_cases[@]}"
do
pkill postgres
rm -rf $TESTDIR/test
initdb -D $TESTDIR/test
pg_ctl -D $TESTDIR/test -l $TESTDIR/logfile start
psql -d postgres -p 5432 -c "CREATE TABLE test_table(data text not null);"
insert_rows $case
for buf_size in "${buffer_sizes[@]}"
do
echo "pq_send_buffer_size = ${buf_size}" >> $TESTDIR/test/postgresql.conf
pg_ctl -D $TESTDIR/test -l $TESTDIR/logfile restart
elapsed_time=0
for a in {1..3}
do
echo "Run ${a}"
echo 3 | sudo tee /proc/sys/vm/drop_caches
pg_ctl -D $TESTDIR/test -l $TESTDIR/logfile restart
start_time=$(date +%s%N)
psql -d postgres -p 5432 -c "COPY test_table TO STDOUT;" 1> /dev/null
end_time=$(date +%s%N)
elapsed_time=$(( (end_time - start_time) + elapsed_time))
done
avg_elapsed_time_in_ms=$((elapsed_time / 3 / 1000000))
echo -e "${branch}-${case}-${buf_size} : ${avg_elapsed_time_in_ms}" >> $TESTDIR/results_tiny.txt
done
pg_ctl -D $TESTDIR/test -l $TESTDIR/logfile stop
done
done
v2-0001-Flush-large-data-immediately-in-pqcomm.patch
Description: Binary data
