Inefficient Security Group listing

[Joris Hartog]

Issue:

Fetching a large Security Group list takes relatively long as several database queries are made for each Security Group.

Context:

Listing SG's takes around 9 seconds with ~500 existing SG's, 16 seconds with ~1000 SG's and around 30 seconds with ~1500 existing SG's, so this time seems to grow at least linearly with number of SG's.

We've looked at flamegraphs of the neutron controller which show that the stack frame `/usr/lib/python2.7/site-packages/neutron/db/securitygroups_db.py:get_security_groups:166` splits into two long running functions, each taking about half of the time (one at line 112 and the other at 115).

```python
103 @classmethod
104 def get_objects(cls, context, _pager=None, validate_filters=True,
105 **kwargs):
106 # We want to get the policy regardless of its tenant id. We'll make
107 # sure the tenant has permission to access the policy later on.
108 admin_context = context.elevated()
109 with cls.db_context_reader(admin_context):
110 objs = super(RbacNeutronDbObjectMixin,
111 cls).get_objects(admin_context, _pager,
112 validate_filters, **kwargs)
113 result = []
114 for obj in objs:
115 if not cls.is_accessible(context, obj):
116 continue
117 result.append(obj)
118 return result
```

We've also seen that the number of database queries also seems to grow linearly:

* Listing ~500 SG's performs ~2100 queries
* Listing ~1000 SG's performs ~3500 queries
* Listing ~1500 SG's performs ~5200 queries

This does not scale well, we're expecting a neglectable increase in listing time.

Reproduction:

* Create 1000 SG's
* Execute `time openstack security group list`
* Create 500 more SG's
* Execute `time openstack security group list`

Version:

We're using neutron 14.0.2-1 on CentOS 7.7.1908.

Perceived Severity:

MEDIUM

[Brian Haley (brian-haley)]

This was fixed in https://review.opendev.org/#/c/665566/ and backported to stable/stein (15.0.0), it wasn't backported further.

Closing as a duplicate of https://bugs.launchpad.net/neutron/+bug/1830679

[Kevin S (kevins2)]

This issue isn't fixed by https://review.opendev.org/#/c/665566/, we have verified the changes have been applied.

So we are running neutron version 14.0.3-1 now as we believe it has been backported to 14.0.3: https://bugs.launchpad.net/neutron/+bug/1830679/comments/21

Examples:
~100 security groups: openstack security group list takes about: ~3 seconds
~1000 security groups: openstack security group list takes about: ~16 seconds
~1500 security groups: openstack security group list takes about: ~30 seconds