Re: SSL encryption makes bytea transfer slow

On 28.10.2011 14:02, Albe Laurenz wrote:
> We selected a 30MB bytea with psql connected with
> "-h localhost" and found that it makes a huge
> difference whether we have SSL encryption on or off.
>
> Without SSL the SELECT finished in about a second,
> with SSL it took over 23 seconds (measured with
> \timing in psql).
> During that time, the CPU is 100% busy.
> All data are cached in memory.
>
> Is this difference as expected?

I tried to reproduce that, but only saw about 4x difference in the
timing, not 23x.

$ PGSSLMODE=disable ~/pgsql.master/bin/psql -h localhost postgres
psql (9.2devel)
Type "help" for help.

postgres=# \o foo
postgres=# \timing
Timing is on.
postgres=# SELECT repeat(xx,65536)::bytea FROM (SELECT
string_agg(lpad(to_hex(x),2, '0' ),'') AS xx FROM generate_series(0,255)
x) AS xx;
Time: 460,782 ms

$ PGSSLMODE=require ~/pgsql.master/bin/psql -h localhost postgres
psql (9.2devel)
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
Type "help" for help.

postgres=# \o foo
postgres=# \timing
Timing is on.
postgres=# SELECT repeat(xx,65536)::bytea FROM (SELECT
string_agg(lpad(to_hex(x),2, '0' ),'') AS xx FROM generate_series(0,255)
x) AS xx;
Time: 1874,276 ms


oprofile suggests that all that overhead is coming from compression.
Apparently SSL does compression automatically. Oprofile report of the
above test case with SSL enabled:

samples  %        image name               symbol name
28177    74.4753  libz.so.1.2.3.4          /usr/lib/libz.so.1.2.3.4
1814      4.7946  postgres                 byteain
1459      3.8563  libc-2.13.so             __memcpy_ssse3_back
1437      3.7982  libcrypto.so.0.9.8       /usr/lib/libcrypto.so.0.9.8
896       2.3682  postgres                 hex_encode
304       0.8035  vmlinux-3.0.0-1-amd64    clear_page_c
271       0.7163  libc-2.13.so             __strlen_sse42
222       0.5868  libssl.so.0.9.8          /usr/lib/libssl.so.0.9.8

And without:

samples  %        image name               symbol name
1601     27.4144  postgres                 byteain
865      14.8116  postgres                 hex_encode
835      14.2979  libc-2.13.so             __memcpy_ssse3_back
290       4.9658  vmlinux-3.0.0-1-amd64    clear_page_c
280       4.7945  libc-2.13.so             __strlen_sse42
184       3.1507  vmlinux-3.0.0-1-amd64    page_fault
174       2.9795  vmlinux-3.0.0-1-amd64    put_mems_allowed


Maybe your data is very expensive to compress for some reason?

--
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com