openstack team mailing list archive

[Monty Taylor <mordred@xxxxxxxxxxxx>]

On 04/26/2012 10:14 AM, Sean Dague wrote:
> On 04/25/2012 05:17 PM, Vishvananda Ishaya wrote:
>> The main issue is when the relevant tables are moved into a separate
>> service a la quantum or cinder. We can't keep referential integrity
>> across multiple databases, so the foreign keys in this case need to be
>> removed. It leads to an odd situation when there is still an internal
>> implementation in addition to the external implementation because the
>> internal implementation no longer has foreign keys.
>>
>> As an example, we used to have foreign key relationships between
>> instances and networks. We can no longer have these because we support
>> networks declared externally. The internal network management now has no
>> referential integrity, but this is the price we pay for separation of
>> concerns. We are going through a similar set of relationship-breaking
>> with the volume code.
> 
> There are definitely the practical aspects of where this "can't" be done
> because the services have split out, and I think that's fine.
> 
> But enforcing the ref constraints where possible just provides another
> level of safety in the data. A policy where we break FK relationships if
> the preferred core model is 2 services (i.e. Nova / Quantum), but we add
> FK constraints within a service might be a good idea.

SO ... in a production MySQL service in this situation, under no
circumstances should foreign keys actually be applied to the database.
Specifying them as part of the SqlAlchemy model is fine, and I believe
conveys the informational relationships that are important. But it turns
out that in practice, especially with an ORM running things, the
performance hit of adding them is pretty bad (generates tons of unneeded
index scans, for one thing) If all of your db access is via the ORM
layer, there is absolutely zero actual benefit.

I think the real key is to have a config option to tell sqlalchemy to
not, even if we're running innodb, add the foreign keys to the DDL sent
to the database. If sqlalchemy doesn't have that ability, we should
write it and contribute it, because anyone using MySQL at scale via
sqlalchemy actually wants the feature, whether they recognize it yet or not.

Monty