Get external networks too slowly because it would join subnet and rbac

[Keyang Li]

Getting networks would join subnet and rbac tables.
When the number of rbac objects which belong to the network is huge, and the network has many subnets.
It would costs sqlachemy much time to instance the orm object of this network.
Such as, a external network with 25 subnets and share to 5000 project.

reproduce this case through the following steps:

1. neutron net-create test_net --router:external True ---> create a external network
2. for i in {1..5000}; do openstack project create test$i;done ----> create 5000 projects
3. for i in {1..25}; do neutron subnet-create test_net 192.168.$i.0/24;done ----> create 25 subnets
4. openstack project list | grep test | awk -F '|' '{print $2}' > tenant_ids -----> save 5000 project ids
5. for i in `cat tenant_ids`;do neutron rbac-create --tenant-id dd969098bfe14970b3b77fd5d524d697 --target-tenant $i --type network --action access_as_shared 619bf61d-46fa-4a8c-84e4-ee249da1afa3;done ---> create 5000 rbac to share the network. dd969098bfe14970b3b77fd5d524d697 is the project id of the network and 619bf61d-46fa-4a8c-84e4-ee249da1afa3 is the id of the network.
6. time neutron net-show test_net ---> this would cost 15+ seconds to get the info of the external network

[Keyang Li (ilkykli)]

the DB sql:
----------------------------------------
SELECT networkrbacs.tenant_id AS networkrbacs_tenant_id, networkrbacs.id AS networkrbacs_id, networkrbacs.target_tenant AS networkrbacs_target_tenant, networkrbacs.action AS networkrbacs_action, networkrbacs.object_id AS networkrbacs_object_id, subnets_1.network_id AS subnets_1_network_id
        FROM (
  SELECT DISTINCT networks.id AS networks_id
        FROM networks
  LEFT OUTER JOIN networkrbacs ON networks.id = networkrbacs.object_id
  LEFT OUTER JOIN externalnetworks ON networks.id = externalnetworks.network_id
  LEFT OUTER JOIN ml2_network_segments ON networks.id = ml2_network_segments.network_id
  JOIN standardattributes ON standardattributes.id = networks.standard_attr_id
        WHERE ml2_network_segments.is_dynamic IS false AND (networks.tenant_id = 'fd27c1f66a5d4f888672a18b1b0c95b6' OR networkrbacs.action = 'access_as_shared' AND (networkrbacs.target_tenant = 'fd27c1f66a5d4f888672a18b1b0c95b6' OR networkrbacs.target_tenant = '*')) AND networks.name IN ('cmnet01_net0116') AND externalnetworks.network_id IS NOT NULL) AS anon_1
  JOIN subnets AS subnets_1 ON anon_1.networks_id = subnets_1.network_id
  JOIN networkrbacs ON subnets_1.network_id = networkrbacs.object_id
  ORDER BY subnets_1.network_id
--------------------------------------------------------------------

In fact,there is no need to join rbac after join subnet when get network.
because before join subnet it process the rbac here:https://github.com/openstack/neutron-lib/blob/master/neutron_lib/db/model_query.py#L121

[Keyang Li (ilkykli)]

SELECT networkrbacs.tenant_id AS networkrbacs_tenant_id, networkrbacs.id AS networkrbacs_id, networkrbacs.target_tenant AS networkrbacs_target_tenant, networkrbacs.action AS networkrbacs_action, networkrbacs.object_id AS networkrbacs_object_id, subnets_1.network_id AS subnets_1_network_id
        FROM (
  SELECT DISTINCT networks.id AS networks_id
        FROM networks
  LEFT OUTER JOIN networkrbacs ON networks.id = networkrbacs.object_id ------1---> https://github.com/openstack/neutron-lib/blob/master/neutron_lib/db/model_query.py#L121
  LEFT OUTER JOIN externalnetworks ON networks.id = externalnetworks.network_id
  LEFT OUTER JOIN ml2_network_segments ON networks.id = ml2_network_segments.network_id
  JOIN standardattributes ON standardattributes.id = networks.standard_attr_id
        WHERE ml2_network_segments.is_dynamic IS false AND (networks.tenant_id = 'fd27c1f66a5d4f888672a18b1b0c95b6' OR networkrbacs.action = 'access_as_shared' AND (networkrbacs.target_tenant = 'fd27c1f66a5d4f888672a18b1b0c95b6' OR networkrbacs.target_tenant = '*')) AND networks.name IN ('cmnet01_net0116') AND externalnetworks.network_id IS NOT NULL) AS anon_1
  JOIN subnets AS subnets_1 ON anon_1.networks_id = subnets_1.network_id -----2-----> https://github.com/openstack/neutron/blob/master/neutron/db/models_v2.py#L258
  JOIN networkrbacs ON subnets_1.network_id = networkrbacs.object_id -----3---> https://github.com/openstack/neutron/blob/master/neutron/db/models_v2.py#L203
  ORDER BY subnets_1.network_id

when get network, I think in the step1 already join rbac and process, there's no need to join rbac again after join subnet table. right?