compressed protocol

Is there any plans (or somebody already working on) to see compressed
protocol in 8.2 ?

zac

zeljko <zeljko(at)holobit(dot)net> writes:
> Is there any plans (or somebody already working on) to see compressed
> protocol in 8.2 ?

Why bother? Run your session through an ssh tunnel and ask it to
compress.

regards, tom lane

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Tom Lane wrote:
> zeljko <zeljko(at)holobit(dot)net> writes:
>> Is there any plans (or somebody already working on) to see compressed
>> protocol in 8.2 ?
>
> Why bother? Run your session through an ssh tunnel and ask it to
> compress.

Wouldn't that add extra libssl overhead?

How difficult would it be to add a zlib(?) hook? (No, really. I'm
*not* assuming that it would be trivial.)

- --
Ron Johnson, Jr.
Jefferson LA USA

Is "common sense" really valid?
For example, it is "common sense" to white-power racists that
whites are superior to blacks, and that those with brown skins
are mud people.
However, that "common sense" is obviously wrong.
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.5 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFE+3XVS9HxQb37XmcRAjG8AKCJ48Gfz8vmTIZZ11PyFfMrIjqFaQCeO+Xc
YToke0+IeaEz0g+5qnHupGM=
=hJ7K
-----END PGP SIGNATURE-----

Ron Johnson <ron(dot)l(dot)johnson(at)cox(dot)net> writes:
> Tom Lane wrote:
>> zeljko <zeljko(at)holobit(dot)net> writes:
>>> Is there any plans (or somebody already working on) to see compressed
>>> protocol in 8.2 ?
>>
>> Why bother? Run your session through an ssh tunnel and ask it to
>> compress.

> Wouldn't that add extra libssl overhead?

Well, (a) if you're sending your data over a nonlocal network (and over
a LAN, you don't need compression), you probably want some encryption.
(b) I believe there's a "null cipher" mode for SSL if you truly don't
need encryption.

regards, tom lane

Tom Lane wrote:

> zeljko <zeljko(at)holobit(dot)net> writes:
>> Is there any plans (or somebody already working on) to see compressed
>> protocol in 8.2 ?
>
> Why bother? Run your session through an ssh tunnel and ask it to
> compress.

I saw it on TODO list, not marked for 8.2 but it's there.

On Sep 3, 2006, at 8:46 PM, Tom Lane wrote:

> (b) I believe there's a "null cipher" mode for SSL if you truly don't
> need encryption.

Nothing such is obviously documented for SSHv2, which is unfortunate
for some uses.

Tom Lane wrote:

> zeljko <zeljko(at)holobit(dot)net> writes:
>> Is there any plans (or somebody already working on) to see compressed
>> protocol in 8.2 ?
>
> Why bother? Run your session through an ssh tunnel and ask it to
> compress.

hm... tunneling works as expected, but there's no compression ?!?
ssh_config contains Compression yes
sshd_config (server) contains Compression yes

any ?

zeljko wrote:
> Tom Lane wrote:
>
> > zeljko <zeljko(at)holobit(dot)net> writes:
> >> Is there any plans (or somebody already working on) to see compressed
> >> protocol in 8.2 ?
> >
> > Why bother? Run your session through an ssh tunnel and ask it to
> > compress.
>
> hm... tunneling works as expected, but there's no compression ?!?
> ssh_config contains Compression yes
> sshd_config (server) contains Compression yes

Huh, how do you _know_ there's no compression?

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

On Fri, Sep 15, 2006 at 03:38:01PM +0200, zeljko wrote:
> Tom Lane wrote:
> > zeljko <zeljko(at)holobit(dot)net> writes:
> >> Is there any plans (or somebody already working on) to see compressed
> >> protocol in 8.2 ?
> >
> > Why bother? Run your session through an ssh tunnel and ask it to
> > compress.
>
> hm... tunneling works as expected, but there's no compression ?!?
> ssh_config contains Compression yes
> sshd_config (server) contains Compression yes

How did you determine that there's no compression? If you run "ssh -v"
do you see a line like "Enabling compression at level X"?

--
Michael Fuhr

Michael Fuhr wrote:

> On Fri, Sep 15, 2006 at 03:38:01PM +0200, zeljko wrote:
>> Tom Lane wrote:
>> > zeljko <zeljko(at)holobit(dot)net> writes:
>> >> Is there any plans (or somebody already working on) to see compressed
>> >> protocol in 8.2 ?
>> >
>> > Why bother? Run your session through an ssh tunnel and ask it to
>> > compress.
>>
>> hm... tunneling works as expected, but there's no compression ?!?
>> ssh_config contains Compression yes
>> sshd_config (server) contains Compression yes
>
> How did you determine that there's no compression? If you run "ssh -v"
> do you see a line like "Enabling compression at level X"?

