Foreign Keys Constraints, perforamance analysis

=?iso-8859-1?Q?Daniel_=C5kerud?= <zilch@home.se> writes:

Deleting really showed what the MySQL team means. The deletion was sometime=
s 30 seconds to < 1 second.

Well, if I understand your rather vague description, you're comparing a
simple bulk delete of all the tuples in the tables, versus a case where
one table sees a bulk delete but the other ones see retail deletion (one
tuple deleted per triggered query, and that tuple has to be searched for
via an indexscan). Not surprising that it's much slower. The real
question is what this scenario has to do with production activities.

If anyone could help, I would really appriciate if someone could tell me wh=
y the child/child_fkc difference was so much more than the married/married_=
fkc difference...

That strikes me as odd too, since the one case has only one FK reference
and the other has two ... seems like it should have been the other way
'round. Experimental noise maybe? Did you repeat the test to make sure
the numbers were reproducible? Do you care to post all the details
(scripts etc) so that others can try to reproduce it?

I doubt is was becuase of the lack of VACUUM ANALYSE.

You *should* be worried about that. The queries triggered by
foreign-key checks are planned by the regular planner.

regards, tom lane

Deleting really showed what the MySQL team means. The deletion was

sometime=

s 30 seconds to < 1 second.

Well, if I understand your rather vague description, you're comparing a
simple bulk delete of all the tuples in the tables, versus a case where
one table sees a bulk delete but the other ones see retail deletion (one
tuple deleted per triggered query, and that tuple has to be searched for
via an indexscan). Not surprising that it's much slower. The real
question is what this scenario has to do with production activities.

It has nothing to do with production activities. I just want to know how,
and how much, Foreign Keys
Constraints affect performance.

I compare (1) manual deletion of person, married and child versus (2)
deletion of person which implies automatic deletion of married_fkc and
child_fkc using ON DELETE CASCADE.

If anyone could help, I would really appriciate if someone could tell me

wh=

y the child/child_fkc difference was so much more than the

married/married_=

fkc difference...

That strikes me as odd too, since the one case has only one FK reference
and the other has two ... seems like it should have been the other way
'round. Experimental noise maybe? Did you repeat the test to make sure
the numbers were reproducible? Do you care to post all the details
(scripts etc) so that others can try to reproduce it?

Well, the tests were run with quite high values and took quite some time, so
I doubt it was experimental noise.
And that was what I thought too, 2 FK versus 1.

I doubt is was becuase of the lack of VACUUM ANALYSE.

You *should* be worried about that. The queries triggered by
foreign-key checks are planned by the regular planner.

I'll rerun the test using VACUUM ANALYSE in between inserting into
married/married_fkc and child/child_fkc,
and post the results!

regards, tom lane

This whole thing is about making myself aware of the performance impace of
Foreign Keys Constraints.

=?iso-8859-1?Q?Daniel_=C5kerud?= <zilch@home.se> writes:

... Not surprising that it's much slower. The real
question is what this scenario has to do with production activities.

It has nothing to do with production activities. I just want to know how,
and how much, Foreign Keys Constraints affect performance.

My point is that unless bulk delete is an operation you do a lot,
this measurement has little to do with everyday performance. A more
reasonable test (I think) would be to time deletion of a *single* person
record --- and the associated implicit deletion of a small number of
dependent records --- against deletion of the same person record and
explicit deletion of the same number of dependent records. That
actually has something to do with performance of real-world applications
that delete individual records. As is, you are measuring (in effect)
DELETE FROM married;
against
FOR akey IN (SELECT key FROM married) DO
DELETE FROM married WHERE key = akey;
and then blaming the speed difference on foreign keys. It's got nothing
to do with foreign keys and everything to do with number of queries
issued.

regards, tom lane

=?iso-8859-1?Q?Daniel_=C5kerud?= <zilch@home.se> writes:

... Not surprising that it's much slower. The real
question is what this scenario has to do with production activities.

It has nothing to do with production activities. I just want to know

how,

and how much, Foreign Keys Constraints affect performance.

My point is that unless bulk delete is an operation you do a lot,
this measurement has little to do with everyday performance. A more
reasonable test (I think) would be to time deletion of a *single* person
record --- and the associated implicit deletion of a small number of
dependent records --- against deletion of the same person record and
explicit deletion of the same number of dependent records. That
actually has something to do with performance of real-world applications
that delete individual records. As is, you are measuring (in effect)
DELETE FROM married;
against
FOR akey IN (SELECT key FROM married) DO
DELETE FROM married WHERE key = akey;
and then blaming the speed difference on foreign keys. It's got nothing
to do with foreign keys and everything to do with number of queries
issued.

