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.