I ran into a performance problem described in this thread:
http://archives.postgresql.org/pgsql-performance/2011-10/msg00249.php
continued here:
http://archives.postgresql.org/pgsql-performance/2011-11/msg00045.php
OpenSSL compresses data by default, and that causes a
performance penalty of 100% and more, at least when
SELECTing larger bytea objects.
The backend process becomes CPU bound.
>From OpenSSL version 1.0.0. on, compression can be
disabled. The attached patch does that, and with that
patch I see dramatic performance improvements:
Unpatched:
samples % image name symbol name
6754 83.7861 libz.so.1.2.3 /lib64/libz.so.1.2.3
618 7.6665 libcrypto.so.1.0.0 /usr/lib64/libcrypto.so.1.0.0
534 6.6245 postgres hex_encode
95 1.1785 libc-2.12.so memcpy
Patched:
samples % image name symbol name
751 50.1670 libcrypto.so.1.0.0 /usr/lib64/libcrypto.so.1.0.0
594 39.6794 postgres hex_encode
83 5.5444 libc-2.12.so memcpy
(the test case is selecting one 27 MB bytea in text
mode over a localhost connection)
Are there any objections to this?
It is possible that this could cause a performance
regression for people who SELECT lots of compressible
data over really slow network connections, but is that
a realistic scenario?
If there are concerns about that, maybe a GUC variable like
ssl_compression (defaulting to off) would be a solution.
Yours,
Laurenz Albe