No,
I compare
DELETE FROM person;
against
DELETE FROM person;
DELETE FROM married;
DELETE FROM child;

Which I think has very much to do with performane of real-worl applications
i think. I often think of Accounts, where there are numerous records stored
for this account - which should be deleted when the account is deleted.

regards, tom lane

Daniel �kerud

No,
I compare
DELETE FROM person;
against
DELETE FROM person;
DELETE FROM married;
DELETE FROM child;

Which I think has very much to do with performane of real-worl

applications

i think. I often think of Accounts, where there are numerous records

stored

for this account - which should be deleted when the account is deleted.

It doesn't unless you delete all your people alot (as Tom said).

There's a BIG difference between
delete from person where name='foo' compared to
delete from person where name='foo'; delete from married where ... ; delete
from child where ...;
and
delete from person; compared to
delete from person; delete from married; delete from child;

In the first case, the system sees either 1 statement that expands into 3
statements effectively versus 3 statements. Not too different.

In the second case the system sees 1 statement + 1 statement per row versus
3 statements.
Very different, because it doesn't know it's going to be deleting all of the
rows so it's probably going to choose to index scan to find the matching
rows for each row per each row in person versus knowing before hand to
delete them all.

In addition, with match unspecified, these two behaviors are also not
guaranteed to be the same. With NULLs in the FK fields, you can have rows
that shouldn't get deleted when you delete all of the PK rows. ("At least
one of the values of the referencing columns in R1 shall be a null value, or
the value of each referencing column in R1 shall be equal to the value of
the corresponding referenced column in some row of the referenced table....
let matching rows be all rows in the referencing table whose referencing
column values equal the corresponding referenced column values for the
referential constraint")

There are problems, and it would be nice to figure out a way to combine
actions and checks when a large number of changes are seen (of course how do
you define a large number, but...) to get around some of these bulk cases.

_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com

No,
I compare
DELETE FROM person;
against
DELETE FROM person;
DELETE FROM married;
DELETE FROM child;

Which I think has very much to do with performane of real-worl

applications

i think. I often think of Accounts, where there are numerous records

stored

for this account - which should be deleted when the account is deleted.

It doesn't unless you delete all your people alot (as Tom said).

Agreed, but I don't want to measure the performance of real-world
application anyway, I just want
to issolate how much you loose having the database manager handle the
deletion for you, as the ON DELETE
CASCADE foreign key constraint does.

There's a BIG difference between
delete from person where name='foo' compared to
delete from person where name='foo'; delete from married where ... ;

delete

from child where ...;
and
delete from person; compared to
delete from person; delete from married; delete from child;

I can see that,
In the first case there are a hell lot of overhead sending the queries.

In the first case, the system sees either 1 statement that expands into 3
statements effectively versus 3 statements. Not too different.

ok...

In the second case the system sees 1 statement + 1 statement per row

versus

3 statements.

I can't see what you mean here... "+ 1 statement per row"... there is only
one row?

Very different, because it doesn't know it's going to be deleting all of

the

rows so it's probably going to choose to index scan to find the matching
rows for each row per each row in person versus knowing before hand to
delete them all.

OK... hmm... *confused* :)

What is the difference between these two (only comparing the tables with
foreign keys constraits now):

DELETE FROM PERSON;

and

DELETE FROM PERSON where id = 1;
DELETE FROM PERSON where id = 2;

The only thing I can see (which I assume is what I do wrong here), is that
there is a lot of overhead sending the queries. If we ignore the overhead in
our conversation, what is the difference?

In addition, with match unspecified, these two behaviors are also not
guaranteed to be the same. With NULLs in the FK fields, you can have rows
that shouldn't get deleted when you delete all of the PK rows. ("At least
one of the values of the referencing columns in R1 shall be a null value,

or

the value of each referencing column in R1 shall be equal to the value of
the corresponding referenced column in some row of the referenced

table....

let matching rows be all rows in the referencing table whose referencing
column values equal the corresponding referenced column values for the
referential constraint")

OK, but this is just a test i write. I _am_ sure there are no NULLs there. I
just want to make myself
aware of how what it costs in performance having foreign keys constraints.

There are problems, and it would be nice to figure out a way to combine
actions and checks when a large number of changes are seen (of course how

do

you define a large number, but...) to get around some of these bulk cases.

before I send this message I just gotta say thanks!
i appreciate your input more than you think :)

Anyway, what I do is, in pseudocode:

FOR ( i = 1 to N*2)
insert into person

