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