From: | Andy Colson <andy(at)squeakycode(dot)net> |
---|---|
To: | Johannes <jotpe(at)posteo(dot)de>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: long transfer time for binary data |
Date: | 2016-01-22 01:53:37 |
Message-ID: | 56A18BA1.90300@squeakycode.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
> 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 different speeds? ssl always adds extra time? Maybe a high latency thing? If you ping the other box what sort of time's do you get? Maybe the extra ssl handshakes up front + high latency is causing it. You could try a shared/cached ssh connection 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 wont get faster than 3 seconds. Is that enough?
-Andy
From | Date | Subject | |
---|---|---|---|
Next Message | David E. Wheeler | 2016-01-22 05:25:10 | Let's Do the CoC Right |
Previous Message | Michael Paquier | 2016-01-22 00:30:36 | Re: Building PostgreSQL 9.6devel sources with Microsoft Visual C++ 2015? |