Re: [PERFORM] PostgreSQL over internet
>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: Slow Fast As I said before I try a small query and big one the result same using IP Tunnel is fast. >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. when I said I connect to localhost I meant I connect to IP tunnel client witch connect me to the remote PGServer >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. yes I am sure, and if there is any it must affect both test. Best regards to all.
Re: [PERFORM] PostgreSQL over internet
> From: pgsql-performance-ow...@postgresql.org [mailto:pgsql-performance-ow...@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 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] PostgreSQL over internet
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 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] PostgreSQL over internet
>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 >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)
Re: [PERFORM] PostgreSQL over internet
On 27/01/13 02:45, k...@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 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] 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 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
