Re: [PERFORM] Performance over a LAN
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 [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
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 [EMAIL PROTECTED] 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 [EMAIL PROTECTED] 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: [EMAIL PROTECTED] === = Pain free spam virus protection by: www.mailsecurity.net.au Forward undetected SPAM to: [EMAIL PROTECTED] ---(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: [PERFORM] Performance over a LAN
On Thu, 2004-07-22 at 23:50, William Carney wrote:
Hello,
Using a test client application that performs 10 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?
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [PERFORM] Performance over a LAN
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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Mark Aufflick Sent: Friday, 23 July 2004 3:50 PM To: Christopher Kings-Lynne Cc: William Carney; [EMAIL PROTECTED] 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 [EMAIL PROTECTED] 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 [EMAIL PROTECTED] 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: [EMAIL PROTECTED] === = Pain free spam virus protection by: www.mailsecurity.net.au Forward undetected SPAM to: [EMAIL PROTECTED] ---(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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Performance over a LAN
On Fri, 2004-07-23 at 01:50, William Carney wrote:
Hello,
Using a test client application that performs 10 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.
---(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: [PERFORM] Performance over a LAN
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 [EMAIL PROTECTED] http://www.jefftrout.com/ http://www.stuarthamm.net/ ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] Performance over a LAN
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 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] Performance over a LAN
--- William Carney [EMAIL PROTECTED] 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;
chardb[10];
charinserts[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;n5000;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 -
so many all-new ways to express yourself http://uk.messenger.yahoo.com
---(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: [PERFORM] Performance over a LAN
William Carney [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[PERFORM] Performance over a LAN
Hello,
Using a test client application that performs 10 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
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
