Thread: Slow deletes in 8.1 when FKs are involved
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
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
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
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