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: [email protected]?subject:unsubscribe http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev
__________________________________________________________________________ OpenStack Development Mailing List (not for usage questions) Unsubscribe: [email protected]?subject:unsubscribe http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev
