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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
