Re: long transfer time for binary data

Am 23.01.2016 um 01:25 schrieb Daniel Verite:
> Johannes wrote:
>
>> psql
>> select lo_get(12345);
>> +ssl -compression 6.0 sec
>> -ssl 4.4 sec
>
> psql requests results in text format so that SELECT does not
> really test the transfer of binary data.
> With bytea_output to 'hex', contents are inflated by 2x.
>
> Can you tell how fast this goes for you, as a comparison point:
> \lo_export 12345 /dev/null
> ?
>
> Many client interfaces use the text format, but you want to
> avoid that if possible with large bytea contents.
> In addition to putting twice the data on the wire, the server has to
> convert the bytes to hex and the client has to do the reverse operation,
> a complete waste of CPU time on both ends.
>
> At the SQL level, the DECLARE name BINARY CURSOR FOR query
> can help to force results in binary, but as the doc says:
>
> http://www.postgresql.org/docs/current/static/sql-declare.html
>
> "Binary cursors should be used carefully. Many applications, including
> psql, are not prepared to handle binary cursors and expect data to
> come back in the text format."
>
> Personally I don't have experience with JDBC, but looking at the doc:
> https://jdbc.postgresql.org/documentation/94/binary-data.html
>
> I see this:
>
> "To use the Large Object functionality you can use either the
> LargeObject class provided by the PostgreSQL™ JDBC driver, or by using
> the getBLOB() and setBLOB() methods."
>
> If the data lives on the server as large objects, I would think that
> this LargeObject class has the best potential for retrieving them
> efficiently, as opposed to "SELECT lo_get(oid)" which looks like
> it could trigger the undesirable round-trip to the text format.
> You may want to test that or bring it up as a question to JDBC folks.
>
>
> Best regards,

\lo_export 12345 /dev/null is completed in 0.86 seconds.

I save my images as large object, which afaik is in practise not
readable with a binary cursor (we should use the lo_* functions). And of
course I already use the LargeObjectManager of the postgresql jdbc library.

You said, the server has to convert the bytes to hex string before
sending it over the wire. In my understanding bytea values are stored as
strings and are may compressed in TOAST storage.
> The bytea data type allows storage of binary strings [1]
What is correct?

Your post gave me the hint. I found a binary transfer parameter in the
postgresql jdbc library available [2], [3].

But turning it on, doesn't speed anything up. It seems the binary
transfer mode is active by default. The byte counter (iptables -v) is
nearly as big as the image itself. It is already optimal.

packets byte counter
psql +ssl 8514 23M
psql -ssl 8179 23M
pgadmin -ssl 11716 33M
pgadmin +ssl -compress 12196 34M
pgadmin +ssl +compress 12193 34M
java jdbc +ssl 14037 24M
java jdbc -ssl 5622 12M (3.1 seconds)
java jdbc -ssl binarytransfer=true 5615 12M (3.1 seconds)

In fact I do not understand what is the bottleneck. OK my server, runs
in a Raspberry 2b+, thats maybe not the best hardware. But the scp
command could be finished from there in 1.3 seconds. So the bottleneck
is not the network speed. And also not the USB diskdrive. Maybe it is
the slow java program? I pointed my java program to my local postgresql
instance (with the same image as large object, same mtu, no loopback
device, no unix socket, but better system) it was finished in 400 ms.
The java progam is out too. Did I forget anything?

I'm afraid I have to live with it and may use thumbnail images.

Best regards

[1] http://www.postgresql.org/docs/current/static/datatype-binary.html
[2] https://wiki.postgresql.org/wiki/JDBC-BinaryTransfer
[3]
https://jdbc.postgresql.org/documentation/94/connect.html#connection-parameters