Thread: Performance over a LAN
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
> 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
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 ========================================================================
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?
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
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.
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/
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
--- 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
"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