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[EMAIL PROTECTED] BFK edv-consulting GmbH http://www.bfk.de/ Durlacher Allee 47tel: +49-721-96201-1 D-76131 Karlsruhe fax: +49-721-96201-99 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Performance penalty for remote access of postgresql (8.1.3)? any experiance?
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:[EMAIL PROTECTED] Sent: 2006Ae7OA19EO 16:30 To: [EMAIL PROTECTED] 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[EMAIL PROTECTED] BFK edv-consulting GmbH http://www.bfk.de/ Durlacher Allee 47tel: +49-721-96201-1 D-76131 Karlsruhe fax: +49-721-96201-99 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Performance penalty for remote access of postgresql (8.1.3)? any experiance?
* Guoping Zhang: Thanks for pointing me the cause, but we simply cannot use the COPY FROM solution. Why not? Just do something like this: CREATE TEMPORARY TABLE tmp (col1 TEXT NOT NULL, col2 INTEGER NOT NULL); COPY tmp FROM STDIN; row11 row22 ... \. INSERT INTO target SELECT * FROM tmp; If you need some kind of SELECT/INSERT/UPDATE cycle, it's far more complex, of course, and I'm not quite happy with what I'm using right now. -- Florian Weimer[EMAIL PROTECTED] BFK edv-consulting GmbH http://www.bfk.de/ Durlacher Allee 47tel: +49-721-96201-1 D-76131 Karlsruhe fax: +49-721-96201-99 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Performance penalty for remote access of postgresql (8.1.3)? any experiance?
* Guoping Zhang ([EMAIL PROTECTED]) wrote: Obviously, if there is no better solution, the TCP round trip penalty will stop us doing so as we do have performance requirement. Actually, can't you stick multiple inserts into a given 'statement'? ie: insert into abc (123); insert into abc (234); I'm not 100% sure if that solves the round-trip issue, but it might.. Also, it looks like we might have multi-value insert support in 8.2 (I truely hope so anyway), so you could do something like this: insert into abc (123),(234); I guess there shall be quite number of people out there facing the similar problem, right? No alternative solution? Havn't run into it myself... Quite often you either have large inserts being done using COPY commands (data warehousing and analysis work) or you have a relatively small number of one-off inserts (OLTP) per transaction. Enjoy, Stephen signature.asc Description: Digital signature
Re: [PERFORM] Performance penalty for remote access of postgresql (8.1.3)? any experiance?
* Stephen Frost: Actually, can't you stick multiple inserts into a given 'statement'? ie: insert into abc (123); insert into abc (234); IIRC, this breaks with PQexecParams, which is the recommended method for executing SQL statements nowadays. -- Florian Weimer[EMAIL PROTECTED] BFK edv-consulting GmbH http://www.bfk.de/ Durlacher Allee 47tel: +49-721-96201-1 D-76131 Karlsruhe fax: +49-721-96201-99 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Performance penalty for remote access of postgresql (8.1.3)? any experiance?
* Florian Weimer ([EMAIL PROTECTED]) wrote: * Stephen Frost: Actually, can't you stick multiple inserts into a given 'statement'? ie: insert into abc (123); insert into abc (234); IIRC, this breaks with PQexecParams, which is the recommended method for executing SQL statements nowadays. For prepared queries you're absolutely correct. It's also true that it's the recommended approach for large numbers of inserts. If the network delay is more of a problem than the processing speed then it might make sense. It does seem to me that with multi-value insert we might consider changes to libpq to be able to use multi-value prepared inserts... Or it might be interesting to see the performance of non-prepared multi-value inserts vs. prepared statements. Thanks, Stephen signature.asc Description: Digital signature
Re: [PERFORM] Performance penalty for remote access of postgresql
In response to Guoping Zhang [EMAIL PROTECTED]: 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? I suppose I'm a little confused on two points: 1) What did you expect. 2) What is your network? On #1: networking adds overhead. Period. Always. I believe you earlier said you estimated around %20 perf hit. For small transactions, I wouldn't expect much better. TCP adds a good bit of header to each packet, plus the time in the kernel, and the RTT. 20% sounds about average to me. #2 falls into a number of different categories. For example: a) What is your topology? If you absolutely need blazing speed, you should have a dedicated gigabit switched network between the machines. b) Not all network hardware is created equal. Cheap switches seldom perform at their advertised speed. Stick with high-end stuff. NICs are the same way. On #2, you'll want to ensure that the problem is not in the hardware before you start complaining about PostgreSQL, or even TCP. If you've got a cheap, laggy switch, not amount of TCP or PostgreSQL tuning is going to overcome it. Hope some of this is helpful. -- Bill Moran Collaborative Fusion Inc. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Performance penalty for remote access of postgresql
Stephen Frost wrote: * Guoping Zhang ([EMAIL PROTECTED]) wrote: Obviously, if there is no better solution, the TCP round trip penalty will stop us doing so as we do have performance requirement. Actually, can't you stick multiple inserts into a given 'statement'? ie: insert into abc (123); insert into abc (234); I'm not 100% sure if that solves the round-trip issue, but it might.. Also, it looks like we might have multi-value insert support in 8.2 (I truely hope so anyway), so you could do something like this: insert into abc (123),(234); Yeah, see my post from last night on PATCHES. Something like insert into abc (123); insert into abc (234); ... actually seems to work pretty well as long as you don't drive the machine into swapping. If you're doing a very large number of INSERTs, break it up into bite-sized chunks and you should be fine. Joe ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
