Re: [PERFORM] Correcting Hash Join Estimates

2005-04-04 Thread Mark Lubratt
On Apr 4, 2005, at 12:54 AM, Tom Lane wrote: [EMAIL PROTECTED] writes: I'm trying to optimize a query and the EXPLAIN ANALYZE (see link below) shows that some hash join row estimates are wrong by a factor of 2-3, and upwards of 7-8. I doubt that improving those estimates would lead to markedly

[PERFORM] Postgresql vs SQLserver for this application ?

2005-04-04 Thread bsimon
hi all. We are designing a quite big application that requires a high-performance database backend. The rates we need to obtain are at least 5000 inserts per second and 15 selects per second for one connection. There should only be 3 or 4 simultaneous connections. I think our main concern is

[PERFORM] 8.0.1 performance question.

2005-04-04 Thread alvin.yk
Hi, I have just upgraded our db from 7.4.2 to 8.0.1 and we are doing some testing. For some reasons, we have discovered that our application performs much slower on 8.0.1. My initial reaction was to turn on log_min_duration_statement to see what's happening. However,

Re: [PERFORM] How to improve db performance with $7K?

2005-04-04 Thread Alex Turner
To be honest, I've yet to run across a SCSI configuration that can touch the 3ware SATA controllers. I have yet to see one top 80MB/sec, let alone 180MB/sec read or write, which is why we moved _away_ from SCSI. I've seen Compaq, Dell and LSI controllers all do pathetically badly on RAID 1, RAID

Re: [PERFORM] Sustained inserts per sec ... ?

2005-04-04 Thread Christopher Petrilli
On Apr 1, 2005 3:59 PM, Christopher Petrilli [EMAIL PROTECTED] wrote: On Apr 1, 2005 3:53 PM, Joshua D. Drake [EMAIL PROTECTED] wrote: What seems to happen is it slams into a wall of some sort, the system goes into disk write frenzy (wait=90% CPU), and eventually recovers and starts

Re: [PERFORM] Sustained inserts per sec ... ?

2005-04-04 Thread Alex Turner
Yup, Battery backed, cache enabled. 6 drive RAID 10, and 4 drive RAID 10, and 2xRAID 1. It's a 3ware 9500S-8MI - not bad for $450 plus BBU. Alex Turner netEconomist On Apr 1, 2005 6:03 PM, Tom Lane [EMAIL PROTECTED] wrote: Alex Turner [EMAIL PROTECTED] writes: On Apr 1, 2005 4:17 PM, Alex

Re: [PERFORM] Sustained inserts per sec ... ?

2005-04-04 Thread Tom Lane
Christopher Petrilli [EMAIL PROTECTED] writes: The table has: * 21 columns (nothing too strange) * No OIDS * 5 indexes, including the primary key on a string Could we see the *exact* SQL definitions of the table and indexes? Also some sample data would be interesting. I'm

Re: [PERFORM] How to improve db performance with $7K?

2005-04-04 Thread Steve Poe
Alex Turner wrote: To be honest, I've yet to run across a SCSI configuration that can touch the 3ware SATA controllers. I have yet to see one top 80MB/sec, let alone 180MB/sec read or write, which is why we moved _away_ from SCSI. I've seen Compaq, Dell and LSI controllers all do pathetically

Re: [PERFORM] Sustained inserts per sec ... ?

2005-04-04 Thread Christopher Petrilli
On Apr 4, 2005 11:52 AM, Tom Lane [EMAIL PROTECTED] wrote: Christopher Petrilli [EMAIL PROTECTED] writes: The table has: * 21 columns (nothing too strange) * No OIDS * 5 indexes, including the primary key on a string Could we see the *exact* SQL definitions of the table and

Re: [PERFORM] [JDBC] 8.0.1 performance question.

2005-04-04 Thread Kris Jurka
On Tue, 5 Apr 2005 [EMAIL PROTECTED] wrote: I see statements below being executed non-stop. Who is triggering these statemetns? Is this normal? What am I doing wrong? 2005-04-04 18:05:00 CST PARSELOG: statement: SELECT attnotnull FROM pg_catalog.pg_attribute WHERE attrelid = $1 AND

