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 ca
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 sec
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 o
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
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 identi
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 th
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
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 us
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 t
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 Spieg
"Rosser Schwarz" <[EMAIL PROTECTED]> writes:
>> 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.
"select version()" is t
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
while you weren't looking, Tom Lane wrote:
> I hate to break it to you, but that most definitely means you are
> running with BLCKSZ = 32K. Whatever you thought you were rebuilding
> didn't take effect.
I saw that and thought so. The other day, I was rooting around in
$PGDATA, and saw a lot of
"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
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?
No
while you weren't looking, Tom Lane wrote:
> What's not immediately evident is whether the excess I/O accounted for
> all of the slowdown. Could you retry the strace with -r and -T options
> so we can see how much time is being spent inside and outside the
> syscalls?
Unlike the previous run (th
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 question
ptions and control-c out?
- Original Message -
From: "Rosser Schwarz" <[EMAIL PROTECTED]>
To: "'Tom Lane'" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Tuesday, March 16, 2004 7:20 PM
Subject: Re: [PERFORM] atrocious update performance
wh
"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
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:
> 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 accumu
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 tota
"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:
> 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 v
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 = 131
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.
'll try 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: [PERF
"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
> 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 n
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)
> > >-
Bulk updates are generally dogs (not just in pg), so I avoid doing them by
doing faster selects and inserts. You can create a new table using 'create
table as' to produce your target results. This is real fast - avoiding the
row iteration in insert, allowing the select optimizer to run and no index
> > # 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
>
33 matches
Mail list logo
