pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedSlow deletes in 8.1 when FKs are involved
7+ messages / 4 participants
[nested] [flat]
* Slow deletes in 8.1 when FKs are involved
@ 2006-04-20 16:48 Will Reese <wreese@rackspace.com>
2006-04-26 23:43 ` Re: Slow deletes in 8.1 when FKs are involved Stef T <stef@ummon.com>
0 siblings, 1 reply; 7+ messages in thread
From: Will Reese @ 2006-04-20 16:48 UTC (permalink / raw)
To: pgsql-performance
I'm preparing for an upgrade from PostgreSQL 7.4.5 to 8.1.3, and I
noticed a potential performance issue.
I have two servers, a dual proc Dell with raid 5 running PostgreSQL
7.4, and a quad proc Dell with a storage array running PostgreSQL
8.1. Both servers have identical postgresql.conf settings and were
restored from the same 7.4 backup. Almost everything is faster on the
8.1 server (mostly due to hardware), except one thing...deletes from
tables with foreign keys.
I have table A with around 100,000 rows, that has foreign keys to
around 50 other tables. Some of these other tables (table B, for
example) have around 10 million rows.
On the 7.4 server, I can delete a single row from a table A in well
under a second (as expected). On the 8.1 server, it takes over a
minute to delete. I tried all the usual stuff, recreating indexes,
vacuum analyzing, explain analyze. Everything is identical between
the systems. If I hit ctrl-c while the delete was running on 8.1, I
repeatedly got the following message...
db=# delete from "A" where "ID" in ('6');
Cancel request sent
ERROR: canceling statement due to user request
CONTEXT: SQL statement "SELECT 1 FROM ONLY "public"."B" x WHERE
"A_ID" = $1 FOR SHARE OF x"
It looks to me like the "SELECT ... FOR SHARE" functionality in 8.1
is the culprit. Has anyone else run into this issue?
Will Reese -- http://blog.rezra.com
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Slow deletes in 8.1 when FKs are involved
2006-04-20 16:48 Slow deletes in 8.1 when FKs are involved Will Reese <wreese@rackspace.com>
@ 2006-04-26 23:43 ` Stef T <stef@ummon.com>
2006-04-27 02:13 ` Re: Slow deletes in 8.1 when FKs are involved Will Reese <wreese@rackspace.com>
0 siblings, 1 reply; 7+ messages in thread
From: Stef T @ 2006-04-26 23:43 UTC (permalink / raw)
To: Will Reese <wreese@rackspace.com>; +Cc: pgsql-performance
Hey there Will,
I would assume that, perhaps, jst perhaps, the FK doesn't have an
index on the field on both sides, so, your seeing a potential sequential
scan happening. Can you fling up an explain anaylze for everyone please
? Anything more will be merely shooting in the dark, and, tracer bullets
aside, I have heard that -that- can be dangerous ;p
Regards
Stef
Will Reese wrote:
> I'm preparing for an upgrade from PostgreSQL 7.4.5 to 8.1.3, and I
> noticed a potential performance issue.
>
> I have two servers, a dual proc Dell with raid 5 running PostgreSQL
> 7.4, and a quad proc Dell with a storage array running PostgreSQL 8.1.
> Both servers have identical postgresql.conf settings and were restored
> from the same 7.4 backup. Almost everything is faster on the 8.1
> server (mostly due to hardware), except one thing...deletes from
> tables with foreign keys.
>
> I have table A with around 100,000 rows, that has foreign keys to
> around 50 other tables. Some of these other tables (table B, for
> example) have around 10 million rows.
>
> On the 7.4 server, I can delete a single row from a table A in well
> under a second (as expected). On the 8.1 server, it takes over a
> minute to delete. I tried all the usual stuff, recreating indexes,
> vacuum analyzing, explain analyze. Everything is identical between
> the systems. If I hit ctrl-c while the delete was running on 8.1, I
> repeatedly got the following message...
>
> db=# delete from "A" where "ID" in ('6');
> Cancel request sent
> ERROR: canceling statement due to user request
> CONTEXT: SQL statement "SELECT 1 FROM ONLY "public"."B" x WHERE
> "A_ID" = $1 FOR SHARE OF x"
>
> It looks to me like the "SELECT ... FOR SHARE" functionality in 8.1 is
> the culprit. Has anyone else run into this issue?
>
>
> Will Reese -- http://blog.rezra.com
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Slow deletes in 8.1 when FKs are involved
2006-04-20 16:48 Slow deletes in 8.1 when FKs are involved Will Reese <wreese@rackspace.com>
2006-04-26 23:43 ` Re: Slow deletes in 8.1 when FKs are involved Stef T <stef@ummon.com>
@ 2006-04-27 02:13 ` Will Reese <wreese@rackspace.com>
0 siblings, 0 replies; 7+ messages in thread
From: Will Reese @ 2006-04-27 02:13 UTC (permalink / raw)
To: pgsql-performance
Stef:
There is already a post explaining the solution. All the proper
indexes were there, and it works great on 7.4. The problem lies with
leftover 7.4 RI triggers being carried over to an 8.1 database. The
solution is to drop the triggers and add the constraint. Hopefully
this will not cause as many locking issues with FKs on 8.1 as it did
in 7.4 (which is why one of the RI triggers was removed in the first
place).
Will Reese -- http://blog.rezra.com
On Apr 26, 2006, at 6:43 PM, Stef T wrote:
>
> Hey there Will,
> I would assume that, perhaps, jst perhaps, the FK doesn't have an
> index on the field on both sides, so, your seeing a potential
> sequential
> scan happening. Can you fling up an explain anaylze for everyone
> please
> ? Anything more will be merely shooting in the dark, and, tracer
> bullets
> aside, I have heard that -that- can be dangerous ;p
>
> Regards
> Stef
>
> Will Reese wrote:
>> I'm preparing for an upgrade from PostgreSQL 7.4.5 to 8.1.3, and I
>> noticed a potential performance issue.
>>
>> I have two servers, a dual proc Dell with raid 5 running PostgreSQL
>> 7.4, and a quad proc Dell with a storage array running PostgreSQL
>> 8.1.
>> Both servers have identical postgresql.conf settings and were
>> restored
>> from the same 7.4 backup. Almost everything is faster on the 8.1
>> server (mostly due to hardware), except one thing...deletes from
>> tables with foreign keys.
>>
>> I have table A with around 100,000 rows, that has foreign keys to
>> around 50 other tables. Some of these other tables (table B, for
>> example) have around 10 million rows.
>>
>> On the 7.4 server, I can delete a single row from a table A in well
>> under a second (as expected). On the 8.1 server, it takes over a
>> minute to delete. I tried all the usual stuff, recreating indexes,
>> vacuum analyzing, explain analyze. Everything is identical between
>> the systems. If I hit ctrl-c while the delete was running on 8.1, I
>> repeatedly got the following message...
>>
>> db=# delete from "A" where "ID" in ('6');
>> Cancel request sent
>> ERROR: canceling statement due to user request
>> CONTEXT: SQL statement "SELECT 1 FROM ONLY "public"."B" x WHERE
>> "A_ID" = $1 FOR SHARE OF x"
>>
>> It looks to me like the "SELECT ... FOR SHARE" functionality in
>> 8.1 is
>> the culprit. Has anyone else run into this issue?
>>
>>
>> Will Reese -- http://blog.rezra.com
>>
>>
>>
>>
>> ---------------------------(end of
>> broadcast)---------------------------
>> TIP 1: if posting/reading through Usenet, please send an appropriate
>> subscribe-nomail command to majordomo@postgresql.org so that
>> your
>> message can get through to the mailing list cleanly
>>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Slow deletes in 8.1 when FKs are involved
@ 2006-04-24 02:41 Will Reese <wreese@rackspace.com>
2006-04-24 03:32 ` Re: Slow deletes in 8.1 when FKs are involved Tom Lane <tgl@sss.pgh.pa.us>
2006-04-24 17:09 ` Re: Slow deletes in 8.1 when FKs are involved Jim C. Nasby <jnasby@pervasive.com>
0 siblings, 2 replies; 7+ messages in thread
From: Will Reese @ 2006-04-24 02:41 UTC (permalink / raw)
To: pgsql-performance
I'm preparing for an upgrade from PostgreSQL 7.4.5 to 8.1.3, and I
noticed a potential performance issue.
I have two servers, a dual proc Dell with raid 5 running PostgreSQL
7.4, and a quad proc Dell with a storage array running PostgreSQL
8.1. Both servers have identical postgresql.conf settings and were
restored from the same 7.4 backup. Almost everything is faster on the
8.1 server (mostly due to hardware), except one thing...deletes from
tables with many foreign keys pointing to them.
I have table A with around 100,000 rows, that has foreign keys from
about 50 other tables pointing to it. Some of these other tables
(table B, for example) have around 10 million rows.
On the 7.4 server, I can delete a single row from a table A in well
under a second (as expected). On the 8.1 server, it takes over a
minute to delete. I tried all the usual stuff, recreating indexes,
vacuum analyzing, explain analyze. Everything is identical between
the systems. If I hit ctrl-c while the slow delete was running on
8.1, I repeatedly got the following message...
db=# delete from "A" where "ID" in ('6');
Cancel request sent
ERROR: canceling statement due to user request
CONTEXT: SQL statement "SELECT 1 FROM ONLY "public"."B" x WHERE
"A_ID" = $1 FOR SHARE OF x"
It looks to me like the "SELECT ... FOR SHARE" functionality in 8.1
is the culprit. Has anyone else run into this issue?
Will Reese -- http://blog.rezra.com
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Slow deletes in 8.1 when FKs are involved
2006-04-24 02:41 Slow deletes in 8.1 when FKs are involved Will Reese <wreese@rackspace.com>
@ 2006-04-24 03:32 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-04-26 00:02 ` Re: Slow deletes in 8.1 when FKs are involved Will Reese <wreese@rackspace.com>
1 sibling, 1 reply; 7+ messages in thread
From: Tom Lane @ 2006-04-24 03:32 UTC (permalink / raw)
To: Will Reese <wreese@rackspace.com>; +Cc: pgsql-performance
Will Reese <wreese@rackspace.com> writes:
> ... Both servers have identical postgresql.conf settings and were
> restored from the same 7.4 backup. Almost everything is faster on the
> 8.1 server (mostly due to hardware), except one thing...deletes from
> tables with many foreign keys pointing to them.
I think it's unquestionable that you have a bad FK plan in use on the
8.1 server. Double check that you have suitable indexes on the
referencing (not referenced) columns, that you've ANALYZEd all the
tables involved, and that you've started a fresh psql session (remember
the backend tends to cache FK plans for the life of the connection).
It might help to EXPLAIN ANALYZE one of the slow deletes --- 8.1 will
break out the time spent in FK triggers, which would let you see which
one(s) are the culprit.
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Slow deletes in 8.1 when FKs are involved
2006-04-24 02:41 Slow deletes in 8.1 when FKs are involved Will Reese <wreese@rackspace.com>
2006-04-24 03:32 ` Re: Slow deletes in 8.1 when FKs are involved Tom Lane <tgl@sss.pgh.pa.us>
@ 2006-04-26 00:02 ` Will Reese <wreese@rackspace.com>
0 siblings, 0 replies; 7+ messages in thread
From: Will Reese @ 2006-04-26 00:02 UTC (permalink / raw)
To: pgsql-performance
I did double check for indexes on the referenced and referencing
columns, and even though this database is restored and vacuum
analyzed nightly the issue remains. Using explain analyze in
postgresql 8.1, I was able to see where the problem lies. For
performance reasons on our 7.4 server, we removed one of the 3 RI
triggers for some constraints (the RI trigger that performs the
SELECT....FOR UPDATE to prevent modifications) and replaced it with a
trigger to just prevent deletes on this data indefinitely (the data
never gets deleted or updated in our app). This works great in
postgresql 7.4 and nearly eliminated our performance issue, but when
that database is restored to postgresql 8.1 one of the remaining two
RI triggers does not perform well at all when you try to delete from
that table (even though it's fine in postgresql 7.4). On the 8.1
server I dropped the remaining two RI triggers, and added the
constraint to recreate the 3 RI triggers. After that the delete
performed fine. So it looks like the 7.4 RI triggers that carried
over to the 8.1 server don't perform very well. I'm hoping that the
SELECT...FOR SHARE functionality in 8.1 will allow us to re-add our
constraints and not suffer from the locking issues we had in
postgresql 7.4.
Will Reese -- http://blog.rezra.com
On Apr 23, 2006, at 10:32 PM, Tom Lane wrote:
> Will Reese <wreese@rackspace.com> writes:
>> ... Both servers have identical postgresql.conf settings and were
>> restored from the same 7.4 backup. Almost everything is faster on the
>> 8.1 server (mostly due to hardware), except one thing...deletes from
>> tables with many foreign keys pointing to them.
>
> I think it's unquestionable that you have a bad FK plan in use on the
> 8.1 server. Double check that you have suitable indexes on the
> referencing (not referenced) columns, that you've ANALYZEd all the
> tables involved, and that you've started a fresh psql session
> (remember
> the backend tends to cache FK plans for the life of the connection).
>
> It might help to EXPLAIN ANALYZE one of the slow deletes --- 8.1 will
> break out the time spent in FK triggers, which would let you see which
> one(s) are the culprit.
>
> regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Slow deletes in 8.1 when FKs are involved
2006-04-24 02:41 Slow deletes in 8.1 when FKs are involved Will Reese <wreese@rackspace.com>
@ 2006-04-24 17:09 ` Jim C. Nasby <jnasby@pervasive.com>
1 sibling, 0 replies; 7+ messages in thread
From: Jim C. Nasby @ 2006-04-24 17:09 UTC (permalink / raw)
To: Will Reese <wreese@rackspace.com>; +Cc: pgsql-performance
On Sun, Apr 23, 2006 at 09:41:14PM -0500, Will Reese wrote:
> I'm preparing for an upgrade from PostgreSQL 7.4.5 to 8.1.3, and I
> noticed a potential performance issue.
>
> I have two servers, a dual proc Dell with raid 5 running PostgreSQL
> 7.4, and a quad proc Dell with a storage array running PostgreSQL
> 8.1. Both servers have identical postgresql.conf settings and were
BTW, you'll want to tweak some things between the two .conf files,
especially if the 8.1.3 server has more memory. Faster drive array means
you probably want to tweak random_page_cost down.
Also, 8.1.3 has a lot of new config settings compared to 7.4.x; it'd
probably be best to take the default 8.1 config and tweak it, rather
than bringing the 7.4 config over.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2006-04-27 02:13 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-04-20 16:48 Slow deletes in 8.1 when FKs are involved Will Reese <wreese@rackspace.com>
2006-04-26 23:43 ` Stef T <stef@ummon.com>
2006-04-27 02:13 ` Will Reese <wreese@rackspace.com>
2006-04-24 02:41 Slow deletes in 8.1 when FKs are involved Will Reese <wreese@rackspace.com>
2006-04-24 03:32 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-04-26 00:02 ` Will Reese <wreese@rackspace.com>
2006-04-24 17:09 ` Jim C. Nasby <jnasby@pervasive.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox