Open Stack

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/performance-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:unsubscribe
> http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev
>