Re: [PERFORM] Sustained inserts per sec ... ?

2005-04-04 Thread Tom Lane
Christopher Petrilli [EMAIL PROTECTED] writes: On Apr 4, 2005 11:52 AM, Tom Lane [EMAIL PROTECTED] wrote: Could we see the *exact* SQL definitions of the table and indexes? Of course, this is a bit cleansed, since it's an internal project, but only the column names are changed: Thanks. No

Re: [PERFORM] Sustained inserts per sec ... ?

2005-04-04 Thread Christopher Petrilli
On Apr 4, 2005 12:23 PM, Tom Lane [EMAIL PROTECTED] wrote: Christopher Petrilli [EMAIL PROTECTED] writes: On Apr 4, 2005 11:52 AM, Tom Lane [EMAIL PROTECTED] wrote: Could we see the *exact* SQL definitions of the table and indexes? Of course, this is a bit cleansed, since it's an internal

Re: [PERFORM] [JDBC] 8.0.1 performance question.

2005-04-04 Thread alvin.yk
Thank you for the quick response. To help me debug what's happening, can you tell me what's the difference between the 7.4 and 8.0 jdbc drivers in this regard? Is this something that is newly introduced in 8.0? Or is this something that has always been happening? Thanks. On Apr 5, 2005

Re: [PERFORM] [JDBC] 8.0.1 performance question.

2005-04-04 Thread Kris Jurka
On Tue, 5 Apr 2005 [EMAIL PROTECTED] wrote: Thank you for the quick response. To help me debug what's happening, can you tell me what's the difference between the 7.4 and 8.0 jdbc drivers in this regard? Is this something that is newly introduced in 8.0? Or is this something that has

Re: [PERFORM] How to improve db performance with $7K?

2005-04-04 Thread Alex Turner
I'm no drive expert, but it seems to me that our write performance is excellent. I think what most are concerned about is OLTP where you are doing heavy write _and_ heavy read performance at the same time. Our system is mostly read during the day, but we do a full system update everynight that

Re: [PERFORM] How to improve db performance with $7K?

2005-04-04 Thread Vivek Khera
On Apr 4, 2005, at 3:12 PM, Alex Turner wrote: Our system is mostly read during the day, but we do a full system update everynight that is all writes, and it's very fast compared to the smaller SCSI system we moved off of. Nearly a 6x spead improvement, as fast as 900 rows/sec with a 48 byte

Re: [PERFORM] How to improve db performance with $7K?

2005-04-04 Thread Alex Turner
I'm doing some research on SATA vs SCSI right now, but to be honest I'm not turning up much at the protocol level. Alot of stupid benchmarks comparing 10k Raptor drives against Top of the line 15k drives, where usnurprsingly the SCSI drives win but of course cost 4 times as much. Although even

Re: [PERFORM] Sustained inserts per sec ... ?

2005-04-04 Thread Simon Riggs
On Mon, 2005-04-04 at 09:48 -0400, Christopher Petrilli wrote: The point, in the rough middle, is where the program begins inserting into a new table (inherited). The X axis is the total number of rows inserted. and you also mention the same data plotted with elapsed time:

Re: [PERFORM] Sustained inserts per sec ... ?

2005-04-04 Thread Christopher Petrilli
On Apr 4, 2005 3:46 PM, Simon Riggs [EMAIL PROTECTED] wrote: On Mon, 2005-04-04 at 09:48 -0400, Christopher Petrilli wrote: The point, in the rough middle, is where the program begins inserting into a new table (inherited). The X axis is the total number of rows inserted. and you also

Re: [PERFORM] Sustained inserts per sec ... ?

2005-04-04 Thread Simon Riggs
On Mon, 2005-04-04 at 15:56 -0400, Christopher Petrilli wrote: On Apr 4, 2005 3:46 PM, Simon Riggs [EMAIL PROTECTED] wrote: On Mon, 2005-04-04 at 09:48 -0400, Christopher Petrilli wrote: The point, in the rough middle, is where the program begins inserting into a new table (inherited).

