Open Stack

Clint Byrum <clint at fewbar.com> wrote:

> 
> So I think I didn't speak clearly enough here. The benchmarks are of
> course needed, but there's a tipping point when write activity gets to
> a certain level where it's cheaper to let it get a little skewed and
> correct asynchronously. This is not unique to SQL, this is all large
> scale distributed systems. There's probably a super cool formula for it
> too, but roughly it is
> 
> (num_trans_per_s * cost_of_fk_check_per_trans)
> 
> versus
> 
> (error_cost * error_rate)+(cost_find_all_errors/seconds_to_find_all_errors)

Well the error cost here would be a database that would be “corrupted”,
meaning it has rows which no longer refer to things that exist and the
database is now in a case where it may very well be unusable by the
application, without being rolled back to some known state. 

If Keystone truly doesn’t care about ACID it might want to consider MyISAM
tables, which are faster for read-heavy workloads, though these aren’t
compatible with Galera.

> So it's not really something I think one can blindly accept as "better",
> but rather something that one needs to calculate for themselves. You say
> cost_of_fk_check_per_trans is negligible, but that has been measured as
> not true in the past:
> 
> http://www.percona.com/blog/2010/09/20/instrumentation-and-the-cost-of-foreign-keys/

That’s not a surprising case because the “parent” row being modified is
being referred to by the “child” row that’s still in transaction. This is an
implementation detail of the ACID guarantees which one gets when they use a
relational database. If Keystone’s relational backend in fact has a
performance bottleneck due to an operation like this, it should be visited
individually. But I think it’s extremely unlikely this is actually the case.

> That article demonstrates that the FK adds lock contention in
> InnoDB. There's more. With NDB (MySQL cluster) it's an 18% performance
> hit on raw throughput:
> 
> http://johanandersson.blogspot.com/2013/06/benchmarking-performance-impact-of.html

For NDB cluster, foreign key support was only added to that system two years
ago, in version 5.6.10 in 2013. This is clearly not a system designed to
support foreign keys in the first place, the feature is entirely bleeding
edge for that specific system, and performance like that is entirely
atypical outside for database systems outside of NDB cluster. Specifically
with Openstack, the clustering solution usually used is Galera which has no
such performance issue.

So sure, if you’re using NDB cluster, FOREIGN KEY support is
bleeding edge and you may very well want to disable constraints as you’re
using a system that wasn’t designed with this use case in mind. But because
using a relational database is somewhat pointless if you don’t need ACID,
I’d probably use Galera instead.

> 
> Now, where that point is with Keystone I don't know. The point is, if you
> write the code relying on the existence, Keystone becomes a vertically
> scaling app that cannot ever scale out beyond whatever that limit is.

There seems to be some misunderstanding that using foreign keys to enforce
referential integrity seems to imply that the application is now dependent
on these constraints being in place. I notice that the conversation was
originally talking a bit about allowing rows to be deleted using CASCADE,
and my original question referred to the notion of foreign key use
*overall*, not specifically as a means to offer automatic deletion of
related rows with CASCADE.   The use of foreign key constraints
in openstack applications does not imply an unbreakable reliance
upon them at all, for two reasons.

For the first reason, foreign keys first and foremost offer nothing more
than an integrity guarantee that prevents a particular row from being
deleted or having its primary key modified such that other rows which refer
to that primary key would now be left with an invalid reference. At this
level, you can have an application that is working perfectly, you can then
shut off the foreign key constraints entirely, and the application will
continue to work perfectly with no change; the difference is only that if
the application at some point failed, due to bugs either present or newly
introduced, or if an end user tried to tinker with the database directly,
operations which would leave referencing rows hanging will not be blocked.
So the use of foreign keys at this level has nothing to do with the
application relying upon their existence; they are nothing more than
integrity guarantees which can be silently removed.

For the second, within the realm of ON DELETE CASCADE specifically, Keystone
like all other Openstack applications uses SQLAlchemy for relational
persistence. SQLAlchemy supports foreign key constraints that support or
don’t support ON DELETE CASCADE transparently; meaning, whether or not a
foreign key can be relied upon to delete dependent rows is nothing more than
a configuration option in the mappings; SQLAlchemy will continue to emit a
DELETE statement for rows that are locally mirrored in memory but if it
knows that the foreign key has ON DELETE CASCADE set up, it will let the
rows that are unloaded be deleted automatically. With the flag off, it will
attempt to locate those rows as well. So in this situation, the use of ON
DELETE CASCADE *improves* performance, by reducing database round trips.


> __________________________________________________________________________
> OpenStack Development Mailing List (not for usage questions)
> Unsubscribe: OpenStack-dev-request at lists.openstack.org?subject:unsubscribe
> http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev