yahoo-eng-team team mailing list archive

[Miro Tomaska <2069061@xxxxxxxxxxxxxxxxxx>]

Public bug reported:

We have attempted to optimize the number of sql queries neutron makes during testing with some "cheap wins" such as using `joined` instead of `subquery` relationship [1]. This helped with the number of queries but the actual optimization was never measured, it was just marked as "significant".  
By doing this "cheap win" we have introduced regression in the development code for some cases where a large amount of tags is defined. This is documented in LP[2] and subsequently reverted in [3].

This LP is filled to go back and explore the use of `selectin`[4]
relation which should help with reducing the queries AND not cause
regression with large deployments. The `selectin`  option has been
available since around SQLAlchemy 1.3 but given it was in its infancy
back then, we should consider this with later sqlalchemy, e.i. 2.0+.


[1] https://review.opendev.org/c/openstack/neutron/+/891580
[2] https://launchpad.net/bugs/2068761
[3] https://review.opendev.org/c/openstack/neutron/+/921566
[4] https://docs.sqlalchemy.org/en/20/orm/queryguide/relationships.html#summary-of-relationship-loading-styles

** Affects: neutron
     Importance: Medium
         Status: New


** Tags: db

** Changed in: neutron
   Importance: Undecided => Medium

** Tags added: db

-- 
You received this bug notification because you are a member of Yahoo!
Engineering Team, which is subscribed to neutron.
https://bugs.launchpad.net/bugs/2069061

Title:
  Investigate 'selectin' relation optimization with newer SQLAlchemy

Status in neutron:
  New

Bug description:
  We have attempted to optimize the number of sql queries neutron makes during testing with some "cheap wins" such as using `joined` instead of `subquery` relationship [1]. This helped with the number of queries but the actual optimization was never measured, it was just marked as "significant".  
  By doing this "cheap win" we have introduced regression in the development code for some cases where a large amount of tags is defined. This is documented in LP[2] and subsequently reverted in [3].

  This LP is filled to go back and explore the use of `selectin`[4]
  relation which should help with reducing the queries AND not cause
  regression with large deployments. The `selectin`  option has been
  available since around SQLAlchemy 1.3 but given it was in its infancy
  back then, we should consider this with later sqlalchemy, e.i. 2.0+.

  
  [1] https://review.opendev.org/c/openstack/neutron/+/891580
  [2] https://launchpad.net/bugs/2068761
  [3] https://review.opendev.org/c/openstack/neutron/+/921566
  [4] https://docs.sqlalchemy.org/en/20/orm/queryguide/relationships.html#summary-of-relationship-loading-styles

To manage notifications about this bug go to:
https://bugs.launchpad.net/neutron/+bug/2069061/+subscriptions