Date: Sat, 26 Jan 2013 20:45:05 -0600
From: "ktm@rice.edu" <ktm@rice.edu>
To: belal hamed <belalhamed@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: PostgreSQL over internet
Message-ID: <20130127024505.GA12423@aart.rice.edu>
References: 
 <CAHsqH1zQUj5po-1eHK7j_z7k+VdHWNWBK7XfbCxuRskkAj5LSQ@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: 
 <CAHsqH1zQUj5po-1eHK7j_z7k+VdHWNWBK7XfbCxuRskkAj5LSQ@mail.gmail.com>
User-Agent: Mutt/1.5.20 (2009-12-10)
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

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