Thread: Performance over a LAN

Performance over a LAN

From
"William Carney"
Date:
Hello,

Using a test client application that performs 100000 insert operations on a
table, with the client application running on the same machine as the
Postgres server, I get the following results for the time taken to run the
test:

Unix domain socket connection: 26 seconds
Inet domain socket ('localhost'): 35 seconds

The table has two columns, a timestamp and a character(16), no indexes.

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.

The machines used are P4s running FreeBSD 5.2.1. The Postgres version is
7.4.3. Can anyone tell me why there's such a big difference?

Thanks,
William


Re: Performance over a LAN

From
Christopher Kings-Lynne
Date:
> 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


Re: Performance over a LAN

From
Mark Aufflick
Date:
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
========================================================================


Re: Performance over a LAN

From
"Scott Marlowe"
Date:
On Thu, 2004-07-22 at 23:50, William Carney wrote:
> Hello,
>
> Using a test client application that performs 100000 insert operations on a
> table, with the client application running on the same machine as the
> Postgres server, I get the following results for the time taken to run the
> test:
>
> Unix domain socket connection: 26 seconds
> Inet domain socket ('localhost'): 35 seconds
>
> The table has two columns, a timestamp and a character(16), no indexes.
>
> 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.
>
> The machines used are P4s running FreeBSD 5.2.1. The Postgres version is
> 7.4.3. Can anyone tell me why there's such a big difference?

Are you using the exact same script locally as across the network?

Have you checked to see how fast you can copy just a plain text file
across the network connection?

Have you checked your system to see if you're getting lots of network
errors or anything like that?


Re: Performance over a LAN

From
"William Carney"
Date:
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


Re: Performance over a LAN

From
Rod Taylor
Date:
On Fri, 2004-07-23 at 01:50, William Carney wrote:
> Hello,
>
> Using a test client application that performs 100000 insert operations on a
> table, with the client application running on the same machine as the
> Postgres server, I get the following results for the time taken to run the
> test:
>
> Unix domain socket connection: 26 seconds
> Inet domain socket ('localhost'): 35 seconds

> The machines used are P4s running FreeBSD 5.2.1. The Postgres version is
> 7.4.3. Can anyone tell me why there's such a big difference?

Domains sockets have significantly less work to do than inet sockets as
well as less delays for the transmission itself.



Re: Performance over a LAN

From
Jeff
Date:
On Jul 23, 2004, at 3:57 AM, William Carney wrote:

>
> 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

It would be interesting to run something like ntop that can show you
current network usage... unless you are doing a large COPY the PG
protocol has a lot of back and forth messages...

--
Jeff Trout <jeff@jefftrout.com>
http://www.jefftrout.com/
http://www.stuarthamm.net/


Re: Performance over a LAN

From
Michael Adler
Date:
On Fri, Jul 23, 2004 at 03:20:54PM +0930, William Carney 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.
>
> The machines used are P4s running FreeBSD 5.2.1. The Postgres version is
> 7.4.3. Can anyone tell me why there's such a big difference?

Can you reproduce this problem in a tiny test case? If your application
is doing other networky things (e.g. many name resolutions that hang
for 10 seconds each), they may be slowing down the PostgreSQL work.

Just a WAG.

-mike

Re: Performance over a LAN

From
Gary Cowell
Date:
--- William Carney <wcarney@sa.quiktrak.com.au> wrote:


> 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


It's probably the number of round trips to the server.
 If pg can accept host variable arrays, try using a
thousand element array or something to do your
inserts.

e.g. char mycharhv[1000][10]

then set up the mycharhvs[1][..], [2][...] etc and
fling them at the database with a single insert
statement.

I just tried this with the following program:

#include <stdio.h>
exec sql include sqlca;
exec sql begin declare section;
char    db[10];
char    inserts[5000][10];
exec sql end declare section;
int main(void) {
unsigned int n;
        strcpy(db,"mydb");
        exec sql connect to :db;
        printf("sqlcode connect %i\n",sqlca.sqlcode);
        for(n=0;n<5000;n++) {
                strcpy(inserts[n],"hello");
        }
        exec sql insert into gaz values (:inserts);
        printf("sqlcode insert %i\n",sqlca.sqlcode);
        exec sql commit work;
}


This didn't work on pg, I only got one row inserted.
This is using ecpg 2.9.0, pg 7.2.2

On Oracle with PRO*C this causes 5000 rows to be
written with one insert and is a technique I've used
to  get better network performance with Oracle.

Is this fixed in newer versions? If not, it sounds
like a good feature.





___________________________________________________________ALL-NEW Yahoo! Messenger - sooooo many all-new ways to
expressyourself http://uk.messenger.yahoo.com 

Re: Performance over a LAN

From
Greg Stark
Date:
"William Carney" <wcarney@sa.quiktrak.com.au> writes:

> The machines used are P4s running FreeBSD 5.2.1. The Postgres version is
> 7.4.3. Can anyone tell me why there's such a big difference?

You're going to have to run tcpdump and see where the delays are. It might be
hard to decode the postgres protocol though.

Which driver are you using? I wonder if it isn't the same nagle+delayed ack
problem that came up recently.

--
greg