After deinstalling and scrubbing PostgreSQL from my server and doing
a clean build using a vanilla 7.4.2 tree, I'm rather more confident
that foreign key validation is at cause in my performance problems.
I recreated my schemas and ran the original update, with foreign
keys referring to the
On 5 Apr 2004 at 12:05, Rosser Schwarz wrote:
Just this morning, however, I created a copy of the target table (all
4.7M rows), with absolutely no foreign keys referring to it, and ran
the update against the copy. That update took 2300 seconds. The
join columns were indexed in both cases.
while you weren't looking, Kevin Barnard wrote:
Have you added indexes for the custid column for tables
account.acct accunt.orgacct and note?
They were indexed in the original case, yes. There was no
need to index them in today's test case, as that was done
purely in attempt to rule in or
On Mon, 5 Apr 2004, Kevin Barnard wrote:
On 5 Apr 2004 at 12:05, Rosser Schwarz wrote:
Just this morning, however, I created a copy of the target table (all
4.7M rows), with absolutely no foreign keys referring to it, and ran
the update against the copy. That update took 2300 seconds.
On Mon, 5 Apr 2004, Rosser Schwarz wrote:
while you weren't looking, Kevin Barnard wrote:
Have you added indexes for the custid column for tables
account.acct accunt.orgacct and note?
They were indexed in the original case, yes. There was no
need to index them in today's test case, as
Greg Spiegelberg wrote:
Will advise.
After creating 100, 1K, 10K, 100K and 1M-row subsets of account.cust and
the corresponding rows/tables with foreign key constraints referring to
the table, I'm unable to reproduce the behavior at issue.
explain analyze looks like the following, showing the
Greg Spiegelberg wrote:
I've been following this thread closely as I have the same problem
with an UPDATE. Everything is identical here right down to the
strace output.
Has anyone found a workaround or resolved the problem? If not,
I have test systems here which I can use to help up test
Rosser Schwarz wrote:
Greg Spiegelberg wrote:
I've been following this thread closely as I have the same problem
with an UPDATE. Everything is identical here right down to the
strace output.
Has anyone found a workaround or resolved the problem? If not,
I have test systems here which I can
Greg Spiegelberg [EMAIL PROTECTED] writes:
RedHat 7.3 + Kernel 2.4.24 + ext3 + PostgreSQL 7.3.5
Please try 7.4.
regards, tom lane
---(end of broadcast)---
TIP 9: the
I've been following this thread closely as I have the same problem
with an UPDATE. Everything is identical here right down to the
strace output.
Has anyone found a workaround or resolved the problem? If not,
I have test systems here which I can use to help up test and explore.
Greg
--
Greg
On Tuesday 16 March 2004 00:08, Tom Lane wrote:
I'm inclined to suspect an issue with foreign-key checking. You didn't
give us any details about foreign key relationships your cust table is
involved in --- could we see those? And the schemas of the other tables
involved?
Two questions Tom:
while you weren't looking, Tom Lane wrote:
Hm. It looks like you mistakenly traced psql rather than the backend,
but since the delay went away we wouldn't have learned
anything anyhow.
Have you got any idea what conditions may have changed between seeing
delay and not seeing delay?
None,
Rosser Schwarz [EMAIL PROTECTED] writes:
while you weren't looking, Tom Lane wrote:
Have you got any idea what conditions may have changed between seeing
delay and not seeing delay?
None, offhand. I have noticed that when a large query is running,
the machine can sporadically just
I wrote:
Regardless, something thinks it's still there. Is there any way that
it is, and that I've somehow been running 7.3.2 all along? `which
psql`, c show the bindir from my configure, but I'm not sure that's
sufficient.
The weird thing is that I know I never built 7.3.anything with 32K
Shridhar Daithankar [EMAIL PROTECTED] writes:
Rosser Schwarz wrote:
shared_buffers = 4096
sort_mem = 32768
vacuum_mem = 32768
wal_buffers = 16384
checkpoint_segments = 64
checkpoint_timeout = 1800
checkpoint_warning = 30
commit_delay = 5
effective_cache_size = 131072
First of all,
while you weren't looking, Tom Lane wrote:
But with the possible exception of wal_buffers, I can't see
anything in
these settings that explains the originally complained-of performance
problem. I'm still wondering about foreign key checks.
Many of the configs I posted were fairly wild
Rosser Schwarz [EMAIL PROTECTED] writes:
But if I'm not touching the column referenced from account.acct, why
would it be looking there at all? I've got an explain analyze of the
update running now, but until it finishes, I can't say for certain
what it's doing. explain, alone, says:
while you weren't looking, Tom Lane wrote:
EXPLAIN won't tell you anything about triggers that might get fired
during the UPDATE, so it's not much help for investigating possible
FK performance problems. EXPLAIN ANALYZE will give you some indirect
evidence: the difference between the total
Rosser Schwarz [EMAIL PROTECTED] writes:
Having never more than glanced at the output of vacuum verbose, I
can't say whether that makes the cut for oodles. My suspicion is no.
Nope, it sure doesn't. We occasionally see people who don't know they
need to vacuum regularly and have accumulated
while you weren't looking, Tom Lane wrote:
[trace]
`strace -p 21882` run behind the below query and plan ... below that.
# explain update account.cust set prodid = tempprod.prodid, subprodid =
tempprod.subprodid where origid = tempprod.debtid;
QUERY PLAN
Rosser Schwarz [EMAIL PROTECTED] writes:
`strace -p 21882` run behind the below query and plan ... below that.
Hmm ... that took 20 seconds eh?
It is a fairly interesting trace. It shows that the backend needed to
read 63 system catalog pages (that weren't already in shared memory),
which is
We're in the throes of an MS SQL to PostgreSQL migration; our databases
include a number of ~5M row tables. We decided to take this opportunity
to clean up and slightly re-normalize our schemas, given what we've
learned about the data over its lifetime and such, else we wouldn't be
experiencing
# explain update account.cust set prodid = tempprod.prodid
where tempprod.did = origid;
Merge Join (cost=0.00..232764.69 rows=4731410 width=252)
Merge Cond: ((outer.origid)::text = (inner.did)::text)
- Index Scan using ix_origid on cust (cost=0.00..94876.83
# explain update account.cust set prodid = tempprod.prodid
where tempprod.did = origid;
Merge Join (cost=0.00..232764.69 rows=4731410 width=252)
Merge Cond: ((outer.origid)::text = (inner.did)::text)
- Index Scan using ix_origid on cust (cost=0.00..94876.83
PROTECTED]
To: [EMAIL PROTECTED]
Sent: Monday, March 15, 2004 3:28 PM
Subject: [PERFORM] atrocious update performance
We're in the throes of an MS SQL to PostgreSQL migration; our databases
include a number of ~5M row tables. We decided to take this opportunity
to clean up and slightly re
On Mon, 2004-03-15 at 16:15, Rosser Schwarz wrote:
# explain update account.cust set prodid = tempprod.prodid
where tempprod.did = origid;
Merge Join (cost=0.00..232764.69 rows=4731410 width=252)
Merge Cond: ((outer.origid)::text = (inner.did)::text)
- Index Scan using
You can create a new table using 'create table as' to produce your
target results. This is real fast ...
I often see 2 orders of magnitude improvement doing this, and no
need to vacuum.
Indeed:
Query returned successfully with no result in 582761 ms.
Though I must say, ten minutes is
Rosser Schwarz [EMAIL PROTECTED] writes:
You can create a new table using 'create table as' to produce your
target results. This is real fast ...
I often see 2 orders of magnitude improvement doing this, and no
need to vacuum.
Indeed:
Query returned successfully with no result in 582761 ms.
to put together a
sample of a slow mass join update.
/Aaron
- Original Message -
From: Tom Lane [EMAIL PROTECTED]
To: Rosser Schwarz [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Sent: Monday, March 15, 2004 7:08 PM
Subject: Re: [PERFORM] atrocious update performance
Rosser Schwarz [EMAIL
Rosser Schwarz wrote:
shared_buffers = 4096
sort_mem = 32768
vacuum_mem = 32768
wal_buffers = 16384
checkpoint_segments = 64
checkpoint_timeout = 1800
checkpoint_warning = 30
commit_delay = 5
effective_cache_size = 131072
You didn't mention the OS so I would take it as either linux/freeBSD.
30 matches
Mail list logo
