Обсуждение: PostgreSQL over internet

On Sun, Jan 27, 2013 at 03:15:45AM +0300, belal hamed wrote:
>
> I connect to my server through ADSL connection 4Mbps
>

Here is your "problem". You need to understand the performance
characteristics of your communication channel. ADSL is a VERY
asymmetric communications channel. Down is usually much faster
than up.

> I try this query
>
> select "MatID", "MatName", "MatCode"
> from "Materials"
> where "MatCode" ~* '^1101'
> order by "MatCode"
> limit 2
>
> by wireshark I monitor TCP packets I found total data transmit/received 400B
> I took about 2.5s to fetch results  why ??????
>
> after trying every solution mentioned in previous messages (DNS, tcpip,
> postgresql.conf, ...) not found any improve,
>
> I tried this one:
>
> using Zebedee(http://www.winton.org.uk/zebedee/)
> I build an IP tunnel between me and my data server (I used compression
> level 9)
>
> surprisingly same query now took about 600 ms, "very impressive"
>
> same thing with this query
> select "MatID", "MatName", "MatCode", "MatParent" from "Materials"
> from 48s down to  17s
>
> all these tests done on same connection with same devices so same dns,
> tcp-ip, ....
>
> now I am sure there is something wrong with libpq.

When you wrap the communication channel in an IP tunnel, you are
collapsing much of the syn-ack of the libpq protocol. You can see
the same effect trying to run any sort of X windows application.

Regards,
Ken

On 27/01/13 02:45, ktm@rice.edu wrote:
> On Sun, Jan 27, 2013 at 03:15:45AM +0300, belal hamed wrote:
>>
>> I connect to my server through ADSL connection 4Mbps
>>
>
> Here is your "problem". You need to understand the performance
> characteristics of your communication channel. ADSL is a VERY
> asymmetric communications channel. Down is usually much faster
> than up.

I'm not convinced that ADSL is your problem.

1. Try just SSH directly to the server, and run psql, and run a query
like this one:
    SELECT 'This is a test message' AS status;

This  should run in under 1ms; it also means that we don't have to worry
about the details of your database-schema for the purposes of this problem.

2. Try creating a simple SSH tunnel and using your application locally.
For example, if your server runs Postgresql on port 5432, run this SSH
command:
   ssh -L 5432:localhost:5432 your_server_hostname
and then connect to your LOCAL (localhost) port 5432; SSH will handle
the port forwarding.  [Explanation: "localhost" in the SSH command is in
the context of your_server_hostname]
How does it work now?

3. Try configuration you are currently using, but with the above query.

It should be possible to distinguish between:
   - slowness caused by the database query itself
   - slowness caused by the network fundamentally.
   - slowness caused by the postgresql/libpq.

Hopefully, you'll be able to narrow it down a bit.

HTH,

Richard

On Sun, Jan 27, 2013 at 03:09:55PM +0300, belal hamed wrote:
> >Here is your "problem". You need to understand the performance
> >characteristics of your communication channel. ADSL is a VERY
> >asymmetric communications channel. Down is usually much faster
> >than up.
>
> How it could be ADSL problem when it's the same in tow tests ?
> beside data transferred when using tunnel is much bigger (about 10KB)  than
> direct connection (400B)
> so it should be slower when using tunnel but the result shows it was faster
> !!!!
>

Due to the asymmetric communication, a bigger data output in a single
packet (the result of using compression on the tunnel) will get sent
without waiting. A smaller packet will delay a bit waiting for some
additional data, which in your case does not come. You may want to
check out this document describing some of what I believe is causing
your observed behavior:

http://www.faqs.org/docs/Linux-HOWTO/ADSL-Bandwidth-Management-HOWTO.html#BACKGROUND

> >When you wrap the communication channel in an IP tunnel, you are
> >collapsing much of the syn-ack of the libpq protocol. You can see
> >the same effect trying to run any sort of X windows application.
>
> If that so, why these is not same option in Postgresql, Is it necessary to
> use IP tunnel to do that and perform fast fetch?
>
>
> >Try creating a simple SSH tunnel
> my server is windows 7
>
> >It should be possible to distinguish between:
> >  - slowness caused by the database query itself
> >  - slowness caused by the network fundamentally.
> >  - slowness caused by the postgresql/libpq.
>
> I run the same query on same network connection so I eliminate the
> slowness caused by the database query and network fundamentally,
> nothing left but postgresql/libpq
>
> not anyone consider there may be a bug when connection to a remote server
> over internet in libpq
> the only different when I used the tunnel is I connect to localhost
> instead  of server IP or domain name (I try both)

You would find that if you log in to your DB server and use libpq
to it over a localhost connection that the performance is good which
points to your network as the problem.

Regards,
Ken

> From: pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org] On Behalf Of belal hamed
> Sent: 27 January 2013 13:16
> To: pgsql-performance@postgresql.org
> Subject: [PERFORM] PostgreSQL over internet


> by wireshark I monitor TCP packets I found total data transmit/received
400B
> I took about 2.5s to fetch results  why ??????


Are you sure there's not any QOS somewhere that is slowing down the packets
for port 5432 or whichever you're using for PostgreSQL?
Perhaps temporarily changing PostgreSQL's listening port to something else
might be a good test.

David