I've connected to server with (tunnel created):

ssh -v -L 5400:myserver.ath.cx:5432 kasa(at)myserver(dot)ath(dot)cx
password: *******

Now I'm logged in (there are debug1 messages which says that ssh uses zlib
compression level 6).

Now, I'm sending command
ls -al /usr/bin
it returns in cca. 3 seconds.

logout.

Now ssh to server WITHOUT compression
same command, returns in > 6 seconds.

so, conclusion is that COMPRESSION works fine.

But, when I try (via tunnel, explained above)
psql -p 5400 -h localhost mydatabase
it connects and works fine, but there's no compression.
Query returns in cca 20 seconds, almost same (maybe 0.5 sec. different) as
normal psql connection.Conclusion is that there's no compression of psql
stream. Returned data is varchars and integers.

Any ideas ?

On Fri, Sep 15, 2006 at 05:37:50PM +0200, zeljko wrote:
> But, when I try (via tunnel, explained above)
> psql -p 5400 -h localhost mydatabase
> it connects and works fine, but there's no compression.
> Query returns in cca 20 seconds, almost same (maybe 0.5 sec. different) as
> normal psql connection.Conclusion is that there's no compression of psql
> stream. Returned data is varchars and integers.

That's a tenuous conclusion; it assumes that the data transfer is
what's taking all the time. Query planning and execution and
client-side processing must also be taken into account. Using a
sniffer to observe the amount of data transferred would be a more
appropriate test.

--
Michael Fuhr

On Fri, Sep 15, 2006 at 09:52:04AM -0600, Michael Fuhr wrote:
> On Fri, Sep 15, 2006 at 05:37:50PM +0200, zeljko wrote:
> > But, when I try (via tunnel, explained above)
> > psql -p 5400 -h localhost mydatabase
> > it connects and works fine, but there's no compression.
> > Query returns in cca 20 seconds, almost same (maybe 0.5 sec. different) as
> > normal psql connection.Conclusion is that there's no compression of psql
> > stream. Returned data is varchars and integers.
>
> That's a tenuous conclusion; it assumes that the data transfer is
> what's taking all the time. Query planning and execution and
> client-side processing must also be taken into account. Using a
> sniffer to observe the amount of data transferred would be a more
> appropriate test.

Also, don't discount the amount of time that compressing and
decompressing takes. The ls and psql tests aren't necessarily
comparable due to differing amounts and characteristics of data.

I just ran some tests between a couple of boxes on a local network,
using psql over a tunneled ssh connection as you are. A sniffer
showed that a compressed connection transferred 54% of the amount
of data as an uncompressed connection but it took 69% longer to do
so. If the network is fast and the boxes are slow then a compressed
connection can be a net loser.

--
Michael Fuhr

Michael Fuhr wrote:

> That's a tenuous conclusion; it assumes that the data transfer is

Are you trying to say that data won't be compressed ?

> what's taking all the time. Query planning and execution and
> client-side processing must also be taken into account. Using a
> sniffer to observe the amount of data transferred would be a more
> appropriate test.
>

From client connection:
EXPLAIN SELECT sfi,nvi,jmi FROM articles ORDER BY sif;
QUERY PLAN
------------------------------------------------------------------------------------
Index Scan using articles_pkey on articles (cost=0.00..1031.61
rows=12410 width=42)
(1 row)

So it returns 12410 rows :
sfi = INTEGER, nvi = varchar(50), jmi = varchar(3).

Yes, I know that my conclusions are tenuous, but what to say when
both tests returns result in approx. amount of time
(compressed or not).

Using compression on my nTier (kbmMW (ZLIB) ) same query returns
in 9 seconds (!=20 secs !). (same client, same server , same query).

cheers,
zac

Michael Fuhr wrote:

