Re: Performance over a LAN - Mailing list pgsql-performance

I tested the LAN connection by transferring around some large (150 MByte)
files, and got consistent transfer rates of about 10 MBytes/second in both
directions without any problems, which is what I would expect. Netstat says
that there are no errors, so I think that the ethernet is working OK. Maybe
there's some latency somewhere but I have no reason to think that anything's
abnormal.

The test program is a C program with embedded SQL (ecpg). The only
difference between the tests was the address used in the EXEC SQL CONNECT
.. statement. The inserts are committed to the database by performing an
EXEC SQL COMMIT after every N of them; I tried various values of N up to
several hundred, but it didn't make much difference. Using psql I can see
records appearing in the database in groups of that size. I'm not sure about
all of the protocol versions. I downloaded the complete Postgres source and
built it only a few days ago. Ecpg says that it's version is 3.1.1. I'm not
getting any errors reported anywhere, it's just that things are surprisingly
slow over the LAN for some reason.

William


> -----Original Message-----
> From: pgsql-performance-owner@postgresql.org
> [mailto:pgsql-performance-owner@postgresql.org]On Behalf Of Mark
> Aufflick
> Sent: Friday, 23 July 2004 3:50 PM
> To: Christopher Kings-Lynne
> Cc: William Carney; pgsql-performance@postgresql.org
> Subject: Re: [PERFORM] Performance over a LAN
>
>
> I don't think that's the advice being looked for here - if this
> behaviour is repeatable, then there is something askew with the inet
> protocol.
>
> What is the client application written in? Do you know what version of
> the postgres network protocol your driver code is using? Are the
> inserts inside a transaction?
>
> I'm very interested in this issue since the environment I now work in
> has a lot of network connected databases and the performance is much
> less than I am used to with local databases.
>
> Mark.
> --
> Mark Aufflick
>    e  mark@pumptheory.com
>    w  www.pumptheory.com (work)
>    w  mark.aufflick.com (personal)
>    p  +61 438 700 647
> On 23/07/2004, at 4:02 PM, Christopher Kings-Lynne wrote:
>
> >> But with the server running on one machine and the client running on
> >> another, the two machines being connected by a 100 Mb ethernet, with
> >> nothing
> >> else on the network, this test takes 17 minutes to run. I have tried
> >> changing the frequency of COMMIT operations, but with only a small
> >> effect.
> >
> > Are you using separate INSERT statements?  Try using COPY instead,
> > it's much faster.
> >
> > chris
> >
> >
> > ---------------------------(end of
> > broadcast)---------------------------
> > TIP 3: if posting/reading through Usenet, please send an appropriate
> >      subscribe-nomail command to majordomo@postgresql.org so that your
> >      message can get through to the mailing list cleanly
> >
> > =======================================================================
> > =
> > Pain free spam & virus protection by:          www.mailsecurity.net.au
> > Forward undetected SPAM to:                   spam@mailsecurity.net.au
> > =======================================================================
> > =
> >
>
>
> ========================================================================
>  Pain free spam & virus protection by:          www.mailsecurity.net.au
>  Forward undetected SPAM to:                   spam@mailsecurity.net.au
> ========================================================================
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
>       joining column's datatypes do not match