Re: long transfer time for binary data

> Am 21.01.2016 um 03:33 schrieb Andy Colson:
>> On 01/20/2016 03:29 PM, Johannes wrote:
>>> I noticed transferring a large object or bytea data between client and
>>> server takes a long time.
>>> For example: An image with a real size of 11 MB could be read on server
>>> side (explain analyze) in 81ms. Fine.
>>>
>>> But on client side the result was completed after 6.7 seconds without
>>> ssl compression and 4.5 seconds with ssl compression (both via 100MBit
>>> ethernet).
>>>
>>> SSL compression seems to be not a good idea anymore, since this had
>>> become a security risk. Its still possible with pgadmin, but afaik not
>>> with java/jdbc .
>>>
>>> Are there any other solutions available to display my images in my
>>> client application more quickly? Or are there planned improvements to
>>> postgresql (transferring the real binary data)?
>>>
>>> Best regards
>>> Johannes
>>>
>>
>> Yep, that's slow.  The ssl compression is very odd if the image is
>> jpeg'ish and already compressed.  If its a bitmap or uncompressed tif
>> then its not so surprising.
>>
>> A few tests you could try:
>>
>> 1) copy the same 11 meg file from server to client via regular file copy
>> and time it.  At 100 Mbit/s it should take about a second.  If it takes
>> 6 you have network problems, not PG problems.
>>
>> 2) try it via psql command line (or at least something other than java),
>> to see if its java thats the problem.
>>
>> 3) watch wireshark/tcpdump, maybe you'll see something glaring that'll
>> point you in the right direction.
>>
>> -Andy
>>
>> PS: I've never heard that ssl compression was a security risk, got
>> links/proof?
>>
>>
>

On 01/21/2016 03:59 PM, Johannes wrote:
> Here are some transferring measurements (from server to client) with the
> same file.
>
> scp
> +ssl -compression 1.3 sec
> +ssl +compression 4.6 sec
>
> pgadmin
> select lo_get(12345);
> -ssl              3.4 sec
> +ssl +compression 5.5 sec
> +ssl -compression 4.5 sec
>
> psql
> select lo_get(12345);
> +ssl -compression 6.0 sec
> -ssl              4.4 sec
>
> java/jdbc
> only while(in.read(buf,0,len))
> +ssl -compression 6.0 sec
> -ssl              3.0 sec (+ 1.8 sec for new Image())
>
> Here is a link for insecure ssl compression:
> https://en.wikipedia.org/wiki/Comparison_of_TLS_implementations#Compression
>
> Best Regargs
> Johannes
>

Please don't top post.

Thanks for the link on ssl compression, I'd not seen that before.  I'm going to have to read up.

Your numbers ... look ...  odd.  scp +compression is slower?  pgadmin -ssl and psql -ssl and java -ssl are all
differentspeeds?  ssl always adds extra time?  Maybe a high latency thing?  If you ping the other box what sort of
time'sdo you get?  Maybe the extra ssl handshakes up front + high latency is causing it.  You could try a shared/cached
sshconnection to avoid the overhead. 

Best case though, your file copy was 1.3 seconds and with PG it was 3 seconds.  Even getting ssl fixed, you probably
wontget faster than 3 seconds.  Is that enough? 

-Andy