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
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
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,
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
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
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
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
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
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
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
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
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
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
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
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
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:
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
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).
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
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
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
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
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
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
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]
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
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
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
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
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
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
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
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
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
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
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 ...
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
37 matches
Mail list logo