Re: [PERFORM] Sustained inserts per sec ... ?

2005-04-04 Thread Christopher Petrilli
On Apr 4, 2005 4:11 PM, Simon Riggs [EMAIL PROTECTED] wrote: I'm very interested in the graphs of elapsed time for COPY 500 rows against rows inserted. The simplistic inference from those graphs are that if you only inserted 5 million rows into each table, rather than 10 million rows

Re: [PERFORM] Sustained inserts per sec ... ?

2005-04-04 Thread Christopher Petrilli
On Apr 4, 2005 4:53 PM, PFC [EMAIL PROTECTED] wrote: This is done using COPY syntax, not INSERT syntax. So I suppose yes I do. The file that is being used for COPY is kept on a ramdisk. COPY or psql \copy ? If you wanna be sure you commit after each COPY, launch a psql in

Re: [PERFORM] Sustained inserts per sec ... ?

2005-04-04 Thread PFC
This is done using COPY syntax, not INSERT syntax. So I suppose yes I do. The file that is being used for COPY is kept on a ramdisk. COPY or psql \copy ? If you wanna be sure you commit after each COPY, launch a psql in a shell and check if the inserted rows are visible (watching SELECT

Re: [PERFORM] Sustained inserts per sec ... ?

2005-04-04 Thread Simon Riggs
On Mon, 2005-04-04 at 16:18 -0400, Christopher Petrilli wrote: On Apr 4, 2005 4:11 PM, Simon Riggs [EMAIL PROTECTED] wrote: I'm very interested in the graphs of elapsed time for COPY 500 rows against rows inserted. The simplistic inference from those graphs are that if you only

Re: [PERFORM] Sustained inserts per sec ... ?

2005-04-04 Thread Christopher Petrilli
On Apr 4, 2005 4:58 PM, Simon Riggs [EMAIL PROTECTED] wrote: Can you do: select relname from pg_class where relfilenode = 26488271 and confirm that the name is the table you've been loading... It is. Couldn't see all your indexes... are they still there? Nope, I'm running a second run

Re: [PERFORM] Sustained inserts per sec ... ?

2005-04-04 Thread Simon Riggs
On Mon, 2005-04-04 at 17:03 -0400, Christopher Petrilli wrote: On Apr 4, 2005 4:58 PM, Simon Riggs [EMAIL PROTECTED] wrote: Can you do: select relname from pg_class where relfilenode = 26488271 and confirm that the name is the table you've been loading... It is. Couldn't see all

Re: [PERFORM] Sustained inserts per sec ... ?

2005-04-04 Thread Mike Rylander
If I'm getting the point of this thread correctly, have a huge amount of data in one table degrades INSERT/COPY performance even with just a PKEY index. If that's about the size of it, read on. If not, ignore me because I missed something. On Apr 4, 2005 10:44 PM, Simon Riggs [EMAIL PROTECTED]

[PERFORM] Bulk COPY end of copy delimiter

2005-04-04 Thread Steven Rosenstein
Today while trying to do a bulk COPY of data into a table, the process aborted with the following error message: ERROR: end-of-copy marker corrupt CONTEXT: COPY tbl_logged_event, line 178519: 606447014,1492,2005-02-24 03:16:14,2005-02-23 20:27:48,win_applog,,error,adsmclientservice,nt

Re: [PERFORM] Bulk COPY end of copy delimiter

2005-04-04 Thread Gavin Sherry
Hi, On Mon, 4 Apr 2005, Steven Rosenstein wrote: Today while trying to do a bulk COPY of data into a table, the process aborted with the following error message: ERROR: end-of-copy marker corrupt CONTEXT: COPY tbl_logged_event, line 178519: 606447014,1492,2005-02-24 03:16:14,2005-02-23

Re: [PERFORM] Sustained inserts per sec ... ?

2005-04-04 Thread Christopher Petrilli
On Apr 4, 2005 6:44 PM, Simon Riggs [EMAIL PROTECTED] wrote: Before I start to tunnel-vision on a particular coincidence... How much memory have you got on the system? Now, 2Gb, but most of it is free in this situation. Earlier, I posted some of the settings related to work mem. How much

Re: [PERFORM] Sustained inserts per sec ... ?

2005-04-04 Thread Tom Lane
Christopher Petrilli [EMAIL PROTECTED] writes: On Apr 4, 2005 12:23 PM, Tom Lane [EMAIL PROTECTED] wrote: do a test run with *no* indexes on the table, just to see if it behaves any differently? Basically I was wondering if index overhead might be part of the problem.

Re: [PERFORM] Sustained inserts per sec ... ?

2005-04-04 Thread Christopher Petrilli
On Apr 4, 2005 10:36 PM, Tom Lane [EMAIL PROTECTED] wrote: Christopher Petrilli [EMAIL PROTECTED] writes: On Apr 4, 2005 12:23 PM, Tom Lane [EMAIL PROTECTED] wrote: do a test run with *no* indexes on the table, just to see if it behaves any differently? Basically I was wondering if index

Re: [PERFORM] Bulk COPY end of copy delimiter

2005-04-04 Thread Steven Rosenstein
Your assumption is correct. The data was generated out of a DB2 database, and uses commas as field delimiters. Thank you for the workaround, --- Steve ___ Steven Rosenstein IT Architect/Developer | IBM Virtual

Re: [PERFORM] Sustained inserts per sec ... ?

2005-04-04 Thread Tom Lane
Christopher Petrilli [EMAIL PROTECTED] writes: On Apr 4, 2005 10:36 PM, Tom Lane [EMAIL PROTECTED] wrote: The indicated fix of course is to increase shared_buffers. Any idea where it should be set? Not really. An upper bound would be the total size of the finished indexes for one 10M-row

Re: [PERFORM] Sustained inserts per sec ... ?

2005-04-04 Thread Greg Stark
Tom Lane [EMAIL PROTECTED] writes: What I think might be happening is that the working set of pages touched during index inserts is gradually growing, and at some point it exceeds shared_buffers, and at that point performance goes in the toilet because we are suddenly doing lots of reads to

Re: [PERFORM] Sustained inserts per sec ... ?

2005-04-04 Thread Christopher Petrilli
On 04 Apr 2005 23:45:47 -0400, Greg Stark [EMAIL PROTECTED] wrote: Tom Lane [EMAIL PROTECTED] writes: What I think might be happening is that the working set of pages touched during index inserts is gradually growing, and at some point it exceeds shared_buffers, and at that point

Re: [PERFORM] Sustained inserts per sec ... ?

2005-04-04 Thread Tom Lane
Greg Stark [EMAIL PROTECTED] writes: All this is happening within a single transaction too, right? So there hasn't been an fsync the entire time. It's entirely up to the kernel when to decide to start writing data. No ... there's a commit every 500 records. However, I think Chris said he was

[PERFORM] Compressing WAL

2005-04-04 Thread Jim C. Nasby
Maybe better for -hackers, but here it goes anyway... Has anyone looked at compressing WAL's before writing to disk? On a system generating a lot of WAL it seems there might be some gains to be had WAL data could be compressed before going to disk, since today's machines are generally more I/O

Re: [PERFORM] Sustained inserts per sec ... ?

2005-04-04 Thread Christopher Petrilli
On Apr 4, 2005 11:57 PM, Tom Lane [EMAIL PROTECTED] wrote: Greg Stark [EMAIL PROTECTED] writes: All this is happening within a single transaction too, right? So there hasn't been an fsync the entire time. It's entirely up to the kernel when to decide to start writing data. No ...

Re: [PERFORM] Sustained inserts per sec ... ?

2005-04-04 Thread Jim C. Nasby
On Tue, Apr 05, 2005 at 12:16:27AM -0400, Christopher Petrilli wrote: My fear is that it's some bizarre situation interacting with both issues, and one that might not be solvable. Does anyone else have much experience with this sort of sustained COPY? You might ask the guy who just posted to