Re: Flushing large data immediately in pqcomm - Mailing list pgsql-hackers

On 20/11/2023 14:21, Melih Mutlu wrote:
> Hi hackers
> 
> I've been looking into ways to reduce the overhead we're having in 
> pqcomm and I'd like to propose a small patch to modify how 
> socket_putmessage works.
> 
> Currently socket_putmessage copies any input data into the pqcomm send 
> buffer (PqSendBuffer) and the size of this buffer is 8K. When the send 
> buffer gets full, it's flushed and we continue to copy more data into 
> the send buffer until we have no data left to be sent.
> Since the send buffer is flushed whenever it's full, I think we are safe 
> to say that if the size of input data is larger than the buffer size, 
> which is 8K, then the buffer will be flushed at least once (or more, 
> depends on the input size) to store and all the input data.

Agreed, that's silly.

> Proposed change modifies socket_putmessage to send any data larger than 
> 8K immediately without copying it into the send buffer. Assuming that 
> the send buffer would be flushed anyway due to reaching its limit, the 
> patch just gets rid of the copy part which seems unnecessary and sends 
> data without waiting.

If there's already some data in PqSendBuffer, I wonder if it would be 
better to fill it up with data, flush it, and then send the rest of the 
data directly. Instead of flushing the partial data first. I'm afraid 
that you'll make a tiny call to secure_write(), followed by a large one, 
then a tine one again, and so forth. Especially when socket_putmessage 
itself writes the msgtype and len, which are tiny, before the payload.

Perhaps we should invent a new pq_putmessage() function that would take 
an input buffer with 5 bytes of space reserved before the payload. 
pq_putmessage() could then fill in the msgtype and len bytes in the 
input buffer and send that directly. (Not wedded to that particular API, 
but something that would have the same effect)

> This change affects places where pq_putmessage is used such as 
> pg_basebackup, COPY TO, walsender etc.
> 
> I did some experiments to see how the patch performs.
> Firstly, I loaded ~5GB data into a table [1], then ran "COPY test TO 
> STDOUT". Here are perf results of both the patch and HEAD > ...
> The patch brings a ~5% gain in socket_putmessage.
> 
> [1]
> CREATE TABLE test(id int, name text, time TIMESTAMP);
> INSERT INTO test (id, name, time) SELECT i AS id, repeat('dummy', 100) 
> AS name, NOW() AS time FROM generate_series(1, 100000000) AS i;

I'm surprised by these results, because each row in that table is < 600 
bytes. PqSendBufferSize is 8kB, so the optimization shouldn't kick in in 
that test. Am I missing something?

-- 
Heikki Linnakangas
Neon (https://neon.tech)