yahoo-eng-team team mailing list archive

[Rodolfo Alonso <1976355@xxxxxxxxxxxxxxxxxx>]

** Changed in: neutron
       Status: In Progress => Won't Fix

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

Title:
  remove eager subquery load for PortBindingLevel

Status in neutron:
  Won't Fix

Bug description:
  ENV: stable/victoria

  In a large scale cloud deployment, when restart neutron agent,
  especially l3 agents, neutron server side will trigger too many slow
  DB query. And this will cause the agent to take too long to restart
  and also increase the load on the DB.

  From the show processlist, we see that ml2_port_binding_levels is too slow. As long as the port is queried, the port_binding_levels will certainly be queried, but in many cases when the port is queried, the port_binding_levels information is not needed.
  Would lazy loading of port_binding_levels make more sense?

  Comparison of interface time consumption for lazy loading and eager loading:
  <get_routers> Elapsed:38.931s
  <get_routers> Elapsed:19.212s

  Error log of l3-agent restart:

  ```
  ymysql.err.OperationalError) (2013, 'Lost connection to MySQL server during query') [SQL: 'SELECT ml2_port_binding_levels.port_id AS ml2_port_binding_levels_port_id, ml2_port_binding_levels.host AS ml2_port_binding_levels_host, ml2_port_binding_levels.level AS ml2_port_binding_levels_level, ml2_port_binding_levels.driver AS ml2_port_binding_levels_driver, ml2_port_binding_levels.segment_id AS ml2_port_binding_levels_segment_id, ports_1.id AS ports_1_id \nFROM (SELECT routers.id AS routers_id \nFROM routers LEFT OUTER JOIN (SELECT routerl3agentbindings.router_id AS router_id, count(routerl3agentbindings.router_id) AS count \nFROM routerl3agentbindings INNER JOIN router_extra_attributes ON routerl3agentbindings.router_id = router_extra_attributes.router_id INNER JOIN routers ON routers.id = router_extra_attributes.router_id GROUP BY routerl3agentbindings.router_id) AS anon_2 ON routers.id = anon_2.router_id) AS anon_1 INNER JOIN routerports AS routerports_1 ON anon_1.routers_id = routerports_1.router_id INNER JOIN ports AS ports_1 ON ports_1.id = routerports_1.port_id INNER JOIN ml2_port_binding_levels ON ports_1.id = ml2_port_binding_levels.port_id ORDER BY ports_1.id'] (Background on this error at: http://sqlalche.me/e/e3q8)
  ```
  as well as

  ```console

  SELECT ml2_port_binding_levels.port_id AS ml2_port_binding_levels_port_id, ml2_port_binding_levels.host AS ml2_port_binding_levels_host, ml2_port_binding_levels.level AS ml2_port_binding_levels_level, ml2_port_binding_levels.driver AS ml2_port_binding_levels_driver, ml2_port_binding_levels.segment_id AS ml2_port_binding_levels_segment_id, ports_1.id AS ports_1_id
  FROM (SELECT DISTINCT routerports.port_id AS routerports_port_id
  FROM routerports
  WHERE routerports.router_id IN ('6e4ed0f5-e1b0-4cf1-931d-b30c93433719') AND routerports.port_type IN ('network:router_interface', 'network:ha_router_replicated_interface', 'network:router_interface_distributed')) AS anon_1 INNER JOIN ports AS ports_1 ON ports_1.id = anon_1.routerports_port_id INNER JOIN ml2_port_binding_levels ON ports_1.id = ml2_port_binding_levels.port_id ORDER BY ports_1.id;

  ```

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