FOR (i = 1 to N)
insert into married or married_fkc

FOR (i = 1 to 2*N)
insert into child or child_fkc

if (fkc)
delete from person;
else
delete from person, delete from married, delete from child;

I guess this last example shows quite good what I do. Don't this change your
minds?

Daniel �kerud

FOR ( i = 1 to N*2)
insert into person

FOR (i = 1 to N)
insert into married or married_fkc

FOR (i = 1 to 2*N)
insert into child or child_fkc

if (fkc)
delete from person;
else
delete from person, delete from married, delete from child;

Forgott to say that these 4 sections is in four transactions. and with
vacuum analyse in between all of them.

NOW, why is it that the difference between the married/married_fkc (which is
about 50% longer per insert) is the same on child/child_fkc ? Ofcourse
child/child_fkc should take roughly twice the time as married/married_fkc
ignoring the fact that
there are FK Constraints.
But considering the double foreign keys constraints in married_fkc that is
quite strange...

Is there something wrong with the tables?

DROP SEQUENCE person_id_seq;
DROP SEQUENCE married_fkc_id_seq;
DROP SEQUENCE married_id_seq;
DROP SEQUENCE child_fkc_id_seq;
DROP SEQUENCE child_id_seq;

CREATE SEQUENCE person_id_seq MINVALUE 0;
CREATE SEQUENCE married_fkc_id_seq MINVALUE 0;
CREATE SEQUENCE married_id_seq MINVALUE 0;
CREATE SEQUENCE child_fkc_id_seq MINVALUE 0;
CREATE SEQUENCE child_id_seq MINVALUE 0;

DROP TABLE person;
DROP TABLE married_fkc;
DROP TABLE married;
DROP TABLE child_fkc;
DROP TABLE child;

CREATE TABLE person (
id integer DEFAULT nextval('person_id_seq'),
name TEXT
);

CREATE UNIQUE INDEX person_id_key ON person(id);

CREATE TABLE married_fkc (
id integer DEFAULT nextval('married_fkc_id_seq'),
person1ID integer NOT NULL REFERENCES person ( id ) ON DELETE
CASCADE,
person2ID integer NOT NULL REFERENCES person ( id ) ON DELETE
CASCADE,
UNIQUE ( person1ID ),
UNIQUE ( person2ID )
);

CREATE UNIQUE INDEX married_fkc_id_key ON married_fkc(id);

CREATE TABLE married (
id integer DEFAULT nextval('married_id_seq'),
person1ID integer NOT NULL,
person2ID integer NOT NULL,
UNIQUE ( person1ID ),
UNIQUE ( person2ID )
);

CREATE UNIQUE INDEX married_id_key ON married(id);

CREATE TABLE child_fkc (
id integer DEFAULT nextval('child_fkc_id_seq'),
marriedID integer NOT NULL REFERENCES married_fkc ( id ) ON
DELETE CASCADE,
name TEXT
);

CREATE UNIQUE INDEX child_fkc_id_key ON child_fkc(id);

CREATE TABLE child (
id integer DEFAULT nextval('child_id_seq'),
marriedID integer NOT NULL,
name TEXT
);

CREATE UNIQUE INDEX child_id_key ON child(id);

Daniel �kerud

There's a BIG difference between
delete from person where name='foo' compared to
delete from person where name='foo'; delete from married where ... ;

delete

from child where ...;
and
delete from person; compared to
delete from person; delete from married; delete from child;

I can see that,
In the first case there are a hell lot of overhead sending the queries.

In the first case, the system sees either 1 statement that expands into

3

statements effectively versus 3 statements. Not too different.

ok...

In the second case the system sees 1 statement + 1 statement per row

versus

3 statements.

I can't see what you mean here... "+ 1 statement per row"... there is only
one row?

Well, if the parent table has 100 rows, the delete from table turns into 101
statements pretty much.
It basically turns into:
delete from parent;
delete from married where <keys from first person row>
delete from married where <keys from second person row>
...
etc...
In your example with N*2 rows in person and N rows in married_fkc and
N*2 rows in child_fkc, with the bulk delete in person is going to go N*2
deletes from married_fkc and N deletes in child_fkc.

What is the difference between these two (only comparing the tables with
foreign keys constraits now):

DELETE FROM PERSON;

and

DELETE FROM PERSON where id = 1;
DELETE FROM PERSON where id = 2;

The only thing I can see (which I assume is what I do wrong here), is that
there is a lot of overhead sending the queries. If we ignore the overhead

in

our conversation, what is the difference?

