Message-ID: <4EABBE7D.9080201@enterprisedb.com>
Date: Sat, 29 Oct 2011 11:51:09 +0300
From: Heikki Linnakangas <heikki.linnakangas@enterprisedb.com>
User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; fi-FI;
	rv:1.9.2.21) Gecko/20110831 Icedove/3.1.13
MIME-Version: 1.0
To: Albe Laurenz <laurenz.albe@wien.gv.at>
CC: pgsql-performance@postgresql.org
Subject: Re: SSL encryption makes bytea transfer slow
References: 
 <D960CB61B694CF459DCFB4B0128514C2070C3E84@exadv11.host.magwien.gv.at>
In-Reply-To: 
 <D960CB61B694CF459DCFB4B0128514C2070C3E84@exadv11.host.magwien.gv.at>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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