yahoo-eng-team team mailing list archive

[liujinxin <1976355@xxxxxxxxxxxxxxxxxx>]

Public bug reported:

ENV: stable/victoria

We have enabled DVR and have some huge virtual routers with around
60 router interfaces scheduled on around 800 compute nodes.

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 restart time to be too long to
operate.

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 routers.id AS routers_id  FROM routers LEFT OUTER JOIN (SELECT routerl3agentbindings.router_id AS router_id, count(routerl3agentbindings.router_id) AS count  FROM 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;


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;


```

from show processlist. and we saw excessive amounts of slow queries for
ml2_port_binding_levels which is weird because it looks like not
necessary.

** Affects: neutron
     Importance: Undecided
         Status: New

-- 
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:
  New

Bug description:
  ENV: stable/victoria

  We have enabled DVR and have some huge virtual routers with around
  60 router interfaces scheduled on around 800 compute nodes.

  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 restart time to be too long to
  operate.

  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 routers.id AS routers_id  FROM routers LEFT OUTER JOIN (SELECT routerl3agentbindings.router_id AS router_id, count(routerl3agentbindings.router_id) AS count  FROM 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;

  
  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;

  
  ```

  from show processlist. and we saw excessive amounts of slow queries
  for ml2_port_binding_levels which is weird because it looks like not
  necessary.

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