Reply-To: <guoping.zhang@nec.com.au>
From: "Guoping Zhang" <guoping.zhang@nec.com.au>
To: "'Florian Weimer'" <fweimer@bfk.de>
Cc: <pgsql-performance@postgresql.org>
Subject: Re: Performance penalty for remote access of postgresql (8.1.3)? any
	experiance?
Date: Wed, 19 Jul 2006 17:33:34 +1000
Message-ID: <003501c6ab05$a4978f20$74304c93@eddy>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit
In-Reply-To: <82irlu2jk9.fsf@mid.bfk.de>
Importance: Normal

Hi, Florian

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?

Regards,
Guoping Zhang


-----Original Message-----
From: Florian Weimer [mailto:fweimer@bfk.de]
Sent: 2006Ae7OA19EO 16:30
To: guoping.zhang@nec.com.au
Cc: pgsql-performance@postgresql.org; Guoping Zhang (E-mail)
Subject: Re: [PERFORM] Performance penalty for remote access of
postgresql (8.1.3)? any experiance?


* Guoping Zhang:

>   a) Anyone has the similar experience? How do you deal with it?
>   b) Why TCP stack imposes such big delay? any tuning point I shall do?

If you use INSERT, you'll incur a network round-trip delay for each
record.  Try using COPY FROM instead, possibly to a temporary table if
you need more complex calculations.  If you do this, there won't be a
huge difference between local and remote access as long as the
bandwidth is sufficient.

--
Florian Weimer                <fweimer@bfk.de>
BFK edv-consulting GmbH       http://www.bfk.de/
Durlacher Allee 47            tel: +49-721-96201-1
D-76131 Karlsruhe             fax: +49-721-96201-99