It's not the cost on the delete from person, it's the cost on the delete
from married,
and it's not the sending overhead, it's the processing overhead to find the
particular
row you want to delete over and over again. It costs more to find a row by
index
value than to just get the next row in the table (generally). So, in the
first case
you can get all the rows sequentially. In the second you have to pay the
find
a row by index cost for every row. [Either delete 100 rows sequentially or
delete 1 row by index 100 times. The latter is more expensive.]

The difference for purposes of "real world" testing is that you generally
don't do bulk
deletes like that very often and isn't at all what's being optimized for,
and the cost of (taking only the first level):
delete from person where id=1;
delete from married_fkc where person1ID=1;
delete from married_fkc where person2ID=1;
(or even where person1ID=1 or person2ID=1)
and the fk version as:
delete from person where id=1;
*should* be reasonably similar. If it's not, that's a definate performance
problem.

_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com

OK, I've been discussing this with a collegue of mine... and I'm starting to
see the light here...

I will, first of all, make a new, simpler, 1<->1 realtionship to test FK
constraints... no 2<->1<->2 relasionship here...

Person -> Item/item_fkc

And I will do no bulk-delete. Instead these tests:

Fill person. no time measuring.
Fill item, no foreign keys constraints, time measuring.
Fill item, foreign keys constraints
Compare last to measurments. How much do you loose in performance having the
Foreign Key check?
For all persons {
if (fkc)
delete from person where id = $id
else
delete from person where id = $id; delete from item where personid=$id
}
Compare measurements. How much do you loose having the foreign keys
constraints delete the item?

This, I think, this is a more fair comparison.

Can you call the FK itself a foreign key constraint, as it actually is
constraining something?

Thanks Stephan and Tom for the help! Greatly appreciated!!

Daniel �kerud wrote:

OK, I've been discussing this with a collegue of mine... and I'm starting to
see the light here...

I will, first of all, make a new, simpler, 1<->1 realtionship to test FK
constraints... no 2<->1<->2 relasionship here...

One thing you'd have to take into account is the fact that
the MySQL documentation is totally misleading. If you intend
to compare apples to apples, you'd have to take a more real
world scenario.

Every business application has to reflect the business rules
it implements. Using a relational database system it's
possible to move some of the business logic into the database
itself. So we allways have to look at the middleware (e.g.
PHP) and the database (e.g. Postgres) as a unit. If the
business modell now requires some referential integrity, like
there can never be an invoice referencing a non-existent
customer, this unit has to ensure it, no matter what. There
are two possible solutions,

1. Setup a foreign key constraint, so that the database will
not allow the insertion of the invoice or deletion of the
customer, however concurrent the DB access might be.

2. Implement the required checks with appropriate locking
and transaction coverage at every place in the
application, where these two relations are modified WRT
the logical requirement of the business model.

IMHO solution #1 has a major advantage. Only the DB designer
really must understand the entire business modell to ensure
that there will never be logically inconsistent data in the
database. The worst thing that can happen if a WEB developer
doesn't honor the business modell is an aborted transaction
and maybe an error message the user doesn't understand. For
#2 every WEB programmer, at any time editing a single PHP
code snippet, has to have the business modell in mind. Here
the worst possible consequence is violation of the business
modell. So we might have invoices where we don't know anymore
who's the customer.

Thus, to compare MySQL vs. Postgres WRT referential
integrity, create a sample application where the Postgres
version checks for possible errors (can be optimized using
deferred constraints and checking just at the COMMIT). The
MySQL version instead implements the constraints on the
middleware level including transaction handling and locking,
so you'd have to use BDB tables only for example.

Just my $0.02

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #

_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com

OK, I've been discussing this with a collegue of mine... and I'm

starting to

see the light here...

I will, first of all, make a new, simpler, 1<->1 realtionship to test FK
constraints... no 2<->1<->2 relasionship here...

One thing you'd have to take into account is the fact that
the MySQL documentation is totally misleading. If you intend
to compare apples to apples, you'd have to take a more real
world scenario.

Every business application has to reflect the business rules
it implements. Using a relational database system it's
possible to move some of the business logic into the database
itself. So we allways have to look at the middleware (e.g.
PHP) and the database (e.g. Postgres) as a unit. If the
business modell now requires some referential integrity, like
there can never be an invoice referencing a non-existent
customer, this unit has to ensure it, no matter what. There
are two possible solutions,

1. Setup a foreign key constraint, so that the database will
not allow the insertion of the invoice or deletion of the
customer, however concurrent the DB access might be.