> On Fri, Sep 15, 2006 at 09:52:04AM -0600, Michael Fuhr wrote:
>> On Fri, Sep 15, 2006 at 05:37:50PM +0200, zeljko wrote:
>> > But, when I try (via tunnel, explained above)
>> > psql -p 5400 -h localhost mydatabase
>> > it connects and works fine, but there's no compression.
>> > Query returns in cca 20 seconds, almost same (maybe 0.5 sec. different)
>> > as normal psql connection.Conclusion is that there's no compression of
>> > psql stream. Returned data is varchars and integers.
>>
>> That's a tenuous conclusion; it assumes that the data transfer is
>> what's taking all the time. Query planning and execution and
>> client-side processing must also be taken into account. Using a
>> sniffer to observe the amount of data transferred would be a more
>> appropriate test.
>
> Also, don't discount the amount of time that compressing and
> decompressing takes. The ls and psql tests aren't necessarily
> comparable due to differing amounts and characteristics of data.
>
> I just ran some tests between a couple of boxes on a local network,
> using psql over a tunneled ssh connection as you are. A sniffer
> showed that a compressed connection transferred 54% of the amount
> of data as an uncompressed connection but it took 69% longer to do
> so. If the network is fast and the boxes are slow then a compressed
> connection can be a net loser.
>

I'm testing over DSL line (test server have 256k Upload and 1MB download).
Results are based on this connection.
Server : PIV 3Ghz HT, 1GB RAM. (DSL D 1MB U 256k)
Client : PIV 3Ghz HT, 1GB RAM. (DSL D 3MB U 384k).

nTier results shows real compression (faster more than twice).

On Fri, Sep 15, 2006 at 06:34:48PM +0200, zeljko wrote:
> I'm testing over DSL line (test server have 256k Upload and 1MB download).
> Results are based on this connection.
> Server : PIV 3Ghz HT, 1GB RAM. (DSL D 1MB U 256k)
> Client : PIV 3Ghz HT, 1GB RAM. (DSL D 3MB U 384k).
>
> nTier results shows real compression (faster more than twice).

What does a sniffer like tcpdump or wireshark/ethereal show? Let's
see how much data is being sent over a compressed vs. uncompressed
connection and how long the data transfers are taking.

--
Michael Fuhr

Michael Fuhr wrote:

> On Fri, Sep 15, 2006 at 06:34:48PM +0200, zeljko wrote:
>> I'm testing over DSL line (test server have 256k Upload and 1MB
>> download). Results are based on this connection.
>> Server : PIV 3Ghz HT, 1GB RAM. (DSL D 1MB U 256k)
>> Client : PIV 3Ghz HT, 1GB RAM. (DSL D 3MB U 384k).
>>
>> nTier results shows real compression (faster more than twice).
>
> What does a sniffer like tcpdump or wireshark/ethereal show? Let's
> see how much data is being sent over a compressed vs. uncompressed
> connection and how long the data transfers are taking.
>

I've tried sniffing with ethereal , but there's too much data, this is what
ssh with -v command parameter says. (amount of incoming data and
compression factor). It shows minor compression with query data.

ls -al /usr/lib
This is ssh log (compression enabled):
debug1: compress incoming: raw data 135639, compressed 20931, factor 0.15

Same query as in my previous posts.

This is ssh log (compression enabled):
debug1: compress incoming: raw data 682162, compressed 680894, factor 1.00

Query which returns integer field only:
debug1: compress incoming: raw data 193514, compressed 192030, factor 0.99

Query which returns varchar(3) only:
debug1: compress incoming: raw data 178999, compressed 177505, factor 0.99

Michael Fuhr wrote:

> What does a sniffer like tcpdump or wireshark/ethereal show? Let's
> see how much data is being sent over a compressed vs. uncompressed
> connection and how long the data transfers are taking.

ARGHHHHH.... IT WORKS ! :)))))
postgresql.conf contained ssl=on , so ssl=off = speed of light via ssh
tunnel :)

thanks for your time :)

zac

On Fri, Sep 15, 2006 at 07:58:54PM +0200, zeljko wrote:
> ARGHHHHH.... IT WORKS ! :)))))
> postgresql.conf contained ssl=on , so ssl=off = speed of light via ssh
> tunnel :)

So you were doing SSL over an SSH tunnel. That explains why
compression didn't seem to be working -- SSH was trying to compress
essentially random data (the SSL-encrypted data), and random data
doesn't have enough redundancy to compress.

--
Michael Fuhr

Michael Fuhr wrote:

> On Fri, Sep 15, 2006 at 07:58:54PM +0200, zeljko wrote:
> So you were doing SSL over an SSH tunnel. That explains why
> compression didn't seem to be working -- SSH was trying to compress
> essentially random data (the SSL-encrypted data), and random data
> doesn't have enough redundancy to compress.
>

yep ;)