Open Stack

Note that neutron no longer relies on giant join conditions so heavily due
to explosions in result sizes with the various many to one relationships as
of about a week ago[1].

We switched to subqueries for the one to many relationships to prevent the
result size explosions, so now we issue a lot more queries for an
individual retrieval. I'm not sure if that is better or worse WRT the
sqlalchemy performance but I thought it was important to bring up before we
spend time optimizing the old way of massive joins.

The subqueries make it that much more important that we bulk up requests to
retrieve ports, etc since we now pay a higher constant query cost.

1. https://bugs.launchpad.net/neutron/+bug/1649317

On Feb 16, 2017 8:38 AM, "Mike Bayer" <mbayer at redhat.com> wrote:

>
>
> On 02/15/2017 12:46 PM, Daniel Alvarez Sanchez wrote:
>
>> Also, while having a look at server profiling, around the 33% of the
>> time was spent building SQL queries [1]. Mike Bayer went through this
>> and suggested having a look at baked queries and also submitted a sketch
>> of his proposal [2].
>>
>
> Neutron relies heavily on a big JOIN query that returns just one row. In
> the profiling, it seemed like joined eager loading overhead is
> significant.  Someone independently opened an upstream issue at
> https://bitbucket.org/zzzeek/sqlalchemy/issues/3915/performa
> nce-degradation-on-version-10xx#comment-34442856 with similar comments.
>
> While the "baked" query thing is the ultimate hammer for "ORM SQL
> building" overhead, it's a very heavy hammer to swing as folks will note in
> the gerrit that shows roughly how it would look, it's involved and not that
> easy to work with.
>
> Fortunately, the joined eager load codepaths here have never been
> optimized for the "many short queries" use case, and a large portion of the
> overhead is all rolled up into some SQL alias objects that can be memoized
> so that most of the work they do happens once, instead of thousands of
> times.
>
> In https://gerrit.sqlalchemy.org/311  (note this is SQLAlchemy's gerrit,
> not openstack's) I have a patch that reduces the overhead associated
> specifically with joined eager loaded entities by around 270% for a
> worst-case scenario (which Neutron seems to be close to).  If those folks
> running the load tests can please try this revision out and see if it makes
> a dent, that would be helpful.
>
> Note that SQLAlchemy 1.1 has been out for about five months now, and it's
> time that Openstack move up to 1.1 series - that's where the performance
> enhancement will be.
>
>
>
>
>> I wanted to share these findings with you (probably most of you knew but
>> I'm quite new to OpenStack so It's been a really nice exercise for me to
>> better understand how things work) and gather your feedback about how
>> things can be improved. Also, I'll be happy to share the results and
>> discuss further if you think it's worth during the PTG next week.
>>
>> Thanks a lot for reading and apologies for such a long email!
>>
>> Cheers,
>> Daniel
>> IRC: dalvarez
>>
>> [0] http://imgur.com/WQqaiYQ
>> [1] http://imgur.com/6KrfJUC
>> [2] https://review.openstack.org/430973
>>
>>
>> ____________________________________________________________
>> ______________
>> OpenStack Development Mailing List (not for usage questions)
>> Unsubscribe: OpenStack-dev-request at lists.openstack.org?subject:unsubscrib
>> e
>> http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev
>>
>>
> __________________________________________________________________________
> 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
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.openstack.org/pipermail/openstack-dev/attachments/20170216/f542eadc/attachment.html>