2. Implement the required checks with appropriate locking
and transaction coverage at every place in the
application, where these two relations are modified WRT
the logical requirement of the business model.

IMHO solution #1 has a major advantage. Only the DB designer
really must understand the entire business modell to ensure
that there will never be logically inconsistent data in the
database. The worst thing that can happen if a WEB developer
doesn't honor the business modell is an aborted transaction
and maybe an error message the user doesn't understand. For
#2 every WEB programmer, at any time editing a single PHP
code snippet, has to have the business modell in mind. Here
the worst possible consequence is violation of the business
modell. So we might have invoices where we don't know anymore
who's the customer.

Thus, to compare MySQL vs. Postgres WRT referential
integrity, create a sample application where the Postgres
version checks for possible errors (can be optimized using
deferred constraints and checking just at the COMMIT). The
MySQL version instead implements the constraints on the
middleware level including transaction handling and locking,
so you'd have to use BDB tables only for example.

Just my $0.02

Jan

--

Thanks for the input!
... anyway, I never had in mind to compare MySQL to PostgreSQL... just to
see what performance impact the little check the "foreign key references"
has on an insert... and what performance impact ON DELETE CASCADE has when
deleting (if you choose to let the dbmanager to handle it)...

Very good points, and well explained!

Daniel �kerud

Agreed, but I don't want to measure the performance of real-world
application anyway, I just want
to issolate how much you loose having the database manager handle the
deletion for you, as the ON DELETE
CASCADE foreign key constraint does.

That doesn't make any sense. There's no performance in the abstract,
only with regard to actual cases.

You might as well say, "I don't want to measure the performance of
any real CPU, but only the theoretical performance of various CPU
designs using imaginary data." You'll probably get a number, but it
won't refer to anything.

You have a good point there, but it has nothing to do with what I am
doing...
You assume I will conclude that there is X loss in performance and then
that's it. However that is not the case here. The results will be
generalised in time and then the numbers have no meaning in the big case.
I'm writing a big chapter about foreign keys and foreign keys constraints in
general (thanks Jan Wieck) and I just want to show that there _is_ actually
performance loss. Thats all. Most of the chapter will explain what you GAIN
having them! :)

Thanks.

Daniel �kerud

On Mon, 25 Jun 2001, [iso-8859-1] Daniel ���kerud wrote:

OK, I've been discussing this with a collegue of mine... and I'm starting to
see the light here...

I will, first of all, make a new, simpler, 1<->1 realtionship to test FK
constraints... no 2<->1<->2 relasionship here...

Person -> Item/item_fkc

And I will do no bulk-delete. Instead these tests:

Fill person. no time measuring.
Fill item, no foreign keys constraints, time measuring.
Fill item, foreign keys constraints
Compare last to measurments. How much do you loose in performance having the
Foreign Key check?
For all persons {
if (fkc)
delete from person where id = $id
else
delete from person where id = $id; delete from item where personid=$id
}
Compare measurements. How much do you loose having the foreign keys
constraints delete the item?

This, I think, this is a more fair comparison.

Can you call the FK itself a foreign key constraint, as it actually is
constraining something?

Yeah, that's a more even test. Since it sounds like you're writing
something on foreign key constraints (presumably about postgres), you
might want to mention the differences with table clearing deletes
but point out the foreign key constraints aren't generally used in
schemas where you're doing that :)

As for naming, I'd think so. It is a constraint, just that
"foreign key constraint" is much longer to type than FK :)

OK, I've been discussing this with a collegue of mine... and I'm

starting to

see the light here...

I will, first of all, make a new, simpler, 1<->1 realtionship to test FK
constraints... no 2<->1<->2 relasionship here...

Person -> Item/item_fkc

And I will do no bulk-delete. Instead these tests:

Fill person. no time measuring.
Fill item, no foreign keys constraints, time measuring.
Fill item, foreign keys constraints
Compare last to measurments. How much do you loose in performance having

the

Foreign Key check?
For all persons {
if (fkc)
delete from person where id = $id
else
delete from person where id = $id; delete from item where

personid=$id

}
Compare measurements. How much do you loose having the foreign keys
constraints delete the item?

This, I think, this is a more fair comparison.

Can you call the FK itself a foreign key constraint, as it actually is
constraining something?

Yeah, that's a more even test. Since it sounds like you're writing
something on foreign key constraints (presumably about postgres), you
might want to mention the differences with table clearing deletes
but point out the foreign key constraints aren't generally used in
schemas where you're doing that :)

As for naming, I'd think so. It is a constraint, just that
"foreign key constraint" is much longer to type than FK :)

Thanks :)

Daniel �kerud