yahoo-eng-team team mailing list archive

[Brian Haley <2067770@xxxxxxxxxxxxxxxxxx>]

Public bug reported:

During a mailing list discussion on some OOM issues neutron has been
seeing [0], Mike Bayer recommended we should change from using subquery
to selectin DB load strategy.

A full description of this strategy can be found here [1], but in short:

- “subquery” loading also incurs additional performance / complexity
issues when used on a many-levels-deep eager load, as subqueries will be
nested repeatedly.

- "The subqueryload() eager loader is mostly legacy at this point,
superseded by the selectinload()

- "The only scenario in which selectin eager loading is not feasible is
when the model is using composite primary keys, and the backend database
does not support tuples with IN, which currently includes SQL Server."
So that does not apply to us.

The plan agreed to at the neutron drivers meeting [2] was to make this
change early in the cycle so we would be able to see if there were any
issues through the D cycle.

[0] https://lists.openstack.org/archives/list/openstack-discuss@xxxxxxxxxxxxxxxxxxx/thread/EHLQQXNG3NLLZYPDGG2ES3DINIJ7YT3N/
[1] https://docs.sqlalchemy.org/en/20/orm/queryguide/relationships.html#selectin-eager-loading
[2] https://meetings.opendev.org/meetings/neutron_drivers/2024/neutron_drivers.2024-05-31-14.00.log.html#l-67

** Affects: neutron
     Importance: Wishlist
     Assignee: Brian Haley (brian-haley)
         Status: In Progress


** Tags: 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/2067770

Title:
  Neutron should use selectin for DB load strategy

Status in neutron:
  In Progress

Bug description:
  During a mailing list discussion on some OOM issues neutron has been
  seeing [0], Mike Bayer recommended we should change from using
  subquery to selectin DB load strategy.

  A full description of this strategy can be found here [1], but in
  short:

  - “subquery” loading also incurs additional performance / complexity
  issues when used on a many-levels-deep eager load, as subqueries will
  be nested repeatedly.

  - "The subqueryload() eager loader is mostly legacy at this point,
  superseded by the selectinload()

  - "The only scenario in which selectin eager loading is not feasible
  is when the model is using composite primary keys, and the backend
  database does not support tuples with IN, which currently includes SQL
  Server." So that does not apply to us.

  The plan agreed to at the neutron drivers meeting [2] was to make this
  change early in the cycle so we would be able to see if there were any
  issues through the D cycle.

  [0] https://lists.openstack.org/archives/list/openstack-discuss@xxxxxxxxxxxxxxxxxxx/thread/EHLQQXNG3NLLZYPDGG2ES3DINIJ7YT3N/
  [1] https://docs.sqlalchemy.org/en/20/orm/queryguide/relationships.html#selectin-eager-loading
  [2] https://meetings.opendev.org/meetings/neutron_drivers/2024/neutron_drivers.2024-05-31-14.00.log.html#l-67

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