Date: Wed, 19 Jul 2006 10:41:49 -0400
From: Bill Moran <wmoran@collaborativefusion.com>
To: guoping.zhang@nec.com.au
Cc: pgsql-performance@postgresql.org
Subject: Re: Performance penalty for remote access of postgresql
Message-Id: <20060719104149.784d00e1.wmoran@collaborativefusion.com>
In-Reply-To: <003501c6ab05$a4978f20$74304c93@eddy>
References: <82irlu2jk9.fsf@mid.bfk.de> <003501c6ab05$a4978f20$74304c93@eddy>
Organization: Collaborative Fusion
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

In response to "Guoping Zhang" <guoping.zhang@nec.com.au>:
> 
> Thanks for pointing me the cause, but we simply cannot use the COPY FROM
> solution.
> 
> Currently, our application service is running with its own dedicated local
> database, IF Feasible, we want to separate the application services out of
> database server and run SEVERAL instances of applation serivice on its own
> server (one per server), and make them all shall one database server. This
> helps to the scalability and also reduce the device cost as only database
> server would need mirror/backup/UPS etc.
> 
> Obviously, if there is no better solution, the TCP round trip penalty will
> stop us doing so as we do have performance requirement.
> 
> I guess there shall be quite number of people out there facing the similar
> problem, right? No alternative solution?

I suppose I'm a little confused on two points:
1) What did you expect.
2) What is your network?

On #1: networking adds overhead.  Period.  Always.  I believe you earlier
said you estimated around %20 perf hit.  For small transactions, I wouldn't
expect much better.  TCP adds a good bit of header to each packet, plus
the time in the kernel, and the RTT.  20% sounds about average to me.

#2 falls into a number of different categories.  For example:
a) What is your topology?  If you absolutely need blazing speed, you should
   have a dedicated gigabit switched network between the machines.
b) Not all network hardware is created equal.  Cheap switches seldom
   perform at their advertised speed.  Stick with high-end stuff.  NICs
   are the same way.
On #2, you'll want to ensure that the problem is not in the hardware before
you start complaining about PostgreSQL, or even TCP.  If you've got a cheap,
laggy switch, not amount of TCP or PostgreSQL tuning is going to overcome
it.

Hope some of this is helpful.

-- 
Bill Moran
Collaborative Fusion Inc.